Hello,
Clearly, I shouldn't actually use these transactions unless I have to, and
in cases where I do use it, I'd expect the completion of the transaction to
depend on the speed of all participating databases in the transaction, but
are there any additional overheads which might come with a significant time
penalty that might commonly be overlooked by someone like me with no
previous experience with two-phase commit (2PC)?
---
The application:
I'm evaluating a design for a database scheme where the nation is
partitioned into small areas (e.g. 2km squares), each area serviced solely
by its own dedicated database.
All queries are locally pinpointed, with a limited search radius, and the
database enclosing the centre is responsible for executing the query.
The only issue is to ensure that a query near a boundary between two
adjacent areas behaves as though there was no partitioning. To do this, I'm
looking into using 8.1's new 2PC to allow me to selectively copy data
inserted near a boundary into the adjacent neighbouring databases, so that
this data will appear in boundary searches carried out by the neighbours.
The percentage of inserts which are copied into neighbours is intended to be
roughly 25%, most of which involve just a single copy.
My scheme intends to ensure that all the databases are able to fit entirely
in RAM, and in addition, the amount of data in each database will be
relatively small (and therefore quick to sift through). Inserted data is
'small', and most of the time, each database is servicing read requests
rather than writing, updating or inserting.
A single nationwide database would be logically simpler, but in my case, the
application is a website, and I want a hardware solution that is cheap to
start with, easily extensible, allows a close coupling between the apache
server responsible for a region and the database it hits.
Any insights gratefully received!
Andy Ballingall