Optimizing DELETE Query on MySQL MEMORY Table

Question

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. However, this session table is showing up in the slow query log when we trim old records:

# Time: 120719 10:05:11
# User@Host: xxx[xxx] @  [10.x.x.x]
# Thread_id: 369051896  Schema: forumdb  Last_errno: 0  Killed: 0
# Query_time: 8.352811  Lock_time: 0.000028  Rows_sent: 0  Rows_examined: 19635  Rows_affected: 19635  Rows_read: 0
# Bytes_sent: 13  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1342710311;
DELETE FROM session
    WHERE lastactivity < 1342709401;

I've confirmed there is a BTREE index on the lastactivity table:

mysql> SHOW INDEX FROM session FROM forumdb;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| session |          0 | PRIMARY      |            1 | sessionhash  | NULL      |       78941 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid       |            1 | userid       | NULL      |       26313 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | idhash       |            1 | idhash       | NULL      |        8771 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            2 | lastactivity | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            2 | host         | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | lastactivity |            1 | lastactivity | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

I'm curious why the delete query is taking so long with so few records.

It should be noted that this table is utilized very heavily with so many users online at the same time. We have some heavy hardware sitting behind it.

Any ideas on what I can do to speed up this process? It appears to be locking the table so that I see high load across my cluster when this delete function is taking place.

Query cache is turned off (we use memcache). Here is relevant portions of my.cnf:

table_open_cache=8242
table_definition_cache=600
open_files_limit=65535
binlog_cache_size=6M
sort_buffer_size=8M
key_buffer_size=5G
myisam_sort_buffer_size=256M
join_buffer_size=3M
thread_cache_size=1000
thread_concurrency=16
ft_min_word_len=3
tmp_table_size=512M
max_allowed_packet=128M
max_heap_table_size=512M
read_rnd_buffer_size=1M
skip-external-locking

query_cache_limit=1M
query_cache_size= 32M
query_cache_type = 0

Finally, here is some more info on the table:

FIXED:

After changing table over to InnoDB (at 17:15 in the chart below) I am seeing much better performance and no more DELETE in the slowlogs for this table. The whole cluster is seeing better performance since making this change. Thank you.

Answer 1

I would probably convert your Memory table to InnoDB

ALTER TABLE session ENGINE=InnoDB

for your situation. Memory tables shine for low writes, many-reads that you don't care if you lose the data (loading configuration values, for instance). But when you start writing a lot, the table locks kill you if you have many connections accessing it:

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions. [src]

Tracking of session data may seem like a good candidate because of not caring of the data-loss, but I think you might gain significant benefit of removing table locks by switching to InnoDB in this case.

Answer 2

Memory tables require allocation of heap memory and they reuse that memory for deleted rows.

"Deleted rows are put in a linked list and are reused when you insert new data into the table." - http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

My best guess is that with 19k rows, the 8 seconds is being spent managing the linked list data structure behind the scenes that is being handed the deleted rows.

If you remember from freshman comp sci, a list insertion requires the next pointer of the last element to be updated to point to the new element and the tail pointer of the list to be updated to the new element, as well as the list count. I can imagine that 19k x those takes some cycles, plus removing the data and swapping the memory.

If you delete 19 rows is it faster by a factor of 1000 times? This could be explained as an (O)n linear cost.

Answer 3

Maybe you can do a temp table bait-and-switch

ALTER TABLE session RENAME sessionold;
CREATE TABLE sessionnew LIKE sessionold;
INSERT INTO sessionnew SELECT * FROM sessionold WHERE lastactivity >= 1342709401;
ALTER TABLE sessionnew RENAME session;
DROP TABLE sessionold;

This is a faster DELETE because

• this takes the session table away from everyone to perform this DELETE. Since MEMORY tables use full table locks like MyISAM, a heavily used MEMORY table would be cumbersome to do any DML because of the constant disk I/O (each access of a MEMORY table must always read the .frm).
• with the session table isolated from users, you can copy everything for lastactivity >= 1342709401 with any I/O against it.
• the table becomes available again only with records to be kept

Give it a Try !!!

UPDATE 2012-07-19 12:38 EDT

Here is a better alternative:

CREATE TABLE sessionnew LIKE session;
ALTER TABLE session RENAME sessionold;
ALTER TABLE sessionnew RENAME session;
INSERT INTO session SELECT * FROM sessionold WHERE lastactivity >= 1342709401;
DROP TABLE sessionold;

This should work even faster because the first three(3) lines will empty out the session table immediately. Then, whatever rows have lastactivity >= 1342709401 from the old session table is brought over. The old session table is then dropped.