MySQL comes with a strong caching mechanism that correct usage has dramatic impact on the the database performance. But it's not a plug and play functionality and some configuration is required. Below I'll show you how important this configuration is and what parameters you should set to get satisfying results.
Although this configuration is very simple it's not for everyone. In case your host has less than 1G of RAM you shouldn't change the default MySQL config because it's going to damage the performance instead of improving it. My report is tested on a 12G RAM, 4 core CPU virtual machine and I choose the numbers that fit my system. In order find out what's best for you I recommend checking the following link: Calculating InnoDB Buffer Pool Size For Your MySQL Server
Testing properly allow you to witness the improvements, therefore you should install the right tools for testing. SysBench is a command-line test tool with great testing functionalities for relational databases including MySQL and Postgres.
Add the Sysbench repository:
wget -qO - https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
Install Sysbench:
sudo apt install -y sysbench
When installation is completed, we can check Sysbench version:
sysbench --version
Testing Sysbench The following command can be used to evaluate the CPU performance:
sysbench cpu run
Sysbench offers the ability to tweak the tests by writing scripts in lua. But as it's a time consuming process, there's also some template tests for RDBSM services that is good enough for our need. The template files can be found in /usr/share/sysbench/.
In this report we will only conver reading only tests which is the process of executing SELECT queries thousands of times on thousands of database records.
This command prepares the database, meaning it builds the tables and fill it with random data. Before running check the parameters match your instance.
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=4 --mysql-host=<HOST> --mysql-user=<USER> --mysql-password=<PASSWORD> --mysql-port=3306 --tables=10 --table-size=100000 prepare
This command runs the actual test. You can either adjust the --events or the --time to indicate how long this test should keep runnning.
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16 --events=0 --time=10 --mysql-host=<HOST> --mysql-user=<USER> --mysql-password=<PASSOWRD> --mysql-port=3306 --tables=10 --table-size=100000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
At the end of the you'll see a report of the test.
SQL statistics:
queries performed:
read: 58640
write: 0
other: 11728
total: 70368
transactions: 5864 (585.27 per sec.)
queries: 70368 (7023.20 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0181s
total number of events: 5864
Latency (ms):
min: 1.88
avg: 27.31
max: 300.70
95th percentile: 86.00
sum: 160129.24
Threads fairness:
events (avg/stddev): 366.5000/11.86
execution time (avg/stddev): 10.0081/0.01
As you can see my test queries took a long time to process.it has the average of 27ms per query and 95% of where between 1 and 86ms. now let's improve it.
In order to improve the performance you should increase the innodb-buffer-pool-size which expands the limitation on memory usage by InnoDB buffer. This means that the database will use more ram than before an faster responses. Also there's another parameter innodb-buffer-pool-instances that you should increase when you're gonna allocate more than 1G of RAM to innodb-buffer-pool-size (default value is 1).
So open the my.cnf file (usualy placed in /etc/mysql/) and set the following parametes.
[mysqld]
innodb-buffer-pool-size=9G
innodb-buffer-pool-instances=16
restart MySQL instance and verify the parameters are set correctly by running follwing queries:
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
select @@innodb_buffer_pool_instances;
now let's test them one more time. You don't need to perpare the database again as it's already prepared. So just execute the run command one more time.
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16 --events=0 --time=10 --mysql-host=<HOST> --mysql-user=<USER> --mysql-password=<PASSOWRD> --mysql-port=3306 --tables=10 --
table-size=100000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
results:
SQL statistics:
queries performed:
read: 251410
write: 0
other: 50282
total: 301692
transactions: 25141 (2511.73 per sec.)
queries: 301692 (30140.71 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0082s
total number of events: 25141
Latency (ms):
min: 1.70
avg: 6.37
max: 49.95
95th percentile: 13.95
sum: 160031.23
Threads fairness:
events (avg/stddev): 1571.3125/139.90
execution time (avg/stddev): 10.0020/0.00
As you can see the average time dropped from 27ms to 6ms and 95% of the queries are done in less than 13 milliseconds.
Although this configuration is simple it has a great impact on database performance. I hope you've found my report useful. Wish you all the bests.
Configuring InnoDB Buffer Pool Size
Calculating InnoDB Buffer Pool Size for your MySQL Server
How to Benchmark Performance of MySQL & MariaDB Using SysBench