MySQL Forums
Forum List  »  Performance

Sudden drop in performance on MyISAM DB
Posted by: william Warwick
Date: October 15, 2012 07:46AM

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 


Options: ReplyQuote


Subject
Views
Written By
Posted
Sudden drop in performance on MyISAM DB
2615
October 15, 2012 07:46AM


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.