vBulletin session table and Hash index in MEMORY Engine

In vBulletin Board System there is session table contains online user information and tracking, in which forum now, what the current URL now, So it is have huge concurrent update statement, By default this table is MEMORY engine because its data not important in case MySQL restart.

I notice early loaded in MySQL and server, So I do full tuning for the server then analyze slow query using mysqlsla and notice this query in the top:

SELECT user.username, (user.options & 512) AS invisible, user.usergroupid,
   session.userid, session.inforum, session.lastactivity, session.badlocation,
   IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
  FROM session AS session force index(lastact_user)
  LEFT JOIN user AS user ON(user.userid = session.userid)
  WHERE session.lastactivity > 1269376225
  ORDER BY lastactivity DESC

and this is create table statement

mysql> show create table session\G
*************************** 1. row ***************************
       Table: session
Create Table: CREATE TABLE `session` (
  `sessionhash` char(32) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `host` char(15) NOT NULL DEFAULT '',
  `idhash` char(32) NOT NULL DEFAULT '',
  `lastactivity` int(10) unsigned NOT NULL DEFAULT '0',
  `location` char(255) NOT NULL DEFAULT '',
  `useragent` char(100) NOT NULL DEFAULT '',
  `styleid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `languageid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `loggedin` smallint(5) unsigned NOT NULL DEFAULT '0',
  `inforum` smallint(5) unsigned NOT NULL DEFAULT '0',
  `inthread` int(10) unsigned NOT NULL DEFAULT '0',
  `incalendar` smallint(5) unsigned NOT NULL DEFAULT '0',
  `badlocation` smallint(5) unsigned NOT NULL DEFAULT '0',
  `bypass` tinyint(4) NOT NULL DEFAULT '0',
  `profileupdate` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`sessionhash`),
  KEY `userid` (`userid`),
  KEY `lastact_user` (`lastactivity`,`userid`),
  KEY `idx_host_user` (`host`,`userid`),
  KEY `idhash_idx` (`idhash`),
  KEY `idx_lastactivity` (`lastactivity`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 PACK_KEYS=0

so for first impression this query use index for where and order by, so its optimal!!!!!

Unfortunately, we see it not using the index even when force it.

mysql> explain select * from session where lastactivity > 1269509561;
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys                 | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | session | ALL  | lastact_user,idx_lastactivity | NULL | NULL    | NULL | 3199 | Using where |
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+

mysql> explain select * from session force index (idx_lastactivity) where lastactivity > 1269509561;
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | session | ALL  | idx_lastactivity | NULL | NULL    | NULL | 3199 | Using where |
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+

Why?

MEMORY engine support B-tree and Hash indexes, but by default use Hash algorithm:
    *      They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values.
    *      The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
    *      MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
    *      Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

so the optimizer will not use this index.

when we alter index algorithm by:

alter table session drop index idx_lastactivity, add index `idx_lastactivity` (`lastactivity`) USING BTREE;

we notice:

mysql> explain select * from session where lastactivity > 1269509561;
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys                 | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | session | range | lastact_user,idx_lastactivity | idx_lastactivity | 4       | NULL | 1919 | Using where |
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+

mysql> explain select * from session force index (idx_lastactivity) where lastactivity > 1269509561;
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | session | range | idx_lastactivity | idx_lastactivity | 4       | NULL | 1919 | Using where |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+

So we should be careful when use MEMORY table with indexes

vBulletin, session table is InnoDB

In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.

So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)

innodb_data_home_dir = /dev/shm/mysql/ #this path in memory partition
innodb_log_group_home_dir = /dev/shm/mysql/
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
innodb_thread_concurrency = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

thanks

any comment?

UPDATE:


In this configuration when restart MySQL in error log will see:

090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662

So may be a bug with tmpfs and O_DIRECT!!!

see http://bugs.mysql.com/45671