Opened 9 months ago

Last modified 9 months ago

#13450 assigned Question/How do I...?

May the Postgres deamon go to swap?

Reported by: https://id.mayfirst.org/gripuqam Owned by: https://id.mayfirst.org/jamie
Priority: Medium Component: Tech
Keywords: Cc:
Sensitive: no

Description

This is a question I have since a few years.

On the servers I use at Mayfirst, Postgres seems to be much less used than MySQL. I would imagine that the deamon can have long periods of time where it is not used. I also have observed very often that the first time I go on a website (hosted on theses servers) in the morning (even using wget) or after a long period without use, it is slow. I also received complaints from users about that. I would easily imagine that the Postgres deamon is pushed to swap during a long period of inactivity. I don't know if this is possible or if there is another explanation.

What are the suggested ways to solve this issue? The ways I see are like a cron task visiting the site or starting psql with a well chosen command. Any suggestion?

Change History (4)

comment:1 Changed 9 months ago by https://id.mayfirst.org/jaimev

  • Owner set to https://id.mayfirst.org/jamie
  • Status changed from new to assigned

Let's get jamie's input on this.

comment:2 Changed 9 months ago by https://id.mayfirst.org/jamie

We don't use swap space on our servers (instead, we try to allocate enough RAM to avoid swapping entirely).

I don't know the inner workings of postgres, but I'm guessing that it keeps data in RAM, but with some kind of expiration that, once passed, forces postgres to reload the data from disk and that is what is causing the slow down.

Which servers are you experiencing this problem with? If available, another option might be to allocate some SSD space for postgres which would speed these things up. Otherwise, perhaps the wget cron job idea would work.

comment:3 Changed 9 months ago by https://id.mayfirst.org/gripuqam

Thanks for this information!

At the moment, I was checking the Drupal 7 website sosterritoireweb@chelsea. Today I made some more precise timing. I see some other Drupal 8 websites responding better, which is encouraging on longer term.

The Idea of some SSD space looks good, would it mean some tables would be moved to SSD?

We also spoke at some point (with dkg https://support.mayfirst.org/wiki/members/GRIP-UQAM/Postgres#Postgresforcachetables), about having some cache tables in RAM. Probably the best scenario would be to have a very few tables in ram, some more in ssd and the rest on regular disk.

The things I'll need to do would be:

  • Choosing which tables to put where (not yet clear to me)
  • Changing the Drupal postgres config of the website.

Priorities I am trying to put priorities in the different actions which can be taken. I need some more timing information and I am thinking about some subtle SQL commands I could send to psql for timing. Wget cron job is easy. Non-Durable Settings for cache seems to be the next priority, but I do not know yet how to check if Drupal 7 or 8 uses Non-Durable Settings for cache. Then I could learn to move tables to SSD or RAM.

comment:4 Changed 9 months ago by https://id.mayfirst.org/jamie

Unfortunately, I spoke to soon. We don't have SSD available on chelsea :(.

So I think your best option would be to experiment with a cron job to run queries on a regular basis to load the data into RAM.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.