mysql - E_WARNING : Error while sending STMT_PREPARE packet. PID=*

Question

我的 Laravel 5.7 网站遇到了一些我认为彼此相关的问题(但发生在不同的时间):

PDO::prepare(): MySQL server has gone away

E_WARNING: Error while sending STMT_PREPARE packet. PID=10

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry (我的数据库似乎经常尝试在同一秒内两次写入相同的记录。我一直无法弄清楚为什么或如何重现它；它似乎与用户行为无关。)

不知何故，前两种类型的错误只出现在我的 Rollbar 日志中，而不出现在服务器上的文本日志或我的 Slack 通知中，因为所有错误都应该出现(以及所有其他错误)。

几个月来，我一直看到像这样可怕的日志消息，我完全无法重现这些错误(并且无法诊断和解决它们)。
我还没有发现任何实际症状或听到用户的任何提示，但错误消息似乎很重要，所以我真的很想了解并解决根本原因。

我尝试更改我的 MySQL 配置以使用 max_allowed_packet=300M (而不是 4M 的 default)，但在我的网站有超过几个访问者的日子里，仍然经常遇到这些异常。
由于 this advice，我还设置了(从 5M 和 10M 更改)以下内容:

innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M

作为进一步的背景:

我的站点有一个运行作业的队列工作器 ( artisan queue:work --sleep=3 --tries=3 --daemon )。

有一堆排队的工作可以根据访问者的注册时间安排在同一时刻发生。但我看到最多同时发生的是 20。

MySQL 慢查询日志中没有条目。

我有一些 cron 工作，但我怀疑它们有问题。每分钟运行一次，但非常简单。另一个每 5 分钟运行一次，以发送某些预定的电子邮件(如果有的话)。另一个每 30 分钟运行一次以运行报告。

我跑过各种 mysqlslap查询(虽然我完全是新手)并且即使在模拟数百个并发客户端时也没有发现任何缓慢的问题。

我正在使用 Laradock (Docker)。

我的服务器是 DigitalOcean 1GB RAM、1 个 vCPU、25GB SSD。我也试过 2GB RAM 没有区别。

来自 SHOW VARIABLES; 的结果和 SHOW GLOBAL STATUS; are here .

我的 my.cnf是:

[mysql]

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
character-set-server=utf8
innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M
max_allowed_packet=300M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query_log.log
long_query_time = 10
log_queries_not_using_indexes = 0

关于我应该探索什么来诊断和解决这些问题的任何想法？谢谢。

Answer 1

Re Slowlog:向我们展示你的 my.cnf。 [mysqld]中的变化部分？通过 SELECT SLEEP(12); 进行测试，然后查看文件和表格。

查找查询的替代方法:由于查询需要几分钟，请执行 SHOW FULL PROCESSLIST;当您认为它可能正在运行时。

你有多少内存？没有max_allowed_packet=300M除非你有至少 30GB 的内存。否则你就有交换(甚至崩溃)的风险。将该设置保持在 RAM 的 1% 以下。

如需进一步分析可调参数，请提供 (1) RAM 大小，(2) SHOW VARIABLES;和 (3) SHOW GLOBAL STATUS; .

回复 deleted_at :您提供的链接以“列已删除的_at 不是一个好的索引候选者”开头。你误解了它。说的是单列INDEX(deleted_at) .我建议使用复合指数，例如 INDEX(contact_id, job_class_name, execute_at, deleted_at) .

对小表进行简单查询需要 158 秒？可能还有很多其他事情正在发生。获取 PROCESSLIST .

重新分离索引与复合索引:考虑两个索引:INDEX(last_name)和 INDEX(first_name) .你翻阅last_name索引找到“James”，那你能做什么？翻阅“Rick”的其他索引不会帮助您找到我。

变量和全局状态分析

观察:

版本:5.7.22-log

1.00 GB 内存

正常运行时间 = 16 天 10:30:19

你确定这是一个 SHOW GLOBAL STATUS 吗？

您不是在 Windows 上运行。

运行 64 位版本

您似乎完全(或大部分)在运行 InnoDB。

更重要的问题:

innodb_buffer_pool_size -- 我以为你有 213M，而不是 10M。 10M太小了。另一方面，您的数据似乎少于那么多。

由于 RAM 太小，我建议将 tmp_table_size 和 max_heap_table_size 和 max_allowed_pa​​cket 降低到 8M。
并将 table_open_cache、table_definition_cache 和 innodb_open_files 降低到 500。

是什么导致如此多的同时连接？

详细信息和其他意见:
( innodb_buffer_pool_size / _ram ) = 10M / 1024M = 0.98% -- 用于 InnoDB buffer_pool 的 RAM 百分比
( innodb_buffer_pool_size ) = 10M -- InnoDB 数据 + 索引缓存
( innodb_lru_scan_depth ) = 1,024-- "InnoDB: page_cleaner: 1000ms 预期循环花费..."可以通过降低 lru_scan_depth 来修复
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 375 / 638 = 58.8% -- 当前未使用的 buffer_pool 的百分比
-- innodb_buffer_pool_size 比必要的大吗？
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4M / 10M = 40.0% -- 数据占用缓冲池的百分比
-- 很小的百分比可能表明 buffer_pool 不必要地大。
( innodb_log_buffer_size / _ram ) = 16M / 1024M = 1.6% -- 用于缓冲 InnoDB 日志写入的 RAM 百分比。
-- 太大会影响 RAM 的其他用途。
( innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size ) = 48M * 2 / 10M = 960.0% -- 日志大小与 buffer_pool 大小的比率。建议使用 50%，但请参阅其他计算以了解它是否重要。
-- 日志不需要大于缓冲池。
( innodb_flush_method ) = innodb_flush_method = -- InnoDB 应该如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免双缓冲。 (至少对于 Unix。)请参阅 chrischandler 有关 O_ALL_DIRECT 的警告
( innodb_flush_neighbors ) = 1 -- 将块写入磁盘时的小优化。
-- SSD 驱动器使用 0； 1 个用于硬盘。
( innodb_io_capacity ) = 200 - 每秒能够在磁盘上执行的 I/O 操作数。 100 用于慢速驱动器； 200 用于旋转驱动器； SSD 1000-2000；乘以 RAID 系数。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- 是否记录所有死锁。
-- 如果您受到死锁的困扰，请打开它。注意:如果您有很多死锁，这可能会向磁盘写入大量内容。
( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 16M, 16M ) / 1024M = 1.6% -- 当需要 MEMORY 表(每个表)或 SELECT 中的临时表(每个临时表每个 SELECT)时分配的 RAM 百分比。太高可能会导致交换。
-- 将 tmp_table_size 和 max_heap_table_size 减小到 ram 的 1%。
( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10% ( local_infile ) = local_infile = ON-- local_infile = ON 是一个潜在的安全问题
( Select_scan / Com_select ) = 111,324 / 264144 = 42.1% -- % 的选择进行全表扫描。 (可能会被存储的例程所迷惑。)
-- 添加索引/优化查询
( long_query_time ) = 10 -- 定义“慢”查询的截止(秒)。
-- 建议 2
( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- 峰值连接百分比
-- 增加 max_connections 和/或减少 wait_timeout

您有一半的查询缓存。您应该同时设置 query_cache_type = OFF 和 query_cache_size = 0 。 QC 代码中存在(根据谣言)一个“错误”，除非您关闭这两个设置，否则会留下一些代码。

异常小:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.186
Created_tmp_files = 0.015 /HR
Handler_write = 0.21 /sec
Innodb_buffer_pool_bytes_data = 3 /sec
Innodb_buffer_pool_pages_data = 256
Innodb_buffer_pool_pages_total = 638
Key_reads+Key_writes + Innodb_pages_read+Innodb_pages_written+Innodb_dblwr_writes+Innodb_buffer_pool_pages_flushed = 0.25 /sec
Table_locks_immediate = 2.8 /HR
Table_open_cache_hits = 0.44 /sec
innodb_buffer_pool_chunk_size = 5MB

异常大:

Com_create_db = 0.41 /HR
Com_drop_db = 0.41 /HR
Connection_errors_peer_address = 2
Performance_schema_file_instances_lost = 9
Ssl_default_timeout = 500

异常字符串:

ft_boolean_syntax = + -><()~*:&
have_ssl = YES
have_symlink = DISABLED
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
session_track_system_variables = time_zone, autocommit, character_set_client, character_set_results, character_set_connection
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN