在我们运行 MariaDB 10.1(最新的 Debian 软件包)的服务器上,负载较低(平均负载:0.79、0.88、1.00),我经常收到错误消息“Prepared statement需要重新准备”。
难以复制,随机出现,每天约40-50次。
我已阅读有关 repreparation的文档,以下是 repreparation 的解释:
重新准备发生在DDL 语句之后,例如创建、删除、更改、重命名或截断表,或者分析、优化或修复表的语句。在从表定义缓存中刷新引用的表或视图之后,也会发生重新准备,或者隐式地为缓存中的新条目腾出空间,或者显式地由于FLUSH TABLES。
我不知道服务器上运行的任何 DDL 语句。我们的应用程序不使用“FLUSH TABLES”。除了我们的应用程序只有内部使用的 Phabricator 正在运行(由 8 个开发人员使用)和另外两个使用 MariaDB 用 PHP 编写的最少访问的应用程序。以下是命令统计信息:
你有什么想法,如何找到重新准备的原因?我不想一味的增加table_definition_cache(目前是400)或者table_open_cache(2000)。还是您认为它很小并且可以安全增加?
对于调试信息:SHOW GLOBAL STATUS,SHOW VARIABLES和来自服务器的内存信息。
从 5.1.25 变更日志:
所以...
Com_alter_table
= 2496 = 3.5/小时 -- 这相当高,可能需要重新准备。CREATE/DROP TABLE
也比较频繁。table_definition_cache
.进一步的分析...
观察:
更重要的问题:
将其余部分从 MyISAM 转换为 InnoDB
USE dbname
有几个SHOWs
非常频繁地发生;到底是怎么回事?很多慢查询、表扫描等。寻找最慢的几个查询并努力改进它们:http: //mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
您是否故意关闭了一些优化器开关?如果是这样,为什么?
细节和其他观察:
( innodb_buffer_pool_size / _ram ) = 1024M / 65536M = 1.6%
-- 用于 InnoDB buffer_pool 的 RAM 百分比( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 1024M / 0.70) / 65536M = 2.4%
-- 大部分可用的 ram 应可用于缓存。-- http://mysql.rjweb.org/doc.php/memory( Opened_tables ) = 6,996,394 / 2595193 = 2.7 /sec
-- 打开Tables的频率 -- 增加table_open_cache( Opened_table_definitions ) = 5,643,679 / 2595193 = 2.2 /sec
-- 打开 .frm 文件的频率 -- 增加 table_definition_cache 和/或 table_open_cache。( innodb_buffer_pool_size ) = 1024M
-- InnoDB 数据 + 索引缓存 -- 小得可怜。( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- 是否记录所有死锁。-- 如果你被死锁困扰,打开它。注意:如果你有很多死锁,这可能会写入很多磁盘。( join_buffer_size / _ram ) = 262,144 / 65536M = 0.00%
-- 每个线程 0-N。可以加快 JOIN(更好地修复查询/索引)(所有引擎)用于索引扫描、范围索引扫描、全表扫描、每个完整 JOIN 等。 -- 如果很大,请减小 join_buffer_size 以避免内存压力。建议少于 1% 的 RAM。如果很小,则将 RAM 增加到 0.01% 以改进一些查询。( query_prealloc_size / _ram ) = 24,576 / 65536M = 0.00%
-- 用于解析。占内存的百分比( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00%
-- 用于解析。占内存的百分比( local_infile ) = local_infile = ON
-- local_infile = ON 是一个潜在的安全问题( Qcache_lowmem_prunes ) = 54,439,645 / 2595193 = 21 /sec
-- QC 空间不足 -- 增加 query_cache_size( Qcache_lowmem_prunes/Qcache_inserts ) = 54,439,645/193592210 = 28.1%
-- 去除率(需要修剪的时间百分比)( Qcache_hits / Qcache_inserts ) = 194,941,934 / 193592210 = 1.01
-- 命中插入率 -- 高是好的 -- 考虑关闭查询缓存。( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 2696464) / 7979 / 16384 = 0.108
-- query_alloc_block_size vs 公式 -- 调整 query_alloc_block_size( Created_tmp_disk_tables ) = 13,897,242 / 2595193 = 5.4 /sec
-- 创建磁盘“临时”表作为复杂 SELECT 的一部分的频率 -- 增加 tmp_table_size 和 max_heap_table_size。检查何时使用 MEMORY 而不是 MyISAM 的临时表规则。也许较小的模式或查询更改可以避免 MyISAM。更好的索引和查询的重新制定更有可能有所帮助。( Select_scan ) = 168,942,798 / 2595193 = 65 /sec
-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)( Select_scan / Com_select ) = 168,942,798 / 402363586 = 42.0%
-- % 的选择进行全表扫描。(可能被存储例程愚弄。)——添加索引/优化查询( binlog_format ) = binlog_format = STATEMENT
-- 声明/行/混合。ROW 是首选;它可能成为默认值。( Slow_queries ) = 169,941,950 / 2595193 = 65 /sec
-- 频率(每秒慢查询) -- 返工慢人;改进指标;观察慢速日志文件的磁盘空间( Slow_queries / Questions ) = 169,941,950 / 712388796 = 23.9%
-- 频率(所有查询的百分比) -- 查找慢查询;检查索引。( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 1,016,245 / ( 1016245 + 217503779 ) = 0.47%
-- 子查询缓存命中率 -- 考虑 SET optimizer_switch='subquery_cache=off';( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON
-- 是否在慢日志中包含此类。-- 这会使慢日志变得混乱;把它关掉,这样你就可以看到真正的慢查询。( back_log / max_connections ) = 80 / 151 = 53.0%
( Max_used_connections / max_connections ) = 152 / 151 = 100.7%
-- 连接的峰值百分比 -- 增加 max_connections 和/或减少 wait_timeout( Com_change_db / Connections ) = 245,625,094 / 14750877 = 16.7
-- 每个连接的数据库切换 --(次要)考虑使用“db.table”语法( Com_change_db ) = 245,625,094 / 2595193 = 95 /sec
-- 可能来自 USE 语句。-- 考虑与 DB 连接,使用 db.tbl 语法,消除虚假的 USE 语句等。( Connections ) = 14,750,877 / 2595193 = 5.7 /sec
-- Connections -- 增加wait_timeout;使用池化?( Threads_created / Connections ) = 1,856,073 / 14750877 = 12.6%
-- 进程创建速度 -- 增加thread_cache_size异常大:
异常字符串: