Skip to content

Instantly share code, notes, and snippets.

@itochan

itochan/gist:4942757

Last active December 13, 2015 16:49
Show Gist options
  • Save itochan/4942757 to your computer and use it in GitHub Desktop.
Save itochan/4942757 to your computer and use it in GitHub Desktop.
Download ZIP
MySQLでext4からZFSに移行すると121倍高速化した件 (注意事項: http://twitter.com/i315/status/301583503936614400 http://twitter.com/i315/status/301584082104631296 )
Raw
gistfile1.txt
結構ストレージの環境が違いますし、キャッシュされていると思われるのでそこまで参考にしないでください。。
キャッシュ関連全くいじっていないのでこういう環境ではZFSに載せるほうが速いという話になります。
% cat /proc/scsi/scsi
Attached devices:
Host: scsi0 Channel: 00 Id: 00 Lun: 00
Vendor: ATA Model: ST3500418AS Rev: CC34
Type: Direct-Access ANSI SCSI revision: 05
Host: scsi2 Channel: 00 Id: 00 Lun: 00
Vendor: ATA Model: WDC WD20EFRX-68A Rev: 80.0
Type: Direct-Access ANSI SCSI revision: 05
Host: scsi3 Channel: 00 Id: 00 Lun: 00
Vendor: ATA Model: WDC WD20EFRX-68A Rev: 80.0
Type: Direct-Access ANSI SCSI revision: 05
ext4: Seagate ST3500418AS
ZFS: WDC WD20EFRX*2 mirror
この時点でかなりext4側に不利があります。
ST3500418ASはあと3台ほど家に転がっているので時間があれば検証したいです。
% cat /proc/cpuinfo G 'model name'
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E3-1265L V2 @ 2.50GHz
% free -m
total used free shared buffers cached
Mem: 31865 24397 7467 0 452 1476
-/+ buffers/cache: 22468 9397
Swap: 32459 0 32459
% mysqld --version
mysqld Ver 5.5.29-0ubuntu0.12.10.1 for debian-linux-gnu on x86_64 ((Ubuntu))
Ubuntu 12.10
ext4
% mysqlslap --user=root --password --host=localhost --port=3306 --concurrency=10 --iterations=3 --engine=innodb --auto-generate-sql --auto-generate-sql-load-type=write --number-of-queries=1000
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 8.280 seconds
Minimum number of seconds to run all queries: 8.146 seconds
Maximum number of seconds to run all queries: 8.373 seconds
Number of clients running queries: 10
Average number of queries per client: 100
ZFS on Linux
% mysqlslap --user=root --password --host=localhost --port=3306 --concurrency=10 --iterations=3 --engine=innodb --auto-generate-sql --auto-generate-sql-load-type=write --number-of-queries=1000
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.068 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 0.081 seconds
Number of clients running queries: 10
Average number of queries per client: 100
% sudo zpool get all tank
zpool: ../../lib/libzfs/libzfs_config.c:250: Assertion `nvlist_lookup_nvlist(config, ZPOOL_CONFIG_FEATURE_STATS, &features) == 0' failed.
ZFS on Linuxの不具合なのかわかりませんが `sudo zpool get all tank` ができなかったのでかわりに
% sudo zdb
tank:
version: 28
name: 'tank'
state: 0
txg: 13711
pool_guid: 10297202962727787091
hostname: 'kurokami.itochan.jp'
vdev_children: 1
vdev_tree:
type: 'root'
id: 0
guid: 10297202962727787091
children[0]:
type: 'mirror'
id: 0
guid: 3102353931888364043
whole_disk: 0
metaslab_array: 30
metaslab_shift: 34
ashift: 12
asize: 2000384688128
is_log: 0
create_txg: 4
children[0]:
type: 'disk'
id: 0
guid: 5922651257137734895
path: '/dev/disk/by-id/scsi-SATA_WDC_WD20EFRX-68_WD-WMCXXXXXXXXX-part1'
whole_disk: 1
DTL: 132
create_txg: 4
children[1]:
type: 'disk'
id: 1
guid: 1315060752594313481
path: '/dev/disk/by-id/scsi-SATA_WDC_WD20EFRX-68_WD-WMCXXXXXXXXX-part1'
whole_disk: 1
DTL: 24
create_txg: 4
resilvering: 1
% sudo zfs get all tank/mysql
NAME PROPERTY VALUE SOURCE
tank/mysql type filesystem -
tank/mysql creation Mon Feb 11 13:19 2013 -
tank/mysql used 53.9M -
tank/mysql available 1.09T -
tank/mysql referenced 53.9M -
tank/mysql compressratio 1.00x -
tank/mysql mounted yes -
tank/mysql quota none default
tank/mysql reservation none default
tank/mysql recordsize 16K local
tank/mysql mountpoint /var/lib/mysql local
tank/mysql sharenfs off default
tank/mysql checksum on default
tank/mysql compression off default
tank/mysql atime on default
tank/mysql devices on default
tank/mysql exec on default
tank/mysql setuid on default
tank/mysql readonly off default
tank/mysql zoned off default
tank/mysql snapdir hidden default
tank/mysql aclinherit restricted default
tank/mysql canmount on default
tank/mysql xattr on default
tank/mysql copies 1 default
tank/mysql version 5 -
tank/mysql utf8only off -
tank/mysql normalization none -
tank/mysql casesensitivity sensitive -
tank/mysql vscan off default
tank/mysql nbmand off default
tank/mysql sharesmb off default
tank/mysql refquota none default
tank/mysql refreservation none default
tank/mysql primarycache all default
tank/mysql secondarycache all default
tank/mysql usedbysnapshots 0 -
tank/mysql usedbydataset 53.9M -
tank/mysql usedbychildren 0 -
tank/mysql usedbyrefreservation 0 -
tank/mysql logbias latency default
tank/mysql dedup off default
tank/mysql mlslabel none default
tank/mysql sync standard default
tank/mysql refcompressratio 1.00x -
tank/mysql written 53.9M -
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment