[HyperDB] Partitioning wpmu data

Felix Gushansky felix.gushansky at internetbrands.com
Thu Apr 30 17:36:33 GMT 2009 

Hey, thanks for the reply, Callum. We actually did progress. Enabling
define('WPMU', true); IS a problem, but if we keep it commented out and only
use require('db-settings.php'); db partitioning works wonderfully. We have
created table name patterns /^00/ - /^99/ and mapped them to the separate
DBs. That spreads the tables just the way we want it.

Now, as far as define('WPMU', true), we¹ve noticed that enabling it exposes
a side problem, the symptom of which is that $wpdb->prefix isn¹t being
properly set. When WPMU is undefined $wpdb->prefix is set properly - to
$table_prefix in wp-config.php - however later on there are other problems,
most likely because afterall the code counts on WPMU to be enabled. If we do
enable it however, as prescribed, the $wpdb->prefix is NOT being properly
set (it is actually set to null), causing bunch of DB queries fail in the
initial stages of execution. We do not yet understand why it happens, but we
are looking. 

I¹ve added the community back to the mailing list, as there maybe something
here useful to others.

Best,
-- Felix


From: Callum Macdonald <me at callum-macdonald.com>
Date: Wed, 29 Apr 2009 23:42:16 -0700
To: Felix Gushansky <felix.gushansky at internetbrands.com>
Subject: Re: [HyperDB] Partitioning wpmu data

Hola,

Just a quick private reply to say I'm not sure what else to suggest. I
didn't think it was worth sending a message round the whole list.

You could try the MultiDB class if you can't get HyperDB to work. I think
you'd need to join the WPMU Dev Premium service, but I'm not 100% sure on
the details.

Cheers - Callum.

On Mon, 2009-04-27 at 10:40 -0700, Felix Gushansky wrote:
> 
> Right, I did this initially, Callum, and here is what I saw:
> 
> - if I exclude *both* the define('WPMU', true); and the
> require('db-settings.php'); then everything works fine. If at that point
> it's already using db.php, as you seem to suggest, then we're in good shape.
> 
> - if in wp-config.php I include one of the two statements - or both - I get
> the error described below. This is unexpected, since, according to readme in
> the package, I should be able to just include define('WPMU', true); and
> everything should work using the default settings. In my case it does not.
> 
> Not sure if it's a symptom of another problem, which could be the reason I
> can not make it work with require('db-settings.php');
> 
> Thanks,
> -- Felix
> 
> 
>> > From: Callum Macdonald <lists.automattic.com at callum-macdonald.com>
>> > Reply-To: <hyperdb at lists.automattic.com>
>> > Date: Mon, 27 Apr 2009 10:19:13 -0700
>> > To: <hyperdb at lists.automattic.com>
>> > Subject: Re: [HyperDB] Partitioning wpmu data
>> > 
>> > Felix, you're absolutely right, you do need to include db-settings.php
>> > in wp-config.php.
>> > 
>> > As I understand it, you don't need to configure the default db settings.
>> > HyperDB will automatically pick up the settings in wp-config.php and use
>> > those. So if you just want to test, install db.php in wp-content/ and
>> > don't include db-settings.php.
>> > 
>> > Then you can try adding db-settings.php and see if it works as you
>> > expect.
>> > 
>> > Cheers - Callum.
>> > 
>> > On Mon, 2009-04-27 at 09:31 -0700, Felix Gushansky wrote:
>>> >> 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
>>> >> 
>>> >> _______________________________________________
>>> >> 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

==
Callum Macdonald

US Cell: +1 585 201 1120
Desk: +44 845 126 0875
Skype: callum.macdonald <callto:callum.macdonald>
www.callum-macdonald.com <http://www.callum-macdonald.com>

More information about the HyperDB mailing list