[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Autovacuum Issue
From: Raghavendra <raghavendra.rao () enterprisedb ! com>
Date: 2011-11-23 6:22:38
Message-ID: CA+h6AhgWR=F-i9xMLfNxPKSWxxizwN7F2O=0qP511eMXk68e_Q () mail ! gmail ! com
[Download RAW message or body]
On Wed, Nov 23, 2011 at 11:25 AM, J Ramesh Kumar <rameshj1977@gmail.com>wrote:
> Hi,
>
> My application is performing 1600 inserts per second and 7 updates per
> second. The updates occurred only in a small table which has only 6 integer
> columns. The inserts occurred in all other daily tables. My application
> creates around 75 tables per day. No updates/deletes occurred in those 75
> daily tables (only inserts and drop tables if older than 40 days). Since
> only inserts in the daily tables, I disabled autovacuum in the conf file
> and I can see it is off stat using show command.
>
> *sasdb=# show "autovacuum";
> autovacuum
> ------------
> off
> (1 row)*
>
> But the autovacuum is running frequently and it impact the performance of
> my system(high CPU). You can see the autovacuum in the pg_stat_activity.
> *
> sasdb=# select current_query from pg_stat_activity where current_query
> like 'autovacuum%';
> ** current_query **
>
> ------------------------------**------------------------------**
> ---------------------------
> autovacuum: VACUUM public.xxxxx**_17_Oct_11 (to prevent wraparound)
> autovacuum: VACUUM public.**xxxxx**_17_Oct_11 (to prevent wraparound)
> autovacuum: VACUUM public.**xxxxx**_17_Oct_11 (to prevent wraparound)
> (3 rows)
>
>
> *
Its pretty clear, its to prevent tranx wrap-around.
autovacuum_freeze_max_age (integer)
Specifies the maximum age (in transactions) that a table's pg_class.
relfrozenxid field can attain before a VACUUM operation is forced to
prevent transaction ID wraparound within the table. Note that the system
will launch autovacuum processes to prevent wraparound even when autovacuum
is otherwise disabled.
http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum.html
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
> **Why the autovacuum is running even though, I disabled ? Am I miss
> anything ?
>
> And also please share your views on my decision about disable autovacuum
> for my application. I am planning to run vacuum command daily on that small
> table which has frequent updates.
>
> Thanks,
> Ramesh
>
[Attachment #3 (text/html)]
<font class="Apple-style-span" face="'trebuchet ms', \
sans-serif"><br></font><div class="gmail_quote">On Wed, Nov 23, 2011 at 11:25 AM, J \
Ramesh Kumar <span dir="ltr"><<a \
href="mailto:rameshj1977@gmail.com">rameshj1977@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;">Hi,<br><br>My application is performing 1600 inserts per \
second and 7 updates per second. The updates occurred only in a small table which has \
only 6 integer columns. The inserts occurred in all other daily tables. My \
application creates around 75 tables per day. No updates/deletes occurred in those 75 \
daily tables (only inserts and drop tables if older than 40 days). Since only inserts \
in the daily tables, I disabled autovacuum in the conf file and I can see it is off \
stat using show command. <br>
<br><i style="color:rgb(0,0,153)">sasdb=# show "autovacuum";<br> autovacuum \
<br>------------<br> off<br>(1 row)</i><br><br>But the autovacuum is running \
frequently and it impact the performance of my system(high CPU). You can see the \
autovacuum in the pg_stat_activity. <br>
<i style="color:rgb(0,0,153)"><br>sasdb=# select current_query from pg_stat_activity \
where current_query like 'autovacuum%';<br> \
</i><i style="color:rgb(0,0,153)"> current_query </i><i \
style="color:rgb(0,0,153)"> <br>
------------------------------</i><i \
style="color:rgb(0,0,153)">------------------------------</i><i \
style="color:rgb(0,0,153)">---------------------------<br> autovacuum: VACUUM \
public.xxxxx</i><i style="color:rgb(0,0,153)">_17_Oct_11 (to prevent wraparound)<br> \
autovacuum: VACUUM public.</i><i style="color:rgb(0,0,153)">xxxxx</i><i \
style="color:rgb(0,0,153)">_17_Oct_11 (to prevent wraparound)<br>
autovacuum: VACUUM public.</i><i style="color:rgb(0,0,153)">xxxxx</i><i \
style="color:rgb(0,0,153)">_17_Oct_11 (to prevent wraparound)<br> (3 \
rows)<br><br><br></i></blockquote><div><br></div><div>Its pretty clear, its to \
prevent tranx wrap-around. </div><div><br></div><div><dt style="font-family: verdana, \
sans-serif; background-color: rgb(255, 255, 255); font-size: medium; ">
<tt class="VARNAME">autovacuum_freeze_max_age</tt> (<tt \
class="TYPE">integer</tt>)</dt><dd style="font-family: verdana, sans-serif; \
background-color: rgb(255, 255, 255); font-size: medium; "><p>Specifies the maximum \
age (in transactions) that a table's <tt class="STRUCTNAME">pg_class</tt>.<tt \
class="STRUCTFIELD">relfrozenxid</tt> field can attain before a <tt \
class="COMMAND">VACUUM</tt> operation is forced to prevent transaction ID wraparound \
within the table. Note that the system will launch autovacuum processes to prevent \
wraparound even when autovacuum is otherwise disabled.</p>
<p><a href="http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum. \
html">http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum.html</a></p></dd></div><div><div><font \
face="'trebuchet ms', sans-serif"><div>
---</div><div>Regards,</div><div>Raghavendra</div><div>EnterpriseDB \
Corporation</div><div>Blog: <a href="http://raghavt.blogspot.com/" \
target="_blank">http://raghavt.blogspot.com/</a></div></font></div><br><br></div><div>
<br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex;"><i \
style="color:rgb(0,0,153)"></i>Why the autovacuum is running even though, I disabled \
? Am I miss anything ? <br>
<br>And also please share your views on my decision about disable autovacuum for my \
application. I am planning to run vacuum command daily on that small table which has \
frequent updates. <br> <br>Thanks,<br>Ramesh<br>
</blockquote></div><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic