Re: Perf decreased although server is better

On 11/02/2016 02:26 PM, Benjamin Toueg wrote:
> Hi everyone,
>
> I'm facing a peformance decrease after switching to a more performant
> VPS :
> http://serverfault.com/questions/812702/posgres-perf-decreased-although-server-is-better
>

Well, changing so many things at once (CPU, RAM, storage, Ubuntu 
version, probably kernel version, PostgreSQL version) is a bad idea, 
exactly because it makes investigating regressions more complicated.

> My questions are:
>
>  1. What benchmark should I perform before switching to a new server?

Three types of benchmarks, in this order:

1) system-level benchmarks to test various resources (fio to test disks, 
etc.)

2) general-purpose PostgreSQL benchmarks (regular pgbench, ...)

3) application-specific benchmarks, or at least pgbench with templates 
that match your workload somewhat better than the default one

Start with (1), compare results between machines, if it's OK start with 
(2) and so on.

>  2. What's your rule of thumb regarding my specific issue? What should
>     be investigated first?
>

There's a bottleneck somewhere. You need to identify which resource is 
it and why, until then it's just wild guessing. Try measuring how long 
the requests take at different points - at the app server, at the 
database, etc. That will tell you whether it's a database issue, a 
network issue etc. If the queries take longer on the database, use 
something like perf to profile the system.

ISTM it's not a disk issue (at least the chart shows minimum usage). But 
you're doing ~400tps, returning ~5M rows per second.

Also, if it turns out to be a database issue, more info about config and 
data set would be useful.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services