The Ultimate Guide to High Availability with MariaDB

Spider Cluster Management

You are viewing an old version of this article. View the current version here.

Contents

  1. Direct SQL
  2. Direct Handler Socket
  3. Inter Nodes Copy Table
  4. Resharding
  5. Debug Mode
  6. Status Variables
  7. Information Schema Tables

Direct SQL

Direct SQL is a way to map reduce execution on remote backend and store the result in a local table. In 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, it is expected to be fixed in MariaDB 10.0.7 or MariaDB 10.0.8, ask a SkySQL representative for 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

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 copy table data from source link ID to destination link ID list without stoping your service for copying

spider_copy_tables(Spider table name, source link ID, destination link ID list[, parameters])

  • Returns 1 if coping data is success.
  • Returns 0 if coping data is failed.

If Spider table is partitioned, you must set "Spider table name" with part name like "table_name#P#part_name".

You can check table name and the link ID with part name using the following SQL:

select table_name from mysql.spider_tables;

Resharding

Debug Mode

Compile MariaDB in debug mode 

./cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=Debug   -DWITH_VALGRIND=ON

Run MariaDB the wollowing way to have a details 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 complet 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    |       |
+-------------------+---------------------+------+-----+---------+-------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.