我应该在MySQL中关闭查询缓存吗? [英] Should I turn off Query Cache in MySQL?

问题描述

我正在使用具有32GB RAM的专用服务器和8核服务器，并使用Maria DB 10.1，并且大多数表都是InnoDB.数据库总大小小于2GB，但我认为性能很慢.

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.

以下是我正在使用的 my.cnf 文件:

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建议以下内容:

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?

还有其他建议吗?

推荐答案

在几乎所有生产服务器中，关闭查询缓存都是明智的.对表的每次修改都会清除该表的所有质量控制条目.表格越大，花费的时间越多.128M太高了.

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.

通常，明智的做法是将 innodb_buffer_pool_size 设置为可用 RAM的大约70％.您已将其设置为更低的值，甚至小于数据集的大小.3G可能会有所帮助.20G将无济于事(直到您的数据集显着增长).

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).

确保操作系统和MySQL均为64位版本.

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

要进行更全面的分析，请提供

For a more thorough analysis, provide

• RAM大小(32G)
• SHOW VARIABLES;
• SHOW GLOBAL STATUS; (至少运行24小时后)

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

分析变量和状态:

更重要的问题

由于您仅使用(?)InnoDB，并且仅使用2GB数据，因此响应有关 innodb_buffer_pool_size 和 key_buffer_size

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

提供有关您大量使用 DELETE 的更多详细信息.

Provide some more details on your heavy use of DELETE.

利用慢速日志查找最差"查询.更多详细信息 此处 .那应该确定下面提到的tmp_table和表扫描问题.

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.

不用费心使用 OPTIMIZE TABLE .

您如何进行交易"?有时使用自动提交，有时使用 COMMIT?

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

详细信息和其他观察结果

(Key_blocks_used * 1024/key_buffer_size)= 4,710 * 1024/128M = 3.6％-使用的key_buffer的百分比.高水位标记.-降低key_buffer_size以避免不必要的内存使用.

( 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% -- 用于 InnoDB buffer_pool 的 RAM 百分比

( 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％-应该使大多数可用的ram都可用于缓存.- http://mysql.rjweb.org/doc.php/memory

( (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_size 大于工作集；可以减少它

( 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_write/Innodb_buffer_pool_write_requests)= 7,144,121/29935426 = 23.9％-写入必须命中磁盘的请求-检查innodb_buffer_pool_size

( 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％-数据占用的缓冲池百分比-很小的可能表示buffer_pool不必要地大.

( 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.

(正常运行时间/60 * innodb_log_file_size/Innodb_os_log_write)= 533,153/60 * 512M/20356473344 = 234 -InnoDB日志轮换之间的分钟数从5.6.8开始，可以动态更改一定还要更改my.cnf.-(建议每两次轮换60分钟有点随意.)调整innodb_log_file_size.(无法在AWS中更改.)

( 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 -- 流失-不要排队，就去做."(如果将MySQL用作队列.)

( 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％-溢出到磁盘的临时表的百分比-可能增加tmp_table_size和max_heap_table_size;避免斑点等.

( 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 -全表扫描-添加索引/优化查询(除非它们是很小的表)

( 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％-执行全表扫描的选择百分比.(可能会被存储例程欺骗.)-添加索引/优化查询

( 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 -执行优化表的频率.-OPTIMIZE TABLE很少有用，肯定不是高频.

( 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 -定义慢"查询的截止时间(秒).-建议2

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

极端(无评论):

异常小:

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

异常大:

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?)

异常字符串:

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

查询缓存

由于已关闭，因此未设置任何Qcache状态值.因此，我无法解决原始问题.如果您想打开QC并重新启动服务器并等待几天，我可以重新对其进行分析.有关命中，修剪等的各种指标 可以解决原始问题.

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.

这篇关于我应该在MySQL中关闭查询缓存吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！