Thread: pg_autovacuum and REINDEX at the same time (?)
Hi.
I've been running autovacuum over a month now without any problems, but
today one of the critical tables got locked and made a pretty big mess. :)
We have a cron script that does REINDEX on this table every 5 minutes.
So i wonder if running REINDEX and VACUUM on the same table at the same
time may cause this deadlock?
Or can a VACUUM make so much trouble?
Version: PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)
Vacuum settings:
vacuum_cost_delay = 150
vacuum_cost_page_hit = 6
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 150
From the log:
2007-02-28 10:40:13 EET - LOG: autovacuum: processing database "dbname"
2007-02-28 10:40:56 EET - postgres FATAL: sorry, too many clients
already (here i assume the table got completely locked)
...
Lots and lots of it..
...
2007-02-28 10:41:35 EET - postgres FATAL: sorry, too many clients already
2007-02-28 10:41:35 EET - postgres ERROR: deadlock detected
2007-02-28 10:41:35 EET - postgres DETAIL: Process 41021 waits for
AccessExclusiveLock on relation 965687 of database 16398; blocked by
process 41071.
Process 41071 waits for AccessShareLock on relation 965684 of
database 16398; blocked by process 41021.
2007-02-28 10:41:35 EET - postgres CONTEXT: SQL statement "REINDEX
TABLE category_tree"
PL/pgSQL function "update_itemcount" line 48 at SQL statement
2007-02-28 10:41:35 EET - postgres STATEMENT: SELECT * FROM
update_itemcount();
2007-02-28 10:41:35 EET - postgres ERROR: current transaction is
aborted, commands ignored until end of transaction block
Janar Kartau wrote: > Hi. > I've been running autovacuum over a month now without any problems, but > today one of the critical tables got locked and made a pretty big mess. :) > We have a cron script that does REINDEX on this table every 5 minutes. > So i wonder if running REINDEX and VACUUM on the same table at the same > time may cause this deadlock? > Or can a VACUUM make so much trouble? ISTM that you have two separate issues. The "sorry, too many clients already" error means that you've reached the maximum number of connections, as set with the max_connections setting. The autovacuum processes needs one connection to run. I suspect that the deadlock is not related to the autovacuum, but just an interaction between your transactions and the REINDEX. What does the update_itemcount() function look like? Are you running the REINDEX in a transaction? Why do you need to reindex every 5 minutes? How long does the vacuum run normally? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
I know what causes the "sorry, too many clients already" error, but the
number of connections shouldn't grow so big.. so i guess the table
(category_tree) got locked.
We do REINDEX every 5 minutes because the table gets updated very often
and the query's took a lot of time. I think since we use autovacuum now,
it's not needed anymore.
Here's the fuction itself..
DECLARE
row RECORD;
BEGIN
UPDATE category_tree SET item_count = 0;
FOR row IN SELECT
count(I.item_id) AS itemcount
,CT.node_left
,CT.node_right
FROM
items I
JOIN category_tree CT ON (I.category1=CT.category_id)
WHERE
date_start <= now()
AND date_end >= now()
AND item_status = 1
AND I.view_group IS NULL
GROUP BY
node_left||node_right,
node_left,
node_right
LOOP
UPDATE category_tree SET item_count = item_count + row.itemcount
where node_left <= row.node_left AND node_right >= row.node_right;
END LOOP;
FOR row IN SELECT
count(I.item_id) AS itemcount
,CT.node_left
,CT.node_right
FROM
items I
JOIN category_tree CT ON (I.category2=CT.category_id)
WHERE
date_start <= now()
AND date_end >= now()
AND item_status = 1
AND I.view_group IS NULL
GROUP BY
node_left||node_right,
node_left,
node_right
LOOP
UPDATE category_tree SET item_count = item_count + row.itemcount
where node_left <= row.node_left AND node_right >= row.node_right;
END LOOP;
--REINDEX TABLE category_tree;
RETURN 1;
END;
and it's called from..
BEGIN;
SELECT * FROM update_itemcount();
UPDATE cache.cached_stats SET intval=(SELECT count(*) FROM items WHERE
item_status = 1 AND view_group IS NULL) WHERE stat_id = 1;
COMMIT;
Heikki Linnakangas wrote:
> Janar Kartau wrote:
>> Hi.
>> I've been running autovacuum over a month now without any problems,
>> but today one of the critical tables got locked and made a pretty big
>> mess. :)
>> We have a cron script that does REINDEX on this table every 5
>> minutes. So i wonder if running REINDEX and VACUUM on the same table
>> at the same time may cause this deadlock?
>> Or can a VACUUM make so much trouble?
>
> ISTM that you have two separate issues.
>
> The "sorry, too many clients already" error means that you've reached
> the maximum number of connections, as set with the max_connections
> setting. The autovacuum processes needs one connection to run.
>
> I suspect that the deadlock is not related to the autovacuum, but just
> an interaction between your transactions and the REINDEX.
>
> What does the update_itemcount() function look like? Are you running
> the REINDEX in a transaction?
>
> Why do you need to reindex every 5 minutes? How long does the vacuum
> run normally?
>
Janar Kartau wrote: > I know what causes the "sorry, too many clients already" error, but the > number of connections shouldn't grow so big.. so i guess the table > (category_tree) got locked. If that happens again, take a look at pg_stat_activity or ps output to see what's going on. Something must be using the connections, a locked table alone won't give you that error message. > We do REINDEX every 5 minutes because the table gets updated very often > and the query's took a lot of time. I think since we use autovacuum now, > it's not needed anymore. Yeah, a vacuum is normally enough to keep the indexes in good shape. I'd suggest removing the REINDEX call and either letting autovacuum handle it, or adding an explicit VACUUM call after the commit. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com