I was asked today about what is the cost of adding an index on a frequently updated column ( like a timestamp, count, or weight )… typically my answer is it depends. But for this question it was narrowed down to a specific case. An update on a secondary index based on a PK lookup. I decided to try and give an exact answer. I hacked the Juice DB Benchmark to attack my medium sized table ( which magically already had a count column in it ). I then cranked up the test. A few more details Query 23 updated a column without an index, queries 21,23,24 updated the d_count column. query 21 adds 5 to the count, query 22 adds 150, query 24 subtracts 1…. here are the results:

With a solo index on d_count:

Run Number:  86  threads:  8 Length :  340 LoadType: upd
Total Test Runtime = 375.245010137558 seconds, limiting results to 300 seconds however
QNum:     21 ... QCount:  78448 ... QTime:   0.003985 ... Max:   0.095937 ... FlatTime:   0.003673 …

Last night I was on a plane to a client site and I was reviewing the waffle grid code for inclusion of multi-gets when calling read-ahead functions (potentially in other area’s as well)…  their I noticed something that may slow down our performance in waffle grid.  You see we read from memcached in the buf_read_page_low function.  This function is responsible for checking wether a page exists in the buffer and if not making an IO request for it via the function fil_io.  What I saw in buf_read_page_low was:  we check the buffer, then check memcached, then go get the page off disk… you follow?  Alright The get page from disk part is challenge.  Let me do a quick deep dive on the internal function calls.

You see buf_read_page_low is called by a few different functions in a few different ways, the ones I am concerned with are the functions that are pasing a sync of false ( you do not want synchronous IO, …

In my previous Post, we took a look at the file structure of the InnoDB primary key index. With the same table structure, but a slightly different dataset:

mysql> select * from test_innodb_growth limit 10;
+----+------------+--------+
| id | data       | spacer |
+----+------------+--------+
|  1 | a          | |      |
|  2 | aa         | |      |
|  3 | aaa        | |      |
|  4 | aaaa       | |      |
|  5 | aaaaa      | |      |
|  6 | aaaaaa     | |      |
|  7 | aaaaaaa    | |      |
|  8 | aaaaaaaa   | |      |
|  9 | aaaaaaaaa  | |      |
| 10 | aaaaaaaaaa | |      |
+----+------------+--------+
10 rows in set (0.00 sec)

All the rows after id = 10 have data = ‘aaaaa’. Let’s take a look at the secondary index ‘idxdata’. I recently had an argument with a client that claimed that a varchar is fully expanded in a secondary index. The following will prove it is not …

Recently, I found a very handy tool, innodb-recovery, that can break an InnoDB file in pages. I am a visual type person so what a better occasion to try to learn more about the InnoDB file structure. To explore the file structure, I used the following table:

Create Table: CREATE TABLE `test_innodb_index` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(50) DEFAULT NULL,
  `spacer` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxdata` (`data`)
) ENGINE=InnoDB AUTO_INCREMENT=1901 DEFAULT CHARSET=latin1

and inserted a bunch of rows like these:

select * from test_innodb_index limit 4;
+----+-------+--------+
| id | data  | spacer |
+----+-------+--------+
|  1 | aaaaa | |      |
|  2 | aaaaa | |      |
|  3 | aaaaa | |      |
|  4 | aaaaa | |      |
+----+-------+--------+
4 rows in set (0.00 sec)

After the insertion, the table status looked like this:

mysql> show table status like …