Small Datum

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. In this post I share results from sysbench with an IO-bound workload on Intel NUC servers. My previous post shared results from an in-memory workload. This is part of my work from bug 86215  as I identify CPU performance regressions from MySQL 5.6 to 5.7 and 8. tl;dr The results here are similar to, but not as bad as, the results from the in-memory workload . This is reasonable because CPU regressions can be hidden by IO-bound tests. But if you scroll to the bottom of this page and look at the graph for the point-query test you will see there are problems even for IO-bound tests. For the i5 NUC most of the regression is from 5.6 to 5.7 For the i3 NUC, MySQL 5.7 did better especially on range scans but there is still a regression from 5.6 to 8. From many tests I have run it looks like someone did great work in MySQL 5.7 to make range scans more efficient in InnoDB. For long

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. This continues my series on low-concurrency performance regressions,  Here I share results for in-memory sysbench using my home Intel NUC servers . The results were first shared in bug 86215 and the numbers for the charts in this post are here . tl;dr For the i5 NUC most of the regression is from MySQL 5.6 to 5.7 For the i3 NUC MySQL 5.7.17 matches 5.6.35 in many tests and does much better on range scans. Alas I have yet to debug this. But MySQL 8.0.1 is much worse than 5.6.35. At first I only had results for the i3 NUC and assumed the regression was from 5.7 to 8 but now I think that most of the regression comes from MySQL 5.6 to 5.7. Soon I will repeat these tests on servers I use at work that have a better CPU than what I get in the i5 NUC. For long range scans there is more overhead with the default charset/collation in MySQL 8.0.1 and switching back to latin1/latin1_s

It took me too long to figure this out while reading the chapter on PS in the MySQL manual. Hopefully this saves me time the next time I need to figure it out. I think it gives me the equivalent of the data I get from IS.user_statistics and IS.table_statistics when using FB MySQL . From a few tests I ran the overhead from the PS was small, maybe less than 5%, while collecting this data. Add performance_schema=1 to my.cnf For table stats: select * from table_io_waits_summary_by_table For user stats:   select * from events_statements_summary_by_account_by_event_name Update - great advice from Mark Leith for MySQL 5.7 and newer For table stats see the docs and run: SELECT * FROM sys.schema_table_statistics For user stats see the docs and run: SELECT * FROM sys.user_summary

Update  - the regression isn't as bad as I have been reporting. Read  this post  to understand why. What is a reasonable goal for performance regressions between major releases of MySQL (5.6, 5.7, 5.8)? Some regressions are expected because more features means longer code paths. But what if MySQL 5.7 and 5.8 only get 2/3 of the QPS versus MySQL 5.6 at low concurrency? The low-concurrency performance regressions from MySQL 5.6 to 5.7 continue in MySQL 8. That isn't a surprise for two reasons. First, more features usually make a DBMS slower. Second, all of the performance marketing for new MySQL releases focuses on high-concurrency workloads. One example of the regression occurs for in-memory sysbench where MySQL 5.7 and MySQL 8 get 60% to 70% of the QPS compared to 5.6, which is the same as writing that 5.6 gets 1.43X to 1.67X more QPS than MySQL 5.7 and MySQL 8. A deployment that I care about uses MySQL 5.6 and we want to use MySQL 8. Alas, we need to reduce the performa

MariaDB 10.2.6 is GA . It includes MyRocks , which isn't GA yet but is much easier to use thanks to the hard work of the MariaDB team. This is a big deal if you want performant efficiency  or efficient performance . For some workloads it provides better performance than InnoDB. For most (approximately all) workloads it uses much less space and writes much more efficiently. We continue to make RocksDB and MyRocks better and there are many interesting projects in progress. It runs in production, so Domas has begun finding even more things to improve. My contribution is performance evaluation and while I have been busy running tests I have fallen behind on sharing the results. I will do better over the next few months.

I use Intel NUC servers at home to test open source databases. I like them because they are small, quiet and don't use much power. For about 2 years I have been using NUC5i3ryh servers with a 5th gen core i3 CPU, 8gb of RAM, 2.5" SATA disk for the OS and 120gb Samsung 850 EVO m.2 for the database. I used this so much that I replaced the SSD devices last year after one reached the endurance limit. I am upgrading to a new setup using NUC7i5bnh . This has a 7th gen core i5, 16gb of RAM, 2.5" SATA SSD ( Samsung 850 EVO ) for the OS and m.2 SSD ( Samsung 960 EVO ) for the database. It has twice the RAM, twice the CPU and more than twice the IOPs of my old setup. The old and new setups use Ubuntu 16.04 server. First performance comparison is "make -j4" for MySQL 8.0.1 - 1307 seconds for old NUC, 684 seconds for new NUC. BIOS I disabled turbo mode in the BIOS on the NUC7i5bnh. There is no turbo mode on the NUC5i3rvh. This was done to avoid frequent variance i

I work with InnoDB and RocksDB storage engines for MySQL and do performance tests to compare both storage engines and storage devices. I have expertise in MySQL and storage engines but not so much in storage devices, so I don't mind running MySQL. Other people have expertise in the layers under MySQL (Linux, storage) and might mind running MySQL. Fortunately, we have benchmark clients for them. Obviously there is fio and it is my first choiceThe fio team even added support for coordinated omission when I asked for it. Alas it can't generate all of the IO patterns that I need. It would be great to share a link to io.go here were  Domas  to publish that code. I wrote innosim many years ago to simulate InnoDB IO patterns. Docs are here and a helper script to run a sequence of tests is here . Finally there is db_bench for RocksDB. One challenge with RocksDB is tuning, so I have a script to help with that and use good options to run a sequence of tests in a special pattern.

This post shows some of the improvements we recently made to RocksDB to reduce response time variance for write-heavy workloads. This work helps RocksDB, MyRocks and MongoRocks. This also extends the result I shared for the impact of the InnoDB redo log size on insert benchmark load throughout. Here I add results for MyRocks and TokuDB. In case you haven't heard, the goal for MyRocks is to provide similar performance to InnoDB with much better space and write efficiency. We have real workloads where InnoDB uses 4X more space than MyRocks. tl;dr Sorry for overusing the word much . Write response time variance is much better in recent RocksDB builds All engines have room for improvement to reduce write response time variance The MyRocks advantage increases when moving from faster to slower storage MyRocks insert rates are not slowed when compression is enabled. While the write rates I demonstrate here for all engines are impressive, the rates might not be sustainable if I

What is a typical InnoDB redo log size? By size I mean the product of innodb_log_file_size and innodb_log_files_in_group . A typical setup for me is 4gb via 2gb/file and 2 files. Using a large InnoDB redo log can help performance but it comes at a cost. I have been using a 4gb redo log in my tests because that is what we frequently use in production. Dimitri mentioned using a 32gb redo log to show great throughput for InnoDB in recent releases. Here I share results from the insert benchmark with a 4gb, 8gb, 16gb and 32gb redo log. tl;dr - conclusions specific to my test A larger redo log improves throughput A larger redo log helps more with slower storage than with faster storage because page writeback is more of a bottleneck with slower storage and a larger redo log reduces writeback. A larger redo log can help more when the working set is cached because there are no stalls from storage reads and storage writes are more likely to be a bottleneck. InnoDB in MySQL 5.7.17 is m