pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex file bloating still in 7.4 ?
18+ messages / 5 participants
[nested] [flat]
* index file bloating still in 7.4 ?
@ 2003-10-18 21:55 Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-18 21:55 UTC (permalink / raw)
To: pgsql-performance
Hi,
I downloaded PostgreSQL 7.4beta4 and tried it out.
It turns out that the index file is still bloating even
after running vacuum or vacuum analyze on the table.
Still, only reindex will claim the space back.
Is the index bloating issue still not resolved in 7.4beta4 ?
Thanks.
Gan
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-18 21:58 Josh Berkus <josh@agliodbs.com>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2003-10-18 21:58 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; pgsql-performance
Gan,
> Is the index bloating issue still not resolved in 7.4beta4 ?
No, it should be. Please post your max_fsm_pages setting, and the output of a
sample VACUUM VERBOSE ANALYZE. You probably don't have your FSM set right.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 01:52 Seum-Lim Gan <slgan@lucent.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-19 01:52 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-performance
Hi Josh,
Sample verbose analyze:
VACUUM VERBOSE ANALYZE hello_rda_or_key;
INFO: vacuuming "craft.hello_rda_or_key"
INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.42s/0.13u sec elapsed 4.76 sec.
INFO: "hello_rda_or_key": found 0 removable, 740813 nonremovable row
versions in 12778 pages
DETAIL: 440813 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.78s/0.66u sec elapsed 6.41 sec.
INFO: analyzing "craft.hello_rda_or_key"
INFO: "hello_rda_or_key": 12778 pages, 3000 rows sampled, 39388
estimated total rows
VACUUM
Here is my postgresql.conf file:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#tcpip_socket = false
#max_connections = 100
max_connections = 600
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
port = 5333
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
shared_buffers = 1200 # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024 # min 64, size in KB
sort_mem = 40960 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
vacuum_mem = 81920 # min 1024, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
#log_min_duration_statement = 0 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero disables.
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false
# - Query/Index Statistics Collector -
#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
At 2:58 pm -0700 2003/10/18, Josh Berkus wrote:
>Gan,
>
>> Is the index bloating issue still not resolved in 7.4beta4 ?
>
>No, it should be. Please post your max_fsm_pages setting, and the output of a
>sample VACUUM VERBOSE ANALYZE. You probably don't have your FSM set right.
>
>
>--
>-Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 02:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-10-19 02:21 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: josh@agliodbs.com; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> Sample verbose analyze:
> VACUUM VERBOSE ANALYZE hello_rda_or_key;
> INFO: vacuuming "craft.hello_rda_or_key"
> INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
So what's the problem? That doesn't seem like a particularly bloated
index. You didn't say what datatype the index is on, but making the
most optimistic assumptions, index entries must use at least 16 bytes
each. You're getting about 300 entries per page, compared to the
theoretical limit of 512 ... actually more, since I'm not allowing for
upper btree levels in this calculation ... which says to me that the
page loading is right around the expected btree loading of 2/3.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 05:11 Seum-Lim Gan <slgan@lucent.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-19 05:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com; pgsql-performance
Hi Tom,
I did that when I have stopped my updates.
Now, I am doing updates below is the output of vacuum.
After doing the vacuum verbose analyze, it reported the following :
INFO: vacuuming "craft.dsperf_rda_or_key"
INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.61s/0.36u sec elapsed 17.92 sec.
INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable
row versions in 30892 pages
DETAIL: 1492218 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.95s/1.99u sec elapsed 26.95 sec.
INFO: analyzing "craft.dsperf_rda_or_key"
INFO: "hello_rda_or_key": 30909 pages, 3000 rows sampled, 93292
estimated total rows
VACUUM
Gan
At 10:21 pm -0400 2003/10/18, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>> Sample verbose analyze:
>
>> VACUUM VERBOSE ANALYZE hello_rda_or_key;
>> INFO: vacuuming "craft.hello_rda_or_key"
>> INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
>
>So what's the problem? That doesn't seem like a particularly bloated
>index. You didn't say what datatype the index is on, but making the
>most optimistic assumptions, index entries must use at least 16 bytes
>each. You're getting about 300 entries per page, compared to the
>theoretical limit of 512 ... actually more, since I'm not allowing for
>upper btree levels in this calculation ... which says to me that the
>page loading is right around the expected btree loading of 2/3.
>
> regards, tom lane
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 05:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-10-19 05:48 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: josh@agliodbs.com; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> INFO: vacuuming "craft.dsperf_rda_or_key"
> INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.61s/0.36u sec elapsed 17.92 sec.
> INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable
> row versions in 30892 pages
> DETAIL: 1492218 dead row versions cannot be removed yet.
You still haven't got an index-bloat problem. I am, however, starting
to wonder why you have so many dead-but-unremovable rows. I think you
must have some client process that's been holding an open transaction
for a long time.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 14:46 Seum-Lim Gan <slgan@lucent.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-19 14:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com; pgsql-performance
Hi Tom,
Thanks for info. I stoped the update and removed the process that's doing
the update and did vacuum analyze. This time the result says
the index row has been removed :
vacuum verbose analyze dsperf_rda_or_key;
INFO: vacuuming "scncraft.dsperf_rda_or_key"
INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages
DETAIL: 3097702 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.86s/25.49u sec elapsed 54.16 sec.
INFO: "dsperf_rda_or_key": removed 3097702 row versions in 53726 pages
DETAIL: CPU 6.29s/26.05u sec elapsed 78.23 sec.
INFO: "dsperf_rda_or_key": found 3097702 removable, 300000
nonremovable row versions in 58586 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 5 unused item pointers.
0 pages are entirely empty.
CPU 10.23s/53.79u sec elapsed 135.78 sec.
INFO: analyzing "scncraft.dsperf_rda_or_key"
INFO: "dsperf_rda_or_key": 58586 pages, 3000 rows sampled, 176830
estimated total rows
VACUUM
However, when I check the disk space usage, it has not changed.
Before and after the vacuum, it stayed the same :
/pg 822192 21% Sun Oct 19 09:34:25 CDT 2003
table /pg/data/base/17139/34048 Size=479936512 (relfilenode for table)
index /pg/data/base/17139/336727 Size=101474304 (relfilenode for index)
Any idea here ?
Another question, if we have a process that has different threads trying
to update PostgreSQL, is this going to post a problem if we do not have
the thread-safety option during configure ?
Thanks.
Gan
At 1:48 am -0400 2003/10/19, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>> INFO: vacuuming "craft.dsperf_rda_or_key"
>> INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages
>> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
>> CPU 0.61s/0.36u sec elapsed 17.92 sec.
>> INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable
>> row versions in 30892 pages
>> DETAIL: 1492218 dead row versions cannot be removed yet.
>
>You still haven't got an index-bloat problem. I am, however, starting
>to wonder why you have so many dead-but-unremovable rows. I think you
>must have some client process that's been holding an open transaction
>for a long time.
>
> regards, tom lane
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 15:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-10-19 15:47 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: josh@agliodbs.com; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> vacuum verbose analyze dsperf_rda_or_key;
> INFO: vacuuming "scncraft.dsperf_rda_or_key"
> INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages
> DETAIL: 3097702 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
Hm, interesting that you deleted 90% of the entries and still had no
empty index pages at all. What was the pattern of your deletes and/or
updates with respect to this index's key?
> However, when I check the disk space usage, it has not changed.
It won't in any case. Plain VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS. For that you need
more-invasive operations like VACUUM FULL or REINDEX.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 16:55 Seum-Lim Gan <slgan@lucent.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-19 16:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com; pgsql-performance
Hi Tom,
The key is a range from 1 to 30000 and picked randomly.
Oh, so in order to reclaim the disk space, we must run
reindex or vacuum full ?
This will lock out the table and we won't be able to do anything.
Looks like this is a problem. It means we cannot use it for
24x7 operations without having to stop the process and do the vacuum full
and reindex. Is there anything down the road that these operations
will not lock out the table ?
I let the process ran overnight. The last email I sent you with
the vacuum analyze output just about an hour ago, that was after
I removed the process that does the updates.
However, I search through all the vacuum I did just before I
went to bed and found that earlier vacuum did say 5 indexes deleted and
5 reusable. It has been pretty constant for about 1 to 2 hours and
then down to zero and has been like this since.
Sun Oct 19 00:50:07 CDT 2003
INFO: vacuuming "scncraft.dsperf_rda_or_key"
INFO: index "dsperf242_1105" now contains 402335 row versions in 7111 pages
DETAIL: 5 index pages have been deleted, 5 are currently reusable.
CPU 1.32s/0.17u sec elapsed 22.44 sec.
INFO: "dsperf_rda_or_key": found 0 removable, 401804 nonremovable
row versions in 35315 pages
DETAIL: 101802 dead row versions cannot be removed yet.
There were 1646275 unused item pointers.
0 pages are entirely empty.
CPU 2.38s/0.71u sec elapsed 27.09 sec.
INFO: analyzing "scncraft.dsperf_rda_or_key"
INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 156124
estimated total rows
VACUUM
Sleep 60 seconds
Sun Oct 19 00:51:40 CDT 2003
INFO: vacuuming "scncraft.dsperf_rda_or_key"
INFO: index "dsperf242_1105" now contains 411612 row versions in 7111 pages
DETAIL: 5 index pages have been deleted, 5 are currently reusable.
CPU 1.28s/0.22u sec elapsed 23.38 sec.
INFO: "dsperf_rda_or_key": found 0 removable, 410889 nonremovable
row versions in 35315 pages
DETAIL: 110900 dead row versions cannot be removed yet.
There were 1637190 unused item pointers.
0 pages are entirely empty.
CPU 2.13s/0.92u sec elapsed 27.13 sec.
INFO: analyzing "scncraft.dsperf_rda_or_key"
INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 123164
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.
Sun Oct 19 02:14:41 CDT 2003
INFO: vacuuming "scncraft.dsperf_rda_or_key"
INFO: index "dsperf242_1105" now contains 1053582 row versions in 7112 pages
DETAIL: 5 index pages have been deleted, 5 are currently reusable.
CPU 0.58s/0.29u sec elapsed 21.63 sec.
INFO: "dsperf_rda_or_key": found 0 removable, 1053103 nonremovable
row versions in 35315 pages
DETAIL: 753064 dead row versions cannot be removed yet.
There were 995103 unused item pointers.
0 pages are entirely empty.
CPU 1.54s/1.35u sec elapsed 26.17 sec.
INFO: analyzing "scncraft.dsperf_rda_or_key"
INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627
estimated total rows
VACUUM
Sleep 60 seconds
Sun Oct 19 02:16:16 CDT 2003
INFO: vacuuming "scncraft.dsperf_rda_or_key"
INFO: index "dsperf242_1105" now contains 1065887 row versions in 7119 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.71s/0.36u sec elapsed 21.12 sec.
INFO: "dsperf_rda_or_key": found 0 removable, 1065357 nonremovable
row versions in 35315 pages
DETAIL: 765328 dead row versions cannot be removed yet.
There were 982849 unused item pointers.
0 pages are entirely empty.
CPU 1.70s/1.42u sec elapsed 26.65 sec.
INFO: analyzing "scncraft.dsperf_rda_or_key"
INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.
Thanks.
Gan
At 11:47 am -0400 2003/10/19, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>> vacuum verbose analyze dsperf_rda_or_key;
>> INFO: vacuuming "scncraft.dsperf_rda_or_key"
>> INFO: index "dsperf242_1105" now contains 300000 row versions in
>>12387 pages
>> DETAIL: 3097702 index row versions were removed.
> > 0 index pages have been deleted, 0 are currently reusable.
>
>Hm, interesting that you deleted 90% of the entries and still had no
>empty index pages at all. What was the pattern of your deletes and/or
>updates with respect to this index's key?
>
>> However, when I check the disk space usage, it has not changed.
>
>It won't in any case. Plain VACUUM is designed for maintaining a
>steady-state level of free space in tables and indexes, not for
>returning major amounts of space to the OS. For that you need
>more-invasive operations like VACUUM FULL or REINDEX.
>
> regards, tom lane
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-19 19:04 Josh Berkus <josh@agliodbs.com>
parent: Seum-Lim Gan <slgan@lucent.com>
1 sibling, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2003-10-19 19:04 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Gan,
> Oh, so in order to reclaim the disk space, we must run
> reindex or vacuum full ?
> This will lock out the table and we won't be able to do anything.
> Looks like this is a problem. It means we cannot use it for
> 24x7 operations without having to stop the process and do the vacuum full
> and reindex. Is there anything down the road that these operations
> will not lock out the table ?
I doubt it; the amount of page-shuffling required to reclaim 90% of the space
in an index for a table that has been mostly cleared is substantial, and
would prevent concurrent access.
Also, you seem to have set up an impossible situation for VACUUM. If I'm
reading your statistics right, you have a large number of threads accessing
most of the data 100% of the time, preventing VACUUM from cleaning up the
pages. This is not, in my experience, a realistic test case ... there are
peak and idle periods for all databases, even webservers that have been
slashdotted.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-20 16:04 Seum-Lim Gan <slgan@lucent.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-20 16:04 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Josh, Tom,
OK. As I understand it, vacuum does not release the space
used by the index file.
However, it should be able to reuse the space for indexing.
I have observed that during initial updates of the table,
the index file did not grow and was steady but it did not last long
and keeps growing afterwards. Vacuum/vacuum analyze did not help.
In all the update testing, vacuum analyze was done every 1 minute.
Tom, something caught your attention the last time.
Any insight so far ? Is it a bug ?
Thanks.
Gan
Tom Lane wrote:
Seum-Lim Gan <slgan@lucent.com> writes:
> vacuum verbose analyze dsperf_rda_or_key;
> INFO: vacuuming "scncraft.dsperf_rda_or_key"
> INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages
> DETAIL: 3097702 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
Hm, interesting that you deleted 90% of the entries and still had no
empty index pages at all. What was the pattern of your deletes and/or
updates with respect to this index's key?
> However, when I check the disk space usage, it has not changed.
It won't in any case. Plain VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS. For that you need
more-invasive operations like VACUUM FULL or REINDEX.
regards, tom lane
At 12:04 pm -0700 2003/10/19, Josh Berkus wrote:
>Gan,
>
>> Oh, so in order to reclaim the disk space, we must run
>> reindex or vacuum full ?
>> This will lock out the table and we won't be able to do anything.
>> Looks like this is a problem. It means we cannot use it for
>> 24x7 operations without having to stop the process and do the vacuum full
>> and reindex. Is there anything down the road that these operations
>> will not lock out the table ?
>
>I doubt it; the amount of page-shuffling required to reclaim 90% of the space
>in an index for a table that has been mostly cleared is substantial, and
>would prevent concurrent access.
>
>Also, you seem to have set up an impossible situation for VACUUM. If I'm
>reading your statistics right, you have a large number of threads accessing
>most of the data 100% of the time, preventing VACUUM from cleaning up the
>pages. This is not, in my experience, a realistic test case ... there are
>peak and idle periods for all databases, even webservers that have been
>slashdotted.
>
>--
>Josh Berkus
>Aglio Database Solutions
>San Francisco
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-20 21:14 Seum-Lim Gan <slgan@lucent.com>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-20 21:14 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom, Josh,
We tried one more thing: with the table not being updated
at all and we did vacuum. Each time a vacuum is done,
the index file becomes bigger.
This is probably what is contributing to the index file
growing as well.
Thanks.
Gan
At 11:04 am -0500 2003/10/20, Seum-Lim Gan wrote:
>Hi Josh, Tom,
>
>OK. As I understand it, vacuum does not release the space
>used by the index file.
>However, it should be able to reuse the space for indexing.
>
>I have observed that during initial updates of the table,
>the index file did not grow and was steady but it did not last long
>and keeps growing afterwards. Vacuum/vacuum analyze did not help.
>
>In all the update testing, vacuum analyze was done every 1 minute.
>
>Tom, something caught your attention the last time.
>
>Any insight so far ? Is it a bug ?
>
>Thanks.
>
>Gan
>
>Tom Lane wrote:
>
>Seum-Lim Gan <slgan@lucent.com> writes:
>> vacuum verbose analyze dsperf_rda_or_key;
>> INFO: vacuuming "scncraft.dsperf_rda_or_key"
>> INFO: index "dsperf242_1105" now contains 300000 row versions in
>>12387 pages
>> DETAIL: 3097702 index row versions were removed.
>> 0 index pages have been deleted, 0 are currently reusable.
>
>Hm, interesting that you deleted 90% of the entries and still had no
>empty index pages at all. What was the pattern of your deletes and/or
>updates with respect to this index's key?
>
>> However, when I check the disk space usage, it has not changed.
>
>It won't in any case. Plain VACUUM is designed for maintaining a
>steady-state level of free space in tables and indexes, not for
>returning major amounts of space to the OS. For that you need
>more-invasive operations like VACUUM FULL or REINDEX.
>
> regards, tom lane
>
>At 12:04 pm -0700 2003/10/19, Josh Berkus wrote:
>>Gan,
>>
>>> Oh, so in order to reclaim the disk space, we must run
>>> reindex or vacuum full ?
>>> This will lock out the table and we won't be able to do anything.
>>> Looks like this is a problem. It means we cannot use it for
>>> 24x7 operations without having to stop the process and do the vacuum full
>>> and reindex. Is there anything down the road that these operations
>>> will not lock out the table ?
>>
>>I doubt it; the amount of page-shuffling required to reclaim 90% of the space
>>in an index for a table that has been mostly cleared is substantial, and
>>would prevent concurrent access.
>>
>>Also, you seem to have set up an impossible situation for VACUUM. If I'm
>>reading your statistics right, you have a large number of threads accessing
>>most of the data 100% of the time, preventing VACUUM from cleaning up the
>>pages. This is not, in my experience, a realistic test case ... there are
>>peak and idle periods for all databases, even webservers that have been
>>slashdotted.
>>
>>--
>>Josh Berkus
>>Aglio Database Solutions
>>San Francisco
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>--
>+--------------------------------------------------------+
>| Seum-Lim GAN email : slgan@lucent.com |
>| Lucent Technologies |
>| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
>| Naperville, IL 60566, USA. fax : (630)-713-7272 |
>| web : http://inuweb.ih.lucent.com/~slgan |
>+--------------------------------------------------------+
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-20 21:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-10-20 21:25 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> We tried one more thing: with the table not being updated
> at all and we did vacuum. Each time a vacuum is done,
> the index file becomes bigger.
It is not possible for plain vacuum to make the index bigger.
VACUUM FULL possibly could make the index bigger, since it has to
transiently create duplicate index entries for every row it moves.
If you want any really useful comments on your situation, you're going
to have to offer considerably more detail than you have done so far ---
preferably, a test case that lets someone else reproduce your results.
So far, all we can do is guess on the basis of very incomplete
information. When you aren't even bothering to mention whether a vacuum
is FULL or not, I have to wonder whether I have any realistic picture of
what's going on.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-20 21:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
1 sibling, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-10-20 21:42 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: josh@agliodbs.com; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> [ successive outputs from VACUUM ANALYZE ]
FWIW, I don't think your problem is really index bloat at all, it's
more like too-many-dead-rows bloat. Note that the number of "dead row
versions" is climbing steadily from run to run:
> DETAIL: 101802 dead row versions cannot be removed yet.
> DETAIL: 110900 dead row versions cannot be removed yet.
> DETAIL: 753064 dead row versions cannot be removed yet.
> DETAIL: 765328 dead row versions cannot be removed yet.
It's hardly the index's fault that it's growing, when it has to keep
track of an ever-increasing number of rows.
The real question is what you're doing that requires the system to keep
hold of these dead rows instead of recycling them. I suspect you have
a client process somewhere that is holding an open transaction for a
long time ... probably not doing anything, just sitting there with an
unclosed BEGIN ...
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-21 10:56 Andrew Sullivan <andrew@libertyrms.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Andrew Sullivan @ 2003-10-21 10:56 UTC (permalink / raw)
To: pgsql-performance
On Mon, Oct 20, 2003 at 05:42:52PM -0400, Tom Lane wrote:
> hold of these dead rows instead of recycling them. I suspect you have
> a client process somewhere that is holding an open transaction for a
> long time ... probably not doing anything, just sitting there with an
> unclosed BEGIN ...
Which could be because you're doing something nasty with one of the
"autocommit=off" clients. Most of the client libraries implement
this by doing "commit;begin;" at every commit. This means you have
way more idle in transaction connections than you think. Look in
pg_stat_activity, assuming you've turned on query echoing.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-21 12:12 Gaetano Mendola <mendola@bigfoot.com>
parent: Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 0 replies; 18+ messages in thread
From: Gaetano Mendola @ 2003-10-21 12:12 UTC (permalink / raw)
To: pgsql-performance; +Cc: Andrew Sullivan <andrew@libertyrms.info>
Andrew Sullivan wrote:
> On Mon, Oct 20, 2003 at 05:42:52PM -0400, Tom Lane wrote:
>
>
>>hold of these dead rows instead of recycling them. I suspect you have
>>a client process somewhere that is holding an open transaction for a
>>long time ... probably not doing anything, just sitting there with an
>>unclosed BEGIN ...
>
>
> Which could be because you're doing something nasty with one of the
> "autocommit=off" clients. Most of the client libraries implement
> this by doing "commit;begin;" at every commit. This means you have
> way more idle in transaction connections than you think. Look in
> pg_stat_activity, assuming you've turned on query echoing.
Or is enough do a ps -eafwww | grep post
to see the state of the connections
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-21 16:25 Seum-Lim Gan <slgan@lucent.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Seum-Lim Gan @ 2003-10-21 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Hi Tom,
1.)
OK. We have narrowed it down.
We did a few (like 5 to 8 times) vacuum analyze <tablename> (no full), the
pg_statistics relfilenode grew. There was no database operation when
we did this, no other client connections except the one that does
the vacuum.
If we do plain simple "vacuum <tablename>" (again no full), we see
pg_statistics_relid_att_index relfilenode grew instead of
pg_statistics.
So, overtime, these files will grow if we do vacuum.
Are these expected ?
The question now is, if we are not doing anything
to the database, why would they grow after a few vacuums ?
2.)
The other problem we have with
> DETAIL: 101802 dead row versions cannot be removed yet.
> DETAIL: 110900 dead row versions cannot be removed yet.
> DETAIL: 753064 dead row versions cannot be removed yet.
> DETAIL: 765328 dead row versions cannot be removed yet.
We will collect more data and see what we can get from the
the process. Offhand, the process is connecting to
the database through ODBC and we don't use any BEGIN in
our updates, just doing plain UPDATE repeatedly
with different keys randomly.
The database is defaulted to autocommit=true in postgresql.conf.
Thanks.
Gan
At 5:25 pm -0400 2003/10/20, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>> We tried one more thing: with the table not being updated
>> at all and we did vacuum. Each time a vacuum is done,
>> the index file becomes bigger.
>
>It is not possible for plain vacuum to make the index bigger.
>
>VACUUM FULL possibly could make the index bigger, since it has to
>transiently create duplicate index entries for every row it moves.
>
>If you want any really useful comments on your situation, you're going
>to have to offer considerably more detail than you have done so far ---
>preferably, a test case that lets someone else reproduce your results.
>So far, all we can do is guess on the basis of very incomplete
>information. When you aren't even bothering to mention whether a vacuum
>is FULL or not, I have to wonder whether I have any realistic picture of
>what's going on.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index file bloating still in 7.4 ?
@ 2003-10-21 16:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Seum-Lim Gan <slgan@lucent.com>
0 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2003-10-21 16:42 UTC (permalink / raw)
To: Seum-Lim Gan <slgan@lucent.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Seum-Lim Gan <slgan@lucent.com> writes:
> We did a few (like 5 to 8 times) vacuum analyze <tablename> (no full), the
> pg_statistics relfilenode grew.
Well, sure. ANALYZE puts new rows into pg_statistic, and obsoletes old
ones. You need to vacuum pg_statistic every so often (not to mention
the other system catalogs).
> If we do plain simple "vacuum <tablename>" (again no full), we see
> pg_statistics_relid_att_index relfilenode grew instead of
> pg_statistics.
Don't think I believe that. Plain vacuum won't touch pg_statistic
at all (unless it's the target table of course). I'd expect ANALYZE
to make both the stats table and its index grow, though.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2003-10-21 16:42 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-18 21:55 index file bloating still in 7.4 ? Seum-Lim Gan <slgan@lucent.com>
2003-10-18 21:58 ` Josh Berkus <josh@agliodbs.com>
2003-10-19 01:52 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-19 02:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-19 05:11 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-19 05:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-19 14:46 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-19 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-19 16:55 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-19 19:04 ` Josh Berkus <josh@agliodbs.com>
2003-10-20 16:04 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-20 21:14 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-20 21:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-21 16:25 ` Seum-Lim Gan <slgan@lucent.com>
2003-10-21 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-20 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-21 10:56 ` Andrew Sullivan <andrew@libertyrms.info>
2003-10-21 12:12 ` Gaetano Mendola <mendola@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox