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
Best filesystems are ext3 with delalloc and ext2 of course.
Best filesystems are reiserfs and ext2.
Worst filesystem is xfs
Best filesystems are ext3 with delalloc and ext2 of course.
Best filesystems are ext3 with data=ordered or data=writeback,delalloc and ext2 of course.
Best filesystem is reiserfs.
Worst filesystem is jfs.
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).
Best filesystem is ext3 data=ordered. Worst is xfs.
Best filesystems are ext2 and jfs.
Results
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
