And just to be clear, I am able to reproduce this problem on any
tcp connection to any port (not just mysql). We first noticed it in our
production network on mysql, but were able to see it in our proxy =>
backend layer as well as backend => backend layer (ftp/ssh).
I have attached the perl script I use to collect data and to
articifically generate the timeouts (requires the non-stanard
Getopt::Declare module).
Typical usage is something like:
tcptest multdb.pn -f
or for many hosts using port 80 and sleeping 100ms in between
tcptest -rf host1 host2 host3 -p http -u 100000
on Wednesday, Apr 02, 2008, Gabriel Barazer, wrote:
> On 04/01/2008 11:30:43 PM +0200, Bernd Eckenfels <ecki@xxxxxxxxxxxx> wrote:
>> In article <47F29777.6020400@xxxxxxxx> you wrote:
>>> I'm not sure what do you mean by "mass produce" connections, but even
>>> with only 200 connections/sec to the mysql-server the delay occured.
>> Just an unrelated question: why dont you use a connection pool in this
>> scenario. With Oracle for example that would totally break down, a typical
>> connect takes 80-130ms with Oracle on *ix.
>
> [off-topic]
>
> There is a very good reason, and the Oracle example you give is the one I
> use for comparison when I am asked this. You are far from being the first
> one to ask this very legit question :)
>
> MySQL compared to Oracle has a _very_ low connection overhead by design
> (~1ms), and also opposed to Oracle, has weaknesses to manage high number of
> permanent connections (mainly because of the memory management). Besides,
> there are several cons using the same connection: transactions not properly
> reset, latent session-level variables, etc. and other MySQL-specific
> features. You can't really have a "clean" environment when using an already
> used connection thread with MySQL.
>
> This is also why MySQL became so popular among web/php programmers: because
> the concept of connecting/querying/closing a mysql resource in the same
> script is very easy to understand (and the only way to do with CGI scripts)
> and MySQL makes it viable thanks to its low connection overhead.
>
> This article may be of interest for further information:
> http://www.mysql.com/news-and-events/newsletter/2002-11/a0000000086.html
>
> [/off-topic]
>
> Back to the topic :)
>
> Gabriel
> --
> To unsubscribe from this list: send the line "unsubscribe linux-net" in
> the body of a message to majordomo@xxxxxxxxxxxxxxx
> More majordomo info at http://vger.kernel.org/majordomo-info.html
#!/usr/bin/perl
use IO::Socket;
use Time::HiRes qw/gettimeofday tv_interval usleep/;
use Getopt::Declare;
our $SlowConnection = 10;
our $Brutality = 3000;
our $Babality = 5000;
our $Iterations = 10000;
our $Proto = 'mysql';
our @Hosts = ();
our $Live = 0;
our $Usleep = 0;
our $Randomize;
our $HostName = `hostname --short`;
our $FormatTemplate = 'A12A18A8A8A8A8A5';
chomp $HostName;
my $specification = q(
[strict]
-s <ms> slow connection threshold in ms. Default is 10ms.
{ $::SlowConnection = $ms }
-b <ms> brutally slow connection threshold in ms. Default is 3000ms.
{ $::Brutality = $ms }
-i <num> number of iterations to repeat test. Default is 1000.
{ $::Iterations = $num }
-f run in live feedback mode
{ $::Live++ }
<hosts>... hosts to process
{ @::Hosts = @hosts }
-u <nmu> sleep between connection attempts nmu microseconds.
{ $::Usleep = $nmu }
-p <proto> protocol to use. Default is mysql
{ $::Proto = $proto }
-r randomize order of hosts specified on command line.
{ $::Randomize++ }
);
my $args = Getopt::Declare->new($specification);
$|++;
my $count;
my $times = {};
print pack($FormatTemplate,'from','to','conns','>10ms','>3000ms','>5000ms','avg') . "\n";
while (1) {
@Hosts = randomize(@Hosts) if $Randomize;
foreach my $host (@Hosts) {
my $time = inetconnect($host);
$times->{$host}->{total} += $time;
$times->{$host}->{count}++;
$times->{$host}->{slow}++ if $time >= $SlowConnection;
$times->{$host}->{brutality}++ if $time >= $Brutality and $time < $Babality;
$times->{$host}->{babality}++ if $time >= $Babality;
if ($Live and $count and ($count % (scalar @Hosts) == 0)) {
foreach my $rhost (@Hosts) {
print format_line($HostName,$rhost,$times->{$rhost}) . "\015";
}
}
}
last if ++$count >= $Iterations;
usleep($Usleep) if $Usleep;
}
foreach my $host (@Hosts) {
print format_line($HostName,$host,$times->{$host}) . "\n";
}
sub format_line {
my ($from, $to, $thash) = @_;
$to =~ s/\:.*$//;
return pack(
$FormatTemplate,
$from,
$to,
$thash->{count},
$thash->{slow},
$thash->{brutality},
$thash->{babality},
int($thash->{total}/$thash->{count}),
);
}
sub inetconnect {
my ($host,$port) = split(/\:/,shift);
$port ||= $Proto;
my $start = [gettimeofday];
my $sock = IO::Socket::INET->new(
PeerAddr => $host,
PeerPort => $port,
Proto => 'tcp'
) or warn "could not open socket! $@";
return int(tv_interval($start)*1000);
}
sub randomize {
my @items = @_;
my @new_items = ();
while (my $rand_item = splice(@items,int(rand(scalar @items)),1)) {
push @new_items,$rand_item;
}
return @new_items;
}
