Hi,
I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
postgres 7.4.3. This has been recompiled on the server for 64 stored
procedure parameters, (I assume this makes postgres 64 bit but are not
sure). When the server gets under load from database connections
Does anyone have any ideas what my bottle neck might be and what I can do
about it?
Your bottleneck is that you are using a very old version of PostgreSQL. Try
8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this
kind of situation.
You won't know until you've seen
On Wed, Dec 12, 2007 at 10:16:43AM +, Matthew Lunnon wrote:
Does anyone have any ideas what my bottle neck might be and what I can do
about it?
Your bottleneck is that you are using a very old version of PostgreSQL. Try
8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in
I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
postgres 7.4.3. This has been recompiled on the server for 64 stored
procedure parameters, (I assume this makes postgres 64 bit but are not
sure). When the server gets under load from database connections
executing
Hi Matthew,
I know exactly what you experience.
We had a 4-way DC Opteron and Pg 7.4 too.
You should monitor context switches.
First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.
You need to limit the number of concurrent queries to less than 8 (8
cores) if you need to
Limiting the queries was our initial thought but we then hit a problem
with connection pooling which didn't implement a fifo algorithm. Looks
like I'll have to look deeper into the connection pooling.
So you think the problem might be context switching on the server, I'll
take a closer look
Thanks for the information Claus, Why would reducing the effective
cache size help the processor usage? It seems that there is plenty of
resources on the box although I can see that 10MB of sort space could
mount up if we had 500 connections but at the moment we do not have
anything like
Ah I was afraid of that. Maybe I'll have to come out of the dark ages.
Matthew
Steinar H. Gunderson wrote:
On Wed, Dec 12, 2007 at 10:16:43AM +, Matthew Lunnon wrote:
Does anyone have any ideas what my bottle neck might be and what I can do
about it?
Your bottleneck is that you
Thanks for the information Claus, Why would reducing the effective cache
size help the processor usage? It seems that there is plenty of resources
on the box although I can see that 10MB of sort space could mount up if we
had 500 connections but at the moment we do not have anything like
On Wed, 12 Dec 2007, Matthew Lunnon wrote:
I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
postgres 7.4.3.
shared_buffers = 96000
As you've already been told repeatedly 7.4 is a release from long before
optimizations to work well on a multi-core server like this.
Hi Matthew,
The context switching isn't the issue. This is an indicator which is
useful to identify your problem.
What kind of application do you running? Can you limit the database clients?
We have a web application based on apache running. We have a limit
number of apache processes which are
Hi Matthew,
I remember that I also an issue with AMD Opterons before Pg 8.1
There is a specific Opteron behaviour on shared memory locks which adds
a extra penalty during the execution time for Pg code before 8.1.
I can you provide my patch for Pg 8.0 which should be adaptable for Pg
7.4 if you
Hi Sven,
yes the patch would be great if you could send it to me, we have already
had to compile postgres to up the number of function parameters from 32
to 64.
Meanwhile I will try and persuade my colleagues to consider the upgrade
option.
Thanks
Matthew
Sven Geisler wrote:
Hi Matthew,
Hi Sven,
Yes I have done a reasonable amount of query tuning. The application is
a web service using an apache/resin combination at the front end, we
have thought about using resin threads to limit the number of
connections but are worried about backing up connections in apache and
getting
Hi Matthew,
Please not that is no official patch, but it works with our Opteron
server without any problems. You should run the regression test after
you adapt it for Pg 7.4.
Sven.
Matthew Lunnon schrieb:
Hi Sven,
yes the patch would be great if you could send it to me, we have already
had
Hi Matthew,
The apache is able to queue 1024 request. Reducing the number of
max_clients was the key to deal with the problem of to much concurrent
queries. We have monitored less concurrent http request after we
decrease max_clients.
We also have introduce a global statement timeout to stop
reading postgres benchmarks for beginners advises to stop reading on the
words default (ie. unchanged postgresql.conf); but the real test is given
right after:
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
That confirmes my first impression (on different workload) of the
Hi Sven,
Does this mean that one option I have is to use a multi core Intel based
server instead of an AMD based server?
Matthew
Sven Geisler wrote:
Hi Matthew,
I remember that I also an issue with AMD Opterons before Pg 8.1
There is a specific Opteron behaviour on shared memory locks
Hi Matthew,
You should be upgrade to Pg 8.2.5.
We did test on different hardware. The bigest step is to use Pg 8.2.5.
The high number of context switches which we saw before simple disappeared.
In regards to you question about XEONs: You will have the similar issue
with a XEON box.
We tried
Harald Armin Massa wrote:
reading postgres benchmarks for beginners advises to stop reading on the
words default (ie. unchanged postgresql.conf); but the real test is
given right after:
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
That confirmes my first impression (on
Matthew Lunnon wrote:
Ah I was afraid of that. Maybe I'll have to come out of the dark ages.
Yes :) but ignore the comment about the 8.3 Beta series. It is Beta for
a reason, that means testing only, no production.
Sincerely,
Joshua D. Drake
Your bottleneck is that you are using a very
Matthew Lunnon wrote:
Hi Sven,
Does this mean that one option I have is to use a multi core Intel based
server instead of an AMD based server?
Wow... hold on.
I remember that I also an issue with AMD Opterons before Pg 8.1
There is a specific Opteron behaviour on shared memory locks which
22 matches
Mail list logo
