Help -- ludicrously slow operations on table with 2.6mln records, fully indexed (MyISAM, MySQL 5.0.51, Linux)
Hi guys,
I suspect i am missing something pretty obvious, but this problem is become really serious on my end, and I can't figure out what I did wrong. What happens is that even fairly simple operations on this table, with 2.6mln entries, take minutes. For example I am currently trying to delete 123k records from it, and it's been running for half an hour.
System: 8GB RAM, 64-bit
OS: Ubuntu 8.04 (whcih is why i can't upgrade to the latest mySQl server)
Relevant mySQL config:
key_buffer_size = 2048M
max_allowed_packet = 16M
thread_stack = 512K
thread_cache_size = 32
sort_buffer_size = 256M
record_buffer = 4M
myisam_sort_buffer_size = 128M
Relevant table info:
mysql> describe prospect_lists_prospects;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| id | varchar(36) | NO | PRI | | |
| prospect_list_id | varchar(36) | YES | MUL | NULL | |
| related_id | varchar(36) | YES | MUL | NULL | |
| related_type | varchar(25) | YES | MUL | NULL | |
| date_modified | datetime | YES | | NULL | |
| deleted | tinyint(1) | YES | MUL | 0 | |
+------------------+-------------+------+-----+---------+-------+
mysql> show indexes from prospect_lists_prospects;
+--------------------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| prospect_lists_prospects | 0 | PRIMARY | 1 | id | A | 2651054 | NULL | NULL | | BTREE | |
| prospect_lists_prospects | 1 | idx_plp_pro_id | 1 | prospect_list_id | A | 245 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | idx_plp_rel_id | 1 | related_id | A | 1325527 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | idx_plp_rel_id | 2 | related_type | A | 1325527 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | idx_plp_rel_id | 3 | prospect_list_id | A | 2651054 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | related_id | 1 | related_id | A | 1325527 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | related_type | 1 | related_type | A | 3 | NULL | NULL | YES | BTREE | |
| prospect_lists_prospects | 1 | deleted | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | |
+--------------------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
So the following simple query took 6 minutes:
> select count(p.id) from prospect_lists as l join prospect_lists_prospects as p on p.prospect_list_id=l.id where l.deleted=1;
note that prospect_lists.id is the primary key, so it's indexed as well.
And now i am trying to run a deletion on the same criteria, and it's taking half an hour by now, and still going (the "show processlist" shows the status of "deleting from reference tables")!
> delete p from prospect_lists as l join prospect_lists_prospects as p on p.prospect_list_id=l.id where l.deleted=1;
I am totally beside myself. I think I got the engine optimization set up properly, and the table is fully indexed, and the hardware is decent, but for a table of measly 2.6mln records, the performance is insanely bad! Does anyone have an idea of what I might be missing?
Edited 1 time(s). Last edit at 01/05/2012 09:16AM by Victor Danilcehnko.