Re: Perf decreased although server is better

I've noticed a network latency increase. Ping between web server and
database : 0.6 ms avg before, 5.3 ms avg after -- it wasn't that big 4 days
ago :(

I've narrowed my investigation to one particular "Transaction" in terms of
the NewRelic APM. It's basically the main HTTP request of my application.

Looks like the ping impacts psycopg2:connect (see http://imgur.com/a/LDH1c)
4 ms up to 16 ms on average.

That I can understand. However, I don't understand the performance decrease
of the select queries on table1 (see https://i.stack.imgur.com/QaUqy.png)
80 ms up to 160 ms on average

Same goes for table 2 (see http://imgur.com/a/CnETs) 4 ms up to 20 ms on
average

However, there is a commit in my request, and it performs better (see
http://imgur.com/a/td8Dc) 12 ms down to 6 ms on average.

I don't see how this can be due to network latency!

I will provide a new bonnie++ benchmark when the requests per minute is at
the lowest (remember I can only run benchmarks while the server is in use).

Rick, what did you mean by kernel configuration? The OS is a standard
Ubuntu 16.04:

- Linux 4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016
x86_64 x86_64 x86_64 GNU/Linux

Do you think losing half the number of cores can explain my performance
issue ? (AMD 8 cores down to Haswell 4 cores).

Best Regards,

Benjamin

PS : I've edited the SO post
http://serverfault.com/questions/812702/posgres-perf-decreased-although-server-is-better

2016-11-04 1:05 GMT+01:00 Kevin Grittner <kgrittn(at)gmail(dot)com>:

> On Thu, Nov 3, 2016 at 9:51 AM, Benjamin Toueg <btoueg(at)gmail(dot)com> wrote:
> >
> > Stream gives substantially better results with the new server
> (before/after)
>
> Yep, the new server can access RAM at about twice the speed of the old.
>
> > I've run "bonnie++ -u postgres -d /tmp/ -s 4096M -r 1096" on both
> > machines. I don't know how to read bonnie++ results (before/after)
> > but it looks quite the same, sometimes better for the new,
> > sometimes better for the old.
>
> On most metrics the new machine looks better, but there are a few
> things that look potentially problematic with the new machine: the
> new machine uses about 1.57x the CPU time of the old per block
> written sequentially ((41 / 143557) / (16 / 87991)); so if the box
> becomes CPU starved, you might notice writes getting slower than on
> the new box. Also, several of the latency numbers are worse -- in
> some cases far worse. If I'm understanding that properly, it
> suggests that while total throughput from a number of connections
> may be better on the new machine, a single connection may not run
> the same query as quickly. That probably makes the new machine
> better for handling an OLTP workload from many concurrent clients,
> but perhaps not as good at cranking out a single big report or
> running dump/restore.
>
> Yes, it is quite possible that the new machine could be faster at
> some things and slower at others.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>