pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovaccum settings while Bulk Loading data
3+ messages / 2 participants
[nested] [flat]
* Autovaccum settings while Bulk Loading data
@ 2010-06-10 08:47 Ambarish Bhattacharya <b.ambarish@live.in>
2010-06-10 09:01 ` Re: Autovaccum settings while Bulk Loading data Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ambarish Bhattacharya @ 2010-06-10 08:47 UTC (permalink / raw)
To: pgsql-performance
Dear Experts,
I have data about half milllion to 1 million which is populated into the Postgres db using a batch job (A sql script consists of pl/pgsql functions and views) .
I am using PostgreSQL 8.3.5 on windows 2003 64-Bit machine.
It would be helpful if you can suggest me the appropriate Autovacuum settings for handling this large data as my autovacuum setting is hanging the entire process.
As of now I have the below Autovacuum settings in postgresql.conf file.
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 5
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 200
#autovacuum_vacuum_cost_limit = -1
--------------------------------------------------------------------------------------
Please provide you suggestion regarding the same.
Many thanks
_________________________________________________________________
The latest in fashion and style in MSN Lifestyle
http://lifestyle.in.msn.com/=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovaccum settings while Bulk Loading data
2010-06-10 08:47 Autovaccum settings while Bulk Loading data Ambarish Bhattacharya <b.ambarish@live.in>
@ 2010-06-10 09:01 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 15:27 ` Re: Autovaccum settings while Bulk Loading data Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2010-06-10 09:01 UTC (permalink / raw)
To: Ambarish Bhattacharya <b.ambarish@live.in>; +Cc: pgsql-performance
On 10/06/10 11:47, Ambarish Bhattacharya wrote:
> It would be helpful if you can suggest me the appropriate Autovacuum settings for handling this large data as my autovacuum setting is hanging the entire process.
What do you mean by "hanging the entire process"?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovaccum settings while Bulk Loading data
2010-06-10 08:47 Autovaccum settings while Bulk Loading data Ambarish Bhattacharya <b.ambarish@live.in>
2010-06-10 09:01 ` Re: Autovaccum settings while Bulk Loading data Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
@ 2010-06-10 15:27 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Heikki Linnakangas @ 2010-06-10 15:27 UTC (permalink / raw)
To: Ambarish Bhattacharya <b.ambarish@live.in>; +Cc: pgsql-performance
Please keep the mailing list CC'd, so that others can help.
On 10/06/10 15:30, Ambarish Bhattacharya wrote:
>> On 10/06/10 11:47, Ambarish Bhattacharya wrote:
>>> It would be helpful if you can suggest me the appropriate Autovacuum settings for handling this large data as my autovacuum setting is hanging the entire process.
>>
>> What do you mean by "hanging the entire process"?
>
> Hanging the entire process means...the autovacuum and auto analyzes starts and after that there is no acitivity i could see in the postgres log related to the bulk loading and when checked the postgres processes from the task manager i could see few of the postgres porcess are still running and had to be killed from there..normal shut down in not happening in this case...
You'll have to provide a lot more details if you want people to help
you. How do you bulk load the data? What kind of log messages do you
normally get in the PostgreSQL log related to bulk loading?
Autovacuum or autoanalyze should not interfere with loading data, even
if it runs simultaneously.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-06-10 15:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-10 08:47 Autovaccum settings while Bulk Loading data Ambarish Bhattacharya <b.ambarish@live.in>
2010-06-10 09:01 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 15:27 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.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