Thanks Tim, this helped me a lot.
Martin
Tim Hawkins napsal(a):
If you are looking at future expansion then the separate DB per blog is
defiantly the way to go.
Some notes:
1) Avoid joins like the plague, in fact most operations on a blog
application would consist of getting primary record and then decorating
it with secondary data, for example getting a blog post and then getting
the comments associated with it. Do two queries for this, dont join them.
2) Consider putting some intelligence into the database selection, have
a master table/db that holds the database/server details for each blog,
It will allow you to spread your blog application across multiple DB
servers, as load goes up you can reassign blogs to different machines.
You can memcache the data from the master db on a per blog basis, so you
wont take a hit on accessing it, but it gives you great flexibility of
redistributing data to different machines/clusters.
3) Use memcache, its a life saver.
4) If user registrations are common across all blogs, have a separate db
for the users, again you can "shard" this, use a hashing algorithm to
allow sections of the user database to be split across multiple user
databases on multiple db servers. Again memcache the hell out of the
user lookup, its a fixed id=>db/datarecord mapping so its great for
using memcache against as the mapping never changes.
5) Use an external indexer for any search functionality such as sphinx
(http://www.sphinxsearch.com/), sphinx can index separate databases and
join the indices together to form a single distributed search, it also
supports incremental indexing. Dont be tempted to use the mysql query
system for searches.
On 28 Sep 2008, at 00:22, Jack van Zanen wrote:
If it were Oracle I'd go with one database and separate schema for each
blog.
For Mysql I think I'd go for a database each blog.
Jack
2008/9/28 Martin Zvarík <mzvarik@xxxxxxxxx>
Hi,
I am working on a blog system and I am currently thinking of what
would be
the best DB approach.
I have read lots about wordpress and other blog's optimizations and DB
structure, but I have not found any mention of having separate
database for
each blog/user.
So, my question is, which one is performance better (talking about 1000
blogs):
a) 1000 blogs * 5 (let's say we will have tables like comments,
post... for
each blog) = 5000 tables in one database
... this is Wordpress default
b) 1000 databases (for each blog) each having 5 tables
c) 5 databases by 1000 tables - in this case, won't this be an issue
when
SELECTing like this: [db_comments].testblog, [db_posts].testblog ?
Is that a controversial topic? :-/
Thanks for ideas,
Martin
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
J.A. van Zanen
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
