Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

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
>