Amazon RDS instances are convenient and scalable. However, whilst running long-running workflows with large datasets, the servers can come under considerable strain at times. Sometimes, we have noticed that certain transactions return with an error – not because something is wrong with the transaction syntax, rather that the MySQL server believes that the transaction is taking too long. This behaviour appears to be specific to MySQL – such behaviour has not been observed with other RDBMS software, such as PostgreSQL.
- Increase max_allowed_packet from the default 1MB to 512MB. Some databases (including various Microbase tables) make use of the MySQL BLOB type. In order to send large BLOBs over the network, this value needs to be set larger than the default.
- Increase innodb_lock_wait_timeout – the default value is 50 seconds. Increase lock wait time to a maximum of 3600. Useful for long-running transactions.
- Increase net_write_timeout – the default value is 60. Increases the timeout to 300 seconds. Allows for large or congested network transfers.
rds-modify-db-parameter-group microbase --parameters "name=max_allowed_packet, value=536870912, method=immediate"
rds-modify-db-parameter-group microbase --parameters "name=innodb_lock_wait_timeout, value=3600, method=pending-reboot"
rds-modify-db-parameter-group microbase --parameters "name=net_write_timeout, value=300, method=immediate"
rds-modify-db-parameter-group microbase --parameters "name=innodb_flush_log_at_trx_commit, value=0, method=immediate"
rds-modify-db-parameter-group microbase --parameters "name=sync_binlog, value=0, method=immediate"
- innodb_flush_log_at_trx_commit – sets the behaviour of syncing on commit. The default value is 1(?). There are several possible values:
- 0 – if MySQL crashes, last second of txns lost;
- 1 – flush to disk each update/commit;
- 2 – flush to OS (if OS crashes, 1 sec txns are lost)
- sync_binlog – sets when the transaction log is synchronised. Default is 0, but we set it here to make sure.
- 0 – does no synchronising. If the DB crashes, you will lose whatever transaction log entries were in memory waiting to be written to disk.
- 1 – synchronises after every write. You lose at most 1 statement from the binary log in the event of a crash.
- October 23, 2015 @ 18:14:36 [Current Revision] by admin
- October 23, 2015 @ 18:14:36 by admin