Executed_Gtid_Set

MySQL & MariaDB Percona Server for MySQL 5.6
1

Hello,
I have a 3 node galera cluster(multi master) running on:
Server version: 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11

My slave hangs off node1 for replication.

Working on getting my slave back online and in replication, I ran the slave status and noticed a lot of GTID’s…

Why do I have so many GTID’s? any way to fix this?

Retrieved_Gtid_Set: 06fb5658-2486-ee1a-5161-953c56ca7bce:165832513-175386408
Executed_Gtid_Set: 04d7e815-4ae6-11e5-9823-f0921c053a58:1-1757,
06fb5658-2486-ee1a-5161-953c56ca7bce:1-165832512,
1e07439c-b527-ee1a-4b83-848aa25c4186:1-26751103,
2a2d73a5-b708-ee1a-5136-1959e7687657:1-5148177,
33ab8564-b8da-ee1a-4fe7-f0972d9df64e:1-4970416,
4269889c-dc59-11e5-a71b-f0921c051a08:1-2,
46d537bb-5e47-ee1a-4c83-2832623a6cf2:1-560922031,
4f5db2a5-ad7e-ee1a-60bf-20c5ae2584af:1-2017813,
4f619bf4-9fd9-ee1a-77fd-0884a0db9e85:1-26577,
5d59cde8-48fe-11e5-b325-f0921c051a08:1-5,
5df96a91-ad7f-ee1a-5cf1-418e928bedc9:1-5524,
63142e8b-57ba-11e5-9e0f-f0921c053a58:1-7693,
8a787515-6130-ee1a-47c9-1867dc7e88a3:1-23723494,
96512664-4672-11e5-8111-f0921c053a58:1-74,
a3d75afd-bcb6-ee1a-4c59-a4730998eef5:1-1600696,
afa4bd1a-259c-ee1a-6529-79cd9cb9ffca:1-12016218,
b7a2d638-c2b3-11e5-9b86-f0921c053a58:1-4336,
c65bd610-df6d-11e5-be75-f0921c053a50:1-213816,
cfad0778-9fd5-ee1a-6d27-7da8c3d55ff2:1-23490,
d780d0ae-9fd2-ee1a-5060-107a160688f7:1-187069468,
db5b0676-2bc1-ee1a-4b42-b473a4f16db0:1-71047185,
ef1ced5c-6d91-ee1a-5c8d-2183aa126dbe:1-89956800,
f0ec6f66-acf2-ee1a-4ba7-c0b12402f82e:1-36542094
Auto_Position: 1
1 row in set (0.00 sec)

2

Hello,

Executed_Gtid_Set: is a representation of the set of all transactions that are logged in the binary log. Documentation for this variable here.

[url]MySQL :: MySQL 5.6 Reference Manual :: 17.1.4.5 Global Transaction ID Options and Variables

Should you proceed to face difficulties with getting your slave back online, please include full output from the following.

SHOW SLAVE STATUS;
SHOW FULL PROCESSLIST;

Could you provide along with this please provide the output for pt-summary and pt-mysql-summary from the percona toolkit.

Thank You,
Blair Page

3

Here is my show slave status:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.3.20
Master_User: cmon
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.002755
Read_Master_Log_Pos: 222302173
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 399
Relay_Master_Log_File: binlog.002697
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 event on table person.user; Can’t find record in ‘user’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log binlog.002697, end_log_pos 858821513
Skip_Counter: 0
Exec_Master_Log_Pos: 858820463
Relay_Log_Space: 61695171377
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 event on table person.user; Can’t find record in ‘user’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log binlog.002697, end_log_pos 858821513
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: b7a2d638-c2b3-11e5-9b86-f0921c053a58
Master_Info_File: /mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160315 10:20:25
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 06fb5658-2486-ee1a-5161-953c56ca7bce:165832513-184761871
Executed_Gtid_Set: 04d7e815-4ae6-11e5-9823-f0921c053a58:1-1757,
06fb5658-2486-ee1a-5161-953c56ca7bce:1-165832512,
1e07439c-b527-ee1a-4b83-848aa25c4186:1-26751103,
2a2d73a5-b708-ee1a-5136-1959e7687657:1-5148177,
33ab8564-b8da-ee1a-4fe7-f0972d9df64e:1-4970416,
4269889c-dc59-11e5-a71b-f0921c051a08:1-2,
46d537bb-5e47-ee1a-4c83-2832623a6cf2:1-560922031,
4f5db2a5-ad7e-ee1a-60bf-20c5ae2584af:1-2017813,
4f619bf4-9fd9-ee1a-77fd-0884a0db9e85:1-26577,
5d59cde8-48fe-11e5-b325-f0921c051a08:1-5,
5df96a91-ad7f-ee1a-5cf1-418e928bedc9:1-5524,
63142e8b-57ba-11e5-9e0f-f0921c053a58:1-7693,
8a787515-6130-ee1a-47c9-1867dc7e88a3:1-23723494,
96512664-4672-11e5-8111-f0921c053a58:1-74,
a3d75afd-bcb6-ee1a-4c59-a4730998eef5:1-1600696,
afa4bd1a-259c-ee1a-6529-79cd9cb9ffca:1-12016218,
b7a2d638-c2b3-11e5-9b86-f0921c053a58:1-4338,
c65bd610-df6d-11e5-be75-f0921c053a50:1-213816,
cfad0778-9fd5-ee1a-6d27-7da8c3d55ff2:1-23490,
d780d0ae-9fd2-ee1a-5060-107a160688f7:1-187069468,
db5b0676-2bc1-ee1a-4b42-b473a4f16db0:1-71047185,
ef1ced5c-6d91-ee1a-5c8d-2183aa126dbe:1-89956800,
f0ec6f66-acf2-ee1a-4ba7-c0b12402f82e:1-36542094
Auto_Position: 1
1 row in set (0.00 sec)

ERROR:
No query specified

Full process list:

mysql> show full processlist;
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
| 1 | cmon | 10.0.1.25:40838 | information_schema | Sleep | 0 | | NULL | 414 | 414 |
| 2 | cmon | 10.0.1.25:40839 | websportal | Sleep | 2 | | NULL | 0 | 3 |
| 4 | system user | | NULL | Connect | 82707 | Waiting for master to send event | NULL | 0 | 0 |
| 1669 | root | localhost | NULL | Query | 0 | init | show full processlist | 0 | 0 |
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
4 rows in set (0.00 sec)

4

pt-summary

Percona Toolkit System Summary Report

Network Config

Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme II BCM57810 10 Gigabit Ethernet (rev 10)
Controller | Broadcom Corporation NetXtreme II BCM57810 10 Gigabit Ethernet (rev 10)
FIN Timeout | 60
Port Range | 65000

Interface Statistics

interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 225000000000 400000000 0 225000000000 400000000 0
eth0 4500000000000 6000000000 150 600000000000000 400000000000 0
eth1 90000000000000 125000000000 15000 600000000000000 400000000000 0
eth2 0 0 0 0 0 0
eth3 0 0 0 0 0 0
eth4 0 0 0 0 0 0
eth5 0 0 0 0 0 0
bond0 90000000000000 125000000000 15000 1000000000000000 800000000000 0

Network Devices

Device Speed Duplex
========= ========= =========
bond0 20000Mb/s Full
eth0 10000Mb/s Full
eth1 10000Mb/s Full
eth2 Unknown! Unknown!
eth3 Unknown! Unknown!
eth4 Unknown! Unknown!
eth5 Unknown! Unknown!

Network Connections

Connections from remote IP addresses
10.0.1.20 100
10.0.1.25 10
10.0.3.20 1
10.0.4.4 2
10.0.4.5 1
10.0.254.77 1
Connections to local IP addresses
10.0.3.23 125
Connections to top 10 local ports
10050 100
22 10
57802 1
65531 1
65532 1
65534 1
States of connections
ESTABLISHED 15
LISTEN 4
TIME_WAIT 100

Top Processes

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1646 root 20 0 15692 1796 808 R 3.8 0.0 0:00.03 top
6216 mysql 20 0 155g 11g 7720 S 3.8 4.4 40:27.73 mysqld
47847 zabbix 20 0 79180 1192 676 S 1.9 0.0 111:08.93 zabbix_agentd
1 root 20 0 19356 716 472 S 0.0 0.0 0:17.88 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.96 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 66:04.28 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 5:10.33 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 stopper/0
6 root RT 0 0 0 0 S 0.0 0.0 0:23.53 watchdog/0

Notable Processes

PID OOM COMMAND
10325 -17 sshd
36036 -17 udevd
36037 -17 udevd

Simplified and fuzzy rounded vmstat (wait please)

procs —swap-- -----io---- —system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
1 0 0 0 600 50 0 0 1 1 98 0 0
0 0 0 0 0 0 4000 3000 0 1 99 0 0
0 0 0 0 0 150 1500 1500 0 0 100 0 0
1 0 0 0 0 25 700 700 0 0 100 0 0
1 0 0 0 0 0 500 600 0 0 100 0 0

The End

5

pt-mysql-summary

pt-mysql-summary

Percona Toolkit MySQL Summary Report

System time | 2016-03-16 13:34:42 UTC (local TZ: CDT -0500)

Instances

Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /mysql 0 0 /var/lib/mysql/mysql.sock

MySQL Executable

Path to executable | /usr/sbin/mysqld
Has symbols | No

Report On Port 3306

User | root@localhost
Time | 2016-03-16 08:34:42 (CDT)
Hostname | sql04.XXXXXXXXXXXXX.com
Version | 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11
Built On | Linux x86_64
Started | 2016-03-15 09:27 (up 0+23:07:13)
Databases | 19
Datadir | /mysql/
Processes | 3 connected, 1 running
Replication | Is a slave, has 0 slaves connected
Pidfile | /mysql/mysqld.pid (exists)

Processlist

Command COUNT(*) Working SUM(Time) MAX(Time)

Connect 1 1 80000 80000
Query 1 1 0 0
Sleep 2 0 0 0

User COUNT(*) Working SUM(Time) MAX(Time)

cmon 2 0 0 0
root 1 1 0 0
system user 1 1 80000 80000

Host COUNT(*) Working SUM(Time) MAX(Time)

10.0.1.25 2 0 0 0
1 1 80000 80000
localhost 1 1 0 0

db COUNT(*) Working SUM(Time) MAX(Time)

information_schema 1 0 0 0
NULL 2 2 80000 80000
websportal 1 0 0 0

State COUNT(*) Working SUM(Time) MAX(Time)

2 0 0 0
init 1 1 0 0
Waiting for master to send eve 1 1 80000 80000

Status Counters (Wait 10 Seconds)

Variable Per day Per second 11 secs
Aborted_clients 2
Aborted_connects 4
Binlog_snapshot_position 1500
Binlog_cache_use 2
Bytes_received 60000000000 700000 300000
Bytes_sent 600000000 6000 9000
Com_admin_commands 400000 4 3
Com_begin 5
Com_commit 2
Com_create_db 700
Com_create_table 700
Com_drop_db 1500
Com_drop_table 700
Com_insert 700
Com_reset 1
Com_select 100000 1 2
Com_set_option 25
Com_show_binlogs 300
Com_show_databases 3
Com_show_engine_status 300
Com_show_master_status 3000
Com_show_processlist 17500
Com_show_slave_status 4500
Com_show_slave_status_nolock 300
Com_show_status 90000 1 1
Com_show_storage_engines 300
Com_show_tables 1
Com_show_variables 3000
Com_slave_start 5
Com_slave_stop 1
Com_truncate 20
Com_update 5
Connections 1750 1
Created_tmp_disk_tables 17500 1
Created_tmp_files 6
Created_tmp_tables 150000 1 8
Flush_commands 1
Handler_commit 1500
Handler_external_lock 300000 3 3
Handler_read_first 700
Handler_read_key 2250
Handler_read_next 60
Handler_read_rnd 150000 1 1
Handler_read_rnd_next 40000000 450 450
Handler_rollback 10
Handler_write 35000000 400 450
Innodb_background_log_sync 90000
Innodb_buffer_pool_bytes_data 60000000 700
Innodb_buffer_pool_pages_flushed 30000
Innodb_buffer_pool_pages_old 250
Innodb_buffer_pool_read_requests 450000 5
Innodb_buffer_pool_reads 600
Innodb_buffer_pool_write_requests 150000 1
Innodb_checkpoint_max_age 1750000000 20000
Innodb_data_fsyncs 9000
Innodb_data_read 9000000 100
Innodb_data_reads 600
Innodb_data_writes 45000
Innodb_data_written 1000000000 12500
Innodb_dblwr_pages_written 30000
Innodb_dblwr_writes 1250
Innodb_history_list_length 1750
Innodb_ibuf_free_list 12500
Innodb_ibuf_segment_size 12500
Innodb_ibuf_size 1
Innodb_log_write_requests 12500
I

6

nnodb_log_writes 8000
Innodb_lsn_current 6000000000000 70000000
Innodb_lsn_flushed 6000000000000 70000000
Innodb_lsn_last_checkpoint 6000000000000 70000000
Innodb_master_thread_active_loops 700
Innodb_master_thread_idle_loops 90000
Innodb_max_trx_id 9000000000 100000
Innodb_mem_adaptive_hash 2500000000 30000
Innodb_mem_dictionary 600000000 7000
Innodb_mem_total 175000000000 2000000
Innodb_mutex_os_waits 500
Innodb_mutex_spin_rounds 25000
Innodb_mutex_spin_waits 8000
Innodb_os_log_fsyncs 3000
Innodb_os_log_written 10000000 125
Innodb_pages_created 3000
Innodb_pages_read 600
Innodb_pages_written 30000
Innodb_purge_trx_id 9000000000 100000
Innodb_rows_inserted 1500
Innodb_rows_read 1500
Innodb_num_open_files 15
Innodb_read_views_memory 300
Innodb_descriptors_memory 8000
Innodb_s_lock_os_waits 2000
Innodb_s_lock_spin_rounds 60000
Innodb_s_lock_spin_waits 2000
Innodb_available_undo_logs 125
Innodb_x_lock_os_waits 9
Innodb_x_lock_spin_rounds 300
Innodb_x_lock_spin_waits 5
Key_read_requests 175
Key_reads 1
Open_table_definitions 70
Opened_files 125000 1 25
Opened_table_definitions 1500
Opened_tables 800
Queries 600000 6 9
Questions 225000 2 5
Select_full_join 100000 1 1
Select_scan 175000 2 2
Slave_received_heartbeats 1
Sort_rows 150000 1 1
Sort_scan 35000
Table_locks_immediate 150000 1 1
Table_open_cache_hits 150000 1 1
Table_open_cache_misses 800
Threads_created 4
Uptime 90000 1 1

Table cache

Size | 1024
Usage | 6%

Key Percona Server features

Table & Index Stats | Disabled
Multiple I/O Threads | Enabled
Corruption Resilient | Enabled
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Disabled
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown

Percona XtraDB Cluster

wsrep_on | OFF

Plugins

InnoDB compression | ACTIVE

Query cache

query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%

Schema

Specify --databases or --all-databases to dump and summarize schemas

Noteworthy Technologies

SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0# InnoDB #####################################################
Version | 5.6.24-72.2
Buffer Pool Size | 143.1G
Buffer Pool Fill | 0%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
Log Buffer Size | 96M
Flush Method | O_DIRECT
Flush Log At Commit | 2
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 64
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 0
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 1657
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 2xnot started

MyISAM

Key Cache | 24.0M
Pct Used | 20%
Unflushed | 0%

Security

Users | 41 users, 0 anon, 2 w/o pw, 2 old pw
Old Passwords | 0

Binary Logging

Binlogs | 174
Zero-Sized | 0
Total Size | 171.2G
binlog_format | ROW
expire_logs_days | 7
sync_binlog | 0
server_id | 101
binlog_do_db |
binlog_ignore_db |

Noteworthy Variables

Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | NO_ENGINE_SUBSTITUTION
join_buffer_size | 256k
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
max_allowed_packet | 512M
thread_stack | 256k
log |
log_error | /var/log/mysqld.log
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON

Configuration File

Config File | /etc/my.cnf

[MYSQLD]
user = mysql
basedir = /usr/
datadir = /mysql
socket = /var/lib/mysql/mysql.sock
pid_file = mysqld.pid
port = 3306
log_error = /var/log/mysqld.log
log_warnings = 2
innodb_buffer_pool_size = 146562M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 200
innodb_doublewrite = 1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 96M
innodb_buffer_pool_instances = 8
innodb_log_files_in_group = 2
innodb_thread_concurrency = 64
innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2
innodb_stats_on_metadata = 0
default_storage_engine = innodb
gtid_mode = ON
log_bin = binlog
log_slave_updates = 1
enforce_gtid_consistency = ON
expire_logs_days = 7
server_id = 101
binlog_format = ROW
slave_net_timeout = 60
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
skip_name_resolve
memlock = 0
sysdate_is_now = 1
max_connections = 200
thread_cache_size = 512
query_cache_type = 0
query_cache_size = 0
table_open_cache = 1024
lower_case_table_names = 0
explicit_defaults_for_timestamp = 1

The End

7

So what I did alread ywas create a backup of one of the masters and then ran this on the slave:

mysql> reset master; mysql> source /mysql/dump.sql;
mysql> CHANGE MASTER TO MASTER_HOST=“10.0.3.20”, MASTER_USER=“cmon”, MASTER_PASSWORD=“cmon”, MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

mysql> start slave;
mysql> show slave status\G;

That starts replication again but it quickly errors out with the error you see above in the slave status. Is this becasue I reset the GTID and now its executing old binlogs that dont need to be executed?

8

Hello,

Could you please the error.log entries around thelocations on both master and slaves.
Please also include pt-summary and pt-mysql-summary from the master server’s as well.

Thank You,
Blair Page

9

I didnt have time to try and fix it. I just rebuilt the slave using this: [url]http://lostdomain.org/2013/01/01/fixing-mysql-slaves-with-percona-xtrabackup/[/url]
It is working correctly now.