public inbox for [email protected]help / color / mirror / Atom feed
RDS IO Read time 5+ messages / 3 participants [nested] [flat]
* RDS IO Read time @ 2025-03-31 13:54 Eden Aharoni <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Eden Aharoni @ 2025-03-31 13:54 UTC (permalink / raw) To: [email protected] <[email protected]> Hi all, Hopefully, someone here could help us understand whats going on with our deployment.. We are running Postgres 17.4 on AWS RDS on an ec2 instance that has: * 32vCPU * 128GB RAM * gp3 with 25K IOPS and 4000MiB/s throughput * the instance supports up to 3125GB/s of throughput. Whenever a query fetches data from the disk (index scan, bitmap scans, etc.) we’re reaching 23 – 30MB/s of IO Read time. We changed our autovacuum settings to be more aggressive (0 scale factor and 10K threshold) so our index only scans will (hopefully) read data from the disk rarely. However, we can’t optimize all the queries to use index-only-scan since our users can dynamically select which columns to see and filtering. Is this expected IO read rate? I can’t help but feel we’re missing something here.. Thanks a lot! 😊 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RDS IO Read time @ 2025-03-31 15:08 Adrian Klaver <[email protected]> parent: Eden Aharoni <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2025-03-31 15:08 UTC (permalink / raw) To: Eden Aharoni <[email protected]>; [email protected] <[email protected]> On 3/31/25 06:54, Eden Aharoni wrote: > Hi all, > > Hopefully, someone here could help us understand whats going on with our > deployment.. > > We are running Postgres 17.4 on AWS RDS on an ec2 instance that has: > > * 32vCPU > * 128GB RAM > * gp3 with 25K IOPS and 4000MiB/s throughput > * the instance supports up to 3125GB/s of throughput. > > Whenever a query fetches data from the disk (index scan, bitmap scans, > etc.) we’re reaching 23 – 30MB/s of IO Read time. > > We changed our autovacuum settings to be more aggressive (0 scale factor > and 10K threshold) so our index only scans will (hopefully) read data > from the disk rarely. However, we can’t optimize all the queries to use > index-only-scan since our users can dynamically select which columns to > see and filtering. > > Is this expected IO read rate? I can’t help but feel we’re missing > something here.. RDS is a black box controlled by AWS, you are going to need to reach out to their tech support. > > Thanks a lot! 😊 > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RDS IO Read time @ 2025-03-31 15:15 Christophe Pettus <[email protected]> parent: Eden Aharoni <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Christophe Pettus @ 2025-03-31 15:15 UTC (permalink / raw) To: Eden Aharoni <[email protected]>; +Cc: [email protected] <[email protected]> > On Mar 31, 2025, at 06:54, Eden Aharoni <[email protected]> wrote: > Is this expected IO read rate? I can’t help but feel we’re missing something here.. Really, no particular I/O rate is "expected": if PostgreSQL needs that much data, it'll use that much I/O to get it. From your description, it's likely that it's a case of the working set for the database just not fitting into the memory you have, so PostgreSQL needs to go out to secondary storage a lot to fetch the data. The best first step is to use Performance Insights to see which queries are using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see where the I/O is being used within the query. Given that you allow users to assemble arbitrary queries, it's likely that PostgreSQL is having to use a wide variety of indexes (or sequential scans), so it can't successfully cache a particular set in memory. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [EXTERNAL] Re: RDS IO Read time @ 2025-03-31 17:32 Eden Aharoni <[email protected]> parent: Christophe Pettus <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Eden Aharoni @ 2025-03-31 17:32 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: [email protected] <[email protected]> First, thanks for the reply :) So, I do know which part is taking a lot of IO time and it's to be honest any node that reads from the disk.. of course, we're running EXPLAIN on our queries (to be more specific we use auto_explain) but we can't seem to find what could cause an index scan that reads 34 MB to take more than a second (1.2 sec).. we do know that our dataset doesn't fit the memory and we were ok with that but with IO being so slow we just don't know what to do anymore. Any other suggestions other than contact AWS (which we did but it seems this path won't lead anywhere). Thanks again Get Outlook for Android<https://aka.ms/AAb9ysg; ________________________________ From: Christophe Pettus <[email protected]> Sent: Monday, March 31, 2025 6:15:39 PM To: Eden Aharoni <[email protected]> Cc: [email protected] <[email protected]> Subject: [EXTERNAL] Re: RDS IO Read time [You don't often get email from [email protected]. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ] > On Mar 31, 2025, at 06:54, Eden Aharoni <[email protected]> wrote: > Is this expected IO read rate? I can’t help but feel we’re missing something here.. Really, no particular I/O rate is "expected": if PostgreSQL needs that much data, it'll use that much I/O to get it. From your description, it's likely that it's a case of the working set for the database just not fitting into the memory you have, so PostgreSQL needs to go out to secondary storage a lot to fetch the data. The best first step is to use Performance Insights to see which queries are using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see where the I/O is being used within the query. Given that you allow users to assemble arbitrary queries, it's likely that PostgreSQL is having to use a wide variety of indexes (or sequential scans), so it can't successfully cache a particular set in memory. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [EXTERNAL] RDS IO Read time @ 2025-03-31 17:43 Christophe Pettus <[email protected]> parent: Eden Aharoni <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Christophe Pettus @ 2025-03-31 17:43 UTC (permalink / raw) To: Eden Aharoni <[email protected]>; +Cc: [email protected] <[email protected]> > On Mar 31, 2025, at 10:32, Eden Aharoni <[email protected]> wrote: > > First, thanks for the reply :) > So, I do know which part is taking a lot of IO time and it's to be honest any node that reads from the disk.. of course, we're running EXPLAIN on our queries (to be more specific we use auto_explain) but we can't seem to find what could cause an index scan that reads 34 MB to take more than a second (1.2 sec).. we do know that our dataset doesn't fit the memory and we were ok with that but with IO being so slow we just don't know what to do anymore. Any other suggestions other than contact AWS (which we did but it seems this path won't lead anywhere). The performance of EBS is definitely a question for AWS. I can say that, in our experience, EBS mounts for RDS almost never approach the stated maximum throughput, although io2 tends to be closer than gp2 or gp3. ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-31 17:43 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-31 13:54 RDS IO Read time Eden Aharoni <[email protected]> 2025-03-31 15:08 ` Adrian Klaver <[email protected]> 2025-03-31 15:15 ` Christophe Pettus <[email protected]> 2025-03-31 17:32 ` Eden Aharoni <[email protected]> 2025-03-31 17:43 ` Christophe Pettus <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox