[HyperDB] Partitioning wpmu data

[Felix Gushansky]

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