Created
December 28, 2022 08:14
-
-
Save AminAzGol/7b0e7ad1917567e198caaf2adbf0e9db to your computer and use it in GitHub Desktop.
Revisions
-
AminAzGol created this gist
Dec 28, 2022 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,140 @@ # Improve MySQL performance by increasing RAM usage 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. # Who should do it? 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](https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/) # Install SysBench 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 ``` # Start testing MySQL 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/`. ## Read only test 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. ### Prepare 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 ``` ### Run 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. # Improving performance 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. # Conclusion 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. # References [Configuring InnoDB Buffer Pool Size](https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html) [Calculating InnoDB Buffer Pool Size for your MySQL Server](https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/) [How to Benchmark Performance of MySQL & MariaDB Using SysBench](https://severalnines.com/blog/how-benchmark-performance-mysql-mariadb-using-sysbench/)