Should I turn off Query Cache in MySQL?

Question

I'm using a dedicated server with 32GB RAM and an 8-core server, using Maria DB 10.1 and most tables are InnoDB. Total DB size is less than 2GB but I think performance is slow.

The following is the my.cnf file I'm using:

[mysqld]
log-error=/home/MySQL_Server/mysql/dedi.server.co.err
datadir=/home/MySQL_Server/mysql
pid-file=/home/MySQL_Server/mysqlmysqld.pid
innodb_file_per_table=1

skip-name-resolve=1
bind-address=127.0.0.1
#skip-networking=1
#query_cache_type=0
query_cache_type=1
innodb_file_per_table=1
default-storage-engine=InnoDB

#query_cache_size=0
query_cache_size=128M
query_cache_limit=256K
query_cache_min_res_unit = 2k

performance_schema=ON
innodb_buffer_pool_size = 1536M
innodb_log_file_size = 140M
innodb_log_files_in_group=2

sort_buffer_size=256k
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
thread_stack=256k
mrr_buffer_size=256k

join_cache_level=8

tmp_table_size=64M
max_heap_table_size=64M

table_open_cache=1024
thread_cache_size=32

innodb_buffer_pool_instances=1

innodb_use_sys_malloc = 1

max_connections=500
wait_timeout=300
interactive_timeout=360
#tmpdir=/var/mysqltmp
#max_allowed_packet=268435456

MySQL Tuner suggested the following:

General recommendations:
    Control warning line(s) into /home/MySQL_Server/mysql/dedi.niresh.co.err file
    Control error line(s) into /home/MySQL_Server/mysql/dedi.niresh.co.err file
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    innodb_log_file_size should be (=192M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Should I turn off the query cache?

Is there any additional recommendation?

Solution

In almost all production servers, it is wise to turn off the Query cache. Every modification to a table causes purging of all QC entries for that table. The larger the table, the more time that takes. 128M is dangerously high.

Normally, it is wise to set innodb_buffer_pool_size to about 70% of available RAM. You have it set to a much lower value, even less than the dataset size. 3G would probably help. 20G would not help any more (until your dataset grows significantly).

Make sure that both the OS and MySQL are 64-bit versions.

For a more thorough analysis, provide

• RAM size (32G)
• SHOW VARIABLES;
• SHOW GLOBAL STATUS; (after running at least 24 hours)

Analysis of VARIABLES and STATUS:

The More Important Issues

Since you are only (?) using InnoDB and only 2GB of data, it is not critical to respond to the comments blow about innodb_buffer_pool_size and key_buffer_size

Provide some more details on your heavy use of DELETE.

Make use of the slowlog to find the 'worst' queries. More details here . That should identify the tmp_table and table scan issues mentioned below.

Don't bother using OPTIMIZE TABLE.

How are you doing "transactions"? Sometimes with autocommit, sometimes with COMMIT?

Details and other observations

( Key_blocks_used * 1024 / key_buffer_size ) = 4,710 * 1024 / 128M = 3.6% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 4096M / 32768M = 12.5% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (128M / 0.20 + 4096M / 0.70) / 32768M = 19.8% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 187,813 * 16384 / 4096M = 71.6% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 7,144,121 / 29935426 = 23.9% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 1,199,046,656 / 4096M = 27.9% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 533,153 / 60 * 512M / 20356473344 = 234 -- 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. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 364,605 / 414950 = 0.879 -- Churn -- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 247,373 / (247373 + 446152) = 35.7% -- Percent of temp tables that spilled to disk -- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Select_scan ) = 871,872 / 533153 = 1.6 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 871,872 / 12593904 = 6.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_optimize ) = 216 / 533153 = 1.5 /HR -- How often OPTIMIZE TABLE is performed. -- OPTIMIZE TABLE is rarely useful, certainly not at high frequency.

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

Extremes (without comment):

Abnormally small:

Com_commit = 2.5 /HR
Innodb_buffer_pool_pages_made_not_young = 0.15 /sec
Innodb_ibuf_merged_delete_marks = 27 /HR
Innodb_row_lock_time = 8
Innodb_row_lock_time_max = 1
interactive_timeout = 360

Abnormally large:

Com_rollback_to_savepoint = 14 /HR
Handler_savepoint_rollback = 14 /HR
join_cache_level = 8   (This may be unused?  It was removed in 5.6.3, but possibly left in MariaDB 10.1?)

Abnormal strings:

Innodb_buffer_pool_dump_status = Dumping buffer pool(s) not yet started
Innodb_buffer_pool_load_status = Loading buffer pool(s) not yet started
innodb_checksum_algorithm = INNODB
innodb_cleaner_lsn_age_factor = HIGH_CHECKPOINT
innodb_empty_free_list_algorithm = BACKOFF
innodb_force_load_corrupted = OFF
innodb_foreground_preflush = EXPONENTIAL_BACKOFF
innodb_log_checksum_algorithm = INNODB
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
opt_s__mrr = off
opt_s__mrr_cost_based = off

Query cache

Since it was turned off, none of the Qcache status values were set. So I cannot address the original question. If you would like to turn on the QC and restart the server and wait a few days, I could re-analyze with it on. Various metrics about hits, prunes, etc may address the original question.

Comments

[Niresh]

Here are your requested SHOW VARIABLES; SHOW GLOBAL STATUS; & of course after 24hrs d26dzxoao6i3hh.cloudfront.net/items/0X0Y3A2Q3L2F1A1Q401w/… d26dzxoao6i3hh.cloudfront.net/items/2a0Z2E0w390d2u0e2n3u/…