Re: Re: Declarative partitioning optimization for largeamount of partitions - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject Re: Re: Declarative partitioning optimization for largeamount of partitions
Date
Msg-id 20170324132258.GB16830@e733.localdomain
Whole thread Raw
In response to Re: Re: Declarative partitioning optimization for largeamount of partitions  (Aleksander Alekseev <a.alekseev@postgrespro.ru>)
List pgsql-hackers
Also I would like to share some benchmark results.

Before applying any patches (copied from one of previous messages):

```
latency average = 1153.495 ms
latency stddev = 154.366 ms
tps = 6.061104 (including connections establishing)
tps = 6.061211 (excluding connections establishing)
```

After applying first patch (copied as well):

```
latency average = 853.862 ms
latency stddev = 112.270 ms
tps = 8.191861 (including connections establishing)
tps = 8.192028 (excluding connections establishing)
```

After applying both patches:

```
latency average = 533.646 ms
latency stddev = 86.311 ms
tps = 13.110328 (including connections establishing)
tps = 13.110608 (excluding connections establishing)
```

Small amount of partitions (2 to be exact), no patches:

```
latency average = 0.928 ms
latency stddev = 0.296 ms
tps = 7535.224897 (including connections establishing)
tps = 7536.145457 (excluding connections establishing)
```

Small amount of partitions, bot patches applied:

```
latency average = 0.915 ms
latency stddev = 0.269 ms
tps = 7638.344922 (including connections establishing)
tps = 7639.164769 (excluding connections establishing)
```

As you can see these patches don't make things worse in any regard.

Perf shows that both bottlenecks are gone. Before [1] and after [2].

[1] http://afiskon.ru/s/00/2008c4ae66_temp.png
[2] http://afiskon.ru/s/a5/fd81628a3a_temp.png

On Fri, Mar 24, 2017 at 03:17:44PM +0300, Aleksander Alekseev wrote:
> Hi Anastasia,
>
> Thanks a lot for a review!
>
> As was mentioned above there is also a bottleneck in find_all_inheritors
> procedure. Turned out the problem there is similar and it could be easily
> fixed as well. Corresponding patch is attached to this message. To keep
> things in order I'm attaching the previous patch as well.

--
Best regards,
Aleksander Alekseev

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Patch: Write Amplification Reduction Method (WARM)
Next
From: David Steele
Date:
Subject: Re: increasing the default WAL segment size