Re: SendRowDescriptionMessage() is slow for queries with a lot of columns

On Sat, Sep 16, 2017 at 3:03 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Hi Thom,
>
> On 2017-09-15 22:05:35 +0100, Thom Brown wrote:
>> Okay, I've retested it using a prepared statement executed 100,000
>> times (which selects a single row from a table with 101 columns, each
>> column is 42-43 characters long, and 2 rows in the table), and I get
>> the following:
>>
>> master:
>>
>> real 1m23.485s
>> user 1m2.800s
>> sys 0m1.200s
>>
>>
>> patched:
>>
>> real 1m22.530s
>> user 1m2.860s
>> sys 0m1.140s
>>
>>
>> Not sure why I'm not seeing the gain.
>
> I suspect a part of that is that you're testing the patch in isolation,
> whereas I tested it as part of multiple speedup patches. There's some
> bigger bottlenecks than this one. I've attached my whole stack.
>
> But even that being said, here's the result for these patches in
> isolation on my machine:
>

I have run the same test on Scylla for the single client. I have used
the same steps script as shared by you in above mail.
[mithun(dot)cy(at)localhost ~]$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 56
On-line CPU(s) list: 0-55
Thread(s) per core: 2
Core(s) per socket: 14
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 63
Model name: Intel(R) Xeon(R) CPU E5-2695 v3 @ 2.30GHz
Stepping: 2
CPU MHz: 1200.761
BogoMIPS: 4594.35
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 35840K
NUMA node0 CPU(s): 0-13,28-41
NUMA node1 CPU(s): 14-27,42-55

(result is median of 3 pgbench runs, each run 5 mins)

Base TPS:
========
12199.237133

With Patches TPS:
===============
(0002-Add-more-efficient-functions-to-pqformat-API.patch +
0003-Improve-performance-of-SendRowDescriptionMessage.patch)

13003.198407 (an improvement of 6.5%)

Perf report also says same.
Base: perf_bmany_cols
-------------------------------
19.94% 1.86% 11087 postgres postgres [.]
SendRowDescriptionMessage
|
---SendRowDescriptionMessage
|
|--99.91%-- exec_describe_portal_message
| PostgresMain
| ExitPostmaster
| BackendStartup
| ServerLoop
| PostmasterMain
| startup_hacks
| __libc_start_main
--0.09%-- [...]

After Patch: perf_many_cols
--------------------------------------
16.80% 0.04% 158 postgres postgres [.]
SendRowDescriptionMessage
|
---SendRowDescriptionMessage
|
|--99.89%-- exec_describe_portal_message
| PostgresMain
| ExitPostmaster
| BackendStartup
| ServerLoop
| PostmasterMain
| startup_hacks
| __libc_start_main
--0.11%-- [...]

So I think performance gain is visible. We saved a good amount of
execution cycle in SendRowDescriptionMessagewhen(my callgrind report
confirmed same) when we project a large number of columns in the query
with these new patches.

--
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com