Not strictly related to compression, but I've noticed something really
strange...
pg 8.4 (vanilla) is doing it, and my compressed version is doing it too.
tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives,
but it does it too if I put the tablespace and data on the same volume.
The traditional test table :
BEGIN; CREATE TABLE dwb (
bid SERIAL,
aid INTEGER NOT NULL,
ts TIMESTAMP NOT NULL,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
i3 INTEGER NOT NULL,
i4 INTEGER NOT NULL
) WITHOUT OIDS;
The traditional test data :
INSERT INTO dwb (ts,aid,i1,i2,i3,i4)
SELECT now() + '1 sec'::INTERVAL, (1+random()*99998), random()*10000000,
n+random()*10000, n+random()*1000, n FROM generate_series( 1, 60000000 ) AS n;
vmstat output :
it starts out relatively fast :
si so bi bo in cs us sy id wa 0 0 0 43680 2796 19162 42 18 37 3 0 0 0 45515 3165
2065244 17 35 4 0 0 0 43130 3046 21991 43 17 38 2
then here it starts to slow down : check "bo" output
0 0 181 24439 577 3541 31 6 40 23 0 0 176 17258 292 1324 31 4 43 22 0 0 0 18626 162
69335 3 49 12 0 0 1 21554 235 1362 31 5 50 14 0 0 0 19177 324 2053 35 4 50 12 0 0 0
19208 206 1155 36 4 48 12 0 0 1 20740 215 1117 33 4 50 13 0 0 0 20154 258 1100 32 4 50 14 0
0 0 20355 316 2056 34 5 49 12
... and it stays like this until the end of the INSERT...
It's not writing any xlog since the table was created after the BEGIN...
I'm trying to benchmark insert speed, but no luck. This volume does about
100 MB/s sustained write speed, so ?......