Single Innodb Datafile vs Multiple datafiles

Over the past couple of months I have seen a few different clients with innodb setup to have multiple datafiles opposed to using file per table or a single datafile. While I generally do not think twice about a handful of datafiles, I started seeing clients with 50-100 or more 2-4GB datafiles on a single RAID set ( or in some cases a single disk ), and this started to raise the little alarms in my head. Asking these various clients the key question, why? The answers were surprisingly similar. Things like: “we always did it this way” , “their used to be a 2GB file limit”, “we did this in oracle”, etc. Their was not a ton of compelling answers ( like manageability and performance ).

Looking at these systems it seemed like they are really causing “self-induced” fragmentation. They all have large tables, and are doing scans over large amounts of the data… for instance a 40GB table in a database with 2GB datafiles. In the best case this table is spread across 20 files on disk. This is all theory mind you, but it seems like common sense. Additionally there is some management overhead associated with maintaining multiple datafiles, not 100% sure how to quantify this yet.

So I put together a small test. I created 4 tables with random data. When loading the data I staggered the loads of the tables ( insert into tbl1, then tbl2, then tbl3, etc ). I loaded the data into a single datafile, then loaded the same data into a database with 40 datafiles. I used a small dataset ( 1.5GB ) with a proportionately small innodb buffer pool of 128M.  Tests were done in 5.1.22.  To run the tests I created 8 queries that were similar to what I have seen running at these clients and replayed them over and over.

The queries for those who care:

1 select * from fragtest.test_frag1 where id = ’30718′;
2 select * from fragtest.test_frag1 where val1 < 1000 limit 1,1;
3 select * from fragtest.test_frag1 where id = 90718;
4 select count(*), sum(val1), sum(val2), avg(val3) from fragtest.test_frag1 where val4 < 10000;
5 select sum(val4+val5) from fragtest.test_frag2;
6 select * from fragtest.test_frag3 where val1=5718;
7 select * from fragtest.test_frag4 where val1> 5700 and val2 < 10000 and val3 =14242;
8 select count(*), sum(val1), sum(val2), avg(val3) from fragtest.test_frag1 where state in (94,84,35,36,11,12,99,50) group by state ;

Here is the table def for the tables (1-4 are the same def):

CREATE TABLE `test_frag1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) DEFAULT NULL,
`address` varchar(1000) DEFAULT NULL,
`state` char(2) DEFAULT NULL,
`val1` int(11) DEFAULT NULL,
`val2` int(11) DEFAULT NULL,
`val3` int(11) DEFAULT NULL,
`val4` int(11) DEFAULT NULL,
`val5` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_idx` (`name`),
KEY `val_idx` (`val1`,`val2`)
) ENGINE=InnoDB AUTO_INCREMENT=1039773 DEFAULT CHARSET=latin1

For the first round of tests I run each query 20 times. In between each run I restarted the database to ensure that the innodb buffer pool would be free and clear of any pages. Realizing that the initial overhead of opening 40 files may drag down these numbers I also ran the queries without restarting for comparisons sake, those numbers later….

Here are the results:

1 Innodb File 40 Innodb files &nbsp
1 0. 481 0.537 +11.5%
2 0. 531 0.567 +6.5%
3 0.486 0.527 +8.3%
4 7.44 8.19 +10.0%
5 8.29 9.46 +14.1%
6 4.09 4.02 -1.8%
7 8.58 9.53 +11.0%
8 7.31 8.37 +14.4%

Now these numbers were pretty interesting. In these tests 7 out of 8 tests came back faster with a single datafile. The averages for the 20 tests were repeatable within 1-2% every time I rerun the numbers. A 10-15% difference could result in lots of extra cpu cycles and less disk contention.

I wanted to rerun the same tests without restarting the database. These tests should be faster as some data will remain in the buffer pool. Additionally I decided to add a few additional queries with joins just to see what would happen. I ran these tests 55 times, I dropped the top 4 times, and the bottom 4 times from the result set to ensure that the outliers did not really skew the data.

Here are the new query’s:

1 select * from fragtest.test_frag1 where id = ’30718′;
2 select * from fragtest.test_frag1 where val1 < 1000 limit 1,1;
3 select * from fragtest.test_frag1 where id = 90718;
4 select count(*), sum(val1), sum(val2), avg(val3) from fragtest.test_frag1 where val4 < 10000;
5 select sum(val4+val5) from fragtest.test_frag2;
6 select * from fragtest.test_frag3 where val1=5718;
7 select * from fragtest.test_frag4 where val1> 5700 and val2 < 10000 and val3 =14242;
8 select count(*), sum(val1), sum(val2), avg(val3) from fragtest.test_frag1 where state in (94,84,35,36,11,12,99,50) group by state ;
9 select * from fragtest.test_frag3 a, fragtest.test_frag1 b where a.val1 = b.id and a.id = 1111;
10 select * from fragtest.test_frag2 a, fragtest.test_frag4 b where a.val1 = b.id and b.id = 9690;
11 select sum(a.val2) from fragtest.test_frag2 a, fragtest.test_frag4 b where a.val1 = b.id and b.id = 999;

Here are the results:

Query ID 1 Innodb File 40 Innodb files

DIFF x

1

0.0103

0.0159

53.53%

2

0.0567

0.0478

-15.72%

3

0.0205

0.0226

10.16%

4

7.0861

7.6782

8.36%

5

7.8960

8.4054

6.45%

6

3.7241

3.7422

0.49%

7

8.3528

9.2764

11.06%

8

7.0573

7.7802

10.24%

9

0.0468

0.0505

8.05%

10

1.3608

1.3966

2.64%

11

0.0211

0.0262

24.14%

What makes me scratch my head here is that 2 is 15% slower with a single datafile… This is a drop from 6.5% faster after a restart…. Another oddity is that #1 is faster then #3 but thats a subject for a different post:) But all in all 5 tests out of 11 showed 10% or more improvement from a single datafile, while 10 out of 11 showed some increase.

Interesting stuff.  Bottom line is this seems to point to performance issues with a larger number of datafiles.  I am going to rerun these test on another server over the weekend to see how these flush out.

This entry was posted in Common Mistakes, mysql, performance. Bookmark the permalink.

3 Responses to Single Innodb Datafile vs Multiple datafiles

  1. m00dawg says:

    One big per for InnoDB file per table is that you can shrink the datafiles. You cannot do that with a global table (without dumping out, blowing away the table-space, and re-importing). Now for people only using InnoDB on a dedicated DB server, this is not a big deal. I see quite a few people using a mix and match of InnoDB and MyISAM, so it is occasionally necessary to clean up space consumed by InnoDB. The new InnoDB plug-in also seems to favor the file per table solution.

    I am basically indifferent as I see benefits of both, but I think having a global InnoDB table-space is generally easier to manage. I do like the way Falcon handles it with specific table-spaces you can define (at least it could do that last time I checked :)

    -Tim

  2. tomhotch says:

    Note that for query 2, the query times are very short ~ 50-60ms. Other effects probably dominate and may skew the results. Also, performance differences for queries that run this fast probably don’t matter much. If you look at queries that take >= 1 sec, the results consistently favor a single file.

    -Tom

  3. GBA says:

    I’d be interested to see results using file_per_table – do you still have this test rig setup?

    I use this mostly for manageability, but i would expect to perform better than both the examples – especially in real world where the tables aren’t populated in they’re entirity sequentially, but slowly built over time.

    Actually – that could be another interesting parameter in the above; mixing up the inserts so the data isn’t sequential on disk.