我应该关闭 MySQL 中的查询缓存吗?

2022-01-15 00:00:00 server mariadb mysql mariasql

我正在使用具有 32GB RAM 和 8 核服务器的专用服务器，使用 Maria DB 10.1，大多数表都是 InnoDB.总 DB 大小小于 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?

还有其他推荐吗?

推荐答案

在几乎所有的生产服务器中，关闭查询缓存是明智的.对表的每一次修改都会导致清除该表的所有个 QC 条目.桌子越大，花费的时间就越多.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

  • 内存大小 (32G)
  • 显示变量；
  • 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_written/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.

( 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 中更改.)

( 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 的频率.-- 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.

相关文章