[prev in list] [next in list] [prev in thread] [next in thread] 

List:       slony1-general
Subject:    Re: [Slony1-general] Soliciting ideas for v2.0
From:       "Andrew Hammond" <andrew.george.hammond () gmail ! com>
Date:       2007-06-30 6:57:47
Message-ID: 5a0a9d6f0706292357idf07fffpa130c1deb427d08b () mail ! gmail ! com
[Download RAW message or body]

On 6/29/07, Christopher Browne <cbbrowne@mail.libertyrms.com> wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > On Thu, 2007-06-28 at 18:17 -0400, Christopher Browne wrote:
> > > Are there more items we should try to add?
> > 
> > Is there a good way to make it more efficient to do large
> > deletes/updates?
> > 
> > For instance, if the number of tuples that need to be deleted for a
> > transaction exceeds a certain amount, could we use a different process
> > for the delete on the subscriber so that it doesn't do millions of
> > single-tuple deletes?
> > 
> > I don't know exactly how that would work, perhaps by using a temporary
> > table on the subscriber and doing a single "DELETE FROM foo WHERE id IN
> > (SELECT id FROM foo_delete_tmp)" or something?
> 
> Ah, yes.
> 
> It would be nontrivial to improve this in the case of a multi-column
> key, but if the candidate primary key consists of a single column, one
> might be able to detect this.
> 
> Here's a sample, of sorts...
> 
> slonyregress1@[local]:5834=# select * from _slony_regress1.sl_log_1 where \
> log_cmdtype = 'D' order by log_actionseq limit 10; log_origin | log_xid | \
>                 log_tableid | log_actionseq | log_cmdtype | log_cmddata
> ------------+---------+-------------+---------------+-------------+-------------
> 1 | 3939096 |           2 |          9277 | D           | id='1'
> 1 | 3939096 |           1 |          9278 | D           | id='1'
> 1 | 3939096 |           2 |          9279 | D           | id='2'
> 1 | 3939096 |           1 |          9280 | D           | id='2'
> 1 | 3939096 |           2 |          9281 | D           | id='3'
> 1 | 3939096 |           2 |          9282 | D           | id='4'
> 1 | 3939096 |           2 |          9283 | D           | id='6'
> 1 | 3939096 |           2 |          9284 | D           | id='9'
> 1 | 3939096 |           2 |          9285 | D           | id='13'
> 1 | 3939096 |           2 |          9286 | D           | id='18'
> (10 rows)
> 
> This would normally get turned into:
> 
> delete from table2 where id = '1';
> delete from table1 where id = '1';
> delete from table2 where id = '2';
> delete from table1 where id = '2';
> ... and so forth ...
> 
> It should, in principle, be possible to group together requests for one table, so \
> that we could have the following: 
> delete from table2 where
> id = '3'
> or
> id = '4'
> or
> id = '6'
> or
> id = '9'
> or
> id = '13'
> or
> id = '18';
> 
> where this groups together the consecutive entries for table #2.
> 
> This actually still works for multiple-column PKs as long as we put
> parentheses around each "log_cmddata" entry.  The win isn't in turning
> this into a parser-challengingly-huge single query; it is in doing
> several entries as one query, so I'd be inclined to let it start a new
> DELETE request any time the size of the query reaches 100 tuples.

A really interesting win would be in detecting cases where you can go from

WHERE id IN ( a list )

to

WHERE a < id AND id < b

However I think this is only possible at the time the transaction
happens (how else will you know if your sequence is contigious. And
that suggests to me that it's not reasonable to do at this time.

Also, ISTM that the big reason we don't like statement based
replication is that SQL has many non-deterministic aspects. However,
there is probably a pretty darn big subset of SQL which is provably
non-deterministic. And for that subset, would it be any less rigorous
to transmit those statements than to transmit the per-row change
statments like we currently do?

> It would take some parsing of the log_cmddata to do this, nonetheless,
> I think it ought to be possible to compress this into some smaller
> number of queries.  Again, if we limited each query to process 100
> tuples, at most, that would still seem like enough to call it a "win."

I can see two places to find these wins. When the statement is parsed
(probably very affordable) and, as you mentioned above, by inspecting
the log tables. I think that we'd have to be pretty clever with the
log tables to avoid having it get too expensive. I wonder if full text
indexing with an "sql stemmer" might be clever way to index that data
usefully.

Two downsides of the parser approach that I can see are
1) the postgresql parser / planner is already plenty complex
2) it doesn't group stuff across multiple statements

Just some thoughts.

Andrew
_______________________________________________
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general


[prev in list] [next in list] [prev in thread] [next in thread]