 Intermittent Connection Failures and High aborted_connects [message #309] |
Mon, 30 October 2006 18:52  |
 tomp_gl
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
Hi,
I always seem to be having trouble with MySQL. For months i have gotten a lot of "Got timeout reading communication packets" and the aborted_connects value is very high. I can't get to the bottom of this problem because this error occurs immediately and intermittently, and i never have time to SHOW PROCESSLIST to see what is causing it, because it only occurs for a split second - this problem causes PHP mysql_connect() to fail immediately, and when this happens i die() the entire page so users see a white screen (which is better than a whole lot of empty boxes, i guess).
Other than this annoying problem, the performance is very good. On average i do 198 queries per second and the site runs well. I have a suspicion the problem is caused by a query locking out the rest of the queries. But i can never tell.
When i examine my slow query log, i see simple UPDATE queries taking 5 seconds. I have low_priority_updates=1 - is this the reason they appear in the slow query log?
When i examine slow SELECT statements, i get conflicting results. One SELECT query took 5 seconds in the slow query log but when i executed it myself it took 0.0004 seconds. When i alter the query slightly to make it unique (to avoid taking results from the query cache), it takes 0.0006 seconds.
Any ideas?
Thanks,
Tom
|
|
|
| Re: Intermittent Connection Failures and High aborted_connects [message #310 is a reply to message #309 ] |
Tue, 31 October 2006 07:54  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Which OS are you using ?
When connect fails immediately what error message are you getting - it should contain OS specific error code if it is connect timeout ot connection refused.
Now about your query - what kind of tables are you using ? For MyISAM you would see "lock time" in slow query log which corresponds to time spend waiting on lock.
Waiting on lock may well be the reason for query to take much longer time.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Intermittent Connection Failures and High aborted_connects [message #311 is a reply to message #310 ] |
Tue, 31 October 2006 18:04 |
tomp_gl
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
Peter,
Thanks for replying. I changed my wait_timeout value from 15 to 3600 yesterday for an unrelated reason. I also changed max_connections from 100 to 50 because i noticed i don't go anywhere near 100. Since doing that, i no longer see any communication error messages in my error log. In fact the last messages in the log relate to the boot sequence a day ago (which is nice to see for once... but maybe the log stopped working?  ). However, my users and i still get the white screens.
Is there a way to output the mysql processlist every second, for say 5 minutes and store it in a file? This would allow me to track down the query locking out the threads (if that is what it is).
My database has primarily MyISAM tables, but also InnoDB tables for the big, busy read/write tables. My belief is that there is a MyISAM table that needs to be converted to an InnoDB table somewhere in my application because of its table level locks causing problems for other threads. Maybe i should go exclusively to InnoDB for all tables?
In my slow query log, the queries are on mainly InnoDB tables. Here are some SELECT examples:
# Query_time: 2 Lock_time: 0 Rows_sent: 4 Rows_examined: 2427
# Query_time: 2 Lock_time: 0 Rows_sent: 4 Rows_examined: 13529
I know, these queries should be optimized. But i can't explain why they take 0.0004 seconds when i try testing them myself.
These SELECT statements are on busy InnoDB tables. They do a lot of reading and a little bit of writing. Sometimes i see SELECT queries on these InnoDB tables doing "Copying to tmp table" in the processlist.
And the slow UPDATEs look like this:
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
I am confused about the UPDATEs -- they are single row updates using primary keys. But i'm not too worried.
I'm using Centos 4 and MySQL 5.0.24-standard-log.
Thanks,
Tom
[Updated on: Tue, 31 October 2006 18:05]
|
|
|
| Re: Intermittent Connection Failures and High aborted_connects [message #312 is a reply to message #311 ] |
Tue, 31 October 2006 19:01 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
The disconnect error messages you saw in error log are probably because of low wait_timeout but these should not have cause any problems. You also mentioned you had connect failures - this is what should not be affected by wait timeout.
You can run mysqladmin processlist -i 300 > file.txt to get processlist every 5 minutes.
Regarding selects - they traverse plenty of rows which is probably the reason for the slowness.
Update is likely waiting on row level lock which is not seen in the slow query log.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Intermittent Connection Failures and High aborted_connects [message #319 is a reply to message #312 ] |
Thu, 02 November 2006 02:08 |
tomp_gl
Messages: 9
Registered: October 2006 |
Junior Member |
|
|
| Peter wrote on Tue, 31 October 2006 19:01 |
You also mentioned you had connect failures - this is what should not be affected by wait timeout.
|
Yes, exactly. That is why i always found the error message very confusing, because an immediate connection error incrementing aborted_connects shouldn't report as a timeout error in the log. My only idea is the error reports for a lack of a more accurate error description available to mysql.
Thanks for your help. I will try mysqladmin processlist -i 300 > file.txt and see what happens.
|
|
|
Members
Search
Help
Register
Login
Home
