We have intermittent slow queries. Is our PostgreSQL struggling with memory?

Question

I am investigating a few slow queries and I need some help reading the data I got.

We have this one particular query which uses an index and runs pretty fast most of the time, however from time to time it runs slow (700ms+), not sure why.

Limit  (cost=8.59..8.60 rows=1 width=619) (actual time=5.653..5.654 rows=1 loops=1)
   ->  Sort  (cost=8.59..8.60 rows=1 width=619) (actual time=5.652..5.652 rows=1 loops=1)
         Sort Key: is_main DESC, id
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using index_pictures_on_imageable_id_and_imageable_type on pictures  (cost=0.56..8.58
rows=1 width=619) (actual time=3.644..5.587 rows=1 loops=1)
               Index Cond: ((imageable_id = 12345) AND ((imageable_type)::text = 'Product'::text))
               Filter: (tag = 30)
               Rows Removed by Filter: 2
 Planning Time: 1.699 ms
 Execution Time: 5.764 ms

If I understand that correctly, I would say that almost the entire cost of the query is on index scan, right? which sounds good to me, so why does the same query run pretty slow sometimes?

I started to think that maybe our instance is not being able to keep the entire index in memory, so it is using disk from time to time. That would explain the slow queries. However, that is way over my head. Does that make sense?

That table has around 15 million rows and 5156 MB in size. Index is 1752 MB. BTW, it is a btree index.

Our PostgreSQL is on a "Highly available" Google Cloud SQL instance. It has 2 vCPUs and 7.5 GB of RAM. Our entire database is around 35 GB in size.

CPU consumption almost never goes beyond 40%. It usually settles around 20-30%.

Checking instance memory graph, I noticed that consumption grows until ~4 GB, then it drops down ~700 MB and it starts growing again. That is a repetitive pattern.

In theory, the instance has 7.5 GB of RAM, but I don't know if all of it is supposed to be available for PostgreSQL. Anyway, ~3.5 GB just for OS sounds pretty high, right?

Memory graph

I read that these configs are important, so throwing them here (Cloud SQL defaults):

shared_buffers                             | 318976
temp_buffers                               | 1024
work_mem                                   | 4096

Considering that we have a bunch of other tables and indexes, is it reasonable to assume that if one index alone is 1.7 GB, 7.5 GB for the entire instance is too low?

Is there any way I can assert whether we have a memory issue or not?

I appreciate your help.

Stortz

Not sure if it could help, but you can check your indexes size with a query from this link: wiki.postgresql.org/wiki/Index_Maintenance – Rifkin

@Rifkin Thank you, that helps. Table size is 5156 MB, index size is 1752 MB – Stortz

Unless you catch a slow execution with EXPLAIN (ANALYZE, BUFFERS), all you can do is guess. Perhaps an ACCESS EXCLUSIVE lock? Perhaps I/O or CPU overload? Perhaps killed index tuples (do you have big deletes)? – Coffeecolored

I believe we don't have big delete, especially on that table. It is basically only inserts. CPU looks good too. I will try to catch one of those slow queries live. Thank you. – Stortz

The auto explain module can help you catch those slow execution plans – Unfurl

Answer 1

Three things that can help you:

1. This function do a "prewarm" on table permanently on your memory. This reduces drastically your disk access, helping a lot on performance. The limitation for prewarm is resources. So, not all tables can be put on memory. If the table is small or not constantly accessed, it's not recommended. Every time that your database is stopped, on the next up of database, you need to run pg_prewarm() again

   https://www.postgresql.org/docs/current/pgprewarm.html

2. Create a CLUSTER on your index. You can create one cluster per table. Clustering your index is a great way to get a good access of the data. The way that data is stored is related with cluster, so, to access a determined position on previously ordered data is very faster.

   CLUSTER [VERBOSE] table_name [ USING index_name ]
   

   Reference: https://www.postgresql.org/docs/current/sql-cluster.html

3. Run periodically VACUUM ANALYZE on table. Postgresql collect statistics about your queries and classifies the information in vacuum with analyze option focused on optimize your queries.

Commorant

Thank you for the suggestions. I didn't know about prewarn and CLUSTER. I'll give them a try. – Stortz

cluster does not "create" a cluster - it merely physically sorts the rows on disk and is something that has to be done on a regular basis. – Unfurl

Non of the above helped with occasional lags in postgres in AWS RDS. – Kinson

Answer 2

I think is more a memory problem as you say. Checking your graph I can say that most of the time your database is using the 4GB of memory assigned and when you run your query postgres has to use the disk.

I suppose your query runs faster is when is under the memory limit. Another thing to consider is that maybe, time ago, your database was not big as now and with the dafult memory assign (4 GB) was ok.

You can modify your memory assigned to postgres configuring the flags, in particular the work_mem flag. I suggest to assign 2GB of extra memory and check the results. If you see your database uses again the 100% of the memory, consider increasing the whole memory and the memory assigned to the database.

Risner

Yeah, that graph really intrigues me. Although, that instance has actually 7.5GB of RAM assigned to it. I guess I will have to add more memory and see what happens. I increased work_mem from 4MB to 8MB and I'll collect the results. – Stortz

How your query is running after memory increase? – Risner