The benefice is to found for a given slave, for all table listed in a given SELECT, if table data have change on the master.
Read scalability is implemented if a set of tables used in a query is updated less frequently then the replication delay.
How does it work:
- Read write splitting lua script that implement GTTID
- A memory table is create on the master to propagate the GTTID
- For write queries, for each table, inject some queries updating memcached with key : db.tables , server-id and value : GTTID
- Replication updating memcached with key : db.tables , server-id on GTID receive
MariaDB [mysql]> desc TBLGTID;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| crc32 | int(10) unsigned | NO | PRI | 0 | |
| gtid | int(1) | NO | | 0 | |
| memres | tinyint(4) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
Injected query with the proxy
INSERT INTO
mysql.TBLGTID
SELECT
CRC32(\"" .. tbl .. "\)",
0 ,
memc_set(concat(\"" .. CRC32.Hash(tbl) .. "\",@@server_id),0)
ON DUPLICATE KEY UPDTAE
gtid=gtid+1,
memres=memc_set(concat(\"" .. CRC32.Hash(tbl) .. "\",@@server_id),gtid+1)"
As a new slave can enter the replication or a db can be restarted and lose his memory table information the memcache itself can be lost for a while. No updates on tables can happen for some time and so position in memcache is out of sync and most queries will likely end up to the master .
To prevent this to happen we need to reset all table status done by the heartbeat process
REPLACE INTO
mysql.TBLGTID
select
CRC32(concat(table_schema, "." ,table_name)),
0,
memc_set(concat( CRC32(concat(table_schema,".", table_name)) ,@@server_id),0)
FROM
information_schema.tables
The process of choosing a slave is done by reading for each table in the query The gttid of the slave and of the master and failback to the master if we found a difference
Monitoring of gttid efficiency will be done via storing in tarantool using memcache protocol indexes like:
- Counter per db
- Counter per query template