Variable's Day Out #14: log_queries_not_using_indexes

Properties:

Applicable To	MySQL Server
Server Startup Option	--log-queries-not-using-indexes
Scope	Global
Dynamic	Yes
Possible Values	Boolean
Default	False
Category	Performance, Monitoring, Best Practices

Description:

If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.

Usage:

While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable log_queries_not_using_indexes and start index usage testing. Analyze all queries, introduce indexes one by one and you are all set to go. This helps a lot in rewriting queries, figuring out the best possible index etc. Many times I have seen the need for swapping WHERE clause CONDITIONS for making it possible for the query to use an existing index.

For already deployed systems' performance glitches and production outages, this flag do serve as a starting point.

Though as always, usage of a variable is subject to circumstances. For my conditions and usage pattern, this variable comes very handy.

Read more:

• MySQL manual entry on log-queries-not-using-indexes
• Slow Query Logs

Hope you enjoyed reading this.

Variable's Day Out #2: key_buffer_size

Properties:

Engine(s)	MyISAM
Server Startup Option	--key_buffer_size=<value>
Scope	Global
Dynamic	Yes
Possible Values	Integer Range: 8 - 4294967295 (4 GB)
Default Value	131072 (128 KB)
Category	Performance

Description:

This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the MySQL Documentation, "using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common". As suggested by Peter, "Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload".

Starting with 25%, one can keep monitoring key cache hit ratio and increase it over time. A key cache hit ratio of more than 95% should be good enough. Also, for preventing oneself from allocating too much memory to this buffer, monitoring key buffer usage should be helpful. I feel one should target towards more than 80% of buffer usage.

If you are not using very few MyISAM tables, still key_buffer can be useful for MySQL's tables and temporary tables as well.

Calculations:

• key_cache_hit_ratio = (1 - Key_reads/Key_read_requests) * 100
• key_buffer_usage    = (1 - (Key_blocks_unused * key_cache_block_size)/key_buffer_size) * 100

Read More:

MySQL manual entry on key_buffer_size

What to tune in MySQL server after installation

MyISAM Key Cache - MySQL Manual

 

Hope this post was helpful to you.

InnoDB Deadlock - Next Key Locking

So you use InnoDB, have indexes on your table, think of row level locking and concurrent queries, feel good and go to sleep. All this while forgetting that even UPDATE and SELECT .... FOR UPDATE statements will (or may) also use the same index for scanning or updating. Then what? You may ask.

Well, InnoDB row level locking works in a somewhat different manner when using indexes. In this case, InnoDB locks index records in shared or exclusive mode when searching or scanning an index. Therefore, as mentioned in MySQL Documentation, the row level locks are actually index record locks.

To complicate matters (or resolve issues) further, the lock is a gap lock. A gap lock refers to a lock that only locks a gap before some index record.

As per the example in MySQL Documentation, lets say we have a query like this.

   1: SELECT * FROM child WHERE id > 100 FOR UPDATE;

And there is an index on id column. The query will lock all the index records where id is greater than 100, like 103, 107, etc. Meanwhile, the query also locks out inserts made in the gaps, say for 101,102, 104, 105, 106, 108 and above. If this is not done, there may be inserts while the transaction is under way.

Now let us consider a little tricky problem. Suppose we have a table table_name with index on colx. We will have two queries running in parallel. For sure, both the queries are working on only one row each and as per the application, they are always different queries.

The queries we have are:

   1: mysql> SELECT * FROM table_name WHERE

   2:     -> colx IS NULL

   3:     -> LIMIT 1

   4:     -> FOR UPDATE;

and

   1: mysql> UPDATE table_name SET

   2:     -> colx = NULL, coly = NULL

   3:     -> WHERE colz = 'something';

The first query starts locking the index records. But locking is not done in a single shot, it is done record by record. Meanwhile, the second query also gets lock on the record that signifies the current value of colx and proceeds towards the NULL value record. Now by the time second query gets to the NULL value record, that gets locked by the first query and first query somehow waits for the lock already grabbed by second one. Huh!

You need to be very (un)lucky to achieve this.

Workarounds

1. Figure out all such indexes and queries where a deadlock can happen. In most cases, theoretical investigation may lead to unnecessary paranoia, so its better to have a regression test on your system and monitor for deadlocks. If you can, part away from those indexes.
2. If you think you cannot live without your indexes, and the queries involved in deadlocks are mutually exclusive, you can go ahead with enabling innodb_locks_unsafe_for_binlog without citing this blog as a reference. It's all your choice.
3. The tests I have conducted have been done on MySQL 4.1.x. There is a claim that from MySQL 5.0.2, UPDATE and DELETE only locks rows that are going to be affected. So this should reduce the probability of deadlocks. I haven't tested it though.

Hope this blog was helpful to you. Keep posting your comments.

InnoDB Row Counting using Indexes

This is always mentioned that InnoDB is slower in giving results for COUNT(*) as compared to MyISAM. But as Peter points out in his blog that this fact only applies to COUNT(*) queries without WHERE clause. This text is from Peter's blog only - "If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions." Let's see what EXPLAIN has in store for us.

   1: mysql> CREATE TABLE `test_index` (

   2:   `id` int(11) NOT NULL AUTO_INCREMENT,

   3:   `x` int(11) DEFAULT NULL,

   4:   `y` int(11) DEFAULT NULL,

   5:   `z` varchar(255) NOT NULL DEFAULT 'testing',

   6:   PRIMARY KEY (`id`),

   7:   KEY `x` (`x`,`y`)

   8: ) ENGINE=InnoDB;

   9:  

  10: mysql> EXPLAIN SELECT COUNT(*) FROM test_index \G

  11: *************************** 1. row ***************************

  12:            id: 1

  13:   select_type: SIMPLE

  14:         table: test_index

  15:          type: index

  16: possible_keys: NULL

  17:           key: PRIMARY

  18:       key_len: 4

  19:           ref: NULL

  20:          rows: 4875772

  21:         Extra: Using index

  22: 1 row in set (0.01 sec)

  23:  

  24: mysql> SELECT COUNT(*) FROM test_index;

  25: +----------+

  26: | COUNT(*) |

  27: +----------+

  28: |  4915200 |

  29: +----------+

  30: 1 row in set (2.61 sec)

The explain states that the counting is going to be done on PRIMARY index and Using Index. The best part is that since it is going to use PRIMARY index and since it is NOT NULL, MySQL will actually count the values from the index itself. So, contrary to the thought that something like COUNT(1) will work faster is not true in this case. Here is an interesting case from a bug.

   1: mysql> CREATE TABLE `test` (

   2:   `id` int(11) NOT NULL,

   3:   `int_k` int(11) NOT NULL,

   4:   `data1` varchar(255) NOT NULL,

   5:   `data2` varchar(255) NOT NULL,

   6:   PRIMARY KEY (`id`),

   7:   KEY `int_k` (`int_k`)

   8: ) ENGINE=InnoDB DEFAULT CHARSET=latin1

   9: 1 row in set (0.00 sec)

  10:  

  11: mysql> CREATE PROCEDURE populate()

  12: begin

  13:   declare i int;

  14:   set i = 0;

  15:   start transaction;

  16:   while i < 300000 do

  17:     insert into test (id, int_k, data1, data2)

  18:       values (i, i, repeat("-", 250), repeat("-", 250));

  19:     set i = i + 1;

  20:     if i % 1000 = 0 then

  21:       start transaction;

  22:     end if;

  23:   end while;

  24:   commit;

  25: end

  26: 1 row in set (0.00 sec)

  27:  

  28: mysql> call populate;

  29: Query OK, 0 rows affected (1 min 0.65 sec)

  30:  

  31: mysql> SELECT COUNT(*) FROM test;

  32: +----------+

  33: | COUNT(*) |

  34: +----------+

  35: |   300000 |

  36: +----------+

  37: 1 row in set (1.08 sec)

  38:  

  39: mysql> SELECT COUNT(*) FROM test use index (int_k);

  40: +----------+

  41: | COUNT(*) |

  42: +----------+

  43: |   300000 |

  44: +----------+

  45: 1 row in set (0.12 sec)

Using a secondary index is faster. But why? Generally speaking, the PRIMARY index should be faster because it is usually in order and can be read with sequential I/O at around 15 times more speed than generally fragmented secondary index. Actually this is a special case, since the secondary index is inserted into the table in perfect order, which is very rare. Also, as Heikki points out in the bug

"Since the minimum record size of InnoDB is about 20 bytes, and the fill-factor of a secondary index is typically 70 %, we can calculate that if the row length is > 15 * 1.5 * 20 = 450 bytes, then scanning the secondary index would probably be a better option." Till this feature is implemented and we have a much better optimized count(*) for InnoDB, please use secondary index explicitly for counting rows if you satisfy any of above conditions. Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: InnoDB, MySQL, Performance, Indexes