Thread: PostgreSQL 8.2.3 VACUUM Timings/Performance

PostgreSQL 8.2.3 VACUUM Timings/Performance

From
"Bruce McAlister"
Date:
Hi All,

I tried posting this last week but it has not come through yet, so please
excuse me if there is a double post.

We're having some issue's with the vacuum times within our database
environment, and would like some input from the guru's out there that could
potentially suggest a better approach or mechanism.



------------------------------------------------------------------------------------

Problem Background

First off, I'm no PostgreSQL guru, so, please be nice :)


Over time we have noticed increased response times from the database which
has an adverse affect on our registration times. After doing some research
it appears that this may have been related to our maintenance regime, and
has thus been amended as follows:


[1] AutoVacuum runs during the day over the entire PostgreSQL cluster,

[2] A Vacuum Full Verbose is run during our least busy period (generally
03:30) against the Database,

[3] A Re-Index on the table is performed,

[4] A Cluster on the table is performed against the most used index,

[5] A Vacuum Analyze Verbose is run against the database.


These maintenance steps have been setup to run every 24 hours.


The database in essence, once loaded up and re-index is generally around
17MB for data and 4.7MB for indexes in size.


Over a period of 24 hours the database can grow up to around 250MB and the
indexes around 33MB (Worst case thus far). When the maintenance kicks in,
the vacuum full verbose step can take up to 15 minutes to complete (worst
case). The re-index, cluster and vacuum analyze verbose steps complete in
under 1 second each. The problem here is the vacuum full verbose, which
renders the environment unusable during the vacuum phase. The idea here is
to try and get the vacuum full verbose step to complete in less than a
minute. Ideally, if we could get it to complete quicker then that would be
GREAT, but our minimal requirement is for it to complete at the very most 1
minute. Looking at the specifications of our environment below, do you think
that this is at all possible?


------------------------------------------------------------------------------------

Environment Background:


We are running a VoIP service whereby the IP phones perform a registration
request every 113 seconds. These registration requests are verified against
the database and the details are updated accordingly. Currently we average
around 100 - 150 read/write requests per second to this particular database.
The requirement here is that the database response is sub 15 milliseconds
for both types of requests, which it currently is. The database _must_ also
be available 24x7.


------------------------------------------------------------------------------------

Hardware Environment:

SunFire X4200

      2 x Dual Core Opteron 280's

      8GB RAM

      2 x Q-Logic Fibre Channel HBA's


Sun StorEdge 3511 FC SATA Array

      1 x 1GB RAID Module

      12 x 250GB 7200 RPM SATA disks


------------------------------------------------------------------------------------

RAID Environment:

      5 Logical drives, each LD is made up of 2 x 250GB SATA HDD in a RAID 1
mirror.

      2 x 250GB SATA HDD allocated as hot spares



The logical drives are partitioned and presented to the OS as follows:

      LD0 (2 x 250GB SATA HDD's RAID 1)

        Partition 0 (120GB)

        Partition 1 (120GB)

      LD1 (2 x 250GB SATA HDD's RAID 1)

        Partition 0 (120GB)

        Partition 1 (120GB)

      LD2 (2 x 250GB SATA HDD's RAID 1)

        Partition 0 (80GB)

        Partition 1 (80GB)

        Partition 2 (80GB)

      LD3 (2 x 250GB SATA HDD's RAID 1)

        Partition 0 (80GB)

        Partition 1 (80GB)

        Partition 2 (80GB)

      LD4 (2 x 250GB SATA HDD's RAID 1)

        Partition 0 (120GB)

        Partition 1 (120GB)



-------------------------------------------------------------------------------------

OS Environment

Solaris 10 Update 3 (11/06)

      Boot disks are 76GB 15000 RPM configure in a RAID 1 mirror.


-------------------------------------------------------------------------------------

Filesystem Layout

      PostgreSQL Data

        250GB ZFS file-system made up of:

            LD0 Partition 0 Mirrored to LD1 Partition 0 (120GB)

            LD0 Partition 1 Mirrored to LD1 Partition 1 (120GB)

        The above 2 vdevs are then striped across each other



      PostgreSQL WAL

        80GB ZFS filesystem made up of:

            LD2 Partition 0 Mirrored to LD3 Partition 0 (80GB)

            LD2 partition 1 Mirrored to LD3 Partition 1 (80GB)

        The above 2 vdevs are then striped across each other



-------------------------------------------------------------------------------------

PostgreSQL Configuration

      PostgreSQL version 8.2.3


#---------------------------------------------------------------------------

# RESOURCE USAGE (except WAL)

#---------------------------------------------------------------------------



# - Memory -



shared_buffers = 1024MB                 # min 128kB or max_connections*16kB

                                        # (change requires restart)

temp_buffers = 8MB                      # min 800kB

max_prepared_transactions = 200         # can be 0 or more

                                        # (change requires restart) # Note:
increasing max_prepared_transactions costs ~600 bytes of shared memory # per
transaction slot, plus lock space (see max_locks_per_transaction).

work_mem = 1MB                          # min 64kB

maintenance_work_mem = 256MB            # min 1MB

max_stack_depth = 2MB                   # min 100kB



# - Free Space Map -



max_fsm_pages = 2048000                 # min max_fsm_relations*16, 6 bytes
each

                                        # (change requires restart)

max_fsm_relations = 10000               # min 100, ~70 bytes each

                                        # (change requires restart)



# - Kernel Resource Usage -



#max_files_per_process = 1000           # min 25

                                        # (change requires restart)

#shared_preload_libraries = ''          # (change requires restart)



# - Cost-Based Vacuum Delay -



vacuum_cost_delay = 200                 # 0-1000 milliseconds

vacuum_cost_page_hit = 1                # 0-10000 credits

vacuum_cost_page_miss = 10              # 0-10000 credits

vacuum_cost_page_dirty = 20             # 0-10000 credits

vacuum_cost_limit = 200                 # 0-10000 credits



# - Background writer -



#bgwriter_delay = 200ms                 # 10-10000ms between rounds

#bgwriter_lru_percent = 1.0             # 0-100% of LRU buffers
scanned/round

#bgwriter_lru_maxpages = 5              # 0-1000 buffers max written/round

#bgwriter_all_percent = 0.333           # 0-100% of all buffers
scanned/round

#bgwriter_all_maxpages = 5              # 0-1000 buffers max written/round



#---------------------------------------------------------------------------

# WRITE AHEAD LOG

#---------------------------------------------------------------------------



# - Settings -



fsync = on                              # turns forced synchronization on or
off

wal_sync_method = open_datasync         # the default is the first option

                                        # supported by the operating system:

                                        #   open_datasync

                                        #   fdatasync

                                        #   fsync

                                        #   fsync_writethrough

                                        #   open_sync

#full_page_writes = on                  # recover from partial page writes

wal_buffers = 512kB                     # min 32kB

                                        # (change requires restart)

commit_delay = 10000                    # range 0-100000, in microseconds

commit_siblings = 50                    # range 1-1000



# - Checkpoints -



checkpoint_segments = 128               # in logfile segments, min 1, 16MB
each

checkpoint_timeout = 5min               # range 30s-1h

checkpoint_warning = 5min               # 0 is off





#---------------------------------------------------------------------------

# QUERY TUNING

#---------------------------------------------------------------------------



# - Planner Method Configuration -



#enable_bitmapscan = on

#enable_hashagg = on

#enable_hashjoin = on

#enable_indexscan = on

#enable_mergejoin = on

#enable_nestloop = on

#enable_seqscan = on

#enable_sort = on

#enable_tidscan = on



# - Planner Cost Constants -



#seq_page_cost = 1.0                    # measured on an arbitrary scale

random_page_cost = 2.0                  # same scale as above

#cpu_tuple_cost = 0.01                  # same scale as above

#cpu_index_tuple_cost = 0.005           # same scale as above

#cpu_operator_cost = 0.0025             # same scale as above

effective_cache_size = 5120MB



# - Genetic Query Optimizer -



#geqo = on

#geqo_threshold = 12

#geqo_effort = 5                        # range 1-10

#geqo_pool_size = 0                     # selects default based on effort

#geqo_generations = 0                   # selects default based on effort

#geqo_selection_bias = 2.0              # range 1.5-2.0



# - Other Planner Options -



#default_statistics_target = 10         # range 1-1000

#constraint_exclusion = off

#from_collapse_limit = 8

#join_collapse_limit = 8                # 1 disables collapsing of explicit

                                        # JOINs

#---------------------------------------------------------------------------

# RUNTIME STATISTICS

#---------------------------------------------------------------------------



# - Query/Index Statistics Collector -



stats_command_string = on

update_process_title = on



stats_start_collector = on              # needed for block or row stats

                                        # (change requires restart)
stats_block_level = on stats_row_level = on

stats_reset_on_server_start = off       # (change requires restart)





# - Statistics Monitoring -



#log_parser_stats = off

#log_planner_stats = off

#log_executor_stats = off

#log_statement_stats = off





#---------------------------------------------------------------------------

# AUTOVACUUM PARAMETERS

#---------------------------------------------------------------------------



autovacuum = on                         # enable autovacuum subprocess?

                                        # 'on' requires
stats_start_collector

                                        # and stats_row_level to also be on

autovacuum_naptime = 1min               # time between autovacuum runs

autovacuum_vacuum_threshold = 500       # min # of tuple updates before

                                        # vacuum

autovacuum_analyze_threshold = 250      # min # of tuple updates before

                                        # analyze

autovacuum_vacuum_scale_factor = 0.2    # fraction of rel size before

                                        # vacuum

autovacuum_analyze_scale_factor = 0.1   # fraction of rel size before

                                        # analyze

autovacuum_freeze_max_age = 200000000   # maximum XID age before forced
vacuum

                                        # (change requires restart)

autovacuum_vacuum_cost_delay = -1       # default vacuum cost delay for

                                        # autovacuum, -1 means use

                                        # vacuum_cost_delay

autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for

                                        # autovacuum, -1 means use

                                        # vacuum_cost_limit



------------------------------------------------------------------------------------

Database Environment



There are 18 databases running within this PostgreSQL cluster, the database
we are having the vacuum issue with is described as follows:



                  List of relations

 Schema |        Name         |   Type   |   Owner

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

 public | dialplans           | table    |

 public | extensions          | table    |

 public | extensions_id_seq   | sequence |

 public | iaxaccounts         | table    |

 public | numbercategories    | table    |

 public | numbers             | table    |

 public | numbersubcategories | table    |

 public | portnumbers         | table    |

 public | portrequests        | table    |

 public | sipaccounts         | table    |

 public | voicemailaccounts   | table    |



The table (with indexes) being used the most is described as follows:



                                  Table "public.sipaccounts"

       Column       |           Type           |                  Modifiers

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

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

 id                 | character varying(36)    | not null

 name               | character varying(80)    | not null

 accountcode        | character varying(20)    |

 amaflags           | character varying(13)    |

 callgroup          | character varying(10)    |

 callerid           | character varying(80)    |

 canreinvite        | character(3)             | default 'no'::bpchar

 context            | character varying(80)    |

 defaultip          | character varying(15)    |

 dtmfmode           | character varying(7)     |

 fromuser           | character varying(80)    |

 fromdomain         | character varying(80)    |

 fullcontact        | character varying(80)    |

 host               | character varying(31)    | not null default
''::character varying

 insecure           | character varying(11)    |

 language           | character(2)             |

 mailbox            | character varying(50)    |

 md5secret          | character varying(80)    |

 nat                | character varying(5)     | not null default
'no'::character varying

 deny               | character varying(95)    |

 permit             | character varying(95)    |

 mask               | character varying(95)    |

 pickupgroup        | character varying(10)    |

 port               | character varying(5)     | not null default
''::character varying

 qualify            | character(4)             |

 restrictcid        | character(1)             |

 rtptimeout         | character(3)             |

 rtpholdtimeout     | character(5)             |

 secret             | character varying(80)    |

 type               | character varying(6)     | not null default
'friend'::character varying

 username           | character varying(80)    | not null default
''::character varying

 disallow           | character varying(100)   |

 allow              | character varying(100)   |

 musiconhold        | character varying(100)   |

 regseconds         | integer                  | not null default 0

 ipaddr             | character varying(15)    | not null default
''::character varying

 regexten           | character varying(80)    | not null default
''::character varying

 cancallforward     | character(3)             | default 'yes'::bpchar

 setvar             | character varying(100)   | not null default
''::character varying

 inserted           | timestamp with time zone | not null default now()

 lastregister       | timestamp with time zone |

 useragent          | character varying(128)   |

 natsendkeepalives  | character(1)             | default 'n'::bpchar

 natconnectionstate | character(1)             |

 outboundproxyport  | character varying(5)     | not null default
''::character varying

 outboundproxy      | character varying(31)    | not null default
''::character varying

 voicemailextension | character varying(128)   |

 pstncallerid       | character varying(24)    | default 'Uknown'::character
varying

 dialplan           | character varying(64)    |

 whitelabelid       | character varying(32)    |

 localcallprefix    | character varying(10)    |

Indexes:

    "sippeers_pkey" PRIMARY KEY, btree (id), tablespace "bf_service_idx"

    "sippeers_name_key" UNIQUE, btree (name) CLUSTER, tablespace
"bf_service_idx"

    "accountcode_index" btree (accountcode), tablespace "bf_service_idx"





If anyone has any comments/suggestions please feel free to respond. Any
responses are most welcome.



Thanks

Bruce





Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
Heikki Linnakangas
Date:
Bruce McAlister wrote:
> Over time we have noticed increased response times from the database which
> has an adverse affect on our registration times. After doing some research
> it appears that this may have been related to our maintenance regime, and
> has thus been amended as follows:
>
>
> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,
>
> [2] A Vacuum Full Verbose is run during our least busy period (generally
> 03:30) against the Database,
>
> [3] A Re-Index on the table is performed,
>
> [4] A Cluster on the table is performed against the most used index,
>
> [5] A Vacuum Analyze Verbose is run against the database.
>
>
> These maintenance steps have been setup to run every 24 hours.
>
>
> The database in essence, once loaded up and re-index is generally around
> 17MB for data and 4.7MB for indexes in size.
>
>
> Over a period of 24 hours the database can grow up to around 250MB and the
> indexes around 33MB (Worst case thus far). When the maintenance kicks in,
> the vacuum full verbose step can take up to 15 minutes to complete (worst
> case). The re-index, cluster and vacuum analyze verbose steps complete in
> under 1 second each. The problem here is the vacuum full verbose, which
> renders the environment unusable during the vacuum phase. The idea here is
> to try and get the vacuum full verbose step to complete in less than a
> minute. Ideally, if we could get it to complete quicker then that would be
> GREAT, but our minimal requirement is for it to complete at the very most 1
> minute. Looking at the specifications of our environment below, do you think
> that this is at all possible?

250MB+33MB isn't very much. It should easily fit in memory, I don't see
why you need the 12 disk RAID array. Are you sure you got the numbers right?

Vacuum full is most likely a waste of time. Especially on the tables
that you cluster later, cluster will rewrite the whole table and indexes
anyway. A regular normal vacuum should be enough to keep the table in
shape. A reindex is also not usually necessary, and for the tables that
you cluster, it's a waste of time like vacuum full.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
"Bruce McAlister"
Date:
Hi Heikki,

Thanks for the reply.

The RAID array was implemented due to a projected growth pattern which
incorporate all 18 of our databases. The sizings I mentioned only refer to 1
of those databases, which, is also the most heavily used database :)

If I understand you correctly, we could in essence change our maintenance
routine to the follwing:

[1] Cluster on most used index
[2] Perform a vacuum analyze on the table

If I read your post correctly, this will regenerate the index that the
cluster is performed on (1 of 3) and also re-generate the table in the
sequence of that index?

If that is the case, why would anyone use the vacuum full approach if they
could use the cluster command on a table/database that will regen these
files for you. It almost seems like the vacuum full approach would, or
could, be obsoleted by the cluster command, especially if the timings in
their respective runs are that different (in our case the vacuum full took
15 minutes in our worst case, and the cluster command took under 1 second
for the same table and scenario).

The output of our script for that specific run is as follows (just in-case
i'm missing something):

Checking disk usage before maintenance on service (sipaccounts) at
02-Mar-2007 03:30:00

258M /database/pgsql/bf_service/data
33M /database/pgsql/bf_service/index

Completed checking disk usage before maintenance on service (sipaccounts) at
02-Mar-2007 03:30:00

Starting VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007
03:30:00

INFO: vacuuming "public.sipaccounts"
INFO: "sipaccounts": found 71759 removable, 9314 nonremovable row versions
in 30324 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 318 to 540 bytes long.
There were 439069 unused item pointers.
Total free space (including removable row versions) is 241845076 bytes.
28731 pages are or will become empty, including 41 at the end of the table.
30274 pages containing 241510688 free bytes are potential move destinations.
CPU 0.00s/0.05u sec elapsed 31.70 sec.
INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages
DETAIL: 7265 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 1.52 sec.
INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages
DETAIL: 7161 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 3.07 sec.
INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages
DETAIL: 71759 index row versions were removed.
1151 index pages have been deleted, 1151 are currently reusable.
CPU 0.02s/0.08u sec elapsed 56.31 sec.
INFO: "sipaccounts": moved 3395 row versions, truncated 30324 to 492 pages
DETAIL: CPU 0.03s/0.56u sec elapsed 751.99 sec.
INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages
DETAIL: 3395 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.21 sec.
INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages
DETAIL: 3395 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages
DETAIL: 3395 index row versions were removed.
1159 index pages have been deleted, 1159 are currently reusable.
CPU 0.01s/0.01u sec elapsed 30.03 sec.
INFO: vacuuming "pg_toast.pg_toast_2384131"
INFO: "pg_toast_2384131": found 0 removable, 0 nonremovable row versions in
0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_2384131_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.
VACUUM

Completed VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:35
Starting REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35

REINDEX

Completed REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35
Starting CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:35

CLUSTER sipaccounts;
CLUSTER

Completed CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:36
Starting VACUUM ANALYZE VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:36

INFO: vacuuming "public.sipaccounts"
INFO: scanned index "sippeers_name_key" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "sippeers_pkey" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.20 sec.
INFO: scanned index "accountcode_index" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sipaccounts": removed 9 row versions in 9 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "sippeers_name_key" now contains 9361 row versions in 36 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "sippeers_pkey" now contains 9361 row versions in 69 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "accountcode_index" now contains 9361 row versions in 49 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sipaccounts": found 9 removable, 9361 nonremovable row versions in
495 pages
DETAIL: 131 dead row versions cannot be removed yet.
There were 0 unused item pointers.
28 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.84 sec.
INFO: vacuuming "pg_toast.pg_toast_2386447"
INFO: index "pg_toast_2386447_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2386447": 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 contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.sipaccounts"
INFO: "sipaccounts": scanned 517 of 517 pages, containing 8966 live rows and
800 dead rows; 3000 rows in sample, 8966 estimated total rows VACUUM

Completed VACUUM ANALYZE VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:39
Checking disk usage after maintenance on service (sipaccounts) at
02-Mar-2007 03:44:39

22M /database/pgsql/bf_service/data

6.7M /database/pgsql/bf_service/index

Completed checking disk usage after maintenance on service (sipaccounts) at
02-Mar-2007 03:44:39

Thanks
Bruce
"Heikki Linnakangas" <heikki@enterprisedb.com> wrote in message
news:45EC1931.3020701@enterprisedb.com...
> Bruce McAlister wrote:
>> Over time we have noticed increased response times from the database
>> which has an adverse affect on our registration times. After doing some
>> research it appears that this may have been related to our maintenance
>> regime, and has thus been amended as follows:
>>
>>
>> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,
>>
>> [2] A Vacuum Full Verbose is run during our least busy period (generally
>> 03:30) against the Database,
>>
>> [3] A Re-Index on the table is performed,
>>
>> [4] A Cluster on the table is performed against the most used index,
>>
>> [5] A Vacuum Analyze Verbose is run against the database.
>>
>>
>> These maintenance steps have been setup to run every 24 hours.
>>
>>
>> The database in essence, once loaded up and re-index is generally around
>> 17MB for data and 4.7MB for indexes in size.
>>
>>
>> Over a period of 24 hours the database can grow up to around 250MB and
>> the indexes around 33MB (Worst case thus far). When the maintenance kicks
>> in, the vacuum full verbose step can take up to 15 minutes to complete
>> (worst case). The re-index, cluster and vacuum analyze verbose steps
>> complete in under 1 second each. The problem here is the vacuum full
>> verbose, which renders the environment unusable during the vacuum phase.
>> The idea here is to try and get the vacuum full verbose step to complete
>> in less than a minute. Ideally, if we could get it to complete quicker
>> then that would be GREAT, but our minimal requirement is for it to
>> complete at the very most 1 minute. Looking at the specifications of our
>> environment below, do you think that this is at all possible?
>
> 250MB+33MB isn't very much. It should easily fit in memory, I don't see
> why you need the 12 disk RAID array. Are you sure you got the numbers
> right?
>
> Vacuum full is most likely a waste of time. Especially on the tables that
> you cluster later, cluster will rewrite the whole table and indexes
> anyway. A regular normal vacuum should be enough to keep the table in
> shape. A reindex is also not usually necessary, and for the tables that
> you cluster, it's a waste of time like vacuum full.
>
> --
>   Heikki Linnakangas
>   EnterpriseDB   http://www.enterprisedb.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
Heikki Linnakangas
Date:
Bruce McAlister wrote:
> Hi Heikki,
>
> Thanks for the reply.
>
> The RAID array was implemented due to a projected growth pattern which
> incorporate all 18 of our databases. The sizings I mentioned only refer to 1
> of those databases, which, is also the most heavily used database :)
>
> If I understand you correctly, we could in essence change our maintenance
> routine to the follwing:
>
> [1] Cluster on most used index
> [2] Perform a vacuum analyze on the table
>
> If I read your post correctly, this will regenerate the index that the
> cluster is performed on (1 of 3) and also re-generate the table in the
> sequence of that index?

That's right. In fact, even cluster probably doesn't make much
difference in your case. Since the table fits in memory anyway, the
physical order of it doesn't matter much.

I believe you would be fine just turning autovacuum on, and not doing
any manual maintenance.

> If that is the case, why would anyone use the vacuum full approach if they
> could use the cluster command on a table/database that will regen these
> files for you. It almost seems like the vacuum full approach would, or
> could, be obsoleted by the cluster command, especially if the timings in
> their respective runs are that different (in our case the vacuum full took
> 15 minutes in our worst case, and the cluster command took under 1 second
> for the same table and scenario).

In fact, getting rid of vacuum full, or changing it to work like
cluster, has been proposed in the past. The use case really is pretty
narrow; cluster is a lot faster if there's a lot of unused space in the
table, and if there's not, vacuum full isn't going to do much so there's
not much point running it in the first place. The reason it exists is
largely historical, there hasn't been a pressing reason to remove it either.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
Tom Lane
Date:
"Bruce McAlister" <bruce.mcalister@blueface.ie> writes:
> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,

Good, but evidently you need to make it more aggressive.

> [2] A Vacuum Full Verbose is run during our least busy period (generally
> 03:30) against the Database,

> [3] A Re-Index on the table is performed,

> [4] A Cluster on the table is performed against the most used index,

> [5] A Vacuum Analyze Verbose is run against the database.

That is enormous overkill.  Steps 2 and 3 are a 100% waste of time if
you are going to cluster in step 4.  Just do the CLUSTER and then
ANALYZE (or VACUUM ANALYZE if you really must, but the value is marginal).

            regards, tom lane

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
Heikki Linnakangas
Date:
Aidan Van Dyk wrote:
> * Heikki Linnakangas <heikki@enterprisedb.com> [070305 09:46]:
>> In fact, getting rid of vacuum full, or changing it to work like
>> cluster, has been proposed in the past. The use case really is pretty
>> narrow; cluster is a lot faster if there's a lot of unused space in the
>> table, and if there's not, vacuum full isn't going to do much so there's
>> not much point running it in the first place. The reason it exists is
>> largely historical, there hasn't been a pressing reason to remove it either.
>
> I've never used CLUSTER, because I've always heard murmerings of it not
> being completely MVCC safe.  From the TODO:
>     * CLUSTER
>     o Make CLUSTER preserve recently-dead tuples per MVCC
>       requirements

Good point, I didn't remember that. Using cluster in an environment like
the OP has, cluster might actually break the consistency of concurrent
transactions.

> But the documents don't mention anything about cluster being unsafe.

Really? <checks docs>. Looks like you're right. Should definitely be
mentioned in the docs.

> AFAIK, Vacuum full doesn't suffer the same MVCC issues that cluster
> does.  Is this correct?

That's right. Vacuum full goes to great lengths to be MVCC-safe.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
Aidan Van Dyk
Date:
* Heikki Linnakangas <heikki@enterprisedb.com> [070305 09:46]:

> >If that is the case, why would anyone use the vacuum full approach if they
> >could use the cluster command on a table/database that will regen these
> >files for you. It almost seems like the vacuum full approach would, or
> >could, be obsoleted by the cluster command, especially if the timings in
> >their respective runs are that different (in our case the vacuum full took
> >15 minutes in our worst case, and the cluster command took under 1 second
> >for the same table and scenario).
>
> In fact, getting rid of vacuum full, or changing it to work like
> cluster, has been proposed in the past. The use case really is pretty
> narrow; cluster is a lot faster if there's a lot of unused space in the
> table, and if there's not, vacuum full isn't going to do much so there's
> not much point running it in the first place. The reason it exists is
> largely historical, there hasn't been a pressing reason to remove it either.

I've never used CLUSTER, because I've always heard murmerings of it not
being completely MVCC safe.  From the TODO:
    * CLUSTER
    o Make CLUSTER preserve recently-dead tuples per MVCC
      requirements
But the documents don't mention anything about cluster being unsafe.

AFAIK, Vacuum full doesn't suffer the same MVCC issues that cluster
does.  Is this correct?

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Attachment

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
"Bruce McAlister"
Date:
Hi Tom,

Thanks for the suggestion. It's been a while since I replied to this as I
had to go and do some further investigation of the docs with regards the
autovacuum daemons configuration. According to the documentation, the
formula's for the vacuum and analyze are as follows:

Vacuum
    vacuum threshold = vacuum base threshold + vacuum scale factor * number
of tuples
Analyze
    analyze threshold = analyze base threshold + analyze scale factor *
number of tuples

My current settings for autovacuum are as follows:

# - Cost-Based Vacuum Delay -

vacuum_cost_delay         = 200                 # 0-1000 milliseconds
vacuum_cost_page_hit    = 1                     # 0-10000 credits
vacuum_cost_page_miss = 10                   # 0-10000 credits
vacuum_cost_page_dirty = 20                   # 0-10000 credits
vacuum_cost_limit           = 200                 # 0-10000 credits

#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum                                   = on                      #
enable autovacuum subprocess?

      # 'on' requires stats_start_collector

      # and stats_row_level to also be on
autovacuum_naptime                     = 1min                   # time
between autovacuum runs
autovacuum_vacuum_threshold     = 500                     # min # of tuple
updates before

      # vacuum
autovacuum_analyze_threshold      = 250                    # min # of tuple
updates before

      # analyze
autovacuum_vacuum_scale_factor = 0.2                     # fraction of rel
size before

      # vacuum
autovacuum_analyze_scale_factor = 0.1                     # fraction of rel
size before

      # analyze
autovacuum_freeze_max_age       = 200000000         # maximum XID age before
forced vacuum

      # (change requires restart)
autovacuum_vacuum_cost_delay  = -1                       # default vacuum
cost delay for

      # autovacuum, -1 means use

      # vacuum_cost_delay
autovacuum_vacuum_cost_limit    = -1                       # default vacuum
cost limit for

     # autovacuum, -1 means use

     # vacuum_cost_limit

Thus to make the autovacuum more aggressive I am thinking along the lines of
changing the following parameters:

autovacuum_vacuum_threshold     = 250
autovacuum_analyze_threshold     = 125

The documentation also mentions that when the autovacuum runs it selects a
single database to process on that run. This means that the particular table
that we are interrested in will only be vacuumed once every 17 minutes,
assuming we have 18 databases and the selection process is sequential
through the database list.

From my understanding of the documentation, the only way to work around this
issue is to manually update the system catalog table pg_autovacuum and set
the pg_autovacuum.enabled field to false to skip the autovacuum on tables
that dont require such frequent vacuums. If I do enable this feature, and I
manually run a vacuumdb from the command line against that particular
disabled table, will the vacuum still process the table? I'm assuming too,
that the best tables to disable autovacuum on will be ones with a minimal
amount of update/delete queries run against it. For example, if we have a
table that only has inserts applied to it, it is safe to assume that that
table can safely be ignored by autovacuum.

Do you have any other suggestions as to which tables generally can be
excluded from the autovacuum based on the usage patterns?
Can you see anything with respect to my new autovacuum parameters that may
cause issue's and are there any other parameters that you suggest I need to
change to make the autovacuum daemon more aggressive?

PS: Currently we have the Cluster command running on the sipaccounts table
as the vacuum full is taking too long. It would be nice though to have some
piece of mind that the cluster command is mvcc safe, as Heikki and Aidan
have mentioned that it is not and may break things in our environment, I'm a
little afraid of running with the cluster command, and should possibly go
back to the vacuum full :/

Thanks all for any and all suggestions/comments.

Thanks
Bruce


"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:19106.1173111068@sss.pgh.pa.us...
> "Bruce McAlister" <bruce.mcalister@blueface.ie> writes:
>> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,
>
> Good, but evidently you need to make it more aggressive.
>
>> [2] A Vacuum Full Verbose is run during our least busy period (generally
>> 03:30) against the Database,
>
>> [3] A Re-Index on the table is performed,
>
>> [4] A Cluster on the table is performed against the most used index,
>
>> [5] A Vacuum Analyze Verbose is run against the database.
>
> That is enormous overkill.  Steps 2 and 3 are a 100% waste of time if
> you are going to cluster in step 4.  Just do the CLUSTER and then
> ANALYZE (or VACUUM ANALYZE if you really must, but the value is marginal).
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
"Anton Melser"
Date:
> In fact, getting rid of vacuum full, or changing it to work like
> cluster, has been proposed in the past. The use case really is pretty
> narrow; cluster is a lot faster if there's a lot of unused space in the
> table, and if there's not, vacuum full isn't going to do much so there's
> not much point running it in the first place. The reason it exists is
> largely historical, there hasn't been a pressing reason to remove it either.

I can assure you it is a great way to get back gigabytes when someone
has put no vacuum strategy in place and your 200K row table (with
about 200 bytes per row) is taking up 1.7gig!!!
Vive le truncate table, and vive le vacuum full!
:-)
Anton

Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

From
"Bruce McAlister"
Date:
Hi All,

Okay, I'm getting a little further now. I'm about to create entries in the
pg_autovacuum system tables. However, I'm a little confused as to how I go
about finding out the OID value of the tables. The pg_autovacuum table
requires the OID of the table you want to create settings for (vacrelid).
Can anyone shed some light on how I can extract the OID of the table? Also,
what happens if you create a table without OID's, are you still able to add
it's details in the pg_autovacuum table if there is no OID associated with a
table?

      Name Type References Description
      vacrelid oid pg_class.oid The table this entry is for
      enabled bool   If false, this table is never autovacuumed
      vac_base_thresh integer   Minimum number of modified tuples before
vacuum
      vac_scale_factor float4   Multiplier for reltuples to add to
vac_base_thresh
      anl_base_thresh integer   Minimum number of modified tuples before
analyze
      anl_scale_factor float4   Multiplier for reltuples to add to
anl_base_thresh
      vac_cost_delay integer   Custom vacuum_cost_delay parameter
      vac_cost_limit integer   Custom vacuum_cost_limit parameter
      freeze_min_age integer   Custom vacuum_freeze_min_age parameter
      freeze_max_age integer   Custom autovacuum_freeze_max_age parameter


Thanks
Bruce


"Bruce McAlister" <bruce.mcalister@blueface.ie> wrote in message
news:esrdsb$1klg$1@news.hub.org...
> Hi Tom,
>
> Thanks for the suggestion. It's been a while since I replied to this as I
> had to go and do some further investigation of the docs with regards the
> autovacuum daemons configuration. According to the documentation, the
> formula's for the vacuum and analyze are as follows:
>
> Vacuum
>    vacuum threshold = vacuum base threshold + vacuum scale factor * number
> of tuples
> Analyze
>    analyze threshold = analyze base threshold + analyze scale factor *
> number of tuples
>
> My current settings for autovacuum are as follows:
>
> # - Cost-Based Vacuum Delay -
>
> vacuum_cost_delay         = 200                 # 0-1000 milliseconds
> vacuum_cost_page_hit    = 1                     # 0-10000 credits
> vacuum_cost_page_miss = 10                   # 0-10000 credits
> vacuum_cost_page_dirty = 20                   # 0-10000 credits
> vacuum_cost_limit           = 200                 # 0-10000 credits
>
> #---------------------------------------------------------------------------
> # AUTOVACUUM PARAMETERS
> #---------------------------------------------------------------------------
>
> autovacuum                                   = on                      #
> enable autovacuum subprocess?
>
> # 'on' requires stats_start_collector
>
> # and stats_row_level to also be on
> autovacuum_naptime                     = 1min                   # time
> between autovacuum runs
> autovacuum_vacuum_threshold     = 500                     # min # of tuple
> updates before
>
> # vacuum
> autovacuum_analyze_threshold      = 250                    # min # of
> tuple updates before
>
> # analyze
> autovacuum_vacuum_scale_factor = 0.2                     # fraction of rel
> size before
>
> # vacuum
> autovacuum_analyze_scale_factor = 0.1                     # fraction of
> rel size before
>
> # analyze
> autovacuum_freeze_max_age       = 200000000         # maximum XID age
> before forced vacuum
>
> # (change requires restart)
> autovacuum_vacuum_cost_delay  = -1                       # default vacuum
> cost delay for
>
> # autovacuum, -1 means use
>
> # vacuum_cost_delay
> autovacuum_vacuum_cost_limit    = -1                       # default
> vacuum cost limit for
>
> # autovacuum, -1 means use
>
> # vacuum_cost_limit
>
> Thus to make the autovacuum more aggressive I am thinking along the lines
> of changing the following parameters:
>
> autovacuum_vacuum_threshold     = 250
> autovacuum_analyze_threshold     = 125
>
> The documentation also mentions that when the autovacuum runs it selects a
> single database to process on that run. This means that the particular
> table that we are interrested in will only be vacuumed once every 17
> minutes, assuming we have 18 databases and the selection process is
> sequential through the database list.
>
> From my understanding of the documentation, the only way to work around
> this issue is to manually update the system catalog table pg_autovacuum
> and set the pg_autovacuum.enabled field to false to skip the autovacuum on
> tables that dont require such frequent vacuums. If I do enable this
> feature, and I manually run a vacuumdb from the command line against that
> particular disabled table, will the vacuum still process the table? I'm
> assuming too, that the best tables to disable autovacuum on will be ones
> with a minimal amount of update/delete queries run against it. For
> example, if we have a table that only has inserts applied to it, it is
> safe to assume that that table can safely be ignored by autovacuum.
>
> Do you have any other suggestions as to which tables generally can be
> excluded from the autovacuum based on the usage patterns?
> Can you see anything with respect to my new autovacuum parameters that may
> cause issue's and are there any other parameters that you suggest I need
> to change to make the autovacuum daemon more aggressive?
>
> PS: Currently we have the Cluster command running on the sipaccounts table
> as the vacuum full is taking too long. It would be nice though to have
> some piece of mind that the cluster command is mvcc safe, as Heikki and
> Aidan have mentioned that it is not and may break things in our
> environment, I'm a little afraid of running with the cluster command, and
> should possibly go back to the vacuum full :/
>
> Thanks all for any and all suggestions/comments.
>
> Thanks
> Bruce
>
>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:19106.1173111068@sss.pgh.pa.us...
>> "Bruce McAlister" <bruce.mcalister@blueface.ie> writes:
>>> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,
>>
>> Good, but evidently you need to make it more aggressive.
>>
>>> [2] A Vacuum Full Verbose is run during our least busy period (generally
>>> 03:30) against the Database,
>>
>>> [3] A Re-Index on the table is performed,
>>
>>> [4] A Cluster on the table is performed against the most used index,
>>
>>> [5] A Vacuum Analyze Verbose is run against the database.
>>
>> That is enormous overkill.  Steps 2 and 3 are a 100% waste of time if
>> you are going to cluster in step 4.  Just do the CLUSTER and then
>> ANALYZE (or VACUUM ANALYZE if you really must, but the value is
>> marginal).
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>