[PERFORM] database bloat,non removovable rows, slow query etc...
Hi, probably this is a very frequenfly question... I read archivies of
this list but I didn't found a finally solution for this aspect. I'll
explain my situation.
PSQL version 8.1.3
configuration of fsm,etcc default
autovacuum and statistics activated
22 daemons that have a persistent connection to this database(all
connection are in "idle"(no transaction opened).
this is the vacuum output of a table that it's updated frequently:
database=# VACUUM ANALYZE verbose cliente;
INFO: vacuuming "public.cliente"
INFO: index "cliente_pkey" now contains 29931 row versions in 88 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cliente_login_key" now contains 29931 row versions in 165 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO: "cliente": found 0 removable, 29931 nonremovable row versions in 559
pages
DETAIL: 29398 dead row versions cannot be removed yet.
There were 9 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
INFO: vacuuming "pg_toast.pg_toast_370357"
INFO: index "pg_toast_370357_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_370357": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.cliente"
INFO: "cliente": scanned 559 of 559 pages, containing 533 live rows and 29398
dead rows; 533 rows in sample, 533 estimated total rows
VACUUM
database=# SELECT * from pgstattuple('cliente');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
---+-+---+---+--++++--
4579328 | 533 | 84522 | 1.85 |29398 |
4279592 | 93.45 | 41852 | 0.91
(1 row)
The performance of this table it's degraded now and autovacuum/vacuum full
don't remove these dead tuples. Only if I do a CLUSTER of the table the tuples
are removed.
The same problem is on other very trafficated tables.
I think that the problems probably are:
- tune the value of my fsm/etc settings in postgresql.conf but i don't
understdand how to tune it correctly.
- the persistent connections to this db conflict with the
autovacuum but i don't understand why. there are no transaction opened,
only connections in "idle" state.
Tell me what do you think...
Regards,
Matteo
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > 22 daemons that have a persistent connection to this database(all > connection are in "idle"(no transaction opened). You may think that, but you are wrong. > INFO: "cliente": found 0 removable, 29931 nonremovable row versions in 559 > pages > DETAIL: 29398 dead row versions cannot be removed yet. The only way the above can happen is if there are some fairly old open transactions. Looking in pg_stat_activity might help you identify the culprit(s). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
Are there open transactions on the table in question? We had the same
issue. A 100K row table was so bloated that the system thought there was
1M rows. We had many transaction that we noticed in TOP, but since
we could not track down which process or user was holding the table we had
to restart Pg. Once restarted we were able to do a VACUUM FULL and this
took care of the issue.
hth
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
Matteo Sgalaberni
<[EMAIL PROTECTED]>
Sent by: To
pgsql-performance pgsql-performance@postgresql.org
[EMAIL PROTECTED] cc
.org
Subject
[PERFORM] database bloat,non
09/01/06 05:39 AM removovable rows, slow query etc...
Hi, probably this is a very frequenfly question... I read archivies of
this list but I didn't found a finally solution for this aspect. I'll
explain my situation.
PSQL version 8.1.3
configuration of fsm,etcc default
autovacuum and statistics activated
22 daemons that have a persistent connection to this database(all
connection are in "idle"(no transaction opened).
this is the vacuum output of a table that it's updated frequently:
database=# VACUUM ANALYZE verbose cliente;
INFO: vacuuming "public.cliente"
INFO: index "cliente_pkey" now contains 29931 row versions in 88 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cliente_login_key" now contains 29931 row versions in 165
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO: "cliente": found 0 removable, 29931 nonremovable row versions in 559
pages
DETAIL: 29398 dead row versions cannot be removed yet.
There were 9 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
INFO: vacuuming "pg_toast.pg_toast_370357"
INFO: index "pg_toast_370357_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_370357": found 0 removable, 0 nonremovable row versions in
0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.cliente"
INFO: "cliente": scanned 559 of 559 pages, containing 533 live rows and
29398 dead rows; 533 rows in sample, 533 estimated total rows
VACUUM
database=# SELECT * from pgstattuple('cliente');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
---+-+---+---+--++++--
4579328 | 533 | 84522 | 1.85 |29398 |
4279592 | 93.45 | 41852 | 0.91
(1 row)
The performance of this table it's degraded now and autovacuum/vacuum full
don't remove these dead tuples. Only if I do a CLUSTER of the table the
tuples
are removed.
The same problem is on other very trafficated tables.
I think that the problems probably are:
- tune the value of my fsm/etc settings in postgresql.conf but i don't
understdand how to tune it correctly.
- the persistent connections to this db conflict with the
autovacuum but i don't understand why. there are no transaction opened,
only connections in "idle" state.
Tell me what do you think...
Regards,
Matteo
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
Hi, Tom and Matteo, Tom Lane wrote: > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: >> 22 daemons that have a persistent connection to this database(all >> connection are in "idle"(no transaction opened). > > You may think that, but you are wrong. > >> INFO: "cliente": found 0 removable, 29931 nonremovable row versions in 559 >> pages >> DETAIL: 29398 dead row versions cannot be removed yet. > > The only way the above can happen is if there are some fairly old open > transactions. Looking in pg_stat_activity might help you identify the > culprit(s). Another possibility might be an outstanding two-phase-commit transaction. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
Hi, I've been looking at the results from the pg_statio* tables, to view the impact of increasing the shared buffers to increase performance. As expected, increasing from the default by a factor of 10~20 moves table/index disk blocks reads to cache hits, but the overall service time of my test page is not changed (I'm testing with a set of queries implying an increase of 170,000 of sum(heap_blks_hit) and 2,000 of sum(idx_blks_hit) from pg_statio_user_tables). I've seen that documentation says: data that is not in the PostgreSQL buffer cache may still reside in the kernel's I/O cache, and may therefore still be fetched without requiring a physical read I guess this is the best explanation (btw, my test machine runs Linux 2.6 on 1G of RAM), but I'm still wondering what should be expected from moving caching from OS filesystem to PG - probably PG can "cleverly" flush its cache when it is full (e.g. table data before index data maybe?), whereas the OS will do it "blindly", but I'm wondering about the limits of this behaviour, particularly considering that being "very clever" about cache flush would probably need realtime query statistics which I am not sure PG does. After all, memory added to shared buffers should be mecanically removed from effective cache size (or others), so I cannot just increase it until the OS cannot cache anymore :) -- Guillaume Cottenceau ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
On Fri, Sep 01, 2006 at 10:43:30AM -0400, Tom Lane wrote: > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > > 22 daemons that have a persistent connection to this database(all > > connection are in "idle"(no transaction opened). > > You may think that, but you are wrong. Ok. I stopped all clients. No connections to this database. Only psql console. Made vacuum full/freeze all cominations... again dead rows non removable. Nothing changed as in production. this is my postgres config: http://pastebin.com/781480 I read a lot about bloat tables related to not appropriate fsm settings... can be the mine a case of misconfiguration of these parameters? Thx Matteo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > Ok. I stopped all clients. No connections to this database. When you say "this database", do you mean the whole postmaster cluster, or just the one database? Open transactions in other databases of the same cluster can be a problem. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <[EMAIL PROTECTED]> wrote: Hi, I've been looking at the results from the pg_statio* tables, to view the impact of increasing the shared buffers to increase performance. I think 'shared buffers' is one of the most overrated settings from a performance standpoint. however you must ensure there is enough for things the server does besides caching. It used to be a bigger deal than it is in modern versionf of postgresql modern operating systems. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] database bloat,non removovable rows, slow query
On Fri, 2006-09-01 at 12:28, Matteo Sgalaberni wrote: > On Fri, Sep 01, 2006 at 10:43:30AM -0400, Tom Lane wrote: > > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > > > 22 daemons that have a persistent connection to this database(all > > > connection are in "idle"(no transaction opened). > > > > You may think that, but you are wrong. > Ok. I stopped all clients. No connections to this database. Only psql > console. Made vacuum > full/freeze all cominations... again dead rows non removable. Nothing > changed as in production. > > this is my postgres config: > > http://pastebin.com/781480 > > I read a lot about bloat tables related to > not appropriate fsm settings... can be the mine a case of > misconfiguration of these parameters? Something is holding a lock, somewhere. Have you tried shutting down and restarting the database to see if you can get it to vacuum that way? You're not in a transaction in psql, right? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
Guillaume 1G is really not a significant amount of memory these days, That said 6-10% of available memory should be given to an 8.0 or older version of postgresql Newer versions work better around 25% I'm not sure what you mean by mechanically removed from effective_cache effective cache is really a representation of shared buffers plus OS cache Dave On 1-Sep-06, at 1:00 PM, Guillaume Cottenceau wrote: Hi, I've been looking at the results from the pg_statio* tables, to view the impact of increasing the shared buffers to increase performance. As expected, increasing from the default by a factor of 10~20 moves table/index disk blocks reads to cache hits, but the overall service time of my test page is not changed (I'm testing with a set of queries implying an increase of 170,000 of sum(heap_blks_hit) and 2,000 of sum(idx_blks_hit) from pg_statio_user_tables). I've seen that documentation says: data that is not in the PostgreSQL buffer cache may still reside in the kernel's I/O cache, and may therefore still be fetched without requiring a physical read I guess this is the best explanation (btw, my test machine runs Linux 2.6 on 1G of RAM), but I'm still wondering what should be expected from moving caching from OS filesystem to PG - probably PG can "cleverly" flush its cache when it is full (e.g. table data before index data maybe?), whereas the OS will do it "blindly", but I'm wondering about the limits of this behaviour, particularly considering that being "very clever" about cache flush would probably need realtime query statistics which I am not sure PG does. After all, memory added to shared buffers should be mecanically removed from effective cache size (or others), so I cannot just increase it until the OS cannot cache anymore :) -- Guillaume Cottenceau ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
On 1-Sep-06, at 3:49 PM, Merlin Moncure wrote: On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <[EMAIL PROTECTED]> wrote: Hi, I've been looking at the results from the pg_statio* tables, to view the impact of increasing the shared buffers to increase performance. I think 'shared buffers' is one of the most overrated settings from a performance standpoint. however you must ensure there is enough for things the server does besides caching. It used to be a bigger deal than it is in modern versionf of postgresql modern operating systems. merlin So if shared buffers is the most overrated, what do you consider the proper way of tuning ? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] increasing shared buffers: how much should be removed
I think 'shared buffers' is one of the most overrated settings from a performance standpoint. however you must ensure there is enough for things the server does besides caching. It used to be a bigger deal than it is in modern versionf of postgresql modern operating systems. Previous to 8.1 I would agree with you, but as of 8.1 it is probably the most underrated. Joshua D. Drake merlin So if shared buffers is the most overrated, what do you consider the proper way of tuning ? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
