I’m having an issue where running an alter table or optimize table on any table in a database will lock all the other tables until the operation is complete. Data isn’t lost, it fills in after the operation is done, but this still concerns me and I’d like to figure out a way around it.
I only write to one node at a time, the other two are failover.
Note this is a three-node xtradb cluster with the following (centos 7) packages installed:
Percona-XtraDB-Cluster-56.x86_64 1:5.6.24-25.11.1.el7
Percona-XtraDB-Cluster-client-56.x86_64
Percona-XtraDB-Cluster-galera-3.x86_64
Percona-XtraDB-Cluster-server-56.x86_64
Percona-XtraDB-Cluster-shared-56.x86_64
percona-xtrabackup.x86_64 2.2.12-1.el7
Here is my mysql configuration:
[mysqld]
binlog_format = ROW
datadir = /percona
default_storage_engine = InnoDB
log_bin
log_error = /var/log/mysqld.log
transaction-isolation = READ-COMMITTED
sort_buffer_size = 512K
read_rnd_buffer_size = 512K
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 8G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 20M
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
wsrep_cluster_address = gcomm://xxxxx,xxxxx,xxxxx
wsrep_cluster_name = xxxxx
wsrep_node_address = xxxxx
wsrep_node_name = xxxxx
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_slave_threads = 4
wsrep_sst_auth = xxxxx:xxxxx
wsrep_sst_method = xtrabackup-v2
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
!includedir /etc/my.cnf.d
And here is an example table (every table is identical):
mysql> show table status like "al_20151118" \G
*************************** 1. row ***************************
Name: al_20151118
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 7487416
Avg_row_length: 95
Data_length: 716177408
Max_data_length: 0
Index_length: 260849664
Data_free: 5767168
Auto_increment: 23423276
Create_time: 2015-11-18 10:44:02
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=COMPRESSED
Comment:
1 row in set (0.00 sec)
mysql> describe al_20151118;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| time | datetime | NO | MUL | NULL | |
| source | varchar(10) | NO | | NULL | |
| hostname | varchar(50) | NO | MUL | NULL | |
| clientip | varchar(15) | NO | MUL | NULL | |
| method | varchar(10) | NO | | NULL | |
| request | varchar(255) | NO | | NULL | |
| response | smallint(5) unsigned | YES | | 0 | |
| size | int(10) unsigned | YES | | 0 | |
| referrer | varchar(255) | YES | | NULL | |
| useragent | varchar(255) | YES | | NULL | |
| extra1 | varchar(20) | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)
mysql> show create table al_20151118 \G
*************************** 1. row ***************************
Table: al_20151118
Create Table: CREATE TABLE `al_20151118` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`source` varchar(10) NOT NULL,
`hostname` varchar(50) NOT NULL,
`clientip` varchar(15) NOT NULL,
`method` varchar(10) NOT NULL,
`request` varchar(255) NOT NULL,
`response` smallint(5) unsigned DEFAULT '0',
`size` int(10) unsigned DEFAULT '0',
`referrer` varchar(255) DEFAULT NULL,
`useragent` varchar(255) DEFAULT NULL,
`extra1` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `al_20151118_time_idx` (`time`),
KEY `al_20151118_hostname_idx` (`hostname`),
KEY `al_20151118_clientip_idx` (`clientip`)
) ENGINE=InnoDB AUTO_INCREMENT=23436428 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
Any advice for the locking problem, my configuration, or my table schema would be greatly appreciated. Right now I’m running about 200 inserts/second, reads are very rare since this is for data archival with the occasional report.