MySQL Bugs: #39128: Load balancing of two backends of mysql servers(master/slave) cause 7x perf drop

Bug #39128	Load balancing of two backends of mysql servers(master/slave) cause 7x perf drop
Submitted:	29 Aug 2008 17:00	Modified:	9 Mar 2009 21:00
Reporter:	jenny chen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Proxy	Severity:	S2 (Serious)
Version:	0.6.0	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
When ran mysqlslap test with one query per client and 100 concurrent clients on one 2xCPUs operon box, I can get average number of seconds to run all quries arround 1.05sec

Then set up two mysql servers as master on 2xCPU operon box and a slave on the other 2xCPU operon box.    
ran the same query and 100 concurrent users with mysqlslap, I can get average number of seconds to run all quries arround 7.08 sec

When observing the mysql status of the two servers(master/slave) at run time:
on the master server, the "Threads_created" is 166, and "threads_running/connected" is only 11; and on the slave server, the "Thread_created" is 294, and "Threads_runing/connected" is only 1

According to JK's MySQL Proxy document: in Load balancing, each backend will get the same number of connections. So in my test case which have totally 100 connections, is mysql proxy supposed to connect each server with 50 connections? But what I observed from the mysql status output at runtimes, there are many threads created, but only few connected to the server, and the overall performance with the mysql proxy with M/S servers setup on two servers is 7x worse than running single mysql server on single server without mysql proxy

How to repeat:
Start mysql proxy with:
mysql-proxy --proxy-skip-profiling --proxy-backend-address=h1:3306 --proxy-backend-address=h2:3306

Run mysqlslap:
mysqlslap -hproxy-server-hostname -P4040 ./simple.sql --create-schema=sugarcrm -c 100 -i 5

the simple.sql is:
select id,first_name,last_nameuser_name from users where 1-1 AND is_group=0 order by user_name asc

the mysql servers were re-started before each tests, and the thread_running/connected are all gotten at test runtime

This is not the problem of mysqlslap. Using the other test workload(dbt2), I got the similar result. The command to start the proxy was:

mysql-proxy --proxy-skip-profiling --proxy-backend-addresses=master:3306 --proxy-read-only-backend-addresses=slave:3306

After using proxy, the perf drop 3x comparing to not using mysql proxy.

(If you can access machine on SWAN, I can send you the login info to access the test if you need.)

Thanks

Thank you for the feedback.

Please try with current version 0.7: I only can get 2x average performance drop on Solaris 10 SPARC, but not 7x

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".