Re: postgresql 8.3 tps rate

[snip]

I'm actually doing some very similar testing and getting very similar
results. My disk is a single Seagate Barracuda 7200 RPM SATA (160 GB).
The OS is openSUSE 11.1 (2.6.27 kernel) with the "stock" PostgreSQL
8.3.5 RPM. I started out running pgbench on the same machine but just
moved the driver to another one trying to get better results. The other
driver is quite small -- 512 MB 1.6 GHz -- so I might need to shut down
the desktop and X on it.

But the real mystery is this: I have two XFS partitions. Let's call them
sda5 and sda6. The PostgreSQL install put the database in
/var/lib/pgsql, which is on sda5. But I created a tablespace on sda6
specifically for the database that pgbench is using, and put that
database there. At least that's what pgadmin3 is telling me I did. But
when I run pgbench, I see intense I/O on *both* partitions sometimes,
and other times I see it *only* on sda5.

I can understand the "both" times -- I didn't move any "system-level"
things like the write-ahead logs. But what I can't understand is the
periods when it isn't using sda6, where the tablespace is.

Anyhow, here's a short segment of the results I'm getting.

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 2
number of transactions per client: 100
number of transactions actually processed: 200/200
tps = 37.360964 (including connections establishing)
tps = 37.430501 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 4
number of transactions per client: 100
number of transactions actually processed: 400/400
tps = 51.768918 (including connections establishing)
tps = 51.985556 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 6
number of transactions per client: 100
number of transactions actually processed: 600/600
tps = 51.462103 (including connections establishing)
tps = 51.734119 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 8
number of transactions per client: 100
number of transactions actually processed: 800/800
tps = 44.316328 (including connections establishing)
tps = 44.473483 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 44.750672 (including connections establishing)
tps = 44.910703 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 12
number of transactions per client: 100
number of transactions actually processed: 1200/1200
tps = 45.048743 (including connections establishing)
tps = 45.205084 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 14
number of transactions per client: 100
number of transactions actually processed: 1400/1400
tps = 26.849217 (including connections establishing)
tps = 26.916643 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 16
number of transactions per client: 100
number of transactions actually processed: 1600/1600
tps = 11.187072 (including connections establishing)
tps = 11.198109 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 18
number of transactions per client: 100
number of transactions actually processed: 1800/1800
tps = 38.183978 (including connections establishing)
tps = 38.301026 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 20
number of transactions per client: 100
number of transactions actually processed: 2000/2000
tps = 35.012091 (including connections establishing)
tps = 35.109165 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 22
number of transactions per client: 100
number of transactions actually processed: 2200/2200
tps = 28.286106 (including connections establishing)
tps = 28.350341 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 100
number of transactions actually processed: 2400/2400
tps = 29.285593 (including connections establishing)
tps = 29.358284 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 26
number of transactions per client: 100
number of transactions actually processed: 2600/2600
tps = 29.237558 (including connections establishing)
tps = 29.308422 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 28
number of transactions per client: 100
number of transactions actually processed: 2800/2800
tps = 35.251509 (including connections establishing)
tps = 35.351999 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 30
number of transactions per client: 100
number of transactions actually processed: 3000/3000
tps = 29.790523 (including connections establishing)
tps = 29.863336 (excluding connections establishing)


I'm going to move the pgbench database back to the main sda5 partition
-- given the "both" periods, the seeks must be killing me. This is
actually not a benchmark, but a way of generating sample "blktrace"
data, so when it's finally working, I'll have some block-layer traces to
tell me exactly what's going on. :)

--
M. Edward (Ed) Borasky

I've never met a happy clam. In fact, most of them were pretty steamed.