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.
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.
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? D). 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.