How to optimize your MySQL server running on a Ec2

 As a database engineer, I need to perform some DBA tasks, and accessing the desk and running some old-style scripts is still required. Besides the best solution if you are living in the cloud is to use some compute instances. 

AWS EC2 comes in handy, however, to get the best out of your instance, you should tune your EBS volume and your instance to increase speed, reliability and fault-tolerant

We have an external replication stream from an Aurora cluster to the MySQL server version 5.7.29

The ec2 instance used is 

Instance size m5.2xlarge

Maximum bandwidth (Mbps) 1,000

Maximum throughput (MB/s, 128 KiB I/O) 125

Maximum IOPS (16 KiB I/O) 8,000

Following are the best settings that I tuned to get the best performance out of the server  

MySQL variable configurations 

slave-51> select @@innodb_flush_log_at_trx_commit, @@long_query_time,

@@innodb_flush_sync, @@innodb_flush_method, @@innodb_io_capacity, @@log_bin,

@@innodb_lru_scan_depth, @@innodb_buffer_pool_size/1024/1024, @@innodb_flush_neighbors,

@@innodb_log_file_size, @@table_open_cache_instances, @@innodb_page_size, @@sync_binlog\G

*************************** 1. row ***************************

@@innodb_flush_log_at_trx_commit: 0

@@long_query_time: 0.000000

@@innodb_flush_sync: 0 # OFF

@@innodb_flush_method: O_DIRECT

@@innodb_io_capacity: 20000

@@log_bin: 0

@@innodb_lru_scan_depth: 1024

@@innodb_buffer_pool_size_GB: 52

@@innodb_flush_neighbors: 0

@@innodb_log_file_size: 1073741824

@@table_open_cache_instances: 16

@@innodb_page_size: 16384

@@sync_binlog: 0 ## OFF

* How to get the volume block size

blockdev --getbsz /dev/xvdg

4096select @@innodb_page_size;

* Which is the MySQL page size

innodb_page_size is default 16KB for all the DBs

System variables

• The innodb_flush_sync variable, which is enabled by default, causes the innodb_io_capacity setting to be ignored during bursts of I/O activity that occur at checkpoints. To adhere to the I/O rate defined by the innodb_io_capacity setting, disable innodb_flush_sync

• sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.

sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.

• innodb_log_compressed_pages is enabled by default to prevent corruption that could occur if a different version of the zlib compression algorithm is used during recovery. If you are certain that the zlib version is not subject to change, you can disable innodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.

How was the performances tests done

• IOSTAT
• IFTOP
• Is the audit_log on the server? - Disable
• is the perf_schema on? - Disable
• Run i/o disk performance test sudo fio --directory=/mnt/db_main/stress-test/ --name fio_test_file --direct=1 --rw=randwrite --bs=16k --size=1G --numjobs=16 --time_based --runtime=900 --group_reporting --norandommap