MySQLがWaiting for query cache lockによるビジネスダウンタイムの処理を記録する
午前10時、業務フィードバックサーバ502はアクセスできず、すべてのWEBサーバ、キャッシュ、データベースが接続可能であることを問い合わせる.しかし、MySQLクエリは一貫して詰まっていて、できるだけ早く業務を回復するために、直接データベース操作を再開して、この時業務は回復します.
原因を探し始める:1.mysqlエラーログを表示するには、このようにします.
分析した結果は以上ではない
12時になってまた問題が発生し、今回は再起動前にshow processlistを問合せ、ファイルmysql-uroot-pxxxxx-N-e「show processlist」>/tmp/sp.log分析でエクスポートしたファイルをエクスポートしました
解析では多くのWaiting for query cache lockが現れ、検索して以下のように発見された.
1.query_Cacheのlockはグローバルなロックであり,書き込みと読み取りが同時に大きい場合,ボトルネックはこのロックにある.
Qcacheを開くと読み書きに余分な消費が発生します.a.リードクエリが開始する前にキャッシュにヒットしているかどうかを確認する必要があります.b.リードクエリがキャッシュできる場合は、ライトキャッシュc.ライトデータの実行が完了すると、関連するすべてのテーブルのキャッシュを失効に設定する必要があります.キャッシュが大きい場合は、消費も大きくなり、システムを硬直させることができます.この操作はグローバルロックによって保護されるため
innodbテーブルでは、テーブルを変更するとキャッシュが無効になりますが、マルチバージョンのプロパティでは、この変更が他のトランザクションに一時的にブロックされ、トランザクションがコミットされるまですべてのクエリーでキャッシュが使用できません.トランザクションがコミットされるまで、長時間のトランザクションではクエリー・キャッシュのヒット率が大幅に低下します.
クエリー・キャッシュはグローバル・ロックによって保護されるため、クエリーのキャッシュ領域を大きく設定することはできません.クエリー・キャッシュ構成のメモリが大きく、クエリーの結果が大量に格納されている場合、クエリー・キャッシュが試行されると、このグローバル・ロックは長時間保持されます.クエリー・キャッシュのヒット検出操作およびキャッシュの失効検出もこのグローバル・ロックに依存するため、システムが硬直する可能性があります.
結論:1.ビジネスによって大量に書かれたビジネスに対して、クエリーキャッシュ2を使用することを避ける.クエリー・キャッシュを使用しても、あまり大きく設定しないでください.
1.クエリー・キャッシュの使用状況を表示する場合
2.サーバのquery_の表示方法Cache構成
最後に私の処理方法は次のとおりです.
set global query_cache_size=0;
プロファイルにquery_をコメントするcache_sizeこの行
原因を探し始める:1.mysqlエラーログを表示するには、このようにします.
2020-07-08 10:10:14 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7
2020-07-08 10:10:14 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply
2020-07-08 10:10:14 0 [Note] InnoDB: FTS optimize thread exiting.
2020-07-08 10:10:16 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7
2020-07-08 10:10:16 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply
2020-07-08 10:10:18 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7
2020-07-08 10:10:18 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply
2020-07-08 10:10:20 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7分析した結果は以上ではない
12時になってまた問題が発生し、今回は再起動前にshow processlistを問合せ、ファイルmysql-uroot-pxxxxx-N-e「show processlist」>/tmp/sp.log分析でエクスポートしたファイルをエクスポートしました
2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
7 event_schbseuler localhost NULL Daemon 6470 Waiting for next activation NULL 0.000
94 replication 192.168.0.174:39478 NULL Binlog Dump 6453 Master has sent all binlog to slave; waiting for binlog to be updatbse NULL 0.000
95 replication 192.168.0.149:49262 NULL Binlog Dump 6453 Master has sent all binlog to slave; waiting for binlog to be updatbse NULL 0.000
27798 bseu 192.168.0.173:37820 dbusercollege Query 348 Sending data SELECT `p`.`Id`, `p`.`CId`, `p`.`CName`, `p`.`CodeId`, `p`.`CreateTime`, `p`.`IsDeletbse`, `p`.`IsEdi 0.000
27925 bseu 192.168.0.158:60228 dbusercollege Query 349 Waiting for query cache lock UPDATE `article` SET `ArType` = 1, `AuthorId` = '', `AuthorName` = ' ', `AuthorPhoto` = '', `Co 0.000
27930 bseu 192.168.0.155:58588 dbusercollege Query 268 Sending data SELECT `a`.`Id`, `a`.`ArType`, `a`.`AuthorId`, `a`.`AuthorName`, `a`.`AuthorPhoto`, `a`.`Code`, `a`. 0.000
28589 bseu 192.168.0.173:40376 dbusercollege Query 348 Sending data SELECT `p`.`Id`, `p`.`CId`, `p`.`CName`, `p`.`CodeId`, `p`.`CreateTime`, `p`.`IsDeletbse`, `p`.`IsEdi 0.000
28779 bse 192.168.0.149:57342 bse Query 398 Waiting for query cache lock SELECT code,pcode FROM a_customer_relation 0.000
28780 bse 192.168.0.149:57344 bse Sleep 398 NULL 0.000
28781 bse 192.168.0.149:57347 bse Sleep 398 NULL 0.000
28782 bse 192.168.0.149:57346 bse Sleep 398 NULL 0.000
28783 read 192.168.0.149:57350 dbuser Query 398 Writing to net SELECT cast((cr.`code`) as INT) as code,(CASE WHEN cr.`code`=7070 THEN 0 WHEN pcr.`code` IS NULL THE 0.000
28786 bsusr 192.168.0.160:34322 dbuser Query 398 Update INSERT INTO customer (level_up_time, create_time, identity_number, features, job, source, state, typ 0.000
28799 bsusr 192.168.0.173:48238 dbuser Query 391 Waiting for query cache lock INSERT INTO customer_product_browse (views, date_time, create_time, customer_id, org_id, shopkeeper_ 0.000
28810 bsusr 192.168.0.173:48266 dbuser Query 386 Waiting for query cache lock DELETE FROM user_device WHERE channel = 'jPush' AND bindUser_id = 3092570 AND device_id = 'android_1 0.000
28825 bsusr 192.168.0.173:48294 dbuser Query 378 Waiting for query cache lock INSERT INTO customer_product_browse (views, date_time, create_time, customer_id, org_id, shopkeeper_ 0.000
28830 bsusr 192.168.0.160:34430 dbuser Query 377 Waiting for query cache lock INSERT INTO customer_product_browse (views, date_time, create_time, customer_id, org_id, shopkeeper_ 0.000
28831 bsusr 192.168.0.158:35156 dbuser Query 376 Waiting for query cache lock UPDATE shopkeeper SET shopkeeper_auth_time = 1594180592 WHERE id = 10539 0.000
28832 bsusr 192.168.0.173:48328 dbuser Query 375 Waiting for query cache lock UPDATE shopkeeper SET shopkeeper_auth_time = 1594180593 WHERE id = 36873 0.000
28844 bse 192.168.0.158:56890 bse Execute 365 Waiting for query cache lock INSERT INTO `t_shoping_cart` (`create_time` , `update_time` , `user_id` , `shop_id` , `product_id` , 0.000
28855 bsusr 192.168.0.160:34482 dbuser Query 360 Waiting for query cache lock UPDATE shopkeeper SET shopkeeper_auth_time = 1594180608 WHERE id = 51318 0.000
28869 bsusr 192.168.0.173:48476 dbuser Query 357 Waiting for query cache lock UPDATE shopkeeper SET shopkeeper_auth_time = 1594180611 WHERE id = 52191 0.000
28872 bse 192.168.0.173:37620 bse Execute 356 Waiting for query cache lock INSERT INTO `t_shoping_cart` (`create_time` , `update_time` , `user_id` , `shop_id` , `product_id` , 0.000
28874 bse 192.168.0.155:50136 bse Execute 356 Waiting for query cache lock INSERT INTO `t_shoping_cart` (`create_time` , `update_time` , `user_id` , `shop_id` , `product_id` , 0.000解析では多くのWaiting for query cache lockが現れ、検索して以下のように発見された.
1.query_Cacheのlockはグローバルなロックであり,書き込みと読み取りが同時に大きい場合,ボトルネックはこのロックにある.
Qcacheを開くと読み書きに余分な消費が発生します.a.リードクエリが開始する前にキャッシュにヒットしているかどうかを確認する必要があります.b.リードクエリがキャッシュできる場合は、ライトキャッシュc.ライトデータの実行が完了すると、関連するすべてのテーブルのキャッシュを失効に設定する必要があります.キャッシュが大きい場合は、消費も大きくなり、システムを硬直させることができます.この操作はグローバルロックによって保護されるため
innodbテーブルでは、テーブルを変更するとキャッシュが無効になりますが、マルチバージョンのプロパティでは、この変更が他のトランザクションに一時的にブロックされ、トランザクションがコミットされるまですべてのクエリーでキャッシュが使用できません.トランザクションがコミットされるまで、長時間のトランザクションではクエリー・キャッシュのヒット率が大幅に低下します.
クエリー・キャッシュはグローバル・ロックによって保護されるため、クエリーのキャッシュ領域を大きく設定することはできません.クエリー・キャッシュ構成のメモリが大きく、クエリーの結果が大量に格納されている場合、クエリー・キャッシュが試行されると、このグローバル・ロックは長時間保持されます.クエリー・キャッシュのヒット検出操作およびキャッシュの失効検出もこのグローバル・ロックに依存するため、システムが硬直する可能性があります.
結論:1.ビジネスによって大量に書かれたビジネスに対して、クエリーキャッシュ2を使用することを避ける.クエリー・キャッシュを使用しても、あまり大きく設定しないでください.
1.クエリー・キャッシュの使用状況を表示する場合
MariaDB [(none)]> show status like '%Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 0 | # , ， Qcache_lowmem_prunes ， ， query_cache_min_res_unit ， flush query cache ( )，
| Qcache_free_memory | 0 | #
| Qcache_hits | 420977 | #
| Qcache_inserts | 69901 | #
| Qcache_lowmem_prunes | 0 | #
| Qcache_not_cached | 4374 | # query_cache_type
| Qcache_queries_in_cache | 0 | #
| Qcache_total_blocks | 0 | #
+-------------------------+--------+2.サーバのquery_の表示方法Cache構成
MariaDB [(none)]> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 8388608 | # query ，
| query_cache_min_res_unit | 4096 | #
| query_cache_size | 0 | # ， 0， 1024
| query_cache_strip_comments | OFF |
| query_cache_type | ON | # 0(OFF)、1（ON）、2（DEMAND）
| query_cache_wlock_invalidate | OFF | # Query Cache， 1(TRUE)， Query Cache， 0(FALSE) Query Cache。
+------------------------------+---------+最後に私の処理方法は次のとおりです.
set global query_cache_size=0;
プロファイルにquery_をコメントするcache_sizeこの行