I have trouble to forward all SELECT requests to the slave in a MySQL master-slave architecture.
I’m using maxscale’s read-write router, which does its job except for
all statements using temporary tables
being redirected to the master (github doc). But I want those select requests (mostly SELECT COUNT…) being executed on the slave because those are the heaviest ones in terms of cpu load.
I’ve set up a route filter, supposed to redirect all SELECTs to the slave database, but it isn’t working, though maxscale has been restarted and the filter has shown up in the filter chain.
My maxscale configuration:
[maxscale] threads=4 [MySQL Monitor] type=monitor module=mysqlmon servers=master-db, slave-db user=root passwd=* monitor_interval=10000 # =============== Filters ================ # [SelectToSlaveFilter] type=filter module=namedserverfilter match=select* options=ignorecase server=slave-db # =============== Services ================ # [RW Split Router] type=service router=readwritesplit servers=master-db, slave-db user=client passwd=* max_slave_connections=1 enable_root_user=1 filters=SelectToSlaveFilter [Debug Interface] type=service router=debugcli [CLI] type=service router=cli # =============== Listeners ================ # [RW Split Listener] type=listener service=RW Split Router protocol=MySQLClient port=4446 socket=/var/lib/maxscale/rwsplit.sock [Debug Listener] type=listener service=Debug Interface protocol=telnetd address=127.0.0.1 port=4442 [CLI Listener] type=listener service=CLI protocol=maxscaled port=6603 # =============== Servers ================ # [master-db] type=server address=X.X.X.X port=3306 protocol=MySQLBackend [slave-db] type=server address=127.0.0.1 port=3306 protocol=MySQLBackend
Additional information that confirms the filter is used in the routing process:
maxadmin -pmariadb 'show session 0x54ae060' Session 1840 (0x54ae060) State: Session ready for routing Service: RW Split Router (0x54992c0) Client DCB: 0x54aff50 Client Address: root@localhost_from_socket Connected: Thu Nov 26 09:10:40 2015 Idle: 4 seconds Filter: SelectToSlaveFilter Match and route: /select */ -> slave-db No. of queries diverted by filter: 3 No. of queries not diverted by filter: 1
One of the heavy queries still showing up on the master, even with filtering:
Copying to tmp table SELECT COUNT(*) as nb, DATE(`Comment`.`date_update`) as day FROM `table`.`comments` AS `Comment` WHERE `Comment`.`date_update` >= '2015-11-26 00:00:00' AND `Comment`.`date_update` <= '2015-11-26 23:59:59' GROUP BY DATE(`Comment`.`date_update`)
Maxscale error log:
--- Logging is enabled. 2015-11-26 09:56:44 Error: Failed to obtain address for host ::1, Address family for hostname not supported 2015-11-26 09:56:44 Warning: Failed to add user root@::1 for service [RW Split Router]. This user will be unavailable via MaxScale. 2015-11-26 09:56:44 Warning: Duplicate MySQL user found for service [RW Split Router]: root@X.X.X.X for database: (null) 2015-11-26 09:56:44 Warning: Duplicate MySQL user found for service [RW Split Router]: email@example.com for database: (null) 2015-11-26 09:56:44 Warning: Duplicate MySQL user found for service [RW Split Router]: firstname.lastname@example.org for database: (null) 2015-11-26 09:56:44 Error: Failed to set socket options. Error 95: Operation not supported
The problem was not maxscale’s.
I found out that even if the count of routed requests to the slave was incrementing, no connection was ever made to the slave.
Here is a functional status with established connections to the slave, whereas I had always 0 connection before:
maxadmin -pmariadb show servers Server 0x46d6010 (master-db) Server: X.X.X.X Status: Master, Running Protocol: MySQLBackend Port: 3306 Server Version: 10.0.21-MariaDB-wsrep-log Node Id: 1 Master Id: -1 Slave Ids: 2 Repl Depth: 0 Number of connections: 54 Current no. of conns: 0 Current no. of operations: 0 Server 0x46306a0 (slave-db) Server: 127.0.0.1 Status: Slave, Running Protocol: MySQLBackend Port: 3306 Server Version: 10.0.22-MariaDB Node Id: 2 Master Id: 1 Slave Ids: Repl Depth: 1 Number of connections: 54 Current no. of conns: 0 Current no. of operations: 0
The reason was that the slave went into unsynced mod after testing database creations and deletions because I’m only binlogging one database.
I had to redo the slave with a dump from the following command for innodb databases:
mysqldump -u root -p*** --master-data --single-transaction mydb | gzip > dump.sql.gz
After that, all selects were correctly routed to the slave without the need of a filter.