Valerie Schneider DSI/DEV wrote:
Hi,
I have some problem of performance on a PG database, and I don't
know how to improve. I Have two questions : one about the storage
of data, one about tuning queries. If possible !
My job is to compare Oracle and Postgres. All our operational databases
have been
G u i d o B a r o s i o wrote:
The box:
Linux 2.4.24-ck1
8 Intel(R) Xeon(TM) MP CPU 2.80GHz
4 gb RAM.
Postgresql 7.4.2
The problem:
Short in disk space. (waiting new hard)
The real problem:
Developers usually write queries involving the creation of temporary tables.
I seen too this behavior,
Valerie Schneider DSI/DEV wrote:
Hi,
I 've decreased the sort_mem to 5000 instead of 5.
I recreated ma table using integer and real types instead of
numeric : the result is very improved for the disk space :
schema | relfilenode | table | index| reltuples | size
Martin Foster wrote:
Gaetano Mendola wrote:
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of
PostgreSQL (7.4.3) for everything from user information to formatting
and display of specific sections of the site. The server itself, is
a dual processor AMD Opteron
Paul Serby wrote:
Can anyone give a good reference site/book for getting the most out of
your postgres server.
All I can find is contradicting theories on how to work out your settings.
This is what I followed to setup our db server that serves our web
applications.
Paul,
Physical Memory: 2077264 kB
sort_mem = 12000
Hmmm. Someone may already have mentioned this, but that looks problematic.
You're allowing up to 12MB per sort, and up to 300 connections. Even if each
concurrent connection averages only one sort (and they can use more) that's
3600MB
On Wed, 2004-08-04 at 17:25 +0200, Gaetano Mendola wrote:
The queries themselves are simple, normally drawing information from one
table with few conditions or in the most complex cases using joins on
two table or sub queries. These behave very well and always have, the
problem is
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Josh Berkus wrote:
| Paul,
|
|
|Physical Memory: 2077264 kB
|
|
|sort_mem = 12000
|
|
| Hmmm. Someone may already have mentioned this, but that looks problematic.
| You're allowing up to 12MB per sort, and up to 300 connections. Even if each
|
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for your
configuration
sort_mem = 2048
wal_buffers = 128 This is really too small for your configuration
effective_cache_size = 16000
change this values in:
Gaetano,
Of course your are speaking about the worst case, I aplly in scenarios
like
this on the rule 80/20: 80% of connection will perform a sort and 20% will
allocate
memory for the sort operation in the same window time:
Well, I suppose it depends on how aggresive your connection pooling
Mike Benoit wrote:
On Wed, 2004-08-04 at 17:25 +0200, Gaetano Mendola wrote:
The queries themselves are simple, normally drawing information from one
table with few conditions or in the most complex cases using joins on
two table or sub queries. These behave very well and always have, the
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for your
configuration
sort_mem = 2048
wal_buffers = 128 This is really too small for your
configuration
effective_cache_size = 16000
change
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Josh Berkus wrote:
| Gaetano,
|
|
|Of course your are speaking about the worst case, I aplly in scenarios
|
| like
|
|this on the rule 80/20: 80% of connection will perform a sort and 20% will
|
| allocate
|
|memory for the sort operation in the same
Martin Foster [EMAIL PROTECTED] writes:
Gaetano Mendola wrote:
change this values in:
shared_buffers = 5
sort_mem = 16084
wal_buffers = 1500
This value of wal_buffers is simply ridiculous.
There isn't any reason to set wal_buffers higher than the amount of
WAL log data that will be
in the docummentation about the planer it says:
It first combines all possible ways of scanning and joining the relations
that appear in a query
I would like to know if there's a time limit to do that or if it just scans
ALL the posibilities until it finishes..no matter the time it takes..
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for your
configuration
sort_mem = 2048
wal_buffers = 128 This is
On Fri, Aug 06, 2004 at 07:28:38PM -0500, sandra ruiz wrote:
in the docummentation about the planer it says:
It first combines all possible ways of scanning and joining the relations
that appear in a query
I would like to know if there's a time limit to do that or if it just scans
ALL
Scott Marlowe wrote:
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for your
configuration
sort_mem = 2048
wal_buffers = 128 This is
On Fri, 2004-08-06 at 22:02, Martin Foster wrote:
Scott Marlowe wrote:
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for your
Scott Marlowe wrote:
On Fri, 2004-08-06 at 22:02, Martin Foster wrote:
Scott Marlowe wrote:
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192 This is really too small for
20 matches
Mail list logo