Be careful with my findings, I appear to have compile in debug mode, I am redoing the benchmarks. Updated version here.
I recently had to work with many customers having large number of connections opened in MySQL and although I told them this was not optimal, I had no solid arguments to present. More than once, I heard: it is not a problem, those connections are Sleeping! In order to remedy to this situation, I decided to run a series of DBT2 benchmarks while the number of idle connections was varied from 0 to 1500. I was expecting an influence, because those idle threads are presents in internal MySQL list objects that need to be scanned and also the socket SELECT call needs to build a FD bitmap after every network activity and the size of the bitmap is proportional to the number of active threads. What I found is not a small impact at all.
For my daily work, I use 2 computers linked with a gigabit switch so I decided to use them for benchmarking. On the desktop, I installed Percona-Server-11.2 which I configured with the following Innodb settings:
|
1 2 3 4 5 6 7 |
innodb_buffer_pool_size=1G innodb_log_file_size=64M innodb_log_buffer_size=8M innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 max_connections = 10000 open_files_limit = 32768 |
The desktop is running Ubuntu 10.04, has 8GB of RAM and a Core i5 CPU (dual core with HT enabled). Since my goal is to test concurrency, I decided to use only one warehouse for DBT2 which easily fits inside the buffer pool. Then, with innodb_flush_log_at_trx_commit=0, I ensured I was not benchmarking my disk.
On the laptop, I ran the following script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
#!/bin/bash for i in `seq 0 4` do numconn=$((i*20)) echo "Doing $numconn idle connections" php ./make_conn.php $numconn & cat dbt2.sql | mysql -h 10.2.2.129 -u root dbt2 sleep 120 ./run_mysql.sh -h 10.2.2.129 -u root -c 4 -t 600 -w 1 -s 1 -m 100 -e yes killall php sleep 2 done for i in `seq 1 15` do numconn=$((i*100)) echo "Doing $numconn idle connections" php ./make_conn.php $numconn & cat dbt2.sql | mysql -h 10.2.2.129 -u root dbt2 sleep 120 ./run_mysql.sh -h 10.2.2.129 -u root -c 4 -t 600 -w 1 -s 1 -m 100 -e yes killall php sleep 2 done |
Where 10.2.2.129 is the IP of the Desktop. This script uses a PHP script, make_conn.php, to generate the idle connections. This script is the following:
|
1 |
<!--?php $connarray = array(); for ($i = 0;$i < $argv[1];$i++) { $connarray[$i] = mysql_connect('10.2.2.129', 'root','',TRUE); } sleep(1000);?--> |
Before each benchmark, the database is reinitialized to ensure consistent benchmarks. During all the benchmarks, the CPU load on the laptop was never above 10%. I also hacked a bit the “run_mysql.sh” script to allow more processing threads (100) instead of the max of 20. This is required for another series of benchmarks I’ll present soon, this time looking at the number active connections. For these benchmarks, I use pools of 4 connections, basically to match the number of available computing threads the Core i5 allows. I know by experience that this is about the max DBT2 NOTPM for small number of threads.
So, here are the results:
As you can see, the performance drop is shocking. At 1500 idle connections, the performance is 1.3% of the one with 0 idle. Even for as few as 20 idle connections, the drop is already of 40%. I also verified the laptop (running dbt2) was not slowed down by handling all those connections. I tried generating the idle connections from a third box and the results were the exact same. The conclusion is quite straightforward, idle connections hurts performance a lot!!! I am curious as to where MySQL is spending its time, I am planning to use profiling tools to identify the culprit but I had no time yet to do that. I am afraid the same scalability issues affects actives connections although these are also affected by concurrency issues. With new servers having 24+ cores, this phenomenon is seriously affecting performances.
