Date: Wed, 20 Feb 2019 19:32:07 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Gunther <raj@gusw.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: neither CPU nor IO bound, but throttled performance
Message-ID: <20190221013207.GW28750@telsasoft.com>
References: <1b6d8b15-75eb-f00e-2d16-27f79d81eae7@gusw.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1b6d8b15-75eb-f00e-2d16-27f79d81eae7@gusw.net>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Wed, Feb 20, 2019 at 06:32:49PM -0500, Gunther wrote:
> Hi, I have an Amazon Linux based Postgresql 11 server here on a t2.medium
> EC2 instance.
> 
> Everything flows quite nicely. Except, I don't understand why I can't max
> out the CPU or the IO, instead, IO is almost negligible yet the CPU is at
> 30% hardly hitting 50%.

> avg-cpu: %user %nice %system %iowait %steal %idle 36.41 0.00 3.80 0.00 59.78
> 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await
> r_await w_await svctm %util xvda 0.00 0.00 0.00 7.69 0.00 92.31 24.00 0.02

This is unreadable, please try to attach it ?

> previously I had hit 100 %util here, that was when I didn't have the tables
> so spread out over so many tablespaces. Now I have it spread out like in the
> olden days where you spread out your tables over many "spindles", and I did
> this here so I could see which tables or indexes would be bottlenecks.

What was the old storage configuration and what is it now ?

> So how can it be that queries take quite long without the process running at
> higher CPU%?

You said everything flows nicely, but take a long time, and "throttled", can
you show an high-level performance change ?  Other than %cpu or %io.

Justin