Hi Everybody,
I tried to tune an application which uses MySQL to store sessions for multible webservers.
The session table is small about 100 bytes per row.
The session table has lost of insert/deletes and updates.
And updates are mostly done to update "last-page click timestamps".
This setup creates of course a high IO write load for the MySQL server for data which is realtive unimportant and could be kept volatile.
I though "thats easy to fix" and converted the table from type InnoDB to type Memory.
This did remove the disk IO of the table. Nice!
But here comes the gotcha.
The MEMORY table showed up to be significant slower than InnoDB.
I've recorded live SQL-access statements to the table (some million lines) and played these statements several times in parallel (16 threads) against the table.
The Innodb needed 51 secs for this testcase.
But the Memory table needs 130 seconds.
There is 1 key on the table (a unique key on a session md5)
I tried both types of Memory key-types (Hash and INDEX)
The timing for the BTREE is slightly faster 125 seconds.
But in both cases the memory table is significant slower than Innodb.
Isn't this strange?
Everybody would expect that a memory table would have so much less overhead that I should be 10 times faster than Innodb.
But in real live the table is 2 times slower than a disk-based table.
Any ideas?
Cheers
Gunnar von Boehn
I tried to tune an application which uses MySQL to store sessions for multible webservers.
The session table is small about 100 bytes per row.
The session table has lost of insert/deletes and updates.
And updates are mostly done to update "last-page click timestamps".
This setup creates of course a high IO write load for the MySQL server for data which is realtive unimportant and could be kept volatile.
I though "thats easy to fix" and converted the table from type InnoDB to type Memory.
This did remove the disk IO of the table. Nice!
But here comes the gotcha.
The MEMORY table showed up to be significant slower than InnoDB.
I've recorded live SQL-access statements to the table (some million lines) and played these statements several times in parallel (16 threads) against the table.
The Innodb needed 51 secs for this testcase.
But the Memory table needs 130 seconds.
There is 1 key on the table (a unique key on a session md5)
I tried both types of Memory key-types (Hash and INDEX)
The timing for the BTREE is slightly faster 125 seconds.
But in both cases the memory table is significant slower than Innodb.
Isn't this strange?
Everybody would expect that a memory table would have so much less overhead that I should be 10 times faster than Innodb.
But in real live the table is 2 times slower than a disk-based table.
Any ideas?
Cheers
Gunnar von Boehn
Comment