Tags

, , ,

In a previous post, I wrote an overview about Spider for MySQL with its advantages and disadvantages. Now I’ll go through a simple example demonstrating how to implement Spider for MySQL.

System information:

MySQL instances information (shards):

Spider instance information:

Note:

More information on how to install tar-ball binaries can be checked out here.

Testing steps:

  1. Install MySQL server (Oracle binaries) on the instances mysqla, mysqlb and mysqlc.
  2. Install MySQL server (Spider binaries) on the spider_node.
  3. Load the spider plugin on the spider_node by the following SQL command:
    shell> mysql < /$mysql_basedir/share/install_spider.sql"
  4. Check if the spider SE is now available or not:
    SQL> SHOW ENGINES;
    +--------------------+---------+---------------------------------------------
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +--------------------+---------+---------------------------------------------
    | SPIDER | YES | Spider storage engine | YES | YES | NO |
    | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
    +--------------------+---------+---------------------------------------------
  5. Create database called “spider_db” on the 4 MySQL instances (shards and spider):
    SQL> CREATE DATABASE spider_db;
  6. Create a testing table called – let’s say – sp_test in the spider_db database to store 1 million records in each shard as follows:
    On mysqla:
    SQL> CREATE TABLE spider_db.sp_test
    (id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
    PARTITION BY RANGE (id)
    (
    PARTITION p0 VALUES LESS THAN (200000),
    PARTITION p1 VALUES LESS THAN(400000),
    PARTITION p2 VALUES LESS THAN(600000),
    PARTITION p3 VALUES LESS THAN(800000),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
    );
    On mysqlb:
    SQL> CREATE TABLE spider_db.sp_test
    (id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
    PARTITION BY RANGE (id)
    (
    PARTITION p0 VALUES LESS THAN (1200000),
    PARTITION p1 VALUES LESS THAN(1400000),
    PARTITION p2 VALUES LESS THAN(1600000),
    PARTITION p3 VALUES LESS THAN(1800000),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
    );
    On mysqlc:
    SQL> CREATE TABLE spider_db.sp_test
    (id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
    PARTITION BY RANGE (id)
    (
    PARTITION p0 VALUES LESS THAN (2200000),
    PARTITION p1 VALUES LESS THAN(2400000),
    PARTITION p2 VALUES LESS THAN(2600000),
    PARTITION p3 VALUES LESS THAN(2800000),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
    );
  7. Create a MySQL user to be used by the spider storage engine on the shards:
    SQL> GRANT ALL ON *.* TO 'sp_user'@'192.168.56.50' IDENTIFIED BY 'T3$T';
  8. Create the same table on the spider_node using the Spider SE to access the 3 million rows from the shards as follows:
    SQL> CREATE TABLE spider_db.sp_test
    (id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
    ENGINE=Spider
    connection 'table"sp_test",database"spider_db",user"sp_user",password"T3$T",port"3306"'
    PARTITION BY RANGE (id)
    (
    PARTITION p0 VALUES LESS THAN(1000000)COMMENT 'host "192.168.56.51"',
    PARTITION p1 VALUES LESS THAN(2000000)COMMENT 'host "192.168.56.52"',
    PARTITION p2 VALUES LESS THAN(MAXVALUE)COMMENT 'host "192.168.56.53"'
    );

Now, you can manage the tables (select, insert, update, … etc) on the shards through the spider node.
Give it a try and have fun with Spider!

Note:

Although some companies use Spider for MySQL in production systems but – at the time of writing this post – it is NOT production ready yet.

Advertisement