RE: Performance Tuning, hardware-wise - Mailing list pgsql-general

What I would like to see is some simple documentation on what is the optimal
disk setup to maximise database throughput and what are the plans in this
area.
Can I separate the parts of the DB to seperate disks? Is is easy to do with
an operational db? Can I seperate data,  indexes logs, temp files etc onto
seperate disks. Where are the major areas of contention and what can be done
about them, which areas would have the most payoff, which optimisations
create little gain.
Is it worth doing this sort of manual optimisation in the present era of
raid sets and if so which raid types would be most useful in the above
scenario.


-----Original Message-----
From: Gordan Bobic [mailto:gordan@freeuk.com]
Sent: Saturday, 30 December 2000 5:06 AM
To: PostgreSQL General
Subject: Re: [GENERAL] Performance Tuning, hardware-wise


[separate physical disks for web server and database]
> I am just wondering whether this is all mad rambling or decent common
sense.

IMHO, it's decent common sense. That's probably why it isn't documented.

> I would also be keen to have some sense of _how_ much I can hope to gain
> by such measures.

I think this is just another example of people expecting to know exactly
what measure will help how much in the performance of their unspecified
application.

THIS CANNOT BE DONE. Your mileage will vary. Use your common sense. If you
have a rather big, frequent random access select/insert/update database,
then disk speed is a very important issue, and if you can, you should put
it on a separate disk, if other things are also hammering the server's disk
subsystem.

But the improvement is not necessarily so obvious. If you have CGI
generated pages, then the hit will be taken by the CPU and the database,
not necessarily by what httpd does. If your files are comparatively small,
as CGIs generally are, then the chances are that with enough memory, they
will be cached anyway. Especially if they are perl CGIs which are running
using mod_perl. So, the disk hit for this will be minimal because they are
constantly used. Just something to consider...

> A more extreme idea: If I know that the total size of my database won't
grow
> over, say, a gigabyte, then why shouldn't I invest my money in RAM
(little
> more than $500 these days, for a G of RAM), create a ramdisk of 1 G
> (provided my architecture can house that much RAM but that's beside the
> point), and mount /usr/local/pgsql/data right there?!

Hmm... This sounds like something that I never understood completely. Back
in the Windows 3.1 days, some people were swearing by the idea of having a
swap file on a RAM disk. I never could figure that out, as more memory in
the main pool is a lot better than faster swap! Before you start flaming me
for mentioning Windows 3.1, this was 10 years ago when I hadn't discovered
UNIX yet. ;-)

Similarly, putting the database on a RAM disk can work, and no doubt, with
a ramdisk bigger than the database, you will see a vast improvement, if all
you ever run is the database, because the whole database will effectively,
already be in the memory "cache". However, this memory cannot be recycled
and used for anything else. This is why more memory is always a good idea,
but leaving the OS to work out the caching is usually the best way for
dealing with an average case scenario. Linux by default uses all free
memory for caching. So, if you have 100 MB of programs running, and have
500 MB of RAM, it will by default allocate 400 MB to cache. In my modest
experience, I've found that letting the OS figure it out is usually the
most optimal solution.

> Am I going mad?

I don't think so, you're just over-thinking about the issue. ;-)

Optimizing the hardware is simple. Start with something you can scrape
together from spares (within reason, of course). Run your application. If
your CPU load goes to nearly 100% for a long time, you need a faster CPU.
If your memory is always all used up and your machine starts swapping like
mad, you need more memory. If your disk is grinding to a halt, you need a
faster disk. Or any combination of the above. As is the case with a lot of
functionality-oriented development, it can be difficult to estimate
hardware requirements before you actually try to run the application.
Depending on what you do with Postgres, you may be happy with a 486 with 32
MB of RAM and a 500 MB disk. OTOH, a 16-way Alpha with 64 GB of RAM may be
insufficient for a different application that is also based on Postgres.
You need to know an awful lot about what it is you're trying to do before
you can reliably say what will improve performance most.


Regards.

Gordan