pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting better performance
8+ messages / 6 participants
[nested] [flat]
* getting better performance
@ 2006-07-06 06:40 Eugeny N Dzhurinsky <bofh@redwerk.com>
0 siblings, 3 replies; 8+ messages in thread
From: Eugeny N Dzhurinsky @ 2006-07-06 06:40 UTC (permalink / raw)
To: pgsql-performance
Hello!
I have a postgresql server serving thousands of tables. Sometime there are
queries which involves several tables.
In postgresql.conf I have these settings:
shared_buffers = 40000
work_mem = 8192
maintenance_work_mem = 16384
max_stack_depth = 2048
all other settings are left by default (except ones needed for pg_autovacuum).
Is there anything I can tune to get better performance?
--
Eugene N Dzhurinsky
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 06:48 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Eugeny N Dzhurinsky <bofh@redwerk.com>
2 siblings, 1 reply; 8+ messages in thread
From: A. Kretschmer @ 2006-07-06 06:48 UTC (permalink / raw)
To: pgsql-performance
am 06.07.2006, um 9:40:16 +0300 mailte Eugeny N Dzhurinsky folgendes:
> In postgresql.conf I have these settings:
>
> shared_buffers = 40000
> work_mem = 8192
> maintenance_work_mem = 16384
> max_stack_depth = 2048
>
> all other settings are left by default (except ones needed for pg_autovacuum).
>
> Is there anything I can tune to get better performance?
You can set "log_min_duration_statement" to log slow querys and then
analyse this querys.
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 08:48 Ivan Zolotukhin <ivan.zolotukhin@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ivan Zolotukhin @ 2006-07-06 08:48 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
On 7/6/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> am 06.07.2006, um 9:40:16 +0300 mailte Eugeny N Dzhurinsky folgendes:
> > In postgresql.conf I have these settings:
> >
> > shared_buffers = 40000
> > work_mem = 8192
> > maintenance_work_mem = 16384
> > max_stack_depth = 2048
> >
> > all other settings are left by default (except ones needed for pg_autovacuum).
> >
> > Is there anything I can tune to get better performance?
>
> You can set "log_min_duration_statement" to log slow querys and then
> analyse this querys.
When you collect your logs try PgFouine
http://pgfouine.projects.postgresql.org/
to understand what queries should be optimized and what's the reason
of poor performance.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 14:28 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Eugeny N Dzhurinsky <bofh@redwerk.com>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2006-07-06 14:28 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
On Thu, 2006-07-06 at 01:40, Eugeny N Dzhurinsky wrote:
> Hello!
>
> I have a postgresql server serving thousands of tables. Sometime there are
> queries which involves several tables.
Do you add / remove tables a lot? Could be you've got system catalog
bloat.
do you have autovacuum running?
What version of pgsql are you running?
What OS?
What file system?
What kind of machine are you using?
How much memory does it have?
How many disk drives?
Are you using RAID? hardware / software? battery backed cache or no?
Do you have one or two, or thousands of connections at a time?
Do you use connection pooling if you have lots of connections to handle?
There's a lot of info needed to make a decision on how to performance
tune a system.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 15:11 Eugeny N Dzhurinsky <bofh@redwerk.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 8+ messages in thread
From: Eugeny N Dzhurinsky @ 2006-07-06 15:11 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jul 06, 2006 at 09:28:39AM -0500, Scott Marlowe wrote:
> On Thu, 2006-07-06 at 01:40, Eugeny N Dzhurinsky wrote:
> Do you add / remove tables a lot? Could be you've got system catalog
> bloat.
Yes, almost each table is dropped and re-created in 3-5 days.
> do you have autovacuum running?
Yes.
> What version of pgsql are you running?
psql -V
psql (PostgreSQL) 8.0.0
> What OS?
CentOS release 3.7 (Final)
> What file system?
ext3
> What kind of machine are you using?
Pentium IV, 1.8 GHz
> How much memory does it have?
512 Mb RAM
> How many disk drives?
single
> Are you using RAID? hardware / software? battery backed cache or no?
no
> Do you have one or two, or thousands of connections at a time?
something like 20 connections (peak).
> Do you use connection pooling if you have lots of connections to handle?
My application uses Jakarta Commons DBCP module.
--
Eugene Dzhurinsky
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 15:26 Merlin Moncure <mmoncure@gmail.com>
parent: Eugeny N Dzhurinsky <bofh@redwerk.com>
2 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2006-07-06 15:26 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
On 7/6/06, Eugeny N Dzhurinsky <bofh@redwerk.com> wrote:
> Hello!
>
> I have a postgresql server serving thousands of tables. Sometime there are
> queries which involves several tables.
> In postgresql.conf I have these settings:
>
> shared_buffers = 40000
> work_mem = 8192
> maintenance_work_mem = 16384
> max_stack_depth = 2048
>
> all other settings are left by default (except ones needed for pg_autovacuum).
>
> Is there anything I can tune to get better performance?
you may want to explore upping your FSM settings with that many tables.
Merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-06 15:32 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Eugeny N Dzhurinsky <bofh@redwerk.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2006-07-06 15:32 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
On Thu, 2006-07-06 at 10:11, Eugeny N Dzhurinsky wrote:
> On Thu, Jul 06, 2006 at 09:28:39AM -0500, Scott Marlowe wrote:
> > On Thu, 2006-07-06 at 01:40, Eugeny N Dzhurinsky wrote:
> > Do you add / remove tables a lot? Could be you've got system catalog
> > bloat.
>
> Yes, almost each table is dropped and re-created in 3-5 days.
> > do you have autovacuum running?
>
> Yes.
Hopefully, that will keep the system catalogs from getting overly fat.
You still need to check their size every so often to make sure they're
not getting out of line.
you might wanna run a vacuum verbose and see what it has to say.
> > What version of pgsql are you running?
>
> psql -V
> psql (PostgreSQL) 8.0.0
You should update. x.0.0 versions often have the nastiest of the data
loss bugs of any release versions of postgresql. 8.0 is up to 8.0.8
now. The upgrades are generally painless (backup anyway, just in case)
and can be done in place. just down postgres, rpm -Uvh the packages and
restart postgres
> > What OS?
>
> CentOS release 3.7 (Final)
>
> > What file system?
>
> ext3
>
> > What kind of machine are you using?
>
> Pentium IV, 1.8 GHz
>
> > How much memory does it have?
>
> 512 Mb RAM
That's kind of small for a database server. If your data set is fairly
small it's alright, but if you're working on gigs of data in your
database, the more memory the better.
> > How many disk drives?
>
> single
OTOH, if you're doing a lot of committing / writing to the hard drives,
a single disk drive is suboptimal
Is this SCSI, PATA or SATA? If it's [SP]ATA, then you've likely got no
real fsyncing, and while performance won't be a problem, reliability
should the machine crash would be. If it's SCSI, then it could be a
bottle neck for writes.
> > Are you using RAID? hardware / software? battery backed cache or no?
>
> no
I'd recommend looking into it, unless you're CPU bound. A decent RAID
controller with battery backed cache and a pair of drives in a mirror
setup can be a marked improved to start with, and you can add more
drives as time goes by if needs be.
My guess is that you've got sys catalog bloat. You might have to down
the database to single user mode and run a vacuum on the system catalogs
from there. That's how it was in the old days of 7.x databases anyway.
If you don't have sys cat bloat, then you're probably CPU / memory bound
right now. unless you're writing a lot, then you're likely disk i/o
bound, but I kinda doubt it.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: getting better performance
@ 2006-07-07 08:28 Markus Schaber <schabi@logix-tt.com>
parent: Eugeny N Dzhurinsky <bofh@redwerk.com>
1 sibling, 0 replies; 8+ messages in thread
From: Markus Schaber @ 2006-07-07 08:28 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
Hi, Eugeny,
Eugeny N Dzhurinsky wrote:
>> Do you add / remove tables a lot? Could be you've got system catalog
>> bloat.
>
> Yes, almost each table is dropped and re-created in 3-5 days.
If your really recreate the same table, TRUNCATE may be a better
solution than dropping and recreation.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-07-07 08:28 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-06 06:40 getting better performance Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-06 06:48 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-07-06 08:48 ` Ivan Zolotukhin <ivan.zolotukhin@gmail.com>
2006-07-06 14:28 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-06 15:11 ` Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-06 15:32 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-07 08:28 ` Markus Schaber <schabi@logix-tt.com>
2006-07-06 15:26 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox