Mysql – Slow thesqldump on big table

MySQLmysqldump

I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file.
using mysqldump --opt --add-drop-table --quick DATABASE BIG-TABLE >DATABASE.TABLE.sql to export the table ends up with poor read performance of around 8-15 MB/s with high iowait.

more details:

  • Not a destination disk issue, the destination disk is SSD as well with 120.00 MB/s writes (tested)

  • Not a source disk issue, according to GCP and dd the disk (sdb) is SSD volume with 180.00
    MB/s reads.

time dd if=BIG-TABLE.ibd of=/dev/null status=progress
2886054912 bytes (2.9 GB) copied, 17.044179 s, 169 MB/s^C
5709061+0 records in
5709060+0 records out
2923038720 bytes (2.9 GB) copied, 17.237 s, 170 MB/s

iostat:

iostat -m -x 5 -d /dev/sdb
Linux 3.10.0-957.1.3.el7.x86_64 (sql-server)      12/26/2018      _x86_64_        (8 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     4.91  170.00   29.18    19.75     0.73   210.57     3.78   19.00   21.78    2.77   4.92  98.08

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  103.60    0.00     7.33     0.00   144.99     1.68   16.31   16.31    0.00   9.43  97.66

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  145.60    0.00    12.71     0.00   178.81     1.90   13.03   13.03    0.00   6.75  98.22

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  140.00    0.00    12.73     0.00   186.24     2.03   14.52   14.52    0.00   6.98  97.78

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  172.40    0.00    17.16     0.00   203.84     2.81   16.16   16.16    0.00   5.69  98.14

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00   94.20    0.00     6.56     0.00   142.61     1.37   14.79   14.79    0.00  10.41  98.02

iotop enter image description here

cat my.cnf:

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table=ON
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=3G
innodb_log_file_size = 2G
innodb_old_blocks_time=1000
innodb_stats_on_metadata=off
innodb_log_buffer_size = 5M
bulk_insert_buffer_size = 1G
skip_name_resolve = ON
thread_cache_size=8
max_allowed_packet=4G
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 



general-log   = off
server-id       = 1


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

show create table:

| BIG-TABLE | CREATE TABLE `BIG-TABLE` (
  `major` int(11) NOT NULL,
  `minor` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `attributeid` int(11) NOT NULL,
  `value` varchar(128) NOT NULL,
  `longvalue` mediumtext NOT NULL,
  UNIQUE KEY `componentIndex` (`major`,`minor`,`version`,`attributeid`),
  KEY `componentAttrIdx` (`attributeid`),
  KEY `componentValIdx` (`value`,`attributeid`,`major`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Best Answer

Look at this bugreport: https://bugs.centos.org/view.php?id=15723

Long story short: kernel 3.10.0-957 shows much higher disk util than the previous kernel

Related Question