Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, the system does a bunch of work to translate it into updates, inserts, and deletes. Initials and deltas are processed inside long transactions to maintain data integrity.

Unfortunately the current solution isn't scaling very well. The transactions are so large and long running that our RDBMS bogs down with various contention problems. Also, there isn't a good audit trail for how the deltas are applied, making it difficult to troubleshoot issues causing the local and remote versions of the dataset to get out of sync.

One idea is to not run the initials and deltas in transactions at all, and instead to attach a version number to each record indicating which delta or initial it came from. Once an initial or delta is successfully loaded, the application can be alerted that a new version of the dataset is available.

This just leaves the issue of how exactly to compose a view of a dataset up to a given version from the initial and deltas. (Apple's TimeMachine does something similar, using hard links on the file system to create "view" of a certain point in time.)

Does anyone have experience solving this kind of problem or implementing this particular solution?

Thanks!

share|improve this question
    
What database and version? –  Leigh Riffel Apr 30 '11 at 12:34
    
MySQL 5.1.49-log at the moment. –  dacc May 3 '11 at 1:30
1  
Possibly relevant answer from SO –  Gaius May 3 '11 at 12:47
add comment

1 Answer

up vote 1 down vote accepted

If the "bunch of work to translate it" is occurring during the transaction, then that is adding to the transaction time. If you can do bulk processing on the data to translate it before you start dong inserts/updates/deletes that would at least decrease the transaction time providing fewer opportunities for contention.

An alternative would be to maintain a copy of the table and make your changes on the copy and then during a brief outage window swap the table names. The downside is the added storage and the outage window, but the advantage is zero contention.

Update:

I see a couple difficulties with your solution. Inserts would still cause contention if there were unique indexes on the table. Updates would become inserts, so those wouldn't be any more difficult. Deletes would have to be handled somehow. You could insert a blank record with the delta number to indicate that the record has been deleted. I'm concerned that the view necessary to pull this data together for querying would be too slow.

Perhaps someone with MySQL knowledge could suggest something.

share|improve this answer
    
Thanks for your reply, Leigh. Your first idea definitely applies, but I don't think it will provide the kind of improvement I'm hoping for. The size and length of the transactions seems to be the bottleneck, and not the extra time spent in transaction processing data. The second idea would still cause contention over the copy of the table when trying to load multiple deltas concurrently. What do you think of the solution from my original question? –  dacc May 3 '11 at 1:31
    
Updated answer. –  Leigh Riffel May 3 '11 at 14:13
    
Yeah, I was thinking of a "NULL" record to indicate deletion. Contention over the index might definitely still exist, but given everything will be outside of a transaction it seems like the locking behavior would improve. –  dacc May 3 '11 at 17:02
    
Oh, and regarding the view, it wouldn't have to be a "view" in the strict MySQL sense. It could be a background process actively curating a sort of materialized view in an event driven manner, or simply queries that look at the revision number on each record. –  dacc May 3 '11 at 17:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.