[HACKERS] Improve the performance of the standby server when dropping tableson the primary server - Mailing list pgsql-hackers

Hi,

The attached patch changes data structure storing unowned SMgrRelation objects 
from list structure to hash structure.
The reason why I change it is that list structure very slowly removes a node.
And list structure takes longer time to remove a node than hash structure.

The problem was reported in BUG #14575.
https://www.postgresql.org/message-id/20170303023246.25054.66379@wrigleys.postgresql.org

In my customer's case, the standby server was delayed more than 20 minites 
when dropping many table at once.


 - Performance check

I confirmed the performance of dropping tables by the following method.

1. Set up a synchronous streaming replication environment.
   And set synchronous_commit = remote_apply in postgresql.conf.

2. Create 100,000 tables (tbl1, tbl2, ... , tbl100000).
   And insert one row in each table.

3. Measure the time to drop 50 tables by psql

  $ time psql -d ${DATABSE} -p ${PORT} -f drop.sql 

drop.sql
--
begin;
drop table tbl1;
drop table tbl2;
...
drop table tbl50;
commit;
--

Result:
without this patch
real    0m3.734s
user    0m0.003s
sys     0m0.005s

with this patch
real    0m1.292s
user    0m0.005s
sys     0m0.003s

Even in this case, we have improved considerably, 
so I suggest you might approve it.

Regards,
Takashi Tokuda


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers