Re: Low Budget Performance

On 28 Oct 2002 at 13:56, eric soroos wrote:

> Currently, I've got postgres running on the same system as the app server accessing a single fast IDE drive. The database is on the order of 1 gig, with two main tables accounting for 98% of the data. Between the app servers and the database, I'm pretty sure that neither of the main tables are
cached in memory for any significant time. I'm guessing that this is sub optimal. (The data size is 1 gig now, but I will be adding more 1 gig databases to this system in the near future) I'm planning to split this into an app server and database server.
>
> In an ideal world, I'd throw a lot of 15k scsi/raid0+1 at this. But I don't have an ideal world budget. I've got more of an ide world budget, if that. (~1k)
>
> I know the first order of business is to ignore the hardware and make sure that I've got all of the table scans found and turned into indexes. I'm still working on that. Are there any tools that save queries and the plans, then report on the ones that are the biggest performance drags?
>
> But since I do software, it's obviously a hardware problem. ;>
>
> My hardware options:

I would say throw a lot of RAM no matter what type. Even PC133 is going to be
faster than any disk you can buy anytimes. I would say 2Gig is a nice place to
start.

A gig is not much of a database but a lot depends upon what do you do with the
data. Obviously 50 clients doing sequential scan with rows ordered in random
fashion would chew any box,..;-)

Processor does not matter much. But I would advice to split app server and
database server ASAP.

Well, IDE RAID looks like nice optio to me, but before finalising RAID config.,
I would advice to test performance and scalability with separate database
server and couple of Gigs of RAM. Because if this configuration is sufficient
for your need, probably you can choose a conservatice RAID config that would
enhance availability rather than getting every ounce of performance out of it.
As far as possible, don't compramise with storage availability.

> Does the write ahead logging of PG mean that no matter what indexes and data are changed, that there will be one sync to disk? Does this reduce the penalty of indexes? WAL seems to mean that to get performance out of a drive array, I'd want to use the fastest (latency/throughput) logical
single image I could get, not a collection of mirrored drives.

I guess RAID will take care of lot of these issues. Besides if you use volume
manager you can add partitions from different disks, effectively splitting the
IO. Of course, you can shutdown the database and symlink things to another
drive, but that's hack and nothing else. Don't do it as far as possible..

HTH

Bye
Shridhar

--
You're dead, Jim. -- McCoy, "Amok Time", stardate 3372.7