[Typo3-dev] RfC: Making DBAL more flexible and scalable

[Michael Scharkow]

Hi *,

I've been playing with DBAL recently, and would like to discuss two 
features I'd like to see in 1.0.

FEATURE REQUEST: Seperate db configuration for read and write queries.

Karsten and Kasper might already have planned such a feature, as 
todo.txt says "- readOnly control on tables/handlers" so this post might 
can be understood as a reminder.

Imagine the following setup:

2 (or more) identical TYPO3-Webservers[1] w/ shared storage
1 DB-Server (MySQL or PostGres)

Chances are that by becoming popular, the DB server cannot handle the 
load, what now:

The most simple solution, db replication, does not work in this setup 
until now because of sync issues. TYPO3 currently needs master-master 
replication in order to work.
Master-master replication is horrible with MySQL (you could try the 
cluster but it has some obvious drawbacks, like RAM requirements), and 
entirely impossible with Postgres.

IN SHORT:
Since we're abstracting from DB calls anyway, I propose not only 
separating configuration on a per-table basis, but as well for 
read/write queries. With this, you can setup a simple transparent 
replication mechanism that directs all INSERT/UPDATE/DELETE statements 
to the master DB and all SELECT queries to a cluster of slaves, thus 
saving a lot of expensive operations on the master while maintaining 
consistency and scalability.
Master-slave replication is extremely simple with all major open source 
DBs, as is loadbalancing with round-robin-dns.

If the master DB cannot even handle the write queries, you can 
*additionally* move tables to other servers with current DBAL. This 
should give you enough power for most situations (if write queries to 
*one* table still torture your DB, you're admittedly screwed.)

Okay, as a minor point: Why not use the query caching built into adodb 
by using CachedExecute() instead of Execute() for SELECT queries. 
Wouldn't this speed up common operations like displaying a simple page?

Greetings,
Michael



[1]serving *highly* dynamic and personalized content (so mod_proxy+cache 
control headers do not help)