pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Biagioni <andrew.biagioni@e-greek.net>
To: Steve <steve@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: please please please PLEASE help!
Date: Thu, 29 Jul 2004 11:16:39 -0400
Message-ID: <410914D7.8010106@e-greek.net> (raw)
In-Reply-To: <cdqa6v$hdq$1@news.hub.org>
References: <cdqa6v$hdq$1@news.hub.org>

Steve,

I'm not as much of an expert on PostgreSQL as others, but at least I 
have SOME answer.

The way to leverage the multi-disk capability is to use RAID, either 
software or hardware, rather than trying to twist the PostgreSQL 
structure and files to do so.

RAID (I forget what the acronym stands for) is basically a number of 
methods to provide increased reliability and/or performance from 
multiple disks, while having them appear to the programs (including DB 
programs) as a single disk.

You will want to look for RAID information elsewhere, especially since 
you should weigh the pros and cons of the various types of RAID, but 
basically what you will want is to have your data pread out uniformly 
across the disks so that any writes and reads (in your case, especially 
the reads) are done in parallel on all three devices, thus significantly 
cutting the access time.

Another solution that MIGHT help is increasing the memory.  If a 
heavily-used index can be cached in RAM it will speed searches up a lot...

I would recommend that you also study ways to redistribute your data 
and/or add better indices.  Keep in mind that an index will be quite 
large for a big table, and may not be used to its full extent even when 
it might seem that it should (at least in my experience).  You can also 
use EXPLAIN and EXPLAIN ANALYZE to find out where your bottlenecks are, 
to see what else can be done.

I hope this helps, and that the last paragraph didn't come across as 
patronizing.  I'm aware that maybe you already did all that, but I can't 
know -- and if you didn't, then it would be a shame not to mention it!

             Andrew


Steve wrote:

> Hi,
>
> I've asked this question a couple of times before on this forum but no 
> one seems to be nice enough to point me to the right direction or help 
> me out with any information, if possible. Please help me out with this 
> because this is a very serious issue for me and I need to learn more 
> about this. And here it is again:
>
> I've been running postgres on my server for over a year now and the 
> tables have become huge. I have 3 tables that have data over 10GB each 
> and these tables are read very very frequently. In fact, heavy 
> searches on these tables are expected every 2 to 3 minutes. This 
> unfortunately gives a very poor response time to the end user and so 
> I'm looking at other alternatives now.
>
> Currently, the postgresql installation is on a single disk and so all 
> the tables have their data read from a single disk. Searching on 
> different tables by multiple users at the same time results in very 
> slow searches, as it's mainly dependant on the spindle speed. I 
> recently gained access to another server which has 3 SCSI disks. I 
> know there is a way to mirror the tables across the three different 
> disks but I'm not sure if it's as easy as symlinking the files (WAL 
> files only?) across. Can anyone please tell me what to do here and how 
> to harness the power of the three SCSI drives that I have. Which files 
> in the data directory need to be moved? Is this safe? Can backups etc 
> be easily done? Any information will be greatly appreciated. Thank you,
>
>
> Steve
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>



view thread (9+ messages)  latest in thread

Message-ID: <410914D7.8010106@e-greek.net>
Permalink:  ../410914D7.8010106@e-greek.net/
Also on:    postgresql.org/message-id/410914D7.8010106@e-greek.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: andrew.biagioni@e-greek.net, steve@hotmail.com
  Subject: Re: please please please PLEASE help!
  In-Reply-To: <410914D7.8010106@e-greek.net>
  References: <cdqa6v$hdq$1@news.hub.org> <410914D7.8010106@e-greek.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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