测试mysqldump 压缩率和时间消耗
实验总结:
从本次实验数据可以看出,mysqldump通过|gzip参数可以将导出文件压缩53%,同时耗时也普通非压缩模式的2.3倍.
数据库环境:
#[root@db211_08:38:32 /data/57mysql/mysql3506/data]
#du -sch *
4.0K auto.cnf
4.0K backup-my.cnf
68K error.log
16K ib_buffer_pool
100M ibdata1
100M ib_logfile0
100M ib_logfile1
100M ib_logfile2
12M ibtmp1
4.0K innodb_status.7051
12M mysql
4.0K mysql.pid
1.1M performance_schema
4.0K relay-bin.000001
4.0K relay-bin.index
12K slow.log
676K sys
82G sysbench_testdata
15M wenyz
4.0K xtrabackup_binlog_info
4.0K xtrabackup_binlog_pos_innodb
4.0K xtrabackup_checkpoints
4.0K xtrabackup_info
502M xtrabackup_logfile
83G total
使用压缩方式使用时间及文件大小:
#time /usr/local/mysql57/bin/mysqldump -S /tmp/mysql3506.sock -pxxxx--master-data=2 --single-transaction -A|gzip >/data/backup/3506_zip.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
real 115m55.207s
user 128m53.269s
sys 1m25.633s
[root@db211_19:48:52 /data/backup]
#du -sh *
27G 3506_zip.sql
使用非压缩方式的时间及文件大小:
#time /usr/local/mysql57/bin/mysqldump -S /tmp/mysql3506.sock -pxxxx --master-data=2 --single-transaction -A >/backup/3506_uzip.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of ore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
real 50m18.354s
user 16m8.894s
sys 1m48.383s
[root@db211_22:21:31 /data/57mysql/mysql3506/data]
#du -sch /backup/
57G /backup/
57G total
两者对比:
- 导出文件大小与原数据目录对比及导出文件大小间对比:
27/82G=33%
57/82G=69.5%
27/57G=47% - 用时对比
115/50m=2.3倍