Select query too slow from merge table of 1 billion records
I am having performance issues with search queries on a MERGE MyISAM table with more than 1 billion records.
The merge table is based on tables that have a primary key and indexes on 6 other fields.
The performance issue arises when the WHERE condition in SELECT query is based on two or more keys other than the primary key. The query takes 13-17 minutes to fetch 10 records and the 'SHOW PROCESSLIST' command shows that majority of thread time was spent in 'Sending Data' state.
However, the same query has no performance issues when the WHERE condition is based on only one key. In that case, the results are send back in 1 second.
Following are the other details.
MySQL/OS/Hardware details
We are using MySQL 5.0.45 on Solaris 10 x86 box with 8GB RAM.
The key_buffer_size in set to 4GB.
SCHEMA DETAILS
CREATE TABLE `tb_wir_msg` (
`c_msg_id` bigint(20) unsigned NOT NULL,
`c_start_datetime` datetime NOT NULL,
`c_start_date` date default NULL,
`c_start_hour` tinyint(3) unsigned default NULL,
`c_start_min` tinyint(3) unsigned default NULL,
`c_start_sec` tinyint(3) unsigned default NULL,
`c_end_datetime` datetime NOT NULL,
`c_end_date` date default NULL,
`c_end_hour` tinyint(3) unsigned default NULL,
`c_end_min` tinyint(3) unsigned default NULL,
`c_end_sec` tinyint(3) unsigned default NULL,
`c_terminal_ip` varchar(15) NOT NULL,
`c_apn_name_fk` smallint(5) unsigned NOT NULL,
`c_msisdn_fk` int(10) unsigned NOT NULL,
`c_method_id_fk` tinyint(4) NOT NULL,
`c_url_name` varchar(2500) default NULL,
`c_content_type_fk` smallint(5) unsigned NOT NULL,
`c_http_status_id_fk` smallint(5) unsigned default NULL,
`c_req_header_size` int(11) default NULL,
`c_req_content_size` int(11) default NULL,
`c_rsp_header_size` int(11) default NULL,
`c_rsp_content_size` int(11) default NULL,
`c_rsp_status_id` smallint(6) default NULL,
`c_terminal_type_fk` smallint(5) unsigned NOT NULL,
`c_port` smallint(5) unsigned default NULL,
`c_node_id` tinyint(3) unsigned default NULL,
`c_batch_id` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`c_msg_id`),
KEY `c_msisdn_fk` (`c_msisdn_fk`),
KEY `c_apn_name_fk` (`c_apn_name_fk`),
KEY `c_terminal_type_fk` (`c_terminal_type_fk`),
KEY `c_terminal_ip` (`c_terminal_ip`),
KEY `c_start_datetime` (`c_start_datetime`),
KEY `c_end_datetime` (`c_end_datetime`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`tb_wir_msg_1`,`tb_wir_msg_2`,`tb_wir_msg_3`,`tb_wir_msg_4`,`tb_wir_msg_5`,`tb_wir_msg_6`,`tb_wir_msg_7`,`tb_wir_msg_8`,`tb_wir_msg_9`,`tb_wir_msg_10`,`tb_wir_msg_11`);
Total records
mysql> select count(*) from tb_wir_msg;
+------------+
| count(*) |
+------------+
| 1184095732 |
+------------+
1 row in set (0.00 sec)
Query with performance issue:
SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_apn_name_fk = 2 AND t1.c_end_datetime >= '2009-12-31 16:0:0' AND t1.c_end_datetime <= '2010-1-31 15:59:59' LIMIT 10;
+-------------------+
| c_msg_id |
+-------------------+
| 93242317550668786 |
| 93242317449976529 |
| 93242317349340941 |
| 93242317315786351 |
| 93242317299009008 |
| 93242317299008983 |
| 93242317282231671 |
| 93242317299009000 |
| 93242317215122341 |
| 93242317148013263 |
+-------------------+
10 rows in set (13 min 12.16 sec)
The EXPLAIN statement
mysql> explain SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_apn_name_fk = 2 AND t1.c_end_datetime >= '2009-12-31 16:0:0' AND t1.c_end_datetime <= '2010-1-31 15:59:59' LIMIT 10;
+----+-------------+-------+------+------------------------------+---------------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------+---------------+---------+-------+----------+-------------+
| 1 | SIMPLE | t1 | ref | c_apn_name_fk,c_end_datetime | c_apn_name_fk | 2 | const | 11697897 | Using where |
+----+-------------+-------+------+------------------------------+---------------+---------+-------+----------+-------------+
1 row in set (0.00 sec)
Queries that return back very fast (single WHERE condition)
mysql> SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_apn_name_fk = 2 LIMIT 10;
+--------------------+
| c_msg_id |
+--------------------+
| 597572799863479084 |
| 597572799863485655 |
| 597572799863458077 |
| 597572799695711883 |
| 597572799678935234 |
| 597572799628601796 |
| 597572799494385109 |
| 597572799410498652 |
| 597572799209169986 |
| 597572799142062604 |
+--------------------+
10 rows in set (1.10 sec)
mysql> explain SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_apn_name_fk = 2 LIMIT 10;
+----+-------------+-------+------+---------------+---------------+---------+-------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+----------+-------+
| 1 | SIMPLE | t1 | ref | c_apn_name_fk | c_apn_name_fk | 2 | const | 11697897 | |
+----+-------------+-------+------+---------------+---------------+---------+-------+----------+-------+
1 row in set (0.00 sec)
mysql> SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_end_datetime >= '2009-12-31 16:0:0' AND t1.c_end_datetime <= '2010-1-31 15:59:59' LIMIT 10;
+-------------------+
| c_msg_id |
+-------------------+
| 93242305487786647 |
| 93242305487786623 |
| 93242305487786574 |
| 93242305487786676 |
| 93242305487786649 |
| 93242305487786461 |
| 93242305487786576 |
| 93242305487786594 |
| 93242305487786642 |
| 93242305487786628 |
+-------------------+
10 rows in set (0.49 sec)
mysql> explain SELECT t1.c_msg_id FROM tb_wir_msg t1 WHERE t1.c_end_datetime >= '2009-12-31 16:0:0' AND t1.c_end_datetime <= '2010-1-31 15:59:59' LIMIT 10;
+----+-------------+-------+-------+----------------+----------------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+-----------+-------------+
| 1 | SIMPLE | t1 | range | c_end_datetime | c_end_datetime | 8 | NULL | 513465571 | Using where |
+----+-------------+-------+-------+----------------+----------------+---------+------+-----------+-------------+
1 row in set (0.01 sec)
I have also checked that the query that has performance issue returns very fast if it is used on one of the partitioned tables that have 1/10th of data and whose index size is 11G.
Can anyone please explain the cause of this problem? Is it something related to key_buffer_size?
Edited 1 time(s). Last edit at 02/23/2010 08:22PM by Manik Dhawan.