No if there is a pumping effect ie. Loading data in the cache flushed by the other request and again and again. I have had this on an Oracle db years ago. Iostat 100% for days. Managed by partitionning and locking some data in the cache
Le ven. 11 août 2023 à 03:54, Ron <ronljohnso...@gmail.com> a écrit : > Wouldn't IO contention make for additive timings instead of exponential? > > On 8/10/23 20:41, Adam Scott wrote: > > I think your concern is that 20 min + 30 min does not equal 3 hours. > > It might be natural to think the contention would, at max, be 50 min x 2 > (1 hr 40 min). > > So what's going on? > > It seems disk I/O is a primary suspect since you hint for an iostat > replacement inside of Postgres. > > If it is due to disk I/O the resolution will be to add RAID 0 SSDs at best. > > Consider looking at io stats on the container's persistent volumes. > > What is the pipe connecting the database server to the disks? If it's > NAS, well that would explain it. > > HTH, > Adam > > > > On Thu, Aug 10, 2023 at 2:37 PM Marc Millas <marc.mil...@mokadb.com> > wrote: > >> 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 >> >> > -- > Born in Arizona, moved to Babylonia. >
