pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimization
11+ messages / 8 participants
[nested] [flat]
* Optimization
@ 2003-07-28 16:18 Justin Long <justinlong@strategicnetwork.org>
2003-07-28 18:00 ` Re: Optimization Josh Berkus <josh@agliodbs.com>
2003-07-28 18:25 ` Re: Optimization Nick Fankhauser - Doxpop <nickf@doxpop.com>
2003-07-28 18:28 ` Re: Optimization Nick Fankhauser <nickf@ontko.com>
2003-07-28 18:39 ` Re: Optimization Nick Fankhauser <nickf@ontko.com>
2003-07-28 19:27 ` Re: Optimization Josh Berkus <josh@agliodbs.com>
2003-07-29 06:51 ` Re: Optimization Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 6 replies; 11+ messages in thread
From: Justin Long @ 2003-07-28 16:18 UTC (permalink / raw)
To: pgsql-performance
Greetings,
I am trying to understand the various factors used by Postgres to optimize. I presently have a dual-866 Dell server with 1GB of memory. I've done the following:
set /proc/sys/kernel/shmmax to 512000000
shared_buffers = 32000
sort_mem = 32000
max_connections=64
fsync=false
Can someone tell me what effective_cache_size should be set to? what kind of formula to use for this? (I got the other figures from phpbuilder.com, and modified for 512k memory).
The databases I'm using have about 200,000+ news headlines with full-text indexes (which range upwards of a few million records). They are updated about every 5 to 10 minutes, which means I also have to run a vacuum about once every 2 to 3 hours at least. As I get more updates obviously the efficiency goes down. I'm trying to make the most of this system but don't fully understand PG's optimization stuff.
Thanks in advance,
Justin Long
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-28 18:00 ` Josh Berkus <josh@agliodbs.com>
5 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-07-28 18:00 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; pgsql-performance
Justin,
> I am trying to understand the various factors used by Postgres to optimize.
I presently have a dual-866 Dell server with 1GB of memory. I've done the
following:
Please set the performance articles at:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-28 18:25 ` Nick Fankhauser - Doxpop <nickf@doxpop.com>
5 siblings, 0 replies; 11+ messages in thread
From: Nick Fankhauser - Doxpop @ 2003-07-28 18:25 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; pgsql-performance
Justin-
It sounds like you're on a system similar to ours, so I'll pass along the
changes that I made, which seem to have increased performance, and most
importantly, haven't hurt anything. The main difference in our environment
is that we are less Update/Insert intensive than you are- in our
application, 90% of our information (court cases) is static (Closed) and 10%
are frequently being updated (Pending/Active). This means I only vacuum once
a week. I haven't had chance to churn out objective tests yet, but my
subjective judgment is that this set of params works well:
Set SHMMAX and SHMALL in the kernel to 134217728 (128MB)
Set shared_buffers to 8192 (64MB)
Set sort_mem to 16384 (16MB)
Set effective_cache_size to 65536 (1/2 GB)
The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.
The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.
The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)
The PostgreSQL version is 7.3.2
My reasoning was to increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB and boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB. Of the remaining memory, 256MB goes to 16MB sort space
times
a guesstimate of 16 simultaneous sorts at any given time. If I leave about
128 MB for headroom, then 1/2 GB should be left available for the effective
cache size.
I've never been tempted to turn fsync off. That seems like a risky move.
Regards,
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-28 18:28 ` Nick Fankhauser <nickf@ontko.com>
5 siblings, 0 replies; 11+ messages in thread
From: Nick Fankhauser @ 2003-07-28 18:28 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; pgsql-performance
Justin-
It sounds like you're on a system similar to ours, so I'll pass along the
changes that I made, which seem to have increased performance, and most
importantly, haven't hurt anything. The main difference in our environment
is that we are less Update/Insert intensive than you are- in our
application, 90% of our information (court cases) is static (Closed) and 10%
are frequently being updated (Pending/Active). This means I only vacuum once
a week. I haven't had chance to churn out objective tests yet, but my
subjective judgment is that this set of params works well:
Set SHMMAX and SHMALL in the kernel to 134217728 (128MB)
Set shared_buffers to 8192 (64MB)
Set sort_mem to 16384 (16MB)
Set effective_cache_size to 65536 (1/2 GB)
The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.
The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.
The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)
The PostgreSQL version is 7.3.2
My reasoning was to increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB and boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB off the top. Of the remaining memory, 256MB goes to 16MB
sort space times a guesstimate of 16 simultaneous sorts at any given time.
If I leave about 128 MB for headroom, then 1/2 GB should be left available
for the effective cache size.
I've never been tempted to turn fsync off. That seems like a risky move.
Regards,
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-28 18:39 ` Nick Fankhauser <nickf@ontko.com>
5 siblings, 0 replies; 11+ messages in thread
From: Nick Fankhauser @ 2003-07-28 18:39 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; pgsql-performance
>Can someone tell me what effective_cache_size should be set to?
You may be able to intuit this from my last post, but if I understand
correctly, what you should be doing is estimating how much memory is likely
to be "left over" for the OS to do disk caching with after all of the basic
needs of the OS, PostgreSQL & any other applications are taken care of. You
then tell postgresql what to expect in terms of caching resources by putting
this number into effective_cache_size, and this allows the query planner
come up with a strategy that is optimized for the expected cache size.
So the "formula" would be: Figure out how much memory is normally in use
allowing adequate margins, subtract this from your total RAM, and make the
remainder your effective_cache size.
-Nick
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-28 19:27 ` Josh Berkus <josh@agliodbs.com>
5 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-07-28 19:27 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; pgsql-performance
Justin,
> I am trying to understand the various factors used by Postgres to optimize.
I presently have a dual-866 Dell server with 1GB of memory. I've done the
following:
see: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
which has articles on .conf files.
(feel free to link these articles at PHPbuilder.com and elsewhere!)
> The databases I'm using have about 200,000+ news headlines with full-text
indexes (which range upwards of a few million records). They are updated
about every 5 to 10 minutes, which means I also have to run a vacuum about
once every 2 to 3 hours at least. As I get more updates obviously the
efficiency goes down. I'm trying to make the most of this system but don't
fully understand PG's optimization stuff.
Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every
10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your
database. You will also want to increase your FSM_relations so that VACUUM
is more effective/efficient; again, see the articles.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
@ 2003-07-29 06:51 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-29 07:14 ` Re: Optimization Peter Childs <blue.dragon@blueyonder.co.uk>
2003-07-29 07:21 ` Re: Optimization Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
5 siblings, 2 replies; 11+ messages in thread
From: Shridhar Daithankar @ 2003-07-29 06:51 UTC (permalink / raw)
To: pgsql-performance
On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every
> 10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your
> database. You will also want to increase your FSM_relations so that VACUUM
> is more effective/efficient; again, see the articles.
There is an auto-vacuum daemon in contrib and if I understand it correctly, it
is not getting much of a field testing. How about you guys installing it and
trying it?
Bye
Shridhar
--
O'Reilly's Law of the Kitchen: Cleanliness is next to impossible
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
2003-07-29 06:51 ` Re: Optimization Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-29 07:14 ` Peter Childs <blue.dragon@blueyonder.co.uk>
1 sibling, 0 replies; 11+ messages in thread
From: Peter Childs @ 2003-07-29 07:14 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Tue, 29 Jul 2003, Shridhar Daithankar wrote:
> On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> > Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every
> > 10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your
> > database. You will also want to increase your FSM_relations so that VACUUM
> > is more effective/efficient; again, see the articles.
>
> There is an auto-vacuum daemon in contrib and if I understand it correctly, it
> is not getting much of a field testing. How about you guys installing it and
> trying it?
If there is such a daemon, what is it called? As I can't see it.
Is it part of gborg?
Peter Childs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Optimization
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
2003-07-29 06:51 ` Re: Optimization Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-29 07:21 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 11+ messages in thread
From: Shridhar Daithankar @ 2003-07-29 07:21 UTC (permalink / raw)
To: pgsql-performance
On 29 Jul 2003 at 8:14, Peter Childs wrote:
> On Tue, 29 Jul 2003, Shridhar Daithankar wrote:
>
> > On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> > > Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every
> > > 10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your
> > > database. You will also want to increase your FSM_relations so that VACUUM
> > > is more effective/efficient; again, see the articles.
> >
> > There is an auto-vacuum daemon in contrib and if I understand it correctly, it
> > is not getting much of a field testing. How about you guys installing it and
> > trying it?
>
> If there is such a daemon, what is it called? As I can't see it.
> Is it part of gborg?
It is in sources. See contrib module in postgresql CVS, 7.4 beta if you prefer
to wait till announement.
It is called as pgavd..
Bye
Shridhar
--
squatcho, n.: The button at the top of a baseball cap. -- "Sniglets", Rich
Hall & Friends
^ permalink raw reply [nested|flat] 11+ messages in thread
* optimization
@ 2011-02-28 09:43 croolyc <croolists@gmail.com>
2011-02-28 14:57 ` Re: optimization Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 11+ messages in thread
From: croolyc @ 2011-02-28 09:43 UTC (permalink / raw)
To: pgsql-performance
Hi!
can you help me with performance optimization
on my machine I have 8 databases with ca. 1-2GB
processor is:
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E3110 @ 3.00GHz
stepping : 10
cpu MHz : 2992.585
cache size : 6144 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
...
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E3110 @ 3.00GHz
stepping : 10
cpu MHz : 2992.585
cache size : 6144 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
...
memory: 8GB (4*2GB ecc ram)
hdd: raid 1 (mdadm) - 2x 500GB, ext4 (mounted for /var, without /var/log)
powered with UPS
my English is not very good and I completely lost track while reading
the user manual
i find something like that
http://samiux.wordpress.com/2009/07/26/howto-performance-tuning-for-postgresql-on-ubuntudebian/
but i,m not so sure, reading mha220 comments, that is safe for me & my server :/
greatings
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: optimization
2011-02-28 09:43 optimization croolyc <croolists@gmail.com>
@ 2011-02-28 14:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2011-02-28 14:57 UTC (permalink / raw)
To: croolyc <croolists@gmail.com>; pgsql-performance
croolyc <croolists@gmail.com> wrote:
> can you help me with performance optimization
For overall tuning you could start here:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
If, after some general tuning, you are having problems with slow
queries, it is best if you pick one and show it with EXPLAIN ANALYZE
output and the schema of the tables involved.
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-02-28 14:57 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-28 16:18 Optimization Justin Long <justinlong@strategicnetwork.org>
2003-07-28 18:00 ` Josh Berkus <josh@agliodbs.com>
2003-07-28 18:25 ` Nick Fankhauser - Doxpop <nickf@doxpop.com>
2003-07-28 18:28 ` Nick Fankhauser <nickf@ontko.com>
2003-07-28 18:39 ` Nick Fankhauser <nickf@ontko.com>
2003-07-28 19:27 ` Josh Berkus <josh@agliodbs.com>
2003-07-29 06:51 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-29 07:14 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-07-29 07:21 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2011-02-28 09:43 optimization croolyc <croolists@gmail.com>
2011-02-28 14:57 ` Re: optimization Kevin Grittner <Kevin.Grittner@wicourts.gov>
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