Saturday, February 4, 2017
Monday, January 16, 2017
I have for some years been running the queries of the DBT-3 benchmark, both to verify the effect of new query optimization features, and to detect any performance regressions that may have been introduced. However, I have had issues with getting stable results. While repeated runs of a query is very stable, I can get quite different results if I restart the server. As an example, I got a coefficient of variation (CoV) of 0.1% for 10 repeated executions of a query on the same server, while the CoV for the average runtime of 10 such experiments was over 6%!
With such large variation in results, significant performance regressions may not be noticed. I have tried a lot of stuff to get more stable runs, and in this blog post I will write about the things that I have found to have positive effects on stability. At the end, I will also list the things I have tried that did not show any positive effects.
First, I will describe the setup for the tests I run. All tests are run on a 2-socket box running Oracle Linux 7. The CPUs are Intel Xeon E5-2690 (Sandy Bridge) with 8 physical cores @ 2.90GHz.
I always bind the MySQL server to a single CPU, using taskset or numactl, and Turbo Boost is disabled. The computer has 128 GB of RAM, and the InnoDB buffer pool is big enough to contain the entire database. (4 GB buffer pool for scale factor 1 and 32 GB buffer pool for scale factor 10.)
Each test run is as follows:
- Start the server
- Run a query 20 times in sequence
- Repeat step 2 for all DBT-3 queries
- Stop the server
The result for each query will be the average execution times of the last 10 runs. The reason for the long warm-up period is that, from experience, when InnoDB's Adaptive Hash Index is on, you will need 8 runs or so before execution times are stable.
As I wrote above, the variance within each test run is very small, but the difference between test runs can be large. The variance is somewhat improved by picking the best result out of three test runs, but it is still not satisfactory. Also, a full test run on a scale factor 10 database takes 9 hours, so I would like to avoid having to repeat the tests multiple times.
Address Space Layout Randomization
A MySQL colleague mentioned that he had heard about some randomization that was possible to disable. After some googling, I learned about Address Space Layout Randomization (ASLR). This is a security technique that is used to prevent an attacker from being able to determine where code and data are located in the address space of a process. I also found some instructions on stackoverflow for how to disable it on Linux.
Turning off ASLR sure made a difference! Take a look at this graph that shows the average execution time for Query 12 in ten different test runs with and without ASLR (All runs are with a scale factor 1 DBT-3 database on MySQL 8.0.0 DMR):
I will definitely make sure ASLR is disabled in future tests!
Adaptive Hash Index
InnoDB maintains an Adaptive Hash Index (AHI) for frequently accessed pages. The hash index will speed up look-ups on primary key, and is also useful for secondary index accesses since a primary key look-up is needed to get from the index entry to the corresponding row. Some DBT-3 queries run twice as slow if I turn off AHI, so it has definitely some value. However, experience shows that I will have to repeat a query several times before the AHI is actually built for all pages accessed by the query. I plan to write another blog post where I discuss more about AHI.
Until I stumbled across ASLR, turning off AHI was my best bet at stabilizing the results. After disabling ASLR, also turning off AHI only shows a slight improvement in stability. However, there are other reasons for turning off AHI.
I have observed some times that with AHI on, a change of query plan for one query may affect the execution time of subsequent queries. I suspect the reason is that the content of the AHI after a query has been run, may change with a different query plan. Hence, the next query may be affected if it accesses the same data pages.
Turning off AHI also means that I no longer need the long warm-up period for the timing to stabilize. I can then repeat each query 10 times instead of 20 times. This means that even if many of the queries take longer to execute, the total time to execute a test run will be lower.
Because of the above, I have decided to turn off AHI in most of my test runs. However, I will run with AHI on once in a while just to make sure that there are no major regressions in AHI performance.
Preload Data and Indexes
I also tried to start each test run with a set of queries that would sequentially scan all tables and indexes. My thinking was that this could give a more deterministic placement of data in memory. Before I turned off ASLR, preloading had very good effects on the stability when AHI was disabled. With ASLR off, the difference is less significant, but there is still a slight improvement.
Below is a table that shows some results for all combinations of the settings discussed so far.
Ten test runs were performed for each combination on a scale factor 1
database. The numbers shown is the average difference between the best
and the worst runs over all queries, and the largest difference between
the best and the worst runs for a single query.
From the above table it is clear that the most stable runs are achieved by using preloading in combination with disabling both ASLR and AHI.
For one of the DBT-3 queries, using preloading on a scale factor 10 database leads to higher variance within a test run. While the CoV within a test run is below 0.2% for all queries without preloading, query 21 has a CoV of 3% with preloading. I am currently investigating this, and I have indications that the variance can be reduced by setting the memory placement policy to interleave. I guess the reason is that with a 32 GB InnoDB buffer pool, one will not be able to allocate all memory locally to the CPU where the server is running.
What Did Not Have an Effect?
Here is a list of things I have tried that did not seem to have a positive effect on the stability of my results:
- Different governors for CPU frequency scaling. I have chosen the performance governor, because it "sounds good", but I did not see any difference when using the powersave governor instead. I also tried turning off the Intel pstate driver, but did not notice any difference in that case either.
- Bind the MySQL server to a single core or thread (instead of CPU).
- Bind the MySQL client to a single CPU.
- Different settings for NUMA memory placement policy using numactl. (With the possible exception of using interleave for scale factor 10 as mentioned above.)
- Different memory allocation libraries (jemalloc, tcmalloc). jemalloc actually seemed to increase the instability.
- Disable InnoDB buffer pool preloading:
My tests have shown that I get better stability of test results if I disable both ASLR and AHI, and that combining this with preloading of all tables and indexes in many cases will further improve the stability of my test setup. (Note that I do not recommend to disable ASLR in a production environment. That could make you more vulnerable to attacks.)
I welcome any comments and suggestions on how to further increase the stability for MySQL benchmarks. I do not claim to be an expert in this field, and any input will be highly appreciated.
Friday, December 23, 2016
Fortunately, MySQL 5.6 also introduced subquery materialization that can be used when semi-join does not apply; as long as the subquery is not dependent on the outer query. (There should be no references to tables of the outer query within the subquery). In an earlier blog post, I showed how the query execution time for DBT-3 Query 18 improved from over a month to a few seconds due to subquery materialization. In this blog post, I will show how we can further improve the performance of Query 18 by rewriting it.
Let's first take a look at Query 18 in its original form:
This query is called Large Volume Customer Query since it finds large orders (of more than 313 items) and returns the top 100 orders; ordered by total price and date. In MySQL 5.5 and earlier, MySQL would execute the GROUP BY subquery once for every row of the outer query. In MySQL 5.6, subquery materialization made it possible to execute this subquery only once. This can be seen from the EXPLAIN output for this query (some of the columns have been removed to save space):
SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, SUM(l_quantity) FROM customer, orders, lineitem WHERE o_orderkey IN ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 313 ) AND c_custkey = o_custkey AND o_orderkey = l_orderkey GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ORDER BY o_totalprice DESC, o_orderdate LIMIT 100;
|1||PRIMARY||orders||ALL||NULL||1500000||Using where; Using temporary; Using filesort|
That select_type is SUBQUERY indicates that the subquery will be executed only once. (Otherwise, the select_type would be DEPENDENT SUBQUERY.) Here is the Visual EXPLAIN diagram for the same query plan:
Looking at QUERY 18, we realize that the subquery will never give any duplicates. The only column selected is the column that we are grouping on, l_orderkey, so all rows will be distinct. This means that we can replace the IN-expression with a join, and get this equivalent query:
SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, SUM(lineitem.l_quantity) FROM customer, orders, lineitem, ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 313 ) l2 WHERE o_orderkey = l2.l_orderkey AND c_custkey = o_custkey AND o_orderkey = lineitem.l_orderkey GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ORDER BY o_totalprice DESC, o_orderdate LIMIT 100;
The subquery is now a derived table; a subquery in the FROM clause. Take a look at the query plan for the rewritten query:
Friday, October 21, 2016
- How to Analyze and Tune MySQL Queries for Better Performance
(Tutorial at OpenWorld)
- MySQL 8.0: Common Table Expressions
(Presented at both conferences)
Saturday, September 17, 2016
Oystein Grovlen, Senior Principal Software Engineer, Oracle
Wednesday, Sep 21, 3:00 p.m.
MySQL Optimizer: What’s New in 5.7 and Sneak Peek at 8.0 [CON6112]
Manyi Lu, Director Software Engineering, Oracle
Thursday, Sep 22, 12:00 p.m.
MySQL 8.0: Common Table Expressions [CON7928]
Manyi Lu, Director Software Engineering, Oracle
Oystein Grovlen, Senior Principal Software Engineer, Oracle
We will also like to invite people to the MySQL community reception at Jillian's on Tuesday, September 20 @ 7 pm. This reception is not limited to OpenWorld registrants. So if you are in the SF area, please, feel free to come and meet MySQL developers and users!
Friday, March 4, 2016
Wednesday, January 27, 2016
To turn on recording of optimizer trace for the current session:
When optimizer trace is enabled, the information schema table optimizer_trace will contain the trace for the latest query that was explained or executed. I usually dump the trace to a file using this SQL statement:
SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE '<filename>' LINES TERMINATED BY '';
One important thing to note is that there is a configurable maximum size for the memory buffer used to record the trace. The default is pretty low, and you will often have to increase the size to capture the entire trace. The missing_bytes_beyond_max_mem_size column of the optimizer_trace table shows how many bytes are missing from the trace. If this column is non-zero, you should increase the setting of the variable optimizer_trace_max_mem_size:
The above statement increases the trace buffer to about 1 MB. Unless your query joins very many tables, this should be sufficient.
To get more information about optimizer trace, check: