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

Any ideas?


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
  • Where is the primary key? Could you post the output of show create table user_sessions please? – user130370 Sep 17 '12 at 20:36
  • yes, just did it – Eric Sep 17 '12 at 20:40
  • I'll delete my answer not to mislead anyone (and for your question to remain as unanswered). Still it might be better if idsession is unique. – user130370 Sep 17 '12 at 21:02
  • I might have read that an index on varchars is slower than on a char. Have you executed "explain UPDATE user_sessions SET tid='S' WHERE idsession='S'" to find out if the query is really doing what you think? – Arlukin Sep 18 '12 at 22:59
  • Unfortunately there is no EXPLAIN for UPDATE queries – Eric Sep 19 '12 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.