Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

Table info

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.

CPU Results

share|improve this question
    
What are your settings for query_cache_size and query_cache_type? (I can't remember whether query cache invalidation is included within the query time in the slow query log but setting the query cache too large has bitten me before.) –  Ladadadada Jul 19 '12 at 15:26
    
query_cache_limit=1M query_cache_size= 32M query_cache_type = 0 (It is turned off) –  Dave Drager Jul 19 '12 at 15:29
    
Which version of MySQL? –  Derek Downey Jul 19 '12 at 16:12
    
I love fast DELETE questions like this. +1 !!! –  RolandoMySQLDBA Jul 19 '12 at 16:41
add comment

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
Seems inefficient for larg(er) tables. Thank you for the explanation. –  Dave Drager Jul 20 '12 at 13:41
add comment

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.

share|improve this answer
    
I would think for the time period that these commands are running, it would make the site unusable since the sessions table wouldn't be available. Sort of the same issue we are facing right now. –  Dave Drager Jul 19 '12 at 15:43
    
How often is the DELETE to be executed ??? –  RolandoMySQLDBA Jul 19 '12 at 16:30
    
It is run every 5 minutes, so unfortunately this does not work for us. But thank you for your detailed answer and definitely an alternative if you need to use a memory table (for example, if your disk i/o is slow - in our case we are on a RAID SSD array). –  Dave Drager Jul 20 '12 at 13:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.