Mysql – Maxscale read write split and route filter

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