Re: pb with big volumes

Ron <ronljohnsonjr@xxxxxxxxx> · Thu, 10 Aug 2023 20:53:54 -0500



    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.millas@xxxxxxxxxx>
          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.