Archive

Archive for July, 2021

Postgres performance gone south but nothing changed !

July 14th, 2021

Postgres seems pretty cool as open source databases go. I’m finding a much greater affinity for Postgres than say MySQL.

On the other hand, coming from an Oracle background there are a few things that come across as strange on Postgres. Most of the weirdness for me centers around MVCC,

Here is one new surprise for me.

I’m running a steady workload on a set of tables with inserts, updates, deletes and selects. This is putting a decent load on the database.

Then at 20:45, I simply do:


\set AUTOCOMMIT off
\echo :AUTOCOMMIT
create table toto ( id int);
insert into toto values (1);
commit;
select * from toto for update;

and throughput goes down and CPU load goes up.

 

At 20:45 “idle_in_transaction_max_time”, the last time series graph below in yellow starts increasing until 21:04  when I do a “commit” after the “select * from toto for update;”.

Between 20:45 and  21:04 “idle_in_transaction_max_time” increases, the DB Load (last chart) CPU time increases and throughout “Transactions.xact_commit” goes down,  the first graph.

 

 

image (3)

image (4)

 

In Postgres, dead rows are left in blocks until the vacuum process cleans them out.

The vacuum process can only clean out dead rows older than the oldest open transaction.

Thus if there is an open transaction and then new transactions doing deletes and inserts leaving dead rows, they will pile up because vacuum can’t clean them out.

 

Uncategorized