Re: pb with big volumes - Mailing list pgsql-general

Hi,

I have a 15 TB db on postgres 14 (soon 15).

shared buffers is 32 GB.

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. 

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...

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com