Re: How long does iteration over 4-5 million rows usually take?
> On Apr 1, 2022, at 10:18 PM, Ron wrote: > > On 4/1/22 20:34, Shaozhong SHI wrote: >> >> I have a script running to iterate over 4-5 million rows. It keeps showing >> up in red in PgAdmin. It remains active. >> >> How long does iteration over 4-5 million rows usually take? 4-5 million times as long as it takes to do one iteration ( if you’re doing it correctly)
Aw: Re: How long does iteration over 4-5 million rows usually take?
> > On Apr 1, 2022, at 10:18 PM, Ron wrote: > > > > On 4/1/22 20:34, Shaozhong SHI wrote: > >> > >> I have a script running to iterate over 4-5 million rows. It keeps > >> showing up in red in PgAdmin. It remains active. > >> > >> How long does iteration over 4-5 million rows usually take? > > 4-5 million times as long as it takes to do one iteration ( if you’re doing > it correctly) I may not take quite that long because setup/teardown times might not be needed for each iteration. Best, Karsten
Re: Re: How long does iteration over 4-5 million rows usually take?
Thanks, Karsten, I would like the information to work planning purpose. Regards, David On Sat, 2 Apr 2022 at 14:47, Karsten Hilbert wrote: > > > On Apr 1, 2022, at 10:18 PM, Ron wrote: > > > > > > On 4/1/22 20:34, Shaozhong SHI wrote: > > >> > > >> I have a script running to iterate over 4-5 million rows. It keeps > showing up in red in PgAdmin. It remains active. > > >> > > >> How long does iteration over 4-5 million rows usually take? > > > > 4-5 million times as long as it takes to do one iteration ( if you’re > doing it correctly) > > I may not take quite that long because setup/teardown times might not be > needed for each iteration. > > Best, > Karsten > > >
Re: Re: How long does iteration over 4-5 million rows usually take?
For work planning, create a temporary table, copy from a generated data set, and test it out. It really depends. Normally, you don't iterate in SQL since rows are assumed to be independent. You think about SQL in terms of sets and let application code handle the data transfers. It also really depends on the size of your tables, indexing, joins, and more. Basically, there isn't really an answer but it isnt hard to test. Thanks, Ben On Sat, Apr 2, 2022, 10:16 AM Shaozhong SHI wrote: > Thanks, Karsten, > > I would like the information to work planning purpose. > > Regards, > > David > > On Sat, 2 Apr 2022 at 14:47, Karsten Hilbert > wrote: > >> > > On Apr 1, 2022, at 10:18 PM, Ron wrote: >> > > >> > > On 4/1/22 20:34, Shaozhong SHI wrote: >> > >> >> > >> I have a script running to iterate over 4-5 million rows. It keeps >> showing up in red in PgAdmin. It remains active. >> > >> >> > >> How long does iteration over 4-5 million rows usually take? >> > >> > 4-5 million times as long as it takes to do one iteration ( if you’re >> doing it correctly) >> >> I may not take quite that long because setup/teardown times might not be >> needed for each iteration. >> >> Best, >> Karsten >> >> >>
Re: weird issue with occasional stuck queries
Hey, > That wait event according to documentation is "Waiting to access the > multixact member SLRU cache." SLRU = segmented least recently used > cache I see, thanks! > if you are low on memory, it can slow down the allocation of > buffers. Do you have a query that is a "select for update" running > somewhere? If your disk is low on space `df -h` that might explain > the issue. - There aren't any queries that are running for longer than the selects shown earlier; definitely not "select for update" since I don't ever use that in my code. - Both disk and RAM utilization is relatively low. > Is there an ERROR: multixact something in your postgres log? There isn't, but while checking I saw some other concerning errors including "deadlock detected", "could not map dynamic shared memory segment" and "could not attach to dynamic shared area". (full logs here: https://paste.sr.ht/blob/9ced99b119c3fce1ecfd71e8554946e7845a44dd ) > Another thing to look at is `iostat -x -y` and look at disk util %. > This is an indicator, but not definitive, of how much disk access is > going on. It may be your drives are just saturated although your > IOWait looks ok in your attachment. I didn't specifically look at that, but I did notice *very* high disk utilization in at least one instance of the stuck queries, as I mentioned previously. Why would the disks be getting saturated? The query count isn't noticeably higher than average, and the database is not autovacuuming, so not sure what could cause that. spiral
Re: weird issue with occasional stuck queries
The logs were helpful. You may want to see the statements around the errors, as more detail may be there such as the SQL statement associated with the error. Deadlocks are an indicator that the client code needs to be examined for improvement. See https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/ about deadlocks. They will slow things down and could cause a queue of SQL statements eventually bogging down the system. It definitely looks like locking issues which is why you don't see high CPU. IIRC you might see high system CPU usage, as opposed to userspace CPU, where the kernel is getting overloaded. The `top` command will help to show that. The disks could be saturated by the write ahead log (WAL) handling of all the transactions. More about WAL here: https://www.postgresql.org/docs/10/wal-internals.html You could consider moving that directory somewhere else using a symbolic link (conf. the link) Anyway, these are the things I would look at. Adam On Sat, Apr 2, 2022 at 5:23 AM spiral wrote: > Hey, > > > That wait event according to documentation is "Waiting to access the > > multixact member SLRU cache." SLRU = segmented least recently used > > cache > > I see, thanks! > > > if you are low on memory, it can slow down the allocation of > > buffers. Do you have a query that is a "select for update" running > > somewhere? If your disk is low on space `df -h` that might explain > > the issue. > > - There aren't any queries that are running for longer than the selects > shown earlier; definitely not "select for update" since I don't ever > use that in my code. > - Both disk and RAM utilization is relatively low. > > > Is there an ERROR: multixact something in your postgres log? > > There isn't, but while checking I saw some other concerning errors > including "deadlock detected", "could not map dynamic shared memory > segment" and "could not attach to dynamic shared area". > (full logs here: > https://paste.sr.ht/blob/9ced99b119c3fce1ecfd71e8554946e7845a44dd ) > > > Another thing to look at is `iostat -x -y` and look at disk util %. > > This is an indicator, but not definitive, of how much disk access is > > going on. It may be your drives are just saturated although your > > IOWait looks ok in your attachment. > > I didn't specifically look at that, but I did notice *very* high disk > utilization in at least one instance of the stuck queries, as I > mentioned previously. Why would the disks be getting saturated? The > query count isn't noticeably higher than average, and the database > is not autovacuuming, so not sure what could cause that. > > spiral >
Re: weird issue with occasional stuck queries
I would look at optimizing the query to increase performance. SELECT * has room for improvement. Also check out the book Mastering PostgreSQL 11 (or whatever version). There are some good tips in there like using EXPLAIN to analyze the query plan. Looking at/setting max_parallel_workers_per_gather might be of interest. You can turn off parallelism as well as increase the number of workers. I have no idea if that will help you but I found it useful. Also walk through the server settings to optimize the performance like work_mem, shared_buffers, etc. If you haven't gone through all that yet understanding each one and setting it correctly is important to get the best out of your machine. It takes time but with many options comes great power. -Original Message- From: spiral To: pgsql-gene...@postgresql.org Subject: weird issue with occasional stuck queries Date: Fri, 1 Apr 2022 03:06:46 -0400 Hey, I'm having a weird issue where a few times a day, any query that hits a specific index (specifically a `unique` column index) gets stuck for anywhere between 1 and 15 minutes on a LWLock (mostly MultiXactOffsetSLRU - not sure what that is, I couldn't find anything about it except for a pgsql-hackers list thread that I didn't really understand). Checking netdata history, these stuck queries coincide with massive disk read; we average ~2MiB/s disk read and it got to 40MiB/s earlier today. These queries used to get stuck for ~15 minutes at worst, but I turned down the query timeout. I assume the numbers above would be worse if I let the queries run for as long as they need, but I don't have any logs from before that change and I don't really want to try that again as it would impact production. I asked on the IRC a few days ago and got the suggestion to increase shared_buffers, but that doesn't seem to have helped at all. I also tried deleting and recreating the index, but that seems to have changed nothing as well. Any suggestions are appreciated since I'm really not sure how to debug this further. I'm also attaching a couple screenshots that might be useful. spiral
