Re: Server slowing down over time - Mailing list pgsql-performance

From Wei Shan
Subject Re: Server slowing down over time
Date
Msg-id CAFe9ZTpVSFNvr_txe3G2-tdurvVLWDwp+mN9ONU=3wWg5xnGDg@mail.gmail.com
Whole thread Raw
In response to Re: Server slowing down over time  (Igor Neyman <ineyman@perceptron.com>)
List pgsql-performance
Could you check pg_locks table to see if there's any major difference between "healthy" state and "slowing down" state?

On 3 September 2015 at 21:07, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jean Cavallo
Sent: Thursday, August 27, 2015 1:21 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Server slowing down over time

 

Hi,

 

I am currently working on a data migration for a client.

The general plan is :

  - Read data from a postgresql database

  - Convert them to the new application

  - Insert in another database (same postgresql instance).

 

The source database is rather big (~40GB, wo indexes), and the

conversion process takes some time. It is done by multiple workers

on a separate Linux environnement, piece by piece.

 

When we start the migration, at first it looks good.

Performances are good, and it ran smoothly. After a few hours,

we noticed that things started to slow down. Some queries seemed

to be stuck, so we waited for them to end, and restarted the server.

 

After that it went well for some time (~10 minutes), then it slowed

down again. We tried again (a few times), and the pattern repeats.

 

My postgresql specific problem is that it looks like the server gets

stuck. CPU usage is <10%, RAM usage is under 50% max, there is

no noticeable disk usage. But, there are some (<10) active queries,

some of which may take several hours to complete. Those queries

work properly (i.e < 1min) right after the server restarts.

 

So my question is : What could slow the queries from ~1min to 2hours

which does not involve CPU, Memory, or disk usage, and which would

"reset" when restarting the server ?

 

For information, the number of processes does not seem to be the

problem, there are ~20 connections with max_connection set to 100.

We noticed at some point that the hard drive holding the target

database was heavily fragmented (100%...), but defrag did not

seem to change anything.

 

Also, the queries that appear to get stuck are "heavy" queries,

though after a fresh restart they execute in a reasonable time.

 

Finally, whatever causes the database to wait also causes the

Windows instance to slow down. But restarting Postgresql fixes

this as well.

 

Configuration :

 

The Postgresql server runs on a Windows Virtual Machine under

VMWare. The VM has dedicated resources, and the only other

VM on the host is the applicative server (which runs idle while

waiting for the database). There is nothing else running on the

server except postgresql (well, there were other things, but we

stopped everything to no avail).

 

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Windows 2008R2 (64 bits)

10 Go RAM

4 vCPU

 

Host : VMWare ESXi 5.5.0 build-2068190

CPU Intel XEON X5690 3.97GHz

HDD 3x Nearline SAS 15K RAID0

 

Please let me know if any other information may be useful.


Jean Cavallo

 

 

Having 4 CPUs, I’d try to decrease number of connections from ~20 to 8, and see if “slowing down” still happens.

 

Regards,

Igor Neyman

 




--
Regards,
Ang Wei Shan

pgsql-performance by date:

Previous
From: Claudio Freire
Date:
Subject: Re: query with pg_trgm sometimes very slow
Next
From: Tomas Vondra
Date:
Subject: Re: Server slowing down over time