On 6/29/07, Christopher Browne <[EMAIL PROTECTED]> wrote:
Jeff Davis <[EMAIL PROTECTED]> 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... [EMAIL PROTECTED]: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 [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
