Mysql state invalidating

Posted by / 18-Nov-2017 11:11

First, read the manual, it's written at a very easy-to-understand level.

Second, the query cache can be operated in 3 modes only: on, off, and on-demand.

=========================================================== ----------- | version() | ----------- | 5.5.17 | ----------- 1 row in set (0.00 sec) -------------------------- ------------------- | Variable_name | Value | -------------------------- ------------------- | binlog_format | STATEMENT | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | time_format | %H:%i:%s | -------------------------- ------------------- 8 rows in set (0.00 sec) *************************** 1. These statements and the others you show are how replication keeps the slave thread's environment sane as it moves from event to event. Just changing to Barracuda won't make anything different...

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.001620 Read_Master_Log_Pos: 328943038 Relay_Log_File: relay-bin.000109 Relay_Log_Pos: 444602036 Relay_Master_Log_File: binlog.001607 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: 444601893 Relay_Log_Space: 7062540762 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: 32298 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) === -------------------------------- ------- | Variable_name | Value | -------------------------------- ------- | flush | OFF | | flush_time | 0 | | innodb_adaptive_flushing | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | | -------------------------------- ------- 5 rows in set (0.00 sec) =============== ------------------- ------- | Variable_name | Value | ------------------- ------- | log_slave_updates | OFF | ------------------- ------- 1 row in set (0.00 sec) === [[email protected] binlog]# mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=444601893 binlog.001607 | head -30 /*! */; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*! They're normal, and there's really nothing to see here... although if your real problem on the slave is a disk I/O bottleneck, and if you have sufficient memory and cpu, then you could on selected Inno DB tables after enabling Barracuda and possibly eliminate some bottleneck because on-disk compression should mean less disk I/O, but means more demand on CPU as well as more demand on the Inno DB Buffer Pool, which will try to store both the compressed and uncompressed versions of some pages.

From the perspective of your question, I would say that the justification of removing the query cache is not so much the overhead, but how Inno DB manages it.

For more information on how Inno DB interacts with the query cache, please read pages 213-215 of the book "High Performance My SQL (Second Edition)".

If I understand correctly, every insert or update will clear the cache for that table?

Edit: At the request of the user @Rolando My SQLDBA below, here's the info on My ISAM and INNODB. It would be nice if Inno DB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Apparently, you have a lot of queries that get invalidated rather quickly and are probably not being reused. I haven't touched anything such as reseting query cache for the year that the game has been up, I'm working with about 5000-1million rows depending on the table.Everything is being updated constantly and things being inserted into the tables. It often happens to see and hear of replication SQL thread almost always in state “invalidating query cache entries (table)” .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)”.

mysql state invalidating-62mysql state invalidating-90mysql state invalidating-28