pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMVCC performance issue
29+ messages / 15 participants
[nested] [flat]
* MVCC performance issue
@ 2010-11-11 18:25 Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
2010-11-14 08:46 ` Re: MVCC performance issue Marti Raudsepp <marti@juffo.org>
2010-11-30 00:21 ` Re: MVCC performance issue Robert Haas <robertmhaas@gmail.com>
0 siblings, 3 replies; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-11 18:25 UTC (permalink / raw)
To: pgsql-performance
This is my first post in this mailing list and I would like to raise an
issue that in my opinion is causing performance issues of PostgreSQL
especially in a transaction processing environment. In my company we are
using PostgreSQL for the last 8 year for our in-house developed billing
system (telecom). The last few months we started considering moving to
another RDBMS just because of this issue.
After all these years, I believe that the biggest improvement that could
be done and will boost overall performance especially for enterprise
application will be to improve Multiversion Concurrency Control (MVCC)
mechanism. In theory this seems to be improving performance for SELECT
queries but on tables with very intensive and frequent updates, even
that is not fully true because of the fragmentation of data caused by
MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
as a buffer) took more than 40min to return a result! VACUUM is not a
solution in my opinion even though after the introduction of autovacuum
daemon situation got much better.
PROBLEM DECRIPTION
------------------
By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
new copy of the row in a new location. Any SELECT queries within the
same session are accessing the new version of the raw and all other
queries from other users are still accessing the old version. When
transaction is COMMIT PostgreSQL makes the a new version of the row as
the "active" row and expires the old row that remains "dead" and then is
up to VACUUM procedure to recover the "dead" rows space and make it
available to the database engine. In case that transaction is ROLLBACK
then space reserved for the new version of the row is released. The
result is to have huge fragmentation on table space, unnecessary updates
in all affected indexes, unnecessary costly I/O operations, poor
performance on SELECT that retrieves big record sets (i.e. reports etc)
and slower updates. As an example, consider updating the "live" balance
of a customer for each phone call where the entire customer record has
to be duplicated again and again upon each call just for modifying a
numeric value!
SUGGESTION
--------------
1) When a raw UPDATE is performed, store all "new raw versions" either
in separate temporary table space
or in a reserved space at the end of each table (can be allocated
dynamically) etc
2) Any SELECT queries within the same session will be again accessing
the new version of the row
3) Any SELECT queries from other users will still be accessing the old
version
4) When UPDATE transaction is ROLLBACK just release the space used in
new temporary location
5) When UPDATE transaction is COMMIT then try to LOCK the old version
and overwrite it at the same physical location (NO FRAGMENTATION).
6) Similar mechanism can be applied on INSERTS and DELETES
7) In case that transaction was COMMIT, the temporary location can be
either released or archived/cleaned on a pre-scheduled basis. This will
possibly allow the introduction of a TRANSACTION LOG backup mechanism as
a next step.
8) After that VACUUM will have to deal only with deletions!!!
I understand that my suggestion seems to be too simplified and also that
there are many implementation details and difficulties that I am not
aware.
I strongly believe that the outcome of the discussion regarding this
issue will be helpful.
Best Regards,
Kyriacos Kyriacou
Senior Developer/DBA
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-13 05:53 ` Craig Ringer <craig@postnewspapers.com.au>
2010-11-13 07:05 ` Re: MVCC performance issue Rich <rhdyes@gmail.com>
2010-11-13 18:38 ` Re: MVCC performance issue Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-14 08:30 ` Re: MVCC performance issue Marti Raudsepp <marti@juffo.org>
2 siblings, 3 replies; 29+ messages in thread
From: Craig Ringer @ 2010-11-13 05:53 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On 11/12/2010 02:25 AM, Kyriacos Kyriacou wrote:
> The
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates.
Yep. It's all about trade-offs. For some workloads the in-table MVCC
storage setup works pretty darn poorly, but for most it seems to work
quite well.
There are various other methods of implementing relational storage with
ACID properties. You can exclude all other transactions while making a
change to a table, ensuring that nobody else can see "old" or "new" rows
so there's no need to keep them around. You can use an out-of-line redo
log (a-la Oracle). Many other methods exist, too.
They all have advantages and disadvantages for different workloads. It's
far from trivial to mix multiple schemes within a single database, so
mixing and matching schemes for different parts of your DB isn't
generally practical.
> 1) When a raw UPDATE is performed, store all "new raw versions" either
> in separate temporary table space
> or in a reserved space at the end of each table (can be allocated
> dynamically) etc
OK, so you want a redo log a-la Oracle?
> 2) Any SELECT queries within the same session will be again accessing
> the new version of the row
> 3) Any SELECT queries from other users will still be accessing the old
> version
... and incurring horrible random I/O penalties if the redo log doesn't
fit in RAM. Again, a-la Oracle.
Even read-only transactions have to hit the undo log if there's an
update in progress, because rows they need may have been moved out to
the undo log as they're updated in the main table storage.
[snip description]
> I understand that my suggestion seems to be too simplified and also that
> there are many implementation details and difficulties that I am not
> aware.
It sounds like you're describing Oracle-style MVCC, using redo logs.
http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/
http://en.wikipedia.org/wiki/Multiversion_concurrency_control
Oracle's MVCC approach has its own costs. Like Pg's, those costs
increase with update/delete frequency. Instead of table bloat, Oracle
suffers from redo log growth (or redo log size management issues).
Instead of increased table scan costs from dead rows, Oracle suffers
from random I/O costs as it looks up the out-of-line redo log for old
rows. Instead of long-running writer transactions causing table bloat,
Oracle can have problems with long-running reader transactions aborting
when the redo log runs out of space.
Personally, I don't know enough to know which is "better". I suspect
they're just different, with different trade-offs. If redo logs allow
you to do without write-ahead logging, that'd be interesting - but
then, the WAL is useful for all sorts of replication options, and the
use of linear WALs means that write ordering in the tables doesn't need
to be as strict, which has performance advantages.
--
Craig Ringer
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
@ 2010-11-13 07:05 ` Rich <rhdyes@gmail.com>
2 siblings, 0 replies; 29+ messages in thread
From: Rich @ 2010-11-13 07:05 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
In reading what you are describing, don't you think PG 9 goes a long way to
helping you out?
On Sat, Nov 13, 2010 at 12:53 AM, Craig Ringer
<craig@postnewspapers.com.au>wrote:
> On 11/12/2010 02:25 AM, Kyriacos Kyriacou wrote:
>
> The
>> result is to have huge fragmentation on table space, unnecessary updates
>> in all affected indexes, unnecessary costly I/O operations, poor
>> performance on SELECT that retrieves big record sets (i.e. reports etc)
>> and slower updates.
>>
>
> Yep. It's all about trade-offs. For some workloads the in-table MVCC
> storage setup works pretty darn poorly, but for most it seems to work quite
> well.
>
> There are various other methods of implementing relational storage with
> ACID properties. You can exclude all other transactions while making a
> change to a table, ensuring that nobody else can see "old" or "new" rows so
> there's no need to keep them around. You can use an out-of-line redo log
> (a-la Oracle). Many other methods exist, too.
>
> They all have advantages and disadvantages for different workloads. It's
> far from trivial to mix multiple schemes within a single database, so mixing
> and matching schemes for different parts of your DB isn't generally
> practical.
>
>
> 1) When a raw UPDATE is performed, store all "new raw versions" either
>> in separate temporary table space
>> or in a reserved space at the end of each table (can be allocated
>> dynamically) etc
>>
>
> OK, so you want a redo log a-la Oracle?
>
>
> 2) Any SELECT queries within the same session will be again accessing
>> the new version of the row
>> 3) Any SELECT queries from other users will still be accessing the old
>> version
>>
>
> ... and incurring horrible random I/O penalties if the redo log doesn't fit
> in RAM. Again, a-la Oracle.
>
> Even read-only transactions have to hit the undo log if there's an update
> in progress, because rows they need may have been moved out to the undo log
> as they're updated in the main table storage.
>
> [snip description]
>
>
> I understand that my suggestion seems to be too simplified and also that
>> there are many implementation details and difficulties that I am not
>> aware.
>>
>
> It sounds like you're describing Oracle-style MVCC, using redo logs.
>
>
> http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/
>
> http://en.wikipedia.org/wiki/Multiversion_concurrency_control
>
> Oracle's MVCC approach has its own costs. Like Pg's, those costs increase
> with update/delete frequency. Instead of table bloat, Oracle suffers from
> redo log growth (or redo log size management issues). Instead of increased
> table scan costs from dead rows, Oracle suffers from random I/O costs as it
> looks up the out-of-line redo log for old rows. Instead of long-running
> writer transactions causing table bloat, Oracle can have problems with
> long-running reader transactions aborting when the redo log runs out of
> space.
>
> Personally, I don't know enough to know which is "better". I suspect
> they're just different, with different trade-offs. If redo logs allow you
> to do without write-ahead logging, that'd be interesting - but then, the
> WAL is useful for all sorts of replication options, and the use of linear
> WALs means that write ordering in the tables doesn't need to be as strict,
> which has performance advantages.
>
> --
> Craig Ringer
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
@ 2010-11-13 18:38 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-14 00:10 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
2 siblings, 1 reply; 29+ messages in thread
From: Mladen Gogala @ 2010-11-13 18:38 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
Craig Ringer wrote:
> It sounds like you're describing Oracle-style MVCC, using redo logs.
>
> http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/
>
>
Craig, this is an interesting blog page, making some valid points about
the multiversioning vs. locking. The ATM example, however, is
unrealistic and couldn't have happened the way the author describes.
Oracle has the same write consistency mechanism as Postgres and it
restarts the transaction if the transaction blocks were updated while
the transaction was waiting. In other words, the wife's transaction
would have been restarted before committing, the transaction would get
the balance accurately and there wouldn't be a loss of $250.
Such an example is naive, sheer FUD. If that was the case, no bank in
the whole wide world would be using Oracle, and many of them do, I dare
say many more are using Oracle than Sybase. That means that they're not
losing money if 2 spouses decide to withdraw money from the joint
account simultaneously. Given the number of people in the world, I
imagine that to be a rather common and ordinary situation for the banks.
The example is plain silly. Here is what I have in mind as "write
consistency":
http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED:
" If the first updater rolls back, then its effects are negated and the
second updater can proceed with updating the originally found row. If
the first updater commits, the second updater will ignore the row if the
first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row. The search condition of the
command (the WHERE clause) is re-evaluated to see if the updated version
of the row still matches the search condition."
Essentially the same behavior is described here, for Oracle:
http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
"Obviously, we cannot modify an old version of a block—when we go to
modify a row, we must modify the current version of that block.
Additionally, Oracle cannot just simply skip this row, as that would be
an inconsistent read and unpredictable. What we’ll discover is that in
such cases, Oracle will restart the write modification from scratch."
Postgres re-evaluates the where condition, Oracle restarts the entire
transaction, but neither MVCC mechanism would allow for the silly ATM
example described in the blog. Both databases would have noticed change
in the balance, both databases would have ended with the proper balance
in the account.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
2010-11-13 18:38 ` Re: MVCC performance issue Mladen Gogala <mladen.gogala@vmsinfo.com>
@ 2010-11-14 00:10 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 29+ messages in thread
From: Craig Ringer @ 2010-11-14 00:10 UTC (permalink / raw)
To: mladen.gogala@vmsinfo.com; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
On 11/14/2010 02:38 AM, Mladen Gogala wrote:
> Craig Ringer wrote:
>> It sounds like you're describing Oracle-style MVCC, using redo logs.
>>
>> http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/
>>
> Craig, this is an interesting blog page, making some valid points about
> the multiversioning vs. locking. The ATM example, however, is
> unrealistic and couldn't have happened the way the author describes.
Yep, you're quite right. I was using it for its explanation of some of
the costs of MVCC as Oracle implements it, because it's surprisingly
hard to find explanations/analysis of that with some quick Google
searching. I hadn't read beyond that part.
I'd be really interested in some *good* writeups of the costs/benefits
of the various common mvcc and locking based rdbms implementations.
Thanks for posting a breakdown of the issues with that article, lest
others be mislead. Appreciated.
--
Craig Ringer
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
@ 2010-11-14 08:30 ` Marti Raudsepp <marti@juffo.org>
2010-11-15 00:32 ` Re: MVCC performance issue Mladen Gogala <mladen.gogala@vmsinfo.com>
2 siblings, 1 reply; 29+ messages in thread
From: Marti Raudsepp @ 2010-11-14 08:30 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
On Sat, Nov 13, 2010 at 07:53, Craig Ringer <craig@postnewspapers.com.au> wrote:
> Oracle's MVCC approach has its own costs. Like Pg's, those costs increase
> with update/delete frequency. Instead of table bloat, Oracle suffers from
> redo log growth (or redo log size management issues). Instead of increased
> table scan costs from dead rows, Oracle suffers from random I/O costs as it
> looks up the out-of-line redo log for old rows. Instead of long-running
> writer transactions causing table bloat, Oracle can have problems with
> long-running reader transactions aborting when the redo log runs out of
> space.
Another advantage of Oracle's approach seems that they need much less
tuple-level overhead. IMO the 23-byte tuple overhead is a much bigger
drawback in Postgres than table fragmentation.
Regards,
Marti
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Re: MVCC performance issue Craig Ringer <craig@postnewspapers.com.au>
2010-11-14 08:30 ` Re: MVCC performance issue Marti Raudsepp <marti@juffo.org>
@ 2010-11-15 00:32 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 0 replies; 29+ messages in thread
From: Mladen Gogala @ 2010-11-15 00:32 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
Marti Raudsepp wrote:
>
>
> Another advantage of Oracle's approach seems that they need much less
> tuple-level overhead. IMO the 23-byte tuple overhead is a much bigger
> drawback in Postgres than table fragmentation.
>
> Regards,
> Marti
>
>
Oracle, however, does have a problem with "ORA-1555 Snapshot too old",
precisely because of their implementation of MVCC. In other words, if
your query is running long and Oracle is not able to reconstruct the old
rows from the UNDO segments, you're out of luck and your query will die.
The greatest burden of the Postgres implementation is the fact that
there is no row id, so that the table header and the indexes need to be
updated much more frequently than is the case with Oracle.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-14 08:46 ` Marti Raudsepp <marti@juffo.org>
2 siblings, 0 replies; 29+ messages in thread
From: Marti Raudsepp @ 2010-11-14 08:46 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On Thu, Nov 11, 2010 at 20:25, Kyriacos Kyriacou
<kyriacosk@prime-tel.com> wrote:
> By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
> new copy of the row in a new location.
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates.
Have you tried reducing the table fillfactor and seeing if HOT update
ratio increases?
PostgreSQL 8.3 introduced HOT updates as kind of a middle ground -- if
the update doesn't affect indexed columns and there's enough space in
the same page that is being updated, then the new version will be
written in the same page and indexes don't need to be touched at all.
Regards,
Marti
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-30 00:21 ` Robert Haas <robertmhaas@gmail.com>
2 siblings, 0 replies; 29+ messages in thread
From: Robert Haas @ 2010-11-30 00:21 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On Thu, Nov 11, 2010 at 1:25 PM, Kyriacos Kyriacou
<kyriacosk@prime-tel.com> wrote:
> This is my first post in this mailing list and I would like to raise an
> issue that in my opinion is causing performance issues of PostgreSQL
> especially in a transaction processing environment. In my company we are
> using PostgreSQL for the last 8 year for our in-house developed billing
> system (telecom). The last few months we started considering moving to
> another RDBMS just because of this issue.
>
> After all these years, I believe that the biggest improvement that could
> be done and will boost overall performance especially for enterprise
> application will be to improve Multiversion Concurrency Control (MVCC)
> mechanism. In theory this seems to be improving performance for SELECT
> queries but on tables with very intensive and frequent updates, even
> that is not fully true because of the fragmentation of data caused by
> MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
> as a buffer) took more than 40min to return a result! VACUUM is not a
> solution in my opinion even though after the introduction of autovacuum
> daemon situation got much better.
There are probably a number of ways that the behavior you're seeing
could be improved without switching databases or rewriting PostgreSQL,
but you haven't provided enough information here for anyone to help
you in a meaningful way - such as the version of PostgreSQL you're
running. One obvious suggestion would be to empty your table using
TRUNCATE rather than DELETE, which will avoid the particular problem
you're describing here.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 29+ messages in thread
* MVCC performance issue
@ 2010-11-12 13:47 Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:52 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 15:53 ` Re: MVCC performance issue Vitalii Tymchyshyn <tivv00@gmail.com>
2010-11-12 16:21 ` Re: MVCC performance issue Andy Colson <andy@squeakycode.net>
2010-11-12 17:39 ` Re: MVCC performance issue Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 5 replies; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-12 13:47 UTC (permalink / raw)
To: pgsql-performance
This is my first post in this mailing list and I would like to raise an
issue that in my opinion is causing performance issues of PostgreSQL
especially in a transaction processing environment. In my company we are
using PostgreSQL for the last 8 year for our in-house developed billing
system (telecom). The last few months we started considering moving to
another RDBMS just because of this issue.
After all these years, I believe that the biggest improvement that could
be done and will boost overall performance especially for enterprise
application will be to improve Multiversion Concurrency Control (MVCC)
mechanism. In theory this seems to be improving performance for SELECT
queries but on tables with very intensive and frequent updates, even
that is not fully true because of the fragmentation of data caused by
MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
as a buffer) took more than 40min to return a result! VACUUM is not a
solution in my opinion even though after the introduction of autovacuum
daemon situation got much better.
PROBLEM DECRIPTION
------------------
By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
new copy of the row in a new location. Any SELECT queries within the
same session are accessing the new version of the raw and all other
queries from other users are still accessing the old version. When
transaction is COMMIT PostgreSQL makes the a new version of the row as
the "active" row and expires the old row that remains "dead" and then is
up to VACUUM procedure to recover the "dead" rows space and make it
available to the database engine. In case that transaction is ROLLBACK
then space reserved for the new version of the row is released. The
result is to have huge fragmentation on table space, unnecessary updates
in all affected indexes, unnecessary costly I/O operations, poor
performance on SELECT that retrieves big record sets (i.e. reports etc)
and slower updates. As an example, consider updating the "live" balance
of a customer for each phone call where the entire customer record has
to be duplicated again and again upon each call just for modifying a
numeric value!
SUGGESTION
--------------
1) When a raw UPDATE is performed, store all "new raw versions" either
in separate temporary table space
or in a reserved space at the end of each table (can be allocated
dynamically) etc
2) Any SELECT queries within the same session will be again accessing
the new version of the row
3) Any SELECT queries from other users will still be accessing the old
version
4) When UPDATE transaction is ROLLBACK just release the space used in
new temporary location
5) When UPDATE transaction is COMMIT then try to LOCK the old version
and overwrite it at the same physical location (NO FRAGMENTATION).
6) Similar mechanism can be applied on INSERTS and DELETES
7) In case that transaction was COMMIT, the temporary location can be
either released or archived/cleaned on a pre-scheduled basis. This will
possibly allow the introduction of a TRANSACTION LOG backup mechanism as
a next step.
8) After that VACUUM will have to deal only with deletions!!!
I understand that my suggestion seems to be too simplified and also that
there are many implementation details and difficulties that I am not
aware.
I strongly believe that the outcome of the discussion regarding this
issue will be helpful.
Best Regards,
Kyriacos Kyriacou
Senior Developer/DBA
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 13:52 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 15:34 ` Re: MVCC performance issue bricklen <bricklen@gmail.com>
4 siblings, 1 reply; 29+ messages in thread
From: Kenneth Marshall @ 2010-11-12 13:52 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On Fri, Nov 12, 2010 at 03:47:30PM +0200, Kyriacos Kyriacou wrote:
> This is my first post in this mailing list and I would like to raise an
> issue that in my opinion is causing performance issues of PostgreSQL
> especially in a transaction processing environment. In my company we are
> using PostgreSQL for the last 8 year for our in-house developed billing
> system (telecom). The last few months we started considering moving to
> another RDBMS just because of this issue.
>
> After all these years, I believe that the biggest improvement that could
> be done and will boost overall performance especially for enterprise
> application will be to improve Multiversion Concurrency Control (MVCC)
> mechanism. In theory this seems to be improving performance for SELECT
> queries but on tables with very intensive and frequent updates, even
> that is not fully true because of the fragmentation of data caused by
> MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
> as a buffer) took more than 40min to return a result! VACUUM is not a
> solution in my opinion even though after the introduction of autovacuum
> daemon situation got much better.
>
> PROBLEM DECRIPTION
> ------------------
> By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
> new copy of the row in a new location. Any SELECT queries within the
> same session are accessing the new version of the raw and all other
> queries from other users are still accessing the old version. When
> transaction is COMMIT PostgreSQL makes the a new version of the row as
> the "active" row and expires the old row that remains "dead" and then is
> up to VACUUM procedure to recover the "dead" rows space and make it
> available to the database engine. In case that transaction is ROLLBACK
> then space reserved for the new version of the row is released. The
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates. As an example, consider updating the "live" balance
> of a customer for each phone call where the entire customer record has
> to be duplicated again and again upon each call just for modifying a
> numeric value!
>
> SUGGESTION
> --------------
> 1) When a raw UPDATE is performed, store all "new raw versions" either
> in separate temporary table space
> or in a reserved space at the end of each table (can be allocated
> dynamically) etc
> 2) Any SELECT queries within the same session will be again accessing
> the new version of the row
> 3) Any SELECT queries from other users will still be accessing the old
> version
> 4) When UPDATE transaction is ROLLBACK just release the space used in
> new temporary location
> 5) When UPDATE transaction is COMMIT then try to LOCK the old version
> and overwrite it at the same physical location (NO FRAGMENTATION).
> 6) Similar mechanism can be applied on INSERTS and DELETES
> 7) In case that transaction was COMMIT, the temporary location can be
> either released or archived/cleaned on a pre-scheduled basis. This will
> possibly allow the introduction of a TRANSACTION LOG backup mechanism as
> a next step.
> 8) After that VACUUM will have to deal only with deletions!!!
>
>
> I understand that my suggestion seems to be too simplified and also that
> there are many implementation details and difficulties that I am not
> aware.
>
> I strongly believe that the outcome of the discussion regarding this
> issue will be helpful.
>
> Best Regards,
>
> Kyriacos Kyriacou
> Senior Developer/DBA
>
I cannot speak to your suggestion, but it sounds like you are not
vacuuming enough and a lot of the bloat/randomization would be helped
by making use of HOT updates in which the updates are all in the same
page and are reclaimed almost immediately.
Regards,
Ken
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:52 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
@ 2010-11-12 15:34 ` bricklen <bricklen@gmail.com>
2010-11-12 15:37 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 29+ messages in thread
From: bricklen @ 2010-11-12 15:34 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-performance
On Fri, Nov 12, 2010 at 5:52 AM, Kenneth Marshall <ktm@rice.edu> wrote:
>
> I cannot speak to your suggestion, but it sounds like you are not
> vacuuming enough and a lot of the bloat/randomization would be helped
> by making use of HOT updates in which the updates are all in the same
> page and are reclaimed almost immediately.
>
> Regards,
> Ken
IIRC, HOT only operates on non-indexed columns, so if you the tables
are heavily indexed you won't get the full benefit of HOT. I could be
wrong though.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:52 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
2010-11-12 15:34 ` Re: MVCC performance issue bricklen <bricklen@gmail.com>
@ 2010-11-12 15:37 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 18:13 ` Re: MVCC performance issue Scott Carey <scott@richrelevance.com>
0 siblings, 1 reply; 29+ messages in thread
From: Kenneth Marshall @ 2010-11-12 15:37 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-performance
On Fri, Nov 12, 2010 at 07:34:36AM -0800, bricklen wrote:
> On Fri, Nov 12, 2010 at 5:52 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> >
> > I cannot speak to your suggestion, but it sounds like you are not
> > vacuuming enough and a lot of the bloat/randomization would be helped
> > by making use of HOT updates in which the updates are all in the same
> > page and are reclaimed almost immediately.
> >
> > Regards,
> > Ken
>
> IIRC, HOT only operates on non-indexed columns, so if you the tables
> are heavily indexed you won't get the full benefit of HOT. I could be
> wrong though.
>
That is true, but if they are truly having as big a bloat problem
as the message indicated, it would be worth designing the schema
to leverage HOT for the very frequent updates.
Cheers,
Ken
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:52 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
2010-11-12 15:34 ` Re: MVCC performance issue bricklen <bricklen@gmail.com>
2010-11-12 15:37 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
@ 2010-11-12 18:13 ` Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 29+ messages in thread
From: Scott Carey @ 2010-11-12 18:13 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: bricklen <bricklen@gmail.com>; pgsql-performance
HOT also usually requires setting FILLFACTOR to something other than the default for your table, so that there is guaranteed room in the page to modify data without allocating a new page.
If you have fillfactor=75, then basically this proposal is already done -- each page has 25% temp space for updates in it. With the caveat that that is only true if the updates are to columns without indexes.
On Nov 12, 2010, at 7:37 AM, Kenneth Marshall wrote:
> On Fri, Nov 12, 2010 at 07:34:36AM -0800, bricklen wrote:
>> On Fri, Nov 12, 2010 at 5:52 AM, Kenneth Marshall <ktm@rice.edu> wrote:
>>>
>>> I cannot speak to your suggestion, but it sounds like you are not
>>> vacuuming enough and a lot of the bloat/randomization would be helped
>>> by making use of HOT updates in which the updates are all in the same
>>> page and are reclaimed almost immediately.
>>>
>>> Regards,
>>> Ken
>>
>> IIRC, HOT only operates on non-indexed columns, so if you the tables
>> are heavily indexed you won't get the full benefit of HOT. I could be
>> wrong though.
>>
>
> That is true, but if they are truly having as big a bloat problem
> as the message indicated, it would be worth designing the schema
> to leverage HOT for the very frequent updates.
>
> Cheers,
> Ken
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 13:54 ` Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
4 siblings, 1 reply; 29+ messages in thread
From: Thom Brown @ 2010-11-12 13:54 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On 12 November 2010 13:47, Kyriacos Kyriacou <kyriacosk@prime-tel.com>wrote:
> This is my first post in this mailing list and I would like to raise an
> issue that in my opinion is causing performance issues of PostgreSQL
> especially in a transaction processing environment. In my company we are
> using PostgreSQL for the last 8 year for our in-house developed billing
> system (telecom). The last few months we started considering moving to
> another RDBMS just because of this issue.
>
> After all these years, I believe that the biggest improvement that could
> be done and will boost overall performance especially for enterprise
> application will be to improve Multiversion Concurrency Control (MVCC)
> mechanism. In theory this seems to be improving performance for SELECT
> queries but on tables with very intensive and frequent updates, even
> that is not fully true because of the fragmentation of data caused by
> MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
> as a buffer) took more than 40min to return a result! VACUUM is not a
> solution in my opinion even though after the introduction of autovacuum
> daemon situation got much better.
>
> PROBLEM DECRIPTION
> ------------------
> By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
> new copy of the row in a new location. Any SELECT queries within the
> same session are accessing the new version of the raw and all other
> queries from other users are still accessing the old version. When
> transaction is COMMIT PostgreSQL makes the a new version of the row as
> the "active" row and expires the old row that remains "dead" and then is
> up to VACUUM procedure to recover the "dead" rows space and make it
> available to the database engine. In case that transaction is ROLLBACK
> then space reserved for the new version of the row is released. The
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates. As an example, consider updating the "live" balance
> of a customer for each phone call where the entire customer record has
> to be duplicated again and again upon each call just for modifying a
> numeric value!
>
> SUGGESTION
> --------------
> 1) When a raw UPDATE is performed, store all "new raw versions" either
> in separate temporary table space
> or in a reserved space at the end of each table (can be allocated
> dynamically) etc
> 2) Any SELECT queries within the same session will be again accessing
> the new version of the row
> 3) Any SELECT queries from other users will still be accessing the old
> version
> 4) When UPDATE transaction is ROLLBACK just release the space used in
> new temporary location
> 5) When UPDATE transaction is COMMIT then try to LOCK the old version
> and overwrite it at the same physical location (NO FRAGMENTATION).
> 6) Similar mechanism can be applied on INSERTS and DELETES
> 7) In case that transaction was COMMIT, the temporary location can be
> either released or archived/cleaned on a pre-scheduled basis. This will
> possibly allow the introduction of a TRANSACTION LOG backup mechanism as
> a next step.
> 8) After that VACUUM will have to deal only with deletions!!!
>
>
> I understand that my suggestion seems to be too simplified and also that
> there are many implementation details and difficulties that I am not
> aware.
>
> I strongly believe that the outcome of the discussion regarding this
> issue will be helpful.
>
>
Which version of PostgreSQL are you basing this on?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
@ 2010-11-12 16:14 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:19 ` Re: MVCC performance issue Ben Chobot <bench@silentmedia.com>
2010-11-12 16:22 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
2010-11-12 16:22 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:28 ` Re: MVCC performance issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-12 16:14 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
database size is over 200Gb so upgrade is not an easy decision!
I have it in my plans so in next few months I will setup new servers and
upgrade to version 9.
>> Which version of PostgreSQL are you basing this on?
>>
>>--
>>Thom Brown
>>Twitter: @darkixion
>>IRC (freenode): dark_ixion
>>Registered Linux user: #516935
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 16:19 ` Ben Chobot <bench@silentmedia.com>
2010-11-12 17:48 ` Re: MVCC performance issue Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 1 reply; 29+ messages in thread
From: Ben Chobot @ 2010-11-12 16:19 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On Nov 12, 2010, at 8:14 AM, Kyriacos Kyriacou wrote:
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and database size is over 200Gb so upgrade is not an easy decision!
This is why we have slony, so you can slowly upgrade your 200Gb while you're live and then only suffer a minute or so of downtime while you switchover. Even if you only install slony for the point of the upgrade and then uninstall it after you're done, that seems well worth it to me rather than running on 8.2.4 for a while.
Note there were some changes between 8.2 and 8.3 in regards to casting that might make you revisit your application.=
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:19 ` Re: MVCC performance issue Ben Chobot <bench@silentmedia.com>
@ 2010-11-12 17:48 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Scott Marlowe @ 2010-11-12 17:48 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
On Fri, Nov 12, 2010 at 9:19 AM, Ben Chobot <bench@silentmedia.com> wrote:
> On Nov 12, 2010, at 8:14 AM, Kyriacos Kyriacou wrote:
>
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
> database size is over 200Gb so upgrade is not an easy decision!
>
> This is why we have slony, so you can slowly upgrade your 200Gb while you're
> live and then only suffer a minute or so of downtime while you switchover.
> Even if you only install slony for the point of the upgrade and then
> uninstall it after you're done, that seems well worth it to me rather than
> running on 8.2.4 for a while.
> Note there were some changes between 8.2 and 8.3 in regards to casting that
> might make you revisit your application.
I work in a slony shop and we used slony to upgrade from 8.2 to 8.3
and it was a breeze. Course we practiced on some test machines first,
but it went really smoothly. Our total downtime, due to necessary
testing before going live again, was less than 20 mintues.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 16:22 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 16:33 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
3 siblings, 1 reply; 29+ messages in thread
From: Kenneth Marshall @ 2010-11-12 16:22 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
Ah, this is a very old version. If you can take advantage of
a version with HOT support, you should be much, much happier.
Cheers,
Ken
On Fri, Nov 12, 2010 at 06:14:00PM +0200, Kyriacos Kyriacou wrote:
>
>
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
> database size is over 200Gb so upgrade is not an easy decision!
>
> I have it in my plans so in next few months I will setup new servers and
> upgrade to version 9.
>
>
> >> Which version of PostgreSQL are you basing this on?
>
> >>
> >>--
> >>Thom Brown
> >>Twitter: @darkixion
> >>IRC (freenode): dark_ixion
> >>Registered Linux user: #516935
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:22 ` Re: MVCC performance issue Kenneth Marshall <ktm@rice.edu>
@ 2010-11-12 16:33 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
0 siblings, 0 replies; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-12 16:33 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
To be honest I just now read about HOT (Heap Overflow Tuple) and it
seems that will help a lot. Thanks for your point.
Kyriacos
-----Original Message-----
From: Kenneth Marshall [mailto:ktm@rice.edu]
Sent: Friday, November 12, 2010 6:22 PM
To: Kyriacos Kyriacou
Cc: Thom Brown; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] MVCC performance issue
Ah, this is a very old version. If you can take advantage of
a version with HOT support, you should be much, much happier.
Cheers,
Ken
On Fri, Nov 12, 2010 at 06:14:00PM +0200, Kyriacos Kyriacou wrote:
>
>
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
> database size is over 200Gb so upgrade is not an easy decision!
>
> I have it in my plans so in next few months I will setup new servers
and
> upgrade to version 9.
>
>
> >> Which version of PostgreSQL are you basing this on?
>
> >>
> >>--
> >>Thom Brown
> >>Twitter: @darkixion
> >>IRC (freenode): dark_ixion
> >>Registered Linux user: #516935
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 16:22 ` Thom Brown <thom@linux.com>
2010-11-12 17:27 ` Re: MVCC performance issue Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 1 reply; 29+ messages in thread
From: Thom Brown @ 2010-11-12 16:22 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On 12 November 2010 16:14, Kyriacos Kyriacou <kyriacosk@prime-tel.com>wrote:
>
>
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
> database size is over 200Gb so upgrade is not an easy decision!
>
> I have it in my plans so in next few months I will setup new servers and
> upgrade to version 9.
>
>
Everything changed, performance-wise, in 8.3, and there have also been
improvements since then too. So rather than completely changing your
database platform, at least take a look at what work has gone into Postgres
since the version you're using.
http://www.postgresql.org/docs/8.3/static/release-8-3.html#AEN87319
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:22 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
@ 2010-11-12 17:27 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Scott Marlowe @ 2010-11-12 17:27 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; pgsql-performance
On Fri, Nov 12, 2010 at 9:22 AM, Thom Brown <thom@linux.com> wrote:
> On 12 November 2010 16:14, Kyriacos Kyriacou <kyriacosk@prime-tel.com>
> wrote:
>>
>>
>>
>> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
>> database size is over 200Gb so upgrade is not an easy decision!
>>
>> I have it in my plans so in next few months I will setup new servers and
>> upgrade to version 9.
>
> Everything changed, performance-wise, in 8.3, and there have also been
> improvements since then too. So rather than completely changing your
> database platform, at least take a look at what work has gone into Postgres
> since the version you're using.
Agreed. 8.3 was a colossal step forward for pg performance. 8.4 was
a huge step ahead in maintenance with on disk fsm. If I was upgrading
from 8.2 today I would go straight to 8.4 and skip 8.3 since it's a
much bigger pain in the butt to configure for fsm stuff.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:54 ` Re: MVCC performance issue Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2010-11-12 16:28 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
"Kyriacos Kyriacou" <kyriacosk@prime-tel.com> writes:
> We are still using PostgreSQL 8.2.4.
In that case you don't have HOT updates, so it seems to me to be a
little premature to be proposing a 100% rewrite of the system to fix
your problems.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 15:53 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2010-11-12 16:18 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
4 siblings, 1 reply; 29+ messages in thread
From: Vitalii Tymchyshyn @ 2010-11-12 15:53 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
12.11.10 15:47, Kyriacos Kyriacou написав(ла):
> PROBLEM DECRIPTION
> ------------------
> As an example, consider updating the "live" balance
> of a customer for each phone call where the entire customer record has
> to be duplicated again and again upon each call just for modifying a
> numeric value!
>
Have you considered splitting customer record into two tables with
mostly read-only data and with data that is updated often? Such 1-1
relationship can make a huge difference to performance in your case. You
can even try to simulate old schema by using an updateable view.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 15:53 ` Re: MVCC performance issue Vitalii Tymchyshyn <tivv00@gmail.com>
@ 2010-11-12 16:18 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
0 siblings, 0 replies; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-12 16:18 UTC (permalink / raw)
To: Vitalii Tymchyshyn <tivv00@gmail.com>; +Cc: pgsql-performance
This was done already as a workaround after identifying this problem.
I just gave it as an example.
-----Original Message-----
From: Vitalii Tymchyshyn [mailto:tivv00@gmail.com]
Sent: Friday, November 12, 2010 5:54 PM
To: Kyriacos Kyriacou
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] MVCC performance issue
12.11.10 15:47, Kyriacos Kyriacou написав(ла):
> PROBLEM DECRIPTION
> ------------------
> As an example, consider updating the "live" balance
> of a customer for each phone call where the entire customer record has
> to be duplicated again and again upon each call just for modifying a
> numeric value!
>
Have you considered splitting customer record into two tables with
mostly read-only data and with data that is updated often? Such 1-1
relationship can make a huge difference to performance in your case. You
can even try to simulate old schema by using an updateable view.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 16:21 ` Andy Colson <andy@squeakycode.net>
2010-11-12 17:13 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
4 siblings, 1 reply; 29+ messages in thread
From: Andy Colson @ 2010-11-12 16:21 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
On 11/12/2010 7:47 AM, Kyriacos Kyriacou wrote:
>
> SUGGESTION
> --------------
> 1) When a raw UPDATE is performed, store all "new raw versions" either
> in separate temporary table space
> or in a reserved space at the end of each table (can be allocated
> dynamically) etc
Your use of "raw" is confusing. I'll just ignore the word. New row
versions are already stored in a dynamically allocated spot, right along
with the other versions of the table. You are assuming that getting to
the "correct" version of the row is very slow? That's only going to be
the case if you have lots and lots of versions. And your solution will
not actually help if there are lots of versions. While one person who
is hitting the most recent version might be ok, everyone else will still
have to search for theirs. Just as they do now.
> 2) Any SELECT queries within the same session will be again accessing
> the new version of the row
I don't see how this is different from what we currently have. "same
session" could have been dropped from your separate table space, and
then you'd have to go search through previous versions of the row...
exactly like you do now.
And worse, if you dont want to drop your version of the row from the
separate table space until you commit/rollback, then no other user can
start a transaction on that table until your done! oh no! You have
reads and writes blocking each other.
> 3) Any SELECT queries from other users will still be accessing the old
> version
Again.. the same.
> 4) When UPDATE transaction is ROLLBACK just release the space used in
> new temporary location
current layout makes rollback very very fast.
> 5) When UPDATE transaction is COMMIT then try to LOCK the old version
> and overwrite it at the same physical location (NO FRAGMENTATION).
Not sure what you mean by lock, but lock requires single user access and
slow's things down. Right now we just bump the "most active transaction
number", which is very efficient, and requires no locks. As soon as you
lock anything, somebody, by definition, has to wait.
> 6) Similar mechanism can be applied on INSERTS and DELETES
> 7) In case that transaction was COMMIT, the temporary location can be
> either released or archived/cleaned on a pre-scheduled basis. This will
> possibly allow the introduction of a TRANSACTION LOG backup mechanism as
> a next step.
You are kind of assuming there will only ever be one new transaction,
and one old transaction. What about a case where 10 people start a
transaction, and there are 10 versions of the row?
It seems to me like you are using very long transactions, which is
causing lots of row versions to show up. Have you run explain analyze
on your slow querys to find out the problems?
Have you checked to see if you are cpu bound or io bound? If you are
dealing with lots of row versions, I'd assume you are cpu bound. If you
check your system though, and see you are io bound, I think that might
invalidate your assumptions above.
MVCC makes multi user access very nice because readers and writers dont
block each other, and there are very few locks. It does come with some
kinks (gotta vacuum, keep transactions short, you must commit, etc).
select count(*) for example is always going to be slow... just expect
it, lets not destroy what works well about the database just to make it
fast. Instead, find a better alternative so you dont have to run it.
Just like any database, you have to work within MVCC's good points and
try to avoid the bad spots.
-Andy
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:21 ` Re: MVCC performance issue Andy Colson <andy@squeakycode.net>
@ 2010-11-12 17:13 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 18:19 ` Re: MVCC performance issue Scott Carey <scott@richrelevance.com>
0 siblings, 1 reply; 29+ messages in thread
From: Kyriacos Kyriacou @ 2010-11-12 17:13 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
My suggestion had just a single difference from what currently MVCC is
doing (btw I never said that MVCC is bad).
NOW ===> on COMMIT previous version record is expired and the
new version record (created in new dynamically allocated
spot, as you said) is set as "active"
MY ===> on COMMIT, to update new version data over the same physical
location that initial version was
and release the space used to keep the new version (that was
dynamically allocated).
The rest are all the same! I do not think that this is breaking anything
and I still believe that this might help.
I will try to plan upgrade the soonest possible to the newest version.
Reading few words about HOT updates
it seems that more or less is similar to what I have described and will
be very helpful.
Kyriacos
> -----Original Message-----
> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Friday, November 12, 2010 6:22 PM
> To: Kyriacos Kyriacou
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] MVCC performance issue
>
> On 11/12/2010 7:47 AM, Kyriacos Kyriacou wrote:
> >
> > SUGGESTION
> > --------------
> > 1) When a raw UPDATE is performed, store all "new raw versions"
either
> > in separate temporary table space
> > or in a reserved space at the end of each table (can be
allocated
> > dynamically) etc
>
> Your use of "raw" is confusing. I'll just ignore the word. New row
> versions are already stored in a dynamically allocated spot, right
along
> with the other versions of the table. You are assuming that getting
to
> the "correct" version of the row is very slow? That's only going to
be
> the case if you have lots and lots of versions. And your solution
will
> not actually help if there are lots of versions. While one person who
> is hitting the most recent version might be ok, everyone else will
still
> have to search for theirs. Just as they do now.
>
> > 2) Any SELECT queries within the same session will be again
accessing
> > the new version of the row
>
> I don't see how this is different from what we currently have. "same
> session" could have been dropped from your separate table space, and
> then you'd have to go search through previous versions of the row...
> exactly like you do now.
>
> And worse, if you dont want to drop your version of the row from the
> separate table space until you commit/rollback, then no other user can
> start a transaction on that table until your done! oh no! You have
> reads and writes blocking each other.
>
> > 3) Any SELECT queries from other users will still be accessing the
old
> > version
>
> Again.. the same.
>
> > 4) When UPDATE transaction is ROLLBACK just release the space used
in
> > new temporary location
>
> current layout makes rollback very very fast.
>
> > 5) When UPDATE transaction is COMMIT then try to LOCK the old
version
> > and overwrite it at the same physical location (NO FRAGMENTATION).
>
> Not sure what you mean by lock, but lock requires single user access
and
> slow's things down. Right now we just bump the "most active
transaction
> number", which is very efficient, and requires no locks. As soon as
you
> lock anything, somebody, by definition, has to wait.
>
>
> > 6) Similar mechanism can be applied on INSERTS and DELETES
> > 7) In case that transaction was COMMIT, the temporary location can
be
> > either released or archived/cleaned on a pre-scheduled basis. This
will
> > possibly allow the introduction of a TRANSACTION LOG backup
mechanism as
> > a next step.
>
> You are kind of assuming there will only ever be one new transaction,
> and one old transaction. What about a case where 10 people start a
> transaction, and there are 10 versions of the row?
>
>
> It seems to me like you are using very long transactions, which is
> causing lots of row versions to show up. Have you run explain analyze
> on your slow querys to find out the problems?
>
> Have you checked to see if you are cpu bound or io bound? If you are
> dealing with lots of row versions, I'd assume you are cpu bound. If
you
> check your system though, and see you are io bound, I think that might
> invalidate your assumptions above.
>
> MVCC makes multi user access very nice because readers and writers
dont
> block each other, and there are very few locks. It does come with
some
> kinks (gotta vacuum, keep transactions short, you must commit, etc).
>
> select count(*) for example is always going to be slow... just expect
> it, lets not destroy what works well about the database just to make
it
> fast. Instead, find a better alternative so you dont have to run it.
>
> Just like any database, you have to work within MVCC's good points and
> try to avoid the bad spots.
>
> -Andy
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:21 ` Re: MVCC performance issue Andy Colson <andy@squeakycode.net>
2010-11-12 17:13 ` Re: MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 18:19 ` Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 29+ messages in thread
From: Scott Carey @ 2010-11-12 18:19 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
On Nov 12, 2010, at 9:13 AM, Kyriacos Kyriacou wrote:
> My suggestion had just a single difference from what currently MVCC is
> doing (btw I never said that MVCC is bad).
>
> NOW ===> on COMMIT previous version record is expired and the
> new version record (created in new dynamically allocated
> spot, as you said) is set as "active"
>
> MY ===> on COMMIT, to update new version data over the same physical
> location that initial version was
> and release the space used to keep the new version (that was
> dynamically allocated).
But what about other transactions that can still see the old version?
You can't overwrite the old data if there are any other transactions open in the system at all. You have to have a mechanism to keep the old copy around for a while.
>
> The rest are all the same! I do not think that this is breaking anything
> and I still believe that this might help.
>
> I will try to plan upgrade the soonest possible to the newest version.
> Reading few words about HOT updates
> it seems that more or less is similar to what I have described and will
> be very helpful.
>
> Kyriacos
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: MVCC performance issue
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
@ 2010-11-12 17:39 ` Scott Marlowe <scott.marlowe@gmail.com>
4 siblings, 0 replies; 29+ messages in thread
From: Scott Marlowe @ 2010-11-12 17:39 UTC (permalink / raw)
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>; +Cc: pgsql-performance
OK, in general you have to pay for MVCC one way or another. Many
databases make you pay as you go, so to speak, by storing all the MVCC
info in a log to be applied at some future date. Other databases you
can pay later, by storing all the MVCC in the table itself. Both have
similar costs, but one can punish you harshly if you let the MVCC data
stored in the database get out of hand.
8.3 and above are much more aggresive about autovacuuming, and on
bigger hardware you can make it VERY aggressive and keep the bloat out
while keeping up good throughput. On some servers I set up 4 or 6 or
8 autovacuum threads to keep up. If you were on another db you
might be adding more drives to make some other part faster.
For batch processing storing all MVCC data in the data store can be
problematic, but for more normal work where you're changing <1% of a
table all the time it can be very fast.
Some other databases will just run out of space to store transactions
and roll back everything you've done. PostgreSQL will gladly let you
shoot yourself in the foot with bloating the data store by running
successive whole table updates without vacuuming in between.
Bottom line, if your hardware can't keep up, it can't keep up. If
vacuum capsizes your IO and still can't keep up then you need more
disks and / or better storage subsystems. A 32 disk array with single
controller goes for ~$7 to $10k, and you can sustain some pretty
amazing thgouhput on that kind of IO subsystem.
If you're doing batch processing you can get a lot return by just
making sure you vacuum after each mass update. Especially if you are
on a single use machine with no cost delays for vacuum, running a
vacuum on a freshly worked table should be pretty fast.
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2010-11-30 00:21 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-11 18:25 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-13 05:53 ` Craig Ringer <craig@postnewspapers.com.au>
2010-11-13 07:05 ` Rich <rhdyes@gmail.com>
2010-11-13 18:38 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-14 00:10 ` Craig Ringer <craig@postnewspapers.com.au>
2010-11-14 08:30 ` Marti Raudsepp <marti@juffo.org>
2010-11-15 00:32 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-14 08:46 ` Marti Raudsepp <marti@juffo.org>
2010-11-30 00:21 ` Robert Haas <robertmhaas@gmail.com>
2010-11-12 13:47 MVCC performance issue Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 13:52 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 15:34 ` bricklen <bricklen@gmail.com>
2010-11-12 15:37 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 18:13 ` Scott Carey <scott@richrelevance.com>
2010-11-12 13:54 ` Thom Brown <thom@linux.com>
2010-11-12 16:14 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:19 ` Ben Chobot <bench@silentmedia.com>
2010-11-12 17:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-11-12 16:22 ` Kenneth Marshall <ktm@rice.edu>
2010-11-12 16:33 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:22 ` Thom Brown <thom@linux.com>
2010-11-12 17:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-11-12 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-12 15:53 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2010-11-12 16:18 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 16:21 ` Andy Colson <andy@squeakycode.net>
2010-11-12 17:13 ` Kyriacos Kyriacou <kyriacosk@prime-tel.com>
2010-11-12 18:19 ` Scott Carey <scott@richrelevance.com>
2010-11-12 17:39 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox