Thread: [HACKERS] Improve the performance of the standby server when dropping tableson the primary server

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

On 1 August 2017 at 05:45, Tokuda, Takashi
<tokuda.takashi@jp.fujitsu.com> wrote:
> 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

Interesting, thanks for the patch.

Couple of points regarding performance...

* The previous coding allowed for a fast path to access the last
unowned relation, which this patch removes. It seems a good idea to
cache the last unowned relation, or if not explain why the comment
that says why it worked that way is no longer true.

* We should only create the hash table when needed, i.e. on or after
when we add an unowned relation, since that is not a typical case.

* The hash table is sized at 400 elements and will grow from there.
The comments in dynahash say "An overly large nelem will penalize
hash_seq_search speed without buying much." so this makes your patch
suitable for the bulk case but likely to perform worse for fewer
elements. So I'm guessing that you picked 400 because that's what the
parameter is set to for the smgr relation table rather than because
this has had good consideration.

I'll take your word for now that it improves the main case but I'd
suggest you consider the performance effects of the patch on other
cases that use this code.

Without looking deeper: does this code also run for temp objects? Can
it be optimized for that case a little better?

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi,

> * The previous coding allowed for a fast path to access the last
> unowned relation, which this patch removes. It seems a good idea to
> cache the last unowned relation, or if not explain why the comment
> that says why it worked that way is no longer true.
> 
> * We should only create the hash table when needed, i.e. on or after
> when we add an unowned relation, since that is not a typical case.
> 
> * The hash table is sized at 400 elements and will grow from there.
> The comments in dynahash say "An overly large nelem will penalize
> hash_seq_search speed without buying much." so this makes your patch
> suitable for the bulk case but likely to perform worse for fewer
> elements. So I'm guessing that you picked 400 because that's what the
> parameter is set to for the smgr relation table rather than because
> this has had good consideration.

I thought abount improving the above problems.
But I have no good ideas to improve every case.
Do you have any good ideas?

I suggest to apply this modification only for the startup process.
This is because the startup process has many unowned SMgrRelation objects.
In XLOG replay, statup process create fake relcaches.
Fake relcaches create unowned SMgrRelation objects.
So startup process has more unowned SMgrRelation objects than any other process.

Of cource, it is necessary to think about the problems such as hash size.
Do you think about it.

Regards, Takashi Tokuda