[Info-ingres] Re: Ingres/Replicator Stress Test
- From: martin.bowes@xxxxxxxxxxxxx
- Date: Thu, 24 Nov 2005 11:54:38 -0000
Hi Everyone,
I think the second row was caused by the update collision - although
given that I'm the only one writing to the backup database, I'm still not
certain why there should have been a collision problem. I'm running
with LastWriteWins collision mode, I suspect that whats occurred is the
Replicator Server got its order wrong, and replicated an update that
didn't match the data in the destination table. So having detected the
collision it just wrote a new record and ploughed on.
I note the update procedure should return error 1227 if the update is
unsuccessful, but my Update Collision record seems to have a 1392
error code. I cant find that error in the remote update procedure or any
other for that matter.
I'd selected the LastWriteWins on the grounds that it had an automatic
resolution of collisions and it seemed like a good place to start a trial.
I'm replicating Peer-to-Peer as I wanted the replicated database to be
immediately available, and to be usable in reverse should the worst
happen so I thought it reasonable to have some collision handling - but
didn't expect any as the target database should never be being used
whilst the source is live.
The replication of a single row sequential counter field is probably not
the best idea. Its given me the most problem with deadlocks and
associated problems with database procedures.
But I've got to replicate what I have with minimal disruption to the
structures and software already deployed. Even if Roy Hann is perfectly
right when he maintains sequential keys are evil, they have a certain
inertia that is hard to budge.
The deadlocking is caused by the users updates to a base table
requiring corresponding entries in the shadow and archive tables.
However, the replication server needs to scan the shadow table and
then go back to the base table to get all the data for the row being
replicated to fill in the parameters to the update procedure its about to
execute on the remote database. Its almost inevitable that the lock
contention will deadlock at this point.
Even more fun is if the Replicator Server can't find the data in the base
table as its been altered by some other process. It then has to find the
correct entry in the Archive table.
The only hope is to analyse the data flow, determine an appropriate
hash table minpages to set and then pray. I suspect partitioned tables
may be useful in many cases here. But it seems like a lot of hassle for
a single row table!
The single row sequence counter table has a few other tricks as well.
To be replicated we need a unique key. The temptation is to create a
table with a key field and the counter. The key field is a constant.
The trouble is that the shadow table then has an index of potentially
thousands of entries - all with exactly the same key. Not useful.
Plus the deadlock contention is between user updates and the
Replicator Server is now totally unavoidable as the data is always on
the same page.
The only easy way out of this is to make the single row table a hash
with a seriously large minpages keyed on the incrementing sequence
counter.
I'm beginning to think that my simple stress test has already answered
the question on an Ingres/Replicator deployment.
Does anyone have any comments/advice? Be Nice!
Martin Bowes
--
Random Titus Quote #4:
It's always funny until someone gets hurt. And then its funnier.
.
- Follow-Ups:
- Re: Ingres/Replicator Stress Test
- From: denjo02
- Re: Ingres/Replicator Stress Test
- Prev by Date: RE: [Info-ingres] abf 3gl function
- Next by Date: Re: [Info-ingres] Ingres/STAR, ING_SYSTEM_SET and set lockmode
- Previous by thread: [Info-ingres] Ingres/Replicator Stress Test
- Next by thread: Re: Ingres/Replicator Stress Test
- Index(es):
Relevant Pages
|
