MySQL Bugs: #45878: MySQL Proxy hangs sysbench

Bug #45878	MySQL Proxy hangs sysbench
Submitted:	1 Jul 2009 14:03	Modified:	17 Aug 2009 15:08
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Proxy: Core	Severity:	S2 (Serious)
Version:	0.8.0 r682	OS:	Any
Assigned to:	MEM Agent Devs	CPU Architecture:	Any

Description:
This bug is for tracking purposes. If there is anything else I could do to provide more information, please add a comment.

I was running some benchmarks using sysbench and I noticed that after a number of runs, the proxy seems to hang sysbench. In the sense that sysbench does not send any more queries.

You can still open a new client connection through the proxy using another terminal window.

When I run

$ netstat -an 
I only get 2 connections on CLOSE_WAIT and just one on TIME_WAIT

This is running mysql proxy and mysqld on one box with 8 cores and 8GB of ram and sysbench on another box with 8 cores and 8 GB RAM

How to repeat:
I will add simpler steps later today, for now, I do this:

1- Start the proxy with event-threads=16
2- On another box, use this file

$cat run_benchmark.sh
#!/bin/bash
# run this benchmark 10 times with mysql and 10 times with proxy + mysql
echo "Sysbench using $1 threads" >> mysql_proxy.log
for i in {1..4}; do
  date >> mysql_proxy.log ;
  /export/home/merlin/sysbench-binary/bin/sysbench  --test=oltp  --oltp-table-size=1000000    --mysql-host=bur02.norway.sun.com --mysql-port=3306 --mysql-user=agent --mysql-password=mysql --mysql-db=test   cleanup >> mysql_proxy.log;
  /export/home/merlin/sysbench-binary/bin/sysbench  --test=oltp  --oltp-table-size=1000000    --mysql-host=bur02.norway.sun.com --mysql-port=3306 --mysql-user=agent --mysql-password=mysql --mysql-db=test  prepare >> mysql_proxy.log;
  /export/home/merlin/sysbench-binary/bin/sysbench  --num-threads=$1 --max-requests=100000 --test=oltp --oltp-table-size=1000000  --oltp-read-only --mysql-host=bur02.norway.sun.com --mysql-port=3306 --mysql-user=agent --mysql-password=mysql --mysql-db=test  run >>mysql_proxy.log;
  date >> mysql_proxy.log;
  sleep 120;
done;

3- Execute 
$ ./run_benchmark.sh 4 8

After the 3rd time that loop runs, it will just sit there, no high COU usage anywhere, and no more queries from sysbench.

Diego Medina writes: 
I need a binary with this revision

"Rev 708: raise the listen() backlog from 8 to 128"

Diego Medina writes: 
revno: 708
committer: jan@mysql.com
branch nick: trunk
timestamp: Thu 2009-07-02 16:52:53 +0200
message:
 raise the listen() backlog from 8 to 128 (fixes #43278)
modified:
 src/network-socket.c

Diego Medina writes: 
testing r740

Diego Medina writes: 
Verified fixed on 0.8.0

The backlog size was 8, and now it is 128

A note has been added o the 0.7.3 and 0.8.0 changelog: 

MySQL Proxy could stop accepting network packets if a large number of packets were sent to the proxy. The listen queue has been extended to allow a larger backlog.