Maxscale read write split and route filter

Posted on

Question :

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]: root@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: cmon@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Error: Failed to set socket options. Error 95: Operation not supported

Answer :

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.

Leave a Reply

Your email address will not be published.