0

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 ?

1 Answer 1

1

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.

4
  • There is a drastic decrease in the write operations .Is it good/bad for my DB performance ? Commented Mar 3, 2016 at 5:47
  • Define "performance". It seemed that performance was OK before. Or was it sluggish? If anything, these changes allow you to have many more connections doing more stuff. The main slowdown in MySQL is I/O; the changes decreased the I/O.
    – Rick James
    Commented Mar 3, 2016 at 5:50
  • By database performance I mean , the optimization of resource used to increase throughput and minimize contention, enabling the largest possible workload to be processed. Commented Mar 7, 2016 at 8:37
  • The setting change led to optimization of the I/O resource. So, from that point of view, a greater workload can be handled. Of course, some other bottleneck should show up next. Still, as your graphs show, there is a significant improvement.
    – Rick James
    Commented Mar 7, 2016 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.