This section describes prerequisites and general best practices for MySQL database deployment on bare metal nodes.
MySQL deployment and recommendation
Follow these best practices to deploy MySQL:
- To install MySQL, see MySQL Installation.
- Ensure that the swap partition is disabled.
#swapoff -a
- Create dedicated volumes for MySQL data, binary, redo logs, and undo logs. The required MySQL configurations are updated in the my.cnf file, as shown in Appendix A.
MySQL recommendation for performance
The following parameters were configured in the MySQL my.cnf file based on the performance observed in the test setup:
- A large buffer pool was used for better performance. The buffer pool size was set to one-third of the MySQL database size.
- The double write buffer was disabled as PowerFlex provides data resiliency and protection at the storage layer.
- The InnoDB page size was set to 8 K as tests performed showed increased performance.
- The InnoDB read and write threads were set to the maximum value. Tune the inno_read_io_thread and the inno_write_io_thread until the pending IO request is almost zero. To determine the pending IO’s, run the following command:
SHOW ENGINE INNODB STATUS \G
- The InnoDB IO capacity was set to over 250,000 IOPs as the four-node PowerFlex system can achieve more than 250,000 IOPs.
PowerFlex best practices
Dell Technologies recommends the following best practices:
- Set jumbo frames on all ports across the compute and PowerFlex storage system.
- Set SDS threads to 12 for all the storage-only nodes:
$ scli --set_performance_parameters --all_sds --tech --sds_number_os_threads 12