Hi!
Here is the case.
Assume we have a master to slave replication with shared_buffers set up
to 2 GB at the master and 4 GB at the slave. All of the data is written
to the master, while reading occurs from slave.
Now we decided to drop many tables, let's say 1000 or 10000 not in a
single transaction, but each table in a separate one. So, due to "plain"
shared_buffers memory we have to do for loop for every relation which
leads to lag between master and slave.
In real case scenario such issue lead to not a minutes lag, but hours
lag. At the same time PostgreSQL have a great routine to delete many
relations in a single transaction.
So, to get rid of this kind of issue here came up an idea: what if not
to delete everyone of relations right away and just store them in an
array, prevent shared buffers (correspond to a deleted relations) from
been flushed. And then array reaches it max size we need to walk all
buffers only once to "free" shared buffers correspond to a deleted
relations.
Here some values from the test which I am made.
Without patch:
1.
(master 2 GB) - drop 1000 tables took 6 sec
(slave 4 GB) - drop 1000 tables took 8 sec
2.
(master 4 GB) - drop 1000 tables took 10 sec
(slave 8 GB) - drop 1000 tables took 16 sec
3.
(master 10 GB) - drop 1000 tables took 22 sec
(slave 20 GB) - drop 1000 tables took 38 sec
With patch:
1.
(master 2 GB) - drop 1000 tables took 2 sec
(slave 4 GB) - drop 1000 tables took 2 sec
2.
(master 4 GB) - drop 1000 tables took 3 sec
(slave 8 GB) - drop 1000 tables took 3 sec
3.
(master 10 GB) - drop 1000 tables took 4 sec
(slave 20 GB) - drop 1000 tables took 4 sec
--
Max Orlov
E-mail: m.orlov@postgrespro.ru