Filesystems comparison using sql-bench and mySQL (2GB of memory)

Because some loops in test scripts were too small to have accurate results, I changed some of them:

Script $opt_loop_count
old value
$opt_loop_count
new value
test-ATIS 100 1000
test-big-tables 1000 10000
test-connect 10000 100000
test-create 10000 30000
test-wisconsin 10 100

Raw results can be found here.

Summary of the results

alter-table

Best filesystems are ext3 with delalloc and ext2 of course.

ATIS

Best filesystems are reiserfs and ext2.
Worst filesystem is xfs

big-tables

Best filesystems are ext3 with delalloc and ext2 of course.

connect

Best filesystems are ext3 with data=ordered or data=writeback,delalloc and ext2 of course.

create

Best filesystem is reiserfs.
Worst filesystem is jfs.

insert

Best filesystem is ext3 data=ordered, but all filesystems are very good except ext3 with data=ordered
(funny result: ext3 with data=ordered and delalloc is more efficient BUT delalloc is not supported with data=ordered... see 64MB results).

select

Best filesystem is ext3 data=ordered. Worst is xfs.

wisconsin

Best filesystems are ext2 and jfs.

Results

alter-table

alter-table average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 54.60 54.00 54.00 54.00 55.00 55.00 55.00 55.00 55.00 55.00 54.00
ext3_data=ordered 70.70 69.00 72.00 70.00 70.00 71.00 71.00 71.00 71.00 71.00 71.00
ext3_data=ordered,extents 69.80 68.00 68.00 71.00 70.00 70.00 71.00 70.00 70.00 71.00 69.00
ext3_data=ordered,mballoc,delalloc,extents 51.90 51.00 52.00 52.00 52.00 52.00 52.00 53.00 52.00 52.00 51.00
ext3_data=ordered,mballoc,extents 74.60 74.00 74.00 74.00 74.00 76.00 74.00 76.00 75.00 75.00 74.00
ext3_data=writeback 66.80 66.00 66.00 67.00 67.00 67.00 67.00 67.00 66.00 68.00 67.00
ext3_data=writeback,extents 65.90 65.00 66.00 65.00 66.00 66.00 66.00 66.00 66.00 67.00 66.00
ext3_data=writeback,mballoc,delalloc,extents 51.80 51.00 51.00 52.00 51.00 53.00 52.00 51.00 52.00 53.00 52.00
ext3_data=writeback,mballoc,extents 69.70 69.00 70.00 69.00 69.00 70.00 70.00 69.00 71.00 71.00 69.00
jfs_defaults 73.60 74.00 71.00 74.00 74.00 74.00 74.00 74.00 76.00 72.00 73.00
reiserfs_defaults 69.90 68.00 70.00 70.00 70.00 71.00 71.00 69.00 71.00 70.00 69.00
xfs_defaults 71.20 72.00 72.00 58.00 73.00 73.00 73.00 72.00 73.00 73.00 73.00
alter-table

ATIS

ATIS average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 143.10 142.00 142.00 142.00 143.00 143.00 146.00 143.00 143.00 144.00 143.00
ext3_data=ordered 143.60 141.00 141.00 143.00 145.00 145.00 146.00 145.00 143.00 144.00 143.00
ext3_data=ordered,extents 143.90 143.00 143.00 144.00 144.00 145.00 144.00 145.00 144.00 145.00 142.00
ext3_data=ordered,mballoc,delalloc,extents 143.80 143.00 142.00 144.00 144.00 145.00 145.00 143.00 144.00 144.00 144.00
ext3_data=ordered,mballoc,extents 144.80 143.00 143.00 146.00 145.00 148.00 146.00 142.00 147.00 145.00 143.00
ext3_data=writeback 143.80 143.00 142.00 144.00 144.00 145.00 144.00 145.00 144.00 144.00 143.00
ext3_data=writeback,extents 143.80 142.00 142.00 144.00 144.00 146.00 146.00 144.00 144.00 144.00 142.00
ext3_data=writeback,mballoc,delalloc,extents 144.30 142.00 143.00 145.00 145.00 145.00 145.00 145.00 145.00 144.00 144.00
ext3_data=writeback,mballoc,extents 143.50 142.00 141.00 144.00 145.00 144.00 145.00 145.00 145.00 143.00 141.00
jfs_defaults 143.30 142.00 142.00 144.00 144.00 146.00 143.00 143.00 143.00 145.00 141.00
reiserfs_defaults 143.00 141.00 141.00 144.00 144.00 143.00 143.00 144.00 143.00 146.00 141.00
xfs_defaults 147.20 146.00 145.00 147.00 149.00 147.00 149.00 147.00 148.00 148.00 146.00
ATIS

big-tables

big-tables average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 102.00 102.00 101.00 101.00 103.00 102.00 102.00 102.00 102.00 102.00 103.00
ext3_data=ordered 103.20 101.00 102.00 101.00 105.00 105.00 105.00 104.00 103.00 103.00 103.00
ext3_data=ordered,extents 104.00 103.00 102.00 104.00 105.00 106.00 104.00 104.00 106.00 104.00 102.00
ext3_data=ordered,mballoc,delalloc,extents 102.20 100.00 100.00 103.00 102.00 102.00 102.00 103.00 103.00 105.00 102.00
ext3_data=ordered,mballoc,extents 104.90 102.00 104.00 103.00 104.00 117.00 104.00 103.00 106.00 103.00 103.00
ext3_data=writeback 102.80 100.00 103.00 103.00 102.00 103.00 102.00 107.00 103.00 103.00 102.00
ext3_data=writeback,extents 102.50 101.00 100.00 104.00 104.00 104.00 102.00 103.00 102.00 103.00 102.00
ext3_data=writeback,mballoc,delalloc,extents 102.10 102.00 101.00 102.00 103.00 103.00 101.00 102.00 103.00 103.00 101.00
ext3_data=writeback,mballoc,extents 102.80 102.00 101.00 104.00 102.00 102.00 103.00 104.00 104.00 103.00 103.00
jfs_defaults 102.70 101.00 100.00 104.00 103.00 102.00 104.00 103.00 105.00 102.00 103.00
reiserfs_defaults 102.90 101.00 101.00 103.00 103.00 103.00 103.00 102.00 104.00 106.00 103.00
xfs_defaults 102.40 100.00 100.00 103.00 105.00 103.00 102.00 103.00 103.00 102.00 103.00
big-tables

connect

connect average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 289.00 284.00 279.00 283.00 289.00 293.00 292.00 291.00 291.00 292.00 296.00
ext3_data=ordered 288.20 280.00 284.00 282.00 293.00 291.00 292.00 289.00 291.00 288.00 292.00
ext3_data=ordered,extents 290.50 281.00 294.00 288.00 290.00 289.00 294.00 289.00 291.00 292.00 297.00
ext3_data=ordered,mballoc,delalloc,extents 289.80 279.00 284.00 288.00 291.00 288.00 289.00 293.00 287.00 296.00 303.00
ext3_data=ordered,mballoc,extents 294.20 281.00 285.00 294.00 293.00 325.00 295.00 291.00 291.00 292.00 295.00
ext3_data=writeback 289.40 283.00 286.00 292.00 291.00 290.00 289.00 288.00 293.00 288.00 294.00
ext3_data=writeback,extents 289.20 281.00 281.00 291.00 290.00 290.00 291.00 296.00 288.00 290.00 294.00
ext3_data=writeback,mballoc,delalloc,extents 288.40 285.00 283.00 290.00 293.00 288.00 292.00 287.00 285.00 286.00 295.00
ext3_data=writeback,mballoc,extents 290.60 281.00 286.00 296.00 291.00 289.00 288.00 292.00 297.00 294.00 292.00
jfs_defaults 290.80 282.00 286.00 293.00 297.00 293.00 287.00 291.00 290.00 292.00 297.00
reiserfs_defaults 289.80 281.00 280.00 291.00 291.00 290.00 291.00 293.00 295.00 295.00 291.00
xfs_defaults 293.60 290.00 287.00 298.00 293.00 293.00 296.00 295.00 296.00 292.00 296.00
connect

create

create average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 366.60 372.00 378.00 378.00 380.00 375.00 368.00 355.00 346.00 366.00 348.00
ext3_data=ordered 507.20 500.00 505.00 503.00 522.00 506.00 510.00 507.00 506.00 507.00 506.00
ext3_data=ordered,extents 508.40 497.00 493.00 506.00 504.00 518.00 496.00 549.00 508.00 509.00 504.00
ext3_data=ordered,mballoc,delalloc,extents 481.60 478.00 478.00 481.00 482.00 483.00 482.00 483.00 484.00 484.00 481.00
ext3_data=ordered,mballoc,extents 508.00 504.00 505.00 508.00 509.00 509.00 510.00 509.00 509.00 509.00 508.00
ext3_data=writeback 509.90 502.00 503.00 507.00 510.00 522.00 508.00 509.00 507.00 508.00 523.00
ext3_data=writeback,extents 500.10 491.00 506.00 495.00 497.00 497.00 509.00 497.00 512.00 502.00 495.00
ext3_data=writeback,mballoc,delalloc,extents 482.20 479.00 486.00 483.00 483.00 483.00 481.00 483.00 482.00 481.00 481.00
ext3_data=writeback,mballoc,extents 510.40 511.00 508.00 510.00 512.00 508.00 510.00 511.00 511.00 512.00 511.00
jfs_defaults 842.90 839.00 841.00 845.00 845.00 845.00 845.00 843.00 842.00 842.00 842.00
reiserfs_defaults 119.30 119.00 117.00 120.00 120.00 120.00 120.00 116.00 121.00 121.00 119.00
xfs_defaults 235.00 234.00 245.00 234.00 237.00 247.00 223.00 234.00 233.00 235.00 228.00
create

insert

insert average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 782.90 772.00 773.00 782.00 779.00 789.00 794.00 801.00 769.00 791.00 779.00
ext3_data=ordered 1154.60 1192.00 1191.00 1124.00 1184.00 1140.00 1114.00 1180.00 1090.00 1263.00 1068.00
ext3_data=ordered,extents 1102.00 1017.00 1143.00 1194.00 1065.00 1143.00 1146.00 1102.00 1063.00 1079.00 1068.00
ext3_data=ordered,mballoc,delalloc,extents 788.70 786.00 770.00 795.00 787.00 811.00 785.00 783.00 784.00 783.00 803.00
ext3_data=ordered,mballoc,extents 1080.00 1062.00 985.00 1085.00 1117.00 1067.00 1099.00 1035.00 1082.00 1141.00 1127.00
ext3_data=writeback 801.60 803.00 808.00 824.00 797.00 799.00 797.00 795.00 791.00 788.00 814.00
ext3_data=writeback,extents 796.70 789.00 785.00 810.00 804.00 794.00 799.00 802.00 806.00 786.00 792.00
ext3_data=writeback,mballoc,delalloc,extents 791.40 789.00 791.00 797.00 801.00 813.00 786.00 788.00 791.00 776.00 782.00
ext3_data=writeback,mballoc,extents 801.90 801.00 783.00 798.00 797.00 829.00 812.00 795.00 798.00 811.00 795.00
jfs_defaults 785.00 789.00 789.00 777.00 780.00 788.00 789.00 781.00 793.00 783.00 781.00
reiserfs_defaults 799.40 792.00 789.00 804.00 811.00 814.00 801.00 797.00 812.00 792.00 782.00
xfs_defaults 802.00 812.00 792.00 828.00 804.00 808.00 795.00 796.00 799.00 789.00 797.00
insert

select

select average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 375.10 369.00 369.00 377.00 371.00 373.00 378.00 386.00 376.00 380.00 372.00
ext3_data=ordered 372.90 385.00 373.00 374.00 370.00 371.00 370.00 370.00 374.00 375.00 367.00
ext3_data=ordered,extents 376.70 375.00 393.00 371.00 374.00 374.00 372.00 378.00 378.00 376.00 376.00
ext3_data=ordered,mballoc,delalloc,extents 377.40 383.00 372.00 374.00 371.00 381.00 373.00 375.00 375.00 377.00 393.00
ext3_data=ordered,mballoc,extents 374.50 368.00 375.00 374.00 370.00 375.00 371.00 371.00 372.00 402.00 367.00
ext3_data=writeback 381.00 377.00 396.00 392.00 376.00 371.00 373.00 374.00 381.00 369.00 401.00
ext3_data=writeback,extents 378.10 367.00 372.00 392.00 378.00 382.00 379.00 378.00 391.00 374.00 368.00
ext3_data=writeback,mballoc,delalloc,extents 379.50 381.00 386.00 376.00 377.00 401.00 373.00 381.00 373.00 377.00 370.00
ext3_data=writeback,mballoc,extents 379.70 378.00 369.00 374.00 378.00 405.00 373.00 373.00 373.00 402.00 372.00
jfs_defaults 376.60 377.00 390.00 372.00 381.00 372.00 392.00 368.00 371.00 371.00 372.00
reiserfs_defaults 374.30 372.00 382.00 374.00 370.00 375.00 371.00 370.00 383.00 382.00 364.00
xfs_defaults 385.40 405.00 387.00 395.00 381.00 384.00 381.00 382.00 381.00 383.00 375.00
select

wisconsin

wisconsin average 1 2 3 4 5 6 7 8 9 10
ext2_defaults 20.40 21.00 20.00 20.00 20.00 21.00 21.00 21.00 20.00 20.00 20.00
ext3_data=ordered 21.20 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 22.00 22.00
ext3_data=ordered,extents 21.30 21.00 21.00 21.00 21.00 22.00 21.00 21.00 21.00 22.00 22.00
ext3_data=ordered,mballoc,delalloc,extents 20.90 21.00 21.00 21.00 21.00 21.00 21.00 20.00 21.00 21.00 21.00
ext3_data=ordered,mballoc,extents 21.20 21.00 21.00 21.00 21.00 22.00 21.00 21.00 22.00 21.00 21.00
ext3_data=writeback 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00
ext3_data=writeback,extents 20.60 20.00 20.00 21.00 21.00 21.00 21.00 21.00 20.00 21.00 20.00
ext3_data=writeback,mballoc,delalloc,extents 20.80 20.00 21.00 21.00 21.00 21.00 21.00 22.00 20.00 20.00 21.00
ext3_data=writeback,mballoc,extents 20.80 21.00 20.00 21.00 21.00 21.00 21.00 21.00 20.00 21.00 21.00
jfs_defaults 20.40 20.00 20.00 21.00 20.00 20.00 21.00 20.00 21.00 21.00 20.00
reiserfs_defaults 20.90 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 20.00
xfs_defaults 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00 21.00
wisconsin