[wp-hackers] WPM(U|S) and table partitions

[Adam Backstrom]

Have you looked into HyperDB yet? It's an Automattic thing.

http://svn.wp-plugins.org/hyperdb/trunk/

<http://svn.wp-plugins.org/hyperdb/trunk/>I'm no expert on HyperDB (we are
still in the "planning on switching" stage) but I see a
get_ds_part_from_table()
function that handles the advertised support for partitioning. If I had to
guess, I would say you need to write a query filter to determine if you're
querying on one of your custom partitioned tables, and then modify the
incoming table name to match the expected partitioned table name syntax.
Others (or The Google) may have a more authoritative answer.

On Mon, Apr 5, 2010 at 19:19, Elizabeth Buckwalter <
elizabeth at slatetechpdx.com> wrote:

> Has anyone explored table partitioning with wordpress?  I've Googled and
> searched these archives, but I haven't found a reference.  I've been
> reading
> up on MySQLs table partitioning (
> http://dev.mysql.com/doc/refman/5.1/en/partitioning.html) after my husband
> says that his company uses it with their Oracle servers.
>
> I understand that for sites like wordpress.com and edublogs, having a
> separation of content is really important.  Not to mention that since what
> they have works fine, there's no reason to switch.  The site we're
> developing for my client is not for users to make blogs but for managing
> multiple sites.  So far wordpress mu has worked great.  Unfortunately, I've
> been running into various foreach($blog_id) $wpdb->get_results('wp_' .
> $blog_id . '_tablename'); issues. And having to create statistic tables
> just
> to be able to do otherwise simple joings.  It seems that with proper
> partitions that this would become less of an issue.
>
> I was thinking a basic range partition on blog_id for most tables, with a
> subpartition on date (month or year seeing how traffic goes) for comments
> and posts.  I have various plugins that would use the blog_id partition as
> well.
>
> Categories makes for an interesting design problem.  There exists already,
> wp_sitecategories, and the ids are the same ids used in wp_$blog_id_terms.
>  I have some ideas, but nothing concrete yet.
>
> From what I've seen of the internals, the table data is pretty abstracted.
>  With some if statements and reg exp, I think I can just add a ' WHERE
> blog_id=' . $blog_id to the query functions in the db class.  I'd allow for
> an 'all' => true or 'blog_id' => array(1..n) argument for easy sitewide
> searches.
>
> I'm still relatively new to Wordpress hacking, so I'd really love to get
> your opinion.  Are people interested in this as a contribution or option?
> Do
> you think wordpress's data abstraction is good enough to handle this?
>  There
> doesn't seem to be any performance related data with regards to Wordpress
> specifically, so if you are interested in performance data, what statements
> would you like me to run?
>
> (We have 60 blogs, with 40,000 posts with 2 or 3 more blogs with 100-200
> posts each a day.  I will be transferring that data to the test db to play
> around with.  I won't be activating any plugins at first.  I have a custom
> plugin with a bunch of user statistics data that I will then turn on and
> test)
>
> --
> Thanks,
> Elizabeth Buckwalter
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>



-- 
Adam Backstrom
adam at sixohthree.com