Spider Storage Engine Overview
MariaDB starting with 10.0.4
The Spider storage engine was introduced in MariaDB 10.0.4
Contents
About
The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance.
When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server.
When more than one Spider storage engine table can be used by the same connection, the connection is shared.
The Spider documentation on the MariaDB Knowledge Base is currently incomplete. See the Spider website for more: http://spiderformysql.com/, as well as the spider-1.0-doc and spider-2.0-doc repositories.
Installing
To install Spider,run the install_spider.sql script, located in the share
directory, for example, from the command line:
mysql -uroot -p < /usr/share/mysql/install_spider.sql
or, from within mysql
source /usr/share/mysql/install_spider.sql
SPIDER should now appear as a supported storage engine:
SELECT engine, support, transactions, xa FROM information_schema.engines; +--------------------+---------+--------------+------+ | engine | support | transactions | xa | +--------------------+---------+--------------+------+ | SPIDER | YES | YES | YES | | CSV | YES | NO | NO | | MyISAM | YES | NO | NO | | BLACKHOLE | YES | NO | NO | | FEDERATED | YES | YES | NO | | MRG_MyISAM | YES | NO | NO | | ARCHIVE | YES | NO | NO | | MEMORY | YES | NO | NO | | PERFORMANCE_SCHEMA | YES | NO | NO | | Aria | YES | NO | NO | | InnoDB | DEFAULT | YES | YES | +--------------------+---------+--------------+------+
The script installs a number of new tables inside the mysql
database, including:
- spider_link_failed_log
- spider_link_mon_servers
- spider_tables
- spider_xa
- spider_xa_failed_log
- spider_xa_member
The script will also update the format of the tables if upgrading from an earlier version.
Usage
Preparing 10M record table using the sysbench utility
/usr/local/skysql/sysbench/bin/sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.202 --mysql-port=5054 --oltp-table-size=10000000 --mysql-db=test prepare
Make a first read only benchmark to check the initial single node performance.
/usr/local/skysql/sysbench/bin/sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.202 --mysql-port=5054 --mysql-db=test --oltp-table-size=10000000 --num-threads=4 --max-requests=100000 --oltp-read-only=on run
sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 4 Doing OLTP test. Running mixed OLTP test Doing read-only test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Maximum number of requests for OLTP test is limited to 100000 Threads started! Done. OLTP test statistics: queries performed: read: 1400196 write: 0 other: 200028 total: 1600224 transactions: 100014 (1095.83 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1400196 (15341.58 per sec.) other operations: 200028 (2191.65 per sec.) Test execution summary: total time: 91.2681s total number of events: 100014 total time taken by event execution: 364.3693 per-request statistics: min: 1.85ms avg: 3.64ms max: 30.70ms approx. 95 percentile: 4.66ms Threads fairness: events (avg/stddev): 25003.5000/84.78 execution time (avg/stddev): 91.0923/0.00
Define a easy way to access the nodes from the MariaDB or MySQL client.
alias backend1='/usr/local/skysql/mysql-client/bin/mysql --user=skysql --password=skyvodka --host=192.168.0.202 --port=5054' alias backend2='/usr/local/skysql/mysql-client/bin/mysql --user=skysql --password=skyvodka --host=192.168.0.203 --port=5054' alias spider1='/usr/local/skysql/mysql-client/bin/mysql --user=skysql --password=skyvodka --host=192.168.0.201 --port=5054'
Create the empty tables to hold the data and repeat for all available backend nodes.
backend1 << EOF CREATE DATABASE backend; CREATE TABLE backend.sbtest ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k (k) ) ENGINE=InnoDB; EOF backend2 << EOF CREATE DATABASE backend; CREATE TABLE backend.sbtest ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k (k) ) ENGINE=InnoDB; EOF
We will fist use the following setup.
spider1 << EOF CREATE SERVER backend FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.0.202', DATABASE 'test', USER 'skysql', PASSWORD 'skyvodka', PORT 5054 ); CREATE TABLE test.sbtest ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k (k) ) ENGINE=spider COMMENT='wrapper "mysql",srv "backend"'; SELECT * FROM test.sbtest LIMIT 10; EOF
We will now create the following shading setup.
Create the spider table on the Spider Node
spider1 << EOF CREATE SERVER backend1 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.0.202', DATABASE 'backend', USER 'skysql', PASSWORD 'skyvodka', PORT 5054 ); CREATE SERVER backend2 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.0.203', DATABASE 'backend', USER 'skysql', PASSWORD 'skyvodka', PORT 5054 ); CREATE DATABASE IF NOT EXISTS backend; CREATE TABLE backend.sbtest ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k (k) ) ENGINE=spider COMMENT='wrapper "mysql", table "sbtest"' PARTITION BY KEY (id) ( PARTITION pt1 COMMENT = 'srv "backend1"', PARTITION pt2 COMMENT = 'srv "backend2"' ) ; EOF
Copy the data from the original sysbench table to the spider table
/usr/local/skysql/mariadb/bin/mysqldump --user=skysql --password=skyvodka --host=192.168.0.202 --port=5054 --no-create-info test sbtest | spider1 backend backend2 -e"select count(*) from backend.sbtest;" +----------+ | count(*) | +----------+ | 3793316 | +----------+ root@node1:~# backend1 -e"select count(*) from backend.sbtest;" +----------+ | count(*) | +----------+ | 6206684 | +----------+
We observe a common issue with partitioning is a non uniform distribution of data between the backends. based on the partition key hashing algorithm.
Re run the Benchmark with less queries
/usr/local/skysql/sysbench/bin/sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.201 --mysql-port=5054 --mysql-db=backend --mysql-engine-trx=yes --oltp-table-size=10000000 --num-threads=4 --max-requests=100 --oltp-read-only=on run
OLTP test statistics: queries performed: read: 1414 write: 0 other: 202 total: 1616 transactions: 101 (22.95 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1414 (321.30 per sec.) other operations: 202 (45.90 per sec.) Test execution summary: total time: 4.4009s total number of events: 101 total time taken by event execution: 17.2960 per-request statistics: min: 114.48ms avg: 171.25ms max: 200.98ms approx. 95 percentile: 195.12ms Threads fairness: events (avg/stddev): 25.2500/0.43 execution time (avg/stddev): 4.3240/0.04
As you can see the response time move to 0.04 and 1414 (321.30 per sec.). This is perfectly expected because the query latency is increase from multiple network round trip.
We need to increase the concurrency to get better throughput.
We have no background search available that will be available before MariaDB 10 GA but here is how to optimize with background search .