Eric,
Great info!
I ran the same test with MySQL and had this:
MySQL, TCP, using 127.0.0.1
p...@hanuman:~/perl$ time ./mysql_connecttest.pl
queries 100001 times
real 1m43.316s
user 0m40.210s
sys 0m14.570s
980 connect/query/disconnects per sec
MySQL, TCP, USING 192.168.1.xxx
p...@hanuman:~/perl$ time ./mysql_connecttest.pl
queries 100001 times
real 1m42.821s
user 0m40.920s
sys 0m13.850s
980 connect/query/disconnects per sec
MySQL, Unix domain socket
p...@hanuman:~/perl$ time ./mysql_connecttest.pl
queries 100001 times
real 1m22.790s
user 0m38.710s
sys 0m5.830s
1219 connect/query/disconnects per sec
Drizzle:
p...@hanuman:~/perl$ time ./drizzle.pl
DBI connect('database=test;host=localhost','',...) failed:
drizzle_state_connect:connect:99 at ./drizzle.pl line 15
Can't call method "do" on an undefined value at ./drizzle.pl line 27.
drizzle_state_connect:connect:99, count 28233
real 0m24.675s
user 0m11.820s
sys 0m6.890s
(1176 connects, queries, disconnects per sec)
Thoughts? What does MySQL do differently?
Thanks!
--Patrick
Eric Day wrote:
Hi Patrick!
This is the result of sockets being kept around in the kernel in the
TIME_WAIT state so any axtra TCP packets can be flushed out. The 28000
is the default port range available for socket connections. You can
see this with:
# cat /proc/sys/net/ipv4/tcp_fin_timeout
60
# cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000
61000-32768= 28232
(these are the defaults on Debian Linux).
So you only have a pool of 28232 sockets to work with, and each will
linger around for 60 seconds in a TIME_WAIT state even after being
close()d on both ends. You can increase your port range and lower your
TIME_WAIT value, but it's only buying you a larger window. If you push
connects/disconnects through fast enough you'll hit the new limit. :)
Something to keep in mind though for servers that have a high
connect rate.
-Eric
On Thu, Jul 09, 2009 at 12:26:11PM -0400, Patrick Galbraith wrote:
Hi all,
I am in the process of trying to get sql-bench to work with Drizzle, and
stumbled into this issue that at first I thought was a problem with
DBD::drizzle, but in reducing the problem to a simple C program, the
problem still exists. All the program does is connect, select one row
from a table with a single record, disconnect, in a loop that goes as
high as 100k. It most often fails at 28,200 or so connections. The Perl
test in sql-bench is test-insert. When I ran the Perl program through
valgrind, it slowed the execution enough for the test to succeed with
100k settings.
If I run the test right after failing, it doesn't work, as well as
connecting using the drizzle client.
--Patrick
-- DRIZZLE dump 10.13 Distrib 0.3, for unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 2009.06.1058
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
--
-- Table structure for table `bench1`
--
DROP TABLE IF EXISTS `bench1`;
CREATE TABLE `bench1` (
`a` int NOT NULL,
`i` int DEFAULT NULL,
`s` varchar(10) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB;
--
-- Dumping data for table `bench1`
--
ALTER TABLE `bench1` DISABLE KEYS;
INSERT INTO `bench1` VALUES (1,100,'AAA');
ALTER TABLE `bench1` ENABLE KEYS;
SET foreign_key_chec...@old_foreign_key_checks;
SET unique_chec...@old_unique_checks;
-- Dump completed on 2009-07-08 17:42:08
/*
* Drizzle Client & Protocol Library
*
* Copyright (C) 2008 Eric Day ([email protected])
* All rights reserved.
*
* Use and distribution licensed under the BSD license. See
* the COPYING file in this directory for full text.
*/
#include <stdio.h>
#include <string.h>
#include <libdrizzle/drizzle_client.h>
int main(int argc, char *argv[])
{
char query[100];
drizzle_con_st con;
drizzle_result_st result;
drizzle_return_t ret;
char **row;
int i= 1;
for (i= 1; i <= 100000; i++ )
{
sprintf(query, "select a,i,s,%d from bench1", i);
if (drizzle_con_create(NULL, &con) == NULL)
{
printf("drizzle_con_create:NULL\n");
return 1;
}
drizzle_con_set_db(&con, "test");
(void)drizzle_query_str(&con, &result, query, &ret);
if (ret != DRIZZLE_RETURN_OK)
{
printf("query %s\n", query);
printf("drizzle_query:%s\n", drizzle_con_error(&con));
return 1;
}
ret= drizzle_result_buffer(&result);
if (ret != DRIZZLE_RETURN_OK)
{
printf("drizzle_result_buffer:%s\n", drizzle_con_error(&con));
return 1;
}
drizzle_result_free(&result);
drizzle_con_free(&con);
}
return 0;
}
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
|
