E_WARNING:发送STMT_PREPARE数据包时出错. PID = * [英] E_WARNING: Error while sending STMT_PREPARE packet. PID=*

查看：79
本文介绍了E_WARNING:发送STMT_PREPARE数据包时出错. PID = *的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

截至2019年1月30日世界标准时间(UTC)，您仍然可以赢得500点赏金，因为所有答案都无济于事！

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

  1. PDO::prepare(): MySQL server has gone away
  2. E_WARNING: Error while sending STMT_PREPARE packet. PID=10
  3. PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry(我的数据库通常似乎试图在同一秒内两次写入同一记录.我一直无法弄清楚为什么或如何复制它；它似乎与用户行为无关.)
  4. 以某种方式，前两种错误只出现在我的Rollbar日志中，而没有出现在服务器上的文本日志中或我的Slack通知中，因为所有错误都应该(以及其他所有错误)出现.

几个月来，我一直在看到类似这样的可怕日志消息，而且我完全无法重现这些错误(并且无法诊断和解决它们).

我还没有发现任何实际的症状或听到用户的任何抱怨，但是错误消息似乎并不重要，所以我真的想了解并解决根本原因.

我尝试将我的MySQL配置更改为使用max_allowed_packet=300M(而不是此建议，我还设置了以下内容(从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;的结果在这里.

我的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

关于我应该探索以诊断和解决这些问题的任何想法?谢谢.

解决方案

Re Slowlog:向我们展示您的my.cnf. [mysqld]部分中的更改了吗?通过SELECT SLEEP(12);对其进行测试，然后在文件和表格中进行查找.

查找查询的另一种方法:由于查询需要花费几分钟，因此请在认为可能正在运行时执行SHOW FULL PROCESSLIST;.

您有多少RAM?除非您至少有30GB的RAM，否则请不要使用max_allowed_packet=300M.否则，您有交换(甚至崩溃)风险.将该设置保持在RAM的1％以下.

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

关于deleted_at:您给出的链接开始于"deleted_at列不是很好的索引候选者".你误解了.它在谈论单列INDEX(deleted_at).我建议使用诸如INDEX(contact_id, job_class_name, execute_at, deleted_at)的复合索引.

158秒即可在一张小桌子上进行简单查询?可能发生了很多 other 事情.获取PROCESSLIST.

Re分离索引与复合索引:考虑两个索引:INDEX(last_name)和INDEX(first_name).您翻阅last_name索引以找到"James"，那该怎么办?翻阅"Rick"的其他索引不会帮助您找到我.

变量和全局状态分析

观察:

  • 版本:5.7.22-log
  • 1.00 GB RAM
  • 正常运行时间= 16天10:30:19
  • 您确定这是显示全球状态"吗?
  • 您未在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 -可以通过降低lru_scan_depth来解决"InnoDB:page_cleaner:预期的循环时间为1000毫秒……"

( 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应如何要求OS编写块.建议使用O_DIRECT或O_ALL_DIRECT(Percona)，以避免双重缓冲. (至少对于Unix.)请参阅chrischandler，以获取有关O_ALL_DIRECT的警告

( innodb_flush_neighbors ) = 1-将块写入磁盘时的次要优化. -对于SSD驱动器使用0; 1个用于HDD.

( 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

As of 2019-01-30 14:52 UTC, you can still win the 500-point Bounty because none of the answers have helped!

My Laravel 5.7 website has been experiencing a few problems that I think are related to each other (but happen at different times):

  1. PDO::prepare(): MySQL server has gone away
  2. E_WARNING: Error while sending STMT_PREPARE packet. PID=10
  3. PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry (My database often seems to try to write the same record twice in the same second. I've been unable to figure out why or how to reproduce it; it doesn't seem to be related to user behavior.)
  4. Somehow, those first 2 types of errors only ever appear in my Rollbar logs but not on the text logs on the server or in my Slack notifications, as all errors are supposed to (and all others do).

For months, I've continued to see scary log messages like these, and I've been completely unable to reproduce these errors (and have been unable to diagnose and solve them).

I haven't yet found any actual symptoms or heard of any complaints from users, but the error messages seem non-trivial, so I really want to understand and fix the root causes.

I've tried changing my MySQL config to use max_allowed_packet=300M (instead of the default of 4M) but still get these exceptions frequently on days when I have more than a couple of visitors to my site.

I've also set (changed from 5M and 10M) the following because of this advice:

innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M

As further background:

  • My site has a queue worker that runs jobs (artisan queue:work --sleep=3 --tries=3 --daemon).
  • There are a bunch of queued jobs that can be scheduled to happen at the same moment based on the signup time of visitors. But the most I see that have happened simultaneously is 20.
  • There are no entries in the MySQL Slow Query Log.
  • I have a few cron jobs, but I doubt they're problematic. One runs every minute but is really simple. Another runs every 5 minutes to send certain scheduled emails if any are pending. And another runs every 30 minutes to run a report.
  • I've run various mysqlslap queries (I'm completely novice though) and haven't found anything slow even when simulating hundreds of concurrent clients.
  • I'm using Laradock (Docker).
  • My server is DigitalOcean 1GB RAM, 1 vCPU, 25GB SSD. I've also tried 2GB RAM with no difference.
  • The results from SHOW VARIABLES; and SHOW GLOBAL STATUS; are here.

My my.cnf is:

[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

Any ideas about what I should explore to diagnose and fix these problems? Thanks.

解决方案

Re Slowlog: Show us your my.cnf. Were the changes in the [mysqld] section? Test it via SELECT SLEEP(12);, then look both in the file and the table.

Alternate way to find the query: Since the query is taking several minutes, do SHOW FULL PROCESSLIST; when you think it might be running.

How much RAM do you have? Do not have max_allowed_packet=300M unless you have at least 30GB of RAM. Else you are risking swapping (or even crashing). Keep that setting under 1% of RAM.

For further analysis of tunables, please provide (1) RAM size, (2) SHOW VARIABLES; and (3) SHOW GLOBAL STATUS;.

Re deleted_at: That link you gave starts with "The column deleted_at is not a good index candidate". You misinterpreted it. It is talking about a single-column INDEX(deleted_at). I am suggesting a composite index such as INDEX(contact_id, job_class_name, execute_at, deleted_at).

158 seconds for a simple query on a small table? It could be that there is a lot of other stuff going on. Get the PROCESSLIST.

Re Separate indexes versus composite: Think of two indexes: INDEX(last_name) and INDEX(first_name). You flip through the last_name index to find "James", then what can you do? Flipping through the other index for "Rick" won't help you find me.

Analysis of VARIABLES and GLOBAL STATUS

Observations:

  • Version: 5.7.22-log
  • 1.00 GB of RAM
  • Uptime = 16d 10:30:19
  • Are you sure this was a SHOW GLOBAL STATUS ?
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

innodb_buffer_pool_size -- I thought you had it at 213M, not 10M. 10M is much too small. On the other hand, you seem to have less than that much data.

Since the RAM is so small, I recommend dropping tmp_table_size and max_heap_table_size and max_allowed_packet to 8M. And lower table_open_cache, table_definition_cache, and innodb_open_files to 500.

What causes so many simultaneous connections?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 10M / 1024M = 0.98% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_size ) = 10M -- InnoDB Data + Index cache

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 375 / 638 = 58.8% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4M / 10M = 40.0% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( innodb_log_buffer_size / _ram ) = 16M / 1024M = 1.6% -- Percent of RAM used for buffering InnoDB log writes. -- Too large takes away from other uses for RAM.

( innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size ) = 48M * 2 / 10M = 960.0% -- Ratio of log size to buffer_pool size. 50% is recommended, but see other calculations for whether it matters. -- The log does not need to be bigger than the buffer pool.

( innodb_flush_method ) = innodb_flush_method = -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 16M, 16M ) / 1024M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping. -- Decrease tmp_table_size and max_heap_table_size to, say, 1% of ram.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( local_infile ) = local_infile = ON -- local_infile = ON is a potential security issue

( Select_scan / Com_select ) = 111,324 / 264144 = 42.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections -- increase max_connections and/or decrease wait_timeout

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

( 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

Abnormally large:

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

Abnormal strings:

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

这篇关于E_WARNING:发送STMT_PREPARE数据包时出错. PID = *的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆