The Replication is from a master server with InnoDB tables, replicating to a slave with TokuDB tables. Since PXC does not yet support TokuDB, and our databases using PXC, the only way we can use TokuDB is for slaves.
The goal for this study and test is to first see if replication from a master with InnoDB tables can work with a slave running TokuDB tables.
I will use Percona Xtrabackup to make the full backup and see how it works out.
# On the Master, take the full backup
The goal for this study and test is to first see if replication from a master with InnoDB tables can work with a slave running TokuDB tables.
I will use Percona Xtrabackup to make the full backup and see how it works out.
# On the Master, take the full backup
george.chilumbu@db-tokudb-1:~$ sudo innobackupex --user=kkuser --password=kkpass BK
160125 14:50:27 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
...
160125 14:50:33 Finished backing up non-InnoDB tables and files
160125 14:50:33 Executing LOCK BINLOG FOR BACKUP...
160125 14:50:33 [00] Writing xtrabackup_binlog_info
160125 14:50:33 [00] ...done
160125 14:50:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1965372'
xtrabackup: Stopping log copying thread.
.160125 14:50:33 >> log scanned up to (1965414)
160125 14:50:33 Executing UNLOCK BINLOG
160125 14:50:33 Executing UNLOCK TABLES
160125 14:50:33 All tables unlocked
160125 14:50:33 Backup created in directory '/BK/2016-01-25_14-50-27'
MySQL binlog position: filename 'mysql-bin.000001', position '8757', GTID of the last change '591ad2f9-c0ed-11e5-b791-0050569856f8:1-43'
160125 14:50:33 [00] Writing backup-my.cnf
160125 14:50:33 [00] ...done
160125 14:50:33 [00] Writing xtrabackup_info
160125 14:50:33 [00] ...done
xtrabackup: Transaction log of lsn (1965372) to (1965414) was copied.
160125 14:50:33 completed OK!
george.chilumbu@db-tokudb-1:~$
# Prepare the backup
george.chilumbu@db-tokudb-1:~$ sudo innobackupex --apply-log BK/2016-01-25_14-56-03/
160125 14:56:34 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
...
InnoDB: 5.6.24 started; log sequence number 1966604
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1966614
160125 14:56:39 completed OK!
george.chilumbu@db-tokudb-1:~$
On the slave, make a backup$sudo service mysql stop $sudo mv /var/lib/mysql /var/lib/mysql-backupOn the slave, try to import the backup from the master
$sudo mv BK/2016-01-25_15-39-11 /var/lib/mysql $sudo chmod 755 /var/lib/mysql/ $sudo chown mysql:mysql -R /var/lib/mysql/ $sudo service mysql restartConfigure and start replication. First, get the grid_purged value from your your new data dir
$ cd /var/lib/mysql
$cat xtrabackup_binlog_info
mysql-bin.000082 166451 bad31d61-6363-ee1a-433b-941925b8ed06:1-143
$mysql -uroot -p #log onto mysql
mysql> RESET MASTER;
mysql> SET GLOBAL gtid_purged="bad31d61-6363-ee1a-433b-941925b8ed06:1-143";
mysql>CHANGE MASTER TO MASTER_HOST=“10.1.5.106", MASTER_USER=“superuser", MASTER_PASSWORD=“superpass", MASTER_AUTO_POSITION=1;
mysql>start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: superuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000082
Read_Master_Log_Pos: 166451
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000082
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
When you now try to check available engines, you will notice that TokuDB is not listed:mysql> show engines; +--------------------+---------+--------------+------+------------+ | Engine | Support | Transactions | XA | Savepoints | +--------------------+---------+--------------+------+------------+ | InnoDB | DEFAULT |YES | YES | YES | | MRG_MYISAM | YES | NO | NO | NO | | MyISAM | YES | NO | NO | NO | | BLACKHOLE | YES | NO | NO | NO | | MEMORY | YES | NO | NO | NO | | FEDERATED | NO | NULL | NULL | NULL | | ARCHIVE | YES | NO | NO | NO | | CSV | YES | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | NO | NO | NO | +--------------------+---------+----------------------------------+ 10 rows in set (0.00 sec)To enable TokuDB, we have to run the following commands
mysql> INSTALL PLUGIN TokuDB SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.09 sec) mysql> INSTALL PLUGIN tokudb_file_map SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN tokudb_fractal_tree_info SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN tokudb_fractal_tree_block_map SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN tokudb_trx SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN tokudb_locks SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN tokudb_lock_waits SONAME 'ha_tokudb.so'; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL default_storage_engine=TokuDB; Query OK, 0 rows affected (0.00 sec)And when you do "Show engines" again, TokuDB should be listed this time.
Double check to make sure that the new replication was not interruptedmysql> show engines; +--------------------+---------+--------------+------+------------+ | Engine | Support | Transactions | XA | Savepoints | +--------------------+---------+--------------+------+------------+ | InnoDB | DEFAULT |YES | YES | YES | | MRG_MYISAM | YES | NO | NO | NO | | MyISAM | YES | NO | NO | NO | | BLACKHOLE | YES | NO | NO | NO | | MEMORY | YES | NO | NO | NO | | TokuDB | YES | YES | YES | YES | | FEDERATED | NO | NULL | NULL | NULL | | ARCHIVE | YES | NO | NO | NO | | CSV | YES | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | NO | NO | NO | +--------------------+---------+----------------------------------+ 10 rows in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: superuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000082
Read_Master_Log_Pos: 166451
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000082
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
comment:3Changed 9 months ago by george.chilumbu
Change the server's default storage engine back to TokuDB
Instead of using pt-online-schema-change, i will just use alter statements since this a small test database with only a few tables. I would not recommend using an alter statement in production as that would put a lock on the table that can last for several hours if the table is large.
mysql> SET GLOBAL default_storage_engine=TokuDB; Query OK, 0 rows affected (0.00 sec)Convert all InnoDB tables to TOkuDBmysql> show engines; +--------------------+---------+--------------+------+------------+ | Engine | Support | Transactions | XA | Savepoints | +--------------------+---------+--------------+------+------------+ | InnoDB | YES |YES | YES | YES | | MRG_MYISAM | YES | NO | NO | NO | | MyISAM | YES | NO | NO | NO | | BLACKHOLE | YES | NO | NO | NO | | MEMORY | YES | NO | NO | NO | | TokuDB | DEFAULT | YES | YES | YES | | FEDERATED | NO | NULL | NULL | NULL | | ARCHIVE | YES | NO | NO | NO | | CSV | YES | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | NO | NO | NO | +--------------------+---------+----------------------------------+ 10 rows in set (0.00 sec)
Instead of using pt-online-schema-change, i will just use alter statements since this a small test database with only a few tables. I would not recommend using an alter statement in production as that would put a lock on the table that can last for several hours if the table is large.
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA,".",TABLE_NAME, " ENGINE=TokuDB, ROW_FORMAT=TOKUDB_QUICKLZ;")
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND ENGINE='InnoDB'
INTO OUTFILE '/net/account/admin/george.chilumbu/BK/tokudb.sql';
mysql>
mysql> source /net/account/admin/george.chilumbu/BK/tokudb.sql;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table tb1\G
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=TokuDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=TOKUDB_QUICKLZ
1 row in set (0.00 sec)
One more time, verify that replication is workingmysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: superuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000082
Read_Master_Log_Pos: 166451
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000082
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
comment:4Changed 9 months ago by george.chilumbu
Tested to ensure that data entered on the master with InnoDb? tables is correctly replication on the Slave with TokuDB tables. All seems well.
comment:5Changed 9 months ago by george.chilumbu
Some other things to know about TokuDB replication can be found here
comment:6Changed 8 months ago by george.chilumbu
- Resolution set to fixed
- Status changed from accepted to closed
CONCLUSION
In our final analysis following extensive tests, we found some issue with replication regarding our setup of using a master which is part of PXC and has InnoDB tables, and a slave that runs on a standalone percona server and using TokuDB. The issue, to be more specific had to do with binary logs than it did with the setup per say.
I have discussed the replication issue in more detail here here
Basically, TokuDB slave should not have log-bin enabled for replication to work without problems especially in a multi-concurrent environment with heavy workload. The best would be to enable relay_log on the slave, and disable log-bin using sql_log_bin = 0. In our case, the prohibiting factor was GTID, which we use in our replication, and depends log-bin enabled on both the master and the slave. Otherwise in the environment not using GTID, replication would work just fine as long as binary logging is disabled on the slave.
In our final analysis following extensive tests, we found some issue with replication regarding our setup of using a master which is part of PXC and has InnoDB tables, and a slave that runs on a standalone percona server and using TokuDB. The issue, to be more specific had to do with binary logs than it did with the setup per say.
I have discussed the replication issue in more detail here here
Basically, TokuDB slave should not have log-bin enabled for replication to work without problems especially in a multi-concurrent environment with heavy workload. The best would be to enable relay_log on the slave, and disable log-bin using sql_log_bin = 0. In our case, the prohibiting factor was GTID, which we use in our replication, and depends log-bin enabled on both the master and the slave. Otherwise in the environment not using GTID, replication would work just fine as long as binary logging is disabled on the slave.
No comments:
Post a Comment