On 8/10/23 16:36, Marc Millas wrote:
Hi,
I have a 15 TB db on postgres 14 (soon 15).
shared buffers is 32 GB.
Does the system have 128GB AM?
It's a db with max 15 users and often less, and currently 1 or 2.
the biggest table have 133 partitions of 150M to 200M+ rows each.
lots of request access explicitly one of those.
When I, alone, run a query "reading" 15M buffers, it takes 20 minutes
(+-5minutes). inside the query there are 2 group by on a 200M rows
partition, with all the rows in each group by.
When a colleague run the same kind of request (not the same request, but
something reading roughly the same volume ) , on a different set of data,
his request is completed in less than half an hour.
If we run our requests simultaneously... my request take hours. around 3
hours.
I am making a supposition that its some kind of "pumping" effect in the
cache.
What is work_mem set to?
When were the tables last vacuumed and analyzed?
Good index support? (But that might not matter if every row in the table is
in the GROUP BY.)
Clustering the tables, and using BRIN indices might help.
I cannot have access to the underlying OS. I can, for sure, do some copy
xx from program 'some command', but its a container with very limited
possibilities, not even 'ps'.
So I would like to monitor from inside the db (so without iostat and the
same) the volumes of read that postgres do to the OS.
I did activate track_io_timing, but the volumes I get in the explain
analyze buffer are roughly the same alone or not alone. (the 15M buffers
told )
to my understanding, the volumes that are shown in pg_stat_database are
the useful ones ie. even if the db as to read it from disk more than once.
true ? or false ?
So.. either my supposition is not correct, and I will read with a lot of
interest other ideas
either its correct and I would like to know how to monitor this (in the
current context, installing a dedicated extension is not impossible, but
is a very boring process)
Thanks for your help :-)
regards,
PS: I know that providing the complete data model and the exact requests
can be considered mandatory, but when I change the request I get the very
same behaviour...
--
Born in Arizona, moved to Babylonia.
