Re: Upgrade to dual processor machine? - Mailing list pgsql-general

Hi Henrik,

Ok, you're machine is doing a decent amount of work, and will need
looking at carefully.

Going to get more specific about some stuff, as it'll definitely assist
with giving you proper guidance here.

- Have you run any system-performance tools apart from top, to figure
out how the various parts of your system are operating?

For example, by looking into and measuring the different parts of your
system, you may find you have several processes simultaneously waiting
to execute purely because the disk drives can't keep up with the
requests.  The solution may turn out to be upgrading your disks instead
of your CPU's (example only).  Without taking measurements to the point
of understanding what's going on, you'll only be guessing.

The most concerning aspect at the moment is this:

"> - Have you configured the memory after installation of PostgreSQL, so
> it's better optimised than the defaults?
no - what should I do? Looking at 'top' right now, I see the following:
Mem 1020808K av, 1015840K used, 4968K free, 1356K shrd, 32852K buff"

This is telling me that the system is operating close to using all it's
memory with running processes.  *Bad* for this kind of thing.  The
default memory configuration for PostgreSQL is very lean and causes high
CPU load and slow throughput.  You don't seem to have enough spare
memory at the moment to really try adjusting this upwards.  :(

Important question, how much memory can you get into that server?  Could
you do 3GB or more?

Something that would be *really nice* is if you have a second server
with the same configuration hanging around that you can try stuff on.
For example, loading it with a copy of all your data, changing the
memory configuration, then testing it.


Further system specific details needed:

- Which version of the Linux kernel, and of RedHat?  Different version
of the Linux kernel do things differently.  For example version 2.4.3
does virtual memory differently than say version 2.4.17.


- If you do a ps (ps -ef) during a busy time, how many instances of the
PostgreSQL process do you see in memory?  This will tell you how many
clients have an open connection to the database at any time.


- How much data is in your database(s)?  Just to get an idea of your
volume of data.


- If disk performance turns out to be the problem, would you consider
moving to higher-end hard drives?  This will probably mean an Ultra160
or Ultra320 SCSI card, and drives to match.  That's not going to be
totally cheap, but if you have a decent budget then it might be ok.


As you can see, this could take a bit of time an effort to get right.

Regards and best wishes,

Justin Clift


Henrik Steffen wrote:
>
> Hi Justin,
>
> here a little more information:
>
> > - Processor type and speed
> Intel Pentium IV, 1.6 GHz
>
> > - Memory
> 1024 MB ECC-RAM
>
> > - Disk configuration
> 2 x 60 GB IDE (Raid 0)
>
> > - OS
> Redhat Linux
>
> >
> > - Do you do other stuff on it, apart from PostgreSQL?
> No, it's a dedicated database server
>
> >
> > - How many clients simultaneously connecting to it?
> one webserver with max. 50 instances, approximately 10.000 users a day,
> about 150.000 Pageviews daily. All pages are created on the fly using
> mod_perl connecting to the db-server.
>
> > - What do the clients connect with?  JDBC/ODBC/libpq/etc?
> I am using Pg.pm --- this is called libpq, isn't it?
>
> > - Have you configured the memory after installation of PostgreSQL, so
> > it's better optimised than the defaults?
> no - what should I do? Looking at 'top' right now, I see the following:
> Mem 1020808K av, 1015840K used, 4968K free, 1356K shrd, 32852K buff
>
> So, what do you suggest to gain more performance?
>
> Thanks in advance,

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi