Mysql slave state invalidating

01-Feb-2018 05:37

40019 SET @@session.max_insert_delayed_threads=0*/; /*! 50003 SET @[email protected]@COMPLETION_TYPE, COMPLETION_TYPE=0*/; DELIMITER /*! */; # at 444601893 #130323 server id 1 end_log_pos 444601987 Query thread_id=216095 exec_time=1 error_code=0 SET TIMESTAMP=1364099743/*! */; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! But the first step is to identify what about is making it slow. You should have it available but it should be all free, or almost all free. Is anything else on the system competing for resources? so your slave's binlogs will contain row-based logging of events received from the master.Below is the list of your questions that I wasn’t able to answer during the webinar, with responses: Q: Hi Sveta.

mysql slave state invalidating-3mysql slave state invalidating-11mysql slave state invalidating-65mysql slave state invalidating-24

row *************************** Slave_IO_State: Waiting for master to send event Master_Host: dware-master Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.001761 Read_Master_Log_Pos: 147709683 Relay_Log_File: relay-bin.000126 Relay_Log_Pos: 166343455 Relay_Master_Log_File: binlog.001740 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: 166343312 Relay_Log_Space: 11142734247 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: Seconds_Behind_Master: 49451 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: 1 1 row in set (0.00 sec) ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ | Id | User | Host | db | Command | Time | State | Info | ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ | 1135 | system user | | NULL | Connect | 195917 | Waiting for master to send event | NULL | | 1136 | system user | | NULL | Connect | 49451 | invalidating query cache entries (table) | NULL | | 11608 | dbuser1 | dware.prod:36246 | NULL | Query | 0 | NULL | show processlist | ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ Query Cache Status on Slave : ============================= ------------------------- ---------- | Variable_name | Value | ------------------------- ---------- | Qcache_free_blocks | 1 | | Qcache_free_memory | 33536856 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 5223 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | ------------------------- ---------- 8 rows in set (0.00 sec) ===== -------------------- --------------------------- -------------------------- | @@query_cache_size | @@global.query_cache_type | @@local.query_cache_type | -------------------- --------------------------- -------------------------- | 33554432 | ON | ON | -------------------- --------------------------- -------------------------- Will Disabling the Query Cache Increase the Performance on the Slave, by Observing the Qcache Status.

*/; # at 444601987 # at 444602072 # at 444603110 # at 444604148 # at 444605186 # at 444606224 # at 444607262 # at 444608300 # at 444609338 # at 444610372 # at 444611410 # at 444612448 # at 444613486 =========== EDIT : 28th March,2013 ======== *************************** 1.

Hello Experts, I have a server 1 as master and server 2 as slave .

Would changing these tables to Inno DB help the replicated database remain consistent? Switching to Inno DB might help, but it depends on what types of queries you use.

For example, if you often use the error for Inno DB too.

row *************************** Slave_IO_State: Waiting for master to send event Master_Host: dware-master Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.001761 Read_Master_Log_Pos: 147709683 Relay_Log_File: relay-bin.000126 Relay_Log_Pos: 166343455 Relay_Master_Log_File: binlog.001740 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: 166343312 Relay_Log_Space: 11142734247 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: Seconds_Behind_Master: 49451 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: 1 1 row in set (0.00 sec) ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ | Id | User | Host | db | Command | Time | State | Info | ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ | 1135 | system user | | NULL | Connect | 195917 | Waiting for master to send event | NULL | | 1136 | system user | | NULL | Connect | 49451 | invalidating query cache entries (table) | NULL | | 11608 | dbuser1 | dware.prod:36246 | NULL | Query | 0 | NULL | show processlist | ------- ------------- --------------------------------------- ------ --------- ------ -- ------------------------------------------ ------------------ Query Cache Status on Slave : ============================= ------------------------- ---------- | Variable_name | Value | ------------------------- ---------- | Qcache_free_blocks | 1 | | Qcache_free_memory | 33536856 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 5223 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | ------------------------- ---------- 8 rows in set (0.00 sec) ===== -------------------- --------------------------- -------------------------- | @@query_cache_size | @@global.query_cache_type | @@local.query_cache_type | -------------------- --------------------------- -------------------------- | 33554432 | ON | ON | -------------------- --------------------------- -------------------------- Will Disabling the Query Cache Increase the Performance on the Slave, by Observing the Qcache Status. */; # at 444601987 # at 444602072 # at 444603110 # at 444604148 # at 444605186 # at 444606224 # at 444607262 # at 444608300 # at 444609338 # at 444610372 # at 444611410 # at 444612448 # at 444613486 =========== EDIT : 28th March,2013 ======== *************************** 1.Hello Experts, I have a server 1 as master and server 2 as slave .Would changing these tables to Inno DB help the replicated database remain consistent? Switching to Inno DB might help, but it depends on what types of queries you use.For example, if you often use the error for Inno DB too.A: The state shows that the slave IO thread sent a request for a new event, and is waiting for the event from the master.