MySQL – Why Write IOPS, Throughput, Queue Depth Decreased on AWS/RDS

amazon-rdsinnodbMySQLperformanceperformance-tuning

I changed values for my Mysql parameters

1. innodb_flush_log_at_trx_commit=2(before it was 1)

2. sync_binlog=0(before it was 1)

This has changed some of my parameters on AW RDS drastically ,here are some of them.

Write Operations(Count/sec)

enter image description here

Queue Depth

enter image description here

My question is ,What can i interpret from the above results ? Is is good for my database ? Is the performance increased ?

Best Answer

Both of those changes improved throughput, but decreased robustness.

innodb_flush_log_at_trx_commit=1 says to flush stuff to disk at every COMMIT, thereby making your database fully crash-proof. =2 risks one second's worth of COMMITs.

If you have the binlog turned on, sync_binlog=1 says to flush to disk at every COMMIT. =0 allows delaying (buffering) the writes. This variable is somewhat important if you have Slaves and/or do incremental backups.

Related Question