pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RAID or manual split?
3+ messages / 3 participants
[nested] [flat]

* RAID or manual split?
@ 2004-02-17 21:53  Mike Glover <mpg4@duluoz.net>
  0 siblings, 2 replies; 3+ messages in thread

From: Mike Glover @ 2004-02-17 21:53 UTC (permalink / raw)
  To: pgsql-performance


It seems, that if I know the type and frequency of the queries a
database will be seeing, I could split the database by hand over
multiple disks and get better performance that I would with a RAID array
with similar hardware.  Most of the data is volatile
and easily replaceable (and the rest is backed up independently), so
redundancy isn't importand, and I'm willing to do some ongoing
maintenance if I can get a decent speed boost.  Am I misguided, or might
this work? details of my setup are below:

Six large (3-7 Mrow) 'summary' tables, each being updated continuously
by 5-20 processes with about 0.5 transactions/second/process.  

Periodically (currently every two weeks), join queries are
performed between one of the 'summary' tables(same one each time) and
each of the other five.  Each join touches most rows of both tables,
indexes aren't used.  Results are written into a separate group of
'inventory' tables (about 500 Krow each), one for each join.

There are frequent (100-1000/day) queries of both the
inventory and summary tables using the primary key -- always using the
index and returning < 10 rows.

We're currently getting (barely) acceptable performance from a single
15k U160 SCSI disk, but db size and activity are growing quickly. 
I've got more disks and a battery-backed LSI card on order.

-mike


-- 
Mike Glover
GPG Key ID BFD19F2C <mpg4@duluoz.net>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: RAID or manual split?
@ 2004-02-17 22:17  Rod Taylor <rbt@rbt.ca>
  parent: Mike Glover <mpg4@duluoz.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Rod Taylor @ 2004-02-17 22:17 UTC (permalink / raw)
  To: Mike Glover <mpg4@duluoz.net>; +Cc: pgsql-performance

> There are frequent (100-1000/day) queries of both the
> inventory and summary tables using the primary key -- always using the
> index and returning < 10 rows.

For this query frequency I don't think splitting the drives will do much
-- you just need more IO. Look at optimizing the query themselves,
specifically ensuring the useful information is already in memory.

If the 10 rows are recent, you might try using partial indexes with the
last days worth of information instead of an index across the entire
table.

> We're currently getting (barely) acceptable performance from a single
> 15k U160 SCSI disk, but db size and activity are growing quickly. 
> I've got more disks and a battery-backed LSI card on order.

Configure for Raid 10 and you're off.

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: RAID or manual split?
@ 2004-02-17 22:34  matt@ymogen.net
  parent: Mike Glover <mpg4@duluoz.net>
  1 sibling, 0 replies; 3+ messages in thread

From: matt@ymogen.net @ 2004-02-17 22:34 UTC (permalink / raw)
  To: Mike Glover <mpg4@duluoz.net>; +Cc: pgsql-performance

> It seems, that if I know the type and frequency of the queries a
> database will be seeing, I could split the database by hand over
> multiple disks and get better performance that I would with a RAID array
> with similar hardware.

Unlikely, but possible if you had radically different hardware for
different tables.

> Six large (3-7 Mrow) 'summary' tables, each being updated continuously
> by 5-20 processes with about 0.5 transactions/second/process.

Well you should get close to an order of magnitude better performance from
a RAID controller with write-back cache on those queries.

> Periodically (currently every two weeks), join queries are
> performed between one of the 'summary' tables(same one each time) and
> each of the other five.  Each join touches most rows of both tables,
> indexes aren't used.  Results are written into a separate group of
> 'inventory' tables (about 500 Krow each), one for each join.

The more disks the data is spread over the better (the RAID controller
will help here with striping).

> There are frequent (100-1000/day) queries of both the
> inventory and summary tables using the primary key -- always using the
> index and returning < 10 rows.

RAM is what you need, to cache the data and indexes, and then as much CPU
power as you can get.

> We're currently getting (barely) acceptable performance from a single
> 15k U160 SCSI disk, but db size and activity are growing quickly.
> I've got more disks and a battery-backed LSI card on order.

3 or more disks in a stripe set, with write back caching, will almost
certainly give a huge performance boost.  Try that first, and only if you
have issues should you think about futzing with symlinks etc.

M



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-02-17 22:34 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-17 21:53 RAID or manual split? Mike Glover <mpg4@duluoz.net>
2004-02-17 22:17 ` Rod Taylor <rbt@rbt.ca>
2004-02-17 22:34 ` matt@ymogen.net

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox