WordPress.org

Ready to get started?Download WordPress

Forums

HyperDB
[resolved] Partitioning table post into several databases (12 posts)

  1. adlbeat
    Member
    Posted 4 months ago #

    Example of simple config of partitioning configuration for a WordPress Satand Alone (normal WordPress Install)

    Example:
    1. database "A" will hold global tables
    2. database "B" will have tables for posts up to ID 1000
    3. database "C" will have tables for posts form ID 1000 to 2000
    4. And so on...

    Please can anyboby post a configuration for this partitioning setup or the keys for doing it myself.

    I need to improve performance of a WordPress powered site wich DB is 4GB size .

    Thanks a lot.

    Sorry my bad-writting english
    http://wordpress.org/extend/plugins/hyperdb/

    http://wordpress.org/plugins/hyperdb/

  2. Alexandru Vornicescu
    Member
    Posted 3 months ago #

    I was searching for this and it seems it is impossible

  3. adlbeat
    Member
    Posted 3 months ago #

    It is possible, it's hard but you can do it, hyperdb configs database partitioning, this doesn't partition an exists db, this recognices more than one db to use with WordPress.

  4. Alexandru Vornicescu
    Member
    Posted 3 months ago #

    Can you please describe or show how it is possible points 2 and 3? :
    2. database "B" will have tables for posts up to ID 1000
    3. database "C" will have tables for posts form ID 1000 to 2000

  5. Rodrigo Primo
    Member
    Posted 2 months ago #

    @adlbeat are you sure it is possible to split wp_posts table into multiple databases? Could you give more details?

    By the way, why this topic is marked as resolved?

  6. adlbeat
    Member
    Posted 2 months ago #

    Alexandru Vornicescu & Rodrigo Primo:

    Yeah it's possible, look, you only need to edit db-config.php
    the db.php class shouldn't be modified. Is marked as solved because i solved it days before.

    I will use spanish because i'm mexican and i wrote it previously in spanish (now i'm usin copy-paste), use a traslator if required.

    I made it while doing scholar practices at a newspaper factory, they use WordPress and were required to split databse because there were a huge DB (4GB DB size)
    This is an example from a distributed database wich has 3 DB, but you can do more.
    1DB for all wp-tables except posts
    2DB just for posts. (

    2. database "B" will have tables for posts up to ID 1000
    3. database "C" will have tables for posts form ID 1000 to 2000

    )

    This is just an example, adapt it to yur case (sorry my bad english)

    /**
    * Considere que 'wp-develop' es la BD maestra, la BD 'wp-develop1' y 'wp-develop2'
    * contienen los posts pero en fragmentos.
    */

    /**
    * Base de datos principal (no posts)
    */

    $wpdb->add_database(array(
    	'host'     => DB_HOST,
    	'user'     => DB_USER,
    	'password' => DB_PASSWORD,
    	'name'     => DB_NAME,
    	/*'dataset'  => 'global',*/
    ));

    /**
    * Base de datos para posts que impide escritura y
    * no prioriza lectura (posts de años o semestres anteriores)
    * Es posible partir ésta BD aun en más.
    */

    $wpdb->add_database(array(
    	'host'     => DB_HOST,
    	'user'     => DB_USER,
    	'password' => DB_PASSWORD,
    	'name'     => 'wp-develop2',
    	'write'	   => 0,
    	'read'	   => 3,
    	'dataset'  => 'news',
    ));

    /**
    * Base de datos para posts a escribir/leer, máxima prioridad
    * en escritura/lectura (año actual o semestre actual)
    */

    $wpdb->add_database(array(
    	'host'     => DB_HOST,
    	'user'     => DB_USER,
    	'password' => DB_PASSWORD,
    	'name'     => 'wp-develop3',
    	'dataset'  => 'news',
    ));

    /**
    * Reconoce tablas "separadas" de la B.D. y las integra
    * En 'News' dataset, donde news son los posts.
    */

    $wpdb->add_table('news', 'wp_posts');
    $wpdb->add_table('news', 'wp_postmeta');

    If u have questions feel free to posts them, i'll help you. Greetings

  7. zerackam
    Member
    Posted 2 months ago #

    @adlbeat did you then split up the database between different servers or still keep the split up parts on one server? also did you see performance improvements?

    I'm having the same exact problem on a Non-Multisite website where the DB is about 4 gigs now with millions of posts and its getting very slow.

  8. Alexandru Vornicescu
    Member
    Posted 2 months ago #

    This code does not split the posts to different tables, it will only store to one database named 'wp-develop3'

  9. Alexandru Vornicescu
    Member
    Posted 2 months ago #

    Look:
    We have created 3 databases,
    1st database will have all tables except wp_posts and wp_postmeta

    2nd database will have nothing because we set him only to read and not to write

    3rd database will have wp_posts and wp_postmeta tables

    So where is the spliting of table wp_posts to different tables???

  10. Alexandru Vornicescu
    Member
    Posted 2 months ago #

    If we will set 2nd database to write then this will be switching between databases.

    So this seems impossible until someone will show me how this is possible!

  11. Rodrigo Primo
    Member
    Posted 2 months ago #

    I'm also under the impression that this code does not split the posts to different tables.

  12. adlbeat
    Member
    Posted 2 months ago #

    zerackam:
    In this case I split the DB in the same server, but you can distribute it on diferent ones, if you chose to do the second options y think is perfect, but splittin' in the same server improve performance, WP uses foreach everywhere and it's slow when the querys are ran on big tables (or BD's).

    Alexandru Vornicescu:
    You are allright, this does not split the database, but recognices a WordPress Distributed databases, in fact u need to split the database, then implement the php class and join all databases.

    We have 3 dtabases:

    1st database will have all tables except wp_posts and wp_postmeta

    (ok)

    2nd database will have nothing because we set him only to read and not to write

    (wrong) -> In this table we have all the new posts stuff, here are the new and fresh post content so this DB is fast.

    3rd database will have wp_posts and wp_postmeta tables

    (OK)
    In here we have oldests post, the old stuff but like this DB you can do more than one, i just used one, but if you need you can work with more DB like this. Here is the performance trick.

    The split is in DB 2 and DB3:
    In DB2 we can have this year's stuff.
    DB3 can be used to store the oldest content (But we can do more DB's like this, one per year or month, etc.).

    The C.R.U.D. in DataBase is distributed in the system so there's no switching beetwen dsatabases

    Rodrigo Primo:
    You're right, this just "joins" the distributed databases, The spliting can be made easily typing some querys using the prompt (Eg. MySQL Shell)

    If you are looking for an automatization of this (Eg. if you want a db for every year auto-generated) you can use cronjobs or write a script wich ask to server the actual date, then runs the script.

    Greeting.

Reply

You must log in to post.

About this Plugin

About this Topic