I run a dedicated MySQL server (2 cores, 16GB RAM) serving 100-200 requests per second. It is getting sluggish during peak traffic and I have a hard time optimizing the server. So I'm looking for some ideas now that I have done lots of Innodb fine-tuning with the "TUNING PRIMER"
The query that now generates most slow queries is the following (see result from mysqldumpslow):
Count: 433 Time=3.40s (1470s) Lock=0.00s (0s) Rows=0.0 (0), UPDATE user_sessions SET tid='S' WHERE idsession='S'
I am very surprised to have so many long queries for such a simple query with no locking. Fyi, the table is InnoDB and has 14000 rows. It contains all active sessions on the site with approx 10 UPDATE and SELECT hits per second. Here is its structure:
CREATE TABLE `user_sessions` ( `personid` mediumint(9) NOT NULL DEFAULT '0', `ip` varchar(18) COLLATE utf8_unicode_ci NOT NULL, `idsession` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `datum` date NOT NULL DEFAULT '0000-00-00', `tid` time NOT NULL DEFAULT '00:00:00', `status` tinyint(4) NOT NULL DEFAULT '0', KEY `personid` (`personid`), KEY `idsession` (`idsession`), KEY `datum` (`datum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Here is what I get when I activate profiling on this query :
Status Duration starting 0.000049 checking permissions 0.000009 Opening tables 0.000028 System lock 0.000010 init 0.000079 Updating 0.030386 end 0.000007 Waiting for query cache lock 0.000005 end 0.002654 query end 0.000035 closing tables 0.000012 freeing items 0.000022 logging slow query 0.000005 cleaning up 0.000005