mysql - 我应该关闭 MySQL 中的查询缓存吗？

Question

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

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

[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 建议如下:

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.

我应该关闭查询缓存吗？

有什么额外的建议吗？

Answer 1

在几乎所有的生产服务器中，关闭查询缓存是明智的。对表的每次修改都会导致清除该表的所有 QC 条目。 table 越大，花费的时间就越多。 128M是危险的高。

通常，设置 innodb_buffer_pool_size 是明智的。到大约 70% 的可用 RAM。您将其设置为低得多的值，甚至小于数据集的大小。 3G 可能会有所帮助。 20G 将不再有帮助(直到您的数据集显着增长)。

确保操作系统和 MySQL 都是 64 位版本。

要进行更彻底的分析，请提供

RAM 大小 (32G)

SHOW VARIABLES;

SHOW GLOBAL STATUS; (运行至少 24 小时后)

分析变量和状态:

更重要的问题

由于您只(？)使用 InnoDB 并且只有 2GB 的数据，因此回应关于 innodb_buffer_pool_size 的评论并不重要。和 key_buffer_size
提供更多关于您大量使用 DELETE 的详细信息.

利用慢日志查找“最差”的查询。更多详情 here .这应该可以识别下面提到的 tmp_table 和表扫描问题。

不要打扰使用 OPTIMIZE TABLE .

你是如何进行“交易”的？有时使用自动提交，有时使用 COMMIT ?

细节和其他观察
( Key_blocks_used * 1024 / key_buffer_size ) = 4,710 * 1024 / 128M = 3.6% -- 使用的 key_buffer 的百分比。高水位线。
-- 降低 key_buffer_size 以避免不必要的内存使用。
( innodb_buffer_pool_size / _ram ) = 4096M / 32768M = 12.5% -- 用于 InnoDB buffer_pool 的 RAM 百分比
( (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
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 187,813 * 16384 / 4096M = 71.6% -- 缓冲池空闲
-- buffer_pool_size 大于工作集；可以减少它
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 7,144,121 / 29935426 = 23.9% -- 写入必须命中磁盘的请求
-- 检查 innodb_buffer_pool_size
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 1,199,046,656 / 4096M = 27.9% -- 数据占用缓冲池的百分比
-- 很小的百分比可能表明 buffer_pool 过大。
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 533,153 / 60 * 512M / 20356473344 = 234 -- InnoDB 日志轮换之间的分钟数从 5.6.8 开始，可以动态更改；请务必同时更改 my.cnf。
-- (轮换间隔 60 分钟的建议有些随意。)调整 innodb_log_file_size。 (不能在 AWS 中更改。)
( Innodb_rows_deleted / Innodb_rows_inserted ) = 364,605 / 414950 = 0.879 - 搅拌
——“不要排队，就去做吧。” (如果 MySQL 被用作队列。)
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 247,373 / (247373 + 446152) = 35.7% -- 溢出到磁盘的临时表的百分比
-- 可能增加 tmp_table_size 和 max_heap_table_size;避免 Blob 等
( Select_scan ) = 871,872 / 533153 = 1.6 /sec -- 全表扫描
-- 添加索引/优化查询(除非它们是小表)
( Select_scan / Com_select ) = 871,872 / 12593904 = 6.9% -- % 的选择进行全表扫描。 (可能会被存储的例程愚弄。)
-- 添加索引/优化查询
( Com_optimize ) = 216 / 533153 = 1.5 /HR -- 执行 OPTIMIZE TABLE 的频率。
-- OPTIMIZE TABLE 很少有用，当然不是高频。
( long_query_time ) = 10.000000 = 10 -- 用于定义“慢”查询的截止时间(秒)。
-- 建议 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并重新启动服务器并等待几天，我可以重新分析它。关于命中、修剪等的各种指标可以解决最初的问题。