Re: Recovery performance of DROP DATABASE with many tablespaces

On Wed, Jul 4, 2018 at 4:47 PM, Jamison, Kirk <k.jamison@jp.fujitsu.com> wrote:
> Hi, Fujii-san
>
> I came across this post and I got interested in it,
>  so I tried to apply/test the patch but I am not sure if I did it correctly.
> I set-up master-slave sync, 200GB shared_buffers, 20000 max_locks_per_transaction,
> 1 DB with 500 table partitions shared evenly across 5 tablespaces.
>
> After dropping the db, with or without patch,
> there were no difference in recovery performance when dropping database,
> so maybe I made a mistake somewhere. But anyway, here's the results.
>
> ======WITHOUT PATCH=======
> [200GB shared buffers]
> DROPDB only (skipped DROP TABLE and DROP TABLESPACE)
> 2018/07/04_13:35:00.161
> dropdb
> 2018/07/04_13:35:05.591         5.591 sec
>
> [200GB shared_buffers]
> DROPDB (including DROP TABLE and DROP TABLESPACE)
> real    3m19.717s
> user    0m0.001s
> sys     0m0.001s
>
> ======WITH PATCH=======
> [200GB shared_buffers]
> DROPDB only (skipped DROP TABLE and DROP TABLESPACE)
> 2018/07/04_14:19:47.128
> dropdb
> 2018/07/04_14:19:53.177         6.049 sec
>
> [200GB shared_buffers]
> DROPDB (included the DROP TABLE and DROP TABLESPACE commands)
> real    3m51.834s
> user    0m0.001s
> sys     0m0.002s
>
> Just in case, do you also have some performance test numbers/case
> to show the recovery perf improvement when dropping database that contain multiple tablespaces?

Thanks for testing!

TBH, I have no numbers measured by the test.
One question about your test is; how did you measure the *recovery time*
of DROP DATABASE? Since it's *recovery* performance, basically it's not easy
to measure that.

Regards,

-- 
Fujii Masao