Re: Some performance numbers, with thoughts

Brian,

Any idea what your bottleneck is? You can find out at a crude level by
attaching an strace to the running backend, assuming it¹s running long
enough to grab it, then look at what the system call breakdown is.
Basically, run one of your long insert streams, do a ³top² to find which
process id the backend is using (the <pid>), then run this:

strace -p <pid> -c

And CTRL-C after a few seconds to see a breakdown of system calls.

I think what you'll see is that for the small number of inserts per TXN,
you'll be bottlenecked on fsync() calls, or fdatasync() if you defaulted it.
Things might speed up a whole lot there depending on your choice of one or
the other.

- Luke

On 6/19/06 5:09 PM, "Brian Hurt" <bhurt(at)janestcapital(dot)com> wrote:

>
>
> For long involved reasons I'm hanging out late at work today, and rather
> than doing real, productive work, I thought I'd run some benchmarks
> against our development PostgreSQL database server. My conclusions are
> at the end.
>
> The purpose of the benchmarking was to find out how fast Postgres was,
> or to compare Postgres to other databases, but to instead answer the
> question: when does it become worthwhile to switch over to using COPYs
> instead of INSERTS, and by how much? This benchmark should in no way be
> used to gauge absolute performance of PostgreSQL.
>
> The machine in question: a new HP-145 rack mount server, with a
> single-socket dual-core 1.8GHz Opteron 275, 1M of cache per core, with
> 4G of memory, running Redhat Linux (forget which version). Database was
> on the local single SATA hard disk- no raid. From the numbers, I'm
> assuming the disk honors fsync. Some tuning of the database was done,
> specifically shared_buffers was upped to 2500 and temp_buffers to 1500
> (mental note to self: must increase these signifigantly more. Forgot
> they were so low). fsync is definately on. Test program was written in
> Ocaml, compiled to native code, using the Ocaml Postgresql connection
> library (Ocaml bindings of the libpgsql library). The test was single
> threaded- only one insert going on at a time, run over the local gigabit
> ethernet network from a remote machine.
>
> The table design was very simple:
> CREATE TABLE copytest (
> id SERIAL PRIMARY KEY NOT NULL,
> name VARCHAR(64),
> thread INT,
> block INT,
> num INT);
>
> The id column was not specified either in the inserts or in the copies,
> instead it just came from the sequence. Other than the id, there are no
> indexes on the table. Numbers are approximate.
>
> Results:
>
> Inserts, 1 per transaction* 83 inserts/second
> Inserts, 5 per transaction 419 inserts/second
> Inserts, 10 per transaction 843 inserts/second
> Inserts, 50 per transaction ~3,100 inserts/second
> Inserts, 100 per transaction ~4,200 inserts/second
> Inserts, 1,000 per transaction ~5,400 inserts/second
> Copy, 5 element blocks ~405 inserts/second
> Copy, 10 element blocks ~700 inserts/second
> Copy, 50 element blocks ~3,400 inserts/second
> Copy, 100 element blocks ~6,000 inserts/second
> Copy, 1,000 element blocks ~20,000 inserts/second
> Copy, 10,000 element blocks ~27,500 inserts/second
> Copy, 100,000 element blocks ~27,600 inserts/second
>
> * The singleton inserts were not done in an explicit begin/end block,
> but were instead "unadorned" inserts.
>
> Some conclusions:
>
> 1) Transaction time is a huge hit on the small block sizes. Going from
> 1 insert per transaction to 10 inserts per transaction gives a 10x speed
> up. Once the block size gets large enough (10's to 100's of elements
> per block) the cost of a transaction becomes less of a problem.
>
> 2) Both insert methods hit fairly hard walls of diminishing returns were
> larger block sizes gave little performance advantage, tending to no
> performance advantage.
>
> 3) For small enough block sizes, inserts are actually faster than
> copies- but not by much. There is a broad plateau, spanning at least
> the 5 through 100 elements per block (more than an order of magnitude),
> where the performance of the two are roughly identical. For the general
> case, I'd be inclined to switch to copies sooner (at 5 or so elements
> per block) rather than later.
>
> 4) At the high end, copies vastly outperformed inserts. At 1,000
> elements per block, the copy was almost 4x faster than inserts. This
> widened to ~5x before copy started topping out.
>
> 5) The performance of Postgres, at least on inserts, depends critically
> on how you program it. One the same hardware, performance for me varied
> over a factor of over 300-fold, 2.5 orders of magnitude. Programs which
> are unaware of transactions and are designed to be highly portable are
> likely to hit the abysmal side of performance, where the transaction
> overhead kills performance. I'm not sure there is a fix for this (let
> alone an easy fix)- simply dropping transactions is obviously not it.
> Programs that are transaction aware and willing to use
> PostgreSQL-specific features can get surprisingly excellent
> performance. Simply being transaction-aware and doing multiple inserts
> per transaction greatly increases performance, giving an easy order of
> magnitude increase (wrapping 10 inserts in a transaction gives a 10x
> performance boost).
>
> Brian
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>