Обсуждение: pgstattuple free_percent to high

Hello,

We are running postgres 9.6.6 on centos 7.

We have a large DB (180GB) with about 1200 tables.

We have autovacuum set with default values and we are seeing that for some tables the free percent goes really high (51%) and we need to daily full vacuum those tables.

dbanme=# SELECT * FROM pgstattuple('tablename');

 table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------

 2119548928 |      526658 | 1023569149 |         48.29 |                0 |              0 |                  0 | 1083485292 |        51.12

(1 row)

I guess this is because of long queries but I'm not really sure.

Do you know how to avoid this problem and what can cause it?

Do you think that increasing the autovacuum settings for those tables would alleviate the issue?

Thanks,

Nicola

Check for long running transactions modifying (update, insert) on those tables ,using pg_stat_activity.

 

Tweak these storage parameters for such tables:

autovacuum_vacuum_cost_delay : decrease it (the autovacuum daemon goes to sleep less often )

autovacuum_vacuum_threshold : decrease it (to trigger more frequent autovacuum activations )

autovacuum_vacuum_cost_limit : increase it (to allow the autovacuum daemon to work for longer periods)

autovacuum_vacuum_scale_factor : decrease it (to trigger more autovacuum activations when this percentage of a table has been modified)

For example I've set these parameters for one table experiencing long running transactions, and for its access patterns have worked:

autovacuum_vacuum_cost_delay=5, autovacuum_vacuum_threshold=50,autovacuum_vacuum_cost_limit=3000, autovacuum_vacuum_scale_factor=0.01

but these settings are very particular for each usage pattern.

Take into account that more activity from autovacuum means more IO, more CPU usage, you might also benefit from setting autovacuum_work_mem to a higher setting if the available RAM allows it, to give more RAM to the autovacuum daemon.

2017-12-13 9:49 GMT-06:00 Nicola Contu <nicola.contu@gmail.com>:

Hello,

We are running postgres 9.6.6 on centos 7.

We have a large DB (180GB) with about 1200 tables.

We have autovacuum set with default values and we are seeing that for some tables the free percent goes really high (51%) and we need to daily full vacuum those tables.

dbanme=# SELECT * FROM pgstattuple('tablename');

 table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------

 2119548928 |      526658 | 1023569149 |         48.29 |                0 |              0 |                  0 | 1083485292 |        51.12

(1 row)

I guess this is because of long queries but I'm not really sure.

Do you know how to avoid this problem and what can cause it?

Do you think that increasing the autovacuum settings for those tables would alleviate the issue?

Thanks,

Nicola

--

El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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.aste@gtt.net

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: 14 December 2017 16:13
To: Nicola Contu <nicola.contu@gmail.com>
Cc: Rene Romero Benavides <rene.romero.b@gmail.com>; pgsql-general@lists.postgresql.org; Alessandro Aste <alessandro.aste@gtt.net>
Subject: Re: pgstattuple free_percent to high

Greetings Nicola,

* Nicola Contu (nicola.contu@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