MySQL Bugs: #51737: error 2003 under heavy load

Bug #51737	error 2003 under heavy load
Submitted:	4 Mar 2010 21:18	Modified:	18 Apr 2010 17:50
Reporter:	tom egling	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.75	OS:	Linux (Ubuntu 9.04)
Assigned to:		CPU Architecture:	Any

Description:
Hi, I am using mysql to log realtime network data amounting in about 150 queries per second using the python mysqldb connector are suggested by the python pep249. A soon as I increase the server load ( > than about 160-180 queries per second, mostly selects and inserts of about 50-100 bytes) I get error 2003, cannot connect. The mysql server and the python login software are on the same system so I doubt if it is a network issue. There are approximately 30 threads connecting, writing (one thread for each ip address being monitored) and disconnecting after the write. I have enabled caching and set the query cache level to 64mb, I also have a large key memory for the select queries. I am running this system on a dual core 2.2ghz system with a gig of memory. Although this problem can be resolved by increasing the performance of the system, I generally disagree with solving software problems ( very possibly mine ) with hardware. I have read in many places that mysql performs well under heavy load and would assume that it would be capable of handing this load level given the hardware. I have used InnoDb tables as by my understanding the allow row level locking which should circumvent concurrent inserts and selects to the same table. Any insight would be very helpful

Best Regards,

Tom Egling

How to repeat:
If required, I can supply python code samples and sample databases

Thank you for the report.

Please provide output of show variables like 'max_con%'; and show global status like '%con%';

mysql> show variables like "%max_con%";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 500   |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like "%con%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Com_show_contributors    | 0     |
| Connections              | 12755 |
| Max_used_connections     | 16    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 2     |
+--------------------------+-------+
8 rows in set (0.00 sec)

mysql>

Thank you for the feedback.

Connection settings look good. Could you please turn option log_warnings=2 in the configuration file, then repeat the problem and send us error log, configuration file and output of `top` and `free` commands in time when problem occurs?

Hi Sveta ,

After much frustration, I reinstalled Ubuntu, mysql and python and have been unable to reproduce the problem after a week of continuous testing.

Any Ideas ?

Thanks

Tom

Thank you for the feedback.

Please turn option log_warnings=2 in the configuration file, then repeat the problem and send us error log, configuration file and output of `top` and `free` commands in time when problem occurs.

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".