Thread: Upgrade to dual processor machine?

Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

09 November 2002, 10:33:39

hi all,

will an upgrade to a dual processor machine
noticeably increase performance of a postgresql server?

load average now often is about 4.0 - 8.5 - and I'll
have got to do something sooner or later...

any help is appreciated...

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

Re: Upgrade to dual processor machine?

From

Justin Clift

Date:

09 November 2002, 10:50:35

Hi Henrik,

It'd be helpful to know the other specifics of the server, and a bit
about the workload the server has.

i.e.

- Processor type and speed
- Memory
- Disk configuration
- OS

- Do you do other stuff on it, apart from PostgreSQL?

- How many clients simultaneously connecting to it?
- What do the clients connect with?  JDBC/ODBC/libpq/etc?

- Have you configured the memory after installation of PostgreSQL, so
it's better optimised than the defaults?

:-)

Regards and best wishes,

Justin Clift


Henrik Steffen wrote:
>
> hi all,
>
> will an upgrade to a dual processor machine
> noticeably increase performance of a postgresql server?
>
> load average now often is about 4.0 - 8.5 - and I'll
> have got to do something sooner or later...
>
> any help is appreciated...
>
> --
>
> Mit freundlichem Gruß
>
> Henrik Steffen
> Geschäftsführer
>
> top concepts Internetmarketing GmbH
> Am Steinkamp 7 - D-21684 Stade - Germany
> --------------------------------------------------------
> http://www.topconcepts.com          Tel. +49 4141 991230
> mail: steffen@topconcepts.com       Fax. +49 4141 991233
> --------------------------------------------------------
> 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> --------------------------------------------------------
> Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
> System-Partner gesucht: http://www.franchise.city-map.de
> --------------------------------------------------------
> Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> --------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"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

Re: Upgrade to dual processor machine?

From

Neil Conway

Date:

09 November 2002, 16:56:25

"Henrik Steffen" <steffen@city-map.de> writes:
> will an upgrade to a dual processor machine
> noticeably increase performance of a postgresql server?

Assuming you have more than 1 concurrent client, it likely
will. Whether it will be a huge performance improvement depends on the
other characteristics of the workload (e.g. is it I/O bound or CPU
bound?).

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

11 November 2002, 05:06:24

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,

> Hi Henrik,
>
> It'd be helpful to know the other specifics of the server, and a bit
> about the workload the server has.
>
> i.e.
>
> - Processor type and speed
> - Memory
> - Disk configuration
> - OS
>
> - Do you do other stuff on it, apart from PostgreSQL?
>
> - How many clients simultaneously connecting to it?
> - What do the clients connect with?  JDBC/ODBC/libpq/etc?
>
> - Have you configured the memory after installation of PostgreSQL, so
> it's better optimised than the defaults?
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Henrik Steffen wrote:
> >
> > hi all,
> >
> > will an upgrade to a dual processor machine
> > noticeably increase performance of a postgresql server?
> >
> > load average now often is about 4.0 - 8.5 - and I'll
> > have got to do something sooner or later...
> >
> > any help is appreciated...
> >
> > --
> >
> > Mit freundlichem Gruß
> >
> > Henrik Steffen
> > Geschäftsführer
> >
> > top concepts Internetmarketing GmbH
> > Am Steinkamp 7 - D-21684 Stade - Germany
> > --------------------------------------------------------
> > http://www.topconcepts.com          Tel. +49 4141 991230
> > mail: steffen@topconcepts.com       Fax. +49 4141 991233
> > --------------------------------------------------------
> > 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> > --------------------------------------------------------
> > Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
> > System-Partner gesucht: http://www.franchise.city-map.de
> > --------------------------------------------------------
> > Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> > --------------------------------------------------------
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> "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

Re: Upgrade to dual processor machine?

From

Neil Conway

Date:

11 November 2002, 05:32:53

"Henrik Steffen" <steffen@city-map.de> writes:
> > - What do the clients connect with?  JDBC/ODBC/libpq/etc?
> I am using Pg.pm --- this is called libpq, isn't it?

Well, it's a thin Perl wrapper over libpq (which is the C client
API). You said you're using mod_perl: you may wish to consider using
DBI and DBD::Pg instead of Pg.pm, so you can make use of persistent
connections using Apache::DBI.

> > - 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

No, Justin is referring to the memory-related configuration options in
postgresql.conf, like shared_buffers, wal_buffers, sort_mem, and the
like.

> So, what do you suggest to gain more performance?

IMHO, dual processors would likely be a good performance improvement.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Upgrade to dual processor machine?

From

Justin Clift

Date:

11 November 2002, 05:44:29

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

Re: Upgrade to dual processor machine?

From

Helge Bahmann

Date:

11 November 2002, 09:58:37

FWIW, in summer I have done a little bit of testing on one of our
dual-cpu machines; among this I have been running OSDB (open source
database benchmark), 32 simulated clients, against Postgres (7.2.1)/Linux
(2.4.18), once bootet with maxcpus=1 and once with maxcpus=2; if I
remember correctly I saw something between 80-90% performance improvement
on the IR benchmark with the second cpu activated.

Note the run was completely cpu-bound, neither harddisk nor memory was the
bottleneck, so you may see less of an improvement if other parts of your
system are the limit; but Postgres itself appears to make use of the
available cpus quite nicely.

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
The past: Smart users in front of dumb terminals       /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |

Re: Upgrade to dual processor machine?

From

Shaun Thomas

Date:

11 November 2002, 16:09:01

On Mon, 11 Nov 2002, Henrik Steffen wrote:

> > - 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.

Aha.  What kind of web-side data caching are you doing?  That alone can
drop your load down to < 1.  Even something like a 1-hour cache, or
something you can manually expire can work amazing wonders for database
usage.  So far, the only thing we've found that doesn't really fit this
model are full text searches.

Here, the biggest difference to our DB server was caused by *not* having
all of our 9 webservers doing 50+ connections per second, which we
achieved mainly through caching.  Adding another CPU will work as well,
but as far as a long-term, not just throwing hardware at the problem
kind of solution goes, see if you can get caching worked in there
somehow.

Since you know you're using Pg.pm (switch to DBI::pg, trust me on this
one), you should have little problem either caching your result set or
even the whole resulting page with select non-cachable parts.  Not only
will that reduce page-load time, but the strain on your database as
well.

--
Shaun M. Thomas                INN Database Administrator
Phone: (309) 743-0812          Fax  : (309) 743-0830
Email: sthomas@townnews.com    Web  : www.townnews.com

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

11 November 2002, 16:40:20

On Mon, 11 Nov 2002, Shaun Thomas wrote:

> On Mon, 11 Nov 2002, Henrik Steffen wrote:
>
> > > - 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.
>
> Aha.  What kind of web-side data caching are you doing?  That alone can
> drop your load down to < 1.  Even something like a 1-hour cache, or
> something you can manually expire can work amazing wonders for database
> usage.  So far, the only thing we've found that doesn't really fit this
> model are full text searches.
>
> Here, the biggest difference to our DB server was caused by *not* having
> all of our 9 webservers doing 50+ connections per second, which we
> achieved mainly through caching.  Adding another CPU will work as well,
> but as far as a long-term, not just throwing hardware at the problem
> kind of solution goes, see if you can get caching worked in there
> somehow.
>
> Since you know you're using Pg.pm (switch to DBI::pg, trust me on this
> one), you should have little problem either caching your result set or
> even the whole resulting page with select non-cachable parts.  Not only
> will that reduce page-load time, but the strain on your database as
> well.

Agreed.  I highly recommend squid as a caching proxy.  Powerful, fast, and
Open source.  It's included in most flavors of Linux.  I'm sure it's
available as a port if not included in most BSDs as well.

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

12 November 2002, 15:37:45

On Mon, 11 Nov 2002, Henrik Steffen wrote:

> > - 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.

If you've got 50 simos, you could use more CPUs, whether your I/O bound or
not.

> > - 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

Hey, what is the "cached" field saying there?  Is the machine caching a
whole bunch or just a little?  If it's caching a whole bunch, look at
increasing your shmmax shmall settings and then the shared buffers in
postgresql.conf for better performance.

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

12 November 2002, 17:23:15

hi,

thanks for this information...

we are allready using squid as a transpartent www-accelerator,
this works very well and squid handles about 70 % out of all hits.

However, sometimes some search engines use to start
indexing more than 25 DIFFERENT documents per second, this is when things
start getting more difficult .... we have played around a little
with an ip-based bandwidth-regulation tool at squid-level, which
works quite well - though you'll have to add new search-engines
on demand.

But anyway - we still have to look at the facts: we have had a 200 %
increase of visitors and pageviews during the last 6 months.

Upgrading to DBI:pg is something I have been thinking about allready,
but as far as I know, I am allready using persistent connections with
mod_perl and Pg.pm, am I not???!

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shaun Thomas" <sthomas@townnews.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "Justin Clift" <justin@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Monday, November 11, 2002 7:08 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On Mon, 11 Nov 2002, Henrik Steffen wrote:
>
> > > - 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.
>
> Aha.  What kind of web-side data caching are you doing?  That alone can
> drop your load down to < 1.  Even something like a 1-hour cache, or
> something you can manually expire can work amazing wonders for database
> usage.  So far, the only thing we've found that doesn't really fit this
> model are full text searches.
>
> Here, the biggest difference to our DB server was caused by *not* having
> all of our 9 webservers doing 50+ connections per second, which we
> achieved mainly through caching.  Adding another CPU will work as well,
> but as far as a long-term, not just throwing hardware at the problem
> kind of solution goes, see if you can get caching worked in there
> somehow.
>
> Since you know you're using Pg.pm (switch to DBI::pg, trust me on this
> one), you should have little problem either caching your result set or
> even the whole resulting page with select non-cachable parts.  Not only
> will that reduce page-load time, but the strain on your database as
> well.
>
> --
> Shaun M. Thomas                INN Database Administrator
> Phone: (309) 743-0812          Fax  : (309) 743-0830
> Email: sthomas@townnews.com    Web  : www.townnews.com
>

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

12 November 2002, 17:28:29

The cache-field is saying 873548K cached at the moment
Is this a "whole bunch of cache" in your opinion? Is it too much?

So, where do i find and change shmmax shmall settings ??
What should I put there?

What is a recommended value for shared buffers in postgresql.conf ?


FYI:

ps ax | grep -c postgres ==> shows 23 at the moment

however, w shows: load average 3.09, 2.01, 1.76
(this is low at the moment)

thanks again,

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "Justin Clift" <justin@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Tuesday, November 12, 2002 6:22 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On Mon, 11 Nov 2002, Henrik Steffen wrote:
>
> > > - 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.
>
> If you've got 50 simos, you could use more CPUs, whether your I/O bound or
> not.
>
> > > - 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
>
> Hey, what is the "cached" field saying there?  Is the machine caching a
> whole bunch or just a little?  If it's caching a whole bunch, look at
> increasing your shmmax shmall settings and then the shared buffers in
> postgresql.conf for better performance.
>
>
>

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

12 November 2002, 17:42:48

Hi Justin,

thanks for your answer, I will now try to deliver some more information
to you... but I am in particular a programmer, not a hacker ;-)) so please
excuse if I lack some knowledge in system things and stuff....

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

nope. don't know any... which would you recommend for measurement of i/o
usage etc. ?

> The solution may turn out to be upgrading your disks instead
> of your CPU's (example only).

I will at least consider this... IDE disks are not that reliable either...

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

no, sorry  -  1 GB is allready the upper limit... I consider migrating everything
to a new hardware, (dual?) intel xeon with perhaps even raid-v storage system with
a new upper limit of 12 GB RAM which will give me some upgrade-possibilies ... ;-))

> 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.

I actually DO have an identical second server, and the db is allready on it.
however, the system has a few problems concerning harddisk failuers and memory
problems (don't ever use it for running systems!! we had this server on the list
before... I almost gave up on this one, when suddenly all problems and crashes
were solved when moving to a different machine as suggested by tom lane ....)
... but for some testing purpose it sould be sufficient ;-))



> - Which version of the Linux kernel, and of RedHat?

redhat - linux kernel 2.4.7-10


> - 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
> ients have an open connection to the database at any time.

up to 40 clients are running... right now it's 21 processes and w shows
a load average of 1.92, 1.58, 1.59

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

It's 3.6 GB at the moment in one database in 98 user tables.

> - If disk performance turns out to be the problem, would you consider
> moving to higher-end hard drives

allready considering ....


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, November 11, 2002 8:44 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

12 November 2002, 17:45:50

hello,

Am I not allready using persistent connections with Pg.pm ?

It looks at least like it.... I only need a new connection
from webserver to db-server once a new webserver child is born.

well, anyway i am consindering updating to DBD::Pg of course...
it's only to change about 100.000 lines of perl code ....


> No, Justin is referring to the memory-related configuration options in
> postgresql.conf, like shared_buffers, wal_buffers, sort_mem, and the
> like.

so, how am i supposed to tune these settings ??

thanks again,

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Neil Conway" <neilc@samurai.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "Justin Clift" <justin@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Monday, November 11, 2002 8:32 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> "Henrik Steffen" <steffen@city-map.de> writes:
> > > - What do the clients connect with?  JDBC/ODBC/libpq/etc?
> > I am using Pg.pm --- this is called libpq, isn't it?
>
> Well, it's a thin Perl wrapper over libpq (which is the C client
> API). You said you're using mod_perl: you may wish to consider using
> DBI and DBD::Pg instead of Pg.pm, so you can make use of persistent
> connections using Apache::DBI.
>
> > > - 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
>
> No, Justin is referring to the memory-related configuration options in
> postgresql.conf, like shared_buffers, wal_buffers, sort_mem, and the
> like.
>
> > So, what do you suggest to gain more performance?
>
> IMHO, dual processors would likely be a good performance improvement.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Upgrade to dual processor machine?

From

Josh Berkus

Date:

12 November 2002, 18:04:21

Heinrik,

"So, where do i find and change shmmax shmall settings ??
What should I put there?

What is a recommended value for shared buffers in postgresql.conf ?"

There is no "recommended value."   You have to calculate this relatively:

1) Figure out how much RAM your server has available for PostgreSQL.  For
example, I have one server on which I allocate 256 mb for Apache, 128 mb for
linux, and thus have 512mb available for Postgres.

2) Calculate out the memory settings to use 70% of that amount of Ram in
regular usage.   Please beware that sort_mem is *not* shared, meaning that it
will be multiplied by the number of concurrent requests requiring sorting.
Thus, your calculation (in K) should be:

250K  +
8.2K * shared_buffers +
14.2K * max_connections +
sort_mem * average number of requests per minute
=====================================
memory available to postgresql in K * 0.7

You will also have to set SHMMAX and SHMMALL to accept this memory allocation.
Since shmmax is set in bytes, then I generally feel safe making it:
1024 * 0.5 * memory available to postgresql in K

Setting them is done simply:
$ echo 134217728 >/proc/sys/kernel/shmall
$ echo 134217728 >/proc/sys/kernel/shmmax

This is all taken from the postgresql documentation, with some experience:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

12 November 2002, 18:41:09

On Tue, 12 Nov 2002, Josh Berkus wrote:

> Heinrik,
>
> "So, where do i find and change shmmax shmall settings ??
> What should I put there?
>
> What is a recommended value for shared buffers in postgresql.conf ?"
>
> There is no "recommended value."   You have to calculate this relatively:
>
> 1) Figure out how much RAM your server has available for PostgreSQL.  For
> example, I have one server on which I allocate 256 mb for Apache, 128 mb for
> linux, and thus have 512mb available for Postgres.
>
> 2) Calculate out the memory settings to use 70% of that amount of Ram in
> regular usage.   Please beware that sort_mem is *not* shared, meaning that it
> will be multiplied by the number of concurrent requests requiring sorting.
> Thus, your calculation (in K) should be:
>
> 250K  +
> 8.2K * shared_buffers +
> 14.2K * max_connections +
> sort_mem * average number of requests per minute
> =====================================
> memory available to postgresql in K * 0.7
>
> You will also have to set SHMMAX and SHMMALL to accept this memory allocation.
> Since shmmax is set in bytes, then I generally feel safe making it:
> 1024 * 0.5 * memory available to postgresql in K
>
> Setting them is done simply:
> $ echo 134217728 >/proc/sys/kernel/shmall
> $ echo 134217728 >/proc/sys/kernel/shmmax
>
> This is all taken from the postgresql documentation, with some experience:
> http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html

Note that on RedHat boxes, you can also use the /etc/sysctl.conf file to
do this.  It is considered the preferred method, and a little less obtuse
for beginners.

As root, run 'sysctl -a' to get a list of all possible system kernel
settings.  'sysctl -a | grep shm' will show you all the shared memory
settings as they are now.  Edit the /etc/sysctl.conf file with the new
settings and use 'sysctl -p' to process the new settings.  This way you
don't have to edit the /etc/rc.d/rc.local file to get the settings you
want.

On the subject of sort_mem, I've found that if your result sets are all
large (say 100+megs each) that as long as your sort mem isn't big enough
to hold the whole result set, the performance difference is negligable.
I.e. going from 4 meg to 16 meg of sort_mem for a 100 Meg result set
doesn't seem to help much at all.  In fact, in some circumstances, it
seems that the smaller number is faster, especially under heavy parallel
load, since larger settings may result in undesired swapping out of other
processes to allocate memory for sorts.

In other words, it's faster to sort 20 results in 4 megs each if you
aren't causing swapping out, than it is to sort 20 results in 32 megs
each if that does cause things to swap out.

Re: Upgrade to dual processor machine?

From

Neil Conway

Date:

13 November 2002, 01:37:38

"Henrik Steffen" <steffen@city-map.de> writes:
> > No, Justin is referring to the memory-related configuration options in
> > postgresql.conf, like shared_buffers, wal_buffers, sort_mem, and the
> > like.
>
> so, how am i supposed to tune these settings ??

postgresql.conf

See the documentation:

    http://developer.postgresql.org/docs/postgres/runtime-config.html

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 05:29:58

Hello Josh!

This is was I figured out now:

1) RAM available: 1024 MB, there's nothing else but postgres on this
   machine, so if I calculate 128 MB for Linux, there are 896 MB left
   for Postgres.

2) 70 % of 896 MB is 627 MB

Now, if I follow your instructions:

250K +
8.2K * 128 (shared_buffers) = 1049,6K +
14.2K * 64 (max_connections) = 908,8K +
1024K * 5000 (average number of requests per minute) = 5120000K
===============================================================
5122208.4K ==> 5002.16 MB

this is a little bit more than I have available, isn't it? :(((

sure that this has got to be the "average number of requests per minute"
and not "per second" ? seems so much, doesn't it?

what am I supposed to do now?

thanks again,

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-general@postgresql.org>
Cc: <steffen@city-map.de>
Sent: Tuesday, November 12, 2002 9:05 PM
Subject: Re: Upgrade to dual processor machine?


Heinrik,

"So, where do i find and change shmmax shmall settings ??
What should I put there?

What is a recommended value for shared buffers in postgresql.conf ?"

There is no "recommended value."   You have to calculate this relatively:

1) Figure out how much RAM your server has available for PostgreSQL.  For
example, I have one server on which I allocate 256 mb for Apache, 128 mb for
linux, and thus have 512mb available for Postgres.

2) Calculate out the memory settings to use 70% of that amount of Ram in
regular usage.   Please beware that sort_mem is *not* shared, meaning that it
will be multiplied by the number of concurrent requests requiring sorting.
Thus, your calculation (in K) should be:

250K  +
8.2K * shared_buffers +
14.2K * max_connections +
sort_mem * average number of requests per minute
=====================================
memory available to postgresql in K * 0.7

You will also have to set SHMMAX and SHMMALL to accept this memory allocation.
Since shmmax is set in bytes, then I generally feel safe making it:
1024 * 0.5 * memory available to postgresql in K

Setting them is done simply:
$ echo 134217728 >/proc/sys/kernel/shmall
$ echo 134217728 >/proc/sys/kernel/shmmax

This is all taken from the postgresql documentation, with some experience:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 05:53:05

On 13 Nov 2002 at 8:29, Henrik Steffen wrote:

> Hello Josh!
>
> This is was I figured out now:
>
> 1) RAM available: 1024 MB, there's nothing else but postgres on this
>    machine, so if I calculate 128 MB for Linux, there are 896 MB left
>    for Postgres.
>
> 2) 70 % of 896 MB is 627 MB
>
> Now, if I follow your instructions:
>
> 250K +
> 8.2K * 128 (shared_buffers) = 1049,6K +
> 14.2K * 64 (max_connections) = 908,8K +
> 1024K * 5000 (average number of requests per minute) = 5120000K
> ===============================================================
> 5122208.4K ==> 5002.16 MB
>
> this is a little bit more than I have available, isn't it? :(((

Obviously tuning depends upon application and you have to set the threshold by
trial and error.

I would suggest following from some recent discussions on such topics.

1)Set shared buffers somewhere between 500-600MB. Tha'ts going to be optimal
range for a Gig of RAM.

2) How big you database is? How much of it you need it in memory at any given
time? You need to get these figures while setting shared buffers. But still 500-
600MB seems good because it does not include file system cache and buffers.

3) Sort mem is a tricky affair. AFAIU, it is used only when you create index or
sort results of a query. If do these things seldomly, you can set this very low
or default. For individual session that creates index, you can set the sort
memory accordingly. Certainly in your case, number of requests per minute are
high but if you are not creating any index/sorting in each query, you can leave
the default as it is..

HTH

Bye
 Shridhar

--
Another dream that failed.  There's nothing sadder.        -- Kirk, "This side of
Paradise", stardate 3417.3

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 06:14:39

Hello Shridhar,

thanks for your answer...

1) in the docs it says: shared_buffers should be 2*max_connections, min 16.
now, you suggest to put it to 500-600 MB, which means I will have to
increase shared_buffers to 68683 -- is this really correct? I mean,
RAM is allready now almost totally consumed.

2) the database has a size of 3.6 GB at the moment... about 100 user tables.

3) ok, I understand: I am not creating any indexes usually. Only once at night
all user indexes are dropped and recreated, I could imagine to increase the
sort_mem for this script... so sort_mem with 1024K is ok, or should it be
lowered to, say, 512K ?


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 13, 2002 8:53 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On 13 Nov 2002 at 8:29, Henrik Steffen wrote:
>
> > Hello Josh!
> >
> > This is was I figured out now:
> >
> > 1) RAM available: 1024 MB, there's nothing else but postgres on this
> >    machine, so if I calculate 128 MB for Linux, there are 896 MB left
> >    for Postgres.
> >
> > 2) 70 % of 896 MB is 627 MB
> >
> > Now, if I follow your instructions:
> >
> > 250K +
> > 8.2K * 128 (shared_buffers) = 1049,6K +
> > 14.2K * 64 (max_connections) = 908,8K +
> > 1024K * 5000 (average number of requests per minute) = 5120000K
> > ===============================================================
> > 5122208.4K ==> 5002.16 MB
> >
> > this is a little bit more than I have available, isn't it? :(((
>
> Obviously tuning depends upon application and you have to set the threshold by
> trial and error.
>
> I would suggest following from some recent discussions on such topics.
>
> 1)Set shared buffers somewhere between 500-600MB. Tha'ts going to be optimal
> range for a Gig of RAM.
>
> 2) How big you database is? How much of it you need it in memory at any given
> time? You need to get these figures while setting shared buffers. But still 500-
> 600MB seems good because it does not include file system cache and buffers.
>
> 3) Sort mem is a tricky affair. AFAIU, it is used only when you create index or
> sort results of a query. If do these things seldomly, you can set this very low
> or default. For individual session that creates index, you can set the sort
> memory accordingly. Certainly in your case, number of requests per minute are
> high but if you are not creating any index/sorting in each query, you can leave
> the default as it is..
>
> HTH
>
> Bye
>  Shridhar
>
> --
> Another dream that failed.  There's nothing sadder. -- Kirk, "This side of
> Paradise", stardate 3417.3
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 06:26:37

On 13 Nov 2002 at 9:14, Henrik Steffen wrote:
> 1) in the docs it says: shared_buffers should be 2*max_connections, min 16.
> now, you suggest to put it to 500-600 MB, which means I will have to
> increase shared_buffers to 68683 -- is this really correct? I mean,
> RAM is allready now almost totally consumed.

Yes. 2*max connection is minimum. Anything additional is always welcome as long
as it does not starve the system.

If you have a gig of memory and shared buffers are 536MB as you have indicated,
who is taking rest of the RAM?

What are your current settings? Could you please repost. I lost earlier
thread(Sorry for that.. Had a HDD meltdown here couple of days back. Lost few
mails..)

> 2) the database has a size of 3.6 GB at the moment... about 100 user tables.

500-600MB would take you comfortably in this case..

> 3) ok, I understand: I am not creating any indexes usually. Only once at night
> all user indexes are dropped and recreated, I could imagine to increase the
> sort_mem for this script... so sort_mem with 1024K is ok, or should it be
> lowered to, say, 512K ?

That actually depends upons size of table you are indexing and time you can
allow for indexing. Default is 4 MB. I would something like 32MB should help a
lot..

HTH

Bye
 Shridhar

--
QOTD:    "It seems to me that your antenna doesn't bring in too many    stations
anymore."

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 07:43:13

dear shridhar,

> Yes. 2*max connection is minimum. Anything additional is always welcome as long
> as it does not starve the system.

ok, I tried to set shared_buffers to 65535 now. but then restarting postgres
fails - it says:

IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed: Invalid argument

and a message telling me to either lower the shared_buffers or raise the
SHMMAX.

> If you have a gig of memory and shared buffers are 536MB as you have indicated,
> who is taking rest of the RAM?

well, I guess it's postgres... see the output of top below:

 11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K buff
Swap: 1028112K av,      60K used, 1028052K free                  849776K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
10678 root      19   0  2184 2184  1584 S     2,9  0,2   0:00 sendmail
    1 root       8   0   520  520   452 S     0,0  0,0   0:03 init
    2 root       9   0     0    0     0 SW    0,0  0,0   0:00 keventd
    3 root       9   0     0    0     0 SW    0,0  0,0   0:00 kapm-idled
    4 root      19  19     0    0     0 SWN   0,0  0,0   0:00 ksoftirqd_CPU0
    5 root       9   0     0    0     0 SW    0,0  0,0   0:28 kswapd
    6 root       9   0     0    0     0 SW    0,0  0,0   0:00 kreclaimd
    7 root       9   0     0    0     0 SW    0,0  0,0   0:09 bdflush
    8 root       9   0     0    0     0 SW    0,0  0,0   0:00 kupdated
    9 root      -1 -20     0    0     0 SW<   0,0  0,0   0:00 mdrecoveryd
   13 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  136 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  137 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  138 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  139 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  140 root       9   0     0    0     0 SW    0,0  0,0   2:16 kjournald
  378 root       9   0     0    0     0 SW    0,0  0,0   0:00 eth0
  454 root       9   0   572  572   476 S     0,0  0,0   0:00 syslogd
  459 root       9   0  1044 1044   392 S     0,0  0,1   0:00 klogd
  572 root       8   0  1128 1092   968 S     0,0  0,1   0:07 sshd
  584 root       9   0  1056 1056   848 S     0,0  0,1   0:02 nlservd
  611 root       8   0  1836 1820  1288 S     0,0  0,1   0:00 sendmail
  693 root       9   0   640  640   556 S     0,0  0,0   0:00 crond
  729 daemon     9   0   472  464   404 S     0,0  0,0   0:00 atd
  736 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  737 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  738 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  739 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  740 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  741 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
 9800 root       9   0  1888 1864  1552 S     0,0  0,1   0:02 sshd
 9801 root      16   0  1368 1368  1016 S     0,0  0,1   0:00 bash
10574 postgres   0   0  1448 1448  1380 S     0,0  0,1   0:00 postmaster
10576 postgres   9   0  1436 1436  1388 S     0,0  0,1   0:00 postmaster
10577 postgres   9   0  1480 1480  1388 S     0,0  0,1   0:00 postmaster
10579 postgres  14   0 11500  11M 10324 S     0,0  1,1   0:08 postmaster
10580 postgres   9   0 11672  11M 10328 S     0,0  1,1   0:03 postmaster
10581 postgres  14   0 11620  11M 10352 S     0,0  1,1   0:08 postmaster
10585 postgres  11   0 11560  11M 10304 S     0,0  1,1   0:08 postmaster
10588 postgres   9   0 11520  11M 10316 S     0,0  1,1   0:14 postmaster
10589 postgres   9   0 11632  11M 10324 S     0,0  1,1   0:06 postmaster
10590 postgres  10   0 11620  11M 10320 S     0,0  1,1   0:06 postmaster
10591 postgres   9   0 11536  11M 10320 S     0,0  1,1   0:08 postmaster
10592 postgres  11   0 11508  11M 10316 S     0,0  1,1   0:04 postmaster
10595 postgres   9   0 11644  11M 10324 S     0,0  1,1   0:03 postmaster
10596 postgres  11   0 11664  11M 10328 S     0,0  1,1   0:08 postmaster
10597 postgres   9   0 11736  11M 10340 S     0,0  1,1   0:24 postmaster
10598 postgres   9   0 11500  11M 10312 S     0,0  1,1   0:10 postmaster
10599 postgres  11   0 11676  11M 10324 S     0,0  1,1   0:13 postmaster
10602 postgres   9   0 11476  11M 10308 S     0,0  1,1   0:09 postmaster
10652 postgres   9   0  7840 7840  7020 S     0,0  0,7   0:00 postmaster
10669 postgres   9   0  9076 9076  8224 S     0,0  0,8   0:00 postmaster
10677 root      13   0  1032 1028   828 R     0,0  0,1   0:00 top

I have now changed the SHMMAX settings to 545333248 and changed the
shared_buffers to 65535 again. now postgres starts up correctly.

the top result changes to:

 11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K buff
Swap: 1028112K av,      60K used, 1028052K free                  338376K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
11010 root      17   0  1036 1032   828 R    14,2  0,1   0:00 top
11007 postgres  14   0 14268  13M 12668 R     9,7  1,3   0:00 postmaster
11011 root       9   0  2184 2184  1584 S     3,0  0,2   0:00 sendmail
    1 root       8   0   520  520   452 S     0,0  0,0   0:03 init
    2 root       9   0     0    0     0 SW    0,0  0,0   0:00 keventd
    3 root       9   0     0    0     0 SW    0,0  0,0   0:00 kapm-idled
    4 root      19  19     0    0     0 SWN   0,0  0,0   0:00 ksoftirqd_CPU0
    5 root       9   0     0    0     0 SW    0,0  0,0   0:29 kswapd
    6 root       9   0     0    0     0 SW    0,0  0,0   0:00 kreclaimd
    7 root       9   0     0    0     0 SW    0,0  0,0   0:09 bdflush
    8 root       9   0     0    0     0 SW    0,0  0,0   0:00 kupdated
    9 root      -1 -20     0    0     0 SW<   0,0  0,0   0:00 mdrecoveryd
   13 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  136 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  137 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  138 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  139 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
  140 root       9   0     0    0     0 SW    0,0  0,0   2:18 kjournald
  378 root       9   0     0    0     0 SW    0,0  0,0   0:00 eth0
  454 root       9   0   572  572   476 S     0,0  0,0   0:00 syslogd
  459 root       9   0  1044 1044   392 S     0,0  0,1   0:00 klogd
  572 root       8   0  1128 1092   968 S     0,0  0,1   0:07 sshd
  584 root       9   0  1056 1056   848 S     0,0  0,1   0:02 nlservd
  611 root       9   0  1836 1820  1288 S     0,0  0,1   0:00 sendmail
  693 root       9   0   640  640   556 S     0,0  0,0   0:00 crond
  729 daemon     9   0   472  464   404 S     0,0  0,0   0:00 atd
  736 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  737 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  738 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  739 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  740 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
  741 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
 9800 root       9   0  1888 1864  1552 S     0,0  0,1   0:03 sshd
 9801 root      10   0  1368 1368  1016 S     0,0  0,1   0:00 bash
10838 postgres   7   0  6992 6992  6924 S     0,0  0,6   0:00 postmaster
10840 postgres   9   0  6984 6984  6932 S     0,0  0,6   0:00 postmaster
10841 postgres   9   0  7024 7024  6932 S     0,0  0,6   0:00 postmaster
10852 postgres   9   0  489M 489M  487M S     0,0 49,0   0:32 postmaster
10869 postgres   9   0  357M 357M  356M S     0,0 35,8   0:21 postmaster
10908 postgres   9   0  263M 263M  262M S     0,0 26,4   0:20 postmaster
10909 postgres   9   0  283M 283M  281M S     0,0 28,4   0:19 postmaster
10932 postgres   9   0  288M 288M  286M S     0,0 28,9   0:13 postmaster
10946 postgres   9   0  213M 213M  211M S     0,0 21,4   0:06 postmaster
10947 postgres   9   0  239M 239M  238M S     0,0 24,0   0:07 postmaster
10948 postgres   9   0  292M 292M  290M S     0,0 29,2   0:09 postmaster
10957 postgres   9   0  214M 214M  212M S     0,0 21,5   0:10 postmaster
10964 postgres   9   0 58156  56M 56400 S     0,0  5,6   0:05 postmaster
10974 postgres   9   0 50860  49M 49120 S     0,0  4,9   0:04 postmaster
10975 postgres   9   0  209M 209M  207M S     0,0 21,0   0:04 postmaster
10976 postgres   9   0  174M 174M  172M S     0,0 17,5   0:08 postmaster
10977 postgres   9   0 52484  51M 50932 S     0,0  5,1   0:05 postmaster
10990 postgres   9   0  199M 199M  197M S     0,0 19,9   0:06 postmaster
10993 postgres   9   0  141M 141M  139M S     0,0 14,1   0:01 postmaster
10998 postgres   9   0  181M 181M  180M S     0,0 18,2   0:04 postmaster
10999 postgres   9   0  139M 139M  138M S     0,0 14,0   0:01 postmaster
11001 postgres   9   0 45484  44M 43948 S     0,0  4,4   0:01 postmaster
11006 postgres   9   0 15276  14M 13952 S     0,0  1,4   0:00 postmaster


now, does this look better in your eyes?

> What are your current settings? Could you please repost. I lost earlier
> thread(Sorry for that.. Had a HDD meltdown here couple of days back. Lost few
> mails..)

do you need more information here?

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 07:59:42

On 13 Nov 2002 at 10:42, Henrik Steffen wrote:
> > Yes. 2*max connection is minimum. Anything additional is always welcome as long
> > as it does not starve the system.
>
> ok, I tried to set shared_buffers to 65535 now. but then restarting postgres
> fails - it says:
>
> IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed: Invalid argument
>
> and a message telling me to either lower the shared_buffers or raise the
> SHMMAX.

Yes. you need to raise SHMMAX. A good feature of recent linux distro. is that
they set SHMMAX to half of physical memory. A very good default IMO..

>  11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
> 53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
> CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
> Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K buff
> Swap: 1028112K av,      60K used, 1028052K free                  849776K cached
> I have now changed the SHMMAX settings to 545333248 and changed the
> shared_buffers to 65535 again. now postgres starts up correctly.
>
> the top result changes to:
>
>  11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
> 57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
> CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
> Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K buff
> Swap: 1028112K av,      60K used, 1028052K free                  338376K cached
> now, does this look better in your eyes?

Well, don't look at top to find out free memoy. Use free. On my machine..

[shridhar@perth shridhar]$ free
             total       used       free     shared    buffers     cached
Mem:        255828     250676       5152          0      66564      29604
-/+ buffers/cache:     154508     101320
Swap:       401616      12764     388852
[shridhar@perth shridhar]$

Here the important value is second value in second line, 101320. That's true
free memory. Remeber when system needs memory, it can always shrunk
cache/buffers. In both of your stats, cache+memory are roughly 400MB.

Relax, your system is not starving for memory...

> do you need more information here?

Not for this problem, but just curious. What does uname -a says?

Secondly just curious, with 5000 requests per minute, what is the peak number
of connection you are getting? You should look int pooling parameters for
better performance..

HTH


Bye
 Shridhar

--
Hawkeye's Conclusion:    It's not easy to play the clown when you've got to run
the whole    circus.

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 08:55:18

Dear Shridhar,

ok, so my system has got 362 MB of free RAM currently... this sounds good.

uname -a says:
Linux db2.city-map.de 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown

I didn't actually measure requests per minute through a longer period...
I tested it 2 hours ago using debug and logging all queries, and I saw
approx. 2500 requests per minute. but at that time of the day there are
only about 25 simultaneous users on our website. so i calculated 50
users and 5.000 rpm for average daytime usage. I guess the maximum peak
would be approx. 10.000 queries per minute.

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 13, 2002 11:00 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On 13 Nov 2002 at 10:42, Henrik Steffen wrote:
> > > Yes. 2*max connection is minimum. Anything additional is always welcome as long
> > > as it does not starve the system.
> >
> > ok, I tried to set shared_buffers to 65535 now. but then restarting postgres
> > fails - it says:
> >
> > IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed: Invalid argument
> >
> > and a message telling me to either lower the shared_buffers or raise the
> > SHMMAX.
>
> Yes. you need to raise SHMMAX. A good feature of recent linux distro. is that
> they set SHMMAX to half of physical memory. A very good default IMO..
>
> >  11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
> > 53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
> > CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
> > Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K buff
> > Swap: 1028112K av,      60K used, 1028052K free                  849776K cached
> > I have now changed the SHMMAX settings to 545333248 and changed the
> > shared_buffers to 65535 again. now postgres starts up correctly.
> >
> > the top result changes to:
> >
> >  11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
> > 57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
> > CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
> > Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K buff
> > Swap: 1028112K av,      60K used, 1028052K free                  338376K cached
> > now, does this look better in your eyes?
>
> Well, don't look at top to find out free memoy. Use free. On my machine..
>
> [shridhar@perth shridhar]$ free
>              total       used       free     shared    buffers     cached
> Mem:        255828     250676       5152          0      66564      29604
> -/+ buffers/cache:     154508     101320
> Swap:       401616      12764     388852
> [shridhar@perth shridhar]$
>
> Here the important value is second value in second line, 101320. That's true
> free memory. Remeber when system needs memory, it can always shrunk
> cache/buffers. In both of your stats, cache+memory are roughly 400MB.
>
> Relax, your system is not starving for memory...
>
> > do you need more information here?
>
> Not for this problem, but just curious. What does uname -a says?
>
> Secondly just curious, with 5000 requests per minute, what is the peak number
> of connection you are getting? You should look int pooling parameters for
> better performance..
>
> HTH
>
>
> Bye
>  Shridhar
>
> --
> Hawkeye's Conclusion: It's not easy to play the clown when you've got to run
> the whole circus.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 09:02:09

On 13 Nov 2002 at 11:54, Henrik Steffen wrote:

> Dear Shridhar,
>
> ok, so my system has got 362 MB of free RAM currently... this sounds good.

Cool.. Keep watching that.. If that goes down to less than 50, you certainly
need to look into..

> uname -a says:
> Linux db2.city-map.de 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown

hmm.. Some sort of RedHat I assume. Upgrade the kernel at least. Any variant of
2.4.19.x should give you at least 10-15% performance increase. Besides that
will cure the linux VM fiascos as well..

> I didn't actually measure requests per minute through a longer period...
> I tested it 2 hours ago using debug and logging all queries, and I saw
> approx. 2500 requests per minute. but at that time of the day there are
> only about 25 simultaneous users on our website. so i calculated 50
> users and 5.000 rpm for average daytime usage. I guess the maximum peak
> would be approx. 10.000 queries per minute.

Hmm.. Certainly connection pooling will give you advantage. Tune it if you can(
php BTW?)

HTH

Bye
 Shridhar

--
divorce, n:    A change of wife.

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 10:00:53

> Cool.. Keep watching that.. If that goes down to less than 50, you certainly
> need to look into..

I will.

> hmm.. Some sort of RedHat I assume. Upgrade the kernel at least. Any variant of
> 2.4.19.x should give you at least 10-15% performance increase. Besides that
> will cure the linux VM fiascos as well..

redhat, right. Ok, I will have this tested.

> Hmm.. Certainly connection pooling will give you advantage. Tune it if you can(
> php BTW?)

using persistent connections with perl / apache mod_perl

Re: Upgrade to dual processor machine?

From

Neil Conway

Date:

13 November 2002, 11:20:45

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> 3) Sort mem is a tricky affair. AFAIU, it is used only when you create index or
> sort results of a query. If do these things seldomly, you can set this very low
> or default. For individual session that creates index, you can set the sort
> memory accordingly.

What would the benefit of this be? sort_mem is just an upper limit on
memory consumption, and that memory is only allocated on demand. So
there shouldn't be a difference between setting sort_mem globally to
some reasonable value, and manually changing it for backends that need
to do any sorting.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 11:24:33

On 13 Nov 2002 at 8:20, Neil Conway wrote:

> "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > 3) Sort mem is a tricky affair. AFAIU, it is used only when you create index or
> > sort results of a query. If do these things seldomly, you can set this very low
> > or default. For individual session that creates index, you can set the sort
> > memory accordingly.
>
> What would the benefit of this be? sort_mem is just an upper limit on
> memory consumption, and that memory is only allocated on demand. So
> there shouldn't be a difference between setting sort_mem globally to
> some reasonable value, and manually changing it for backends that need
> to do any sorting.

Well, while that is correct, setting sort mem high only when required would
prevent memory exhaustion if that happens.

Remember he has 5000 requests per minute with concurrent connection. Now say
there is a default high setting of sort mem and a connection persist for a long
time, it *might* accumulate memory. Personally I would not keep it high by
default.

Bye
 Shridhar

--
Absentee, n.:    A person with an income who has had the forethought to remove
himself from the sphere of exaction.        -- Ambrose Bierce, "The Devil's
Dictionary"

Re: Upgrade to dual processor machine?

From

Björn Metzdorf

Date:

13 November 2002, 11:29:50

> > What would the benefit of this be? sort_mem is just an upper limit on
> > memory consumption, and that memory is only allocated on demand. So
> > there shouldn't be a difference between setting sort_mem globally to
> > some reasonable value, and manually changing it for backends that need
> > to do any sorting.
>
> Well, while that is correct, setting sort mem high only when required
would
> prevent memory exhaustion if that happens.
>
> Remember he has 5000 requests per minute with concurrent connection. Now
say
> there is a default high setting of sort mem and a connection persist for a
long
> time, it *might* accumulate memory. Personally I would not keep it high by
> default.

Could you elaborate on what exactly is a query requiring sorting (and
therefore is affected by sort_mem setting)?

Is it a SELECT with WHERE-clause using seq scan? Is it rebuilding of an
index? What else could it be?

Regards,
Bjoern

Re: Upgrade to dual processor machine?

From

"Shridhar Daithankar"

Date:

13 November 2002, 11:34:17

On 13 Nov 2002 at 14:30, Björn Metzdorf wrote:

> Could you elaborate on what exactly is a query requiring sorting (and
> therefore is affected by sort_mem setting)?
> Is it a SELECT with WHERE-clause using seq scan? Is it rebuilding of an
> index? What else could it be?

I can think of an sql query with an order by clause on a non-indexed field and
say that field is not included in where condition e.g.

select name, addreess from users where id>1000 order by name;

with index on id.


Bye
 Shridhar

--
You canna change the laws of physics, Captain; I've got to have thirty minutes!

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

13 November 2002, 11:46:56

did you also try a portscan?

the server IS behind a firewall and very well protected.
there's just the ping allowed, nothing else...


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Csaba Nagy" <nagy@domeus.de>
To: "'Henrik Steffen'" <steffen@city-map.de>
Sent: Wednesday, November 13, 2002 1:46 PM
Subject: AW: [GENERAL] Upgrade to dual processor machine?


Your database's IP address can be pinged from the internet... is this
deliberate ? The database should be behind your firewall.
You just published the IP to the Postgres list... don't keep sensitive data
there I would say.

PING wird ausgeführt für db2.city-map.de [62.116.172.165] mit 32 Bytes
Daten:

Antwort von 62.116.172.165: Bytes=32 Zeit=31ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=10ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=10ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=20ms TTL=242

PING wird ausgeführt für www.city-map.de [62.116.172.170] mit 32 Bytes
Daten:

Antwort von 62.116.172.170: Bytes=32 Zeit=20ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=20ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=21ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=10ms TTL=242

Cheers,
Csaba.

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Henrik Steffen
Gesendet: Mittwoch, 13. November 2002 11:55
An: shridhar_daithankar@persistent.co.in
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Upgrade to dual processor machine?


Dear Shridhar,

ok, so my system has got 362 MB of free RAM currently... this sounds good.

uname -a says:
Linux db2.city-map.de 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown

I didn't actually measure requests per minute through a longer period...
I tested it 2 hours ago using debug and logging all queries, and I saw
approx. 2500 requests per minute. but at that time of the day there are
only about 25 simultaneous users on our website. so i calculated 50
users and 5.000 rpm for average daytime usage. I guess the maximum peak
would be approx. 10.000 queries per minute.

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 13, 2002 11:00 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On 13 Nov 2002 at 10:42, Henrik Steffen wrote:
> > > Yes. 2*max connection is minimum. Anything additional is always
welcome as long
> > > as it does not starve the system.
> >
> > ok, I tried to set shared_buffers to 65535 now. but then restarting
postgres
> > fails - it says:
> >
> > IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed:
Invalid argument
> >
> > and a message telling me to either lower the shared_buffers or raise the
> > SHMMAX.
>
> Yes. you need to raise SHMMAX. A good feature of recent linux distro. is
that
> they set SHMMAX to half of physical memory. A very good default IMO..
>
> >  11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
> > 53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
> > CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
> > Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K
buff
> > Swap: 1028112K av,      60K used, 1028052K free                  849776K
cached
> > I have now changed the SHMMAX settings to 545333248 and changed the
> > shared_buffers to 65535 again. now postgres starts up correctly.
> >
> > the top result changes to:
> >
> >  11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
> > 57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
> > CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
> > Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K
buff
> > Swap: 1028112K av,      60K used, 1028052K free                  338376K
cached
> > now, does this look better in your eyes?
>
> Well, don't look at top to find out free memoy. Use free. On my machine..
>
> [shridhar@perth shridhar]$ free
>              total       used       free     shared    buffers     cached
> Mem:        255828     250676       5152          0      66564      29604
> -/+ buffers/cache:     154508     101320
> Swap:       401616      12764     388852
> [shridhar@perth shridhar]$
>
> Here the important value is second value in second line, 101320. That's
true
> free memory. Remeber when system needs memory, it can always shrunk
> cache/buffers. In both of your stats, cache+memory are roughly 400MB.
>
> Relax, your system is not starving for memory...
>
> > do you need more information here?
>
> Not for this problem, but just curious. What does uname -a says?
>
> Secondly just curious, with 5000 requests per minute, what is the peak
number
> of connection you are getting? You should look int pooling parameters for
> better performance..
>
> HTH
>
>
> Bye
>  Shridhar
>
> --
> Hawkeye's Conclusion: It's not easy to play the clown when you've got to
run
> the whole circus.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Upgrade to dual processor machine?

From

Doug McNaught

Date:

13 November 2002, 11:52:33

"Henrik Steffen" <steffen@city-map.de> writes:

> > hmm.. Some sort of RedHat I assume. Upgrade the kernel at least. Any variant of
> > 2.4.19.x should give you at least 10-15% performance increase. Besides that
> > will cure the linux VM fiascos as well..
>
> redhat, right. Ok, I will have this tested.

If you don't want to roll your own, I think RH has a newer errata
kernel on updates.redhat.com that you can install as an RPM.

-Doug

Re: Upgrade to dual processor machine?

From

Csaba Nagy

Date:

13 November 2002, 11:57:10

No, I did not try a portscan. But speaking for myself: I would feel
unconfortable to publish the internet accessible IP address of a database
machine. Uff.

Cheers,
Csaba.

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Henrik Steffen
Gesendet: Mittwoch, 13. November 2002 14:46
An: Csaba Nagy
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Upgrade to dual processor machine?


did you also try a portscan?

the server IS behind a firewall and very well protected.
there's just the ping allowed, nothing else...


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Csaba Nagy" <nagy@domeus.de>
To: "'Henrik Steffen'" <steffen@city-map.de>
Sent: Wednesday, November 13, 2002 1:46 PM
Subject: AW: [GENERAL] Upgrade to dual processor machine?


Your database's IP address can be pinged from the internet... is this
deliberate ? The database should be behind your firewall.
You just published the IP to the Postgres list... don't keep sensitive data
there I would say.

PING wird ausgeführt für db2.city-map.de [62.116.172.165] mit 32 Bytes
Daten:

Antwort von 62.116.172.165: Bytes=32 Zeit=31ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=10ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=10ms TTL=242
Antwort von 62.116.172.165: Bytes=32 Zeit=20ms TTL=242

PING wird ausgeführt für www.city-map.de [62.116.172.170] mit 32 Bytes
Daten:

Antwort von 62.116.172.170: Bytes=32 Zeit=20ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=20ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=21ms TTL=242
Antwort von 62.116.172.170: Bytes=32 Zeit=10ms TTL=242

Cheers,
Csaba.

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Henrik Steffen
Gesendet: Mittwoch, 13. November 2002 11:55
An: shridhar_daithankar@persistent.co.in
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Upgrade to dual processor machine?


Dear Shridhar,

ok, so my system has got 362 MB of free RAM currently... this sounds good.

uname -a says:
Linux db2.city-map.de 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown

I didn't actually measure requests per minute through a longer period...
I tested it 2 hours ago using debug and logging all queries, and I saw
approx. 2500 requests per minute. but at that time of the day there are
only about 25 simultaneous users on our website. so i calculated 50
users and 5.000 rpm for average daytime usage. I guess the maximum peak
would be approx. 10.000 queries per minute.

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 13, 2002 11:00 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On 13 Nov 2002 at 10:42, Henrik Steffen wrote:
> > > Yes. 2*max connection is minimum. Anything additional is always
welcome as long
> > > as it does not starve the system.
> >
> > ok, I tried to set shared_buffers to 65535 now. but then restarting
postgres
> > fails - it says:
> >
> > IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed:
Invalid argument
> >
> > and a message telling me to either lower the shared_buffers or raise the
> > SHMMAX.
>
> Yes. you need to raise SHMMAX. A good feature of recent linux distro. is
that
> they set SHMMAX to half of physical memory. A very good default IMO..
>
> >  11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
> > 53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
> > CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
> > Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K
buff
> > Swap: 1028112K av,      60K used, 1028052K free                  849776K
cached
> > I have now changed the SHMMAX settings to 545333248 and changed the
> > shared_buffers to 65535 again. now postgres starts up correctly.
> >
> > the top result changes to:
> >
> >  11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
> > 57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
> > CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
> > Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K
buff
> > Swap: 1028112K av,      60K used, 1028052K free                  338376K
cached
> > now, does this look better in your eyes?
>
> Well, don't look at top to find out free memoy. Use free. On my machine..
>
> [shridhar@perth shridhar]$ free
>              total       used       free     shared    buffers     cached
> Mem:        255828     250676       5152          0      66564      29604
> -/+ buffers/cache:     154508     101320
> Swap:       401616      12764     388852
> [shridhar@perth shridhar]$
>
> Here the important value is second value in second line, 101320. That's
true
> free memory. Remeber when system needs memory, it can always shrunk
> cache/buffers. In both of your stats, cache+memory are roughly 400MB.
>
> Relax, your system is not starving for memory...
>
> > do you need more information here?
>
> Not for this problem, but just curious. What does uname -a says?
>
> Secondly just curious, with 5000 requests per minute, what is the peak
number
> of connection you are getting? You should look int pooling parameters for
> better performance..
>
> HTH
>
>
> Bye
>  Shridhar
>
> --
> Hawkeye's Conclusion: It's not easy to play the clown when you've got to
run
> the whole circus.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Upgrade to dual processor machine?

From

Tom Lane

Date:

13 November 2002, 12:11:40

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> 3) Sort mem is a tricky affair. AFAIU, it is used only when you create
> index or sort results of a query. If do these things seldomly, you can
> set this very low or default.

I think this is bad advice.  Sort memory is only consumed when needed,
so there's no advantage in decreasing the setting just because you think
a particular client process isn't going to need to sort.  All you will
accomplish is to pessimize your performance if a sort does happen to be
needed.

You do need to set the installation default on the basis of thinking
about what will happen if all backends are trying to sort at once.
But having done that, you should be able to increase the setting in
individual sessions that you know are going to do large sorts.

The default setting (1024K) is, like most of the default settings
in PG, on the small side IMHO.

I don't care for advice that leads to allocating half of physical RAM to
PG's shared buffers, either.  This ignores the fact that the kernel's
disk caches are nearly as effective as PG's internal buffers, and much
more flexible (because the kernel can decrease the size of its caches
when there's heavy memory pressure from processes).  I'd start with a
few thousand shared buffers and let the kernel consume the bulk of RAM
with its buffering.  That approach lets you use a higher sort_mem
setting, too.

            regards, tom lane

Re: Upgrade to dual processor machine?

From

"Josh Berkus"

Date:

13 November 2002, 15:02:11

Henrik,

First off, I'm moving this discussion to the PGSQL-PERFORMANCE list,
where it belongs.   To subscribe, send the message "subscribe
pgsql-perform your@email.address" to "majordomo@postgresql.org".

> This is was I figured out now:
>
> 1) RAM available: 1024 MB, there's nothing else but postgres on this
>    machine, so if I calculate 128 MB for Linux, there are 896 MB left
>    for Postgres.
>
> 2) 70 % of 896 MB is 627 MB
>
> Now, if I follow your instructions:
>
> 250K +
> 8.2K * 128 (shared_buffers) = 1049,6K +
> 14.2K * 64 (max_connections) = 908,8K +
> 1024K * 5000 (average number of requests per minute) = 5120000K
> ===============================================================
> 5122208.4K ==> 5002.16 MB
>
> this is a little bit more than I have available, isn't it? :(((
>
> sure that this has got to be the "average number of requests per
> minute"
> and not "per second" ? seems so much, doesn't it?
>
> what am I supposed to do now?

Well, now it gets more complicated.   You need to determine:
A) The median processing time of each of those requests.
B) The amount of Sort_mem actually required for each request.

I reccommend "per minute" because that's an easy over-estimate ... few
requests last a full minute, and as a result
average-requests-per-minute gives you a safe guage of maximum
concurrent requests (in transactional database environments), which is
really what we are trying to determine.

Um, you do know that I'm talking about *database* requests -- that is,
queries -- and not web page requests, yes?  If you're using server-side
caching, there can be a *huge* difference.

If you have 5000 requests per minute, and only 64 connections, then I
can hypothesize that:
1) you are doing some kind of connection pooling;
2) those are exclusively *read-only* requests;
3) those are very simple requests, or at least processed very quickly.

If all of the above is true, then you can probably base you calculation
on requests-per-second, rather than requests-per-minute.

Then, of course, it becomes an interactive process.  You change the
settings, re-start the database server, and watch the memory used by
the postgreSQL processes.   Your goal is to have that memory usage
hover around 700mb during heavy usage periods (any less, and you are
throttling the database through scarcity of RAM)  but to never, ever,
force usage of Swap memory, which will slow down the server 10-fold.

If you see the RAM only at half that, but the processor at 90%+, then
you should consider upgrading your processor.  But you're more likely
to run out of RAM first.    I believe that you haven't already because
with your low shared-buffer settings, most of the potential sort_mem is
going unused.

BTW, if you are *really* getting 5000 queries per minute, I would
strongly reccomend doubling your RAM.

-Josh Berkus


>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: <pgsql-general@postgresql.org>
> Cc: <steffen@city-map.de>
> Sent: Tuesday, November 12, 2002 9:05 PM
> Subject: Re: Upgrade to dual processor machine?
>
>
> Heinrik,
>
> "So, where do i find and change shmmax shmall settings ??
> What should I put there?
>
> What is a recommended value for shared buffers in postgresql.conf ?"
>
> There is no "recommended value."   You have to calculate this
> relatively:
>
> 1) Figure out how much RAM your server has available for PostgreSQL.
>  For
> example, I have one server on which I allocate 256 mb for Apache, 128
> mb for
> linux, and thus have 512mb available for Postgres.
>
> 2) Calculate out the memory settings to use 70% of that amount of Ram
> in
> regular usage.   Please beware that sort_mem is *not* shared, meaning
> that it
> will be multiplied by the number of concurrent requests requiring
> sorting.
> Thus, your calculation (in K) should be:
>
> 250K  +
> 8.2K * shared_buffers +
> 14.2K * max_connections +
> sort_mem * average number of requests per minute
> =====================================
> memory available to postgresql in K * 0.7
>
> You will also have to set SHMMAX and SHMMALL to accept this memory
> allocation.
> Since shmmax is set in bytes, then I generally feel safe making it:
> 1024 * 0.5 * memory available to postgresql in K
>
> Setting them is done simply:
> $ echo 134217728 >/proc/sys/kernel/shmall
> $ echo 134217728 >/proc/sys/kernel/shmmax
>
> This is all taken from the postgresql documentation, with some
> experience:
>
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco

Re: Upgrade to dual processor machine?

From

"Josh Berkus"

Date:

13 November 2002, 15:18:23

Henrik

Oops!   Two corrections, below.   Sorry about the typos.

> First off, I'm moving this discussion to the PGSQL-PERFORMANCE list,
> where it belongs.   To subscribe, send the message "subscribe
> pgsql-perform your@email.address" to "majordomo@postgresql.org".

Sorry ... thats "subscribe pgsql-performance your@email.address".

> Then, of course, it becomes an interactive process.  You change the
> settings, re-start the database server, and watch the memory used by
> the postgreSQL processes.   Your goal is to have that memory usage
> hover around 700mb during heavy usage periods (any less, and you are

That's "600mb", not "700mb".

I also just read Tom's response regarding reserving more RAM for kernel
buffering.   This hasn't been my experience, but then I work mostly
with transactional databases (many read-write requests) rather than
read-only databases.

As such, I'd be interested in a test:  Calculate out your PostgreSQL
RAM to total, say, 256mb and run a speed test on the database.   Then
calculate it out to the previous 600mb, and do the same.   I'd like to
know the results.

In fact, e-mail me off list if you want further help -- I'm interested
in the outcome.

-Josh Berkus

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

13 November 2002, 15:33:34

On Wed, 13 Nov 2002, Henrik Steffen wrote:

>
> Hello Shridhar,
>
> thanks for your answer...
>
> 1) in the docs it says: shared_buffers should be 2*max_connections, min 16.
> now, you suggest to put it to 500-600 MB, which means I will have to
> increase shared_buffers to 68683 -- is this really correct? I mean,
> RAM is allready now almost totally consumed.

Actually, that's not quite correct.  The RAM is already showing as being
in use, but it's being used by the kernel as file cache, and will be
released the second a process asks for more memory, so it really isn't "in
use" in the classic sense.

> 2) the database has a size of 3.6 GB at the moment... about 100 user tables.
>
> 3) ok, I understand: I am not creating any indexes usually. Only once at night
> all user indexes are dropped and recreated, I could imagine to increase the
> sort_mem for this script... so sort_mem with 1024K is ok, or should it be
> lowered to, say, 512K ?

Generally a sort mem of 8 meg or less is pretty safe, as the allocation is
only made WHILE the sort is running and is released right after.  The
danger is that if it is set higher, like say 32 or 64 meg, and a dozen or
so sql statements just happen to all sort at the same time, you can run
out of memory and have a "swap storm" where the machine is swapping out
processes one after the other to give each the amount of swap space it
needs.  Note also that a SQL query with more than one sort in it will use
up to sort_mem for each sort independently, so a dozen SQL queries that
each require say three sorts all running at once can theoretically use
36*sort_mem amount of memory.  Once the machine starts swapping for
sort_mem, things get slow VERY fast.  It's one of those knees you don't
want to hit.

Re: Upgrade to dual processor machine?

From

Martijn van Oosterhout

Date:

14 November 2002, 03:50:58

On Wed, Nov 13, 2002 at 10:18:19AM -0700, scott.marlowe wrote:
> Generally a sort mem of 8 meg or less is pretty safe, as the allocation is
> only made WHILE the sort is running and is released right after.  The
> danger is that if it is set higher, like say 32 or 64 meg, and a dozen or
> so sql statements just happen to all sort at the same time, you can run
> out of memory and have a "swap storm" where the machine is swapping out
> processes one after the other to give each the amount of swap space it
> needs.  Note also that a SQL query with more than one sort in it will use
> up to sort_mem for each sort independently, so a dozen SQL queries that
> each require say three sorts all running at once can theoretically use
> 36*sort_mem amount of memory.  Once the machine starts swapping for
> sort_mem, things get slow VERY fast.  It's one of those knees you don't
> want to hit.

Something I havn't seen mentioned yet is the very useful program "vmstat".
It gives you a quick summary of how many blocks are being copied to and from
disk, whether and how much you are swapping, how many processes are actually
running and sleeping at any one time. You can tell it you give you an
average over any period of time (like a minute or an hour). Example:

# vmstat 1
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 1  0  0  29728  47584   1128  63048   3   2    17    10   21    38   9   2  35
 1  0  0  29728  47584   1128  63048   0   0     0     0 1539   356   6   0  94
 1  0  0  29728  47552   1128  63080   0   0    32     0 1542   354   5   1  94
 1  0  0  29728  47552   1128  63080   0   0     0     0 1551   355   6   0  94
 1  0  0  29728  47520   1128  63112   0   0    32     0 1542   361   5   2  93

As you can see, not a terribly loaded machine :)

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> We place no reliance  On Virgin or Pigeon;
> Our method is Science, Our aim is Religion.  -  Aleister Crowley

Attachment

msg-32091-50154.dat

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 07:28:51

ok, now i subscribed to performance, too ;-)

> Well, now it gets more complicated.   You need to determine:
> A) The median processing time of each of those requests.
> B) The amount of Sort_mem actually required for each request.

as I am dealing with postgres for a webserver the median processing
time of each request has got to be <1 sec. how can i measure
the amount of sort_mem needed?

at the highest there are perhaps 20 concurrent database requests
at the same time. i have enabled 64 maximum connections, because
i have apache configured to use persistent database connections
using mod_perl and pg.pm. I set Apache to run MaxClients at 40
(there could additionally be some manual psql connections)

> Um, you do know that I'm talking about *database* requests -- that is,
> queries -- and not web page requests, yes?  If you're using server-side
> caching, there can be a *huge* difference.

yes, I did understand that. And when I measured 5.000 requests per minute
I looked at the pgsql.log (after enabling the debug options and counting
all the queries within minute). so, server-side caching does not appear
within these 5.000 requests... that's for sure.

> If you have 5000 requests per minute, and only 64 connections, then I
> can hypothesize that:
> 1) you are doing some kind of connection pooling;
> 2) those are exclusively *read-only* requests;
> 3) those are very simple requests, or at least processed very quickly

1) correct
2) no, not exclusively - but as it's a webserver-application (www.city-map.de)
most users just read from the database, while they always do an update
to raise some statistics (page-views counters etc.) - furthermore, there is
an internal content-management system where about 100 editors do inserts and
updates. but there are of course more visitors (>10.000 daily) than editors.
3) yes, many requests are very simple for better performance in a web-application

Swapping does never happen so far.


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Henrik Steffen" <steffen@city-map.de>; <josh@agliodbs.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-performance@postgresql.org>
Sent: Wednesday, November 13, 2002 6:05 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> Henrik,
>
> First off, I'm moving this discussion to the PGSQL-PERFORMANCE list,
> where it belongs.   To subscribe, send the message "subscribe
> pgsql-perform your@email.address" to "majordomo@postgresql.org".
>
> > This is was I figured out now:
> >
> > 1) RAM available: 1024 MB, there's nothing else but postgres on this
> >    machine, so if I calculate 128 MB for Linux, there are 896 MB left
> >    for Postgres.
> >
> > 2) 70 % of 896 MB is 627 MB
> >
> > Now, if I follow your instructions:
> >
> > 250K +
> > 8.2K * 128 (shared_buffers) = 1049,6K +
> > 14.2K * 64 (max_connections) = 908,8K +
> > 1024K * 5000 (average number of requests per minute) = 5120000K
> > ===============================================================
> > 5122208.4K ==> 5002.16 MB
> >
> > this is a little bit more than I have available, isn't it? :(((
> >
> > sure that this has got to be the "average number of requests per
> > minute"
> > and not "per second" ? seems so much, doesn't it?
> >
> > what am I supposed to do now?
>
> Well, now it gets more complicated.   You need to determine:
> A) The median processing time of each of those requests.
> B) The amount of Sort_mem actually required for each request.
>
> I reccommend "per minute" because that's an easy over-estimate ... few
> requests last a full minute, and as a result
> average-requests-per-minute gives you a safe guage of maximum
> concurrent requests (in transactional database environments), which is
> really what we are trying to determine.
>
> Um, you do know that I'm talking about *database* requests -- that is,
> queries -- and not web page requests, yes?  If you're using server-side
> caching, there can be a *huge* difference.
>
> If you have 5000 requests per minute, and only 64 connections, then I
> can hypothesize that:
> 1) you are doing some kind of connection pooling;
> 2) those are exclusively *read-only* requests;
> 3) those are very simple requests, or at least processed very quickly.
>
> If all of the above is true, then you can probably base you calculation
> on requests-per-second, rather than requests-per-minute.
>
> Then, of course, it becomes an interactive process.  You change the
> settings, re-start the database server, and watch the memory used by
> the postgreSQL processes.   Your goal is to have that memory usage
> hover around 700mb during heavy usage periods (any less, and you are
> throttling the database through scarcity of RAM)  but to never, ever,
> force usage of Swap memory, which will slow down the server 10-fold.
>
> If you see the RAM only at half that, but the processor at 90%+, then
> you should consider upgrading your processor.  But you're more likely
> to run out of RAM first.    I believe that you haven't already because
> with your low shared-buffer settings, most of the potential sort_mem is
> going unused.
>
> BTW, if you are *really* getting 5000 queries per minute, I would
> strongly reccomend doubling your RAM.
>
> -Josh Berkus
>
>
> >
> > ----- Original Message -----
> > From: "Josh Berkus" <josh@agliodbs.com>
> > To: <pgsql-general@postgresql.org>
> > Cc: <steffen@city-map.de>
> > Sent: Tuesday, November 12, 2002 9:05 PM
> > Subject: Re: Upgrade to dual processor machine?
> >
> >
> > Heinrik,
> >
> > "So, where do i find and change shmmax shmall settings ??
> > What should I put there?
> >
> > What is a recommended value for shared buffers in postgresql.conf ?"
> >
> > There is no "recommended value."   You have to calculate this
> > relatively:
> >
> > 1) Figure out how much RAM your server has available for PostgreSQL.
> >  For
> > example, I have one server on which I allocate 256 mb for Apache, 128
> > mb for
> > linux, and thus have 512mb available for Postgres.
> >
> > 2) Calculate out the memory settings to use 70% of that amount of Ram
> > in
> > regular usage.   Please beware that sort_mem is *not* shared, meaning
> > that it
> > will be multiplied by the number of concurrent requests requiring
> > sorting.
> > Thus, your calculation (in K) should be:
> >
> > 250K  +
> > 8.2K * shared_buffers +
> > 14.2K * max_connections +
> > sort_mem * average number of requests per minute
> > =====================================
> > memory available to postgresql in K * 0.7
> >
> > You will also have to set SHMMAX and SHMMALL to accept this memory
> > allocation.
> > Since shmmax is set in bytes, then I generally feel safe making it:
> > 1024 * 0.5 * memory available to postgresql in K
> >
> > Setting them is done simply:
> > $ echo 134217728 >/proc/sys/kernel/shmall
> > $ echo 134217728 >/proc/sys/kernel/shmmax
> >
> > This is all taken from the postgresql documentation, with some
> > experience:
> >
> http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html
> >
> > --
> > -Josh Berkus
> >  Aglio Database Solutions
> >  San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 07:41:48

hi,

this is what my vmstat 1 5 looks like --- cute tool, didn't know it yet - thanks!

  procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 2  1  1     60   4940  10288 344212   0   0   158    74   14    31  25   9  66
 0  3  1     60   4940  10428 343680   0   0  6548   280  500   595  14  10  76
 0  5  1     60   4940  10488 343148   0   0  7732   180  658   983  14  12  74
 0  4  1     60   4964  10540 344536   0   0  6364   268  513   715  11   5  84
 0  4  1     60   4964  10588 344056   0   0  5180   360  578   610  21   6  73

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Martijn van Oosterhout" <kleptog@svana.org>
To: "scott.marlowe" <scott.marlowe@ihs.com>
Cc: "Henrik Steffen" <steffen@city-map.de>; <shridhar_daithankar@persistent.co.in>; <pgsql-general@postgresql.org>
Sent: Thursday, November 14, 2002 6:45 AM
Subject: Re: [GENERAL] Upgrade to dual processor machine?

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 08:04:25

Hi all,

this is how it looks like, when my system is busy (right now!!!)

50 concurrent visitors at the same time surfing through our web-pages

ps ax | grep postgres:
22568 ?        S      0:00 postgres: stats buffer process
22569 ?        S      0:00 postgres: stats collector process
22577 ?        S      0:15 postgres: postgres kunden 62.116.172.180 INSERT
22578 ?        S      0:19 postgres: postgres kunden 62.116.172.180 UPDATE
22582 ?        S      0:14 postgres: postgres kunden 62.116.172.180 idle
22583 ?        S      0:30 postgres: postgres kunden 62.116.172.180 idle
22584 ?        S      0:19 postgres: postgres kunden 62.116.172.180 idle
22586 ?        S      0:17 postgres: postgres kunden 62.116.172.180 idle
22587 ?        S      0:15 postgres: postgres kunden 62.116.172.180 idle
22588 ?        S      0:20 postgres: postgres kunden 62.116.172.180 INSERT
22590 ?        S      0:15 postgres: postgres kunden 62.116.172.180 INSERT
22592 ?        S      0:18 postgres: postgres kunden 62.116.172.180 INSERT
22593 ?        S      0:15 postgres: postgres kunden 62.116.172.180 idle
22594 ?        S      0:19 postgres: postgres kunden 62.116.172.180 UPDATE
22601 ?        D      0:22 postgres: postgres kunden 62.116.172.180 SELECT
22643 ?        S      0:14 postgres: postgres kunden 62.116.172.180 idle
22730 ?        D      0:10 postgres: postgres kunden 62.116.172.180 SELECT
22734 ?        D      0:08 postgres: postgres kunden 62.116.172.180 SELECT
22753 ?        S      0:10 postgres: postgres kunden 62.116.172.180 SELECT
22754 ?        S      0:05 postgres: postgres kunden 62.116.172.180 idle
22755 ?        S      0:02 postgres: postgres kunden 62.116.172.180 idle
22756 ?        S      0:02 postgres: postgres kunden 62.116.172.180 idle
22762 ?        S      0:05 postgres: postgres kunden 62.116.172.180 UPDATE
22764 ?        D      0:04 postgres: postgres kunden 62.116.172.180 SELECT
22765 ?        S      0:02 postgres: postgres kunden 62.116.172.180 UPDATE
22766 ?        D      0:02 postgres: postgres kunden 62.116.172.180 SELECT
22787 ?        S      0:02 postgres: postgres kunden 62.116.172.180 idle
22796 ?        S      0:00 postgres: postgres kunden 62.116.172.180 UPDATE
22803 ?        S      0:00 postgres: postgres kunden 62.116.172.180 idle
22804 ?        S      0:01 postgres: postgres kunden 62.116.172.180 idle
22805 ?        S      0:01 postgres: postgres kunden 62.116.172.180 idle
22806 ?        S      0:00 postgres: postgres kunden 62.116.172.180 idle
22807 ?        S      0:00 postgres: postgres kunden 62.116.172.180 idle
22809 ?        D      0:00 postgres: postgres kunden 62.116.172.180 SELECT
22814 ?        S      0:01 postgres: postgres kunden 62.116.172.180 idle
22815 ?        D      0:03 postgres: postgres kunden 62.116.172.180 SELECT
22818 ?        S      0:00 postgres: postgres kunden 62.116.172.180 idle
22821 ?        S      0:00 postgres: postgres kunden 62.116.172.180 idle
22824 ?        S      0:01 postgres: postgres kunden 62.116.172.180 UPDATE
22825 ?        S      0:00 postgres: postgres kunden 62.116.172.180 UPDATE
22829 ?        S      0:00 postgres: checkpoint subprocess
22830 ?        S      0:00 postgres: postgres kunden 62.116.172.180 INSERT
22832 pts/0    S      0:00 grep postgres

-> I count 20 concurrent database queries above ...

vmstat 1 5:
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 1  8  1     60   4964   5888 309684   0   0   176    74   16    32  25   9  66
 0  6  3     60   4964   5932 308772   0   0  6264   256  347   347  13   9  78
 0  5  1     60   4964   5900 309364   0   0  9312   224  380   309  11   6  83
 1  4  1     60   5272   5940 309152   0   0 10320   116  397   429  17   6  77
 1  4  1     60   4964   5896 309512   0   0 11020   152  451   456  14  10  76


free:
             total       used       free     shared    buffers     cached
Mem:       1020808    1015860       4948     531424       5972     309548
-/+ buffers/cache:     700340     320468
Swap:      1028112         60    1028052


w:
12:04pm  up 2 days, 17:44,  1 user,  load average: 10.28, 7.22, 3.88
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU  WHAT
root     pts/0    condor.city-map. 11:46am  0.00s  0.09s  0.01s  w


this is when things begin to go more slowly....

any advice?


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 13:21:30

hi Ivan

to get the shared buffer memory I followed all the instructions
I gathered here on the list within the last two days. the kernel
settings SHMMAX etc. were important here in my opinion... you could
search the archives for all the other mails within this thread and
try yourself.

by the way: today we update to kernel 2.4.19 and we measured BIG
performance gains! however, since the upgrade 'top' doesn't show any
shared memory in the summary any longer... yet for every process
it lists a certain amount of shared mem... is this a kernel/top issue
or did I miss something here?

the kernel is much more performant!

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "pginfo" <pginfo@t1.unisoftbg.com>
To: "Henrik Steffen" <steffen@city-map.de>
Sent: Thursday, November 14, 2002 3:15 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> Hi,
> Sorry for this question.
> I see you have 8520K shrd .
> How are you setup you linux box to use tis shared buffers? (any answer will be great).
>
>
>
> I have tryed it many times without success.
>
> Also it is courios that the sum of cpu loading is not 100% !
>
> We are using two pg servers:
> one single processor Intel 1 GHz, 1 GB RAM,
> and one dual Intel 1GHz, 1.5 GB RAM.
> It exist big diference in pg performance and I noticed many times when the system use
> all two processors.
>
> If I can help you with more info you are free to ask.
>
> regards,
> Ivan.
>
>
> Henrik Steffen wrote:
>
> > dear shridhar,
> >
> > > Yes. 2*max connection is minimum. Anything additional is always welcome as long
> > > as it does not starve the system.
> >
> > ok, I tried to set shared_buffers to 65535 now. but then restarting postgres
> > fails - it says:
> >
> > IpcMemoryCreate: shmget(key=5432001, size=545333248, 03600) failed: Invalid argument
> >
> > and a message telling me to either lower the shared_buffers or raise the
> > SHMMAX.
> >
> > > If you have a gig of memory and shared buffers are 536MB as you have indicated,
> > > who is taking rest of the RAM?
> >
> > well, I guess it's postgres... see the output of top below:
> >
> >  11:06am  up 1 day, 16:46,  1 user,  load average: 1,32, 1,12, 1,22
> > 53 processes: 52 sleeping, 1 running, 0 zombie, 0 stopped
> > CPU states: 24,5% user, 11,2% system,  0,0% nice,  5,6% idle
> > Mem:  1020808K av, 1006156K used,   14652K free,    8520K shrd,   37204K buff
> > Swap: 1028112K av,      60K used, 1028052K free                  849776K cached
> >
> >   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
> > 10678 root      19   0  2184 2184  1584 S     2,9  0,2   0:00 sendmail
> >     1 root       8   0   520  520   452 S     0,0  0,0   0:03 init
> >     2 root       9   0     0    0     0 SW    0,0  0,0   0:00 keventd
> >     3 root       9   0     0    0     0 SW    0,0  0,0   0:00 kapm-idled
> >     4 root      19  19     0    0     0 SWN   0,0  0,0   0:00 ksoftirqd_CPU0
> >     5 root       9   0     0    0     0 SW    0,0  0,0   0:28 kswapd
> >     6 root       9   0     0    0     0 SW    0,0  0,0   0:00 kreclaimd
> >     7 root       9   0     0    0     0 SW    0,0  0,0   0:09 bdflush
> >     8 root       9   0     0    0     0 SW    0,0  0,0   0:00 kupdated
> >     9 root      -1 -20     0    0     0 SW<   0,0  0,0   0:00 mdrecoveryd
> >    13 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   136 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   137 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   138 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   139 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   140 root       9   0     0    0     0 SW    0,0  0,0   2:16 kjournald
> >   378 root       9   0     0    0     0 SW    0,0  0,0   0:00 eth0
> >   454 root       9   0   572  572   476 S     0,0  0,0   0:00 syslogd
> >   459 root       9   0  1044 1044   392 S     0,0  0,1   0:00 klogd
> >   572 root       8   0  1128 1092   968 S     0,0  0,1   0:07 sshd
> >   584 root       9   0  1056 1056   848 S     0,0  0,1   0:02 nlservd
> >   611 root       8   0  1836 1820  1288 S     0,0  0,1   0:00 sendmail
> >   693 root       9   0   640  640   556 S     0,0  0,0   0:00 crond
> >   729 daemon     9   0   472  464   404 S     0,0  0,0   0:00 atd
> >   736 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   737 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   738 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   739 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   740 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   741 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >  9800 root       9   0  1888 1864  1552 S     0,0  0,1   0:02 sshd
> >  9801 root      16   0  1368 1368  1016 S     0,0  0,1   0:00 bash
> > 10574 postgres   0   0  1448 1448  1380 S     0,0  0,1   0:00 postmaster
> > 10576 postgres   9   0  1436 1436  1388 S     0,0  0,1   0:00 postmaster
> > 10577 postgres   9   0  1480 1480  1388 S     0,0  0,1   0:00 postmaster
> > 10579 postgres  14   0 11500  11M 10324 S     0,0  1,1   0:08 postmaster
> > 10580 postgres   9   0 11672  11M 10328 S     0,0  1,1   0:03 postmaster
> > 10581 postgres  14   0 11620  11M 10352 S     0,0  1,1   0:08 postmaster
> > 10585 postgres  11   0 11560  11M 10304 S     0,0  1,1   0:08 postmaster
> > 10588 postgres   9   0 11520  11M 10316 S     0,0  1,1   0:14 postmaster
> > 10589 postgres   9   0 11632  11M 10324 S     0,0  1,1   0:06 postmaster
> > 10590 postgres  10   0 11620  11M 10320 S     0,0  1,1   0:06 postmaster
> > 10591 postgres   9   0 11536  11M 10320 S     0,0  1,1   0:08 postmaster
> > 10592 postgres  11   0 11508  11M 10316 S     0,0  1,1   0:04 postmaster
> > 10595 postgres   9   0 11644  11M 10324 S     0,0  1,1   0:03 postmaster
> > 10596 postgres  11   0 11664  11M 10328 S     0,0  1,1   0:08 postmaster
> > 10597 postgres   9   0 11736  11M 10340 S     0,0  1,1   0:24 postmaster
> > 10598 postgres   9   0 11500  11M 10312 S     0,0  1,1   0:10 postmaster
> > 10599 postgres  11   0 11676  11M 10324 S     0,0  1,1   0:13 postmaster
> > 10602 postgres   9   0 11476  11M 10308 S     0,0  1,1   0:09 postmaster
> > 10652 postgres   9   0  7840 7840  7020 S     0,0  0,7   0:00 postmaster
> > 10669 postgres   9   0  9076 9076  8224 S     0,0  0,8   0:00 postmaster
> > 10677 root      13   0  1032 1028   828 R     0,0  0,1   0:00 top
> >
> > I have now changed the SHMMAX settings to 545333248 and changed the
> > shared_buffers to 65535 again. now postgres starts up correctly.
> >
> > the top result changes to:
> >
> >  11:40am  up 1 day, 17:20,  1 user,  load average: 2,24, 2,51, 2,14
> > 57 processes: 55 sleeping, 2 running, 0 zombie, 0 stopped
> > CPU states: 24,7% user, 11,3% system,  0,0% nice,  6,2% idle
> > Mem:  1020808K av, 1015844K used,    4964K free,  531420K shrd,   24796K buff
> > Swap: 1028112K av,      60K used, 1028052K free                  338376K cached
> >
> >   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
> > 11010 root      17   0  1036 1032   828 R    14,2  0,1   0:00 top
> > 11007 postgres  14   0 14268  13M 12668 R     9,7  1,3   0:00 postmaster
> > 11011 root       9   0  2184 2184  1584 S     3,0  0,2   0:00 sendmail
> >     1 root       8   0   520  520   452 S     0,0  0,0   0:03 init
> >     2 root       9   0     0    0     0 SW    0,0  0,0   0:00 keventd
> >     3 root       9   0     0    0     0 SW    0,0  0,0   0:00 kapm-idled
> >     4 root      19  19     0    0     0 SWN   0,0  0,0   0:00 ksoftirqd_CPU0
> >     5 root       9   0     0    0     0 SW    0,0  0,0   0:29 kswapd
> >     6 root       9   0     0    0     0 SW    0,0  0,0   0:00 kreclaimd
> >     7 root       9   0     0    0     0 SW    0,0  0,0   0:09 bdflush
> >     8 root       9   0     0    0     0 SW    0,0  0,0   0:00 kupdated
> >     9 root      -1 -20     0    0     0 SW<   0,0  0,0   0:00 mdrecoveryd
> >    13 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   136 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   137 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   138 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   139 root       9   0     0    0     0 SW    0,0  0,0   0:00 kjournald
> >   140 root       9   0     0    0     0 SW    0,0  0,0   2:18 kjournald
> >   378 root       9   0     0    0     0 SW    0,0  0,0   0:00 eth0
> >   454 root       9   0   572  572   476 S     0,0  0,0   0:00 syslogd
> >   459 root       9   0  1044 1044   392 S     0,0  0,1   0:00 klogd
> >   572 root       8   0  1128 1092   968 S     0,0  0,1   0:07 sshd
> >   584 root       9   0  1056 1056   848 S     0,0  0,1   0:02 nlservd
> >   611 root       9   0  1836 1820  1288 S     0,0  0,1   0:00 sendmail
> >   693 root       9   0   640  640   556 S     0,0  0,0   0:00 crond
> >   729 daemon     9   0   472  464   404 S     0,0  0,0   0:00 atd
> >   736 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   737 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   738 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   739 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   740 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >   741 root       9   0   448  448   384 S     0,0  0,0   0:00 mingetty
> >  9800 root       9   0  1888 1864  1552 S     0,0  0,1   0:03 sshd
> >  9801 root      10   0  1368 1368  1016 S     0,0  0,1   0:00 bash
> > 10838 postgres   7   0  6992 6992  6924 S     0,0  0,6   0:00 postmaster
> > 10840 postgres   9   0  6984 6984  6932 S     0,0  0,6   0:00 postmaster
> > 10841 postgres   9   0  7024 7024  6932 S     0,0  0,6   0:00 postmaster
> > 10852 postgres   9   0  489M 489M  487M S     0,0 49,0   0:32 postmaster
> > 10869 postgres   9   0  357M 357M  356M S     0,0 35,8   0:21 postmaster
> > 10908 postgres   9   0  263M 263M  262M S     0,0 26,4   0:20 postmaster
> > 10909 postgres   9   0  283M 283M  281M S     0,0 28,4   0:19 postmaster
> > 10932 postgres   9   0  288M 288M  286M S     0,0 28,9   0:13 postmaster
> > 10946 postgres   9   0  213M 213M  211M S     0,0 21,4   0:06 postmaster
> > 10947 postgres   9   0  239M 239M  238M S     0,0 24,0   0:07 postmaster
> > 10948 postgres   9   0  292M 292M  290M S     0,0 29,2   0:09 postmaster
> > 10957 postgres   9   0  214M 214M  212M S     0,0 21,5   0:10 postmaster
> > 10964 postgres   9   0 58156  56M 56400 S     0,0  5,6   0:05 postmaster
> > 10974 postgres   9   0 50860  49M 49120 S     0,0  4,9   0:04 postmaster
> > 10975 postgres   9   0  209M 209M  207M S     0,0 21,0   0:04 postmaster
> > 10976 postgres   9   0  174M 174M  172M S     0,0 17,5   0:08 postmaster
> > 10977 postgres   9   0 52484  51M 50932 S     0,0  5,1   0:05 postmaster
> > 10990 postgres   9   0  199M 199M  197M S     0,0 19,9   0:06 postmaster
> > 10993 postgres   9   0  141M 141M  139M S     0,0 14,1   0:01 postmaster
> > 10998 postgres   9   0  181M 181M  180M S     0,0 18,2   0:04 postmaster
> > 10999 postgres   9   0  139M 139M  138M S     0,0 14,0   0:01 postmaster
> > 11001 postgres   9   0 45484  44M 43948 S     0,0  4,4   0:01 postmaster
> > 11006 postgres   9   0 15276  14M 13952 S     0,0  1,4   0:00 postmaster
> >
> > now, does this look better in your eyes?
> >
> > > What are your current settings? Could you please repost. I lost earlier
> > > thread(Sorry for that.. Had a HDD meltdown here couple of days back. Lost few
> > > mails..)
> >
> > do you need more information here?
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
>

Re: [PERFORM] Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 13:31:56

hi,

this is what it look like right now... looks like 69 MB of shared memory...

------ Shared Memory Segments --------
key        shmid      owner      perms      Bytes      nattch     Status
0x0052e2c1 131072     postgres  600        69074944   19

------ Semaphore Arrays --------
key        semid      owner      perms      nsems      Status
0x0052e2c1 655360     postgres  600        17
0x0052e2c2 688129     postgres  600        17
0x0052e2c3 720898     postgres  600        17

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Henrik Steffen" <steffen@city-map.de>
Sent: Thursday, November 14, 2002 4:25 PM
Subject: Re: [PERFORM] [GENERAL] Upgrade to dual processor machine?


> On Thursday 14 November 2002 08:50 pm, you wrote:
> > by the way: today we update to kernel 2.4.19 and we measured BIG
> > performance gains! however, since the upgrade 'top' doesn't show any
> > shared memory in the summary any longer... yet for every process
> > it lists a certain amount of shared mem... is this a kernel/top issue
> > or did I miss something here?
>
> No. The shared memory accounting is turned off because it is seemingly much
> complex. Process do share memory.  Check output of ipcs as root..
>
>  Shridhar

Re: Upgrade to dual processor machine?

From

Manfred Koizar

Date:

14 November 2002, 15:13:48

On Thu, 14 Nov 2002 11:03:54 +0100, "Henrik Steffen"
<steffen@city-map.de> wrote:
>this is how it looks like, when my system is busy (right now!!!)
>vmstat 1 5:
>   procs                      memory    swap          io     system         cpu
> r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
> 1  8  1     60   4964   5888 309684   0   0   176    74   16    32  25   9  66
> 0  6  3     60   4964   5932 308772   0   0  6264   256  347   347  13   9  78
> 0  5  1     60   4964   5900 309364   0   0  9312   224  380   309  11   6  83
> 1  4  1     60   5272   5940 309152   0   0 10320   116  397   429  17   6  77
> 1  4  1     60   4964   5896 309512   0   0 11020   152  451   456  14  10  76

More than 10000 disk blocks coming in per second looks quite
impressive, IMHO.  (I wonder if this is due to seq scans?)  But the
cpu idle column tells us that you are not CPU bound any more.

>free:
>             total       used       free     shared    buffers     cached
>Mem:       1020808    1015860       4948     531424       5972     309548
>-/+ buffers/cache:     700340     320468
>Swap:      1028112         60    1028052

There are two camps when it comes to PG shared buffers: (a) set
shared_buffers as high as possible to minimize PG buffer misses vs.
(b) assume that transfers between OS and PG buffers are cheap and
choose a moderate value for shared_buffers ("in the low thousands") to
let the operating system's disk caching do its work.

Both camps agree that reserving half of your available memory for
shared buffers is a Bad Thing, because whenever a page cannot be found
in PG's buffers it is almost certainly not in the OS cache and has to
be fetched from disk.  So half of your memory (the OS cache) is wasted
for nothing.

FYI, I belong to the latter camp and I strongly feel you should set
shared_buffers to something near 4000.

Servus
 Manfred

Re: Upgrade to dual processor machine?

From

"Steve Wolfe"

Date:

14 November 2002, 16:42:55

> The cache-field is saying 873548K cached at the moment
> Is this a "whole bunch of cache" in your opinion? Is it too much?

  Too much cache?  It ain't possible. ; )

  For what it's worth, my DB machine generally uses about 1.25 gigs for
disk cache, in addition to the 64 megs that are on the RAID card, and
that's just fine with me.  I allocate 256 megs of shared memory (32768
buffers), and the machine hums along very nicely.  vmstat shows that
actual reads to the disk are *extremely* rare, and the writes that come
from inserts/etc. are nicely buffered.

  Here's how I chose 256 megs for shared buffers:  First, I increased the
shared buffer amount until I didn't see any more performance benefits.
Then I doubled it just for fun. ; )

  Again, in your message it seemed like you were doing quite a bit of
writes - have you disabled fsync, and what sort of disk system do you
have?

steve

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 17:26:43

hi steve,

why fsync? - what's fsync? never heard of it... google tells
me something about syncing of remote hosts ... so why should I
activate it ?? ... I conclude, it's probably disabled because
I don't know what it is ....

it's a raid-1 ide system

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Steve Wolfe" <nw@codon.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, November 14, 2002 7:46 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> > The cache-field is saying 873548K cached at the moment
> > Is this a "whole bunch of cache" in your opinion? Is it too much?
>
>   Too much cache?  It ain't possible. ; )
>
>   For what it's worth, my DB machine generally uses about 1.25 gigs for
> disk cache, in addition to the 64 megs that are on the RAID card, and
> that's just fine with me.  I allocate 256 megs of shared memory (32768
> buffers), and the machine hums along very nicely.  vmstat shows that
> actual reads to the disk are *extremely* rare, and the writes that come
> from inserts/etc. are nicely buffered.
>
>   Here's how I chose 256 megs for shared buffers:  First, I increased the
> shared buffer amount until I didn't see any more performance benefits.
> Then I doubled it just for fun. ; )
>
>   Again, in your message it seemed like you were doing quite a bit of
> writes - have you disabled fsync, and what sort of disk system do you
> have?
>
> 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)

Re: Upgrade to dual processor machine?

From

Doug McNaught

Date:

14 November 2002, 17:35:59

"Henrik Steffen" <steffen@city-map.de> writes:

> hi steve,
>
> why fsync? - what's fsync? never heard of it... google tells
> me something about syncing of remote hosts ... so why should I
> activate it ?? ... I conclude, it's probably disabled because
> I don't know what it is ....

fsync() is a system call that flushes a file's contents from the
buffer cache to disk.  PG uses it to ensure consistency in the WAL
files.  It is enabled by default.  Do NOT disable it unless you know
exactly what you are doing and are prepared to sacrifice some data
integrity for performance.

-Doug

Re: Upgrade to dual processor machine?

From

"Henrik Steffen"

Date:

14 November 2002, 17:37:08

of course, there are some seq scans... one of the most
difficult queries is for example a kind of full text
search, that searches through 8 different tables with
each between 300.000 and 500.000 rows and 5-50 columns,
but that's a different issue (need a full-text-search-engine...)


I will do some experiments with both camps you described


Thanks to all of you who wrote answers to this thread

It has helped me a huge lot !



--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Manfred Koizar" <mkoi-pg@aon.at>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: <pgsql-general@postgresql.org>; <pgsql-performance@postgresl.org>
Sent: Thursday, November 14, 2002 6:15 PM
Subject: Re: [GENERAL] Upgrade to dual processor machine?


> On Thu, 14 Nov 2002 11:03:54 +0100, "Henrik Steffen"
> <steffen@city-map.de> wrote:
> >this is how it looks like, when my system is busy (right now!!!)
> >vmstat 1 5:
> >   procs                      memory    swap          io     system         cpu
> > r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
> > 1  8  1     60   4964   5888 309684   0   0   176    74   16    32  25   9  66
> > 0  6  3     60   4964   5932 308772   0   0  6264   256  347   347  13   9  78
> > 0  5  1     60   4964   5900 309364   0   0  9312   224  380   309  11   6  83
> > 1  4  1     60   5272   5940 309152   0   0 10320   116  397   429  17   6  77
> > 1  4  1     60   4964   5896 309512   0   0 11020   152  451   456  14  10  76
>
> More than 10000 disk blocks coming in per second looks quite
> impressive, IMHO.  (I wonder if this is due to seq scans?)  But the
> cpu idle column tells us that you are not CPU bound any more.
>
>
> >free:
> >             total       used       free     shared    buffers     cached
> >Mem:       1020808    1015860       4948     531424       5972     309548
> >-/+ buffers/cache:     700340     320468
> >Swap:      1028112         60    1028052
>
> There are two camps when it comes to PG shared buffers: (a) set
> shared_buffers as high as possible to minimize PG buffer misses vs.
> (b) assume that transfers between OS and PG buffers are cheap and
> choose a moderate value for shared_buffers ("in the low thousands") to
> let the operating system's disk caching do its work.
>
> Both camps agree that reserving half of your available memory for
> shared buffers is a Bad Thing, because whenever a page cannot be found
> in PG's buffers it is almost certainly not in the OS cache and has to
> be fetched from disk.  So half of your memory (the OS cache) is wasted
> for nothing.
>
> FYI, I belong to the latter camp and I strongly feel you should set
> shared_buffers to something near 4000.
>
> Servus
>  Manfred
>

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

14 November 2002, 17:54:26

On Thu, 14 Nov 2002, Henrik Steffen wrote:

>
> hi steve,
>
> why fsync? - what's fsync? never heard of it... google tells
> me something about syncing of remote hosts ... so why should I
> activate it ?? ... I conclude, it's probably disabled because
> I don't know what it is ....
>
> it's a raid-1 ide system

fsync is enabled by default.  fsync flushes disk buffers after every
write.  Turning it off lets the OS flush buffers at its leisure.  setting
fsync=false will often double the write performance and since writes are
running faster, there's more bandwidth for the reads as well, so
everything goes faster.

Definitely look at putting your data onto a Ultra160 SCSI 15krpm RAID1
set.  My dual 80 Gig Ultra100 IDEs can get about 30 Megs a second in a
RAID1 for raw reads under bonnie++, while my pair of Ultra80 10krpm 18 gig
scsis can get about 48 Megs a second raw read.

Plus SCSI is usually MUCH faster for writes than IDE.

Re: Upgrade to dual processor machine?

From

"scott.marlowe"

Date:

14 November 2002, 18:00:13

On 14 Nov 2002, Doug McNaught wrote:

> "Henrik Steffen" <steffen@city-map.de> writes:
>
> > hi steve,
> >
> > why fsync? - what's fsync? never heard of it... google tells
> > me something about syncing of remote hosts ... so why should I
> > activate it ?? ... I conclude, it's probably disabled because
> > I don't know what it is ....
>
> fsync() is a system call that flushes a file's contents from the
> buffer cache to disk.  PG uses it to ensure consistency in the WAL
> files.  It is enabled by default.  Do NOT disable it unless you know
> exactly what you are doing and are prepared to sacrifice some data
> integrity for performance.

I thought the danger with WAL was minimized to the point of not being an
issue anymore.  Tom?

Re: [PERFORM] Upgrade to dual processor machine?

From

Tom Lane

Date:

14 November 2002, 18:20:30

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On 14 Nov 2002, Doug McNaught wrote:
>> fsync() is a system call that flushes a file's contents from the
>> buffer cache to disk.  PG uses it to ensure consistency in the WAL
>> files.  It is enabled by default.  Do NOT disable it unless you know
>> exactly what you are doing and are prepared to sacrifice some data
>> integrity for performance.

> I thought the danger with WAL was minimized to the point of not being an
> issue anymore.  Tom?

Actually, more the other way 'round: WAL minimizes the cost of using
fsync, since we now only need to fsync the WAL file and not anything
else.  The risk of not using it is still data corruption --- mainly
because without fsync, we can't be certain that WAL writes hit disk
in advance of the corresponding data-page changes.  If you have a crash,
the system will replay the log as far as it can; but if there are
additional unlogged changes in the data files, you might have
inconsistencies.

I'd definitely recommend keeping fsync on in any production
installation.  For development maybe you don't care about data loss...

            regards, tom lane

Re: [PERFORM] Upgrade to dual processor machine?

From

jmelesky@performics.com

Date:

15 November 2002, 14:44:56

On 14 Nov, Henrik Steffen wrote:
> of course, there are some seq scans... one of the most
> difficult queries is for example a kind of full text
> search, that searches through 8 different tables with
> each between 300.000 and 500.000 rows and 5-50 columns,
> but that's a different issue (need a full-text-search-engine...)

Ah, well, it may be worthwhile to check out fulltextindex or tsearch
in contrib/. They both require some changes to the way you do queries,
but they may be helpful in speeding up those queries.

-johnnnnnnnnn