[HyperDB] Partitioning wpmu data

[Felix Gushansky]

Hm, but the add_db_server (a function in db.php) is being called from
db-settings.php, which I think *won't* get executed unless I require it in
the wp-config, no? That add_db_server call carries the info about my (in
this case default) database.

Thanks,
-- Felix

> From: Callum Macdonald <lists.automattic.com at callum-macdonald.com>
> Reply-To: <hyperdb at lists.automattic.com>
> Date: Sun, 26 Apr 2009 19:18:14 -0700
> To: <hyperdb at lists.automattic.com>
> Subject: Re: [HyperDB] Partitioning wpmu data
> 
> I don't think you need to do add any require() calls. WordPress will
> automatically pick up a file called db.php in wp-content.
> 
> As far as I'm aware, you can drop in the HyperDB class and it will
> automatically fall back on the default db details, in effect changing
> almost nothing.
> 
> Cheers - Callum.
> 
> On Fri, 2009-04-24 at 11:01 -0700, Felix Gushansky wrote:
>> Hi, folks. Before getting into the depths of db partitioning I am having
>> trouble making the thing to work with just my default db. Seemingly the
>> simplest thing in the world: added define and require lines to wp-config (at
>> the top), dropped in db-settings.php, uncommented the default add_db_server
>> ('global') line in it, made sure all variables are defined and good, dropped
>> in db.php in the wp-content dir -- just like prescribed. Croaking in the
>> wpmu-settings.php - I think 'SELECT * FROM sites' does not give back the
>> data resulting in 'count( $sites )' to be equal to 1 -- which is what seems
>> to be expected. As soon as I disable the plugin (commenting out the define
>> and the require lines in the wp-config.php) everything is back to normal.
>> 
>> Any ideas what could be wrong?
>> 
>> Thanks,
>> -- Felix
>> 
>> 
>>> From: Callum Macdonald <lists.automattic.com at callum-macdonald.com>
>>> Reply-To: <hyperdb at lists.automattic.com>
>>> Date: Thu, 23 Apr 2009 11:57:29 -0700
>>> To: <hyperdb at lists.automattic.com>
>>> Subject: Re: [HyperDB] Partitioning wpmu data
>>> 
>>> As Andy said, HyperDB is about on the money for what you want.
>>> 
>>> The biggest challenge you'll face will be moving the data. You have 11k
>>> tables in one database right now, you need to move those to new
>>> databases. The MultiDB class uses the same method as HyperDB did
>>> originally, 4096 databases, first 3 digits of the md5 of the blog_id
>>> (the NNN number).
>>> 
>>> I think your best bet will be to use HyperDB and create your own system
>>> for managing which blog is in which database (maybe a lookup table like
>>> Andy mentioned). That way you'll be able to migrate the data over time.
>>> 
>>> Other options that occur to me:
>>> 
>>> Migrate all the data at once. Take the whole system down for a few hours
>>> and move all the tables as you need to. Pray nothing goes wrong. :-)
>>> 
>>> You could add some logic that checks if the table is found in the new
>>> target database, and if not, fall back on the global database. That
>>> would allow you to move blogs one at a time, without changing any code.
>>> If the blog tables are in the new database, the queries go there, if
>>> not, the queries go to the master table.
>>> 
>>> If you're looking for help, you could speak to the guys at Incsub. I'm a
>>> (present and about to be) retired staffer there, so I'm biased. :-)
>>> 
>>> Best of luck with the project.
>>> 
>>> Cheers - Callum.
>>> 
>>> On Thu, 2009-04-23 at 10:32 -0700, Felix Gushansky wrote:
>>>> Hello, folks. We have just inherited the site that uses wordpress MU (ver
>>>> 2.5.1) with MySQL 5 on the back. Soon we have learned that the blog db is
>>>> huge. Not size wise necessarily (it is slightly over a gig), but table
>>>> wise.
>>>> That db contains over 11,000 tables with the total of over 40,000 files in
>>>> the same directory. That hugely affects performance AND maintenance ­ to
>>>> the
>>>> point in fact that we can not backup the db using mysqldump. We had to
>>>> write
>>>> a script dumping a table at a time.
>>>> 
>>>> So, we need to scale somehow. That¹s why he have looked at HyperDB, which
>>>> seems to have been built just for that. However, the problem we are seeing
>>>> is that in our case the tables in the blog db are created and named
>>>> dynamically. For each new user wpmu creates multiple tables (8?) in the
>>>> database and names them like wp_<nnnn>_comments, wp_<nnnn>_links,
>>>> wp_<nnnn>_options, wp_<nnnn>_postmeta, wp_<nnnn>_posts, etc, where <nnnn>
>>>> is
>>>> a four digit integer. Can we take advantage of HyperDB in this case? And if
>>>> not what are our options?
>>>> 
>>>> Thanks very much folks. Your help is very much appreciated.
>>>> 
>>>> -- Felix
>>>> _______________________________________________
>>>> HyperDB mailing list
>>>> HyperDB at lists.automattic.com
>>>> http://lists.automattic.com/mailman/listinfo/hyperdb
>>> 
>>> _______________________________________________
>>> HyperDB mailing list
>>> HyperDB at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/hyperdb
>> 
>> _______________________________________________
>> HyperDB mailing list
>> HyperDB at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/hyperdb
> 
> _______________________________________________
> HyperDB mailing list
> HyperDB at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/hyperdb