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

Can I use latin1 to store utf8 data?

I've table contains text column and its charset is latin1, and i can store Arabic text ( and non English character) in this column and retrieve it, i don't know how is it?

So how is that? and why I need utf8?

CREATE TABLE `post` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL DEFAULT '0',
`parentid` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`pagetext` mediumtext NOT NULL,
`allowsmilie` smallint(6) NOT NULL DEFAULT '0',
`showsignature` smallint(6) NOT NULL DEFAULT '0',
`ipaddress` varchar(15) NOT NULL DEFAULT '',
`iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
`visible` smallint(6) NOT NULL DEFAULT '0',
`attach` smallint(5) unsigned NOT NULL DEFAULT '0',
`importthreadid` bigint(20) NOT NULL DEFAULT '0',
`importpostid` bigint(20) NOT NULL DEFAULT '0',
`infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
`reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`postid`),
KEY `userid` (`userid`),
KEY `threadid` (`threadid`,`userid`),
KEY `datline_idx` (`dateline`),
KEY `threadid_date` (`threadid`,`dateline`),
FULLTEXT KEY `title` (`title`,`pagetext`)
) ENGINE=MyISAM AUTO_INCREMENT=32451742 DEFAULT CHARSET=latin1

A Brief Introduction to MySQL Performance Tuning

Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.

Server Variables

For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.

For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.

Other variables that are worth investigating for performance tuning are:

query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.

innodb_log_file_size - Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.

sort_buffer_size - Another one that shouldn’t be set too large. Peter Zaitsev did some testing a while back showing that increasing sort_buffer_size can in fact reduce the speed of the query.

Server Hardware

There are a few solid recommendations for improving the performance of MySQL by upgrading your hardware:

• Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM. At this point in time, InnoDB does have issues scaling past 8 cores, so you don’t need to go out of your way to have lots of processors.
• Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.
• If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can seperate your data files, OS files and log files onto different physical disks.

Query Tuning

Finally, though probably the most important, we look at tuning queries. In particular, we make sure that they’re using indexes, and they’re running quickly. To do so, turn on the Slow Query Log for a day, with log_queries_not_using_indexes enabled as well. Run the resulting log through mysqldumpslow, which will produce a summary of the log. This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.