Re: [HACKERS] System catalog vacuum issues

2013-08-22 Thread Jim Nasby

On 8/19/13 7:23 PM, Sergey Konoplev wrote:

On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipovarhi...@dc.baikal.ru  wrote:

Do you have some processes that intensively create tables or columns
and then delete them or create them in transaction and rollback the
transaction?


There are many processes that create and drop temporary tables.

That is the problem. Exactly what Jim was writing about. Autovacuum
have no chance to clean dead tuples at the end of the table because
they are created too intensively. In the latest versions autovacuum
behaves so it would stop working when a concurrent lock is acquired.
As he suggested you should use vacuum in cron, however it might make
other procecess, that create/drop tables to wait.


Hrm... even if vacuum cost delay is set? I recall some talk about doing some 
minimal waiting for the lock, but thought that'd only happen if cost delay was 
0.

That really doesn't matter though. The whole idea of a cron'd vacuum is to 
*stop bloat from happening to begin with*. If there's no bloat to begin with, 
getting the lock to truncate will be a non-issue.
--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-22 Thread Sergey Konoplev
On Wed, Aug 21, 2013 at 2:33 PM, Jim Nasby j...@nasby.net wrote:
 That is the problem. Exactly what Jim was writing about. Autovacuum
 have no chance to clean dead tuples at the end of the table because
 they are created too intensively. In the latest versions autovacuum
 behaves so it would stop working when a concurrent lock is acquired.
 As he suggested you should use vacuum in cron, however it might make
 other procecess, that create/drop tables to wait.


 Hrm... even if vacuum cost delay is set? I recall some talk about doing some
 minimal waiting for the lock, but thought that'd only happen if cost delay
 was 0.

 That really doesn't matter though. The whole idea of a cron'd vacuum is to
 *stop bloat from happening to begin with*. If there's no bloat to begin
 with, getting the lock to truncate will be a non-issue.

Well, according to the pgstattuple log OP showed, free percent jumps
from 1.82 to 70.07 in one minute, so I suppose an empty tail is
inevitable anyway, so there should be locks to truncate by vacuum, if
I understand things correct.

-- 
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...@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-19 Thread Sergey Konoplev
On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
 Do you have some processes that intensively create tables or columns
 and then delete them or create them in transaction and rollback the
 transaction?

 There are many processes that create and drop temporary tables.

That is the problem. Exactly what Jim was writing about. Autovacuum
have no chance to clean dead tuples at the end of the table because
they are created too intensively. In the latest versions autovacuum
behaves so it would stop working when a concurrent lock is acquired.
As he suggested you should use vacuum in cron, however it might make
other procecess, that create/drop tables to wait.

Another solution would be to factor out the temp tables usage from the
logic. Could you please describe what are you using temp tables for?
There might be another, more effective solution of this issue.

-- 
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...@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov

On 08/16/2013 10:44 AM, Vlad Arkhipov wrote:

On 08/15/2013 03:27 AM, Jim Nasby wrote:

On 8/14/13 12:31 AM, Vlad Arkhipov wrote:
I used to use VACUUM FULL periodically to resolve the issue, but the 
problem arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 
2013-08-07.


How much non-temporary DDL do you do? It's possible that you end up 
with a tuple at the end of the table for a non-temporary object. One 
of those would stay valid for quite some time, and if you're unlucky 
then you'll end up with another long-lived row farther down the 
table, etc, etc.


Depending on how frequently you're creating temp objects, autovac 
might not be able to keep up. Assuming that a manual vacuum doesn't 
take too long it might be a good idea to cron a manual vacuum (NOT 
FULL) of that table once a minute.

Not much. 1-2 tables per day.




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov

On 08/15/2013 05:06 AM, Sergey Konoplev wrote:

On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:

I used to use VACUUM FULL periodically to resolve the issue, but the problem
arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.

 date| relpages | reltuples | table_len | tuple_count | tuple_percent
| dead_tuple_count | dead_tuple_len | free_space | free_percent |
autovacuum_count
+--+---+---+-+---+--+++--+--
  2013-08-08 |39029 |109096 | 319725568 |   37950 |  1.66
|52540 |7355600 |  296440048 |92.72 |

Are you sure you did VACUUM FULL pg_attribute on Aug 7, could you
please confirm that free_percent arises from 0 to 92% in one day?

Do you have some processes that intensively create tables or columns
and then delete them or create them in transaction and rollback the
transaction?

Absolutely. Here is 1-minute statistics on this table. VACUUM FULL was 
done on 2013-08-16 00:35:00.

There are many processes that create and drop temporary tables.

 time  |  reltuples  | table_len | tuple_count 
| tuple_percent | dead_tuple_count |  dead_len  | free_space | 
free_percent | autovacuum_count

---+-+---+-+---+--+++--+--
 2013-08-16 00:33:01.977405+09 | 1.50021e+07 | 2211 MB   | 38981 
|  0.24 | 15505917 | 2070 MB| 8339 kB | 0.37 
| 7463
 2013-08-16 00:34:01.718696+09 | 1.50021e+07 | 2211 MB   | 38875 
|  0.23 | 15505952 | 2070 MB| 8349 kB | 0.37 
| 7463
 2013-08-16 00:35:01.570965+09 |   38875 | 5664 kB   | 38875 
| 93.84 |   46 | 6440 bytes | 19 kB | 0.34 
| 7463
 2013-08-16 00:36:01.658131+09 |   38875 | 5664 kB   | 38875 
| 93.84 |   46 | 6440 bytes | 19 kB | 0.34 
| 7463

...
 2013-08-16 08:10:01.201473+09 |   47950 | 52 MB | 47685 
| 12.22 |   318481 | 43 MB  | 229 kB | 0.43 
| 7493
 2013-08-16 08:11:01.411891+09 |   47950 | 54 MB | 47776 
| 11.86 |   329589 | 44 MB  | 333 kB |  0.6 
| 7493
 2013-08-16 08:12:01.623495+09 |   48036 | 56 MB | 47816 
| 11.47 |   343932 | 46 MB  | 199 kB | 0.35 
| 7495
 2013-08-16 08:13:01.837192+09 |   48036 | 58 MB | 47903 
| 11.11 |   356488 | 48 MB  | 286 kB | 0.48 
| 7495
 2013-08-16 08:14:02.041228+09 |   48036 | 59 MB | 47899 
| 10.82 |   366939 | 49 MB  | 370 kB | 0.61 
| 7495
 2013-08-16 08:15:01.254325+09 |   48036 | 61 MB | 48065 
| 10.61 |   376192 | 50 MB  | 420 kB | 0.68 
| 7495
 2013-08-16 08:16:01.557785+09 |   48210 | 62 MB | 48290 
| 10.36 |   386019 | 52 MB  | 696 kB | 1.09 
| 7496
 2013-08-16 08:17:01.774188+09 |   48210 | 64 MB | 48330 
| 10.14 |   392236 | 52 MB  | 1188 kB | 1.82 
| 7496
 2013-08-16 08:18:01.977503+09 |   48210 | 65 MB | 48370 
|  9.87 |79643 | 11 MB  | 46 MB |70.07 
| 7496
 2013-08-16 08:19:01.154589+09 |   48210 | 68 MB | 48550 
|  9.55 |27483 | 3757 kB| 55 MB |81.55 
| 7496
 2013-08-16 08:20:01.321973+09 |   48333 | 69 MB | 48694 
|  9.41 |42512 | 5812 kB| 54 MB |78.83 
| 7497
 2013-08-16 08:21:01.48612+09  |   48333 | 69 MB | 48831 
|  9.43 |43172 | 5902 kB| 54 MB |78.67 
| 7497
 2013-08-16 08:22:01.668103+09 |   48926 | 69 MB | 48947 
|  9.46 |22677 | 3100 kB| 57 MB |82.72 
| 7498
 2013-08-16 08:23:01.83524+09  |   48962 | 69 MB | 48914 
|  9.45 | 8655 | 1183 kB| 59 MB | 85.5 
| 7499

...
 2013-08-16 10:22:01.590888+09 | 52114 | 131 MB|   52395 
|  5.33 |   866015 | 116 MB   | 1045 kB| 0.78 
| 7550
 2013-08-16 10:23:01.908792+09 | 52114 | 133 MB|   52579 
|  5.29 |   560495 | 75 MB| 44 MB  | 33.44 
| 7550
 2013-08-16 10:24:01.207538+09 | 52114 | 134 MB|   52566 
|  5.22 |   222138 | 30 MB| 92 MB  | 68.77 
| 7550
 2013-08-16 10:25:01.485565+09 | 52114 | 136 MB|   52637 
|  5.17 |25493 | 3485 kB  | 

Re: [HACKERS] System catalog vacuum issues

2013-08-14 Thread Jim Nasby

On 8/14/13 12:31 AM, Vlad Arkhipov wrote:

I used to use VACUUM FULL periodically to resolve the issue, but the problem 
arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.


How much non-temporary DDL do you do? It's possible that you end up with a 
tuple at the end of the table for a non-temporary object. One of those would 
stay valid for quite some time, and if you're unlucky then you'll end up with 
another long-lived row farther down the table, etc, etc.

Depending on how frequently you're creating temp objects, autovac might not be 
able to keep up. Assuming that a manual vacuum doesn't take too long it might 
be a good idea to cron a manual vacuum (NOT FULL) of that table once a minute.


dcdb=# select date, relpages, reltuples, table_len, tuple_count, tuple_percent, 
dead_tuple_count, dead_tuple_len, free_space, free_percent, autovacuum_count 
from public.table_statistics where relname = 'pg_attribute' order by date;
 date| relpages | reltuples | table_len | tuple_count | tuple_percent | 
dead_tuple_count | dead_tuple_len | free_space | free_percent | autovacuum_count
+--+---+---+-+---+--+++--+--
  2013-08-08 |39029 |109096 | 319725568 |   37950 | 1.66 |  
  52540 |7355600 |  296440048 |92.72 | 6359
  2013-08-09 |12382 | 95848 | 101433344 |   38232 | 5.28 |  
  57443 |8042020 |   83862864 |82.68 | 6711
  2013-08-10 |11365 |105073 |  93102080 |   37789 | 5.68 |  
  65599 |9183860 |   74483104 |   80 | 7002
  2013-08-12 | 9447 | 95289 |  77389824 |   37811 | 6.84 |  
  57154 |8001560 |   60479736 |78.15 | 7161
  2013-08-13 |47841 | 82877 | 391913472 |   38536 | 1.38 |  
  30461 |4264540 |  369093756 |94.18 | 7347
  2013-08-14 |70265 |104926 | 575610880 |   38838 | 0.94 |  
  34649 |4850860 |  546449480 |94.93 | 7398
(6 rows)

Autovacuum is running on this table, however it keeps growing.

On 08/06/2013 09:35 PM, Tom Lane wrote:

Vlad Arkhipov arhi...@dc.baikal.ru writes:

On 08/06/2013 04:26 PM, Sergey Konoplev wrote:

What pgstattuple shows on this table?

dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
   table_len  | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
   6363938816 |   48786 |   6830040 |  0.11 | 1459439 |
204321460 |   3.21 | 5939017376 | 93.32
(1 row)

So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space.  I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses.  There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you.  Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.

regards, tom lane



--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-14 Thread Sergey Konoplev
On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
 I used to use VACUUM FULL periodically to resolve the issue, but the problem
 arises again in 2-3 months.
 Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.

 date| relpages | reltuples | table_len | tuple_count | tuple_percent
 | dead_tuple_count | dead_tuple_len | free_space | free_percent |
 autovacuum_count
 +--+---+---+-+---+--+++--+--
  2013-08-08 |39029 |109096 | 319725568 |   37950 |  1.66
 |52540 |7355600 |  296440048 |92.72 |

Are you sure you did VACUUM FULL pg_attribute on Aug 7, could you
please confirm that free_percent arises from 0 to 92% in one day?

Do you have some processes that intensively create tables or columns
and then delete them or create them in transaction and rollback the
transaction?

-- 
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...@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-13 Thread Vlad Arkhipov
I used to use VACUUM FULL periodically to resolve the issue, but the 
problem arises again in 2-3 months.

Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.

dcdb=# select date, relpages, reltuples, table_len, tuple_count, 
tuple_percent, dead_tuple_count, dead_tuple_len, free_space, 
free_percent, autovacuum_count from public.table_statistics where 
relname = 'pg_attribute' order by date;
date| relpages | reltuples | table_len | tuple_count | 
tuple_percent | dead_tuple_count | dead_tuple_len | free_space | 
free_percent | autovacuum_count

+--+---+---+-+---+--+++--+--
 2013-08-08 |39029 |109096 | 319725568 |   37950 |  
1.66 |52540 |7355600 |  296440048 |92.72 
| 6359
 2013-08-09 |12382 | 95848 | 101433344 |   38232 |  
5.28 |57443 |8042020 |   83862864 |82.68 
| 6711
 2013-08-10 |11365 |105073 |  93102080 |   37789 |  
5.68 |65599 |9183860 |   74483104 |   80 
| 7002
 2013-08-12 | 9447 | 95289 |  77389824 |   37811 |  
6.84 |57154 |8001560 |   60479736 |78.15 
| 7161
 2013-08-13 |47841 | 82877 | 391913472 |   38536 |  
1.38 |30461 |4264540 |  369093756 |94.18 
| 7347
 2013-08-14 |70265 |104926 | 575610880 |   38838 |  
0.94 |34649 |4850860 |  546449480 |94.93 
| 7398

(6 rows)

Autovacuum is running on this table, however it keeps growing.

On 08/06/2013 09:35 PM, Tom Lane wrote:

Vlad Arkhipov arhi...@dc.baikal.ru writes:

On 08/06/2013 04:26 PM, Sergey Konoplev wrote:

What pgstattuple shows on this table?

dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
   table_len  | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
   6363938816 |   48786 |   6830040 |  0.11 | 1459439 |
204321460 |   3.21 | 5939017376 | 93.32
(1 row)

So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space.  I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses.  There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you.  Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.

regards, tom lane





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Craig Ringer
On 08/06/2013 01:56 PM, Vlad Arkhipov wrote:
 Hello,
 
 We are suffering from a long-standing issue with autovacuuming/vacuuming
 system catalogs on the production server. We are actively using
 temporary tables in the legacy application, so system catalogs grows
 unbounded in time. Autovacuum does not remove dead tuples and neither do
 the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64.
 
 Nobody's holding an open transaction for long periods.

Got any prepared transactions?

SELECT * FROM pg_prepared_xacts;

SHOW max_prepared_transactions;

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov

On 08/06/2013 04:00 PM, Craig Ringer wrote:

On 08/06/2013 01:56 PM, Vlad Arkhipov wrote:

Hello,

We are suffering from a long-standing issue with autovacuuming/vacuuming
system catalogs on the production server. We are actively using
temporary tables in the legacy application, so system catalogs grows
unbounded in time. Autovacuum does not remove dead tuples and neither do
the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64.

Nobody's holding an open transaction for long periods.

Got any prepared transactions?

SELECT * FROM pg_prepared_xacts;

SHOW max_prepared_transactions;


dcdb=# select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-+-+--+---+--
(0 rows)

dcdb=# show max_prepared_transactions;
 max_prepared_transactions
---
 100
(1 row)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
 dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
 from pg_stat_sys_tables where relname = 'pg_attribute';
relname| n_live_tup | n_dead_tup | last_vacuum  |
 last_autovacuum
 --+++---+---
  pg_attribute |   39318086 | 395478 | 2013-08-06 14:47:48.187259+09 |
 2013-08-06 13:43:03.162286+09

What pgstattuple shows on this table?
http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov

On 08/06/2013 04:26 PM, Sergey Konoplev wrote:

On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:

dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
from pg_stat_sys_tables where relname = 'pg_attribute';
relname| n_live_tup | n_dead_tup | last_vacuum  |
last_autovacuum
--+++---+---
  pg_attribute |   39318086 | 395478 | 2013-08-06 14:47:48.187259+09 |
2013-08-06 13:43:03.162286+09

What pgstattuple shows on this table?
http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html



dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
 table_len  | tuple_count | tuple_len | tuple_percent | 
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | 
free_percent

+-+---+---+--++++--
 6363938816 |   48786 |   6830040 |  0.11 | 1459439 |  
204321460 |   3.21 | 5939017376 | 93.32

(1 row)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Tue, Aug 6, 2013 at 12:37 AM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
 What pgstattuple shows on this table?
 http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html


 dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
  table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
 dead_tuple_len | dead_tuple_percent | free_space | free_percent
 +-+---+---+--++++--
  6363938816 |   48786 |   6830040 |  0.11 | 1459439 |
 204321460 |   3.21 | 5939017376 | 93.32
 (1 row)

I guess you need to VACUUM FULL pg_attribute, if it is possible in
your situation of course. If it is not, let me know, I have another
one tricky way of solving this problem in my mind.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Tom Lane
Vlad Arkhipov arhi...@dc.baikal.ru writes:
 On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
 What pgstattuple shows on this table?

 dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
   table_len  | tuple_count | tuple_len | tuple_percent | 
 dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | 
 free_percent
 +-+---+---+--++++--
   6363938816 |   48786 |   6830040 |  0.11 | 1459439 |  
 204321460 |   3.21 | 5939017376 | 93.32
 (1 row)

So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space.  I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses.  There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you.  Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] System catalog vacuum issues

2013-08-05 Thread Vlad Arkhipov

Hello,

We are suffering from a long-standing issue with autovacuuming/vacuuming 
system catalogs on the production server. We are actively using 
temporary tables in the legacy application, so system catalogs grows 
unbounded in time. Autovacuum does not remove dead tuples and neither do 
the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64.


Nobody's holding an open transaction for long periods.

dcdb=# select xact_start, query_start, state, query from 
pg_stat_activity where state  'idle';
  xact_start   |  query_start  | state  
| query

---+---++---
 2013-08-06 14:46:56.303261+09 | 2013-08-06 14:46:56.303261+09 | active 
| select xact_start, query_start, state, query from pg_stat_activity 
where state  'idle';

(1 row)

dcdb=# select count(*) from pg_attribute;
 count
---
 51279
(1 row)

dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, 
last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute';
   relname| n_live_tup | n_dead_tup | last_vacuum  |
last_autovacuum

--+++---+---
 pg_attribute |   39318143 | 427798 | 2013-08-06 14:46:09.323187+09 
| 2013-08-06 13:43:03.162286+09

(1 row)

dcdb=# vacuum analyze verbose pg_attribute;
INFO:  vacuuming pg_catalog.pg_attribute
INFO:  index pg_attribute_relid_attnam_index now contains 492122 row 
versions in 166671 pages

DETAIL:  0 index row versions were removed.
163952 index pages have been deleted, 162834 are currently reusable.
CPU 0.69s/0.21u sec elapsed 0.94 sec.
INFO:  index pg_attribute_relid_attnum_index now contains 492253 row 
versions in 118119 pages

DETAIL:  0 index row versions were removed.
116071 index pages have been deleted, 115269 are currently reusable.
CPU 0.51s/0.14u sec elapsed 0.67 sec.
INFO:  pg_attribute: found 0 removable, 460354 nonremovable row 
versions in 9570 out of 776848 pages

DETAIL:  440706 dead row versions cannot be removed yet.
There were 1788424 unused item pointers.
0 pages are entirely empty.
CPU 1.28s/0.43u sec elapsed 1.75 sec.
INFO:  analyzing pg_catalog.pg_attribute
INFO:  pg_attribute: scanned 3 of 776848 pages, containing 1918 
live rows and 15226 dead rows; 1878 rows in sample, 39318086 estimated 
total rows

VACUUM

dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, 
last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute';
   relname| n_live_tup | n_dead_tup | last_vacuum  |
last_autovacuum

--+++---+---
 pg_attribute |   39318086 | 395478 | 2013-08-06 14:47:48.187259+09 
| 2013-08-06 13:43:03.162286+09

(1 row)



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers