Personal blog of Yzmir Ramirez

A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?

Setup

For this test, we will use these 2 tables (same structure as in Peter’s post):

CREATE TABLE t1000merge (
  id int not null auto_increment primary key,
  i int(11) NOT NULL,
  j int(11) NOT NULL,
  val char(10) NOT NULL,
  KEY i (i),
  KEY j (j)
) ENGINE=InnoDB;
CREATE TABLE t1000idx2 (
  id int not null auto_increment primary key,
  i int(11) NOT NULL,
  j int(11) NOT NULL,
  val char(10) NOT NULL,
  KEY ij (i,j)
) ENGINE=InnoDB;

Tables were populated with 1M rows for this test, i and j have 1000 distinct values (independent of each other). The buffer pool is large enough to hold all data and indexes.

We will look at this query on MySQL 5.5.35 and MySQL 5.6.15:

SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN 100 and 200

Why this specific query? With MySQL 5.5, for t1000idx2, the optimizer estimates that the index on (i,j) is not selective enough and it falls back to a full table scan. While for t1000merge, the index on (j) is an obvious good candidate to filter efficiently.

Consequently this query has a better response on t1000merge (0.01s) than on t1000idx2 (0.45s).

On MySQL 5.6, this query is a good candidate for index condition pushdown (ICP), so we can reasonably hope that response time for t1000idx2 will improve.

ICP: FORCE INDEX to the rescue

Unfortunately the optimizer still prefers the full table scan which gives us the same bad response time:

mysql5.6> EXPLAIN SELECT sum(length(val)) FROM t1000idx2 WHERE j=2 AND i BETWEEN 100 and 200;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1000idx2 | ALL  | ij            | NULL | NULL    | NULL | 1000545 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+

And what if we use FORCE INDEX?

mysql5.6 > EXPLAIN SELECT sum(length(val)) FROM  t1000idx2 FORCE INDEX(ij) WHERE j=2 AND i BETWEEN 100 and 200;
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows   | Extra                 |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | t1000idx2 | range | ij            | ij   | 8       | NULL | 188460 | Using index condition |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+

This time ICP is used (see “Using index condition” in the Extra field)!

And the difference in response time is impressive:
– Without FORCE INDEX (full table scan): 0.45s
– With FORCE INDEX (multiple column index + index condition pushdown): 0.04s, a 10x improvement!

Additional thoughts

It is interesting to see that the optimizer fails to find the best execution plan for this simple query. The optimizer trace sheds some light:

mysql> SET optimizer_trace="enabled=on";
mysql> SELECT sum(length(val)) FROM  T  WHERE j=2 AND i BETWEEN 100 and 200;
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
[...]
"range_analysis": {
                  "table_scan": {
                    "rows": 1000545,
                    "cost": 202835
                  },

This is the estimated cost for a full table scan.
Now we will see how the optimizer estimates the cost of the range scan using the ij index:

[...]
"range_scan_alternatives": [
                      {
                        "index": "ij",
                        "ranges": [
                          "100 <= i <= 200"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 188460,
                        "cost": 226153,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ]
[...]

At this stage the optimizer does not know if ICP can be used. This probably explains why the cost of the range scan is overestimated.

If we look at the optimizer trace for the query with the FORCE INDEX hint, ICP is only detected after the range scan is chosen:

[...]
"refine_plan": [
              {
                "table": "`t1000idx2` FORCE INDEX (`ij`)",
                "pushed_index_condition": "((`t1000idx2`.`j` = 2) and (`t1000idx2`.`i` between 100 and 200))",
                "table_condition_attached": null,
                "access_type": "range"
              }
[...]

Conclusion

Multiple column index vs multiple indexes? Having indexes on single columns often lead to the optimizer using the index_merge access type, which is typically not as good as accessing a single index on multiple columns. MySQL 5.6 makes multiple column indexes more efficient than before with index condition pushdown.

But don’t forget that the optimizer is not perfect: you may have to use index hints to benefit from this feature.

The post Multiple column index vs multiple indexes with MySQL 5.6 appeared first on MySQL Performance Blog.

Based on a lot of surprising comments about my MySQL 5.5 vs 5.6 performance post I decided to perform deeper investigation to see where my results could go possibly wrong. I had set up everything to be as simple as possible to get maximally repeatable results. I did Read Only ran which is typically a lot more repeatable (though also less relevant for production like workload). I had done number of iterations for benchmark run and I used dedicated physical hardware box so external environment impact often causing problems in Virtualized environments can be eliminated. Still I found there could be large variance between the runs.

I set up the benchmarks run to go over night in the loop, doing the benchmark run for 5 runs when restarting MySQL server and repeating the run. I did it on 2 identical boxes to eliminate faulty hardware as possible suspect. In both cases I’ve spotted something along those ways:

Running Socket /tmp/mysql_sandbox5610.sock with 1 threads
    transactions:                        99210  (330.70 per sec.)
    transactions:                        99733  (332.44 per sec.)
    transactions:                        99766  (332.55 per sec.)
    transactions:                        99838  (332.79 per sec.)
    transactions:                        100035 (333.45 per sec.)

These would be very stable results repeatable in most cases, but sometimes you would see something like this instead:

Running Socket /tmp/mysql_sandbox5610.sock with 1 threads
    transactions:                        90816  (302.72 per sec.)
    transactions:                        90748  (302.49 per sec.)
    transactions:                        90941  (303.13 per sec.)
    transactions:                        90934  (303.11 per sec.)
    transactions:                        90689  (302.29 per sec.)

As you can see the variance between individual (5 minute) run iteration is expectedly rather small – less than 1% especially if you remove the first run, which can be seen as warmup. However the difference between different starts of MySQL is staggering. It looks like sometimes you can just get MySQL server started a wrong way and you will get 10% worse performance which will be very stable at that lower level.

I can repeat it for both MySQL 5.5 and MySQL 5.6 both with single thread and 64 threads run, though the difference in performance can vary from 5% to 10%.

This does not seems to be the plan difference which could be one common cause of performance differences based on server restart it seems to be something else. For now I do not have a good answer what it could be. Why I’m writing about it ? Well there are two outcomes for me.

First – I now can see the confirmation for “magical slowdowns” some our customers reported when upon restart MySQL works significantly slower with no changes to the query load or query plans, which can be solved by MySQL restart. My previous approach have been – there must be some external changes in environment which we’re just not tracking.

Second – I understand we need to be a lot more careful with benchmarks. We need to do multiple runs with MySQL restarts in the end to get meaningful data.

I also can still confirm there is a significant performance difference between MySQL 5.5 and MySQL 5.6 in this workload with MySQL 5.6 being slower, yet the worse case performance difference looks lower than 26% I wrote about. I will update with corrected numbers as I will be able to look more into it.

The post Mystery Performance Variance with MySQL Restarts appeared first on MySQL Performance Blog.