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 <iney...@perceptron.com> wrote: > > > > > *From:* pgsql-performance-ow...@postgresql.org [mailto: > pgsql-performance-ow...@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
