1

mysql is running fine on the website and through some profiling (via codeigniter), I noticed some queries running EXTREMELY slow, as in taking more than 30 seconds. 95% of the time it is the sessions table, and in some cases, it slowly SELECTS from another table.

Slow table is of type innoDB.

I'm unsure what the proper process is to fix it.

I've tried repairing the table, but that is as far as I've got. I dont really know what to do next.

Any ideas?

@Nico @Mark Byers

SELECT *
FROM `ast_sessions`
WHERE `session_id` = '636519144bc6194898fd5a1f9e5fbc1d'
AND `user_agent` = 'Mozilla/5.0 (Windows NT 6.1; WOW64)'

Create Table:

CREATE TABLE `ast_sessions` (
  `session_id` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '0',
  `ip_address` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '0',
  `user_agent` varchar(150) COLLATE utf8_bin NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT '0',
  `user_data` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`session_id`,`user_agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

EXPLAIN SELECT:

+----+-------------+-------+------+---------------+------+---------+------+------+-----            ------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows |     Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----    ------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |          Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (1 min 10.41 sec)
ssj1980
  • 391
  • 2
  • 7
  • 1
    Any examples of the slow queries? Maybe the problemn is in the query and not on the table – Nico May 28 '12 at 18:12
  • 1
    Post the SQL for the query, the result of SHOW CREATE TABLE for the table, and the result of EXPLAIN SELECT for your query. Otherwise all we can do is guess. – Mark Byers May 28 '12 at 18:13
  • 1
    It looks like the select is slow because it waits for other locks (otherwise, the lookup / impossible where clause should be quick). Are you actually locking records in your sessions table? Or is it perhaps terribly write-heavy? (a `SHOW FULL PROCESSLIST` could show a little bit of what waits for what). – Wrikken May 28 '12 at 18:48
  • @Wrikken I dont know how to check for locking. It's a sessions table with about 10 active users on the site right now. If no session, it creates a session, else, it updates the required session. I've noticed the inserts to the sessions table are slow too. – ssj1980 May 28 '12 at 18:53
  • 1
    I found these (there's no direct solution, but understanding may lead to one): [Explain extended...](http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-tell-you-all-kinds-of-interesting-things/), [MySQL bug Bug #64197](http://bugs.mysql.com/bug.php?id=64197), [How to run a explain query when a binary field...](http://stackoverflow.com/questions/6324140/how-to-run-a-explain-query-when-a-binary-field-is-in-the-where-clause), [What is the best collation...](http://stackoverflow.com/questions/367711/what-is-the-best-collation-to-use-for-mysql-with-php). – Taz May 28 '12 at 19:22
  • 1
    Hm, you could of course either step through the code, or log all queries and examine those (looking for `LOCK` & `FOR UPDATE` clauses...). Also, you might want to update CodeIgniter to the latest version, as I for instance see this bugreport: https://github.com/EllisLab/CodeIgniter/issues/215 – Wrikken May 28 '12 at 20:00

0 Answers0