MySQL Bugs: #43349: MySQL-connector-net was hanged when processing high load requests.

Bug #43349	MySQL-connector-net was hanged when processing high load requests.
Submitted:	4 Mar 2009 7:02	Modified:	20 Jun 2009 8:50
Reporter:	david yeung	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5	OS:	Linux (RED HAT AS5)
Assigned to:		CPU Architecture:	Any
Tags:	High load, MySQL, MySQL-connector-net

Description:
When clients gave a high load to the whole application, the CPU's wait of the MySQL server is very high and could not process any request.

MySQL-connector-net can not process any requests at the same time.

So we changed the source code of mysql-connector-net. Then MySQL could process very high load and QPS is above 13000/s.
Here is the code which we changed.
 

How to repeat:
Try to call mysql's store procedure using .net + mysql-connector-net and simulate a high concurrency load to MySQL. (MySQL's data is about 150GB)

Suggested fix:
We have changed the source code of this section.

See attachment.

Hi David and thanks for your report.

I do not see any code attached.

This is the source code which we have already changed.

Attachment: bug.connector.net (application/octet-stream, text), 9.72 KiB.

Thanks for your fast reply.

I have put a new attachment here, please review again.

David.

I was too fast so I did not saw your first post.
Now, I have no such environment to reproduce what you claim nor is "speed" fixed description equal to everyone.
Can you please elaborate changes you've done (where, what, why... probably good to put them in attached file with code as a comment) so I can pass this for review?

David, I tried with 15 worker threads of which 4 are calling 3 column SP with 1 input parameter (INT) and the rest are doing SELECT against ~500 row table. Each worker thread does it's job in 1000 iterations... Although my server log looks like battlefield, I was not able to repeat the problem. I am consulting colleagues now on how to put more stress on environment.

MySQL server 5.1.31 is on remote OpenSolaris 2008.11 x64 host while IIS is running from my workstation, W2K8 x64 server. Both MySQL and IIS servers run with default configurations.

I'm sorry that I got the wrong number of the real QPS.

Our QPS is very high which up to 130000/s .

Tonci if I may comment on this, from what I can see from the code, the main change is the removal of the ping and validation of the connection (done in CheckoutConnection) from CheckoutConnection. These checks are now being done in a different function which is called at a point where the the connection pool is not locked.
From first looks it seems that this is an excellent and very simple change that could indeed boost the requests per second to the server since now if ten requests come at the same time they will all be processed in "parallel". Using the old method, each request for a new connection has to wait for all the rest to finish pinging etc.

In addition to this I would also comment that the actual ping is not required in most cases, since it does not help much. If the connection is down an error will be generated when the actual command send to server fails, therefore why send two requests for the server. I would argue that we can remove the ping, but it would actually be better if a background process does the pinging on idle connections. 

Anyway since we, and as I understand most serious users of the driver, are all doing changes like this one and the one that David Dindrop did for issue #40684, I think someone should take a look at these changes, test and maybe merge some of the changes to the driver. 

Thank you,
I would really appreciate any comments on this.

Christos, you are most welcome to comment! Please do not hesitate.

For the most part of your post, you are right. The only thing I would personally object to is the fact that, imho, POOLING implementer (be it IIS or framework) should make sure it's not dealing out stale connections from the pool leaving the driver free of such things... Otherwise, why would I need framework at all.

Now, this is just a quick personal opinion meaningless in context of c/NET.

From what I hear, Bogdan is trying to catalog code contributions and take a first code review. I also understand he's having some troubles incorporating fixes as writing them on standalone box is completely different story than trying to generate proper diff file for merging into development trees.

Tonci,
that's great news that you are trying to merge the community contributions. I am sure if we all cooperate we can push MySql and the related technologies even further.

I assume that david will be reading this and can provide you with a proper SVN diff file to help you do the merging. 

As far as your comments are concerned I am not sure what your mean by this:
"The only thing I would personally object to
is the fact that, imho, POOLING implementer (be it IIS or framework) should make sure it's
not dealing out stale connections from the pool leaving the driver free of such things...
Otherwise, why would I need framework at all."

Is this related to the pinging? If it is then, my point is twofold:
1) At any point in time the connection or the server it self can stop working for some reason, therefore even if the connector pings and hands out a nice and clean conenction, by the time the connection is used, or during its used the connection might be destroyed and and exception would be thrown anyway.
2) If the application is actively using the connections, then for 99+% of the time the connection will be fine. Why ping 100% of the times and waste resources, for that extreme case that the connection is lost?

Finally if the ping is disabled before each connection acquisition, the best way to handle this would be to periodically ping the idle connections in a background thread and remove the ones that expired, are not responding etc.

Christos, as much as I like to read your analysis (and learn from them) other reporters and my boss will freak out :)

I am not sure what Bogdan is trying to do right now but accepting community patches always require a lot of work.

As for my personal objection, I just meant that framework (in this case ass all depend on it, be it IIS or c/NET) should find a way to deal with stale connections, even if thrown from driver and thus eliminate all unnecessary code from driver, PING included.

I'll make sure this conversation reaches Reggie too.

Thanks for your interest and help.

Well since I was interested to actually see what some of the recent recommendations about the connection pooling can achieve, I put together a rather crude test.
The test consisted of taking a few threads (50) and either calling a stored procedure to insert a simple record in a table or just doing a simple select on the table.

The versions that I test are the following:
1) The latest trunk code
2) The latest trunk + patch in this bug report
3) The latest trunk + patch here + my own patch to not send a ping on Checkout
4) The latest trunk + my own patch to not send a ping on Checkout

The results were mostly as I expected, basically all three patched version performed similarly for different reasons, and they were consistently 33% faster i.e. instead of 9 seconds to complete the test it took 6 seconds, which I believe is considerable.

All tests 1-4 were run with with a 50/50 pool and a 10/10 pool size. The test consisted of calling the insert procedure 10000 times. Each test was run 3 times and the average time in milliseconds was recorded.

Results:
(1) no lock opt + ping
pool 10, 10
9580
pool 50, 50
11417

(2) lock opt. + ping
pool 10, 10
6699
pool 50,50
8500

(3) lock opt. + no ping
pool 10, 10
6200
pool 50,50
8096

(4) no lock opt + no ping
pool 10, 10
6347
pool 50, 50
8500
Note:
The simple select test runs are not shown but the results are even more dramatic. In some times the optimized tests completed in half the time.

Conclusion:
My conclusion is that the results are definetely better with either optimization, but the lock optimization will propably perform better on a heavily used connection pool with fast connection between client/server.
The no ping optimization will perform better on slower networks and the performance will not much difference if the pool is heavily used or not.

My recommendation is that both optimizations should be implemented, and in addition if the ping is disabled then it would be nice if a background worker can test ping the idle connections.

This is the patched version of the driver, with the lock opt. and a parameter to control if ping should be send on checkout

Attachment: PingEnhancements.patch (text/plain), 5.04 KiB.

Christos, I believe this is the same problem as in Bug#40036. See my post from [27 Mar 17:33] and tell me what you think please.

Setting this as duplicate of Bug#40036. Continue discussion there.