Hello, anyone can help on this matter, Alessandro highlighted? Thanks a lot, Nicola
2017-12-16 13:40 GMT+01:00 Alessandro Aste <alessandro.a...@gtt.net>: > Stephen, Rene - Thanks! > > Our experience teach us that above 20% of free space performance start to > seriously deteriorate. I'm not sure if this is related to index or table > fragmentation. We'll do our homework and we'll try to discover more. > > However we have identified a process potentially causing the free space > spike high but it's not related to long running transactions modifying > (update, insert) on those tables. What DEV is currently doing is create a > sort of de-normalized cache table like this: > * tablename is the table with the free space issue > > INSERT INTO cache_table > SELECT *, table2.<col>, ...table<N>.col FROM tablename > JOIN table2 on ... > ... > .. > JOIN table<N> on.... > > So no updates/inserts/deletes to tablename are involved but that query may > run for 15 (this is the AVG more or less) minutes holding an ACCESS SHARE > lock (I guess). > > We are wondering why we have that spike if we are not modifying the > relation and what we can suggest DEV from the query/db perspective to > alleviate/fix the issue. > > Again, thanks so much. > > > > Thanks and kind regards > Aste - alessandro.a...@gtt.net > > -----Original Message----- > From: Stephen Frost [mailto:sfr...@snowman.net] > Sent: 14 December 2017 16:13 > To: Nicola Contu <nicola.co...@gmail.com> > Cc: Rene Romero Benavides <rene.romer...@gmail.com>; pgsql-general@lists. > postgresql.org; Alessandro Aste <alessandro.a...@gtt.net> > Subject: Re: pgstattuple free_percent to high > > Greetings Nicola, > > * Nicola Contu (nicola.co...@gmail.com) wrote: > > I think tuning the autovacuum settings may increase performances and > > remove dead_tuples but as far as I know, the autovacuum runs a vacuum > analyze. > > The vacuum analyze won't touch the free_percent of the table. > > That's not entirely accurate. If all of the free space is at the *end* of > the relation then autovacuum will attempt to lock the relation and truncate > the table to give that free space back to the OS. > > On a table where all of the rows are regularly updated, eventually the > "live" data should end up towards the front of the relation and the end of > the relation will be all dead tuples, allowing the truncate to happen. If > you have tuples at the end of the relation that aren't ever updated but > they're "live" then we won't be able to truncate. > > The pg_freespacemap extension can be useful to see where the free space is > in the relation. > > There are a few tools out there that aren't part of core PostgreSQL that > you could consider using such as pg_repack and pg_squeeze. > > > So I'm trying to find a way to adjust the free percent for some tables > > without doing a manually full vacuum. > > We are now monitoring the free percent, so we may find the part of the > > code that can increase that value, but was wondering if there is > > anything on the postgres side to resolve this problem. > > Having some free space in the relation isn't a 'problem' and is a good > thing because it means that new rows (from either INSERTs or UPDATEs) have > a place to go that doesn't require extending the relation (which requires > an additional lock as well as some additional work). As for how much free > space is good to have and how much is too much depends on the specific > workload. > > Thanks! > > Stephen >
