MySQL File System Fragmentation Benchmarks
Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I've got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:
The fragmentation we speak in this article is filesystem fragmentation or internal table fragmentation which affects performance of full table scan. Not all queries are going to be affected same way, for example point select reading single page should not be significantly affected - ie you may not be affected as bad as we show here.
Benchmarks were done using this script:
The benchmark run with following simple shell script:
-
[root@DB10 ~]# for i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e'drop database test1'; mysql -e'create database test1'; done;
-
TABLES: 1; total records: 10000000; WRITE rows per sec: 9498.478176443 , reads rows per sec: 45142.234447809sec.
-
TABLES: 10; total records: 10000000; WRITE rows per sec: 8401.0627704619 , reads rows per sec: 18970.855770078sec.
-
TABLES: 100; total records: 10000000; WRITE rows per sec: 6689.2612428044 , reads rows per sec: 2212.7170957877sec.
-
TABLES: 1000; total records: 10000000; WRITE rows per sec: 5180.4069362984 , reads rows per sec: 1346.7226581156sec.
-
TABLES: 10000; total records: 10000000; WRITE rows per sec: 262.6496819245 , reads rows per sec: 1169.4009695919sec.
The script creates specified amount of tables and does specified number of inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64) and set innodb_buffer_pool_size=8G innodb_flush_logs_at_trx_commit=2 innodb_log_file_size=256M innodb_flush_method=O_DIRECT for Innodb.
The tables were sized so they are considerably larger than amount of memory in box so full table scan will be IO bound.
As you can see from MyISAM results (above) the insert speeds does not degrade that badly until going from 1000 to 10000 tables, even though table_cache was just 64. I expect this is because updating index header (most complex part of opening and closing MyISAM table) can happen by OS in background and flushing 1000 pages each 30 seconds is not big overhead for this server configuration.
Going to 10000 tables however insert speed dropped 20 times. This could be because ext3 does not like so many files in directory or because random updates to 10000 distinct pages for index header updates not to mention modification time update is a lot of overhead. During this last test box felt really sluggish responding 10+ seconds for as simple command as "ls" even though loadavg was about 1. In the process list I could see some single value insert statements taking over 5 seconds... So it does not work very well.
Note: As I checked later contrary to my expectation this filesystem was created without dir_index option which should add significant overhead for insert with many tables.
The read performance, which is the main measurement for this benchmark suffered quite as expected - with 10000 tables it was 40 times worse than with single table! Looking at IOSTAT I could see average read size of being just 4K which means ext3 does horrible job in this case of doing extent allocation. Note however even 100 tables are enough to drop performance 20 times.
Innodb in single tablespace mode showed following results:
-
[root@DB10 ~]# for i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e'drop database test1'; mysql -e'create database test1'; done;
-
TABLES: 1; total records: 10000000; WRITE rows per sec: 4919.7214223134 , reads rows per sec: 25408.766711241sec.
-
TABLES: 10; total records: 10000000; WRITE rows per sec: 4887.5507251885 , reads rows per sec: 11848.973747839sec.
-
TABLES: 100; total records: 10000000; WRITE rows per sec: 4007.1215976416 , reads rows per sec: 11826.941546043sec.
-
TABLES: 1000; total records: 10000000; WRITE rows per sec: 2838.9678814081 , reads rows per sec: 13758.602641499sec.
-
TABLES: 10000; total records: 10000000; WRITE rows per sec: 803.46939763369 , reads rows per sec: 3629.3610005806sec.
As you can see insert speed starts slower but degrades less, even though drop from 1000 to 10000 tables is dramatic as well. The read speed is also slower (expected as table was larger for same amount of rows) though it drops at different rate. Interesting enough it dropped just 2 times and was about same for 10 100 and 1000 tables which could be because of extent allocation for rather large tables. For 10000 tables we had just 1000 of 4K rows in the table which caused too much space allocated as single pages. I expect if we would use larger amount of rows read performance for 10000 tables would be close.
Innodb with innodb_file_per_table=1 had the following results:
-
[root@DB10 ~]# for i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e'drop database test1'; mysql -e'create database test1'; done;
-
TABLES: 1; total records: 10000000; WRITE rows per sec: 4479.3690015631 , reads rows per sec: 25554.477094788sec.
-
TABLES: 10; total records: 10000000; WRITE rows per sec: 4279.1557765714 , reads rows per sec: 16787.265656296sec.
-
TABLES: 100; total records: 10000000; WRITE rows per sec: 3609.974742019 , reads rows per sec: 16525.06580466sec.
-
TABLES: 1000; total records: 10000000; WRITE rows per sec: 2130.8515988384 , reads rows per sec: 11602.826401997sec.
-
TABLES: 10000; total records: 10000000; WRITE rows per sec: 434.330528194 , reads rows per sec: 5157.1389149296sec.
Insert performance is close, the difference is perhaps explained by the fact files needed to be constantly extended (meta data updates) and reopened for more than 100 tables. Read performance starts close but degrades less for 10 and 100 tables and when better again for 10000 tables. I can't explain why it is a bit worse for 1000 tables thought as I did only one run (It took more than 24 hours) it also could be some activity spike.
A bit better performance in this case can be perhaps explained but a bit larger increment how tablespaces are allocated comparted to internal allocation from single tablespace.
Summary: There are few basic things we can learn from these results
- Concurrent growth of many tables causes data fragmentation and affects table scan performance nadly
- MyISAM suffers worse than Innodb
- Innodb extent allocation works (perhaps would be good option for MyISAM as well)
- Innodb suffers fragmentation less if it stores different tables in different files.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
19 Comments
Were you using dir_index for the filesystems?
Comment :: March 22, 2008 @ 6:33 pm
was this a typo?
>>4K which means ext2 does horrible job<< (ext2 or was it ext3?)
Comment :: March 22, 2008 @ 7:30 pm
Nice article Peter,
We had a similar issue with the number of files per directory on a migration project I worked on in the past. A perl script was taking articles from a legacy db and dumping each as individual files onto a linux FS (ext3). After a fast start, as you found it slowed down dramatically.
Our solution was to add an additional step to the dump article script to split the files into 1000 per directory. The performance was then stable throughout the process. The funny thing was I offered a slab of beer to the IT portion of the company for someone who could solve it (got many more people interested).
Maybe you should add another recommendation:
No more than 1000 files per directory on ext3
Have Fun
Paul
Comment :: March 22, 2008 @ 8:32 pm
Simetrical,
I just checked and it was not used… I thought it was set by default already on CentOS4 box. So yes lack of dir_index should be one of the things which impacted insert speed.
Comment :: March 22, 2008 @ 8:55 pm
Diego,
Thanks fixed. I only tested with ext3 you’re welcome to test with ext2 and let us know
Comment :: March 22, 2008 @ 9:23 pm
PaulM,
This is not really about number of files per directory, though I have not tried hashing it. The point is files get fragmented and file read speed becomes low - while scanning large tables the penalty of locating and opening the file is not huge.
Comment :: March 22, 2008 @ 9:25 pm
I might be wrong but if you have table_cache set to a large value then dir_index won’t really make much difference once the file is opened.
This will be amortized over the entire length of the DB server.
Comment :: March 22, 2008 @ 10:05 pm
[...] в Delicious Перт Зайцев провел исследование влияния фрагментации в файловой системе [...]
Pingback :: March 22, 2008 @ 11:00 pm
Kevin,
table_cache is not the only one you should be looking for - if you’re using innodb_file_per_table setting innodb_open_files also need to be set high enough so no reopens are required.
I specially kept those default so we get the open/reopen overhead factored in.
Comment :: March 23, 2008 @ 10:53 am
Peter,
If you’re interested in testing the performance of open() then you should do this in a dedicated benchmark.
If you test two things you’re going to get different results for different filesystems on different OSes.
If you just test fragmentation you would get more and similar results.
Fragmentation with MyISAM InnoDB can happen with just two tables, each taking INSERT load in round robin fashion.
Kevin
Comment :: March 23, 2008 @ 11:04 am
Kevin,
Of course in pure science you could spend a lot of time on this and test different things… Though I mainly tested what had practical interest for me at that point.
Regarding fragmentation - you can’t really assume 2 tables are enough without knowing about internal implementation of OS and tables. First it is quite possible to design filesystem which would be able to handle small amount of growing files well but not large number of files.
But what is even more important is not all fragmentations are same.
Consider the worst case with 2 files with blocks going as 12121212121212 drive/raid/os or storge engine itself will do read-ahead which will fetch few blocks with single read. 1MB read will have only 512K of data for the given table but it is still much better than getting single row with random read
Comment :: March 23, 2008 @ 1:49 pm
Peter.
I agree with your worst case scenario. This is what I tried to point out in my previous comment. Though maybe I didn’t do a good job expressing myself
The point I was trying to make is that in that situation there’s not much the filesystem CAN do.
It could TRY to pre-allocate both files in larger chunks but then you’d have angular velocity kick in on the HDDs.
InnoDB’s grow factor (which by default is 8M I believe) is a good balance.
Comment :: March 23, 2008 @ 1:52 pm
Kevin,
There ARE things filesystem can do. For example you could externally allocate space in extents, related to file size and filesystem size. For example if you have 50MB file allocating in 1MB blocks would still cause no more than 2% space waste. For 10K image of course you do not want.
Another optimization which can be done is called delayed allocation. When you perform writes you can actually allocate space only when you flush data to disk, this way you can accumulate larger fragments.
It is not Innodb single tablespace allocation important here as it is single growing file anyway but how innodb allocates data intermally - which is done in 1M extents after first few pages.
I’m not sure what is default grow increment for innodb_file_per_table tablespace - this one is important as many files grow at the same time.
Comment :: March 23, 2008 @ 2:03 pm
How was the partition created and which flags were used for mounting it?
Things like dir_index etc but also things like noatime.
Could a new test be performed on the same box using for example noatime on the mount and see how it (if any) changes?
Comment :: March 24, 2008 @ 1:55 am
Apachez,
Absolutely. I specially published the benchmark script so everyone could easily repeat the run with options they like
There are a lot of variables you can play with
Comment :: March 24, 2008 @ 12:37 pm
Hi,
you should retroy this benchmark with XFS (noatime) and ReiserFS (noatime, notail).
My findings when I tried it with our workload was that XFS gets really slow as soon as you have a very many files, and ReiserFS works great for such a workload.
Well I didn’t check MySQL Performance at all, just creating folders with empty files in it to see how much the filesystem affected the performance with a lot of folders and a fixed file count in them.
It would be nice to see if the difference is seen as clearly with actual data in the files
Comment :: March 28, 2008 @ 4:59 am
[...] on the MySQL Performance Blog, Peter Zaitsev offers MySQL file system fragmentation benchmarks. Peter ran tests on several engines, and the comments shed a little more light on the [...]
Pingback :: March 28, 2008 @ 10:03 am
Thank Paul,
I may run it on XFS when I have a chance. ReiserFS future is kind of uncertain now and as it is dropped as default filesystem by SuSE and not supported by RedHat I do not see customers eager to use it in production.
Also note just many files and support of many growing files is different things. ReiserFS indeed works well with small files I remember creating 10.000.000 of 100 byte files in the directory and it still was working fine.
Comment :: March 28, 2008 @ 2:46 pm
We use it in production, as XFS slowed up on us and mysql became just slow because we had a lot of users on it. The speed difference was like the difference in O(e^n) vs O(n), but as we have a quite uncommon workload it is somewhat our own problem. We have a lot of Databases which are quite tiny.
You might still be interessted in testing it, as my experience shows that a query on an idle server with xfs and 100K Databases takes way longer than a query on the same server with reiserfs.
Comment :: March 29, 2008 @ 1:54 am