Continuing my investigation of single-threaded performance in the MariaDB server, I managed to increase throughput of single-threaded read-only sysbench by more than 40% so far:

I use read-only sysbench 0.4.12 run like this:

    sysbench --num-threads=1 --test=oltp --oltp-test-mode=simple --oltp-read-only --oltp-skip-trx run

And mysqld is run with minimal options:

    sql/mysqld --no-defaults --basedir=X --datadir=Y --innodb-buffer-pool-size=128M

With modern high-performance CPUs, it is necessary to do detailed measurements using the built-in performance counters in order to get any kind of understanding of how an application performs and what the bottlenecks are. Forget about looking at the code and counting instructions or cycles as we did in the old days. It no longer works, not even to within an order of magnitude.

This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another "virtual" list, the auto-commit non-locking  read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write transaction list and so that is not too expensive and unavoidable. There is another …

Benchmarking, and benchmarking the cloud, is incredibly error prone. I provided guidance though this minefield in the benchmarking chapter of my book (Systems Performance: Enterprise and the Cloud); that chapter can be read online on the InformIT site. I also gave a lightning talk about benchmarking gone wrong at Surge last year. In this post, I’m going to cut to the chase and show you the tools I commonly use for basic cloud benchmarking.

As explained in the benchmarking chapter, I do not run these tools passively. I perform Active Benchmarking, where I use a …

The performance of the mysqldbcopy, mysqldbexport, and mysqldbimport utilities has been optimized in MySQL Utilities 1.3.6. In the case of export/import there have been significant improvements. In particular, multiprocessing support has been added to these utilities and can be enabled with the new --multiprocess option. The option permits concurrent execution and makes the most of the CPU resources available (number of cores).

Multiprocessing is applied at different levels according to the operating system. For non-POSIX systems, multiprocessing is limited to the database-level whereas POSIX systems can make multiprocess at the table level.

More specifically, the mysqldbcopy and mysqldbexport utilities allow multiprocessing at the table level for non- Windows systems and database level for Windows system. The mysqldbimport utility allows multiprocessing at the file level independently from the OS.

…

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1; …

This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another “virtual” list, the auto-commit non-locking read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write transaction list and so that is not too expensive and is unavoidable. There is another …

In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:

• On one hand, I have interacted countless times with users who want to use a replication system as a drop-in replacement for a single server. In many cases, especially when users are dealing with applications that are not much flexible or modular, this means that the replication system must have several points of data entry, and such points must work independently and in symbiosis with the rest of the nodes.
• On the other hand, I am a technology lover (look it up in the dictionary: it is spelled geek), and as such I get my curiosity stirred whenever I discover a new possibility of implementing multi-master systems.

The double nature of this professional curiosity makes me sometimes forget that the …

If the performance of your MySQL servers is important to you, performance tuning is key.

In the 4-day MySQL Performance Tuning course, you learn to analyze and optimize the performance of the MySQL Database, using the tools necessary for monitoring, evaluating and tuning.

You can take this course as a:

• Training-on-Demand event: Start training within 24 hours of registration and take this training at your own pace.
• Live-Virtual event: Attend a live event from your own desk - no travel required. You can choose from a selection of events already on the schedule.
• In-Class event: Travel to an education center to take this training. Below is a selection of the events already on the schedule. …

This week we discuss test data sets. Ear Candy is errata from Episode 165 on Galera Cluster, and At the Movies is "Runaway Complexity in Big Data"

Test Data Sets
Episode 81, where we talked about different benchmark tools
Sysbench - general version
Percona's sysbench tests

Factors: # tables, concurrency, data set size

Episode 143, where we did an ear candy featuring Morgan Tocker's article on estimating MySQL's working set with INFORMATION_SCHEMA

…

As you probably already know, in MySQL 5.7.3 release, InnoDB Memcached reached a record of over 1 million QPS on read only workload. The overview of the benchmark and testing results can be seen in an earlier blog by Dimitri. In this blog, I will spend sometime on the detail changes we have made to achieve this record number.

First thanks to Facebook’s Yoshinori with his bug#70172 that brought our attention to this single commit read only load test. We have been focussing on operation with large batch size. This bug prompted us to do a series of optimization on single commit read only queries and these optimizations eliminated almost all major bottlenecks from the InnoDB Memcached plugin itself.

If you are just …