According to DB-Engines (https://db-engines.com/en/ranking) MySQL is the second most popular database. MySQL is highly portable with binaries available for Microsoft Windows™, Apple MacOS™, various Unix flavors and Linux distributions. MySQL database is well suited for deployments on shared storage block devices such as Pure Storage® FlashArray™ where high I/O performance is required and advanced data services provide fast and convenient means of database protection, cloning and recovery - for details see MySQL Database Cloning With FlashArray Copy and Protecting MySQL Database With FlashArray Snapshots articles.
For database administrators, MySQL offers a rich set of configuration parameters to manage and improve many aspects of the application behavior and performance. As mentioned earlier, MySQL is available on multiple operating systems but the majority of the database installations are on Linux. The objective of the test was to determine performance impact of the database parameters changes rather than focus on an absolute performance results. This article provides suggested MySQL configuration values for databases located on FlashArray volumes and are based on the tests executed at Pure Storage laboratories.
Establish general performance reference point by monitoring and collecting performance data.
The test bed was constructed with a single server running Linux CentOS 7.7 and FlashArray //X. The MySQL version 8.0.19 (with InnoDB engine) host was equipped with a 25 Gb/s dual port Ethernet device for NVMe-oF RoCE server to FlashArray connectivity. Each FlashArray //X controller also had a single, dual port 50 Gb/s NVMe-OF RoCE interface - see Figure 1.
FlashArray also supports other protocols such as Fibre Channel and iSCSI.
For host to storage connectivity see the output of nvme list-subsys and multipath -ll commands shown in Figure 2 and Figure 3.
Host ports were connected to both FlashArray controllers resulting in four paths to the disk. See Figure 4.
The recommended operating system FlashArray settings for Windows are available in the Microsoft Platform Guide. Linux recommended settings are listed in the Linux Reference section as well as NVMe RoCE Initiator Setup. Additionally, Linux HugeTLB memory pool was enabled and configured - see https://dev.mysql.com/doc/refman/8.0/en/large-page-support.html article on how to properly enable and utilize Linux large page support.
Placing data files and logs on different devices and volumes has been a long established practice for relational database management systems. Separating those volumes may be desirable when more granular storage control is required. Multiple volumes allow different snapshot and replication schedules, Quality of Service (QoS) settings and may ease disaster recovery operations. During our testing, separating data and log volumes on FlashArray had not resulted in improved database performance.
The XFS file system has become the de-facto standard for the majority of Linux distributions. XFS offers multiple mount options with nobarrier and noatime frequently cited as possible settings to improve the file system and consequently database performance. In our tests, mounting database volumes with nobarrier and noatime options has not delivered measurable performance improvement.
All the tests were executed using sysbench version 1.0.19. Sysbench is a freely available performance benchmarking tool with MySQL driver already compiled-in. The tool is also scriptable and multiple database workloads are readily available. For this set of tests, a tpcc-like set of scripts from Percona (https://github.com/Percona-Lab/sysbench-tpcc) was configured.
Running the benchmark consists of the following steps:
From mysql prompt, run ‘create database <database_name>;’ command
Prepare Tables and Data
From shell, run ‘sysbench <path_to_tpcc.lua>/tppc.lua --threads=<threads> --tables=10 --scale=100 --db-driver=mysql --mysql-db=<database_name> --mysql-user=<mysql_user> --mysql-password=<password> prepare'
/usr/bin/sysbench /usr/share/sysbench/tpcc.lua --threads=48 --tables=10 --scale=100 --db-driver=mysql --mysql-db=sysbench10x100W --mysql-user=admin --mysql-password=’password’ prepare
From shell, run ‘sysbench <path_to_tpcc.lua>/tppc.lua --threads=<threads> --time=<run_time[s]> --tables=<number_of_tables> --scale=<number_of_warehouses> --db-driver=mysql --mysql-db=<database_name> --mysql-user=<mysql_user> --mysql-password=<password> run > <output_file>'
/usr/bin/sysbench /usr/share/sysbench/tpcc.lua --threads=48 --time=300 --tables=10 --scale=100 --db-driver=mysql --mysql-db=sysbench10x100W --mysql-user=admin --mysql-password=’password’ run > ~/results/5min/r3/32GBP10x100WFA48.out
Our test database consisted of 100 warehouses with 10 tpcc datasets. The test set included 11 runs for 8, 16, 24, 32, 48, 64, 96, 128, 256, 512, 1024 threads (users) to better estimate the impact of configuration changes. Each sysbench test ran for 5 minutes on MySQL server. There was a 30 seconds sleep time (pause) between the runs. Sample sysbench output is shown in Appendix A.
MySQL Server Configuration
The size of the sysbench tpcc-like generated database (10 sets with 100 warehouses) was approximately 100 GB. To simulate IO bound database server MySQL buffer pool was set to 32 GB.
By default, MySQL database configuration parameters are in the /etc/my.cnf file - see sample my.cnf in Appendix B.
Default (included with installation) /etc/my.cnf file is NOT suitable for production level MySQL and FlashArray deployments.
Changing some of the MySQL server database parameters may improve performance at the expense of transaction atomicity and data recoverability.
Understand the parameters, their values and potential impact before making any changes.
Use test systems for configuration changes before applying changes to production systems.
The MySQL parameters with potential IO impact and the suggested values are listed in Table 1.
|Parameter Name||Description||Default Value||Suggested Value|
|innodb_checksum_algorithm||Tablespace page checksum algorithm.||crc32||crc32|
|innodb_doublewrite||Write data to doublewrite buffer before writing to data file.||On||
Data integrity - set to On
Performance - set Off
|innodb_flush_log_at_trx_commit||Controls log flushing and writing.||1||
Data integrity - set to 1
Performance - set to 2
|innodb_flush_neighbors||Same extent buffer pool and other dirty pages flushing. Extent is a group of pages within a tablespace.||0 (zero) - disabled)||0 (zero) - disabled|
Data (buffer pool) and log files (redo, undo) write to disk (flush) method; O_DIRECT is used during flushing IO skipping fsync() after each write operation.
|innodb_idle_flush_pct||Buffer pool flushing during idle period as a percentage of innodb_io_capacity. Value of 100 does not limit page flushing when InnoDB is idle.||100||100|
|innodb_io_capacity||Number of available IOPS to background tasks (dirty page flushing, merging data from the change buffer)||200||≥ 10000|
|innodb_io_capacity_max||Number of maximum available IOPS to background tasks.||100||≥ 20000|
|innodb_log_compressed_pages||Redo log page compression (for table compression only)||On||Off|
|innodb_log_file_size||Size of the log file.||50 MB (503331648 bytes)||≥ 1 GB (see Notes)|
|innodb_log_files_in_group||Number of log files in the log group.||2||≥16 (see Notes)|
|innodb_use_native_io||Use asynchronous I/O - Linux only.||On||On|
The size and number of MySQL redo logs are controlled by innodb_log_file_size and innodb_log_files_in_group parameters. The size of the redo log is a product of innodb_log_file_size and innodb_log_files_in_group values which should be less than 512 GB. By default, there are two 48 GB redo log files in a group for a total of 96 GB. The size of redo log files can be as large as the buffer pool, not to exceed 512 GB. Also, configuring large redo log files may reduce the checkpoint frequency. The general recommendation is to have enough redo log space available to handle more than an hour of write activity. On the other hand, configuring large log files will result in increased crash recovery time.
FlashArray with its ease of use, high performance and rich set of data services becomes an excellent storage choice for MySQL database. Designing and configuring database solutions is frequently a compromise between data integrity, simplicity of management and performance. With FlashArray and MySQL, developing a robust, highly performant environment is now achievable.
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 96
Initializing random number generator from current time
Initializing worker threads...
transactions: 3958982 (13194.65 per sec.)
queries: 112478032 (374871.13 per sec.)
ignored errors: 17306 (57.68 per sec.)
reconnects: 0 (0.00 per sec.)
total time: 300.0426s
total number of events: 3958982
95th percentile: 16.12
events (avg/stddev): 41239.3958/143.69
execution time (avg/stddev): 299.9085/0.01
# For advice on how to change settings please see
# performance special
© 2019 Pure Storage, the Pure P Logo, and the marks on the Pure Trademark List at https://www.purestorage.com/legal/pr...duserinfo.html are trademarks of Pure Storage, Inc. Other names are trademarks of their respective owners.
THIS DOCUMENTATION IS PROVIDED "AS IS" AND ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT, ARE DISCLAIMED, EXCEPT TO THE EXTENT THAT SUCH DISCLAIMERS ARE HELD TO BE LEGALLY INVALID. PURE STORAGE SHALL NOT BE LIABLE FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES IN CONNECTION WITH THE FURNISHING, PERFORMANCE, OR USE OF THIS DOCUMENTATION. THE INFORMATION CONTAINED IN THIS DOCUMENTATION IS SUBJECT TO CHANGE WITHOUT NOTICE.