MySQL intermittent slow queries

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]).

I know it's late but there is a lot that could be going on.

-Make sure your statistics look good:

select owner,table_name,sample_size,num_rows,last_analyzed
from dba_tab_statistics.

num_rows and last_analyzed should match if you use default settings.

select owner,table_name,index_name,sample_size,num_rows,last_analyzed
from dba_ind_statistics.

Again sample_size and num_rows should match.

• Verify if you use CURSOR_SHARING=EXACT or FORCE (if SIMILAR, change it to FORCE),(will require sysdba privilege):

  SQL>show parameter cursor; SQL>alter system set cursor_sharing=FORCE; Note that ideally you want to match the parameters you have on your production system. You may want to change these to test them as a cause of your problem however.

• Verify that your SGA is sized properly:

  select * from v$sga_target_advice You would get something like this:

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

  ---

   10240             .25    131401926              1.2592          1.4876E+10
   15360            .375    117178544              1.1229          1.1815E+10
   20480              .5    112472201              1.0778          1.0648E+10
   25600            .625    109873799              1.0529          1.0250E+10
   30720             .75    107077125              1.0261          9638685159
   35840            .875    105929237              1.0151          9404059270
   40960               1    104353499                   1          9058914623
   46080           1.125    103706507               .9938          8926654470
   51200            1.25    102788197                .985          8724640673
   56320           1.375    102391653               .9812          8646734008
   61440             1.5    101807274               .9756          8515379746
   66560           1.625    101275077               .9705          8394896181
   71680            1.75     97987941                .939          7696453864
   76800           1.875     97967070               .9388          7696453864
   81920               2     97967070               .9388          7696453864
  

Where SGA_SIZE_FACTOR is 1, is my current SGA_SIZE. You can look at column ESTD_DB_TIME_FACTOR and ESTD_PHYSICAL_READS to see how much benefit I get if I increase it to the sizes mentioned in SGA_SIZE. In my case, not much benefit which means I'm good.

• Compare your query when executed with literal values vs bind variables. You may want to see if you have a lot of hard parses for a given query. Check v$sql and v$sql_plan (have a look at column child_number for a given sql_id.

• Do you have very skewed data on some of the columns in your where clause, joins and group by? If so you may be experiencing issues related to skewed data if bind variables are used.