Hi All,
We run a app where a Java webapp uses a fully-ISAM MySQl backend, via Hibernate. Recently we found a case with a specific client having horrible issues with speed on a specific area of the app.
We eventually tracked it down to a few tables of the DB, and how the queries on these tables are horribly slow and produce way more diskIO than seems needed. We found that a few long-running selects seem to lock the tables, causing everything else to stall out.
Now i know your thingking "well yea that happens"... thing is... these queries where never longrunning until a few days ago. they never tool longer than say 5 secs... and there now up to over a few minutes, depending on server load. They have also not generated nearly enough DB size to actual create this issue overnight, so it seems to be a case of "the straw that broke the camels back" as one might say.
The server is one of many VM's all running the same configurations and the same set of resources:
Ubuntu server 10.04 x64
mysql Ver 14.14 Distrib 5.5.9, for linux2.6 (x86_64) using readline 5.1
2Gb ram (1.5Gb used by Java, rest is left to OS and SQL)
Heres the regular outputs for investigation. Note that the select below is NOT the query causing the headache, its a simple select that shows the performance of the tables. The actual query is in hibernate-speak (but i guess i can scratch it out if really needed). Also note that the first run of the query is slow, and any after that is instant, so caching is working.
mysql> select message_id from email_message em, message m where m.id=em.message_id and m.sent_on is null and m.publisher_id=2 order by m.created_on;
Empty set (57.94 sec)
mysql> select message_id from email_message em, message m where m.id=em.message_id and m.sent_on is null and m.publisher_id=2 order by m.created_on;
Empty set (0.00 sec)
mysql> show create table message\G
*************************** 1. row ***************************
Table: message
Create Table: CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` mediumtext,
`entry_folder_id` int(11) DEFAULT NULL,
`audit_message` varchar(3) DEFAULT NULL,
`local_system_origin` varchar(3) DEFAULT NULL,
`auto_communication` varchar(3) DEFAULT NULL,
`sent_on` datetime DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`read_receipt` varchar(3) DEFAULT NULL,
`time_spent` float DEFAULT NULL,
`time_spent_time_unit` int(11) DEFAULT NULL,
`pause_duration` float DEFAULT NULL,
`pause_duration_time_unit` int(11) DEFAULT NULL,
`expires_on` datetime DEFAULT NULL,
`archived` varchar(3) DEFAULT NULL,
`deleted` varchar(3) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`publisher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=218291 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table email_message \G
*************************** 1. row ***************************
Table: email_message
Create Table: CREATE TABLE `email_message` (
`message_id` int(11) NOT NULL,
`content_type` varchar(100) DEFAULT NULL,
`internet_id` varchar(255) DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`headers` text,
`generated_report` varchar(3) DEFAULT NULL,
`inbox_classification` bigint(20) DEFAULT NULL,
`email_folder_id` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
`sensitivity` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select message_id from email_message em, message m where m.id=em.message_id and m.sent_on is null and m.publisher_id=2 order by m.created_on \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: em
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 153725
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: avb.em.message_id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
mysql> select count(*) from message;
+----------+
| count(*) |
+----------+
| 154561 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from email_message;
+----------+
| count(*) |
+----------+
| 153725 |
+----------+
1 row in set (0.06 sec)
### my.cnf applicable ###
query_cache_limit = 8M
query_cache_size = 16M
query_cache_type = 1
max_heap_table_size = 64M
read_buffer_size = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 128K