Search Postgresql Archives

Re: How to avoid Force Autovacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All,

select * from pg_database where datname = 'template0';
-[ RECORD 1 ]-+------------------------------------
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 12865
datfrozenxid  | 2025732249
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}

select * from pg_stat_database where datname = 'template0';
-[ RECORD 1 ]--+------------------------------
datid          | 12865
datname        | template0
numbackends    | 0
xact_commit    | 320390
xact_rollback  | 7
blks_read      | 3797
blks_hit       | 9458783
tup_returned   | 105872028
tup_fetched    | 1771782
tup_inserted   | 10
tup_updated    | 457
tup_deleted    | 10
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2013-04-19 19:22:39.013056-07


select name, setting from pg_settings where name ~ 'vacuum';
              name               |  setting  
---------------------------------+-----------
 autovacuum                      | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold    | 50000
 autovacuum_freeze_max_age       | 200000000
 autovacuum_max_workers          | 3
 autovacuum_naptime              | 60
 autovacuum_vacuum_cost_delay    | 20
 autovacuum_vacuum_cost_limit    | -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold     | 50000
 log_autovacuum_min_duration     | 0
 vacuum_cost_delay               | 0
 vacuum_cost_limit               | 200
 vacuum_cost_page_dirty          | 20
 vacuum_cost_page_hit            | 1
 vacuum_cost_page_miss           | 10
 vacuum_defer_cleanup_age        | 0
 vacuum_freeze_min_age           | 50000000
 vacuum_freeze_table_age         | 150000000
(19 rows)


Our Physical RAM size is 256GB

Please note : we are executing standard vacuum daily (Manual Vacuum) -- Vacuum freeze analyze.. 

But during manual vacuum -- the load is normal -- for all databases ( load is in 1 to 2) 

Load increased to 200 during autovacuum process..

[Previously i had set maintenance_work_mem as 256MB at that time manual vacuum increased the load to 300. Then only i have increased the maintenance work memory to 2GB, Now manual vacuum is fine, Load is normal during vacuum process, so our application is fine during vacuum process also ]

Now the problem is autovacuum.. why it was invoked and increased the load? How to avoid this? 






On Fri, Aug 9, 2013 at 5:21 AM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote:
On Thu, Aug 8, 2013 at 11:18 AM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
> There were some fixes for autovacuum problems in 9.2.3.  Some other
> fixes will be coming when 9.2.5 is released.  Many of your problems
> are likely to go away by staying up-to-date on minor releases.
>
> By setting this so high, you are increasing the amount of work
> autovacuum will need to do when it does work on a table.  A smaller
> value tends to give less "bursty" performance.  Also, any small,
> frequently-updated tables may bloat quite a bit in 50000
> transactions.
>
> Each autovacuum worker will allocate this much RAM.  If all of your
> autovacuum workers wake up at once, would losing 2GB for each one
> from your cache cause a significant performance hit?  (Since you
> didn't say how much RAM the machine has, it's impossible to tell.)
>
> What does running this in psql this show?:
>
> \x on
> select * from pg_database where datname = 'template0';
> select * from pg_stat_database where datname = 'template0';

In addition to Kevin's notes, I think it is also worth to look at the
result of the query below.

select name, setting from pg_settings
where name ~ 'vacuum' and setting <> reset_val;

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@xxxxxxxxx



--
Best Regards,
Vishalakshi.N


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux