In this article we’ll discuss a how-to use ProxySQL to route inserts to a distributed RT index while using Sphinx/Manticore Search.
The distributed index in Manticore allow UPDATE over multiple RealTime indexes as the UPDATE is sent to all indexes, but change occurs only on one index (assuming you have unique IDs across the indexes).
For inserts the distributed index doesn’t know which index should select, this means we need to send the insert to one of the RealTime subindexes, which must be implemented in the application code. This has the disadvantage that making changes to the distributed index (adding a new RT subindex, for example), requires making changes in the application code.
ProxySQL is a SQL aware proxy for MySQL. As Sphinx and Manticore Search has a mysql-compatible protocol, ProxySQL can be used for simple balancing of Manticore servers. But ProxySQL comes with a great feature: a regexp query routing engine, which allows not only to route queries, but also rewrite them.
What are we going to do next is to send an insert to the distributed index and with the help with ProxySQL we will rewrite the query by changing the target index of insert based on a chosen criteria. This way we don’t need to made changes in the application code if we add new shards to the distributed index or to change how documents are distributed across the shards.
Let’s consider a distributed index made of two RT subindexes. We want to distribute the insertion of documents based on the parity of the document ids.
index myrt {
type = distrubuted
local = myrt1
local = myrt2
}
Adding the Manticore Search server as backend
In this example we’ll consider host group id 2 for our Manticore server.
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES(2,'192.168.1.224',9306);
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Adding the rewrite rules in ProxySQL
We want to insert documents with odd ids in myrt1 and documents with even ids in myrt2. We define 2 rules, which capture the ids and depending on id parity will change the name of the index before sending the query to Manticore.
Admin> INSERT INTO mysql_query_rules(rule_id,match_pattern,replace_pattern,destination_hostgroup,active)
VALUES(32,
'^INSERT INTO (\w+)\(id,content,title,user_id,group_id\) VALUES\(([0-9]*)([02468])',
'INSERT INTO \12(id,content,title,user_id,group_id) VALUES(\2\3',2,1);
Admin> INSERT INTO mysql_query_rules(rule_id,match_pattern,replace_pattern,destination_hostgroup,active)
VALUES(33,
'INSERT INTO (\w+)\(id,content,title,user_id,group_id\) VALUES\(([0-9]*)([13579])',
'INSERT INTO \11(id,content,title,user_id,group_id) VALUES(\2\3',2,1);
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Pay great attention when creating the regexp patterns as well as how you build the queries from your application code to make sure the queries will get captured.
Running SphinxQL via ProxySQL
Before we run our first queries, we need to create a user in ProxySQL that defaults to host group id 2, so queries run by it to be routed to our Manticore server.
Admin> INSERT INTO mysql_users(username,password,active,default_hostgroup) VALUES('test','pass',1,2);
Admin> LOAD MYSQL USERS TO RUNTIME;
And let’s run some inserts (using ProxySQL connection):
mysql> INSERT INTO myrt(id,content,title,user_id,group_id) VALUES(1,'test1','test1',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myrt(id,content,title,user_id,group_id) VALUES(2,'test2','test2',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myrt(id,content,title,user_id,group_id) VALUES(20,'test20','test20',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myrt(id,content,title,user_id,group_id) VALUES(11,'test11','test11',1,1);
mysql> select * from myrt;
+------+---------+----------+--------+
| id | user_id | group_id | title |
+------+---------+----------+--------+
| 1 | 1 | 1 | test1 |
| 2 | 1 | 1 | test2 |
| 11 | 1 | 1 | test11 |
| 20 | 1 | 1 | test20 |
+------+---------+----------+--------+
4 rows in set (0.00 sec)
mysql> select * from myrt1;
+------+---------+----------+--------+
| id | user_id | group_id | title |
+------+---------+----------+--------+
| 1 | 1 | 1 | test1 |
| 11 | 1 | 1 | test11 |
+------+---------+----------+--------+
2 rows in set (0.00 sec)
mysql> select * from myrt2;
+------+---------+----------+--------+
| id | user_id | group_id | title |
+------+---------+----------+--------+
| 2 | 1 | 1 | test2 |
| 20 | 1 | 1 | test20 |
+------+---------+----------+--------+
2 rows in set (0.00 sec)
Lastly, don’t forget to save the changes made in ProxySQL to disk for persistence:
Admin> SAVE MYSQL QUERY RULES TO DISK; SAVE MYSQL SERVERS TO DISK; SAVE MYSQL USERS TO DISK;