Reasons for occasionally slow queries?

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`) REF

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 = '', password = '',
temp_password = NULL, reset_password_hash = NULL, email = '@yahoo.com',
phone = NULL, mobile = '', iphone_device_id = 'android:-',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = , location_long = -, 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 = '', password = '',
temp_password = NULL, reset_password_hash = NULL, email = '@yahoo.com',
phone = NULL, mobile = '', iphone_device_id = 'android:---',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = , location_long = -, 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

• Find the row by PRIMARY KEY

• Lock the row and clustered index

• Create MVCC Data for all columns being update

• Four columns are indexed (email,company_id,iphone_device_id,picture_blob_id)

• Each index requires BTREE management

• 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:' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '',
temp_password = NULL, reset_password_hash = NULL, email = '@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = , location_long = , 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 seque

StackExchange Database Administrators Q#3302, answer score: 14