On Wed, Oct 7, 2015 at 4:29 AM, FattahRozzaq <ssoorruu@gmail.com> wrote:
> Response from you all are very precious.
>
> @Merlin,
> I'm misunderstood the question.
> Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use
> nmon for disk IO, iftop for the network utilization).
> Did 1 connection need 1 core dedicatedly?
> (I was having 40-80 connections in stable condition. And when the problem
> happened the connections would be more than 150)
>
> @Scott,
> Just before the problem happened and when the problem happened, my server
> didn't running out of IO/RAM/CPU.
> The SSD IO total usage was 25-50% (I use nmon to monitor the disk IO)
> The RAM total usage was 4-5GB of total 128GB (I monitor it using htop)
> The CPU was 100% in 2 cores, 70% in 3 cores, the other 19 cores were under
> 5% (I monitor it using htop)
> The network interface utilization was only 300-400 Mbps of total 1Gbps (I
> monitor it using iftop)
> So, maybe the 128GB RAM will never all be fully use by PostgreSQL?
Check what vmstat 10 has to say, specifically the in and cs columns,
which is interrupts and context switches per second. What you'll
likely see is it ranging from 10k to 20k normally and spiking to 10 or
100 times when this is happening. That's the typical symptom that your
OS is spending all its time trying to switch between 1,000 processes
instead of servicing a handful at a time.
You should also see a huge uptick in pg processes that are active at
once, either in top or via pg_stat_activity.
Don't worry about making PostgreSQL use all your RAM, the OS will do
that for you, worry about getting PostgreSQL to process as many
queries per second as it can. And you do that by using a connection
pooler. I have machines with 350G dbs on machines with > 512GB RAM,
and eventually the whole db is in kernel cache and the only IO is when
blocks get written to disk. But the kernel only caches the parts of
the db that get read. If your db isn't reading more than a few dozen
gigabytes then that's how much memory will be used to cache the db.