mysql select 禁用缓存,我应该在MySQL中关闭查询缓存吗?

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?

解决方案

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.