Spider Storage Engine Core Concepts

A typical Spider deployment has a shared-nothing clustered architecture. The system works with any inexpensive hardware, and with a minimum of specific requirements for hardware or software. It consists of a set of computers, with one or more MariaDB processes known as nodes.

The nodes that store the data are designed as Backend Nodes, and can be any MariaDB, MySQL, Oracle server instances using any storage engine available inside the backend.

The Spider Proxy Nodes are instances running at least MariaDB 10. Spider Proxy Nodes are used to declare per table attachment to the backend nodes. In addition Spider Proxy Nodes can be setup to enable the tables to be split and mirrored to multiple Backend Nodes.

In the default high availability setup Spider Nodes produce SQL errors when a backend server is not responding. Per table monitoring can be setup to enable availability in case of unresponsive backends monotoring_bg_kind=1 or monotoring_bg_kind=2. The Monitoring Spider Nodes are inter-connected with usage of the system table mysql.link_mon_servers to manage network partitioning. Better known as split brain, an even number of Spider Monitor Nodes should be setup to allow a consensus based on the majority. Rather a single separated shared Monitoring Node instance or a minimum set of 3 Spider Nodes. More information can be found .

MariaDB starting with

Spider's high availability feature has been deprecated (MDEV-28479), and are deleted. Please use other high availability solutions like or .

Spider is a pluggable Storage Engine, acting as a proxy between the optimizer and the remote backends. When the optimizer requests multiple calls to the storage engine, Spider enforces consistency using the 2 phase commit protocol to the backends and by creating transactions on the backends to preserve atomic operations for a single SQL execution. Preserving atomic operation during execution is used at multiple levels in the architecture. For the regular optimizer plan, it refers to multiple split reads and for concurrent partition scans, it will refer to semi transactions.

Costly queries can be more efficient when it is possible to fully push down part of the execution plan on each backend and reduce the result afterwards. Spider enables such execution with some direct execution shortcuts.

Spider uses the per partitions and per table model to concurrently access the remote backend nodes. For memory workload that property can be used to define multiple partitions on a single remote backend node to better adapt the concurrency to available CPUs in the hardware.

Spider maintains an internal dictionary of Table and Index statistics based on separated threads. The statistics are pulled per default on a time line basis and refer to crd for cardinality and sts for table status.

Spider stores resultsets into memory, but =3 stores resultsets into internal temporary tables if the resultsets are larger than quick_table_size.

Spider tables connect to remote servers by reading connection information from one or more sources. MariaDB supports several methods for defining this information, each introduced at different versions. When more than one method is present, a defined precedence order determines which takes effect.

Connection information for Spider tables can be provided in different ways:

• Foreign server options (using CREATE SERVER)

• CONNECTION or COMMENT strings

• Engine-defined options (such as REMOTE_SERVER

Connection information for Spider tables can also be specified for different levels, where partition-level info overrides table-level info.

Spider has a predefined precedence order when using multiple methods.

Applies to both CS and ES

The following behavior applies to both CS and ES:

• Connection specification methods: (foreign server options, COMMENT string, CONNECTION string, and engine-defined options).

• Precedence rules (engine-defined options > COMMENT string > CONNECTION string > foreign server options; partition-level overrides table-level)

ES Additional Behavior (ODBC)

ODBC connection strings are used by Spider in Enterprise Server to create connections via ODBC drivers.

The source of the ODBC connection string could be:

• COMMENT clause

• CONNECTION clause

• engine-defined options

These values are combined and converted into a format that is compatible with ODBC.

CREATE SERVER with COMMENT or CONNECTION

Since earlier versions, connection information can be specified by creating a foreign server, then referencing it in the Spider table definition using COMMENT= or CONNECTION=.

The same server can also be referenced at the partition level.

Engine-defined options: REMOTE_SERVER, REMOTE_DATABASE, REMOTE_TABLE (Since 10.8.1)

Since (), engine-defined table options can be used to directly specify connection details:

These engine-defined options override COMMENT= when both are present. These options are more structured and are the recommended method for new configurations.

Extended Engine-defined options (Since 11.3.1)

Since (), more engine-defined options are available. COMMENT= and CONNECTION= are ignored if there are any engine-defined options and warnings may be generated. Set spider_suppress_comment_ignored_warning = 1 to suppress the warning that is generated when they are ignored.

COMMENT and CONNECTION Deprecated (Since 11.4.1)

Since (), the Spider connection information specified by COMMENT= and CONNECTION= has been deprecated. Instead, use the engine-defined parameters.

ODBC Connection String via CONNECTION (Since 11.4.3-1 ES)

In the following instances, CONNECTION= is used directly as the ODBC connection string since ():

• COMMENT= / CONNECTION= are explicitly ignored (i.e., engine-defined options are present, per )

• The value of CONNECTION= follows the ODBC connection string format (param1=val1;param2=val2;...)

In previous ES versions, the chosen COMMENT=, CONNECTION=, and OPTIONS fields were used to create the ODBC connection string. See for a complete list of fields used.

CREATE SERVER with FOREIGN DATA WRAPPER ODBC

Foreign server options are directly translated to ODBC connection key-value pairs when using CREATE SERVER with FOREIGN DATA WRAPPER ODBC (). The above-described precedence order is maintained.

CREATE SERVER ... FOREIGN DATA WRAPPER supports the following wrapper types:

Note: The odbc wrapper value is only available in MariaDB Enterprise Server (ES). It is not supported in Community Server (CS).

Default Behavior When Connection Information is not Specified

If connection information is not explicitly specified using engine-defined options, Spider can depend on the configuration provided by other methods, such as COMMENT, CONNECTION, or CREATE SERVER statements.

The MariaDB version and configuration method determine the exact resolution behavior. The default behavior when no connection information is provided is currently being reviewed. To ensure predictable behavior, it is advised to explicitly specify connection details using engine-defined options (such as REMOTE_SERVER, REMOTE_DATABASE, and REMOTE_TABLE).

_{This page is licensed: CC BY-SA / Gnu FDL}