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

Alter table locks all tables in database

nambroschnambrosch EntrantCurrent User Role Beginner
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.

Comments

  • nambroschnambrosch Entrant Current User Role Beginner
    I've found at least a partial solution using the pt-online-schema-change tool from the percona toolkit:

    pt-online-schema-change --alter "row_format=compressed" D=al,t=al_20151118 --execute
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.