Perform the following best practices for the MariaDB Server:
- Use dedicated volumes for MariaDB data, log, and temp database. For more information about changing default directories, see MariaDB documentation specifying datadir, tmpdir, and individual InnoDB data files.
- Use a large buffer pool. For this solution, the buffer pool was sized to be a third of the database size.
- Set InnoDB page size to 8 KB. Our experiments showed that setting this value any higher decreases performance.
- Disable doublewrite buffer as PowerFlex provides data resiliency and protection. While the doublewrite buffer is ON, pages are written to the doublewrite buffer first when InnoDB performs page flush. This facility improves fault tolerance and disabling the doublewrite buffer increases performance.
- Tune innodb_read_io_threads and innodb_write_io_threads until pending IO requests are zero most of the time. The pending number of IOs can be obtained by checking the InnoDB status as follows:
show engine innodb status \G
- Set InnoDB IO capacity to be around the number of IOPS that can be handled by the system. The four node PowerFlex system that is used in this solution is capable of achieving 200,000+ IOPS under submillisecond latency. Hence this value is set to 200,000.
- Properly stage the database for performing backup operations using BACKUP STAGE commands. For more information about Instructions for preparing the database, see the Snapshots section.