pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy vacuum
25+ messages / 11 participants
[nested] [flat]
* why vacuum
@ 2005-10-26 04:45 Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 4 replies; 25+ messages in thread
From: Kenneth Gonsalves @ 2005-10-26 04:45 UTC (permalink / raw)
To: pgsql-sql
hi,
i was in a minor flame war with a mysql guy - his major grouse was that
'I wouldnt commit mission critical data to a database that needs to be
vacuumed once a week'. So why does pg need vacuum?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 05:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
3 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-10-26 05:14 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
Kenneth Gonsalves <lawgon@thenilgiris.com> writes:
> i was in a minor flame war with a mysql guy - his major grouse was that
> 'I wouldnt commit mission critical data to a database that needs to be
> vacuumed once a week'.
This guy is not worth arguing with.
> So why does pg need vacuum?
Every database needs maintenance operations. PG is designed in a way
that exposes the maintenance operations to the control of the DBA a bit
more than most other DBMSes do: specifically, you get to decide when
some of the overhead work happens. We think this is a feature, because
you can schedule the overhead for low-activity periods (nights,
weekends, whatever). In other DBMSes the equivalent work happens as
part of foreground queries, no matter how time-critical they might be.
Now, there's no doubt that for a database run by a non-expert person
who can't even spell DBA, exposing this sort of knob isn't very helpful.
So there's work afoot to provide automatic maintenance tools (ie,
autovacuum). Over time I think autovacuum will get smart enough that
even experts will usually use it. But that point will only be reached
when autovacuum has some idea about doing more work during low-load
periods.
Unless MySQL invents some concept equivalent to VACUUM, they won't have
any prayer at all of being able to shift maintenance overhead to
low-load times.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 05:57 Michael Fuhr <mike@fuhr.org>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
3 siblings, 0 replies; 25+ messages in thread
From: Michael Fuhr @ 2005-10-26 05:57 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Wed, Oct 26, 2005 at 10:15:17AM +0530, Kenneth Gonsalves wrote:
> i was in a minor flame war with a mysql guy - his major grouse was that
> 'I wouldnt commit mission critical data to a database that needs to be
> vacuumed once a week'.
The use of the word "commit" is amusing, considering that MySQL's
default table type doesn't support transactions. There's always
InnoDB, but it seems like there was something about that in the
news recently....
Compare the following lists of gotchas and decide which database
*you'd* commit mission-critical data to:
http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html
> So why does pg need vacuum?
See the documentation:
http://www.postgresql.org/docs/8.0/interactive/maintenance.html#ROUTINE-VACUUMING
--
Michael Fuhr
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 06:22 Bath, David <dave.bath@unix.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 25+ messages in thread
From: Bath, David @ 2005-10-26 06:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, 26 Oct 2005 15:14, Tom Lane wrote:
> Kenneth Gonsalves <lawgon@thenilgiris.com> writes:
> > (A MySQueeeel guy said, not Kenneth)...
> > 'I wouldnt commit mission critical data to a database that needs to be
> > vacuumed once a week'.
My two-penneth worth...<flamage>
I wouldn't commit mission critical data to a database (or DBA) that doesn't
have a concept of vacuuming (or desire to do it regularly). But, less
flamingly, I wouldn't commit mission-critical data to something that lacked
the ability to have proper constraints, triggers and server-side procedures
to ensure the data actually remains sensible. Note that Sybase/MS-SQL's
check constraint model asserts the constraint BEFORE the trigger, which
discourages you from attempting to check and handle meaning of data!</flamage>
> This guy is not worth arguing with.
D'Accord!
> > So why does pg need vacuum?
For (inter alia) the same reason that
* Oracle has an ANALYZE_SCHEMA and DBMS_SPACE_ADMIN
and (hoist by his own petard)
* MySQueeeeaL has myisamchk --stats_method=method_name --analyze
<flamage>Oh, well: MySQL bigot and internal consistency? whadya expect?</flamage>
Dave Bath
(Oracle DBA for health/telcos way back in 1986: honeywrong GCOS and Pr1mos)
--
David T. Bath
dave.bath@unix.net
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 07:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bath, David <dave.bath@unix.net>
1 sibling, 0 replies; 25+ messages in thread
From: Tom Lane @ 2005-10-26 07:00 UTC (permalink / raw)
To: dave.bath@unix.net; +Cc: pgsql-sql
"Bath, David" <dave.bath@unix.net> writes:
> ... Note that Sybase/MS-SQL's
> check constraint model asserts the constraint BEFORE the trigger, which
> discourages you from attempting to check and handle meaning of data!
Er, doesn't PG do it that way too?
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 07:49 Kenneth Gonsalves <lawgon@thenilgiris.com>
parent: Bath, David <dave.bath@unix.net>
1 sibling, 0 replies; 25+ messages in thread
From: Kenneth Gonsalves @ 2005-10-26 07:49 UTC (permalink / raw)
To: pgsql-sql; dave.bath@unix.net
On Wednesday 26 Oct 2005 11:52 am, Bath, David wrote:
> > This guy is not worth arguing with.
> D'Accord!
thanks all for the clarification. in case anyone is interested in the
original conversation it is here:
http://ebergen.net/wordpress/?p=83
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 14:44 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
3 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2005-10-26 14:44 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
> hi,
> i was in a minor flame war with a mysql guy - his major grouse was that
> 'I wouldnt commit mission critical data to a database that needs to be
> vacuumed once a week'. So why does pg need vacuum?
The absolutely funniest thing about what this guy is saying is that he
seems rather ignorant of the behaviour of innodb tables. They have
another name for the vacuum command there. It's:
ALTER TABLE tbl_name ENGINE=INNODB
Which rebuilds the whole fraggin's table, with an exclusive lock.
and guess what innodb does if you don't run this command every so often?
Can you guess yet? Yep, that's right, it just keeps growing and growing
and growing.
Hell, innodb isn't any better than the original mvcc implementation
postgresql had when vacuums were all full and took exclusive locks.
But at least with PostgreSQL it was a well documented issue, and was
mentioned in the administrative section of the docs, so you knew you had
to do it. It's kind of tucked away in the innodb section of the mysql
docs, and most mysql folks don't even know they need to do it, since
they almost all use myisam table types.
If someone is more worried about postgresql's non-blocking, easily
scheduled vacuuming, but is using myisam tables, and a database that by
default allows numeric overflows to just insert the maximum possible
value, I wouldn't trust them with handling my paycheck with their
fan-boy database.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 15:19 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
3 siblings, 3 replies; 25+ messages in thread
From: Scott Marlowe @ 2005-10-26 15:19 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
> hi,
> i was in a minor flame war with a mysql guy - his major grouse was that
> 'I wouldnt commit mission critical data to a database that needs to be
> vacuumed once a week'. So why does pg need vacuum?
Oh man oh man. After reading the article, I realized he was saying that
he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't
trust MySQL to replace oracle either.
But, the next time someone says that slony is a toy add on, and MySQL
has REAL replication, point them to THIS page on the same blog:
http://ebergen.net/wordpress/?p=70
In short, it basically shows that MySQL replication is incredibly
fragile, and not fit for production on any real system. The lack of
system wide transaction support, like postgresql has, makes the problem
he outlines that much worse.
The hoops people will jump through to use their favorite toys...
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 16:09 Jan Wieck <JanWieck@Yahoo.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 2 replies; 25+ messages in thread
From: Jan Wieck @ 2005-10-26 16:09 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
On 10/26/2005 11:19 AM, Scott Marlowe wrote:
> On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
>> hi,
>> i was in a minor flame war with a mysql guy - his major grouse was that
>> 'I wouldnt commit mission critical data to a database that needs to be
>> vacuumed once a week'. So why does pg need vacuum?
>
> Oh man oh man. After reading the article, I realized he was saying that
> he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't
> trust MySQL to replace oracle either.
>
> But, the next time someone says that slony is a toy add on, and MySQL
> has REAL replication, point them to THIS page on the same blog:
>
> http://ebergen.net/wordpress/?p=70
>
You must have missed the FAQ and other side notes about replication in
the MySQL manual. Essentially MySQL replication is nothing but a query
duplicating system, with the added sugar of taking care of now() and
some other non-deterministic things, but not all of them.
Non-deterministic user defined procedures, functions and triggers will
simply blow MySQL's sophisticated replication apart.
Jan
> In short, it basically shows that MySQL replication is incredibly
> fragile, and not fit for production on any real system. The lack of
> system wide transaction support, like postgresql has, makes the problem
> he outlines that much worse.
>
> The hoops people will jump through to use their favorite toys...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 16:10 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 25+ messages in thread
From: Scott Marlowe @ 2005-10-26 16:10 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
On Wed, 2005-10-26 at 11:09, Jan Wieck wrote:
> On 10/26/2005 11:19 AM, Scott Marlowe wrote:
>
> > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
> >> hi,
> >> i was in a minor flame war with a mysql guy - his major grouse was that
> >> 'I wouldnt commit mission critical data to a database that needs to be
> >> vacuumed once a week'. So why does pg need vacuum?
> >
> > Oh man oh man. After reading the article, I realized he was saying that
> > he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't
> > trust MySQL to replace oracle either.
> >
> > But, the next time someone says that slony is a toy add on, and MySQL
> > has REAL replication, point them to THIS page on the same blog:
> >
> > http://ebergen.net/wordpress/?p=70
> >
>
> You must have missed the FAQ and other side notes about replication in
> the MySQL manual. Essentially MySQL replication is nothing but a query
> duplicating system, with the added sugar of taking care of now() and
> some other non-deterministic things, but not all of them.
>
> Non-deterministic user defined procedures, functions and triggers will
> simply blow MySQL's sophisticated replication apart.
True, but I never expected a CTRL-C to the mysql command line to break
replication. Even for MySQL's lackadaisical behaviour, that's pretty
far out.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 16:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-10-26 16:12 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
>> i was in a minor flame war with a mysql guy - his major grouse was that
>> 'I wouldnt commit mission critical data to a database that needs to be
>> vacuumed once a week'. So why does pg need vacuum?
> Oh man oh man. After reading the article, I realized he was saying that
> he wouldn't trust PostgreSQL to replace Oracle.
Well, that's a slightly more respectable point of view, but Oracle has
surely got its own set of gotchas ... doesn't it still have issues if
you run a transaction that's large enough to overrun the fixed-size
rollback areas (or whatever they call them)?
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-26 16:18 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Scott Marlowe @ 2005-10-26 16:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
On Wed, 2005-10-26 at 11:12, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
> >> i was in a minor flame war with a mysql guy - his major grouse was that
> >> 'I wouldnt commit mission critical data to a database that needs to be
> >> vacuumed once a week'. So why does pg need vacuum?
>
> > Oh man oh man. After reading the article, I realized he was saying that
> > he wouldn't trust PostgreSQL to replace Oracle.
>
> Well, that's a slightly more respectable point of view, but Oracle has
> surely got its own set of gotchas ... doesn't it still have issues if
> you run a transaction that's large enough to overrun the fixed-size
> rollback areas (or whatever they call them)?
Yep, and it also has the "snapshot too old" issue for long running
transactions (long running meaning how many other things have happened
since it started, not length of time). Imagine starting a backup in
postgresql, getting an hour into it and suddenly it ends because
10,000,000 rows have been inserted while it was going. That kind of
error. My reply on that forum pointed out that EVERY database has
gotchas, and to pretend that your database's gotchas are ok but some
other database's gotchas are unacceptable is disingenuous at best.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 01:34 Igor Shevchenko <igor@carcass.ath.cx>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 25+ messages in thread
From: Igor Shevchenko @ 2005-10-27 01:34 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
On Wednesday 26 October 2005 17:44, Scott Marlowe wrote:
> On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote:
> > hi,
> > i was in a minor flame war with a mysql guy - his major grouse was that
> > 'I wouldnt commit mission critical data to a database that needs to be
> > vacuumed once a week'. So why does pg need vacuum?
>
> The absolutely funniest thing about what this guy is saying is that he
> seems rather ignorant of the behaviour of innodb tables. They have
> another name for the vacuum command there. It's:
>
> ALTER TABLE tbl_name ENGINE=INNODB
>
> Which rebuilds the whole fraggin's table, with an exclusive lock.
>
> and guess what innodb does if you don't run this command every so often?
>
> Can you guess yet? Yep, that's right, it just keeps growing and growing
> and growing.
Not quite so.
I'm running quite a few (>50) mysql/innodb servers with database sizes raging
from 500mb to 50gb, and I never had to rebuild any innodb tables this way.
InnoDB uses index-based data storage and rollback segments, which makes it
harder to add bloat to their databases, as compared to PG (but autovacuum is
my saviour). Innodb will actually free space when you do DELETE or TRUNCATE,
but still, it's tables, indexes and tablespaces will get fragmented. This
gets worse over time, but it had never been a big problem for me. My
databases do 50 queries/second on average, 24/7. Note - all of this can be
due to my access and data change patterns; YMMV. The "only" cleanup operation
I do is CHECK/OPTIMIZE, on monthly basis; it's not much better than old PG's
VACUUM, as it brings mysql/innodb's performance down by 5x-10x times; same
goes for almost any long-running query.
I'm moving those servers to PG, due to this (concurrency) and other reasons.
My top 3 reasons are: a much better concurrency (even with bg vacuums
running :-), a much better planner, and PG's rich feature set.
--
Best Regards,
Igor Shevchenko
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 08:22 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 2 replies; 25+ messages in thread
From: Mario Splivalo @ 2005-10-27 08:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2005-10-26 at 10:19 -0500, Scott Marlowe wrote:
> But, the next time someone says that slony is a toy add on, and MySQL
> has REAL replication, point them to THIS page on the same blog:
>
> http://ebergen.net/wordpress/?p=70
>
> In short, it basically shows that MySQL replication is incredibly
> fragile, and not fit for production on any real system. The lack of
> system wide transaction support, like postgresql has, makes the problem
> he outlines that much worse.
>
> The hoops people will jump through to use their favorite toys...
I see no point in blatantly putting 'other' products such shape. Pgsql
offers no replication at all, you need to use slony (wich is also a poor
replacement for a wannabe replication), or some other commercial
products. What about 2PC? What about linking the databases from
different servers?
I've been using MSSQL for over 6 years now, started with MSSQL7 and went
on with MSSQL2000. The replication it offers is superb! It runs
smoothly, you have click-me-click interface with wich you can create
publications and deploy them to subscribers with ease. Ok, there are
gotchas (and they-re ms-style funny), but it's all well documented, and
works most of the time as expected. So what?
Btw, I 'ported' the merge replication from MSSQL to postgres. It
basicaly adds triggers to every table that is 'published' for
replication. There is a separate table to store and calculate the change
differences from several servers (so you could do update on any of the
servers and change will be propagated to the others). I'm missing 2PC
badly here, I wrote some stupid python 'thingie' wich should act as 2PC
serializer, but that's slow as hell. And triggers slow down postgres
quite a bit.
So, to end this 'my father has bigger car than yours' debate, when will
postgres have two phase commit protocol implemented? I presume that
should come after you allow something like SELECT someCol FROM
myServer.myDatabase[Schema].myTable...
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 08:22 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 2 replies; 25+ messages in thread
From: Mario Splivalo @ 2005-10-27 08:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
> >
>
> You must have missed the FAQ and other side notes about replication in
> the MySQL manual. Essentially MySQL replication is nothing but a query
> duplicating system, with the added sugar of taking care of now() and
> some other non-deterministic things, but not all of them.
>
> Non-deterministic user defined procedures, functions and triggers will
> simply blow MySQL's sophisticated replication apart.
>
That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
replication wos working as expected, and the load that replication posed
was insignificant. The only TRUE problem was that replication was
unidirectional. That SAME problem has Slony, and other 'replication
systems' available for postgres.
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 10:21 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 1 reply; 25+ messages in thread
From: Andrew Sullivan @ 2005-10-27 10:21 UTC (permalink / raw)
To: pgsql-sql
On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote:
> offers no replication at all, you need to use slony (wich is also a poor
> replacement for a wannabe replication), or some other commercial
> products. What about 2PC? What about linking the databases from
Slony is in fact a community-supported system; so I don't know why
you think that amounts to "no replication at all". And since this is
a community-supported system, it'd be nice if you said why it's a
"poor replacement for wannabe replication". What's wrong with it?
> Btw, I 'ported' the merge replication from MSSQL to postgres. It
> basicaly adds triggers to every table that is 'published' for
> replication. There is a separate table to store and calculate the change
> differences from several servers (so you could do update on any of the
> servers and change will be propagated to the others). I'm missing 2PC
> badly here, I wrote some stupid python 'thingie' wich should act as 2PC
> serializer, but that's slow as hell. And triggers slow down postgres
> quite a bit.
This is interesting. Care to package it up for others, or write a
proof-of-concept outline for the lists or General Bits or something
like that? This is a different sort of replication people are asking
for. Note that you get 2PC in the next Postgres release.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 12:34 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 25+ messages in thread
From: Mario Splivalo @ 2005-10-27 12:34 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On Thu, 2005-10-27 at 06:21 -0400, Andrew Sullivan wrote:
> On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote:
> > offers no replication at all, you need to use slony (wich is also a poor
> > replacement for a wannabe replication), or some other commercial
> > products. What about 2PC? What about linking the databases from
>
> Slony is in fact a community-supported system; so I don't know why
> you think that amounts to "no replication at all". And since this is
> a community-supported system, it'd be nice if you said why it's a
> "poor replacement for wannabe replication". What's wrong with it?
Postgres itself offers no replication. You could achive some sort of
replication by restoring the parts of WAL files, but that's rather
inconvinient. Then, if you want to replicate your data in any way, you
need to take slony, or whatever is existant out there, commercial or
open-free-source.
I appologize here if I insulted the slony developers, I ment nothing
like that :) I am sorry. I was just addressing the issue where
replication to one means 'just move my data here', and at others it
means 'merge my data'.
> > Btw, I 'ported' the merge replication from MSSQL to postgres. It
> > basicaly adds triggers to every table that is 'published' for
> > replication. There is a separate table to store and calculate the change
> > differences from several servers (so you could do update on any of the
> > servers and change will be propagated to the others). I'm missing 2PC
> > badly here, I wrote some stupid python 'thingie' wich should act as 2PC
> > serializer, but that's slow as hell. And triggers slow down postgres
> > quite a bit.
>
> This is interesting. Care to package it up for others, or write a
> proof-of-concept outline for the lists or General Bits or something
> like that? This is a different sort of replication people are asking
> for. Note that you get 2PC in the next Postgres release.
I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
could http-redirect me, I'll be most thankfull.
Mentioning the 2PC, is it available in pg8.1beta4?
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 13:21 Richard Huxton <dev@archonet.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 1 reply; 25+ messages in thread
From: Richard Huxton @ 2005-10-27 13:21 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo wrote:
> On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
>
>>You must have missed the FAQ and other side notes about replication in
>>the MySQL manual. Essentially MySQL replication is nothing but a query
>>duplicating system, with the added sugar of taking care of now() and
>>some other non-deterministic things, but not all of them.
>>
>>Non-deterministic user defined procedures, functions and triggers will
>>simply blow MySQL's sophisticated replication apart.
>
> That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
> replication wos working as expected, and the load that replication posed
> was insignificant. The only TRUE problem was that replication was
> unidirectional. That SAME problem has Slony, and other 'replication
> systems' available for postgres.
I think you're missing the "non-deterministic" bit. Mysql replication is
based on shipping statements AFAICT
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
"The binary log contains all statements which updated data or
potentially could have updated it (for example, a DELETE which matched
no rows). Statements are stored in the form of “events” that describe
the modifications."
So - if your statement contains something non-deterministic that isn't
catered for in Mysql's code then it will break.
At it's simplest - if I write a function my_random() and then do:
UPDATE foo SET a=1 WHERE b < my_random();
IF my_random() returns different results on different machines, then the
replication will be broken. See the manual entry below:
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
That's not to say the system is worthless - it works fine for many
people. But it does have limitations.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 13:47 Jan Wieck <JanWieck@Yahoo.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 0 replies; 25+ messages in thread
From: Jan Wieck @ 2005-10-27 13:47 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
On 10/27/2005 4:22 AM, Mario Splivalo wrote:
> On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
>> >
>>
>> You must have missed the FAQ and other side notes about replication in
>> the MySQL manual. Essentially MySQL replication is nothing but a query
>> duplicating system, with the added sugar of taking care of now() and
>> some other non-deterministic things, but not all of them.
>>
>> Non-deterministic user defined procedures, functions and triggers will
>> simply blow MySQL's sophisticated replication apart.
>>
>
> That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
> replication wos working as expected, and the load that replication posed
> was insignificant. The only TRUE problem was that replication was
> unidirectional. That SAME problem has Slony, and other 'replication
> systems' available for postgres.
Read again. "Non-deterministic user defined procedures ...". Please give
me *ONE* example of a user defined procedure in 3.5.x at all. You must
have missed the point that stored procedures are a new feature in 5.0.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 14:12 Jan Wieck <JanWieck@Yahoo.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 0 replies; 25+ messages in thread
From: Jan Wieck @ 2005-10-27 14:12 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
On 10/27/2005 4:22 AM, Mario Splivalo wrote:
> I see no point in blatantly putting 'other' products such shape. Pgsql
> offers no replication at all, you need to use slony (wich is also a poor
> replacement for a wannabe replication), or some other commercial
> products. What about 2PC? What about linking the databases from
> different servers?
I agree that Scott's comment was a bit harsh. But would you please add a
few details that explain what makes Slony a "poor replacement" in your
opinion? And please don't repeat that stupid "not builtin". Any add-on
is as good as its reliability and features. Or would you at the same
time say that MySQL has only a poor replacement for wannabe transactions
and foreign keys, because their storage engines are in fact add-ons?
> Btw, I 'ported' the merge replication from MSSQL to postgres. It
> basicaly adds triggers to every table that is 'published' for
> replication. There is a separate table to store and calculate the change
> differences from several servers (so you could do update on any of the
> servers and change will be propagated to the others). I'm missing 2PC
> badly here, I wrote some stupid python 'thingie' wich should act as 2PC
> serializer, but that's slow as hell. And triggers slow down postgres
> quite a bit.
Would you consider publishing that code under the BSD license? It sounds
very much like one of the "other add-on replication systems" our users
keep asking for. If you can't publish the code, do you have any design
papers or a technical concept that could be used as a base for a new
PostgreSQL community project?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 15:51 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 1 reply; 25+ messages in thread
From: Andrew Sullivan @ 2005-10-27 15:51 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@mobart.hr>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On Thu, Oct 27, 2005 at 02:34:13PM +0200, Mario Splivalo wrote:
>
> Postgres itself offers no replication. You could achive some sort of
> replication by restoring the parts of WAL files, but that's rather
> inconvinient. Then, if you want to replicate your data in any way, you
Well, AFAIK Oracle itself offers no replication, either. If you want
it, you have to buy a license for it. Which means it's an add-on.
Heck, most Linux distributions' kernels don't offer support for
network cards: they're an add-on. We call them modules. (In case
it's not clear, I don't buy the "itself/add-on" distinction. The
point is that the whole system works together. PostgreSQL most
definitely offers replication. In fact, you can get warm-standby with
WAL shipping, or read-only capabilities with Slony or some other
tools.)
> like that :) I am sorry. I was just addressing the issue where
> replication to one means 'just move my data here', and at others it
> means 'merge my data'.
Yes: this multiple-meaning "replication" word causes a great deal of
confusion. But just because one person has need A does not mean that
need B isn't a real one. My employer, Afilias, sponsors the Slony
work, in the direction of our needs. We did not initially have a
many-write-nodes scenario in mind, and we had a pressing need for a
"single master" system. So that's the itch we scratched.
> I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
> at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
> could http-redirect me, I'll be most thankfull.
Proof of concept is just a description of what you did, how it
worked, design and limitations, &c. Post it to the lists (uh,
-general or maybe -hackers, I suppose), or put it on a web page or
whatever. General Bits is a fairly regular column that Elein Mustain
puts out. I bet she'd include a submission on this topic, although
you'd have to ask her. You can find GB at
<http://varlena.com/varlena/GeneralBits/;.
> Mentioning the 2PC, is it available in pg8.1beta4?
Should be.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 17:25 Jan Wieck <JanWieck@Yahoo.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 0 replies; 25+ messages in thread
From: Jan Wieck @ 2005-10-27 17:25 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On 10/27/2005 8:34 AM, Mario Splivalo wrote:
> Postgres itself offers no replication.
Oracle itself offers no replication.
IBM DB2 itself offers no replication.
Yet most of the products out there for Oracle, DB2 and PostgreSQL are
far better than what I read here:
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
What a great example of "offering replication itself" ... *cough*
I guess it'd be easier to port a well designed add-on like Slony-I to
MySQL (now that they have triggers) than fixing their broken design. Not
that I volunteer to do it, but Slony-I is released under BSD, anyone
feel free to offer a good solution for MySQL.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-27 17:52 Jim C. Nasby <jnasby@pervasive.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 25+ messages in thread
From: Jim C. Nasby @ 2005-10-27 17:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: mario.splivalo@mobart.hr; pgsql-sql
On Thu, Oct 27, 2005 at 02:21:15PM +0100, Richard Huxton wrote:
> So - if your statement contains something non-deterministic that isn't
> catered for in Mysql's code then it will break.
>
> At it's simplest - if I write a function my_random() and then do:
> UPDATE foo SET a=1 WHERE b < my_random();
> IF my_random() returns different results on different machines, then the
> replication will be broken. See the manual entry below:
> http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
>
> That's not to say the system is worthless - it works fine for many
> people. But it does have limitations.
And you can easily have multi-master syncronous replication in
PostgreSQL using the same idea; just see pgCluster.
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-28 11:09 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 25+ messages in thread
From: Mario Splivalo @ 2005-10-28 11:09 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On Thu, 2005-10-27 at 11:51 -0400, Andrew Sullivan wrote:
> Well, AFAIK Oracle itself offers no replication, either. If you want
> it, you have to buy a license for it. Which means it's an add-on.
> Heck, most Linux distributions' kernels don't offer support for
> network cards: they're an add-on. We call them modules. (In case
> it's not clear, I don't buy the "itself/add-on" distinction. The
> point is that the whole system works together. PostgreSQL most
> definitely offers replication. In fact, you can get warm-standby with
> WAL shipping, or read-only capabilities with Slony or some other
> tools.)
I wish I was Dumbo now. I could hide myself with me ears :)
> > I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
> > at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
> > could http-redirect me, I'll be most thankfull.
>
> Proof of concept is just a description of what you did, how it
> worked, design and limitations, &c. Post it to the lists (uh,
> -general or maybe -hackers, I suppose), or put it on a web page or
> whatever. General Bits is a fairly regular column that Elein Mustain
> puts out. I bet she'd include a submission on this topic, although
> you'd have to ask her. You can find GB at
> <http://varlena.com/varlena/GeneralBits/;.
I'll go trough my code, it's been a while since I touched it, I'll write
some documentation and I'll inform the comunity. Thnx for the pointouts.
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: why vacuum
@ 2005-10-28 13:34 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 25+ messages in thread
From: Andrew Sullivan @ 2005-10-28 13:34 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@mobart.hr>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On Fri, Oct 28, 2005 at 01:09:43PM +0200, Mario Splivalo wrote:
> I'll go trough my code, it's been a while since I touched it, I'll write
> some documentation and I'll inform the comunity. Thnx for the pointouts.
And thank _you_ for proposing to do this. If everyone contributes
their discoveries and improvements, we all benefit.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2005-10-28 13:34 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 04:45 why vacuum Kenneth Gonsalves <lawgon@thenilgiris.com>
2005-10-26 05:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 06:22 ` Bath, David <dave.bath@unix.net>
2005-10-26 07:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 07:49 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2005-10-26 05:57 ` Michael Fuhr <mike@fuhr.org>
2005-10-26 14:44 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-27 01:34 ` Igor Shevchenko <igor@carcass.ath.cx>
2005-10-26 15:19 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-26 16:09 ` Jan Wieck <JanWieck@Yahoo.com>
2005-10-26 16:10 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-27 08:22 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-27 13:21 ` Richard Huxton <dev@archonet.com>
2005-10-27 17:52 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-27 13:47 ` Jan Wieck <JanWieck@Yahoo.com>
2005-10-26 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 16:18 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-27 08:22 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-27 10:21 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-27 12:34 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-27 15:51 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-28 11:09 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-28 13:34 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-27 17:25 ` Jan Wieck <JanWieck@Yahoo.com>
2005-10-27 14:12 ` Jan Wieck <JanWieck@Yahoo.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