For Percona MySQL which is preferred the file system

General Questions
1

Hi All,

Currently we are running on Ext4 file system but we can see the journal (jdb2) process is adding significant CPU. Also we can experience high IO on the system. We are planning to move to SSD with Percona MySQL 5.6.40 (innodb engine) and would like to know which file system is best suited for a busy server with 1.6 TB of data and an average data growth of 2 GB per day (high read and write).

Regards,
Johnson

2

You should be fine with Ext4, but some may prefer XFS for heavy loaded systems

3

Vadimtk, Thanks for the reply.

If I am going with Ext4 do I need to add any option in the file system for better performance. I am using the default now.

/dev/mapper/applvg-xxxx /var/opt/data/xxx ext4 defaults 1 2

Also for minimizing the IO do I need to consider the below parameters.

sync_binlog = 1 (1 → 0)
innodb_adaptive_flushing = 1
innodb_flush_log_at_trx_commit = 1 ( 1–> 2 )
innodb_flush_method = O_DIRECT

Regards,
Johnson

4

For ext4 - if you are running of physical SSD, I’d recommend to use discard option to format and mount ext4 filesystem.

For InnoDB - set innodb_io_capacity to 2500 and innodb_io_capacity_max to 4500

as for sync_binlog=0 and innodb_flush_log_at_trx_commit = 2 - you can do that, but you risk your database durability. if you are fine with that, you can make these changes.
But really the best what else you could do is to upgrade from 5.6 to 5.7

5

Thank you for your detailed reply.

​​​​​​What I can gain by upgrading to 5.7 from. 5.6 in terms of disk performance and overall performance

Regards,
Johnson