Small Datum

This post has results for InnoDB in MySQL 5.6, 5.7 and 8.0 using an IO-bound & low-concurrency workload via sysbench. The setup is the same  as I described  for MyRocks and I wrote about in-memory sysbench + InnoDB in a previous post . The goal is to understand where performance changes across releases. I will repeat a disclaimer from my previous post, these results are from microbenchmarks. They are useful but be careful to assume these results will reproduce for your workload, especially if your queries are more complex than those used by sysbench. Summary: The regressions here are not as bad as in the in-memory workload. In many tests below the QPS in 8.0 is better than 5.6 because the new CPU overheads are less significant for IO-bound. I hope to repeat some of these tests to explain the odd results. Covered point queries with a secondary index use too much CPU and IO in 8.0. Covered range queries also have regressions in 8.0. But versions of those queries that don't use a

This post has results for InnoDB in MySQL 5.6, 5.7 and 8.0 using an in-memory & low-concurrency workload via sysbench. The setup is the same as I described for MyRocks. The goal is to understand where performance changes across releases and the in-memory workloads make it easier to spot CPU overhead. I will repeat a disclaimer from my previous post, these results are from microbenchmarks. They are useful but be careful to assume these results will reproduce for your workload, especially if your queries are more complex than those used by sysbench. Regardless, it is good for users and the environment to be careful about CPU efficiency in new versions of a popular DBMS. Updated results with improved my.cnf options are here . Summary: Things that might motivate a repeat Change my scripts so that InnoDB writeback is done before starting the tests. Set innodb_purge_threads=1 as the default value (4) caused mutex contention in the past on my NUC servers that have 4 CPU cores. Debug whe

This post has results for an IO-bound workload. The previous post has results for an in-memory workload.  Summary: Many of the regressions are explained by new CPU overhead. But there is also variance that is likely caused by stalls from RocksDB and my scripts don't do a good job of showing that.  In most cases, MyRocks in 8.0.17 gets between 80% and 100% of the throughput vs 5.6.35 Overview Things that are different for this benchmark from the previous post: The test table has 200M rows rather than 10M. Tests here are likely to be IO-bound. Each test step is run for 300 seconds rather than 90. The IO-bound results and shell scripts used to run the tests are  in github . Results The tests are in 4 groups based on the sequence in which they are run: read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.  The summaries focus on relative throughput and HW efficiency for the test with 1 thread although tests were run for 1, 2, 3 and 4 threads

I revived my sysbench helper scripts and have started to get results for MySQL (InnoDB, MyRocks) and Postgres. The results here are from all_small.sh which uses a few Lua files that are in my sysbench fork but have yet to be contributed. Tests were run on my home NUC servers for in-memory and IO-bound workloads. The results here are limited to the in-memory workloads. Disclaimer -- the tests here are microbenchmarks. While useful, be careful about assuming that the regressions here will reproduce on your workloads. The in-memory tests help me understand CPU overhead which tends to increase in newer releases. Summary: In most cases, MyRocks in 8.0.17 achieves between 75% and 100% of the throughput that it gets in 5.6.35. The regression tended to be larger for write-heavy tests than for read-only. Many of the regressions are explained by new CPU overhead. But there is also variance that is likely caused by stalls from RocksDB and my scripts don't do a good job of showing that. I d

I used the Insert Benchmark and a small server to compare performance across many (11, 18 & 17) different configurations for Postgres, InnoDB and MyRocks. The goal is to determine which options matter for this scenario (workload + HW). The goal isn't to compare Postgres vs InnoDB vs MyRocks. Results are here for Postgres , InnoDB and MyRocks . Conclusions are below. Some of these are specific to the workload + HW, so be careful about applying my findings.  For InnoDB It is hard to improve on innodb_dedicated_server . This is great news for deployments as there is less to tune. I hope the perf test community does more to confirm that across more workloads and HW. Performance with the minimal (default + a few things) configuration is lousy. The solution is to set innodb_dedicated_server. Performance with 4kb and 8kb pages is lousy except for QPS with more IO-bound database size where it is better. I don't understand the CPU overhead when it is lousy. QPS with the more IO-b

This presents performance and efficiency results for MyRocks on the  Insert Benchmark  using MySQL 5.6.35 and a  small server  with 4 CPU cores, 16G of RAM and a fast SSD. The goal is to understand the impact of my.cnf options and I have results for 17 different configurations.  Summary I use scripts to generate the performance reports. The reports are here for the  in-memory ,  less IO-bound  and  more IO-bound  database sizes. My summary of the results is below. Some of the conclusions are specific to this workload and HW so be careful about applying this advice. A summary of the results is below. This one is easy. Configurations have little impact on throughput for the in-memory and less IO-bound databases Throughput is lousy with the minimal configuration for the more IO-bound database size My focus in on throughput. There are larger impacts on HW efficiency metrics that I ignore. The impact of configuration should be more interesting for the benchmark run on larger HW with more co

This presents performance and efficiency results for Postgres on the  Insert Benchmark  using release 12.3 and a  small server  with 4 CPU cores, 16G of RAM and a fast SSD. The goal is to understand the impact of my.cnf options and I have results for 11 different configurations.  Summary I use scripts to generate the performance reports. The reports are here for the  in-memory ,  less IO-bound  and  more IO-bound  database sizes. My summary of the results is below. Some of the conclusions are specific to this workload and HW so be careful about applying this advice. I didn't try as many configurations for Postgres as I did for InnoDB and MyRocks. I am not sure that is because there is less tuning opportunity in Postgres. Notice that I didn't try to tune vacuum, which has many options, and instead my test scripts did a manual non-blocking vacuum after each test step. While the only writes in this test are inserts, vacuum is still needed to set bits in the visibility map. I think

This presents performance and efficiency results for InnoDB on the Insert Benchmark using MySQL 8.0.21 and a small server  with 4 CPU cores, 16G of RAM and a fast SSD. The goal is to understand the impact of my.cnf options and I have results for 18 different configurations.  Summary I use scripts to generate the performance reports. The reports are here for the in-memory , less IO-bound and more IO-bound database sizes. Some of the conclusions are specific to this workload and HW so be careful about applying this advice, but I suspect that innodb_dedicated_server should be used in most cases. A summary of the results is below.  It is hard to improve on innodb_dedicated_server . Much of the benefit for innodb_dedicated_server comes from using a large enough value for innodb_log_files_in_group. See the summaries for the in-memory , less IO-bound and more IO-bound database sizes. The minimal configuration, my.cnf.cx0, is lousy. This isn't a big deal because it is trivial to add i

Blind-writes for an LSM can be a challenge in the presence of secondary indexes. This post was inspired by an i nteresting but unpublished paper based on Tarantool and  a blog post on  SAI in DataStax Cassandra. Some of this restates ideas from the Tarantool paper. Contributions of this post are: Explains blind-writes Explains a new way to do blind-writes for some SQL-ish statements. The new way doesn't require readers to validate secondary index entries, which is required with the Tarantool approach. The new approach only works for some types of statements while the Tarantool approach had fewer limits. Longer introduction Non-unique secondary index maintenance is read-free for MyRocks during a write (insert, update, delete). By this I mean that nothing is read from the secondary index so there is no chance of doing a storage read during index maintenance. This makes secondary indexes cheaper with MyRocks than the typical DBMS that uses a B-Tree. The InnoDB change buffer also re