Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id CA82A1337B54 for ; Tue, 28 Dec 2010 01:59:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 33948-02 for ; Tue, 28 Dec 2010 05:59:32 +0000 (UTC) X-Greylist: delayed 00:05:34.867825 by SQLgrey-1.7.6 Received: from mail.cobrasonic.com (250.57.145.202.adsl.static.ttn.net [202.145.57.250]) by mail.postgresql.org (Postfix) with ESMTP id 67A051337B43 for ; Tue, 28 Dec 2010 01:59:31 -0400 (AST) Received: from marcU45J (pc123239 [192.168.123.239]) (authenticated bits=0) by mail.cobrasonic.com (8.14.1/8.14.1) with ESMTP id oBS5ronN024256 for ; Tue, 28 Dec 2010 13:53:51 +0800 From: "marc.hsiao" To: Subject: How to turn autovacuum prevent wrap around run faster? Date: Tue, 28 Dec 2010 13:53:48 +0800 Message-ID: <001b01cba653$9910e9f0$cb32bdd0$@hsiao@cobrasonic.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001C_01CBA696.A73429F0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcumU5iqQUcRLC/SQGORbTKs642Bdw== Content-Language: zh-tw X-cobrasonic-MailScanner-Information: Please contact the ISP for more information X-cobrasonic-MailScanner-ID: oBS5ronN024256 X-cobrasonic-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-cobrasonic-MailScanner-From: marc.hsiao@cobrasonic.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.498 tagged_above=-10 required=5 tests=BAYES_05=-0.5, HTML_MESSAGE=0.001, MSGID_MULTIPLE_AT=0.001 X-Spam-Level: X-Archive-Number: 201012/313 X-Sequence-Number: 41765 This is a multi-part message in MIME format. ------=_NextPart_000_001C_01CBA696.A73429F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi ALL: My Database is for logs only, I almost have 30G data growth in my database. I Use partition table to store those data, those tables are partitioned by time column daily. My data only keep for three days. I will dump those data into dump file and drop the partition table after three days. My question is : The partition table that I have to backup and drop is running a long Prevent-Wraparound-Autovaccuum, Is any way to let the vacuum faster? The Prevent-Wraparound-Autovaccuum run very slow, almost 36 hours. My Disk IO is low. My Server config for vacuum list below: #--------------------------------------------------------------------------- --- # RESOURCE USAGE (except WAL) #--------------------------------------------------------------------------- --- shared_buffers = 4096MB # min 128kB temp_buffers = 384MB # min 800kB max_prepared_transactions = 100 # zero disables the feature work_mem = 100MB # min 64kB maintenance_work_mem = 192MB # min 1MB max_stack_depth = 4MB # min 100kB vacuum_cost_delay = 50ms # 0-100 milliseconds vacuum_cost_page_hit = 6 # 0-10000 credits vacuum_cost_limit = 1000 # 1-10000 credits #--------------------------------------------------------------------------- --- # AUTOVACUUM PARAMETERS #--------------------------------------------------------------------------- --- autovacuum = on # Enable autovacuum subprocess? 'on' autovacuum_max_workers = 3 # max number of autovacuum subprocesses autovacuum_naptime = 1 # time between autovacuum runs autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before vacuum autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for Regards. Marc Hsiao ------=_NextPart_000_001C_01CBA696.A73429F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi = ALL:

&nb= sp;

My = Database is for logs only, I almost have 30G data growth in my = database.

I Use = partition table to store those data, those tables are partitioned by = time column daily.

My data = only keep for three days.

I will = dump those data into dump file and drop the partition table after three = days.

&nb= sp;

My = question is :

The = partition table that I have to backup and drop is running a long = Prevent-Wraparound-Autovaccuum,

Is any = way to let the vacuum faster?

&nb= sp;

The = Prevent-Wraparound-Autovaccuum run very slow, almost 36 = hours.

My Disk = IO is low.

&nb= sp;

My = Server config for vacuum list below:

&nb= sp;

&nb= sp;

#-------= -----------------------------------------------------------------------

# = RESOURCE USAGE (except WAL)

#-------= -----------------------------------------------------------------------

 

shared_buffers =3D = 4096MB     # min 128kB

temp_buffers =3D = 384MB      # min 800kB

max_prepared_transactions =3D 100   = # zero disables the feature

work_mem = =3D 100MB        # min = 64kB

maintenance_work_mem =3D = 192MB    # min 1MB

max_stack_depth =3D = 4MB     # min 100kB

 

vacuum_cost_delay =3D 50ms    = # 0-100 milliseconds

vacuum_cost_page_hit =3D = 6    # 0-10000 credits

vacuum_cost_limit =3D 1000    = # 1-10000 credits

 

#---------------------------------------------= ---------------------------------

# = AUTOVACUUM PARAMETERS

#---------------------------------------------= ---------------------------------

 

autovacuum =3D on     # = Enable autovacuum subprocess?  'on'

autovacuum_max_workers =3D = 3    # max number of autovacuum = subprocesses

autovacuum_naptime =3D = 1    # time between autovacuum = runs

autovacuum_vacuum_scale_factor =3D 0.01 # = fraction of table size before vacuum

autovacuum_vacuum_cost_delay =3D 10ms # = default vacuum cost delay for

&nb= sp;

&nb= sp;

&nb= sp;

&nb= sp;



=
Regards.
  Marc = Hsiao

------=_NextPart_000_001C_01CBA696.A73429F0--