Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.17
Description
master:
MariaDB [mysql]> show variables like '%bin%';
|
+-----------------------------------------+--------------------------------------------------+
|
| Variable_name | Value |
|
+-----------------------------------------+--------------------------------------------------+
|
| binlog_annotate_row_events | ON |
|
| binlog_cache_size | 32768 |
|
| binlog_checksum | NONE |
|
| binlog_commit_wait_count | 20 |
|
| binlog_commit_wait_usec | 500000 |
|
| binlog_direct_non_transactional_updates | OFF |
|
| binlog_format | ROW |
|
| binlog_optimize_thread_scheduling | ON |
|
| binlog_stmt_cache_size | 32768 |
|
| gtid_binlog_pos | 0-20077-142671 |
|
| gtid_binlog_state | 0-77-2218,0-20076-86398,0-76-2219,0-20077-142671 |
|
| innodb_api_enable_binlog | OFF |
|
| innodb_locks_unsafe_for_binlog | OFF |
|
| log_bin | ON |
|
| log_bin_trust_function_creators | OFF |
|
| max_binlog_cache_size | 18446744073709547520 |
|
| max_binlog_size | 1073741824 |
|
| max_binlog_stmt_cache_size | 18446744073709547520 |
|
| sql_log_bin | ON |
|
| sync_binlog | 0 |
|
|
|
show master status;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE INTO TABLE mysql.table_stats;
|
+------------------------------+-----------+--------------+------------------+
|
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
|
+------------------------------+-----------+--------------+------------------+
|
| tulwaurt006-mysql-bin.000143 | 806528297 | | |
|
+------------------------------+-----------+--------------+------------------+
|
1 row in set (0.00 sec)
|
|
|
mysqlbinlog --verbose --base64-output=DECODE-ROWS --start-position 807572906 tulwaurt006-mysql-bin.000143
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
DELIMITER /*!*/;
|
# at 807572906
|
# at 807573930
|
# at 807574874
|
# at 807575844
|
# at 807576798
|
# at 807577780
|
# at 807578796
|
# at 807579746
|
# at 807580714
|
# at 807581666
|
# at 807582664
|
# at 807583704
|
# at 807584716
|
# at 807585692
|
# at 807586722
|
# at 807587746
|
# at 807588730
|
# at 807589754
|
# at 807590722
|
# at 807591738
|
# at 807592720
|
# at 807593742
|
# at 807594722
|
# at 807595684
|
# at 807596646
|
# at 807597632
|
# at 807598608
|
# at 807599568
|
# at 807600596
|
# at 807601620
|
#150325 14:52:02 server id 20077 end_log_pos 807573930 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807574874 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807575844 Update_rows: table id 654
|
....
|
#150325 14:52:02 server id 20077 end_log_pos 807598608 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807599568 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807600596 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807601620 Update_rows: table id 654
|
#150325 14:52:02 server id 20077 end_log_pos 807602020 Update_rows: table id 654 flags: STMT_END_F
|
### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event
|
for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown
|
table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654###
|
Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event fo
|
r unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown tab
|
le #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654# at 807602020
|
#150325 14:52:02 server id 20077 end_log_pos 807602090 Query thread_id=1277 exec_time=0 error_code=0
|
SET TIMESTAMP=1427260922/*!*/;
|
SET @@session.pseudo_thread_id=1277/*!*/;
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
|
SET @@session.sql_mode=0/*!*/;
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
/*!\C utf8 *//*!*/;
|
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
|
SET @@session.lc_time_names=0/*!*/;
|
SET @@session.collation_database=DEFAULT/*!*/;
|
COMMIT
|
/*!*/;
|
# at 807602090
|
#150325 14:52:02 server id 20077 end_log_pos 807602128 GTID 0-20077-141913
|
slave:
Master_Log_File: tulwaurt006-mysql-bin.000143
|
Read_Master_Log_Pos: 852465402
|
Relay_Log_File: tulwaurt005-relay-bin.000022
|
Relay_Log_Pos: 807571992
|
Relay_Master_Log_File: tulwaurt006-mysql-bin.000143
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: No
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 1032
|
Last_Error: Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tulwaurt006-mysql-bin.000143, end_log_pos 807572906
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 807571693
|
Relay_Log_Space: 1927121418
|
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: NULL
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 1032
|
Last_SQL_Error: Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tulwaurt006-mysql-bin.000143, end_log_pos 807572906
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 20077
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 0-20077-141955
|
|
|
| Variable_name | Value |
|
+-----------------------------------------+--------------------------------------------------+
|
| binlog_annotate_row_events | ON |
|
| binlog_cache_size | 32768 |
|
| binlog_checksum | NONE |
|
| binlog_commit_wait_count | 20 |
|
| binlog_commit_wait_usec | 50000 |
|
| binlog_direct_non_transactional_updates | OFF |
|
| binlog_format | MIXED |
|
| binlog_optimize_thread_scheduling | ON |
|
| binlog_stmt_cache_size | 32768 |
|
| gtid_binlog_pos | 0-20077-142746 |
|
| gtid_binlog_state | 0-77-2218,0-20076-86398,0-76-2219,0-20077-142746 |
|
| innodb_api_enable_binlog | OFF |
|
| innodb_locks_unsafe_for_binlog | OFF |
|
| log_bin | ON |
|
| log_bin_trust_function_creators | OFF |
|
| max_binlog_cache_size | 18446744073709547520 |
|
| max_binlog_size | 1073741824 |
|
| max_binlog_stmt_cache_size | 18446744073709547520 |
|
| sql_log_bin | ON |
|
| sync_binlog | 0 |
|
|
|
|
|
MariaDB [(none)]> show global variables like '%para%';
|
+-------------------------------+--------+
|
| Variable_name | Value |
|
+-------------------------------+--------+
|
| slave_domain_parallel_threads | 10 |
|
| slave_parallel_max_queued | 131072 |
|
| slave_parallel_threads | 20 |
|
inspired by the will to work around MDEV-7363
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-7901 re-implement analyze table for low impact
-
- Closed
-
-
-
- Closed
-
Activity
This is actually expected. Persistent statistic updates are not replicated (and cannot be, really); so the contents of the tables can easily be different. If you update the table directly, which you normally shouldn't, and if you aren't careful enough to set sql_log_bin to 0 while doing that, bad things will happen.
While the replication on persistent statistics makes the assumption that the data is the same on the slaves, which is the goal for many replication architectures, I'm generally willing to take that risk compared to the consequence of a replicating analyze table blocking writes to tables for what can be 30mins+ for big tables and using a far amount of IO in the meantime. Is there another consequence to direct manipulation that I'm missing?
Would having engine independent stats table as engine=innodb with STATS_PERSISTENT=0 like innodb_index_stats/innodb_table_stats help?
CREATE TABLE `innodb_index_stats` (
|
...
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
|
Perhaps this should be a feature request for ANALYZE RESULTS_TO_BINLOG TABLE .... Is that more palatable?
Well, there are lots of consequences of replicating updates to persistent stats.
First, a slave might be not MariaDB 10.0/10.1. It can be for example MySQL 5.6 – while it's not our main focus to maintain downgrade/crossgrade replication, we wouldn't want to break it without a good reason.
Second, stat tables are currently MyISAM; updating them in a transaction with InnoDB tables creates a "transactional-non-transactional" mix, which the traditional replication is consistently bad at (also, I can't say right away how exactly it will affect parallel replication, but I don't expect anything good).
Third, replicating stat table events won't really solve anything – ANALYZE TABLE has always been replicated as a statement, statistics or not, so you'll just get both row events and the actual ANALYZE.
I can probably continue. I had at least one other reason, but I removed it because it doesn't sound right and I forgot what I meant while I was writing it.
Of course, all arguments concern certain different replication setups. If you talk about an idealistic one – replication is strictly between the same versions or up; you always use the same binlog format; you don't do any writes on slave and the data is exactly the same; settings (statistics-wise) are exactly the same – then yes, the current implementation is definitely not optimal. Unfortunately, we can't support ideal scenarios only.
If you can think of a solution which will improve the current approach without breaking various scenarios we need to support, you are more than welcome to request the feature... or even implement it and submit a patch 
According to the comment above, closing as "not a bug" for now.
Daniel has created a new task MDEV-7901 for re-implementing ANALYZE TABLE.
no problems with statement based
show master status; set binlog_format=statement;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE INTO TABLE mysql.table_stats;