0

I have an Ubuntu 18 Server configured as a PostgreSQL 12 database server which should be remotely accessed. And I am connectiong to databae from my home using ip address. After I connected to database, I am running some queries. The responses come very fast at first. But after 1-2 minutes later I run a new query, the response comes 15-20 seconds. The next 2-3 queries runs fast. So there is a periodic slowdown. What could be the reason for this?

barteloma
  • 6,403
  • 14
  • 79
  • 173
  • Please try to post EXPLAIN ANALYZE for a fast query and EXPLAIN ANALYZE for a slow query. – pifor Jun 29 '20 at 12:20
  • Actually this is not about only queries or explain analyse. This problem occured while creating a new table or altering a table as well. – barteloma Jun 29 '20 at 12:28
  • Try to check what is going with in database with `pg_stat_activity`: are they any sessions blocked ? Do you have messages in the PostgreSQL log ? What is going on the machine hosting PostgreSQL ? – pifor Jun 29 '20 at 12:51

1 Answers1

0

There are many possibilities that can explain this that have little to do with PostgreSQL itself. Because you say that the pause happens with DDL operations, it sounds to me like one of these may be the problem.

What client are you using? Does the client have an auto-reconnection capability? If so, then your connection may be being closed while idle by a firewall between you and your server, and your client has to detect that the connection was closed and then establish a new one. If this is the case, then check to see if your client supports keepalive.

Does your remote Ubuntu server have enough memory? Is it busy with other tasks? If so, then an idle period may cause postmaster to be deprioritized or swapped out by the OS, and it takes a while for it to be "awakened" to serve your next request.

Is your remote Ubuntu server virtual? Is the host for that VM busy or overcommitted? If so, then your entire Ubuntu VM may be swapped out by the hypervisor on idle.

Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • I am using Datagrip client and QGIS desktop application. Both of them ahs same problem. May it be a firewall issue? Or postgresql idle settings? – barteloma Jun 29 '20 at 13:17
  • @barteloma Datagrip has a keep-alive capability according to the docs https://www.jetbrains.com/help/datagrip/configuring-database-connections.html#configuring-connection-options You may want to put it on a short interval of 15 seconds to see if it solves your problem. A keep-alive could address any of the problems I listed, but if it is a resources problem, then it will cause you problems later. – Mike Organek Jun 29 '20 at 13:37