Thread: postgres 8 settings
Hi, I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The database size is ~45GB. I am using the following values in postgresql.conf: shared_buffers = 100000 work_mem = 128000 maintenance_work_mem = 100000 max_fsm_pages = 200000 bgwriter_percent = 0 bgwriter_maxpages = 0 fsync = false wal_buffers = 1000 checkpoint_segments = 2048 checkpoint_timeout = 3600 effective_cache_size = 1840000 random_page_cost = 2 geqo_threshold = 25 geqo_effort = 1 stats_start_collector = false stats_command_string = false Do these settings seem fine or I am making some mistake. These settings when used with Postgres 7.4 gave me good results but they don't seem to work with Postgres 8.0. Am I missing out on something?? Regards, Vinita Bansal _________________________________________________________________ Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in India.
On Thu, 10 Mar 2005 09:58:02 +0000, vinita bansal <sagivini@hotmail.com> wrote: > Hi, > > I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The database > size is ~45GB. > I've got a similar box, but with only 16G RAM. What is the storage subsystem, fibre channel or SCSI? Also, what OS? > I am using the following values in postgresql.conf: > > shared_buffers = 100000 Seems high. I did some testing with my real data and found that anything of 15000 wasn't really gaining my anything. This is an 8.x config file (maintenance_work_mem vs. sort_mem), and pg 8+ can actually make do with smaller shared_buffers because of the ARC (soon to be 2Q) buffer management algorithm. Unless your working set PER QUERY is enormous I would suggest lowering this. > work_mem = 128000 That's fine, but you may need to bump it up if (as above) you have individual queries that sort/group huge rowsets. > maintenance_work_mem = 100000 Remember to pump this way up when building very large indexes. > max_fsm_pages = 200000 Should probably be bigger. Mine is 2000000 (2 million). > bgwriter_percent = 0 > bgwriter_maxpages = 0 > fsync = false *KLAXON SOUNDS* Unless you dislike having your data around after power/hardware anomalies you'd better turn that on! > wal_buffers = 1000 > checkpoint_segments = 2048 This will require 16 * ((2 * 1000) + 1) MB of drive space... 100 should be fine, really. (next setting related) > checkpoint_timeout = 3600 Conventional wisdom is not to set this higher than 1800. It also means that you can cut your checkpoint_segments in half (more or less). > effective_cache_size = 1840000 > random_page_cost = 2 This might be low, but it depends on your storage subsystem. Is it fibre channel? > geqo_threshold = 25 Wide queries, eh? > geqo_effort = 1 > stats_start_collector = false > stats_command_string = false > If you want to use pg_autovacuum then you will need to turn the stats stuff back on, including row statistics. Plus it's a big help in debugging. > Do these settings seem fine or I am making some mistake. These settings when > used with Postgres 7.4 gave me good results but they don't seem to work with > Postgres 8.0. Am I missing out on something?? > > Regards, > Vinita Bansal > > _________________________________________________________________ > Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in > India. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
"vinita bansal" <sagivini@hotmail.com> writes:
> Do these settings seem fine or I am making some mistake. These settings when
> used with Postgres 7.4 gave me good results but they don't seem to work with
> Postgres 8.0. Am I missing out on something??
Define "don't seem to work", please.
Offhand the only thing that comes to mind is that the GEQO parameters
changed meaning a bit in 8.0; you shouldn't blindly set geqo_effort the
same as you used to. But with geqo_threshold so high it may not matter
... do you even have any queries with more than 25 tables?
Also, it's just plain bizarre to have work_mem larger than
maintenance_work_mem; I cannot imagine any sane reason to do that.
Are you sure that work_mem = 128M is really a safe setting on your
hardware? If you have a good number of clients all concurrently
doing complicated queries, you could easily find yourself pushed
into swapping.
I concur with Mike's thought that shared_buffers = 100000 is on the high
side. wal_buffers = 1000 strikes me as a waste of RAM too; do you have
any proof that it helps to set it above 10?
Do you really think it's a good idea to disable the bgwriter?
Especially with such a long intercheckpoint time?
regards, tom lane
Hi,
- Oops, forgot to mention that!!
It is Redhat Linux and storage system is SCSI. Also it has 4 processors.
- I would say that there is no particular reason for using a particular
setting except that there were no memory constraints in my case and
they seem to work fine with Postgres 7.4.
I had set them to values that worked while I was benchmarking some data
with Postgres 7.4.
- I have 4 processes which query the database at one point in time. There
are some 5-6 tables which have approx 6-8 million rows and they are the
ones which are the major bottlenecks.
The only reason for setting fsync to false was that the updates to these
tables were taking major time and just setting fysnc to false saved a lot of
time for me. Also, I am not using pg_autovacuum but instead run vaccum for
tables as and when required. Btw, Can running pg_autovaccum as a daemon
process affect performance and is it effective??
Also, a particular query which was taking a min or less with postgres 7 is
taking 15 mins now.
Here is the query plan for the query both for postgres 7.4.6 and postgres 8:
Query plan with Postgres 7.4.6:
explain SELECT cm_AllocData1_temp.allocation, cm_QuotaState.tril_gid,
cm_QuotaState.Period, cm_Quota.tril_gid FROM cm_AllocData1_temp,
cm_QuotaState, cm_Quota WHERE ( ( ( ( ( cm_AllocData1_temp.recipient =
cm_QuotaState.SalesTeam ) AND ( ( cm_AllocData1_temp.transDate ) >= (
cm_QuotaState.StartDate ) ) ) AND ( ( cm_AllocData1_temp.transDate ) < (
cm_QuotaState.EndDate ) ) ) AND ( cm_Quota.tril_gid = cm_QuotaState.Quota
) ) AND ( cm_Quota.Model = 'N151VZWTJN0DUVPBUVZSU31JQ2OUZZZZ' ) ) AND (
cm_Quota.Status = 'Enabled' )
QUERY PLAN
---------------
Hash Join (cost=30.69..18800.11 rows=459 width=109)
Hash Cond: (("outer".quota)::text = ("inner".tril_gid)::text)
-> Nested Loop (cost=0.00..18720.38 rows=8889 width=109)
-> Seq Scan on cm_allocdata1_temp (cost=0.00..20.00 rows=1000
width=7
8)
-> Index Scan using statesbysalesteam_i on cm_quotastate
(cost=0.00..
18.54 rows=9 width=126)
Index Cond: ((("outer".recipient)::text =
(cm_quotastate.salestea
m)::text) AND ("outer".transdate >= cm_quotastate.startdate) AND
("outer".transd
ate < cm_quotastate.enddate))
-> Hash (cost=30.65..30.65 rows=16 width=36)
-> Seq Scan on cm_quota (cost=0.00..30.65 rows=16 width=36)
Filter: (((model)::text =
'N151VZWTJN0DUVPBUVZSU31JQ2OUZZZZ'::tex
t) AND ((status)::text = 'Enabled'::text))
(9 rows)
Query plan with Postgres 8:
explain SELECT cm_AllocData1_temp.allocation, cm_QuotaState.tril_gid,
cm_QuotaState.Period, cm_Quota.tril_gid FROM cm_AllocData1_temp,
cm_QuotaState, cm_Quota WHERE ( ( ( ( ( cm_AllocData1_temp.recipient =
cm_QuotaState.SalesTeam ) AND ( ( cm_AllocData1_temp.transDate ) >= (
cm_QuotaState.StartDate ) ) ) AND ( ( cm_AllocData1_temp.transDate ) < (
cm_QuotaState.EndDate ) ) ) AND ( cm_Quota.tril_gid = cm_QuotaState.Quota
) ) AND ( cm_Quota.Model = 'N151VZWTJN0DUVPBUVZSU31JQ2OUZZZZ' ) ) AND (
cm_Quota.Status = 'Enabled' )
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=333271.40..379482.09 rows=255889 width=143)
Merge Cond: ("outer"."?column4?" = "inner"."?column7?")
Join Filter: (("outer".transdate >= "inner".startdate) AND
("outer".transdate < "inner".enddate))
-> Sort (cost=3214.64..3290.02 rows=30150 width=146)
Sort Key: (cm_allocdata1_temp.recipient)::text
-> Seq Scan on cm_allocdata1_temp (cost=0.00..971.50 rows=30150
width=146)
-> Sort (cost=330056.76..330764.27 rows=283005 width=126)
Sort Key: (cm_quotastate.salesteam)::text
-> Hash Join (cost=22.69..304429.99 rows=283005 width=126)
Hash Cond: (("outer".quota)::text = ("inner".tril_gid)::text)
-> Seq Scan on cm_quotastate (cost=0.00..274161.17
rows=5483217 width=126)
-> Hash (cost=22.65..22.65 rows=16 width=36)
-> Seq Scan on cm_quota (cost=0.00..22.65 rows=16
width=36)
Filter: (((model)::text =
'N151VZWTJN0DUVPBUVZSU31JQ2OUZZZZ'::text) AND ((status)::text =
'Enabled'::text))
Just setting enable_sort and enable_hashjoin to off gives a good plan
(Settings for both are the same).
Plan with sort and hash join turned off:
Nested Loop (cost=22.67..7070120.99 rows=255889 width=143)
Join Filter: (("inner".tril_gid)::text = ("outer".quota)::text)
-> Nested Loop (cost=0.00..5285277.72 rows=4957835 width=143)
-> Seq Scan on cm_allocdata1_temp (cost=0.00..971.50 rows=30150
width=146)
-> Index Scan using statesbysalesteam_i on cm_quotastate
(cost=0.00..172.38 rows=165 width=126)
Index Cond: ((("outer".recipient)::text =
(cm_quotastate.salesteam)::text) AND ("outer".transdate >=
cm_quotastate.startdate) AND ("outer".transdate < cm_quotastate.enddate))
-> Materialize (cost=22.67..22.83 rows=16 width=36)
-> Seq Scan on cm_quota (cost=0.00..22.65 rows=16 width=36)
Filter: (((model)::text =
'N151VZWTJN0DUVPBUVZSU31JQ2OUZZZZ'::text) AND ((status)::text =
'Enabled'::text))
(9 rows)
Why is this happening. Do any of the settings I have mentioned before
causing this??
As regards to setting a high value for wall settings, I am not too sure on
why it should be a problem and how in any way it can affect me?
Also can you clarify what max_fsm_pages,bg_writer actually is since it is
not clear from the documentation. Won't turning bg_writer on affect my
overall run. I am too bothered about that since I have migrated a db2
database to postgres and am working on performance improvement, essentially
trying to get the same no's for the run as db2.
Regards,
Vinita Bansal
>From: Mike Rylander <mrylander@gmail.com>
>Reply-To: Mike Rylander <mrylander@gmail.com>
>To: vinita bansal <sagivini@hotmail.com>, PostgreSQL general
><pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] postgres 8 settings
>Date: Thu, 10 Mar 2005 13:14:19 +0000
>
>On Thu, 10 Mar 2005 09:58:02 +0000, vinita bansal <sagivini@hotmail.com>
>wrote:
> > Hi,
> >
> > I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The
>database
> > size is ~45GB.
> >
>
>I've got a similar box, but with only 16G RAM. What is the storage
>subsystem, fibre channel or SCSI? Also, what OS?
>
> > I am using the following values in postgresql.conf:
> >
> > shared_buffers = 100000
>
>Seems high. I did some testing with my real data and found that
>anything of 15000 wasn't really gaining my anything. This is an 8.x
>config file (maintenance_work_mem vs. sort_mem), and pg 8+ can
>actually make do with smaller shared_buffers because of the ARC (soon
>to be 2Q) buffer management algorithm. Unless your working set PER
>QUERY is enormous I would suggest lowering this.
>
> > work_mem = 128000
>
>That's fine, but you may need to bump it up if (as above) you have
>individual queries that sort/group huge rowsets.
>
> > maintenance_work_mem = 100000
>
>Remember to pump this way up when building very large indexes.
>
> > max_fsm_pages = 200000
>
>Should probably be bigger. Mine is 2000000 (2 million).
>
> > bgwriter_percent = 0
> > bgwriter_maxpages = 0
> > fsync = false
>
>*KLAXON SOUNDS* Unless you dislike having your data around after
>power/hardware anomalies you'd better turn that on!
>
> > wal_buffers = 1000
> > checkpoint_segments = 2048
>
>This will require 16 * ((2 * 1000) + 1) MB of drive space... 100
>should be fine, really. (next setting related)
>
> > checkpoint_timeout = 3600
>
>Conventional wisdom is not to set this higher than 1800. It also
>means that you can cut your checkpoint_segments in half (more or
>less).
>
> > effective_cache_size = 1840000
> > random_page_cost = 2
>
>This might be low, but it depends on your storage subsystem. Is it
>fibre channel?
>
> > geqo_threshold = 25
>
>Wide queries, eh?
>
> > geqo_effort = 1
> > stats_start_collector = false
> > stats_command_string = false
> >
>
>If you want to use pg_autovacuum then you will need to turn the stats
>stuff back on, including row statistics. Plus it's a big help in
>debugging.
>
> > Do these settings seem fine or I am making some mistake. These settings
>when
> > used with Postgres 7.4 gave me good results but they don't seem to work
>with
> > Postgres 8.0. Am I missing out on something??
> >
> > Regards,
> > Vinita Bansal
> >
> > _________________________________________________________________
> > Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in
> > India.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>--
>Mike Rylander
>mrylander@gmail.com
>GPLS -- PINES Development
>Database Developer
>http://open-ils.org
_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee�
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
"vinita bansal" <sagivini@hotmail.com> writes:
> Can you please provide details on the relationship between work_mem and
> maintenance_work_mem (Postgres doc specifies that the value for maintenance
> work_mem should be larger but I am not clear on why that should be so)
Because the other way doesn't make sense.
Each active backend in a system may allocate up to work_mem for each
sort or hash it's currently doing (thus, possibly several times work_mem
for a complex query). The operations that use maintenance_work_mem are
guaranteed not to use that more than once per backend, and in most
situations you'd not have many backends concurrently doing such
operations anyway. So maintenance_work_mem can be a fairly large chunk
of your free RAM without driving the system into swapping, but setting
an equally large value of work_mem as a global default is just foolish.
In some cases you might want to raise work_mem temporarily (via SET) for
specific individual queries, but I wouldn't ever put a large value for
it in postgresql.conf.
> Am I right to say that work_mem is the same as sort_mem and
> maintenance_work_mem as vauum_mem in Postgres 7.4??
Yes, although maintenance_work_mem is used for more things than
vacuum_mem was used for in prior releases.
> Also, since I have 32GB RAM, setting wall_buffers as 1000 should not make a
> difference.
The point is that it's not going to help. Why would you throw away RAM
to a useless purpose when it could be getting used for something useful?
Furthermore, depending on your platform, making the buffer area much
larger than necessary can actually be counterproductive. You want all
of Postgres' shared memory to stay "hot" enough in the eyes of the
kernel that it won't get swapped out. If wal_buffers is too large, then
as Postgres circularly re-uses the buffers, it will advance into memory
areas that it has not touched in a good while. If the kernel decided to
swap that area out, it will have to swap it back in. That's two I/O
operations utterly wasted (since in fact the old contents of the buffers
are no longer interesting, but the kernel has no way to know that).
I don't recommend tuning Postgres by randomly twiddling values you don't
understand the effects of. Bigger is not always better. Change one
thing at a time and measure the results.
> I am bothered about settings that might effect time taken by query execution
> (updates in particular)
Increasing checkpoint_segments (and possibly checkpoint_timeout) would
be more likely to help than increasing wal_buffers.
> Why do you think we need to create regular logs, only to handle database
> crashes??
Postgres is not designed on the assumption that that's an insignificant
consideration ;-)
Meanwhile, you didn't answer my question: what problems are you seeing?
regards, tom lane
"vinita bansal" <sagivini@hotmail.com> writes:
> Also, a particular query which was taking a min or less with postgres 7 is
> taking 15 mins now.
> Here is the query plan for the query both for postgres 7.4.6 and postgres 8:
It's hard to say anything about this when you only provided EXPLAIN and
not EXPLAIN ANALYZE results. The rowcount estimates are obviously a lot
different between 7.4 and 8.0, but are they more accurate or less so?
Can't tell from EXPLAIN.
> Why is this happening.
Have you vacuumed and/or analyzed all of the tables used in the query?
It looks like a statistical estimation problem to me. People frequently
complain that a new version has horrible performance because they forgot
to analyze their database after migrating ...
regards, tom lane