PostgreSQL on different fs (ext3, ext4, xfs)

The article is a note that grew out of a question asked in Q & A. In short, it was like this ... A test version of PostgreSQL was proposed on a particular file system and the question was whether this was a normal approach and whether you could at least trust the results of this test. During the discussion of the issue, there were no alternatives, and I decided to test as originally conceived.


')
Actually, what was and how it all went:
There is a mediocre server of the following configuration:

• HP Proliant DL160 G5
• 1x Intel Xeon CPU E5405 @ 2.00GHz (L1 128KiB, L2 12MiB)
• 4x FB-DIMM DDR2 Synchronous 667 MHz (1.5 ns) Total 8GB RAM
• RAID LSI SAS1064E 4-Port PCI-E 3Gb / s
• 4x SAS HP DF146BABUE 146GB 3.5 "LFF 3G 15K RPM
• Gentoo Linux kernel-3.7.1, glibc-2.15-r3, gcc-4.5.4
• PostgreSQL Server 9.2.1

Of the four disks, two RAID1s were assembled, on one of them the operating system is located, the second volume is a testing ground. This disk is used as LVM PV and a 55GB LVM volume is allocated for the test. The test passes as follows:

• format the volume and mount to the directory;
• start the initialization of the postgresql cluster;
• copy the previously prepared postgresql.conf configuration file to the cluster;
• we start postgresql and we create the pgbench test base;
• we start initialization of tables with the help pgbench;
• cyclically in “SELECT's only” mode we run pgbench with a different number of client connections, save the results (each test lasts 2 hours)
• cyclically in the " TPC-B " mode we run pgbench with a different number of client connections, we save the results (each test lasts 2 hours).

The number of clients in each test varies from 8 to 96 (8,16,32,64,96)
Total for one file system we get 10 results (5 SELECT's only, 5 TPC-B)

Some details that matter:

• RAID works without WriteCache;
• all processes / services were stopped / turned off with the exception of PostgreSQL, the process table is almost empty (with the exception of nuclear processes);
• WAL journals live on the same section as the data;
• the test base was initialized in such a way as to occupy 80% of the total disk space;
• each time pgbench was launched, caches were reset.

Postgresql.conf settings
max_connections = 100
shared_buffers = 1500MB
work_mem = 16MB
maintenance_work_mem = 128MB
effective_io_concurrency = 1
checkpoint_segments = 32
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9

Results for "SELECT's only" and TPC-B





Conclusions: in general, as expected, there are no breakthroughs and revolutionary gaps in the results; all file systems have approximately the same level of performance. If you look closely, you can note a few points:
- ext4 shows slightly better performance than ext3 (on average by 1-3%);
- ext4 shows the highest performance in the TPC-B test (from 1% to 8%);
- xfs shows the highest performance in SELECT's only (from 1% to 3%);

In general, you can say "but you can put any fs, the difference is so small that it can be neglected." But remember that (here you can insert any pretentious phrase about the importance of small things). In general, do not neglect the little things))

That's all. You can throw poop, criticize, express thoughts, carry out your ideas ...

PS Andrey Fefelov, hello !!! )))