Postgres — Good for Queuing implementation?
Few days back we got a CloudWatch alert for CPU Utilization for one of the Postgres databases hosted on Amazon Aurora. Looking at Performance Insights we got the top CPU consuming queries.
UPDATE events_tab1
SET locked_by = $1,
last_updated_by = $2,
last_updated=$3,
locked_at=$4,
rtcount = rtcount + ?
WHERE id IN (
(
SELECT id
FROM events_tab1
WHERE rtcount < $5
AND locked_at < $6) FOR UPDATE skip locked limit $7 ) returning *Indexes on events_tab1:
"events_tab1_pk" PRIMARY KEY, btree (id)
"events_tab1_la_rcnt" btree (locked_at, rtcount)
Actually top 5 CPU consuming queries were all related to “FOR UPDATE skip locked” on separate tables. I was new to the syntax “FOR UPDATE skip locked” and upon a quick search I came to know about queuing implementation in Postgres.
That’s Nice. I have seen people making decoupled architecture using messaging services like SQS for implementing queues. Here Postgres has given inbuilt queuing features.
Coming back to the CPU spike issue, on further inspection I noticed that dead tuples are high compared to the number of rows for all those 5 tables even though autovacuum is running regularly on them. That’s the reason, Indexes are fragmented (Query plans are going through Index scan).
select n_live_tup,n_dead_tup,last_autovacuum,current_time from pg_stat_user_tables where relname='events_tab1';n_live_tup | n_dead_tup | last_autovacuum | timetz
------------+------------+-------------------------------+----------
51 | 797750 | 2021-04-03 14:30:32 | 14:49:29select count(*) from events_tab1;
count
-------
25
On further inspection I found that there is one session running for more than 24 hours and in active state. Now one condition for vacuuming is that it does not clean up dead tuples after xmin of the oldest running transaction and that means dead tuples generated in the last 24 hours did not get cleaned up for all the tables.
However that’s a known thing in Postgres and we should keep a watch for long running transactions specially “idle in transaction” ones.
I encountered this issue earlier as well but one thing I was not able to understand is why one long running query on some table blocks the vacuuming process for all other tables. I raised this question in the Postgres community and I was quite happy looking at quick response from community members.
You can read more about this here — https://www.postgresql.org/message-id/19474.1572022017%40sss.pgh.pa.us
Honestly I still need to sit back and understand why the query on table2 blocks vacuuming on table1 and then also compare how other RDBMS are functioning in this aspect.
Other interesting thing here is — Only those 5 queries on events tables (for queuing mechanism) are eating more CPU if vacuuming has not happened on respective table (CPU increases with dead tuples increase in events tables). Workloads for other tables are not impacted that much.This may happen due to too much DELETE/UPDATE/SELECT on those queuing tables and then may be how the queue mechanism works internally in Postgres (more cpu cycles?).
Anyways after killing that long running session (that was manual query from user generated session), CPU brings down to 40% (on 96 vCPU machine).
To terminate a long running query/transaction, we can set statement_timeout or idle_in_transaction_session_timeout parameters after carefully considering the workload.
Why is CPU still 40%? I see the same events table queries still coming in TOP CPU consuming queries. There are many other application generated queries which usually run for 2–3 hours (those are for reporting purposes on Reader instance, hot_standby_feedback is always on on Aurora). So we can not do much in this case and that means there would always be dead tuples accumulated for the last 2–3 hours on all the tables.
Out of curiosity I did a few more tests to see how vacuuming works when other transactions are in progress.
Session 1:
create table test (age int);
CREATE TABLE
insert into test select generate_series(1,100000);
INSERT 0 100000
create index idx on test(age);
CREATE INDEXSession2: (starts a transaction but does not end)create table test2(age int);
CREATE TABLEBEGIN;
BEGIN
select count(*) from test2;
count
-------
0
Now again in Session1, run delete statement and we can see vacuum does not clean up dead rows. SELECT gives 0 rows as expected but dead tuples are not cleaned up.
delete from test;
DELETE 100000
vacuum verbose test;
INFO: vacuuming "public.test"
INFO: index "idx" now contains 100000 row versions in 276 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: "test": found 0 removable, 100000 nonremovable row versions in 443 out of 443 pages
DETAIL: 100000 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.select count(*) from test;
count
-------
0
I was trying to create another Index and did full vacuum as well but as expected they also obeyed the same rule (brings dead tuples in new Index and table and their size remains same).
\dt+ test
Schema | Name | Type | Owner | Persistence | Size |
--------+------+-------+---------------+-------------+---------+----
public | test | table | fintech_admin | permanent | 3568 kB |\di+ idx
Schema | Name | Type | Owner | Table | Persistence | Size
--------+------+-------+---------------+-------+-------------+------
public | idx | index | fintech_admin | test | permanent | 2208 kB select count(*) from test;
count
-------
0create index idx1 on test(age);
CREATE INDEX\di+ idx1
Schema | Name | Type | Owner | Table | Persistence | Size
--------+------+-------+---------------+-------+-------------+------
public | idx1 | index | fintech_admin | test | permanent | 2208 kB vacuum full test;
VACUUM\dt+ test
Schema | Name | Type | Owner | Persistence | Size |
--------+------+-------+---------------+-------------+---------+---
public | test | table | fintech_admin | permanent | 3576 kB |
Possible Solutions (exploring):
- PG_REPACK utility might be handy here to remove the fragmentation/dead tuples. As it creates a new table and switches table names, it should copy those relevant rows only without dead tuples (which normal vacuum is not able to clean up). However pg_repack will be needed to schedule quite frequently, maybe every hour (that momentarily lock could be another issue here).
- Application team is also looking to tune some of the queuing parameters like parallel threads, sleep time (wait time between multiple polls) and then batch size (how many records in a single poll).
Closing Notes:
At 1st glance inbuilt queuing implementation in Postgres looks pretty good, though we are seeing high CPU Utilization because of other long running queries. Actually that may not be completely related to queuing mechanism but overall any table with very high DML and high access rate. But in the end that is why queues are implemented for.
Any thoughts, experiences, Please share.
