Update to session table slow

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up regularly in the slow log.

The Joomla session table definition is

CREATE TABLE IF NOT EXISTS `jos_session` (
      `username` varchar(150) DEFAULT '',
      `time` varchar(14) DEFAULT '',
      `session_id` varchar(200) NOT NULL DEFAULT '0',
      `guest` tinyint(4) DEFAULT '1',
      `userid` int(11) DEFAULT '0',
      `usertype` varchar(50) DEFAULT '',
      `gid` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `client_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `data` longtext,
      PRIMARY KEY (`session_id`(64)),
      KEY `whosonline` (`guest`,`usertype`),
      KEY `userid` (`userid`),
      KEY `time` (`time`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have tried changing the storage engine to InnoDB with no effect, I've tried optimize table and also removed all index, but the primary one. There are records in this table with storage space

Data    12,352.0    KiB
Index   144.0   KiB
Total   12,496.0    KiB

My Server configuration

```
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
back_log 50
basedir /usr
big_tables OFF
binlog_cache_size 32768
binlog_direct_non_transactional_uOFF
binlog_format STATEMENT
binlog_stmt_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results

First things
Set query_cache_type=0 and query_cache_size=0 in my.cnf For multi-core systems the cache will create contention issues.

Important Questions
How much free RAM is showing on your server (use top command)?
What is the particular statement that is showing up in the slow query log?

If you are going to keep the table database engine as InnoDB which I recommend
Make innodb_log_file_size = innodb_buffer_pool_size/2 This will reduce IOs. Be sure to follow instructions found on here: http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html in the place starting with "If you want to change the ".

If you want to keep it as MyISAM
This table will get locked for every update. Change the varchars and longtext to chars so it will be a fixed-length table (see http://dev.mysql.com/doc/refman/5.5/en/myisam-table-formats.html

Change the primary key to use a hash index:

alter table jos_session drop primary key;
alter table jos_session add primary key (`session_id`(64)) using hash;

On query cache
Long story on query cache; if you have the same select query running over and over then yeah it can help, but there's lots of overhead to have it run. General rule, turn it off, and then turn it on if you need to. (see http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/)

Speeding up UPDATE *
Thanks for the slow query log statement. The biggest thing that slows updates is updating the indexes. Since you've tried dropping the indexes the next step is to convert the table to InnoDB Engine. MyISAM is not very good with concurrent updates. If there still is no satisfaction, the next step to see if you cannot partition the table across different devices, but make sure the extra administrative burden is worth the benefit. Do you have any extra hard disk devices available to the server not being used?

Joomla session table
Upon investigation, this table gets deletes which means it will have holes. If you still want to stick with MyISAM, try setting concurrent_insert=2. You can do this without requiring a maintenance window. (more info: http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html). INSERTS get higher priority over updates so speeding those up may speed up your UPDATE statement.

Memory and InnoDB
If you can set the innodb_buffer_pool_size to the total size of the database + indexes of all of the InnoDB tables, then updates will happen in RAM first instead of disk. This is the ideal for perormance. Since I see you are using around 2.7GB on the server, there's probably other apps contending for disk--all the more to go to innodb with the innodb buffer settings.

StackExchange Database Administrators Q#35202, answer score: 5