Re: Fast Inserts and Hardware Questions - Mailing list pgsql-general

Have you done any benchmarks with a prototype of your application.

Based off of some of the numbers I've been seeing with my testing,
I would not be surprised if a single pIII 1ghz box with a decent disk
(ata 100 or scsi 160) would handle the load you describe and is way
cheaper than some big smp box.  A dual pIII with a decent raid card
would get you even farther.  Certainly these are not as expandable as
other options out there but they are worth a look.

What I would be most concered about is a table that grows by a few million
rows a week, that to me seems like a liability in itself since
maintenance on that table will get pretty slow after a few months.

Alex.


On Wed, 14 Mar 2001, Orion Henry wrote:

> I am specing out a database for my company and I need some advice.  The
> database in mind will consist of one table, with about 300 bytes per
> record.  The table will be getting two or three million inserts a week
> and it would be nice if it could handle a sustained 30 to 50 a second.
> The table will have (ACK!) 4 indexes (if anyone can explain to me how I
> can get away with less please let me know)
>
> The indexes will be
> int8 (primary key)
> int4 (group number)
> timestamp (creation date)
> int4 (customer id)
>
> The customers want to be able to query their data whenever and have it
> be snappy.
>
> So here is my question:
> * Is there an OS that is best suited for postgres. All things being
> equal I would like to run this on Linux.
> * Is there an architecture best suited for this.  Should I shell out the
> extra cash for a 64 bit box over a 32bit one.  Quad Xeon or Quad Alpha?
> Quad Alpha or Quad UltraSparc?
> * Since most of what I am doing is inserts I will assume that the disk
> will be my bottleneck over anything else.  Is this correct?
> * Will the 7.1 WAL save me, when it comes to insert times?
> * I read something about disabling fsync() to speed up inserts.  How
> much will this speed things up?  I would consider it as I COULD rebuild
> lost data from my logs in the event of a system crash and one night a
> year of panic on my part is worth saving $100,000 in drive arrays.
>
> Oh, and if any of you SQL guru's are still reading I'll show you the
> queries I will be running to see if I can really ditch an index.
>
> select * from table where customer_id = ? and primary_key = ?::int8
> select * from table where customer_id = ? and group_number = ?
> select * from table where customer_id = ? and creation > ? and creation
> < ?
>
> Thanks for all your help,
>
>     Orion Henry
>     CTO TrustCommerce
>     orion@trustcommerce.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>