Re: Fast Inserts and Hardware Questions

> Right now I have a prototype running on a dual PIII 533 MHz with a scsi
> raid and I am getting 2 inserts / second maximum. (To be fair there is
> some overhead I did not mention on each insert but not a lot).

Two per second!? I can _type_ faster than that! :)

I just ran a simple benchmark on two different systems. With the following
small table and two-column index, I timed the insertion of 10,000 rows.

Machine #1: Dual PIII/733, 512Mb RAM, hardware RAID with 10K RPM Ultra3 SCSI
drives. Running PostgreSQL 7.0.3 and RedHat 7.0. Additional CPU load was
minimal, additional disk load was light. Elapsed time was 56 seconds, for an
average rate of 182 insertions per second. (Ok, ok, keep the sex jokes to
youself, please. :)

Machine #2: Single Pentium 200 (non-MMX), 64Mb RAM, 5400 RPM IDE drive. Running
PostgreSQL 7.1beta5 and RedHat 6.2. Additional diskload was minimal, additional
CPU load included continuous decoding of 192K MP3s, which is not such a trivial
task for the ole' 200. Time was 44 seconds, for an average rate of 226 per
second!

Wow, does WAL make so much of a difference as to make my two-generations-old,
built-from-spare-parts scrap box faster than my latest and greatest production
server? Yikes!

CREATE TABLE "thing" (
        "date" date,
        "id" integer,
        "addr" text,
        "contract" integer,
        "type" integer,
        "count" integer
);

CREATE INDEX "thing_index" on "thing" using btree ( "date" "date_ops", "id" "int4_ops" );

--
Alex Howansky
Wankwood Associates
http://www.wankwood.com/