Recommended Configuration for a Parts Altium Database Library Hosted on AWS
Source AWS: DB instance classes - Amazon Relational Database Service
For best performance MySQL MUST be configured and tuned.
Example innodb configuration:
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size'; 134217728 128Mbits (Default)
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'; 8 Pool Instances
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 1073741824 1GBits (8x Chunk Size)
SHOW VARIABLES LIKE 'innodb_log_file_size'; 134217728 128Mbits
SHOW VARIABLES LIKE 'innodb_flush_method'; O_DIRECT
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; 1 Faster = 0 or 2
SHOW VARIABLES LIKE 'innodb_file_per_table'; On
SHOW VARIABLES LIKE 'innodb_stats_on_metadata'; Off
SHOW VARIABLES LIKE 'innodb_io_capacity'; 200
SHOW VARIABLES LIKE 'innodb_io_capacity_max'; 2000
SHOW VARIABLES LIKE 'innodb_log_buffer_size'; 8388608 8Mbits
Warning. When you change innodb_log_file_size option you have to remove old log files when restarting server:
rm /var/lib/mysql/ib_logfile*
service mysql start
Source: Tuning Mysql 8.0 server for performance | by Denys Golotiuk | DataDenys | Medium
Related Links:
Ten MySQL performance tuning settings after installation Percona
MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.1 Configuring InnoDB Buffer Pool Size
MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.2 Configuring Multiple Buffer Pool Instances
MySQL Performance Tuning 101: Key Tips to Improve MySQL Database Performance
Bard - MySQL Performance on AWS
Bard - MySQL Performance on Azure
vCores: The number of virtual CPUs assigned to the server. More vCores generally mean better performance.
Memory: Available RAM affects query execution, caching, and overall performance.
IOPS: Input/output operations per second represent storage performance. Higher IOPS support faster data access.
Defer disk writes by flushing the log less frequently
By default MySQL will write the log to disk after every single transaction. In our case, it wasn't possible to wrap batches of inserts in transactions, so every single insert query was causing a disk write.You can instead write the log to disk at an interval by setting innodb_flush_log_at_trx_commit to 2. The main issue with setting this away from the default of 1 is possible data loss. While you're still protected from MySQL crashes causing data loss, the entire server loosing power would mean potentially losing data. For example, if you have the flush interval set to the default of 1, you could lose a seconds worth of writes upon failure of the server.
By setting innodb_flush_log_at_trx_commit to 2, we were able to drop the insert time average from over 200ms to under 50ms. While this is a pretty massive improvement, it's possible to squeeze even more speed if you are willing to risk a few more seconds of potential data loss.
You can increase the flush interval by setting innodb_flush_log_at_timeout to your desired interval (in seconds). In our case, we went with 5 seconds, which resulted in the insert time average dropping under 5ms! A massive difference from the original 250ms, and in our case worth the risk of 5 seconds of potential data loss.
Source: RDS MySQL Performance Tuning - Speeding up slow inserts - atymic.dev
No comments:
Post a Comment