How to benchmark MariaDB and MySQL performance

Benchmarking your MariaDB or MySQL server is essential for assessing its performance and stability under load. It helps identify hardware limitations, misconfigurations, or performance bottlenecks that may affect your server’s ability to handle real-world demands. Regular benchmarking allows for ongoing performance tuning and informs decisions about necessary upgrades.

sysbench is a widely-used, open-source tool for testing various aspects of server performance. It can simulate workloads across several components, including CPU, memory, and I/O. Its primary use in this context is to stress test MariaDB and MySQL servers, providing insights into how the database interacts with both the hardware and the operating system. This makes it an effective tool for evaluating server performance and system behavior under pressure.

When running benchmarks, it is important to recognize that sysbench provides a synthetic load. This load is useful for consistent comparisons between different systems or configurations. However, real-world workloads may differ significantly. It’s advisable to combine synthetic benchmarks with actual workload testing for a more complete analysis of your database performance.

1. Install sysbench on your server.

   $ sudo apt-get install sysbench

   Use this command for Ubuntu and Debian-based systems.

2. Set up a test database and table for benchmarking.

   $ sysbench --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=password oltp_read_write prepare

   This command prepares the benchmark by creating a test database named 'test' and filling it with sample data. Replace 'root' and 'password' with your actual credentials.

3. Run the benchmark to evaluate performance.

   $ sysbench --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=password --time=60 --threads=10 --report-interval=10 oltp_read_write run
   SQL statistics:
       queries performed:
           read:                            70000
           write:                           20000
       transactions:                        5000   (83.33 per sec.)
       latency (ms, avg):                   120.00

   This runs the benchmark for 60 seconds using 10 threads, with a performance report every 10 seconds. Adjust --time and --threads as necessary based on your environment.

4. Analyze the benchmark results.

   Focus on the number of transactions, average latency, and the overall throughput per second to assess the server's performance.

5. Remove the test database and cleanup after benchmarking.

   $ sysbench --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=password oltp_read_write cleanup

   This removes the test data created during the benchmarking process.

6. Compare results with previous benchmarks or performance baselines.

   Keep a record of previous benchmarks for future comparisons. This helps track performance improvements or identify regressions.