At 04:30 AM 2/10/2009, you wrote:
Thanks for your comments Mike.
The largest table contains 48 columns (objects), the second largest 20
columns (users) and all the rest are less than 10 columns. The instance
sizes range from 10MB to 1GB.
Transactions and row locking are required. Most queries are updates,
followed by writes, then reads (application mostly uses memcached and other
forms of caching for reads).
I have since thought of having 1 table type per database, resulting in
'only' ~30 databases; this would be 'easier' to maintain, and each database
(containing 1 table type) could be optimised for its ratio of reading :
writing : updating.
However, this approach would require a LOT of work to re-write the
application's database layer.
What approach would be best?
Michael,
Does the saying "between a rock and a hard place" sound
familiar? :-)
I feel you're going to have to create a test suite to benchmark both
solutions thoroughly before you start on the application code. You're going
to find pro's and con's with both designs but after benchmarking you're
going to know which one performs better both from a speed viewpoint and
maintenance viewpoint. The more time you spend testing the design, the more
confidence you'll have that it works and the less chance of throwing it
away and starting over later on down the road. Then you'll also be able to
present to your client some hard facts about each design.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
