Read/Write split, principle of work

[Denis Makovkin]

Hello, can any one help to understand, please. 

Suppose, i setup proxysql as read/write query spliter with simple master-slave mysql  behind it. Client does several queries in one connection. If proxysql meet query, which it routes to master, what happens with next several queries in the same connection, that select data only? Is it possible, that it routes them to slave ? 

I try to explain - i've got some errors with our application, when it works via read/write splitter. It's because in the same connection code can create object in database, and with next query select it object from database. And sometimes that lead to errros, because select query goes to slave, where data is still not present due to replication lag.

So, if i understand correctly, there have to be the way to route all query on the master, after the first one, that change the data.

Or am i totally wrong and my approach to the problem is incorrect? 

Thanks

[Satria Dwi Putra]

Hi Denis.

Yes is possible, you must create query rules to split query read and write. ref : ProxySQL Split Read/Write

To prevent query route to slave with high replication lag you can set threshold maximum replication lag in table mysql_server column

max_replication_lag. ref : max_replication_lag

max_replication_lag – if greater than 0, ProxySQL will 
regularly monitor replication lag and if it goes beyond the configured 
threshold it will temporary shun the host until replication catches up

Regards,

Satria

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e214ea20-1c95-4d8d-884f-39f421378cean%40googlegroups.com.

[Denis Makovkin]

Thanks

The main problem here is that 'Seconds_Behind_Master' on my slave is always 0. But due to pretty high RPS (about 15k) some lag (may be less than second) is still present. And it might, that max_replication_lag setting will not help me. 

May be there is some another approach in building cluster of mysql servers? As i understand, in master-slave(s) schema slaves are always outdate, and so, i can't just do select on random server

[René Cannaò]

Hi Denis,

Please read the article that Satria linked: it describes how you should send to replicas only SELECTs that can tolerate some lag, even if less than a second.

If that is still not good (no SELECTs can tolerate any lag) you may consider switching to Group Replication and tune transaction consistency (https://dev.mysql.com/doc/refman/8.0/en/group-replication-consistency-guarantees.html) , or Galera and tune causality checks (https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-sync-wait).

Beware that global synchronization is likely to increase your transactions latency.

Alternatively you can use ProxySQL causal consistency reads: https://proxysql.com/blog/proxysql-gtid-causal-reads/

Thanks,

René

--

You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/3fc5409a-cf0f-4e2d-9f8b-77f2791d5724n%40googlegroups.com.

[Denis Makovkin]

Thanks a lot for reply and information

[sridhar marella]

Hi Guys,

SInce renecannao mentioned https://proxysql.com/blog/proxysql-gtid-causal-reads/ . I have a doubt regarding GTID.

Suppose if client does several queries in one connection. Does proxysql commit and create GTID after all queries are finished? Or it will create GTID after every query execution?

[René Cannaò]

From https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html :

> A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed

In other words, a GTID is generated when a transaction commits, not for each query

--

You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/9ee86f2e-fa78-49d5-b4eb-b4a126baa548n%40googlegroups.com.

[sridhar marella]

Then, how can we deal with such types of queries, can we tell proxysql to route all the traffic to Master if there is atleast one write query in a transaction?

[Paul S]

I tried twice to write a reply here, but it got deleted immediately. Are there any rules that I need to follow when posting replies?

[Paul S]

I wrote my question in a google document instead. If anyone could take a look and see if you can help, I'd much appreciate it.
https://docs.google.com/document/d/1IMyn9HOsl3V0D-Ql1ty4RJ1NNMLarIaBy2m7gHUQpVU/edit?usp=sharing

Thanks

[Manish Kumar]

Team ,

As per the table stracture in Proxysql DEFAULT 3306, how we can change ir to 3388. How we can do alter and modify with 3388.

{code}

CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) ) |
+---------------+---------------------------------------

{code}

[René Cannaò]

Hi Paul,

There is no filter for your messages, they were not deleted, I am not sure what happened.

About your question, it is difficult to know exactly what is going on without a reproducible test case, but I suspect that what is happening is the following:

* JDBC driver doesn't start an explicit transaction (with START TRANSACTION), but it uses autocommit=0

* with default proxysql configuration (mysql-enforce_autocommit_on_reads=false) proxysql will continue uses autocommit=1 for SELECT statements until the first not SELECT statement is issue: at that point autocommit is switched to 0

In other words, the transaction starts at the first statement that is not a SELECT, therefore the first few SELECT statements may be going to the reader

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/89bf1888-4449-45b0-8b66-da059bfa7a07n%40googlegroups.com.

[René Cannaò]

Hi Manish,

You don't have to change the default in the table, you simple need to specify the port when adding record to the table.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/56f458e3-9c37-4a3c-823c-bdfc50e74eefn%40googlegroups.com.

[Manish Kumar]

Ok, thanks.

--

Thanks & Regards
Manish Kumar Rai
Certified Oracle Mysql DBA

Certified MongoDB DBA

Certified Linux RHCE
Mob:-9899856382

[Paul S]

@re...@proxysql.com thanks for the quick response. I will see if changing the  mysql-enforce_autocommit_on_reads  setting can help, and will also check my implementation to ensure it is handling the transactions correctly.

[Paul S]

Question: is there any way to force ProxySQL to always route SQL statements to the write hostgroup if autocommit=0, and in all other cases use the rules defined in the mysql_query_rules table?

Simple use case: I call setAutoCommit(false) on the java.sql.Connection object, and then perform the following statements in a single transaction:

1. SELECT * from tableA;

2. UPDATE tableA set columnB = 'valueC';

3. SELECT * from tableA;

Finally, I call commit() on the java.sql.Connection object.

#1 can go to either master or slave - it doesn't matter

#2 obviously must go to master (write hostgroup), which it always does in my experiments

I want #3 to go to master as well, because it is part of the same transaction, but it is always going to slave.

I have considered setting mysql-enforce_autocommit_on_reads=true, but after learning about that setting I'm reluctant to change it as it seems it might send some of my UPDATE statements to the slave, which is definitely not what I want.

If ProxySQL could be aware of the autocommit status, and if it is OFF then just send everything to the write hostgroup (or even a specific host) then I think that would solve my problem.

Will experiment with mysql-enforce_autocommit_on_reads tomorrow.

[René Cannaò]

Just do not use generic query rules for read write split.

You solve your concerns if you configure proxysql to send to the writer ALL the traffic with the exception of the handful SELECT statements that can go to replicas.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/d37ced2c-24a9-48b3-ad5a-c2e11041af91n%40googlegroups.com.