Spider Cluster Management

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

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;

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.