Manticore allows for the creation of distributed tables, which act like regular plain or real-time tables, but are actually a collection of child tables used for searching. When a query is sent to a distributed table, it is distributed among all tables in the collection. The server then collects and processes the responses to sort and recalculate values of aggregates, if necessary.

From the client's perspective, it appears as if they are querying a single table.

table foo {
    type = distributed
    local = bar
    local = bar1, bar2
    agent = 127.0.0.1:9312:baz
    agent = host1|host2:tbl
    agent = host1:9301:tbl1|host2:tbl2 [ha_strategy=random retry_count=10]
    ...
}

CREATE TABLE distributed_index type='distributed'
  local='local_index'
  agent='127.0.0.1:9312:remote_table';

The essence of a distributed table lies in its list of child tables, to which it points. There are two types of child tables in a distributed table:

1. Local tables: These are tables that are served within the same server as the distributed table. To enumerate local tables, you use the syntax local =. You can list several local tables using multiple local = lines, or combine them into one list separated by commas.

2. Remote tables: These are tables that are served anywhere outside the server. To enumerate remote tables, you use the syntax agent =. Each line represents one endpoint or agent. Each agent can have multiple external locations and options for how it should work. More details here. It is important to note that the server does not have any information about the type of table it is working with. This may lead to errors if, for example, you issue a CALL PQ to a remote table 'foo' that is not a percolate table.

A remote table in Manticore Search is represented by the agent prefix in the definition of a distributed table. A distributed table can include a combination of local and remote tables. If there are no local tables provided, the distributed table will be purely remote and serve as a proxy only. For example, you might have a Manticore instance that listens on multiple ports and serves different protocols, and then redirects queries to backend servers that only accept connections via Manticore's internal binary protocol, using persistent connections to reduce the overhead of establishing connections. Even though a purely remote distributed table doesn't serve local tables itself, it still consumes machine resources, as it still needs to perform final calculations, such as merging results and calculating final aggregated values.

agent = address1 [ | address2 [...] ][:table-list]
agent = address1[:table-list [ | address2[:table-list [...] ] ] ]

agent directive declares the remote agents that are searched each time the enclosing distributed table is searched. These agents are essentially pointers to networked tables. The value specified includes the address and can also include multiple alternatives (agent mirrors) for either the address only or the address and table list. See Mirroring for mirror syntax and Load balancing for how mirrored agents are selected.

The address specification must be one of the following:

address = hostname[:port] # eg. server2:9312
address = /absolute/unix/socket/path # eg. /var/run/manticore2.sock

The hostname is the remote host name, port is the remote TCP port number, table-list is a comma-separated list of table names, and square brackets [] indicate an optional clause. For TCP connections this port is the remote agent/API port (typically 9312), not the MySQL port (9306).

If the table name is omitted, it is assumed to be the same table as the one where this line is defined. In other words, when defining agents for the 'mycoolindex' distributed table, you can simply point to the address, and it will be assumed that you are querying the mycoolindex table on the agent's endpoints.

If the port number is omitted, it is assumed to be 9312. If it is defined but invalid (e.g. 70000), the agent will be skipped.

You can point each agent to one or more remote tables residing on one or more networked servers with no restrictions. This enables several different usage modes:

• Sharding over multiple agent servers and creating an arbitrary cluster topology
• Sharding over multiple agent servers mirrored for high availability and load balancing purposes (see Mirroring and Load balancing)
• Sharding within localhost to utilize multiple cores (however, it is simpler just to use multiple local tables)

To avoid confusion:

• One agent='host1|host2:table' entry means one remote shard with mirrored backends.
• Multiple agent='...' entries mean multiple remote shards.

All agents are searched in parallel. The index list is passed verbatim to the remote agent. The exact way that list is searched within the agent (i.e. sequentially or in parallel) depends solely on the agent's configuration (see the threads setting). The master has no remote control over this.

It is important to note that the LIMIT, option is ignored in agent queries. This is because each agent can contain different tables, so it is the responsibility of the client to apply the limit to the final result set. This is why the query to a physical table is different from the query to a distributed table when viewed in the query logs. The query cannot be a simple copy of the original query, as this would not produce the correct results.

For example, if a client makes a query SELECT ... LIMIT 10, 10, and there are two agents, with the second agent having only 10 documents, broadcasting the original LIMIT 10, 10 query would result in receiving 0 documents from the second agent. However, LIMIT 10,10 should return documents 10-20 from the resulting set. To resolve this, the query must be sent to the agents with a broader limit, such as the default max_matches value of 1000.

For instance, if there is a distributed table dist that refers to the remote table user, a client query SELECT * FROM dist LIMIT 10,10 would be converted to SELECT * FROM user LIMIT 0,1000 and sent to the remote table user. Once the distributed table receives the result, it will apply the LIMIT 10,10 and return the requested 10 documents.

SELECT * FROM dist LIMIT 10,10;

the query will be converted to:

SELECT * FROM user LIMIT 0,1000

Additionally, the value can specify options for each individual agent, such as:

• ha_strategy - random, roundrobin, nodeads, noerrors (overrides the global ha_strategy setting for the particular agent; see also Mirroring)
• conn - pconn, persistent (equivalent to setting agent_persistent at the table level)
• blackhole 0,1 (identical to the agent_blackhole setting for the agent)
• retry_count an integer value (corresponding to agent_retry_count , but the provided value will not be multiplied by the number of mirrors)

agent = address1:table-list[[ha_strategy=value, conn=value, blackhole=value]]

Example:

# config on box1
# sharding a table over 3 servers
agent = box2:9312:shard1
agent = box3:9312:shard2
# config on box2
# sharding a table over 3 servers
agent = box1:9312:shard2
agent = box3:9312:shard3
# config on box3
# sharding a table over 3 servers
agent = box1:9312:shard1
agent = box2:9312:shard3
# per agent options
agent = box1:9312:shard1[ha_strategy=nodeads]
agent = box2:9312:shard2[conn=pconn]
agent = box2:9312:shard2[conn=pconn,ha_strategy=nodeads]
agent = test:9312:any[blackhole=1]
agent = test:9312|box2:9312|box3:9312:any2[retry_count=2]
agent = test:9312|box2:9312:any2[retry_count=2,conn=pconn,ha_strategy=noerrors]

table products_dist {
  type  = distributed
  agent = 127.0.0.1:9312|127.0.0.1:9313:products
}

CREATE TABLE products_dist type='distributed'
  agent='127.0.0.1:9312:products|127.0.0.1:9313:products';

CREATE TABLE products_dist type='distributed'
  agent='127.0.0.1:9312:products|127.0.0.1:9313:products'
  ha_strategy='roundrobin'
  agent_connect_timeout='200ms'
  agent_query_timeout='500ms'
  mirror_retry_count='2';

SELECT id, title, node FROM products_dist;
SHOW CREATE TABLE products_dist;

+------+------------+-------+
| id   | title      | node  |
+------+------------+-------+
|    1 | same title | node1 |
+------+------------+-------+
+---------------+----------------------------------------------------------------------------------+
| Table         | Create Table                                                                     |
+---------------+----------------------------------------------------------------------------------+
| products_dist | CREATE TABLE products_dist type='distributed' agent='127.0.0.1:9312:products|... |
+---------------+----------------------------------------------------------------------------------+

For optimal performance, it's recommended to place remote tables that reside on the same server within the same record. For instance, instead of:

agent = remote:9312:idx1
agent = remote:9312:idx2

you should prefer:

agent = remote:9312:idx1,idx2

agent_persistent = remotebox:9312:index2

The agent_persistent option allows you to persistently connect to an agent, meaning the connection will not be dropped after a query is executed. The syntax for this directive is the same as the agent directive. However, instead of opening a new connection to the agent for each query and then closing it, the master will keep a connection open and reuse it for subsequent queries. The maximum number of persistent connections per agent host is defined by the persistent_connections_limit option in the searchd section.

It's important to note that the persistent_connections_limit must be set to a value greater than 0 in order to use persistent agent connections. If it's not defined, it defaults to 0, and the agent_persistent directive will act the same as the agentdirective.

Using persistent master-agent connections reduces TCP port pressure and saves time on connection handshakes, making it more efficient.

agent_blackhole = testbox:9312:testindex1,testindex2

The options related to remote agents are not all supported at the same scope. This page focuses on remote-table and per-agent semantics. For mirror-specific behavior, see Mirroring and Load balancing. For full daemon-level behavior, use the linked reference pages in Searchd settings.

agent_connect_timeout = 300ms

CREATE TABLE products_dist type='distributed'
  agent='127.0.0.1:9312:products|127.0.0.1:9313:products'
  agent_connect_timeout='300ms';

agent_query_timeout = 10000 # our query can be long, allow up to 10 sec

SELECT * FROM products_dist OPTION agent_query_timeout=750;

agent_retry_count specifies how many times Manticore will attempt to connect to and query remote agents in a distributed table before reporting a fatal query error. The name varies by scope: use

• agent_retry_count as the instance-wide setting,
• agent_retry_count or its alias mirror_retry_count on a distributed table,
• OPTION retry_count=... per query,
• and [retry_count=...] inside an individual agent=... declaration.

Full daemon-level details are in Searchd: agent_retry_count.

If you use agent mirrors, the server selects a different mirror before each connection attempt according to ha_strategy, and agent_retry_count is aggregated across mirrors.

table products_dist {
  type = distributed
  agent = 127.0.0.1:9312|127.0.0.1:9313:products[retry_count=2]
}

SELECT * FROM products_dist OPTION retry_count=1;

agent_retry_delay = 500ms

SELECT * FROM products_dist OPTION retry_count=2, retry_delay=300;

client_timeout = 1h

The hostname_lookup option defines the strategy for renewing hostnames. By default, the IP addresses of agent host names are cached at server start to avoid excessive access to DNS. However, in some cases, the IP can change dynamically (e.g. cloud hosting) and it may be desirable to not cache the IPs. Setting this option to request disables the caching and queries the DNS for each query. The IP addresses can also be manually renewed using the FLUSH HOSTNAMES command.

The listen_tfo option allows for the use of the TCP_FASTOPEN flag for all listeners. By default, it is managed by the system, but it can be explicitly turned off by setting it to '0'.

For more information about the TCP Fast Open extension, please refer to Wikipedia. In short, it allows to eliminate one TCP round-trip when establishing a connection.

In practice, using TFO can optimize the client-agent network efficiency, similar to when agent_persistent is in use, but without holding active connections and without limitations on the maximum number of connections.

Most modern operating systems support TFO. Linux (as one of the most progressive) has supported it since 2011, with kernels starting from 3.7 (for the server side). Windows has supported it since some builds of Windows 10. Other systems, such as FreeBSD and MacOS, are also in the game.

For Linux systems, the server checks the variable /proc/sys/net/ipv4/tcp_fastopen and behaves accordingly. Bit 0 manages the client side, while bit 1 rules the listeners. By default, the system has this parameter set to 1, i.e., clients are enabled and listeners are disabled.

persistent_connections_limit = 29 # assume that each host of agents has max_connections = 30 (or 29).

The persistent_connections_limit option defines the maximum number of simultaneous persistent connections to remote persistent agents. This is an instance-wide setting and must be defined in the searchd configuration section. Each time a connection to an agent defined under agent_persistent is made, we attempt to reuse an existing connection (if one exists) or create a new connection and save it for future use. However, in some cases it may be necessary to limit the number of persistent connections. This directive defines the limit and affects the number of connections to each agent's host across all distributed tables.

It is recommended to set this value equal to or less than the max_connections option in the agent's configuration.

A special case of a distributed table is a single local and multiple remotes, which is used exclusively for distributed snippets creation, when snippets are sourced from files. In this case, the local table may act as a "template" table, providing settings for tokenization when building snippets.

snippets_file_prefix = /mnt/common/server1/

The snippets_file_prefix is an optional prefix that can be added to the local file names when generating snippets. The default value is the current working folder.

To learn more about distributed snippets creation, see CALL SNIPPETS.

You can create a distributed table from multiple percolate tables. The syntax for constructing this type of table is the same as for other distributed tables, and can include multiplelocal tables as well as agents.

For DPQ, the operations of listing stored queries and searching through them (using CALL PQ) are transparent and work as if all the tables were one single local table. However, data manipulation statements such as insert, replace, truncate are not available.

If you include a non-percolate table in the list of agents, the behavior will be undefined. If the incorrect agent has the same schema as the outer schema of the PQ table (id, query, tags, filters), it will not trigger an error when listing stored PQ rules, and may pollute the list of actual PQ rules stored in PQ tables with its own non-PQ strings. As a result, be cautious and aware of the confusion that this may cause. ACALL PQ to such an incorrect agent will trigger an error.

For more information on making queries to a distributed percolate table, see making queries to a distribute percolate table.

Manticore Search has a single level of hierarchy for tables.

Unlike other DBMS, there is no concept of grouping tables into databases in Manticore. However, for interoperability with SQL dialects, Manticore accepts SHOW DATABASES statements for interoperability with SQL dialect, statements, but the statement does not return any results.

SHOW TABLES [ LIKE pattern ]

SHOW TABLES;

POST /sql?mode=raw -d "SHOW TABLES"

$client->nodes()->table();

utilsApi.sql('SHOW TABLES')

await utilsApi.sql('SHOW TABLES')

res = await utilsApi.sql('SHOW TABLES');

utilsApi.sql("SHOW TABLES", true)

utilsApi.Sql("SHOW TABLES", true)

utils_api.sql("SHOW TABLES", Some(true)).await

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| dist     | distributed |
| plain    | local       |
| pq       | percolate   |
| rt       | rt          |
| template | template    |
+----------+-------------+
5 rows in set (0.00 sec)

[
  {
    "columns": [
      {
        "Table": {
          "type": "string"
        }
      },
      {
        "Type": {
          "type": "string"
        }
      }
    ],
    "data": [
      {
        "Table": "dist",
        "Type": "distributed"
      },
      {
        "Table": "plain",
        "Type": "local"
      },
      {
        "Table": "pq",
        "Type": "percolate"
      },{
        "Table": "rt",
        "Type": "rt"
      },{
        "Table": "template",
        "Type": "template"
      }
    ],
    "total": 5,
    "error": "",
    "warning": ""
  }
]

Array
(
    [dist1] => distributed
    [rt] => rt
    [products] => rt
)

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

SHOW TABLES LIKE 'pro%';

POST /sql?mode=raw -d "SHOW TABLES LIKE 'pro%';"

$client->nodes()->table(['body'=>['pattern'=>'pro%']]);

utilsApi.sql('SHOW TABLES LIKE \'pro%\'');

await utilsApi.sql('SHOW TABLES LIKE \'pro%\'');

utilsApi.sql('SHOW TABLES LIKE \'pro%\'')

utilsApi.sql("SHOW TABLES LIKE 'pro%'", true)

utilsApi.Sql("SHOW TABLES LIKE 'pro%'", true)

utils_api.sql("SHOW TABLES LIKE 'pro%'", Some(true)).await

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| products | distributed |
+----------+-------------+
1 row in set (0.00 sec)

[
  {
    "columns": [
      {
        "Table": {
          "type": "string"
        }
      },
      {
        "Type": {
          "type": "string"
        }
      }
    ],
    "data": [
      {
        "Table": "products",
        "Type": "distributed"
      }
    ],
    "total": 1,
    "error": "",
    "warning": ""
  }
]

Array
(
    [products] => distributed
)

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}

{DESC | DESCRIBE} table_name [ LIKE pattern ]

The DESCRIBE statement lists the table columns and their associated types. The columns are document ID, full-text fields, and attributes. The order matches the order in which fields and attributes are expected by INSERT and REPLACE statements. Column types include field, integer, timestamp, ordinal, bool, float, bigint, string, and mva. ID column will be typed as bigint. Example:

mysql> DESC rt;
+---------+---------+
| Field   | Type    |
+---------+---------+
| id      | bigint  |
| title   | field   |
| content | field   |
| gid     | integer |
+---------+---------+
4 rows in set (0.00 sec)

An optional LIKE clause is supported. Refer to SHOW META for its syntax details.

select * from tbl.@table where type='text';

POST /sql?mode=raw -d "select * from tbl.@table where type='text';"

+------+-------+------+----------------+
| id   | field | type | properties     |
+------+-------+------+----------------+
|    2 | title | text | indexed stored |
+------+-------+------+----------------+
1 row in set (0.00 sec)

[{
"columns":[{"id":{"type":"long long"}},{"field":{"type":"string"}},{"type":{"type":"string"}},{"properties":{"type":"string"}}],
"data":[
{"id":2,"field":"title","type":"text","properties":"indexed stored"}
],
"total":1,
"error":"",
"warning":""
}]

select field from tbl.@table;
select field, properties from tbl.@table where type in ('text', 'uint');
select * from tbl.@table where properties any ('stored');

SHOW CREATE TABLE table_name [ OPTION output_words = 'list' | 'file' ]

SHOW CREATE TABLE tbl\G

POST /sql?mode=raw -d "SHOW CREATE TABLE tbl"

       Table: tbl
Create Table: CREATE TABLE tbl (
f text indexed stored
) charset_table='non_cont,cont' morphology='icu_chinese'
1 row in set (0.00 sec)

[{
"columns":[{"Table":{"type":"string"}},{"Create Table":{"type":"string"}}],
"data":[
{"Table":"tbl","Create Table":"CREATE TABLE tbl (\nf text)"}
],
"total":1,
"error":"",
"warning":""
}]

If you use the DESC statement on a percolate table, it will display the outer table schema, which is the schema of stored queries. This schema is static and the same for all local percolate tables:

mysql> DESC pq;
+---------+--------+
| Field   | Type   |
+---------+--------+
| id      | bigint |
| query   | string |
| tags    | string |
| filters | string |
+---------+--------+
4 rows in set (0.00 sec)

If you want to view the expected document schema, use the following command: DESC <pq table name> table:

mysql> DESC pq TABLE;
+-------+--------+
| Field | Type   |
+-------+--------+
| id    | bigint |
| title | text   |
| gid   | uint   |
+-------+--------+
3 rows in set (0.00 sec)

Also desc pq table like ... is supported and works as follows:

mysql> desc pq table like '%title%';
+-------+------+----------------+
| Field | Type | Properties     |
+-------+------+----------------+
| title | text | indexed stored |
+-------+------+----------------+
1 row in set (0.00 sec)