How to avoid transaction ID wrap
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
We want to keep about a years worth of data at any specific time.
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.
The numbers I have amount to 466,560,000 transactions per month, lasting a
maximum of about 9 months until XID wrap.
I am thinking about a few work arounds, BEGIN/COMMIT to reduce the number
of transactions, COPY, etc. so I'm not dead in the water, but I would be
interested in any observations yo may have.
On Tue, 6 Jun 2006, Mark Woodward wrote:
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
I would suggest doing all the inserts of one frame in one transaction.
Maybe even multiple frames in one transaction. That should bring down the
number of transactions significantly.
We want to keep about a years worth of data at any specific time.
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.
Why not?
The numbers I have amount to 466,560,000 transactions per month, lasting a
maximum of about 9 months until XID wrap.
If you can get that maximum up above one year (which was how long you want
to keep the data), you won't need to freeze the records to
avoid ID wraparound.
- Heikki
Ühel kenal päeval, T, 2006-06-06 kell 08:42, kirjutas Mark Woodward:
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
We want to keep about a years worth of data at any specific time.
partition by month, then you have better chances of removing old data
without causing overload/data loss;
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.
why ? just run it with very friendly delay settings.
The numbers I have amount to 466,560,000 transactions per month, lasting a
maximum of about 9 months until XID wrap.
actually 4.5 months as you will start having problems at 2G xacts.
I am thinking about a few work arounds, BEGIN/COMMIT to reduce the number
of transactions, COPY, etc. so I'm not dead in the water, but I would be
interested in any observations yo may have.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
Mark Woodward wrote:
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
Maybe if you grouped multiple operations on bigger transactions, the I/O
savings could be enough to buy you the ability to vacuum once in a
while. Or consider buffering somehow -- save the data elsewhere, and
have some sort of daemon to put it into the database. This would allow
to cope with the I/O increase during vacuum.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Clinging to sanity, hannu@skype.net (Hannu Krosing) mumbled into her beard:
�hel kenal p�eval, T, 2006-06-06 kell 08:42, kirjutas Mark Woodward:
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
We want to keep about a years worth of data at any specific time.partition by month, then you have better chances of removing old data
without causing overload/data loss;
It's certainly worth something to be able to TRUNCATE an elderly
partition; that cleans things out very nicely...
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.why ? just run it with very friendly delay settings.
"Friendly delay settings" can have adverse effects; it is likely to
make vacuum run on the order of 3x as long, which means that if you
have a very large table that takes 12h to VACUUM, "vacuum delay" will
increase that to 36h, which means you'll have a transaction open for
36h.
That'll be very evil, to be sure...
The numbers I have amount to 466,560,000 transactions per month, lasting a
maximum of about 9 months until XID wrap.actually 4.5 months as you will start having problems at 2G xacts.
Right.
I am thinking about a few work arounds, BEGIN/COMMIT to reduce the number
of transactions, COPY, etc. so I'm not dead in the water, but I would be
interested in any observations yo may have.
Grouping work together to diminish numbers of transactions is almost
always something of a win...
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/rdbms.html
Roses are red,
Violets are blue,
I'm schizophrenic...
And I am too.
On Tue, 2006-06-06 at 13:53 -0400, Christopher Browne wrote:
Clinging to sanity, hannu@skype.net (Hannu Krosing) mumbled into her beard:
Ühel kenal päeval, T, 2006-06-06 kell 08:42, kirjutas Mark Woodward:
OK, here's my problem, I have a nature study where we have about 10 video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.
We want to keep about a years worth of data at any specific time.partition by month, then you have better chances of removing old data
without causing overload/data loss;It's certainly worth something to be able to TRUNCATE an elderly
partition; that cleans things out very nicely...
With one potential snafu -- it blocks new SELECTs against the parent
table while truncate runs on the child (happens with constraint
exclusion as well).
If your transactions are short then it won't be an issue. If you have
mixed length transactions (many short which the occasional long select)
then it becomes tricky since those short transactions will be blocked.
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.why ? just run it with very friendly delay settings.
"Friendly delay settings" can have adverse effects; it is likely to
make vacuum run on the order of 3x as long, which means that if you
have a very large table that takes 12h to VACUUM, "vacuum delay" will
increase that to 36h, which means you'll have a transaction open for
36h.
Sounds like this is almost strictly inserts and selects though. If there
is limited garbage collection (updates, deletes, rollbacks of inserts)
required then it isn't all that bad.
--
Rod Taylor wrote:
With one potential snafu -- it blocks new SELECTs against the parent
table while truncate runs on the child (happens with constraint
exclusion as well).If your transactions are short then it won't be an issue. If you have
mixed length transactions (many short which the occasional long select)
then it becomes tricky since those short transactions will be blocked.
One idea is to try to acquire the lock before issuing the TRUNCATE
itself. If the LOCK TABLE times out, you know you should wait for a
long-running transaction ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Mark Woodward wrote:
OK, here's my problem, I have a nature study where we have about 10
video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.Maybe if you grouped multiple operations on bigger transactions, the I/O
savings could be enough to buy you the ability to vacuum once in a
while. Or consider buffering somehow -- save the data elsewhere, and
have some sort of daemon to put it into the database. This would allow
to cope with the I/O increase during vacuum.
The problem is ssufficiently large that any minor modification can easily
hide the problem for a predictble amount of time. My hope was that someone
would have a real "long term" work around.
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX). Pgbench with 64bit
transaction ID has to pay about a couple of percent of performance. If
64bit transaction ID is a reasonable fix, I've already posted this
patch. Anyone can apply this to later versions.
Mark Woodward wrote:
Mark Woodward wrote:
OK, here's my problem, I have a nature study where we have about 10
video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.Maybe if you grouped multiple operations on bigger transactions, the I/O
savings could be enough to buy you the ability to vacuum once in a
while. Or consider buffering somehow -- save the data elsewhere, and
have some sort of daemon to put it into the database. This would allow
to cope with the I/O increase during vacuum.The problem is ssufficiently large that any minor modification can easily
hide the problem for a predictble amount of time. My hope was that someone
would have a real "long term" work around.---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Koichi Suzuki
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX). Pgbench with 64bit
transaction ID has to pay about a couple of percent of performance.
If 64bit transaction ID is a reasonable fix, I've already posted this
patch. Anyone can apply this to later versions.
Did you check performance on 32-bit or 64-bit systems and 64-bit binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.
Zdenek
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX). Pgbench with 64bit
transaction ID has to pay about a couple of percent of performance. If
64bit transaction ID is a reasonable fix, I've already posted this
patch. Anyone can apply this to later versions.
Be careful, the pg_multixact stuff assumes that a MultiXactId is the
same size as TransactionId, so you have to change that too. I don't
recall offhand if it was defined in a way that would make it "just work"
automatically. (You'd also have to be careful about it not overrunning
the SLRU files when it's close to the end of 'em.).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Wed, Jun 07, 2006 at 01:48:50PM +0200, Zdenek Kotala wrote:
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX). Pgbench with 64bit
transaction ID has to pay about a couple of percent of performance.
If 64bit transaction ID is a reasonable fix, I've already posted this
patch. Anyone can apply this to later versions.Did you check performance on 32-bit or 64-bit systems and 64-bit binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.
I doubt performance is the issue directly. Increasing the size of the
transaction counter would increase the size of narrow tables by maybe
30%. That's 30% more disk space and 30% more memory usage in some
places. Maybe at some point it'll be worth it, but right now I don't
think those commodoties are cheap enough to use like this for fairly
marginal benefits.
Beside, memory bandwidth hasn't grown anywhere enar as fast as memory
space, so it's always a good idea to use as little memory as possible.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX).
Did you check performance on 32-bit or 64-bit systems and 64-bit binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.
The percentage increase in I/O demand is the main reason the patch was
rejected, not so much the arithmetic.
regards, tom lane
On Wed, Jun 07, 2006 at 11:47:45AM -0400, Tom Lane wrote:
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX).Did you check performance on 32-bit or 64-bit systems and 64-bit binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.The percentage increase in I/O demand is the main reason the patch was
rejected, not so much the arithmetic.
Before considering 64 bit XIDs, it'd be very helpful to know why Mark
can't vacuum frequently enough to handle rollover...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, T, 2006-06-06 kell 13:53, kirjutas Christopher
Browne:
We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.why ? just run it with very friendly delay settings.
"Friendly delay settings" can have adverse effects; it is likely to
make vacuum run on the order of 3x as long, which means that if you
have a very large table that takes 12h to VACUUM, "vacuum delay" will
increase that to 36h, which means you'll have a transaction open for
36h.That'll be very evil, to be sure...
Not always. I know that it is evil in slony1 context, but often it *is*
possible to design your system in a way where a superlong transaction is
almost unnoticable.
Long transactions are evil in case they cause some fast-changing table
to grow its storage size several orders of magnitude, but if that is not
the case then they just run there in backgroun with no ill effects,
especially do-nothing transactions like vacuum.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
On Thu, Jun 08, 2006 at 01:30:42AM +0300, Hannu Krosing wrote:
??hel kenal p??eval, T, 2006-06-06 kell 13:53, kirjutas Christopher
Browne:We have triggers that fire is something interesting is found on insert.
We want this thing to run for a log time.
From the numbers, you can see the PostgreSQL database is VERY loaded.
Running VACUUM may not always be possible without losing data.why ? just run it with very friendly delay settings.
"Friendly delay settings" can have adverse effects; it is likely to
make vacuum run on the order of 3x as long, which means that if you
have a very large table that takes 12h to VACUUM, "vacuum delay" will
increase that to 36h, which means you'll have a transaction open for
36h.That'll be very evil, to be sure...
Not always. I know that it is evil in slony1 context, but often it *is*
possible to design your system in a way where a superlong transaction is
almost unnoticable.Long transactions are evil in case they cause some fast-changing table
to grow its storage size several orders of magnitude, but if that is not
the case then they just run there in backgroun with no ill effects,
especially do-nothing transactions like vacuum.
Plus, if the only issue here is in fact the long-running transaction for
vacuum, there's other ways to address that which would be a lot less
intrusive than doing something like going to 64 bit XIDs. IIRC, in 8.2
vacuum will start a new transaction every time it fills up
maintenance_work_mem, so just setting that low could solve the problem
(at the expense of a heck of a lot of extra IO).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, Jun 07, 2006 at 07:07:55PM -0400, Mark Woodward wrote:
I guess what I am saying is that PostgreSQL isn't "smooth," between
checkpoints and vacuum, it is near impossible to make a product that
performs consistently under high load.
Have you tuned the bgwriter and all the vacuum_cost stuff? I've get to
find a case where I couldn't smooth out the IO load so that it wasn't an
issue.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Import Notes
Reply to msg id not found: 18774.24.91.171.78.1149721675.squirrel@mail.mohawksoft.com
On Wed, Jun 07, 2006 at 11:47:45AM -0400, Tom Lane wrote:
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX).Did you check performance on 32-bit or 64-bit systems and 64-bit
binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.The percentage increase in I/O demand is the main reason the patch was
rejected, not so much the arithmetic.Before considering 64 bit XIDs, it'd be very helpful to know why Mark
can't vacuum frequently enough to handle rollover...
The system is under heavy load, and while there are tricks that can be
done, vacuum is a process which is extra load the system when it is
running. It is a sliding scale, as always, you may get the system to the
point where it can vacuum AND perform as needed, but the database is
growing constantly. Eventually you will get to the point where you can't
run vacuum *and* keep up with the data stream.
I guess what I am saying is that PostgreSQL isn't "smooth," between
checkpoints and vacuum, it is near impossible to make a product that
performs consistently under high load.
Now don't flame me, I really do love PostgreSQL, it is just that I bump up
against these issues from time to time and it would be nice if there were
some way to work around them.
Tom Lane wrote:
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
Koichi Suzuki wrote:
I've once proposed a patch for 64bit transaction ID, but this causes
some overhead to each tuple (XMIN and XMAX).Did you check performance on 32-bit or 64-bit systems and 64-bit binary
version of PGSQL? I think that today is not problem to have 64-bit
architecture and 64-bit ID should increase scalability of Postgres.
I checked the performance on 64-bit system and 64bit binary.
The percentage increase in I/O demand is the main reason the patch was
rejected, not so much the arithmetic.
That's right. I've also ovserved I/O demand increase. I remember we
have to pay three to five percent performance decrease in pgbench. So I
don't think we should apply this patch without further justification.
I'm looking for other reasons for larger transaction ID.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
--
Koichi Suzuki
Ühel kenal päeval, K, 2006-06-07 kell 17:45, kirjutas Jim C. Nasby:
Plus, if the only issue here is in fact the long-running transaction for
vacuum, there's other ways to address that which would be a lot less
intrusive than doing something like going to 64 bit XIDs. IIRC, in 8.2
vacuum will start a new transaction every time it fills up
maintenance_work_mem, so just setting that low could solve the problem
(at the expense of a heck of a lot of extra IO).
If the aim is to *only* avoid transaction wraparound, then maybe we
could introduce VACUUM FREEZE ONLY; which never removes any old tuples,
but instead just marks them by setting xmin=xmax for them, in addition
to its freezing of live-and-visible-to-all tuples.
This would avoid touching indexes at all and may well be what is desired
for tables with only very little updates/deletes.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
On Wed, Jun 07, 2006 at 07:07:55PM -0400, Mark Woodward wrote:
I guess what I am saying is that PostgreSQL isn't "smooth," between
checkpoints and vacuum, it is near impossible to make a product that
performs consistently under high load.Have you tuned the bgwriter and all the vacuum_cost stuff? I've get to
find a case where I couldn't smooth out the IO load so that it wasn't an
issue.
In several project that I have been involved with, PostgreSQL had most of
the important features to be used, but in one project, checkpoints caused
us to time out under load. In this current project I am researching, I
know that vacuum may be an issue. The load is brutally constant.
Mark Woodward wrote:
On Wed, Jun 07, 2006 at 07:07:55PM -0400, Mark Woodward wrote:
I guess what I am saying is that PostgreSQL isn't "smooth," between
checkpoints and vacuum, it is near impossible to make a product that
performs consistently under high load.Have you tuned the bgwriter and all the vacuum_cost stuff? I've get to
find a case where I couldn't smooth out the IO load so that it wasn't an
issue.In several project that I have been involved with, PostgreSQL had most of
the important features to be used, but in one project, checkpoints caused
us to time out under load. In this current project I am researching, I
know that vacuum may be an issue. The load is brutally constant.
I was recently involved in a project where we had to decrease the
checkpoint_timeout . The problem was, that the database was performing
so many transactions that if we waiting for 5 minutes, checkpoint would
take entirely too long.
We ended up doing checkpoints every two minutes which with the increase
in checkpoint_segments and adjustment of bgwriter settings would level
out the load.
Sincerely,
Joshua D. Drake
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
"Joshua D. Drake" <jd@commandprompt.com> writes:
I was recently involved in a project where we had to decrease the
checkpoint_timeout . The problem was, that the database was performing
so many transactions that if we waiting for 5 minutes, checkpoint would
take entirely too long.
Seems like the correct fix for that is to make the bgwriter more
aggressive. Narrowing the checkpoint spacing is a pretty horrid answer
because of the resulting increase in full-page-image WAL traffic.
regards, tom lane
Hannu Krosing <hannu@skype.net> writes:
If the aim is to *only* avoid transaction wraparound, then maybe we
could introduce VACUUM FREEZE ONLY; which never removes any old tuples,
but instead just marks them by setting xmin=xmax for them, in addition
to its freezing of live-and-visible-to-all tuples.
This would avoid touching indexes at all and may well be what is desired
for tables with only very little updates/deletes.
Seems like useless complexity. If there are so few dead tuples that you
can afford to not reclaim them, then there are so few that reclaiming
them isn't really going to cost much either ...
regards, tom lane
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
I was recently involved in a project where we had to decrease the
checkpoint_timeout . The problem was, that the database was performing
so many transactions that if we waiting for 5 minutes, checkpoint would
take entirely too long.Seems like the correct fix for that is to make the bgwriter more
aggressive. Narrowing the checkpoint spacing is a pretty horrid answer
because of the resulting increase in full-page-image WAL traffic.
Well we did that as well. Here are the basic symptons:
During normal processing which contained about 250 connections
everything was fine. A checkpoint would start and connections would
start piling up, sometimes breaking 1000.
We narrowed that down to users having to wait longer for query execution
so instead of just reusing connections new connections had to be
initiated because the existing connections were busy.
We tried many different parameters, and bgwriter did significantly help
but the only "solution" was to make checkpoints happen at a much more
aggressive time frame.
Modify bgwriters settings and the checkpoint actually increased our
velocity by about 70% by the time we were done. Bgwriter was definitely
the largest chunk of that although other parameters combined outweighed
it (effective_cache, shared_buffers etc...).
Sincerely,
Joshua D. Drake
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Thu, Jun 08, 2006 at 09:13:33AM -0700, Joshua D. Drake wrote:
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
I was recently involved in a project where we had to decrease the
checkpoint_timeout . The problem was, that the database was performing
so many transactions that if we waiting for 5 minutes, checkpoint would
take entirely too long.Seems like the correct fix for that is to make the bgwriter more
aggressive. Narrowing the checkpoint spacing is a pretty horrid answer
because of the resulting increase in full-page-image WAL traffic.Well we did that as well. Here are the basic symptons:
During normal processing which contained about 250 connections
everything was fine. A checkpoint would start and connections would
start piling up, sometimes breaking 1000.We narrowed that down to users having to wait longer for query execution
so instead of just reusing connections new connections had to be
initiated because the existing connections were busy.We tried many different parameters, and bgwriter did significantly help
but the only "solution" was to make checkpoints happen at a much more
aggressive time frame.Modify bgwriters settings and the checkpoint actually increased our
velocity by about 70% by the time we were done. Bgwriter was definitely
the largest chunk of that although other parameters combined outweighed
it (effective_cache, shared_buffers etc...).
Did you try increasing the checkpoint interval, in the hopes that it
would allow the bgwritter enough extra time to get everything pushed
out?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, N, 2006-06-08 kell 12:09, kirjutas Tom Lane:
Hannu Krosing <hannu@skype.net> writes:
If the aim is to *only* avoid transaction wraparound, then maybe we
could introduce VACUUM FREEZE ONLY; which never removes any old tuples,
but instead just marks them by setting xmin=xmax for them, in addition
to its freezing of live-and-visible-to-all tuples.This would avoid touching indexes at all and may well be what is desired
for tables with only very little updates/deletes.Seems like useless complexity. If there are so few dead tuples that you
can afford to not reclaim them, then there are so few that reclaiming
them isn't really going to cost much either ...
It will cost 1 full scan per index, which can be quite a lot of disk
read traffic, if indexes are not used, say when most access is local to
some hotspot.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
On Tuesday 2006-06-06 20:11, Mark Woodward wrote:
Mark Woodward wrote:
OK, here's my problem, I have a nature study where we have about 10
video
cameras taking 15 frames per second.
For each frame we make a few transactions on a PostgreSQL database.Maybe if you grouped multiple operations on bigger transactions, the I/O
savings could be enough to buy you the ability to vacuum once in a
while. Or consider buffering somehow -- save the data elsewhere, and
have some sort of daemon to put it into the database. This would allow
to cope with the I/O increase during vacuum.The problem is ssufficiently large that any minor modification can easily
hide the problem for a predictble amount of time. My hope was that someone
would have a real "long term" work around.
I'm not certain that I understand the original problem correctly so I am going
to restate it.
VACCUM needs to be run for two reasons.
1) To recover the transaction counter.
2) To recover records marked for deletion.
VACCUM needs to be run over the entire database. If the data in the database
is N, then VACCUM is O(N). Roughly, VACCUM scales linearly with the size of
the database.
In the digital video problem:
Data is stored indefinitely online. (It is not archived.)
(Virtually no records need to be recovered from deletion.)
Data comes in at a constant rate, frames.
The database therefore grows at frames/time (D).
It follows that no matter how much tuning is done, given constant hardware,
VACCUM grows to consume so many resources that it is no longer possible to
process frames/time[m] before frames/time[m+1] arrives.
Ideally, the transaction management system would be proportional to the
marginal change in size of the database rather than the gross size of the
database. That is VACCUM being O(N) should be replaced (or there should be
an optional alternative) that scales with D, O^k(D) where any k > 1 involves
a tradeoff with VACCUM.
On Fri, Jun 09, 2006 at 06:20:21PM -0700, Trent Shipley wrote:
Ideally, the transaction management system would be proportional to the
marginal change in size of the database rather than the gross size of the
database. That is VACCUM being O(N) should be replaced (or there should be
an optional alternative) that scales with D, O^k(D) where any k > 1 involves
a tradeoff with VACCUM.
That's something that's been discussed quite a bit; search the archives
for 'dead space map'. Granted, that wasn't targeted so much at the need
to VACUUM FREEZE, but there's no reason it wouldn't be possible to make
it handle that as well. In the mean time, if you partition the table on
date, you won't need to be vacuuming the entire database to handle XID
wrap.
BTW, you're also ignoring the fact that technology keeps improving the
hardware that's out there. It's possible that hardware advances alone
would keep pace with your vacuuming needs.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, Jun 09, 2006 at 06:20:21PM -0700, Trent Shipley wrote:
VACCUM needs to be run for two reasons.
1) To recover the transaction counter.
2) To recover records marked for deletion.VACCUM needs to be run over the entire database. If the data in the database
is N, then VACCUM is O(N). Roughly, VACCUM scales linearly with the size of
the database.
Well, you only need to vacuum the entire database once every billion
transactions.
Secondly, you can vacuum table by table. If you know a table will never
be modified, you can VACUUM FREZZE it, then it will never need to be
vacuumed again, ever (until you make changes ofcourse).
Ideally, the transaction management system would be proportional to the
marginal change in size of the database rather than the gross size of the
database. That is VACCUM being O(N) should be replaced (or there should be
an optional alternative) that scales with D, O^k(D) where any k > 1 involves
a tradeoff with VACCUM.
That's why people suggest partitions. Then you only vacuum the
partitions that are new and the old ones never need to be touched...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes:
That's why people suggest partitions. Then you only vacuum the
partitions that are new and the old ones never need to be touched...
This will all work a lot better once we track XID wraparound risk on a
per-table rather than per-database basis. I hope that will be done in
time for 8.2.
regards, tom lane
Jim C. Nasby wrote:
On Fri, Jun 09, 2006 at 06:20:21PM -0700, Trent Shipley wrote:
Ideally, the transaction management system would be proportional to the
marginal change in size of the database rather than the gross size of the
database. That is VACCUM being O(N) should be replaced (or there should be
an optional alternative) that scales with D, O^k(D) where any k > 1 involves
a tradeoff with VACCUM.That's something that's been discussed quite a bit; search the archives
for 'dead space map'. Granted, that wasn't targeted so much at the need
to VACUUM FREEZE, but there's no reason it wouldn't be possible to make
it handle that as well. In the mean time, if you partition the table on
date, you won't need to be vacuuming the entire database to handle XID
wrap.
FWIW my patch for vacuum fixes some of these issues. First because you
can freeze a table and will never need to vacuum it again; database-wide
vacuums will not be necessary. And secondly, because as soon as a table
is frozen (either because you VACUUM FREEZE'd it, or because regular
vacuuming froze all tuples on it completely), then you don't need to
vacuum it again and indeed (non-full) VACUUM turns into a no-op.
Mix this with partitioned tables.
Tom Lane wrote:
Martijn van Oosterhout <kleptog@svana.org> writes:
That's why people suggest partitions. Then you only vacuum the
partitions that are new and the old ones never need to be touched...This will all work a lot better once we track XID wraparound risk on a
per-table rather than per-database basis. I hope that will be done in
time for 8.2.
FWIW I posted the patch for non-transactional pg_class stuff in -patches
awhile back, so it's pending review ;-) I'll repost it (today I expect,
or tomorrow at the latest) with some minor corrections, along with the
corresponding relminxid patch. I indend to commit both during next week
(or this week, for those whose weeks start on sundays), barring
objections.