0

We have a projects UI page where users can search projects. I noticed that search queries will be slow the first run (up to 10 seconds) and faster on subsequent runs (around 2 seconds). Could this explain the 2.28% disk hit? This seems like a high percentage right? If we want to ensure these queries are always fast, could we use pg_prewarm to keep them in memory? Would we need to run pg_prewarm periodically? This projects table is 15 GB and we have over 100 GB of server memory on Heroku.

           table name            | disk hits | % disk hits | % cache hits |  total hits
---------------------------------+-----------+-------------+--------------+--------------
 all                             | 730484143 |        0.32 |        99.68 | 225556991278
 projects                        |  34777074 |        2.28 |        97.72 |   1526319404

I'm also trying to understand the pg_prewarm config autoprewarm_interval. What is this used for? This means it will unload the projects table from memory for example?

Here's the memory from the logs https://devcenter.heroku.com/articles/heroku-postgres-metrics-logs

6 GB - sample#memory-postgres: Approximate amount of memory used by your database’s Postgres processes in kB. This includes shared buffer cache as well as memory for each connection.

125 GB - sample#memory-total: Total amount of server memory available.

875 MB - sample#memory-free: Amount of free memory available in kB.

114 GB - sample#memory-cached: Amount of memory being used by the OS for page cache, in kB.

8
  • 1
    I'd say that the only way to avoid this is to have the whole database cached in RAM. But perhaps you can tune the query to be faster and hit fewer 8kB blocks. Nov 18 '21 at 7:33
  • Thanks! It turns out users typically query for projects created within the last three or six months. Three months of projects is 1 GB. Could it make sense to query like every hour the most recent three or six months of projects to push it into the cache?
    – Todd
    Nov 19 '21 at 5:48
  • 1
    That seems excessive. Nov 19 '21 at 6:24
  • 1
    Maybe not excessive - it just seems complicated to do that, and it is uncertain - the tables could still drop from cache. More memory seems to be the safer way. Is there a chance to reduce the footprint of the query, so that it has to read less and is faster? Nov 19 '21 at 20:41
  • 1
    Searching for something like W:* can never be efficient. Usually, you are doing yourself and the user a favor if you force them to use at least one selective criterion that is well indexed. Nov 20 '21 at 5:52
1

Could this explain the 2.28% disk hit?

Certainly could. Disk hits are the reason for the slow first run each time.

could we use pg_prewarm to keep them in memory? Would we need to run pg_prewarm periodically?

Yes, pg_prewarm can populate the cache. But nothing keeps the OS (or Postgres - two different caches) from evicting pages from cache if it's needed for other data. So it depends on competing activity how long pages remain in cache.
If your queries don't have side-effects (purely SELECT) and don't include expensive computations, you might also just run the queries of interest from time to time. May be even better to just pull in the right data pages from table and indices. (Neither has to be cached as a whole.) That depends on what you know about expected queries.

See:

This projects table is 15 GB and we have over 100 GB of server memory on Heroku.

That's not the whole picture.

  • How much of the 100 GB RAM is available for caching? (Postgres file buffer and OS cache.) work_mem, maintenance_work_mem or other things compete for the same resource.
  • The table has 15 GB. What about indices?
  • Surely, this will not be the only table in use?
  • Heroku may be doing Heroku-things to cache memory that I am not aware of.
4
  • Thank you! I'm an application developer trying to learn more about postgres. I'm thinking of periodically querying recent data like past 90 days. Going to investigate and see the date range of projects that users access.
    – Todd
    Nov 17 '21 at 23:00
  • I added the memory stats to the question body
    – Todd
    Nov 17 '21 at 23:02
  • The query is a SELECT that includes a gin search query. We're seeing if we can add a date range to see if it can also use a date or customer_id index. It's paginated so only returns 20 results.
    – Todd
    Nov 17 '21 at 23:06
  • 2
    @Todd: The "2 seconds" for warm cache don't exactly call for speed-ticket, either. You might be able to pre-warm only relevant parts of table & index if you narrow down the query. Plus, optimizing the query (in combination with the corresponding index) might help in addition to the cache issue. Might be worth a separate question ... Nov 17 '21 at 23:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.