MySQL performance issues with occasional complete freezes

MySQLmysql-5.7optimizationperformance

we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes are used. Furthermore, once a week or so, MySQL completely stops for no particular reason found in the log files. To be honest, we have not spent much time trying out different settings, but we tried tweaking a few with no success.

After some investigation, a few months ago we had to change some of the default settings, and that looked like it was good, but not for long. Our current my.cnf:

[mysqld]

  • innodb_buffer_pool_size=12288M # this
  • innodb_lock_wait_timeout=120 # and this
  • sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # per design

After examining performance_schema, I am pretty sure some of these values are bad, but because of some unclear explanations for on MySQL docs and here, I am not sure which should be changed.

E.g. Created_tmp_tables is currently at 86331 with Created_tmp_disk_tables 25743, which seems very high. MySQL documentation states that increasing tmp_table_size and max_heap_table_size should help, but many answers say that that would only hide other issues behind this.

As we are all new to MySQL optimization, I would be grateful if someone could just give us some points in which direction we should look.

Here are global_status and global_variables entries.

Our current system spec is 8 physical CPUs and 64GB of RAM, with SSD discs. MySQL version is 5.7.33.

Best Answer

Rate Per Second = RPS

From the available (very incomplete) global status and global variables posted, please consider the following suggestions to improve performance.

innodb_log_file_size=512M  # from ~ 50M for log rotation reduction from 6 minutes to about 30 minutes.
innodb_log_buffer_size=256M  # from 16M for ~ 30 minutes RAM storage before write required
innodb_buffer-pool_size=40G  # from 12G to reduce innodb_data_reads RPS of 934
thread_cache_size=100  # from 8 to reduce threads_created count and overhead required
tmp_table_size=128M  # from 16M to expand capacity of in RAM tmp tables
max_heap_table_size=128M  # match tmp_table_size and reduce created_tmp_disk_tables RPhr of 1371

Observation, select_scan RPS of 37 indicates additional indexes would reduce table scans. Use your slow query log and EXPLAIN SELECT xxxxx to assist with finding table and columns missing indexes.

Posting more complete data to pastebin.com would allow more complete analysis and there are many more Global Variables that could enhance speed of instance.

Related Question