On 9/10/07, Cyril SCETBON <cscetbon.ext at orange-ftgroup.com> wrote:
>
>
> Jan Wieck wrote:
> > On 9/10/2007 12:49 PM, Cyril SCETBON wrote:
> >>
> >> Christopher Browne wrote:
> >>> Cyril SCETBON wrote:
> >>>>
> >>>>
> >>>> Jan Wieck wrote:
> >>>>> On 9/10/2007 10:07 AM, Cyril SCETBON wrote:
> >>>>>> Hi,
> >>>>>>
> >>>>>> We're hitting 400 requests per second and noticed that sl_log_?
> >>>>>> are growing a lot. This is also due to the fact that our tables
> >>>>>> have a lot of columns (more that 100). We noticed that slony
> >>>>>> stores each column and its value for each request in sl_log_x.
> >>>>>> Would it not be more optimized to store just the user request ?
> >>>>>> Actually, a user is just updating a few column, or inserting a
> >>>>>> tuple by giving some attributes but not all, however as slony
> >>>>>> stores every column with its value it sl_log_x, these tables are
> >>>>>> growing very fast :-(
> >>>>>
> >>>>> Slony only logs columns where the value actually has changed.
> >>>>> Please explain in detail how you propose that the log table would
> >>>>> contain which columns have changed without naming them.
> >>>> when I execute this command on the master :
> >>>>
> >>>> insert into t1(ise) values('cyril100001');
> >>>>
> >>>> I can see in sl_log_1 :
> >>>>
> >>>> psql>select log_cmddata from sl_log_1;
> >>>> (ise,id,id2) values ('cyril100001','24161',NULL)
> >>>>
> >>>> dbtest=3D# \d t1;
> >>>>                                Table "public.t1"
> >>>> Column |         Type          |
> >>>> Modifiers
> >>>>
> --------+-----------------------+----------------------------------------=
---------
> >>>>
> >>>> ise    | character varying(54) | not null
> >>>> id     | integer               | not null default
> >>>> nextval('t1_id_seq'::regclass)
> >>>> id2    | integer               |
> >>>> Indexes:
> >>>>    "t1_pkey" PRIMARY KEY, btree (ise)
> >>>> Triggers:
> >>>>    "_CLUSTER1_logtrigger_1" AFTER INSERT OR DELETE OR UPDATE ON t1
> >>>> FOR EACH ROW EXECUTE PROCEDURE "_CLUSTER1".logtrigger('_CLUSTER1',
> >>>> '1', 'kvv')
> >>>>
> >>>> I agree for id which is an auto_increment but not for id2 :-(
> >>>>
> >>>> In another plateform my table has more than  100 columns as said
> >>>> earlier and that's really matter :-(
> >>> Jan's request seems to remain perfectly good...
> >>>
> >>> "Please explain in detail how you propose that the log table would
> >>> contain which columns have changed without naming them."
> >>>
> >>> It is *not* obvious how to avoid naming all of the columns.  It is
> >>> not safe to simply assume "that column was null so we may omit it" -
> >>> different nodes may be configured differently, and it may well be
> >>> important to actually have that NULL value.
> >> I was just thinking of using the same way as other statement based
> >> replication way like does mysql, that is to say just log the request
> >> that user did. However, I forgot that slony uses triggers to log
> >> requests :-( Maybe finding a way to know which current request is
> >> done on the table for which the trigger has been called would be
> >> possible ?
> >
> > Turning Slony into a statement based replication system would make it
> > far worse.
> You may be right. It was just a suggestion to do something to replace
> the long SQL string by a shorter one to gain space, and so performance
> when getting blocks from disks with more rows :-)
> > Queries depend on the exact transaction commit order with respect to
> > the exact rows that are visible to them. That in addition to the
> > non-deterministic behavior of sequential scans and a myriad of
> > functions ... how do you expect some query like
> >
> >     update mytable set foo =3D nextval('someseq') where foo is null;
> >
> > to lead to exactly the same result on two different databases? Not
> > even the order in which the rows are processed is defined.
> If MySQL support it , why slony would not ?
> (
> http://dev.mysql.com/doc/refman/5.1/en/replication-features-autoincid.html
> ),
> but you right for deterministic functions it's hard if value is added in
> the statement. But it's not what I've suggested.


Well, unlike MySQL our top priority is that it be correct. That's why. If
you want fast, and don't mind sloppy, then perhaps you should investigate
MySQL and their replication solution.

> Compared to Postgres, MySQL is a fairly limited system feature wise.
> > Yet with their limited functionality the MySQL user manual already
> > states that several features are not supported by their statement
> > based replication. What do you think the corresponding documentation
> > for Slony would look like?
> Sorry, but you can compare it to the log shipping feature of slon with
> less lag time.


Slony log shipping is extremely different from MySQL's approach. Slony log
shipping is _NOT_ statement based replication. Like all the rest of slony,
it is data driven. The fact that the data is represented as SQL statements
does not alter the fundamental design. If you would like a clear example of
this, consider a table with 10 entries. On the origin UPDATE foo SET bar =
=3D
1;. With an SBR solution, that command will be transmitted verbatim or very
close to verbatim. With slony log shipping you will see 10 commands that
look like UPDATE foo SET bar =3D 1 WHERE foo_id =3D ?.

I'm not exactly clear what you're suggesting, but perhaps this will clarify
things enough that you can express your idea.

Andrew
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070910/=
8bc456c0/attachment-0001.htm