Thursday, September 3, 2015

Second day with InnoDB transparent page compression

My first day with InnoDB transparent page compression didn't turn out OK, but I am an optimist so onto day 2. I gave up trying to use it on a host with CentOS 6.6, XFS and a 3.10.53 kernel. I had a bit more luck with a Fedora 19 host using XFS and a 3.14.27 kernel and I ran linkbench.

You can follow along here or at bug 78277.

I configured linkbench with maxid1=100M and the database was about 100GB after the load. I used MySQL 5.7.8 with transparent page compression. I won't share the performance results just yet, but at the and of 7 days of the query test I shutdown mysqld and my storage device (400G Intel s3700) had 180G free space. I confirmed that I was able to use at least 176 of the 180 GB, which was a good sign. However, the device was only able to write at ~60 MB/second while making copies of a 2G test file. File copies were writing at ~200 MB/second on a second host that has the same hardware, the same amount of free space, but didn't use hole-punch for compression. So this is one more cost of using hole-punch, it makes the filesystem much slower. But I don't blame XFS. I don't think that doing a hole-punch on every small write was the intended use for that feature.

Then I ran DROP DATABASE for the database used by linkbench. It contained 3 unpartitioned tables. Two were ~60G and one was ~10G. The DROP TABLE took 8 minutes. While that ran there were many warnings like this:
[Warning] InnoDB: Trying to close/delete/truncate tablespace 'linkdb/linktable' but there are 1 pending operations on it.
[Warning] InnoDB: Trying to close/delete/truncate tablespace 'linkdb/linktable' but there are 1 pending operations on it.

Then it got more exciting -- a long semaphore wait courtesy of a slow unlink. Alas the DROP TABLE finished before the background thread killed mysqld.
InnoDB: A long semaphore wait:--Thread 139616032126720 has waited at row0purge.cc line 830 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x327b74a8 created in file dict0dict.cc line 1153
a writer (thread id 139616238388992) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0purge.cc line 830
Last time write locked in file /home/mdcallag/b/mysql-5.7.8-rc/storage/innobase/row/row0mysql.cc line 4202
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:


No comments:

Post a Comment