Re: [HACKERS] SendRowDescriptionMessage() is slow for queries with alot of columns

On Sat, Sep 16, 2017 at 3:03 AM, Andres Freund <andres@anarazel.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.cy@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers