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:
- 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.
Our current system spec is 8 physical CPUs and 64GB of RAM, with SSD discs. MySQL version is 5.7.33.