MySQL Forums
Forum List  »  Performance

Help -- ludicrously slow operations on table with 2.6mln records, fully indexed (MyISAM, MySQL 5.0.51, Linux)
Posted by: Victor Danilcehnko
Date: January 05, 2012 09:16AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.