Not the answer you need?
Register and ask your own question!

Lost data after stress test

IhorIhor EntrantCurrent User Role Beginner
Hello! I have a cluster with 3 nodes on dedicated server (Hetzner) running on centos 6, with Percona Cluster 5.7. When I run sysbench like

sysbench --mysql-table-engine=innodb --oltp-reconnect=on --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=MyPass --db-driver=mysql /usr/share/sysbench/tests/include/oltp_legacy/insert.lua prepare

replication between nodes passed well, but when I change --oltp-table-size=10000 to --oltp-table-size=100000 or more - the data between nodes are different
like

node1:
mysql> SELECT table_schema "sbtest", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+
+
+
| database_name | Data Base Size in MB |
+
+
+
| information_schema | 0.17187500 |
| mysql | 2.42676449 |
| percona | 0.01562500 |
| performance_schema | 0.00000000 |
| sbtest | 25.06250000 |
| sys | 0.01562500 |
+
+
+
6 rows in set (0.03 sec)

node2:
mysql> SELECT table_schema "sbtest", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+
+
+
| sbtest | Data Base Size in MB |
+
+
+
| information_schema | 0.17187500 |
| mysql | 2.42676449 |
| percona | 0.01562500 |
| performance_schema | 0.00000000 |
| sbtest | 23.06250000 |
| sys | 0.01562500 |
+
+
+
6 rows in set (0.02 sec)

node3:
mysql> SELECT table_schema "sbtest", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+
+
+
| sbtest | Data Base Size in MB |
+
+
+
| information_schema | 0.17187500 |
| mysql | 2.42676449 |
| percona | 0.01562500 |
| performance_schema | 0.00000000 |
| sbtest | 24.06250000 |
| sys | 0.01562500 |
+
+
+
6 rows in set (0.02 sec)



my.cnf from first node is:

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
# Please make any edits and changes to the appropriate sectional files
# included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://94.130.142.232,94.130.142.231,94.130.142.228

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node 1 address
wsrep_node_address=94.*.*.232

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

on the other nodes my.cnf are the same, except wsrep_node_address=

Is it normal or someone have a solution?

Comments

  • vadimtkvadimtk Contributor Percona Staff Role
    Ihor,

    To check correctly that data is different,
    can you run SELECT count(*) FROM tables in sbtest database on each node?
  • IhorIhor Entrant Current User Role Beginner
    count on each node is equal, but even the size of dump of database different. and when I opened two databases in SequelPro I found the different data
  • vadimtkvadimtk Contributor Percona Staff Role
    Can you show the example of different data from SELECT statements ?
  • vadimtkvadimtk Contributor Percona Staff Role
    Binary data does not show that the content of tables is different, that's why I am asking to compare SELECT statements.
    You can SELECT * INTO OUTFILE on all nodes and diff between files.
  • IhorIhor Entrant Current User Role Beginner
    looks strange.... files are equal. but why mysql show difference between data?
  • vadimtkvadimtk Contributor Percona Staff Role
    The difference in binary files does not mean that data is different. InnoDB may handle files differently on each node - this should not be a concern.
  • IhorIhor Entrant Current User Role Beginner
    Ok, thanks, I'll test it in production
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.