Reasons for occasionally slow queries?

Question

We are running MySQL 5.1 on Windows Server 2008 R2.

We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to log when we had queries that took a long time (> 2000ms). The results were surprising (and possibly an explanation for our deadlocks).

Occasionally queries, that normally take very little time (<10ms), are taking from 4 to 13 seconds. To be clear, these are queries that are running constantly (several times a second) and not suffering from these query time spikes.

We have gone through our indexes looking for any obvious mistakes and haven't had much luck.

Update

The people table:

| people | CREATE TABLE `people` (
`people_id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`temp_password` varchar(10) DEFAULT NULL,
`reset_password_hash` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL,
`iphone_device_id` varchar(160) DEFAULT NULL,
`iphone_device_time` datetime DEFAULT NULL,
`last_checkin` datetime DEFAULT NULL,
`location_lat` double DEFAULT NULL,
`location_long` double DEFAULT NULL,
`gps_strength` smallint(6) DEFAULT NULL,
`picture_blob_id` bigint(20) DEFAULT NULL,
`authority` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`date_created` datetime NOT NULL,
`last_login` datetime NOT NULL,
`panic_mode` tinyint(1) NOT NULL DEFAULT '0',
`battery_level` double DEFAULT NULL,
`battery_state` varchar(32) DEFAULT NULL,
PRIMARY KEY (`people_id`),
KEY `email` (`email`),
KEY `company_id` (`company_id`),
KEY `iphone_device_id` (`iphone_device_id`),
KEY `picture_blob_id` (`picture_blob_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`picture_blob_id`) REFERENCES `blobs` (`blob_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4658 DEFAULT CHARSET=utf8 |

Indexes:

+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| people |          0 | PRIMARY          |            1 | people_id        | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | email            |            1 | email            | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | company_id       |            1 | company_id       | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | iphone_device_id |            1 | iphone_device_id | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | picture_blob_id  |            1 | picture_blob_id  | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

we have ~5000 rows in the table on the server that is giving us trouble.

Answer 1

The UPDATE queries in your previous two questions (Question1,Question2) are hitting the table 'people' by PRIMARY KEY with row level locking. This is what I stated back in Question1 on June 6, 2011 10:03 AM

All the transactions are traversing the PRIMARY key. Since the PRIMARY is a clustered index in InnoDB, the PRIMARY key and the row itself are together. Thus, traversing a row and and the PRIMARY KEY are one and the same. Therefore, any index lock on the PRIMARY KEY is a row level lock as well.

Something else has not been considered yet that can attribute slowness to indexes: The use of NON-UNIQUE indexes in InnoDB. Every indexed lookup in InnoDB using non-unique indexes also have the rowID of each row attached to the non-unique key. The rowID basically eminates from the Clustered Index. Updating non-unique indexes MUST ALWAYS interact with the clustered index EVEN IF THE TABLE DOES NOT HAVE A PRIMARY KEY.

Another thing to think about is the process of managing BTREE nodes in an index. Sometimes, it requires the page splitting of nodes. All entries in the BTREE node of non-unique indexes contain non-unique fields PLUS the rowID within the clustered index. To properly mitigate the splitting of such BTREE pages without disturbing data integrity, the row associated with the rowID must experience a row level lock internally.

If the 'people' table has a lot of non-unique indexes, prepare to have a large number of index pages in the tablespace as well as having tiny little rows locks sneak up on you from time to time.

There ia another factor which is not as obvious: Key Population

Sometimes when an index get populated, the key values making up the indexes could become lopsided over time and cause the MySQL Query Optimizer to switch from keyed lookups, to index scans, and finally to full table scans. That you cannot control unless you redesign the table with new indexes to compensate for the lopsidedness ot keys. Please provide the table structure for the 'people' table, the count of the 'people' table, and the show indexes output for the 'people' table.

Even if queries use only the PRIMARY KEY, lopsidedness of keys in non-unique indexes still needs BTREE balancing and page splitting to occur. Such BTREE management will produce a notable slowdown due to intermittent row level locks you did not intend to happen.

UPDATE 2011-06-14 22:19

Queries From Question 1

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

Picture the sequence in events

1. Find the row by PRIMARY KEY
2. Lock the row and clustered index
3. Create MVCC Data for all columns being update
4. Four columns are indexed (email,company_id,iphone_device_id,picture_blob_id)
5. Each index requires BTREE management
6. Within the same transaction space, steps 1-5 is trying to be repeated on the same row, updating the same columns (email the same in both queries, company_id the same in both queries, picture_blob_id the same in both queries, iphone_device_id different)

Queries From Question 2

UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;

These two queries are even more confusing because the first query is updating everything except people_id 666. Hundreds of rows are being painfully locked with just the first query. The second query is updating people_id 666 running the 5 sequence of events. The first query is running those same 5 sequence of events on every row involved except people_id 666 but the index for iphone_device_id is on an interecept course with two different queries. Somebody's gotta lock in the BTREE pages on a first-come-first-serve basis.

In the face of these two pairs of queries on a collision course to possibly lock the same BTREE pages within one index can be a gut-wrenching experience for InnoDB or any ACID-compliant RDBMS. Thus, an index slowdown is the destiny of these pairs of queries unless you can guarantee that the queries run with AUTOCOMMIT = 1 or by allowing dirty reads (although collisions like these make READ-COMMITTED and READ-UNCOMMITED a nightmare for MVCC).

UPDATE 2011-06-15 10:29

@RedBlueThing : In the queries from question 2, the first query is a range query, so a lot of row locks are being attained. Also notice both queries are trying to lock the same space id 0 page no 4611 n bits 152 is being locked in the PRIMARY KEY, aka clustered index.

In order to make sure you app is, at the very least, running based on the series of events you expect, there are two different options you could try:

Option 1) Convert this table to MyISAM (at least on a development server). Each UPDATE, INSERT, and DELETE will impose a full table lock on a first-come, first-serve basis.

Option 2) Try using the SERIALIZABLE isolation level. That will lock all intended rows in SHARED mode.

The sequence of events you expect will either break or be successful using these two alternative options. If both of these options fail, then you will need to look over your app and prioritize the order of execution of your queries. Once you establish that priority, you can simply undo these options (For option 1, go back to InnoDB, For option 2, go back to the default isolation level [stop using SERIALIZABLE]).

Answer 2

SHOW VARIABLES LIKE 'innodb%'; -- In particular, if the data and indexes has just not reached the size of the buffer pool, you could be hitting the disk a lot harder than before. I/O is the big performance killer.

Most of your fields are twice as big as needed. BIGINT (8 bytes) is way overkill for most ids. 5000 rows needs only a SMALLINT UNSIGNED (limit of 65K, only 2 bytes). Or use MEDIUMINT for a margin of safety.

DOUBLE gives you 16 significant digits at a cost of 8 bytes. Does battery_level have more than 2 significant digits of precision? FLOAT takes 4 bytes.

My point here is that "smaller -> more cacheable -> faster".

Please show us the slow queries; at least some of the ones that have suddenly become slower. We can only make guesses without them. Turn on the slowlog and set long_query_time = 1; these will help find the slowest queries.

Do you understand the benefit of "compound" indexes?