If there is no error message, or if the message doesn’t provide enough information to solve the issue, use the network troubleshooting methods discussed in the “Troubleshooting hardware resource usage” webinar.Align Dev Ops for your applications with Dev Ops for your SQL Server databases to increase speed of delivery and keep data safe.
A: It will timeout after milliseconds, and then switch to asynchronous replication. Would increasing these to eight offer better IO for slaves? A: Yes, an increased number of IO threads would most likely improve performance.Q: We’re using My SQL on r3.4xlarge EC2 instances (16 CPU). innodb_read_io_threads and innodb_write_io_threads =4. Other parameters that could help are similar to the ones discussed in “Inno DB Troubleshooting” and “Introduction to Troubleshooting Performance: What Affects Query Execution? You need to pay attention to Inno DB options that affect IO ( . A: What do you mean by “how many masters can [you] have working together”?去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.The only way to not acquire the query cache mutex in My SQL pre-5.5 is to compile My SQL without query cache. For My SQL 5.5 , to completely disable the query cache (thus, not acquiring the query cache mutex) is required to set query_cache_type=OFF at startup (not at runtime).
That also means that if query_cache_type=OFF at startup, you need to restart My SQL if later you want to enable the query cache (you can’t enable it at runtime).
For example, the position is 999 and I want to analyze the previous five events. Q: We are having issues with inconsistencies over time.
We also have a lot of “waiting for table lock” statuses during high volume usage.
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 :
The query mutex becomes a very hot spot especially if you are using RBR.