Spider Cluster Management
Contents
Direct SQL
Direct SQL is a way to map reduced execution on remote backend and store the result in a local table. This can be either sequential, using the UDF function spider_direct_sql
, or concurrently using spider_bg_direct_sql
.
MariaDB starting with 10.0.4
The Spider storage engine was introduced in MariaDB 10.0.4, Direct SQL is reported to not work in the first releases. Iit is expected to be fixed in MariaDB 10.0.8. Ask a SkySQL representative for the available patch.
spider1 backend << EOF CREATE TEMPORARY TABLE res ( id int(10) unsigned NOT NULL, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '' ) ENGINE=MEMORY; SELECT spider_direct_sql( 'SELECT * FROM sbtest s WHERE s.id IN(10,12,13)', 'res', concat('host "', host, '", port "', port, '", user "', username, '", password "', password, '", database "', tgt_db_name, '"') ) a FROM mysql.spider_tables WHERE db_name = 'backend' and table_name like 'sbtest#P#pt%'; SELECT * FROM res; EOF
Or if you are using a SERVER
SELECT spider_direct_sql( 'SELECT * FROM sbtest s WHERE s.id IN(10,12,13)', 'res', concat('server "', server, '"') ) a FROM mysql.spider_tables WHERE db_name = 'backend' and table_name like 'sbtest#P#pt%' ;
Direct Handler Socket
Check that Handler Socket is running on the backend nodes
:~# backend2 -e "show variables like 'handler%'" +-------------------------------+---------------+ | Variable_name | Value | +-------------------------------+---------------+ | handlersocket_accept_balance | 0 | | handlersocket_address | 192.168.0.201 | | handlersocket_backlog | 32768 | | handlersocket_epoll | 1 | | handlersocket_plain_secret | | | handlersocket_plain_secret_wr | | | handlersocket_port | 20500 | | handlersocket_port_wr | 20501 | | handlersocket_rcvbuf | 0 | | handlersocket_readsize | 0 | | handlersocket_sndbuf | 0 | | handlersocket_threads | 4 | | handlersocket_threads_wr | 1 | | handlersocket_timeout | 300 | | handlersocket_verbose | 10 | | handlersocket_wrlock_timeout | 12 | +-------------------------------+---------------+
spider1 backend << EOF CREATE TEMPORARY TABLE res ( id int(10) unsigned NOT NULL, k int(10) unsigned NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '' ) ENGINE=MEMORY; SELECT spider_direct_sql('1\t=\t1\t2\t100000\t0','res', 'host "192.168.0.202", table "sbtest", database "test", port "20500", access_mode "1"');
Inter Nodes Copy Table
The UDF function spider_copy_tables
is available for copying table data from the source link ID to the destination link ID list without stopping your service for copying
spider_copy_tables(Spider table name, source link ID, destination link ID list[, parameters])
Returns 1
if copying data succeeded.Returns 0
if copying data failed.
If the Spider table is partitioned, you must set "Spider table name" with a part name such as "table_name#P#part_name".
You can check the table name and the link ID with the part name using the following SQL:
select table_name from mysql.spider_tables;
Resharding
General Log
To capture all queries sent to remote backends on a Spider Node
:
SET GLOBAL general_log=ON
and SET GLOBAL spider_general_log=on
Debug Mode
Compile MariaDB in debug mode
#cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=Debug -DWITH_VALGRIND=ON
Run MariaDB the following way to have a detailed command trace file
mysqld --debug=S:T:t:r:p:n:L:i:F:f:D:d,info,error,query,qcache,my,exit,general,where:O,/tmp/mysqld.trace
Or with Valgrind to get a complete stack trace on a crash.
valgrind /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/inetbase/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/inetbase/mysql/lucifer.err --open-files-limit=65000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
Report the issue in the MariaDB JIRA or to the SkySQL support center
Status Variables
- SPIDER_DIRECT_AGGREGATE
- SPIDER_DIRECT_ORDER_LIMIT
- SPIDER_MON_TABLE_CACHE_VERSION
- SPIDER_MON_TABLE_CACHE_VERSION_REQ
Information Schema Tables
- SPIDER_ALLOC_MEM;
+-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | ID | int(10) unsigned | NO | | 0 | | | FUNC_NAME | varchar(64) | YES | | NULL | | | FILE_NAME | varchar(64) | YES | | NULL | | | LINE_NO | int(10) unsigned | YES | | NULL | | | TOTAL_ALLOC_MEM | bigint(20) unsigned | YES | | NULL | | | CURRENT_ALLOC_MEM | bigint(20) | YES | | NULL | | | ALLOC_MEM_COUNT | bigint(20) unsigned | YES | | NULL | | | FREE_MEM_COUNT | bigint(20) unsigned | YES | | NULL | | +-------------------+---------------------+------+-----+---------+-------+
Performance Schema
Performance schema is commonly use to trouble shoot what consume time inside your workload. Performance schema should not be activate for servers that are experimenting constant heavy load but most of time it is acceptable to lose 5% to 20% additional CPU to keep track of server internals execution.
To activate the performance schema put in the server section of the MariaDB configuration file
performance_schema=on
Activate the Spider probes to be monitored.
UPDATE information_schema.setup_instruments SET ENABLED='YES', TIMED='yes' WHERE NAME LIKE '%spider%';
Run your queries ...
And check the performance metrics. Remove specific Spider metrics to have a more global view.
SELECT * FROM events_waits_summary_global_by_event_name WHERE COUNT_STAR<>0 AND EVENT_NAME LIKE '%spider%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;