我使用的是具有32GB RAM的专用服务器和8核服务器,使用Maria DB 10.1,大多数表都是InnoDB。总数据库大小小于 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.
我应该关闭查询缓存吗?
还有其他建议吗?
在几乎所有生产服务器中,关闭查询缓存是明智的。对表的每次修改都会导致清除该表的所有QC 条目。 桌子越大,花费的时间就越多。 128M是危险的高。
通常,明智的做法是将innodb_buffer_pool_size
设置为可用RAM的70%左右。 您可以将其设置为低得多的值,甚至小于数据集大小。 3G可能会有所帮助。 20G将不再有帮助(直到您的数据集显着增长)。
确保操作系统和MySQL都是64位版本。
要进行更全面的分析,请提供
- 内存大小 (32G)
SHOW VARIABLES;
SHOW GLOBAL STATUS;
(运行至少 24 小时后)
变量和状态分析:
更重要的问题
由于您仅(?)使用InnoDB并且只有2GB的数据,因此响应有关innodb_buffer_pool_size
和key_buffer_size
的评论并不重要
提供有关您大量使用DELETE
的更多详细信息。
利用慢日志来查找"最差"的查询。 更多细节在这里. 这应该确定下面提到的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;避免斑点等。
( Select_scan ) = 871,872 / 533153 = 1.6 /sec
-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)
( Select_scan / Com_select ) = 871,872 / 12593904 = 6.9%
-- 执行全表扫描的选择的百分比。 (可能被存储例程所愚弄。 -- 添加索引/优化查询
( Com_optimize ) = 216 / 533153 = 1.5 /HR
-- 执行优化表的频率。 -- 优化表很少有用,当然频率不高。
( 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并重新启动服务器并等待几天,我可以重新分析它。 关于命中、修剪等的各种指标可能会解决原始问题。
是的,查询缓存应关闭
have_query_cache=0 # from Yes 08/19/2017 to avoid QC overhead, just by its presence
query_cache_size=0 # to ensure QC can not be used
read_rnd_buffer_size=16K # from 256K 08/19/2017 to minimize wasted RD cycles
问问为什么max_allowed_packet=268435456
是必要的。这可能会导致net_buffer_length
的大小增加并导致记忆压力。
其他建议(根据原始问题中的要求)。
除了里克·詹姆斯的建议, 对于 [mysqld] 部分中的 .cfg/ini - 一次一个,请:
max_seeks_for_key = 64 #rather,而不是允许优化程序搜索 40 亿 ndx。 innodb_log_buffer_size = 64M #from 16M 以降低硬盘写入频率 max_connections = 200 #from 500,因为只使用了 123 个 (HWM)table_open_cache = 4000 #from 1024 保持表打开 table_definition_cache = 3000 #from 400 以保持表定义打开 open_files_limit = 30000 #from 10000 保持文件打开
您的状态变量指示 9,391,427 的com_stmt_prepare
,执行相同的计数,但只有 9,388,882com_stmt_close
。 可能需要关闭这几千个以释放资源。
请让我们知道您的进度。
JYelton,variable_value后面的#的目的是允许快速复制/粘贴到[mysqld]函数的末尾,并知道旧值是什么。 感谢您的格式提示,非常感谢。