I'm afraid you might have run into one of the several gotchas of MySQL. See this bug report.
If I have understood everything right, the MySQL command line client causes mysqld to use interactive_timeout
instead of wait_timeout
.
What does the following query return to you?
SELECT @@global.wait_timeout, @@session.wait_timeout;
You need to set the MyISAM Key Cache and the InnoDB Buffer Pool to accommodate the data you have. I published two queries in the DBA StackExchange to compute the recommended size for noth of those caches.
For MyISAM, since only index pages are cached, this query will recommend the size of key_buffer_size based on the sum of your .MYI files and cap it at 4G if it exceeds 4G:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.4999),
SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_key_buffer_size
FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM
(SELECT SUM(index_length) KBS1 FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA
) A,(SELECT 2 PowerOfTwo) B;
For InnoDB, since data and index pages are cached, this query will recommend the size of innodb_buffer_pool_size based on the sum of your data and index pages:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.49999),
SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tablesM
WHERE engine='InnoDB') A,
(SELECT 2 PowerOfTwo) B;
According to your display of mysqltuner.pl, you have about 24GB of RAM, 530MB id InnoDB, 7.2GB of MyISAM indexes. No matter what the recommendations come up as, please just use common sense by setting innodb_buffer_pool_size to a number about 530MB but under 1GB. Since MyISAM doesn't really take advantage of memory in certain instances, you could leave the key_buffer_size to 1024M because mysqltuner.pl says 90% of RAM is used by the DB Connections. The OS needs RAM too.
I would lower these:
[mysqld]
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
Restart mysql afterwards and run mysqltuner.pl. Your goal here is to get the Maximum possible memory usage under 80% so the OS has breathing room for memory.
CAVEAT : Please excuse the Column called 'PowerOfTwo'. It should really be called PowerOf1024. The purpose of PowerOfTwo is to display recommended answers in GB,MB,KB
- (SELECT 0 PowerOfTwo) displays the value in Bytes
- (SELECT 1 PowerOfTwo) displays the value in KiloBytes
- (SELECT 2 PowerOfTwo) displays the value in MegaBytes
- (SELECT 3 PowerOfTwo) displays the value in GigaBytes
Best Answer
wait_timeout is the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection. interactive_timeout is the same, but for interactive sessions (mysql shell)
Setting a value too low may cause connections to drop unexpectedly, specifically if you are using persistent connections in your web application.
Setting a value too high may cause stale connections to remain open, preventing new access to the database.
IMO, for wait_timeout, you would want this value to be as low as possible without affecting availability and performance. You could start with 10 seconds and gradually increase it if you are seeing degraded performance.
For interactive_timeout, changing this value won't really increase or decrease performance of your application.