Thread: [GENERAL] disk writes within a transaction
Can someone enlighten me to how postgres handles disk writing? I've read some generic remarks about buffers, but
that'sabout it.
We have a chunk of code that calls Postgres in a less-than-optimal way within a transaction block. I'm wondering where
toprioritize fixing it, as the traffic on the wire isn't an issue.
Basically the code looks like this:
begin;
update foo set foo.a='1' where foo.bar = 1;
...
update foo set foo.b='2' where foo.bar = 1;
...
update foo set foo.c='3' where foo.bar = 1;
commit;
If the updates are likely to be a memory based operation, consolidating them can wait. If they are likely to hit the
disk,I should schedule refactoring this code sooner than later.
On 02/16/2017 11:33 AM, 2xlp - ListSubscriptions wrote: > Can someone enlighten me to how postgres handles disk writing? I've read some generic remarks about buffers, but that'sabout it. > > We have a chunk of code that calls Postgres in a less-than-optimal way within a transaction block. I'm wondering whereto prioritize fixing it, as the traffic on the wire isn't an issue. > > Basically the code looks like this: > > begin; > update foo set foo.a='1' where foo.bar = 1; > ... > update foo set foo.b='2' where foo.bar = 1; > ... > update foo set foo.c='3' where foo.bar = 1; > commit; > > If the updates are likely to be a memory based operation, consolidating them can wait. If they are likely to hit the disk,I should schedule refactoring this code sooner than later. I would suggest taking a look at: https://www.postgresql.org/docs/9.6/static/wal-configuration.html -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 16, 2017 at 11:33 AM, 2xlp - ListSubscriptions <postgres@2xlp.com> wrote:
Can someone enlighten me to how postgres handles disk writing? I've read some generic remarks about buffers, but that's about it.
We have a chunk of code that calls Postgres in a less-than-optimal way within a transaction block. I'm wondering where to prioritize fixing it, as the traffic on the wire isn't an issue.
Basically the code looks like this:
begin;
update foo set foo.a='1' where foo.bar = 1;
...
update foo set foo.b='2' where foo.bar = 1;
...
update foo set foo.c='3' where foo.bar = 1;
commit;
If the updates are likely to be a memory based operation, consolidating them can wait. If they are likely to hit the disk, I should schedule refactoring this code sooner than later.
You are going to generate more volume of WAL data, which has to reach disk eventually. Although it is likely they will all be consolidated into about the same number of physical writes and syncs.
You are also likely to inhibit the Heap-only-tuple mechanism, because you will end up with 4 copies of the row which all have to fit in the same block. If they don't, it has to migrate some of them to a different block plus do index maintenance, so you will generate more dirty blocks that way. How many more depends on how many indexes you have, and whether the columns being updated are themselves included in indexes.
There is also a CPU issue when the same tuple is updated repeatedly in a single transaction. Each update has to wade through all the previous row versions, so it is an N^2 operation in the number of updates.
It will probably be easier to refactor the code than to quantify just how much damage it does.
cheers,
Jeff
On Feb 17, 2017, at 4:05 PM, Jeff Janes wrote: > It will probably be easier to refactor the code than to quantify just how much damage it does. Thanks for all the info. It looks like this is something worth prioritizing because of the effects on indexes. We had discussed a fix and pointed it; rewriting the code that causes this is pretty massive, and will require blocking outa resource FT for 2 weeks on rewrites and testing. We don't really have time to spare any of those devs, so time to makeproduct tradeoffs ;(