File system performance and pg_xlog - Mailing list pgsql-hackers

A small debate started with bad performance on ReiserFS. I pondered the likely
advantages to raw device access. It also occured to me that the FAT file system
is about as close to a managed raw device as one could get. So I did some
tests:
The hardware:

A PII system running Linux 7.0, with 2.2.16-2.
256M RAM
IDE home hard disk.
Adaptec 2740 with two SCSI drives
A 9G Seagate ST19171W as /dev/sda1 mounted as /sda1
A 4G Seagate ST15150W as /dev/sdb1 mounted as /sdb1
/sda1 has a ext2 file system, and is used as "base" with a symlink.
/sdb1 is either an ext2 or FAT file system used as "pg_xlog" with a symlink.


In a clean Postgres environment, I initialized pgbench as:
./pgbench -i -s 10 -d pgbench

I used this script to produce the results:

psql -U mohawk pgbench -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 1"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 2"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 3"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 4"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 5"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 6"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 7"
psql -U mohawk pgbench  -c "checkpoint; "
su mohawk -c "./pgbench -d pgbench -t 32 -c 8"

(My postgres user is "mohawk")

I had to modify xlog.c to use "rename" instead of link. And I had to explicitly
set ownership of the FAT file system to the postgres user during mount.

I ran the script twice as:

./test.sh > ext2.log

(Then rebuilt a fresh database and formatted sdb1 as fat)
./test.sh > fat.log

Here is a diff of the two runs:

--- ext2.log    Sat May  5 12:58:07 2001
+++ fat.log    Sat May  5 12:58:07 2001
@@ -5,8 +5,8 @@number of clients: 1number of transactions per client: 32number of transactions actually processed:
32/32
-tps = 18.697006(including connections establishing)
-tps = 19.193225(excluding connections establishing)
+tps = 37.439512(including connections establishing)
+tps = 39.710461(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 2 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -14,8 +14,8 @@number of clients: 2number of transactions per client: 32number of transactions actually processed:
64/64
-tps = 32.444226(including connections establishing)
-tps = 33.499452(excluding connections establishing)
+tps = 44.782177(including connections establishing)
+tps = 46.799328(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 3 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -23,8 +23,8 @@number of clients: 3number of transactions per client: 32number of transactions actually processed:
96/96
-tps = 43.042861(including connections establishing)
-tps = 44.816086(excluding connections establishing)
+tps = 55.416117(including connections establishing)
+tps = 58.057013(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 4 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -32,8 +32,8 @@number of clients: 4number of transactions per client: 32number of transactions actually processed:
128/128
-tps = 46.033959(including connections establishing)
-tps = 47.681683(excluding connections establishing)
+tps = 61.752368(including connections establishing)
+tps = 64.796970(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 5 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -41,8 +41,8 @@number of clients: 5number of transactions per client: 32number of transactions actually processed:
160/160
-tps = 49.980258(including connections establishing)
-tps = 51.874653(excluding connections establishing)
+tps = 63.124090(including connections establishing)
+tps = 67.225563(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 6 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -50,8 +50,8 @@number of clients: 6number of transactions per client: 32number of transactions actually processed:
192/192
-tps = 51.800192(including connections establishing)
-tps = 53.752739(excluding connections establishing)
+tps = 65.452545(including connections establishing)
+tps = 68.741933(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 7 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -59,8 +59,8 @@number of clients: 7number of transactions per client: 32number of transactions actually processed:
224/224
-tps = 52.652660(including connections establishing)
-tps = 54.616802(excluding connections establishing)
+tps = 66.525419(including connections establishing)
+tps = 69.727409(excluding connections establishing)CHECKPOINTpghost: (null) pgport: (null) nclients: 8 nxacts: 32
dbName:pgbenchtransaction type: TPC-B (sort of)
 
@@ -68,5 +68,5 @@number of clients: 8number of transactions per client: 32number of transactions actually processed:
256/256
-tps = 55.440884(including connections establishing)
-tps = 57.525931(excluding connections establishing)
+tps = 67.331052(including connections establishing)
+tps = 70.575482(excluding connections establishing)