What is Manticore Buddy?

Manticore Buddy is a PHP-based sidecar that runs alongside Manticore Search. When Manticore receives a query it cannot handle natively, it forwards the request to Buddy for processing. This enables high-level operations like backups, query monitoring, and automatic table creation without sacrificing the core engine's performance.

Buddy is included with the manticore-extra package and launches automatically when Manticore starts.

Let's connect to Manticore:

mysql -P9306 -h0

We can verify Buddy is active by running a Buddy-handled command:

SHOW QUERIES;

If the command returns a result table, Buddy is working. SHOW QUERIES is one of many commands handled by Buddy — we'll explore it in detail later.

Preparing Test Data

Let's create tables and insert sample data for our exercises:

DROP TABLE IF EXISTS products;

CREATE TABLE products(title text, price float, color string, quantity int);

INSERT INTO products(id, title, price, color, quantity) VALUES (1, 'Red Bag with Tassel', 19.85, 'red', 5);

INSERT INTO products(id, title, price, color, quantity) VALUES (2, 'Blue Leather Wallet', 45.00, 'blue', 12);

INSERT INTO products(id, title, price, color, quantity) VALUES (3, 'Green Cotton T-Shirt', 15.50, 'green', 30);

INSERT INTO products(id, title, price, color, quantity) VALUES (4, 'Yellow Running Shoes', 89.99, 'yellow', 8);

INSERT INTO products(id, title, price, color, quantity) VALUES (5, 'Black Wool Scarf', 25.00, 'black', 20);

Verify the data:

SELECT * FROM products;

Let's also create a second table to demonstrate multi-table operations later:

DROP TABLE IF EXISTS orders;

CREATE TABLE orders(id bigint, product_id int, customer text, amount float);

INSERT INTO orders(id, product_id, customer, amount) VALUES (1, 1, 'Alice Johnson', 19.85);

INSERT INTO orders(id, product_id, customer, amount) VALUES (2, 2, 'Bob Smith', 90.00);

INSERT INTO orders(id, product_id, customer, amount) VALUES (3, 3, 'Carol White', 46.50);

INSERT INTO orders(id, product_id, customer, amount) VALUES (4, 4, 'Alice Johnson', 89.99);

SELECT * FROM orders;

Now we're ready to explore Buddy features.

FREEZE and UNFREEZE

FREEZE prepares a table for a safe external backup by preventing disk file modifications. You can use it manually for custom backup workflows or to safely copy table files.

The syntax is:

FREEZE table_name;
UNFREEZE table_name;

Let's freeze the products table:

FREEZE products;

The command returns a list of table files that are now safe to copy. While a table is frozen:

• New inserts go to the RAM chunk (up to rt_mem_limit)
• Updates that would modify disk chunks will wait until unfrozen
• Deletes from disk chunks will also wait
• Table compaction is paused

Checking Lock Status

Use SHOW LOCKS to see all currently frozen tables:

SHOW LOCKS;

You should see the products table in the output with a freeze count of 1.

Let's also freeze the orders table:

FREEZE orders;

SHOW LOCKS;

Now both tables are listed. Each FREEZE call increments a lock counter for the table.

Unfreezing Tables

UNFREEZE decrements the lock counter. When it reaches zero, normal operations resume:

UNFREEZE products;

SHOW LOCKS;

Only orders remains locked now. Let's unfreeze it too:

UNFREEZE orders;

SHOW LOCKS;

The result should be empty — all tables are back to normal operation. Any queued writes or updates that were waiting during the freeze will now complete.

Verify everything works:

INSERT INTO products(id, title, price, color, quantity) VALUES (6, 'White Canvas Sneakers', 55.00, 'white', 15);

SELECT * FROM products;

BACKUP Command

Buddy enables the BACKUP SQL command for creating table backups directly from the SQL interface. Internally, BACKUP uses FREEZE and UNFREEZE (covered in the previous step) to ensure data consistency.

The general syntax is:

BACKUP
  [TABLE | TABLES table1, table2, ...]
  TO /path/to/backup
  [OPTION async = {yes|no}, compress = {yes|no}]

Let's back up all tables. Manticore will create a timestamped subdirectory automatically:

BACKUP TO /tmp;

Back up only specific tables:

BACKUP TABLE products TO /tmp;

For large datasets, run the backup asynchronously so the command returns immediately:

BACKUP TO /tmp OPTION async = yes;

Enable compression to reduce backup size:

BACKUP TO /tmp OPTION compress = yes;

Both options can be combined:

BACKUP TABLE products, orders TO /tmp OPTION async = yes, compress = yes;

Check that our tables are still intact after all the backups:

SHOW TABLES;

SELECT * FROM products;

SHOW QUERIES and KILL

SHOW QUERIES displays all currently running queries. This is useful for monitoring long-running searches and other operations.

SHOW QUERIES;

The output columns are:

• id — query identifier (used with KILL to terminate)
• query — the query text
• time — elapsed time or how long ago the query ran
• protocol — connection protocol (mysql, http, etc.)
• host — client address

Terminating Queries with KILL

KILL terminates a running SELECT query by its ID obtained from SHOW QUERIES.

To demonstrate this, we have a pre-loaded table t with 300,000 rows and a helper script that starts a slow query, finds it with SHOW QUERIES, and terminates it with KILL. Let's exit MySQL first. You can inspect the script before running it:

exit;

cat /slow_query.sh

Now run it:

/slow_query.sh

The script starts an intentionally slow query in the background, then uses SHOW QUERIES to find its id and KILL to terminate it.

Viewing Thread Activity

Let's reconnect to MySQL and inspect current thread activity with SHOW THREADS:

mysql -P9306 -h0

SHOW THREADS;

The format=all option provides more details including the full query text:

SHOW THREADS OPTION format=all;

Auto Schema

Auto schema allows Manticore to automatically create a table when you insert data into a table that does not yet exist. Column types are inferred from the values provided.

Let's try inserting into a non-existent table:

INSERT INTO new_products(title, price, in_stock) VALUES ('Wireless Mouse', 29.99, 1);

Manticore created the table automatically. Check its schema:

DESCRIBE new_products;

SELECT * FROM new_products;

The type detection rules are:

• Integer values → uint (or bigint if the value exceeds 2^32)
• Decimal values → float
• Quoted strings → text (full-text searchable)
• JSON strings → json
• Multi-value sets like (1, 2, 3) → multi

Insert more rows — types will remain consistent:

INSERT INTO new_products(title, price, in_stock) VALUES ('Mechanical Keyboard', 79.99, 1), ('USB-C Hub', 34.50, 0);

SELECT * FROM new_products;

View the full CREATE TABLE statement that Manticore generated:

SHOW CREATE TABLE new_products;

When to Use Auto Schema

Auto schema is convenient for quick prototyping and data exploration. For production use, explicit CREATE TABLE statements give you more control over field types, tokenization settings, and morphology.

Compare the auto-created schema with our manually created table:

SHOW CREATE TABLE products;

The manual table has explicit types for each field, while the auto-created one uses defaults.

Clean up the test table:

DROP TABLE IF EXISTS new_products;