Dmitry, On Fri, Apr 25, 2014 at 11:31 AM, Дмитрий Шалашов <skaurus@xxxxxxxxx> wrote: > Thanks, we'll try new autovacuum settings! I think things with vacuum will be much better. If not, try to find out if you have long running transaction (several minutes or more) and try to avoid such them. > > First, I have to say that load comes and go in waves - we don't yet > understood why. > All new waves have that behaviour - free disk, idle cpu. > First such wave was before checkpoit_segments change, next waves after. That could be a complicate problem caused by many things from suboptimal sql-queries to network issues, could be not easy to guess. - how many locks you have during the wave in comparison with normal workload? - do you use some connection pooling (pgbouncer etc)? - how about long running transactions I have mentioned above? - are you using pg_stat_statements or any other method for detecting slow queries? > > > Best regards, > Dmitriy Shalashov > > > 2014-04-25 13:22 GMT+04:00 Ilya Kosmodemiansky > <ilya.kosmodemiansky@xxxxxxxxxxxxxxxxxxxxxxxxx>: > >> On Fri, Apr 25, 2014 at 10:29 AM, Дмитрий Шалашов <skaurus@xxxxxxxxx> >> wrote: >> > Previously during load disk was 100% busy; now we have around 100 active >> > state queries, 100% loaded proc, but disk is virtually idle... >> >> That was happen after changing checkpoit_segments setting? >> >> > >> > >> > Best regards, >> > Dmitriy Shalashov >> > >> > >> > 2014-04-25 12:22 GMT+04:00 Дмитрий Шалашов <skaurus@xxxxxxxxx>: >> > >> >> Hi Ilya! >> >> >> >> > Actually, thise two things are tightly bound and there is no chance >> >> > to >> >> > avoid vacuum, you can only postpone it, this kind of work eventually >> >> > supposed to be done. >> >> >> >> I understand that autovacuum has to be done, but not right after >> >> previous >> >> autovacuum? And then again and again. >> >> And after cancelling that first autovacuum I started another one by >> >> hand; >> >> from there no autovacuum was cancelled. >> >> >> >> > ionice autovacuum instead of mission critical ckeckpointer or >> >> > bgwriter >> >> Yeah, that was desperate. I restarted server when I had a chance - to >> >> drop >> >> my ionice settings back to defaults. >> >> >> >> > Which exact values have you in the following settings: >> >> >> >> autovacuum_analyze_scale_factor = 0.1 >> >> autovacuum_analyze_threshold = 50 >> >> 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 = 50 >> >> log_autovacuum_min_duration = 0 >> >> >> >> All defaults except last one I believe. >> >> >> >> >> >> Minwhile I noticed in the night logs: >> >> checkpoints are occurring too frequently (138 seconds apart) >> >> Consider increasing the configuration parameter "checkpoint_segments". >> >> >> >> Increased checkpoint_segments to 256 and reloaded config. >> >> >> >> >> >> Best regards, >> >> Dmitriy Shalashov >> >> >> >> >> >> 2014-04-25 12:12 GMT+04:00 Ilya Kosmodemiansky >> >> <ilya.kosmodemiansky@xxxxxxxxxxxxxxxxxxxxxxxxx>: >> >> >> >>> Hi Dmitry, >> >>> >> >>> On Fri, Apr 25, 2014 at 9:47 AM, Дмитрий Шалашов <skaurus@xxxxxxxxx> >> >>> wrote: >> >>> > cancelled autovacuum and it seems to help. >> >>> >> >>> > In the morning autovacuum was back. And then it finished and I gone >> >>> > to >> >>> > work. >> >>> >> >>> Actually, thise two things are tightly bound and there is no chance to >> >>> avoid vacuum, you can only postpone it, this kind of work eventually >> >>> supposed to be done. >> >>> >> >>> What you really need to do as a first thing - configure your >> >>> autovacuum aggressively enough and then mayde ionice autovacuum >> >>> instead of mission critical ckeckpointer or bgwriter. >> >>> >> >>> Which exact values have you in the following settings: >> >>> >> >>> autovacuum_analyze_scale_factor >> >>> autovacuum_analyze_threshold >> >>> autovacuum_freeze_max_age >> >>> autovacuum_max_workers >> >>> autovacuum_naptime >> >>> autovacuum_vacuum_cost_delay >> >>> autovacuum_vacuum_cost_limit >> >>> autovacuum_vacuum_scale_factor >> >>> autovacuum_vacuum_threshold >> >>> log_autovacuum_min_duration >> >>> >> >>> ? >> >>> >> >>> Best regards, Ilya >> >>> > >> >>> > Best regards, >> >>> > Dmitriy Shalashov >> >>> >> >>> >> >>> >> >>> -- >> >>> Ilya Kosmodemiansky, >> >>> >> >>> PostgreSQL-Consulting.com >> >>> tel. +14084142500 >> >>> cell. +4915144336040 >> >>> ik@xxxxxxxxxxxxxxxxxxxxxxxxx >> >> >> >> >> > >> >> >> >> -- >> Ilya Kosmodemiansky, >> >> PostgreSQL-Consulting.com >> tel. +14084142500 >> cell. +4915144336040 >> ik@xxxxxxxxxxxxxxxxxxxxxxxxx > > -- Ilya Kosmodemiansky, PostgreSQL-Consulting.com tel. +14084142500 cell. +4915144336040 ik@xxxxxxxxxxxxxxxxxxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
