Invalidating query cache entries table replication radiocarbon dating and evolution

The question is not about efficiency of query cache, but rather a specific dead lock problem associated with the use of cache.You should invest more time in understanding the question rather than using your copy&paste skills with a hope that it [email protected] the dead lock "Waiting for query cache lock" is usually coming from write contention on the query cache, because it's too big and mysql spend more time to invalidate that cache, and therefore reducing the size of the cache to the lowest performing value is the way to go.You should contact the help centre via the link at the bottom of the page, to ask for your multiple accounts to be merged. row *************************** Id: 7929352 User: system user Host: db: NULL Command: Connect Time: 69899 State: invalidating query cache entries (table) Info: NULL replication shows Seconds_Behind_Master: 70429 Once the invalidating query cache entries (table) process has finished the slave catches up.去slave从库上查看运行状态,果然Seconds_Behind_Master: 28810,而且提示Slave_SQL_Running_State: System lock而且这个是变化的,有的时候提示Slave_SQL_Running_State: invalidating query cache entries (table),而且Seconds_Behind_Master: 29086 不停的增长着: show slave status\G *************************** 1.row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.249.3.11 Master_User: repl Master_Port: 3317 Connect_Retry: 60 Master_Log_File: mysql-bin.000027 Read_Master_Log_Pos: 909456416 Relay_Log_File: mysql-relay-bin.000020 Relay_Log_Pos: 19878093 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 19877880 Relay_Log_Space: 14868115381 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 12001 Master_UUID: cfc81670-fca1-11e5-926d-0017fa0041c0 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql show engine innodb status\G *************************** 1.How to repeat: I believe this might have something to do with row based replication of large tables.Rows: ~49,917,839 Inno DB latin1_swedish_ci Size: 28.7 Gi B I've now yet been able to repeat with 100% accuracy.

invalidating query cache entries table replication-36invalidating query cache entries table replication-62

From the bug reports, it seems to make a difference...The query mutex becomes a very hot spot especially if you are using RBR.In fact, with RBR there is a binlog event for each modified row, thus the mutex is acquired for every row. While sometime this works, others don’t; the query cache is “disabled” and you still see the SQL thread in state “invalidating query cache entries (table)”.That is related to a known bug, fixed in My SQL 5.5 :

Leave a Reply