Re: 8.2 Autovacuum BUG ?
Yes so many wals are continuing to be produced. Deleting the wals after a backup of the database. Yes archiving mode is on. And the warning message in log file is " checkpoints are frequently occurring (1second apart). Consider increasing checkpoint_segements parameter". My doubt is previously the same are the parameters which are reflected as of now. Then what is the point in considering altering those values. Correct me if I am wrong. Regards, Pavan On Jan 23, 2018 7:47 PM, "Andreas Kretschmer" wrote: Am 23.01.2018 um 12:51 schrieb pavan95: > Hi Andreas, > > Yes I'm facing problem because of this huge WAL(archive log) generation. As > it is seriously consuming a lot of disk space almost close to 50GB per day > even if the DML's don't have that impact in this WAL generation. > > Previously the archive_log size is nearly 2 to 3 GB a day. Now with the > same > set of DML's how is it being generated to 50GB is my burning doubt. > Will so many wals continue to be produced? > I just wanted to know how to stabilize this issue, as checking and deleting > the archive logs on hourly basis is not a good idea. > Don't delete wal's! Finally, I'm looking how to reduce this back to normal. Thanks in Advance. > have you set archive_mode to on and defined an archive_command? Wal-files will be reused after 2 checkpoints. Is there something in the logs? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: 8.2 Autovacuum BUG ?
Hi David, If it's yes what needs to be done in order to stabilize this issue?? Thanks in advance. Regards, Pavan On Jan 23, 2018 8:15 PM, "David G. Johnston" wrote: > On Tue, Jan 23, 2018 at 7:39 AM, Pavan Teja > wrote: > >> " checkpoints are frequently occurring (1second apart). Consider >> increasing checkpoint_segements parameter". >> > > The custom on these lists is to bottom or inline post. > > This tends to appear when someone decide to write a load script of the > form: > > INSERT INTO tbl (cols) VALUES (...); > INSERT INTO tbl (cols) VALUES (...); > [repeat many, many, times] > > (note the lack of BEGIN/END, single transaction help mitigate it somewhat) > > David J. > >
Re: 8.2 Autovacuum BUG ?
On Jan 23, 2018 9:37 PM, "Claudio Freire" wrote: On Tue, Jan 23, 2018 at 11:39 AM, Pavan Teja wrote: > Yes so many wals are continuing to be produced. > > Deleting the wals after a backup of the database. > > Yes archiving mode is on. And the warning message in log file is > > " checkpoints are frequently occurring (1second apart). Consider > increasing checkpoint_segements parameter". > > My doubt is previously the same are the parameters which are reflected as > of now. Then what is the point in considering altering those values. > Correct me if I am wrong. > You can use pg_xlogdump to inspect those logs and see which relations/transactions are generating so much WAL. Then you can hunt within your apps which code is responsible for that traffic, or whether it in fact is autovacuum. Hi Claudio, Is pg_xlogdump available for postgres 9.1, as my current production is postgres 9.1. Yes investigated in that area, found DML's and also autovacuum statements for some relations. And the DML's are the same before this huge WAL traffic and normal WAL traffic. Anyways, thanks for your timely response 😊 Regards, Pavan
Re: 8.2 Autovacuum BUG ?
On Jan 24, 2018 7:57 PM, "Claudio Freire" wrote: On Wed, Jan 24, 2018 at 8:50 AM, pavan95 wrote: > Hello all, > > One more interesting observation made by me. > > I have ran the below query(s) on production: > > SELECT > relname, > age(relfrozenxid) as xid_age, > pg_size_pretty(pg_table_size(oid)) as table_size > FROM pg_class > WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 > ORDER BY age(relfrozenxid) DESC ; > relname | > xid_age | table_size > > +-+ > *hxx* | > 7798262 | 3245 MB > hrx | > 7797554 | 4917 MB > irxx| > 7796771 | 2841 MB > hr_ | 7744262 | > 4778 MB > reimbxxx | 6767712 | 1110 MB > > show autovacuum_freeze_max_age; > autovacuum_freeze_max_age > --- > 2 > (1 row) > You seem to be rather far from the freeze_max_age. Unless you're consuming txids at a very high rate, I don't think that's your problem. Hi , Yes, but why doing vacuum freeze of a table is causing a rapid archiving?? Any idea?? Regards, Pavan
Re: 8.2 Autovacuum BUG ?
On Jan 26, 2018 3:00 AM, "Alvaro Herrera" wrote: pavan95 wrote: > Hi Álvaro Herrera, > > Please find the corresponding output: OK, these settings look pretty normal, so they don't explain your problem. What is checkpoint_segments set to? And checkpoint_timeout? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services Hi, checkpoint_segments are set to '3' & checkpoint_timeout was set to '5min'. Regards, Pavan.
Re: 8.2 Autovacuum BUG ?
On Jan 26, 2018 6:02 AM, "Pavan Teja" wrote: On Jan 26, 2018 3:00 AM, "Alvaro Herrera" wrote: pavan95 wrote: > Hi Álvaro Herrera, > > Please find the corresponding output: OK, these settings look pretty normal, so they don't explain your problem. What is checkpoint_segments set to? And checkpoint_timeout? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services Hi, checkpoint_segments are set to '3' & checkpoint_timeout was set to '5min'. Regards, Pavan. Any clue??? Regards, Pavan.
Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
Thanks a lot! I will have a look On Tue, May 22, 2018, 11:53 PM Justin Pryzby wrote: > On Tue, May 22, 2018 at 03:51:44AM -0700, pavan95 wrote: > > Please find the output of explain(analyze,buffers) for the whole query in > > the below link. > > > Seq Scan on res_users users (cost=750.92..1,836.69 rows=249 width=15) > (actual time=3.962..17.544 rows=67 loops=1) > > Not sure but would you try creating an index on: > res_users.res_employee_id > > > Seq Scan on res_users user1 (cost=0.00..58.03 rows=1,303 width=15) > (actual time=0.002..0.002 rows=1 loops=1) > > Also the planner's estimate for table:res_users is off by 1300x..so you > should > probably vacuum analyze it then recheck. I don't think we know what > version > postgres you have, but last week's patch releases include a fix which may > be > relevant (reltuples including dead tuples). > > Also I don't know the definition of this table or its indices: > tms_workflow_history > > ..but it looks like an additional or modified index or maybe clustering the > table on existing index might help (active? is_final_approver?) > Or maybe this should be 3 separate indices rather than composite index? > Perhaps some of those could be BRIN indices, depending on postgres version > > Justin >
