2

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 |
6
  • If a lot of the bulk is in longvalue, consider compressing/uncompressing as you insert/select that column. (And make it MEDIUMBLOB).
    – Rick James
    Dec 26 '18 at 18:22
  • Where is mysqldump being run? Presumably on your machine, not on the VM machine?
    – Rick James
    Dec 26 '18 at 18:26
  • I'm running the mysqldump on that same VM as the database
    – Maoz Zadok
    Dec 26 '18 at 20:04
  • @MaozZadok Is there a reason your componentIndex is not a PRIMARY UNIQUE Key? It if could be PRIMARY one hidden index would not be necessary. Please post on pastbin.com or here TEXT results of SHOW GLOBAL STATUS; and SHOW GLOBAL VARAIBLES; Dec 27 '18 at 17:31
  • @MaozZadok What is result of SELECT @@innodb_io_capacity; Dec 27 '18 at 17:36
1

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.