[MXS-1080] Readwritesplit (documentation of max_slave_replication_lag) Created: 2017-01-16  Updated: 2017-01-25  Resolved: 2017-01-20

Status: Closed
Project: MariaDB MaxScale
Component/s: Documentation
Affects Version/s: 2.0.3
Fix Version/s: 2.0.4

Type: Bug Priority: Major
Reporter: Michael Van Der Beek Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 6

Sprint: 2017-26

 Description   

Hi,

I have a MariaDB cluster setup with 3 nodes. (10.60.1.85,10.60.1.86,10.60.1.87)

I've tried to use mysqlslap to do testing.
Basic test is do connect direct to 10.60.1.85 using this command.
mysqlslap --host 10.60.1.85 --password=password --engine=innodb --auto-generate-sql --concurrency=10 --iterations=200

Okay now I've setup maxscale on 10.60.1.4 on centos 6, with readwritesplit.
If I setup the config to have only 1 server (10.60.1.85), no problem.

Once I add a second server (10.60.1.86). I start getting random failures.
For example.
mysqlslap --host 127.0.0.1 --password=password --engine=innodb --auto-generate-sql --concurrency=10 --iterations=200
mysqlslap: Cannot run query SELECT intcol1,charcol1 FROM t1 ERROR : Table 'mysqlslap.t1' doesn't exist

Sometimes it complaints that it cannot create database.
Sometimes it works.
Adding the 3rd server, almost always guarantees some error.

What my guess is that at the end of mysqlslap, it deletes the database mysqlslap and table t1 before the final few select statements.

Not sure if this is a know issue or there is some option that I can use for testing without failures.

I tries sysbench.. not sure what it does, but it never uses the secondary server for reads.
Even if I turn off the transactions.

Anyway here is my maxscale config file.

# MaxScale documentation on GitHub:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
 
 
 
# Global parameters
 
#
 
# Complete list of configuration options:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
 
 
 
[maxscale]
 
threads=1
 
# Server definitions
 
#
 
# Set the address of the server to the network
 
# address of a MySQL server.
 
#
 
 
 
[server1]
 
type=server
 
address=10.60.1.85
 
port=3306
 
protocol=MySQLBackend
 
priority=1
 
#wight=1
 
 
 
[server2]
 
type=server
 
address=10.60.1.86
 
port=3306
 
protocol=MySQLBackend
 
priority=2
 
#weight=5
 
 
 
[server3]
 
type=server
 
address=10.60.1.87
 
port=3306
 
protocol=MySQLBackend
 
priority=3
 
#weight=5
 
# Monitor for the servers
 
#
 
# This will keep MaxScale aware of the state of the servers.
 
# MySQL Monitor documentation:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md
 
 
 
[MySQL Monitor]
 
type=monitor
 
module=galeramon
 
#servers=server1,server2,server3
 
servers=server1,server2
 
user=root
 
passwd=password
 
monitor_interval=10000
 
use_priority=true
 
 
 
# Service definitions
 
#
 
# Service Definition for a read-only service and
 
# a read/write splitting service.
 
#
 
 
 
# ReadConnRoute documentation:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
 
 
 
#[Read-Only Service]
 
#type=service
 
#router=readconnroute
 
#servers=server1
 
#user=myuser
 
#passwd=mypwd
 
#router_options=slave
 
 
 
# ReadWriteSplit documentation:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
 
 
 
[Read-Write Service]
 
type=service
 
router=readwritesplit
 
#router=readconnroute
 
#servers=server1,server2,server3
 
servers=server1,server2
 
user=root
 
passwd=password
 
enable_root_user=1
 
max_slave_connections=100%
 
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
 
 
 
# This service enables the use of the MaxAdmin interface
 
# MaxScale administration guide:
 
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
 
 
 
[MaxAdmin Service]
 
type=service
 
router=cli
 
 
 
# Listener definitions for the services
 
#
 
# These listeners represent the ports the
 
# services will listen on.
 
#
 
 
 
#[Read-Only Listener]
 
#type=listener
 
#service=Read-Only Service
 
#protocol=MySQLClient
 
#port=4008
 
 
 
[Read-Write Listener]
 
type=listener
 
service=Read-Write Service
 
protocol=MySQLClient
 
port=3306
 
 
 
[MaxAdmin Listener]
 
type=listener
 
service=MaxAdmin Service
 
protocol=maxscaled
 
#socket=default
 
address=localhost
 
port=6603



 Comments   
Comment by markus makela [ 2017-01-18 ]

A good way to check that MaxScale works is to start it up and check the output of maxadmin list servers. This will show the server states and how many connections they have.

Since you have configured max_slave_connections=100%, both server1 and server2 should get a connection when a client connects to the Read-Write Service service. Usually it is recommended to use the LEAST_CURRENT_OPERATIONS selection criteria when all slaves are in use. This will spread concurrent reads evenly across all slave servers.

LEAST_GLOBAL_CONNECTIONS should be used when max_slave_connections is set to a lower value and not all of the slaves are taken into use.

Manually defining the SQL statement for mysqlslap should with detecting any problems in your setup. A query like SELECT @@hostname should be spread across all slave nodes with LEAST_CURRENT_OPERATIONS as the selection criteria.

Comment by Michael Van Der Beek [ 2017-01-19 ]

Hi Markus,

I think the problem is a latency delay between master DB and secondary DBs.
So if a sequence goes like
Inset into test_table (id,col1) values(5,10) Select * from test_table where id=5;

The first line will go to the Master DB.
The Second line goes to the secondary db.

The secondary db may not have gotten the update before the first line propagates to the secondary DBs.
This may give an error.

This goes for any combination of insert/update and immediate reads.

I was wondering now, there is this option max_slave_replication_lag (default disabled) So that implies that it expects no delay between replication of master to slave.
Does that apply to mariadb galera cluster?

Secondary won't LEAST_BEHIND_MASTER be a better option?
Since randomly one secondary db can be slower due to more requests, so it might update slower.

I've noticed in the statistics
Number of queries forwarded: 70035061
Number of queries forwarded to master: 38393882 (54.82%)
Number of queries forwarded to slave: 31641179 (45.18%)
Number of queries forwarded to all: 4531 (0.01%)

What requests are "forwarded to all"?

Regards,

Michael

Comment by markus makela [ 2017-01-19 ]

So if a sequence goes like
Inset into test_table (id,col1) values(5,10) Select * from test_table where id=5;

The first line will go to the Master DB.
The Second line goes to the secondary db.

The secondary db may not have gotten the update before the first line propagates to the secondary DBs.
This may give an error.

This goes for any combination of insert/update and immediate reads.

The usual way that reads after modifications (AKA critical reads) are done is inside transactions. This guarantees that a consistent snapshot of the database and all of the modifications done by the current connection are visible when a read is executed.

For example, the following statements would be routed to different servers with autocommit enabled causing non-deterministic behavior.

INSERT INTO test.t1 VALUES (1, "hello world");
 
SELECT * FROM test.t1 WHERE id = 1;

Wrapping them in a transaction will prevent this from happening as MaxScale routes all statements inside a transaction to the assigned master node.

The upcoming 2.1 release of MaxScale will have a filter (ccrfilter) which allows configurable handling of critical reads.

I was wondering now, there is this option max_slave_replication_lag (default disabled) So that implies that it expects no delay between replication of master to slave.
Does that apply to mariadb galera cluster?

The option does not apply to Galera clusters and it is used to configure the maximum acceptable lag for a slave server. If the slave lag on a slave exceeds the configured value, it will not be used.

Secondary won't LEAST_BEHIND_MASTER be a better option?
Since randomly one secondary db can be slower due to more requests, so it might update slower.

LEAST_BEHIND_MASTER is a good option when you know the lag between a master server and its slaves is greater than one second. This requires that the mysqlmon monitor is configured with detect_replication_lag so that replication lag is monitored. As mentioned earlier, this only works with master-slave clusters, not Galera clusters.

What requests are "forwarded to all"?

All requests that modify the state of the MySQL connection are routed to all connected servers. These consist of requests similar to the following.

USE db;
 
SET autocommit=1;
 
SET @my_variable = 1;

Routing these to all servers keeps the states of the connections identical.

Comment by Michael Van Der Beek [ 2017-01-19 ]

Great thanks for the answers.

I'll have to look at my applications to see if I'll hit these cases where the insert/update can happen within a short interval of the select statements.

Okay.

I think I understand enough of how it works.

Thanks for your help.

Side comment, maybe the documentation should state which options work galera/master-slave or other modes. Less confusion for newbies like me.

Regards,

Michael

Comment by markus makela [ 2017-01-19 ]

That's a good suggestion, we'll add it to the documentation.

Comment by markus makela [ 2017-01-20 ]

The documentation now explains that this only works with master-slave clusters.

Generated at Thu Feb 08 04:04:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.