e\u警告:发送stmt\u prepare数据包时出错pid=*

csbfibhn  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(337)

截至2019-01-30 14:52 utc,您仍然可以赢得500点奖金,因为没有任何答案有帮助!

我的Laravel5.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 (我的数据库似乎经常试图在同一秒钟内写入同一条记录两次。我一直不知道为什么或者如何复制它;它似乎与用户行为无关。)
不知何故,前两种类型的错误只会出现在我的滚动条日志中,而不会出现在服务器的文本日志或我的slack通知中,正如所有错误都应该出现的那样(其他所有错误都会出现)。
几个月来,我一直看到这样可怕的日志消息,我完全无法重现这些错误(也无法诊断和解决它们)。
我还没有发现任何实际的症状,也没有听到任何用户的抱怨,但是错误信息似乎并不琐碎,所以我真的很想了解并解决根本原因。
我已经尝试更改mysql配置以使用 max_allowed_packet=300M (而不是默认的4m),但当我有超过两个访问者访问我的网站时,仍然会经常遇到这些异常。
由于这个建议,我还设置了(从5m和10m改为5m)如下:

innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M

作为进一步的背景:
我的站点有一个运行作业的队列工作程序( artisan queue:work --sleep=3 --tries=3 --daemon ).
有一堆排队的作业可以根据访问者的注册时间安排在同一时刻发生。但我看到最多的同时发生的是20次。
mysql慢速查询日志中没有条目。
我有几份临时工的工作,但我怀疑他们有问题。每分钟跑一次,但很简单。另一个每5分钟运行一次,以发送某些预定的电子邮件(如果有待定的电子邮件)。另一个每30分钟运行一次报告。
我跑过很多次 mysqlslap 查询(虽然我完全是新手),即使在模拟数百个并发客户机时也没有发现任何慢的东西。
我用的是拉拉多克( Docker )。
我的服务器是digitalocean 1gb ram,1个vcpu,25gb ssd。我也尝试过2gb内存,没有任何区别。
结果来自 SHOW VARIABLES; 以及 SHOW GLOBAL STATUS; 你在这里。
我的 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

有什么想法,我应该探索诊断和解决这些问题?谢谢。

fkaflof6

fkaflof61#

如果您随机看到此消息,可能的原因:
你的mysql是在一个代理之后,他们使用不同的 timeout 配置。
您正在使用php的persist连接。
您可以尝试通过以下步骤深入了解问题:
确保到mysql的连接有足够长的超时时间(例如:代理设置,mysql的 wait_timeout / interactive_timeout )
在php端禁用持久连接。
做一些 tcpdump 如果你能看到当你收到错误信息时发生了什么。

kyks70gy

kyks70gy2#

给我们看看你的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 :您提供的链接以“the column deleted\u at is not a good index candidate”开头。你误解了。它说的是一个专栏 INDEX(deleted_at) . 我建议采用综合指数,比如 INDEX(contact_id, job_class_name, execute_at, deleted_at) .
在一张小table上进行简单查询需要158秒?可能还有很多其他的事情。去拿那个 PROCESSLIST .
重新区分指数与综合指数:考虑两个指数: INDEX(last_name) 以及 INDEX(first_name) . 你翻阅姓氏索引找到“詹姆斯”,然后你能做什么?翻另一个索引找“瑞克”帮不了你找到我。
变量和全球状况分析
观察:
版本:5.7.22-log
1.00 gb内存
正常运行时间=16d 10:30:19
你确定这是一场国际秀吗?
您没有在windows上运行。
运行64位版本
您似乎正在运行全部(或大部分)innodb。
更重要的问题是:
innodb\u缓冲池\u池\u大小--我以为你有213米,不是10米。10米太小了。另一方面,你似乎没有那么多的数据。
由于ram太小,我建议将tmp\u table\u size和max\u heap\u table\u size和max\u allowed\u packet降到8m。将table\u open\u cache、table\u definition\u cache和innodb\u open\u files降低到500。
是什么导致这么多的同时连接?
细节和其他观察: ( innodb_buffer_pool_size / _ram ) = 10M / 1024M = 0.98% --innodb缓冲池使用的ram百分比 ( innodb_buffer_pool_size ) = 10M --innodb数据+索引缓存 ( innodb_lru_scan_depth ) = 1,024 --“innodb:page\u cleaner:1000ms designed loop taked…”可以通过降低lru\u scan\u深度来修复 ( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 375 / 638 = 58.8% --缓冲池的pct当前未使用--innodb\U缓冲池\U大小是否大于所需大小? ( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4M / 10M = 40.0% --数据占用缓冲池的百分比——一个小的百分比可能表示缓冲池不必要的大。 ( 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% --日志大小与缓冲池大小的比率。50%是建议的,但是否重要,请参阅其他计算。-日志不需要大于缓冲池。 ( innodb_flush_method ) = innodb_flush_method = --innodb应该如何要求操作系统写块。建议oïu direct或oïu allïu direct(percona)避免双重缓冲(请参阅chrischandler以获取关于o\u all\u direct的警告 ( innodb_flush_neighbors ) = 1 --将块写入磁盘时的一个小优化。-ssd驱动器使用0;1个硬盘驱动器。 ( innodb_io_capacity ) = 200 --磁盘上支持每秒i/o操作。低速行驶时为100;200转驱动;固态硬盘为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% --当需要内存表(每个表)或select中的临时表(每个临时表/某些select)时要分配的ram百分比。过高可能导致交换。-将tmp\u table\u size和max\u heap\u table\u size减小到ram的1%。
( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10% ( local_infile ) = local_infile = ON --local\u infle=on是一个潜在的安全问题 ( Select_scan / Com_select ) = 111,324 / 264144 = 42.1% --%的选择正在执行全表扫描(可能会被存储的例程愚弄。)--添加索引/优化查询 ( long_query_time ) = 10 --定义“慢”查询的截止时间(秒)。--建议2 ( Max_used_connections / max_connections ) = 152 / 151 = 100.7% --连接的峰值百分比--增加最大\u连接数和/或减少等待\u超时
查询缓存减半了。您应该将query\u cache\u type=off和query\u cache\u size=0。有传言称,qc代码中存在一个bug,除非你同时关闭这两个设置,否则它会让一些代码保持打开状态。
异常小:

( 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
d4so4syb

d4so4syb3#

我在一个长时间运行的php cli脚本上遇到了同样的情况(它监听redis列表;每个动作都很快,但脚本基本上永远运行)。
我在开始时创建pdo对象和一个prepared语句,然后重用它们。
在我开始写剧本的第二天,我得到了完全相同的错误:

PHP Warning:  Error while sending STMT_EXECUTE packet. PID=9438 in /...redacted.../myscript.php on line 39

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

在我的情况下,这是一个开发服务器,没有负载,mysql是在同一个盒子上。。。所以不太可能来自外部因素。这很可能与我使用同一个mysql连接太长时间,并且超时有关。而且pdo也不麻烦,所以任何后续查询都只会返回“mysql服务器已经消失”。
在mysql中检查“wait\u timeout”的值:

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.06 sec)

mysql> show local variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

我看到28800秒=8小时,这似乎与我犯错误的时间一致。
在我的例子中,重新启动mysql服务器,或者将wait\u timeout设置得很低,同时保持同一个php worker运行,这使得重现问题变得非常容易。
总体情况:
pdo不关心连接是否超时,也不会自动重新连接。如果在pdo查询周围放置try/catch,脚本将永远不会崩溃,并继续使用过时的pdo示例。
stmt\u execute警告可能是偶然的;因为连接超时的脚本使用的是prepared语句,而超时后的第一个查询恰好使用的是prepared语句
回到你的案子
从理论上讲,laravel 5对此问题免疫:https://blog.armen.im/en/laravel-4-and-stmt_prepare-error/ ; 你用的东西以外的照明,甚至直接裸pdo?另外,我不确定laravel在检测到丢失的连接时会做什么(是否重新连接并重建准备好的语句?),这可能值得进一步挖掘。
检查mysql wait\u超时值,如果太低,则增加它
如果不是一直都这样,请查看错误是否与服务器/db负载相关。高负载会使事情(尤其是大型sql查询)慢几倍,以至于达到其他一些mysql超时,比如最大执行时间。
查看是否将pdo查询 Package 在try/catch块中,并使用它重试查询;这可能是为了防止连接错误冒出来。

相关问题