Re: Server slowing down over time

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(at)perceptron(dot)com> wrote:

>
>
>
>
> *From:* pgsql-performance-owner(at)postgresql(dot)org [mailto:
> pgsql-performance-owner(at)postgresql(dot)org] *On Behalf Of *Jean Cavallo
> *Sent:* Thursday, August 27, 2015 1:21 PM
> *To:* pgsql-performance(at)postgresql(dot)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