Impact from adaptive hash index and innodb_thread_concurrency in MySQL 5.7.5

I have been evaluating micro-benchmark results for point and range queries in MySQL 5.7, 5.6, 5.5, 5.1 and 5.0. In this post I document the impact from disabling the InnoDB adaptive hash index and using innodb_thread_concurrency. Context matters and in this case the database is cached with a small working set (between 1 and 1000 rows) with 1 to 32 concurrent queries on a server with 40 hyperthread cores that is shared by mysqld and the client (mysqlslap). I did not test the case where when the client concurrency exceeds the number of CPU cores and the results there might not match mine here.

The workload includes point queries and index range scans. For each there are three query types. The first uses a PK (q1), the second uses a non-covering secondary index (q4) and the third uses a covering secondary index (q5). The range scans are done for LIMIT 10 and LIMIT 1000. These queries were used in my previous blog posts on 5.7 performance.

My summary is that for this workload there isn't much risk from using innodb_thread_concurrency, although I prefer to continue to make InnoDB efficient on multi-core so we can forget about that option. Not using the adaptive hash index can greatly increase response time for queries that do a lot of work (in this case "a lot" meant 1000 or 2000 index searches) even at high concurrency. We need to make it more efficient on multi-core. I know the InnoDB team can do it, given they previously sharded the buffer pool.

In what follows I use itc to name binary with innodb_thread_concurrency=32, innodb_concurrency_tickets=500 and noahi to name the binary with innodb_adaptive_hash_index=0. More detail on the binaries used and the test configuration is in the previous posts on point and range queries linked above.

The results time below are normalized. Each result shows the value of A/B where A is the response time for MySQL 5.7.5 with a given configuration and query (noahi-q4 for example) and B is the response time for the same query and MySQL 5.7.5 with the base configuration (adaptive hash index enabled, innodb_thread_concurrency=0).

point queries

The overhead for the itc binaries is between +/- 1% for all but one of the cases. The overhead for the noahi binaries is similar except for q1 (point query on PK) where it is between 2.5% and 3.5%. This is a good result to me, assuming there is a benefit for another workload, in that things don't get much worse.

10-row scan

This has results for the 10-row range scan (queries q1, q4 and q5 described above). The overhead for the itc binaries ranges from -2% (good) to +1% (bad). That isn't significant to me assuming there is a benefit for other workloads. For the ahi binaries the overhead is no more than +1% for q1 and q5. But for q4 the overhead is about +8% at 1 thread and +12% at 4 threads. That might be too much. Note that this query does much more work by doing a PK search to get missing columns for each of the 10 entries fetched from the secondary index.

1000 row scan

There are two graphs for the 1000-row range scan. The second graph has the y-axis truncated at 1.15 so the large value for noahi-q4 doesn't hide everything else. Note that the itc binary uses innodb_concurrency_tickets=500 and the query fetches 1000 rows so it will have to fetch more tickets at least once per query. While the default for tickets has increased from 500 to 5000 in recent releases, I can construct a test case that fetches more than 5000 rows. The itc binaries have an overhead between +3% and +10% at 32 threads, and the overhead is much higher at 32 threads then at 1 or 2. The noahi binaries have a huge overhead for q4 that decreases with concurrency, from 1.75X down to 1.02X. This shows the benefit of the adaptive hash index for queries that do many index searches. For other queries (q1 and q5) there was a small benefit from using noahi except for q5 at 32 threads.