Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

Hello,
do you have any advice on this?

Thanks a lot in advance

Il giorno gio 7 mar 2019 alle ore 09:39 Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
ha scritto:

> So the first file is on Postgres11.2 on a test server (and where I compare
> 10 vs 11)
> The second file, is our preprod machine running Postgres 11.2 (different
> hardware etc, it is a VM). I know that could be confusing, but I just
> wanted to compare that too because if you see the two files there's a lot
> of difference between the two machines.
> And they are both running CentOS 7.
>
> So at this point I have two problems. One inside the machine between
> Postgres 10 and 11 and another problem on the preprod (similar to prod)
> with a lot of lseek.
>
> Sorry if this is confusing, hope it is clear now.
>
> Regarding partitions/tables. The first file involves just one table. The
> second file (with a huge lseek) was running the test on a single table, but
> meanwhile it was accessible by the preprod web application. So it was maybe
> hit by some user and some other table.
>
>
> Question:
> 1) Is it possible that pgbench could not be really a good tool for testing
> the performances? If I use a sql script of thousands of insert records and
> compare on the same server between pg10 and pg11 I get pretty much the same
> result (maybe better on pg11)
> 2) regarding preprod, is there any way to reduce those lseek()? Just to
> let you know, comparing the same insert script between the first server,
> the first server takes 2m the second one takes 5-7m.
>
> Thanks a lot,
>
>
>
>
>
> Il giorno gio 7 mar 2019 alle ore 04:47 Thomas Munro <
> thomas(dot)munro(at)gmail(dot)com> ha scritto:
>
>> On Thu, Mar 7, 2019 at 12:12 AM Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
>> wrote:
>> > This is instead the strace of another server running the same version
>> compiled but that is even slower.
>>
>> Huh. That's a lot of lseek(). Some of these will be for random
>> reads/writes and will go way in v12, and some will be for probing the
>> size of relations while planning, and some while executing scans. I
>> bet you could make some of them go away by using prepared statements.
>> Does the query in your test involve many partitions/tables?
>>
>> % time seconds usecs/call calls errors syscall
>> ------ ----------- ----------- --------- --------- ----------------
>> 32.50 143.010306 7 21044095 lseek
>> 26.21 115.354045 14 8144577 read
>> 6.18 27.185578 16 1669889 10 sendto
>> 5.29 23.300584 57 407528 fdatasync
>> 4.93 21.709522 9 2313529 824174 recvfrom
>> 3.31 14.547568 19 765897 write
>> 2.73 12.007486 14 867088 14494 epoll_wait
>> 2.18 9.597460 15 659871 84097 futex
>> 1.85 8.147759 14 567414 close
>> 1.77 7.767832 18 437656 11319 open
>>
>> The other results had 1 usec lseek(), and much fewer of them relative
>> to the number of reads and writes. BTW, are you comparing v10 and v11
>> on the same hardware, kernel, filesystem? Just wondering if there
>> could be some change in syscall overhead on different kernel patch
>> levels or something like that: we see 7 usec vs 1 usec in those two
>> files (though I have no idea how reliable these times are) and if
>> we're going to call it 21 million times at some point it might
>> matter...
>>
>> --
>> Thomas Munro
>> https://enterprisedb.com
>>
>