On 2018/07/16 13:16, Tsunakawa-san wrote:
>Thanks. And what does the comparison look like between the unpartitioned case
>and various partition counts? What's the performance characteristics in terms
>of the latency and partition count? I thought that's what you tried to
>reveal first?
In unpartitioned table, latency of SELECT/UPDATE statement is close to O(n),
where n is number of records.
Latency of INSERT statements is close to O(1).
In partitioned table, up to 400 partitions, latency of SELECT/INSERT statement
is close to O(log n), where n is the number of partitions.
Between 400 and 6400 partitions, latency is close to O(n).
Up to 400 partitions, latency of UPDATE statement is close to O(n).
Between 400 and 6400 partitions, latency of UPDATE statement seems to O(n^2).
Details are as follows.
unpartitioned table result (prepared mode)
------------------------------------------
scale | latency_avg | tps_ex | update_latency | select_latency |
insert_latency
-------+-------------+-------------+----------------+----------------+----------------
100 | 0.24 | 4174.395738 | 0.056 | 0.051 |
0.04
200 | 0.258 | 3873.099014 | 0.065 | 0.059 |
0.04
400 | 0.29 | 3453.171112 | 0.081 | 0.072 |
0.041
800 | 0.355 | 2814.936942 | 0.112 | 0.105 |
0.041
1600 | 0.493 | 2027.702689 | 0.18 | 0.174 |
0.042
3200 | 0.761 | 1313.532458 | 0.314 | 0.307 |
0.043
6400 | 1.214 | 824.001431 | 0.54 | 0.531 |
0.043
partitioned talble result (prepared mode)
-----------------------------------------
num_part | latency_avg | tps_ex | update_latency | select_latency |
insert_latency
----------+-------------+-------------+----------------+----------------+----------------
100 | 0.937 | 1067.473258 | 0.557 | 0.087 |
0.123
200 | 1.65 | 606.244552 | 1.115 | 0.121 |
0.188
400 | 3.295 | 303.491681 | 2.446 | 0.19 |
0.322
800 | 8.102 | 123.422456 | 6.553 | 0.337 |
0.637
1600 | 36.996 | 27.030027 | 31.528 | 1.621 |
2.455
3200 | 147.998 | 6.756922 | 136.136 | 4.21 |
4.94
6400 | 666.947 | 1.499383 | 640.879 | 7.631 |
9.642
regards,
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:[email protected]]
Sent: Monday, July 16, 2018 1:16 PM
To: Kato, Sho/加藤 翔 <[email protected]>
Cc: 'Amit Langote' <[email protected]>; PostgreSQL mailing lists
<[email protected]>
Subject: RE: How to make partitioning scale better for larger numbers of
partitions
From: Kato, Sho [mailto:[email protected]]
> I did pgbench -M prepared and perf record.
>
> UPDATE latency in prepared mode is 95% shorter than in simple mode.
> SELECT latency in prepared mode is 54% shorter than in simple mode.
> INSERT latency in prepared mode is 8% shorter than in simple mode.
Thanks. And what does the comparison look like between the unpartitioned case
and various partition counts? What's the performance characteristics in terms
of the latency and partition count? I thought that's what you tried to reveal
first?
Regards
Takayuki Tsunakawa
