Tuning a database that's slow only during certain days during peak hours

Question

General recommendations:
    Check warning line(s) in /var/lib/mysql/web11.hgregoire.com.err file
    Check error line(s) in /var/lib/mysql/web11.hgregoire.com.err file
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    1 CVE(s) found for your MySQL release. Consider upgrading your version !
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 2M, or use smaller result sets)
    query_cache_size (> 1M)
    join_buffer_size (> 512.0K, or always use indexes with JOINs)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

I ran MYSQLTuner and got these recommendations. Now, the issue is that I keep increasing query_cache_limit, query_cache_size and join_buffer_size and yet MYSQLTuner keeps telling me to increase it, so is there a way to calculate the amount needed to make cache more efficient? What can I do to get the information I need for this?

Another thing is that I am told to increase max_connections, but it's already very high, so I am not sure it's a good idea. Also, it tells me to decrease wait_timeout, but I've read that if you decrease it to 300, the resource needed to reopen the connection may slow the server even more, so I am not sure if there's a way to check if a reduction in wait_timeout will amount to a performance boost.

[--] Up for: 8d 23h 27m 48s (186M q [239.830 qps], 5M conn, TX: 348G, RX: 35G)
[--] Reads / Writes: 75% / 25%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 56.5G
[--] Other process memory: 0B
[--] Total buffers: 18.3G global + 259.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 56.8G (181.00% of installed RAM)
[!!] Maximum possible memory usage: 56.5G (180.19% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (788K/186M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.01%  (456/5515185)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 12.7% (18M cached / 143M selects)
[!!] Query cache prunes per day: 6005371
[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 25M sorts)
[!!] Joins performed without indexes: 14195512
[OK] Temporary tables created on disk: 24% (10M on disk / 44M total)
[OK] Thread cache hit rate: 99% (152 created / 5M connections)
[OK] Table cache hit rate: 99% (192M hits / 192M requests)
[OK] table_definition_cache(400) is upper than number of tables(337)
[OK] Open file limit used: 0% (81/10K)
[OK] Table locks acquired immediately: 100% (16M immediate / 16M locks)

These are the performance metrics.

Answer 1

Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

• Version: 10.4.12-MariaDB
• 31.4 GB of RAM
• Uptime = 10d 15:46:58
• It appears that you are running both MyISAM and InnoDB.
• 239 QPS

The More Important Issues:

If you have any MyISAM tables, consider changing them to InnoDB. (But not the tables in the mysql database.) http://mysql.rjweb.org/doc.php/myisam2innodb

innodb_buffer_pool_size is being pulled in both directions. It could be larger, but there does not seem to be enough data to warrant it to be as big as it currently is. So, just leave it alone, contrary to the details below.

You are hitting max_connections. If you are getting compaints of "out of connections" or "connection denied", let's discuss the various remedies. The quick fixes are increase max_connections (now 151) and/or decrease wait_timeout (now 28800), but there may be underlying problems that should be fixed. A common possibility is Apache having too many "children".

SHOW TABLES is being run about 40 times per minute -- why?

Handler_discover is > 0. Are you using NDB?

Do you have some "prefix" indexes? If so, let's look at them and their usage. There may be some improvements to be had.

Recommended changes to VARIABLES:

innodb_log_file_size = 300M  -- Caution.  If this turns out to be difficult to change, leave it alone.
innodb_adaptive_hash_index = OFF
query_cache_type = OFF   -- It is currently not effective, and causing a lot of overhead
query_cache_size = 0
 

Details and other observations:

( innodb_buffer_pool_size ) = 18G / 33715493273.6 = 57.3% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( innodb_buffer_pool_instances ) = 18 -- Recommend no more than 16. (Beginning to go away in 10.5)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 631,176 / 1154160 = 54.7% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 19327352832) is bigger than necessary?

( Innodb_log_writes ) = 24,075,529 / 920818 = 26 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 73,937,686,528 / (920818 / 3600) / 2 / 48M = 2.87 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 920,818 / 60 * 48M / 73937686528 = 10.4 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( character_set_server ) = character_set_server = latin1 -- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Key_blocks_used * 1024 / key_buffer_size ) = 426 * 1024 / 128M = 0.33% -- Percent of key_buffer used . High-water-mark. -- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.

( Key_writes / Key_write_requests ) = 10,712,314 / 22041880 = 48.6% -- key_buffer effectiveness for writes -- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 134217728).

( query_cache_size ) = 1M -- Size of QC -- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.

( Qcache_lowmem_prunes ) = 63,676,293 / 920818 = 69 /sec -- Running out of room in QC -- increase query_cache_size (now 1048576)

( Qcache_lowmem_prunes/Qcache_inserts ) = 63,676,293/74322826 = 85.7% -- Removal Ratio (frequency of needing to prune due to not enough memory)

( Qcache_not_cached ) = 42,503,443 / 920818 = 46 /sec -- SQL_CACHE attempted, but ignored -- Rethink caching; tune qcache

( Qcache_hits / Qcache_inserts ) = 21,642,863 / 74322826 = 0.291 -- Hit to insert ratio -- high is good -- Consider turning off the query cache.

( Qcache_hits / (Qcache_hits + Com_select) ) = 21,642,863 / (21642863 + 148412902) = 12.7% -- Hit ratio -- SELECTs that used QC -- Consider turning off the query cache.

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 21,642,863 / (21642863 + 74322826 + 42503443) = 15.6% -- Query cache hit rate -- Probably best to turn off the QC.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (1M - 80152) / 454 / 16384 = 0.13 -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size (now 16384)

( Created_tmp_tables ) = 52,379,234 / 920818 = 57 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 12,852,371 / 920818 = 14 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216). Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 12,852,371 / 219809523 = 5.8% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

( Select_full_join / Com_select ) = 16,794,986 / 148412902 = 11.3% -- % of selects that are indexless joins -- Add suitable index(es) to tables used in JOINs.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (46913103 + 16078 + 0 + 0 + 772352 + 0) / 920818 = 52 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.

( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections -- increase max_connections (now 151) and/or decrease wait_timeout (now 28800)

Abnormally small:

Created_tmp_files = 0.098 /HR
innodb_page_cleaners / innodb_buffer_pool_instances = 0.222
innodb_spin_wait_delay = 4

Abnormally large:

Aria_transaction_log_syncs = 13,051
Com_show_tables = 0.61 /sec
Com_show_warnings = 39 /HR
Connection_errors_internal = 0.0039 /HR
Handler_discover = 0.2 /HR
Handler_read_key = 71904 /sec
Innodb_buffer_pool_read_requests = 681014 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Innodb_rows_updated = 354 /sec
Innodb_secondary_index_triggered_cluster_reads = 163096 /sec
Threads_cached = 148
performance_schema_max_cond_classes = 90

Abnormal strings:

Innodb_have_punch_hole = OFF
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = TABLE
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off
profiling = ON

Answer 2

Any tuning analysis has flaws. One metric says to increase something; another says to decrease that same thing.

Now, the issue is that I keep increasing query_cache_limit, query_cache_size and join_buffer_size and yet MYSQLTuner keeps telling me to increase it,

Turn off the QC:

query_cache_type = OFF
query_cache_size = 0

"[!!] Query cache prunes per day: 6005371" -- That's a lot. It may mean that the QC is actually slowing down the system instead of speeding it up. (Hence, another argument for turning it OFF.)

join_buffer_size should be no more than 1% of RAM size. Other than that, the setting is rarely relevant to anything, so don't worry about it.

As for wait_timeout, we need to see Connections / Uptime to see if there are connections are being created "too often". Changing wait_timeout has little noticeable impact on performance.

max_connections defaults to 151. For most servers, that is a good compromise. For tiny RAM (under 4GB) that is too high; for a few very active servers, it is too low. A variety of other clues may help us decide if it is "too low".

[!!] Highest connection usage: 100% (152/151)

Yes, that says something needs change. wait_timeout is a possible 'quick fix'. What type of clients do you have? Web pages using PHP? Java? What is the limit of connections in Apache/Tomcat? Individuals logging in? Is there some form of Connection Pooling? I may find more from the analysis below.

There is no valid formula for "maximum memory usage". 181% is not necessarily dangerous.

[!!] Joins performed without indexes: 14195512
[OK] Temporary tables created on disk: 24% (10M on disk / 44M total)

Both of those suggest that the queries need some tuning -- possibly adding a composite index, possibly reformulating. For more help: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

If you provide the GLOBAL STATUS and VARIABLES and RAM size (31.4G), I will provide a more thorough analysis: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning