Testing MySQL: ZFS vs UFS
I had some time ago the need to create instant backups of the mysql database. The desire existed for a long time, but somehow it still seemed to be living normally with replication and backup with a slave. But the cases are different, and the ability to take an instant snapshot from the master server's file system can make life much easier. I realized that I needed a snapshot . And where there is a snapshot , ZFS appears semi-automatically on the horizon. In addition, it still has some goodies, which at the moment I don’t really need me, but in principle, their presence can greatly brighten up life.The process of taking a snapshot of the file system, I still leave aside, but I'm trying to get some idea about ZFS compared to UFS in my conditions. Recently, I published in Habré a translation of material from John David Duncan . It describes everything quite tasty, but you have to try it yourself.
I tried…
')
I did not set myself the goal of organizing a thorough, super-reliable comparison of ZFS and UFS. Unfortunately, I have neither the strength nor the time for this. Especially since it is to test the exhibits, so that this testing satisfies all interested parties - it is absolutely unrealistic. Everyone has their own conditions and tasks. And better I will describe my conditions to you, and use publicly available tests. And let everyone make the conclusions himself! The only thing I would ask to share these findings in the comments.
So, the test system: dell r610, 2x intel xeon e5520, 8x2gb ecc ddr3 pc1066, hdd 4x146gb 15000rpm Seagate savvio 15k2, 2x bp 717v, perc 6 / I 256mb + bbu.
Drives merged into RAID5 + hotspare
FreeBSD 8.0-RELEASE GENERIC amd64
As a test, I drove the sql-bench from the MySQL package.
Server from ports: mysql-5.1.41.
In addition to the file system in the tests, I changed the mysql configs, again in order not to compose them painfully and do not paint in the article, I used the standard from / usr / local / share / mysql:
my-small.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf
The name of the config is reflected in the table headers. In addition, I tested my config, which is to some extent a hybrid of my-huge.cnf + my-innodb-heavy-4G.cnf, perhaps not the most effective ... For the configuration of my-innodb-heavy-4G.cnf, I added another UFS test with softupdates.
Testing MySQL: ZFS vs UFS
The table shows the number of tests performed in the first column, and the number of seconds spent on performing a test suite.
To make it easier to analyze the data, I perverted a little - put the TOTAL line at the top of the table, and below the decoding for gourmets.
| small | huge | innodb | hybrid | |||||||
| Operation | iterations | ufs | zfs | ufs | zfs | ufs | ufs + su | zfs | ufs | zfs |
| TOTALS | 897029 | 306.00 | 400.00 | 160.00 | 269.00 | 152.00 | 133.00 | 255.00 | 155.00 | 262.00 |
| alter_table_add | 100 | 2.00 | 4.00 | 2.00 | 4.00 | 3.00 | 3.00 | 4.00 | 3.00 | 4.00 |
| alter_table_drop | 91 | 2.00 | 4.00 | 3.00 | 5.00 | 3.00 | 2.00 | 4.00 | 3.00 | 4.00 |
| connect | 10,000 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 |
| connect + select_1_row | 10,000 | 4.00 | 5.00 | 4.00 | 5.00 | 5.00 | 4.00 | 4.00 | 4.00 | 5.00 |
| connect + select_simple | 10,000 | 4.00 | 4.00 | 3.00 | 3.00 | 4.00 | 4.00 | 4.00 | 3.00 | 4.00 |
| count_distinct | 1000 | 6.00 | 6.00 | 0.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| count_distinct_2 | 1000 | 8.00 | 8.00 | 0.00 | 0.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| count_distinct_big | 100 | 2.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| count_distinct_group | 1000 | 7.00 | 7.00 | 0.00 | 0.00 | 0.00 | 1.00 | 0.00 | 1.00 | 0.00 |
| count_distinct_group_on_key | 1000 | 6.00 | 8.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| count_distinct_group_on_key_parts | 1000 | 7.00 | 7.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| count_distinct_key_prefix | 1000 | 3.00 | 3.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| count_group_on_key_parts | 1000 | 8.00 | 9.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 | 0.00 | 1.00 |
| count_on_key | 50,000 | 58.00 | 66.00 | 3.00 | 4.00 | 3.00 | 3.00 | 3.00 | 5.00 | 4.00 |
| create + drop | 10,000 | 14.00 | 25.00 | 3:00 p.m. | 30.00 | 14.00 | 10.00 | 31.00 | 14.00 | 34.00 |
| create_MANY_tables | 10,000 | 12.00 | 18.00 | 12.00 | 25.00 | 11.00 | 10.00 | 25.00 | 11.00 | 24.00 |
| create_index | eight | 0.00 | 0.00 | 1.00 | 1.00 | 0.00 | 0.00 | 1.00 | 0.00 | 1.00 |
| create_key + drop | 10,000 | 16.00 | 68.00 | 16.00 | 87.00 | 16.00 | 16.00 | 11.00 | 17.00 | 90.00 |
| create_table | 31 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| delete_big | one | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| drop_index | eight | 1.00 | 1.00 | 0.00 | 0.00 | 0.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| drop_table | 28 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| drop_table_when_MANY_tables | 10,000 | 6.00 | 3.00 | 6.00 | 3.00 | 6.00 | 2.00 | 3.00 | 7.00 | 3.00 |
| insert | 50768 | 5.00 | 6.00 | 3.00 | 5.00 | 4.00 | 4.00 | 6.00 | 4.00 | 5.00 |
| insert_many_fields | 2000 | 2.00 | 1.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 2.00 | 2.00 |
| min_max_on_key | 70,000 | 8.00 | 10.00 | 4.00 | 6.00 | 4.00 | 5.00 | 4.00 | 5.00 | 6.00 |
| select_1_row | 100,000 | 8.00 | 8.00 | 10.00 | 10.00 | 7.00 | 8.00 | 8.00 | 8.00 | 9.00 |
| select_1_row_cache | 100,000 | 7.00 | 8.00 | 4.00 | 4.00 | 3.00 | 2.00 | 2.00 | 2.00 | 2.00 |
| select_2_rows | 100,000 | 8.00 | 9.00 | 11.00 | 12.00 | 9.00 | 9.00 | 10.00 | 9.00 | 10.00 |
| select_big | 70 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 | 0.00 | 0.00 | 1.00 |
| select_big_str | 10,000 | 2.00 | 4.00 | 3.00 | 4.00 | 3.00 | 3.00 | 3.00 | 2.00 | 2.00 |
| select_cache | 10,000 | 24.00 | 24.00 | 1.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 |
| select_cache2 | 10,000 | 25.00 | 24.00 | 24.00 | 24.00 | 25.00 | 24.00 | 25.00 | 25.00 | 24.00 |
| select_column + column | 100,000 | 8.00 | 8.00 | 11.00 | 10.00 | 8.00 | 10.00 | 8.00 | 7.00 | 7.00 |
| select_distinct | 800 | 1.00 | 2.00 | 1.00 | 0.00 | 1.00 | 1.00 | 0.00 | 1.00 | 0.00 |
| select_group | 2800 | 2.00 | 3.00 | 0.00 | 1.00 | 0.00 | 1.00 | 0.00 | 0.00 | 1.00 |
| select_group_when_MANY_tables | 10,000 | 1.00 | 2.00 | 2.00 | 3.00 | 3.00 | 2.00 | 4.00 | 3.00 | 3.00 |
| select_join | 100 | 0.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 | |
| select_key_prefix_join | 100 | 2.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 | 0.00 | 0.00 |
| select_many_fields | 2000 | 5.00 | 6.00 | 2.00 | 2.00 | 2.00 | 2.00 | 3.00 | 2.00 | 2.00 |
| select_range | 410 | 22.00 | 23.00 | 2.00 | 2.00 | 2.00 | 1.00 | 2.00 | 2.00 | 1.00 |
| select_simple | 100,000 | 3.00 | 5.00 | 5.00 | 5.00 | 4.00 | 3.00 | 3.00 | 5.00 | 2.00 |
| select_simple_cache | 100,000 | 3.00 | 4.00 | 4.00 | 4.00 | 4.00 | 2.00 | 3.00 | 4.00 | 3.00 |
| select_simple_join | 500 | 0.00 | 0.00 | 0.00 | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| wisc_benchmark | 114 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
I would like to dwell on this, but I asked above to share your conclusions in the comments, it would probably not be fair to finish without my own conclusions :)
And my conclusion is that the ZFS overhead is felt on the hardware reyd, and in my specific conditions I don’t want to switch to ZFS, and I’ll still try to take the pictures I need on UFS2. But I have somewhere in a servachek zagashniki without a raid and with several SATA disks, probably there will be the potential of ZFS.
Source: https://habr.com/ru/post/78895/
All Articles