Thread:
All,
I see that an auto vacuum process that has started on April 08 is still continuing till date on a production server
thatI manage.
Some statistics related to the process are as listed below. The queries are below statistics.
1) ps -aux | grep ^postgres
postgres 24188 0.0 2.4 14038764 809904 ? Ss Apr08 1:05 postgres: autovacuum worker process sbcoasis
2) select * from pg_lock
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction| pid | mode | granted
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------
relation | 16384 | 18349 | | | | | | | | 5/6082308
| 24188 | ShareUpdateExclusiveLock | t
3) pg_stat_user_tables values
relid | n_live_tup | n_tup_hot_upd | n_dead_tup | n_tup_ins | n_tup_del
-------+------------+---------------+------------+-----------+-----------
18349 | 1245123345 | 0 | 3 | 76479925 | 5
relid | last_autovacuum | last_autoanalyze
-------+-------------------------------+-------------------------------
18349 | 2009-03-29 20:36:59.18137-07 | 2009-04-07 21:48:09.646087-07
4) Postgres Configuration Parameters:
# - Memory -
shared_buffers = 12GB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # 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 = 2GB # min 64kB
maintenance_work_mem = 1GB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Free Space Map -
max_fsm_pages = 1500000 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
max_fsm_relations = 500 # 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 = 5 # 0-1000 milliseconds
5) The server has quad processors with 32 GB RAM.
--------------------------------------------------------------------------------------------------------------------------------------------------------
Queries
From past experience, running a manual vacuum and analyze on the table after a couple of days took around 12 - 16
hours,so the time taken for the current process seems over the top. It also stops me from running reindex routine
(whichwas to happen last week - runs once a month), as the indexing routine waited for the vacuum to be completed and
hadto be cancelled.
A) Is there a way to know whether the process is still running or is stalled?
B) Can the process be killed safely without causing issues in the autovacuum daemon?
C) Is there a way to know the percentage of autovacuum completed on a table?
D) Can I speed up the autovacuum process in some way? Will increasing the maintenance_work_mem affect other processes?
Please let me know if more information is required to resolve the issue.
Thanks in advance.
Shrirang.
The information contained in this message, including any attachments, is attorney privileged and/or confidential
informationintended only for the use of the individual or entity named as addressee. The review, dissemination,
distributionor copying of this communication by or to anyone other than the intended addressee is strictly prohibited.
Ifyou have received this communication in error, please immediately notify the sender by replying to the message and
destroyall copies of the original message.
Shrirang Chitnis <Shrirang.Chitnis@hovservices.com> writes:
> I see that an auto vacuum process that has started on April 08 is still continuing till date on a production server
thatI manage.
> A) Is there a way to know whether the process is still running or is stalled?
strace the process for a few minutes and see if it does anything or just
sits there. Alternatively, does ps show its runtime is increasing?
> B) Can the process be killed safely without causing issues in the autovacuum daemon?
You could try SIGINT'ing it. Anything stronger might be risky.
regards, tom lane
We are facing a strange problem in our 8.2.7 database.
There is a bash shell script that does:-
sql="select distinct to_char(date_of_issue, 'YYYYMM') from
yan.int_prod_s_master order by 1;"
YYYYMM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c
"$sql"`
for x in $YYYYMM
do
$scriptdir/USCS_production_updates.sh $x
>>$logdir/USCS_production_updates.log 2>&1
done
The $scriptdir/USCS_production_updates.sh script does updates like:-
YYYYMM=$1
database="us_audit"
db_user="postgres"
db_host="nutrageous"
psql_cmd="/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d
${database} -e ";
sql="
update int_prod_manual_price_${YYYYMM} mp
set .
from int_prod_s_master_${YYYYMM} sm
where .
and not exists ( select 1 from int_prod_stop_${YYYYMM} where .)
and .;
";
$psql_cmd -c "$SQL"
When these scripts run, the USCS_production_updates.log shows the correct
update statement, with values of YYYYMM substituted in the table names, and
message like "UPDATE 1025" from postgres indicating 1025 rows got updated.
However, none of these updates actually get applied in the database. Auto
commit is on in the database, but it seems the updates do not get committed.
The system logs also show no errors.
Any ideas why above update is not working?
Thanks!
Atul
Re: Logs say update done but not actually done or committed into database ???
From
Scott Marlowe
Date:
On Wed, Apr 22, 2009 at 2:28 PM, Atul Chojar <achojar@airfacts.com> wrote:
> We are facing a strange problem in our 8.2.7 database.
>
> There is a bash shell script that does:-
>
> sql="select distinct to_char(date_of_issue, 'YYYYMM') from
> yan.int_prod_s_master order by 1;"
> YYYYMM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c
> "$sql"`
> for x in $YYYYMM
> do
> $scriptdir/USCS_production_updates.sh $x
>>>$logdir/USCS_production_updates.log 2>&1
> done
>
> The $scriptdir/USCS_production_updates.sh script does updates like:-
>
> YYYYMM=$1
> database="us_audit"
> db_user="postgres"
> db_host="nutrageous"
> psql_cmd="/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d
> ${database} -e ";
> sql="
> update int_prod_manual_price_${YYYYMM} mp
> set .
> from int_prod_s_master_${YYYYMM} sm
> where .
> and not exists ( select 1 from int_prod_stop_${YYYYMM} where .)
> and .;
> ";
> $psql_cmd -c "$SQL"
>
> When these scripts run, the USCS_production_updates.log shows the correct
> update statement, with values of YYYYMM substituted in the table names, and
> message like "UPDATE 1025" from postgres indicating 1025 rows got updated.
>
> However, none of these updates actually get applied in the database. Auto
> commit is on in the database, but it seems the updates do not get committed.
>
> The system logs also show no errors.
>
> Any ideas why above update is not working?
Any possibility you're doing the updates to an inherited table, or a
table in a different schema or a different database than you think?
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Apr 22, 2009 at 2:28 PM, Atul Chojar <achojar@airfacts.com> wrote:
>> Any ideas why above update is not working?
> Any possibility you're doing the updates to an inherited table, or a
> table in a different schema or a different database than you think?
Another possibility is that the script is issuing BEGIN and then not
sending a COMMIT before it exits. In that case the behavior is going
to be auto-rollback, not auto-commit. The psql invocation as given
doesn't show any sign of that, but maybe there's a \set AUTOCOMMIT off
hiding in ~/.psqlrc, or something like that?
regards, tom lane