AWS RDS MySQL master/slave setup has a problem with --read-only option

[Sigurður Guðbrandsson]

Hi,

I'm having a problem with getting the read/write split working in ProxySQL.

I get the following error when directing my application to ProxySQL:

2016-12-28 12:09:25 MySQL_Session.cpp:2234:handler(): [WARNING] Error during query on (20,read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement

2016-12-28 12:09:25 MySQL_Session.cpp:2256:handler(): [WARNING] Retrying query.

2016-12-28 12:09:25 MySQL_Session.cpp:2234:handler(): [WARNING] Error during query on (20,read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement

Here's the current config for the servers:

mysql> select * from mysql_servers;

+--------------+--------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| hostgroup_id | hostname                                         | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+--------------+--------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 10           | write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com  | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com | 3306 | ONLINE | 100    | 0           | 1000            | 30                  | 0       | 0              |         |

| 20           | write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com  | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+--------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.01 sec)

Here's the config for the replication group:

mysql> select * from mysql_replication_hostgroups;

+------------------+------------------+-------------+

| writer_hostgroup | reader_hostgroup | comment     |

+------------------+------------------+-------------+

| 10               | 20               | Replication |

+------------------+------------------+-------------+

1 row in set (0.00 sec)

I'd like to get a read/write split working with the RDS config.

Rene, can you help me debug this so I can get it working?

Thanks!

/Siggi

[René Cannaò]

Hi Siggy,

Could you please share also the content of mysql_users (without password), and mysql_query_rules ?

Also, what version are you using?

Thanks,

René

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+1

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sigurður Guðbrandsson]

Hi Rene,

Thanks for the quick reply!

Here's the users table:

+----------------+---------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

| username       | password                        | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |

+----------------+---------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

| webserver     | hidden                          | 1      | 0       | 20                | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |

| backup         | hidden                           | 1      | 0       | 20                | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |

| admin           | hidden                              | 1      | 0       | 20                |                     | 0             | 0                      | 0            | 1       | 1        | 10000           |

+----------------+---------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

Here's the version and host information

ubuntu@ip-172-31-3-120:~$ proxysql --version

ProxySQL version 1.3.1-2-gc762ae9, codename Truls

ubuntu@ip-172-31-3-120:~$ uname -a

Linux ip-172-31-3-120 4.4.0-53-generic #74-Ubuntu SMP Fri Dec 2 15:59:10 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

ubuntu@ip-172-31-3-120:~$ cat /etc/lsb-release 

DISTRIB_ID=Ubuntu

DISTRIB_RELEASE=16.04

DISTRIB_CODENAME=xenial

DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS"

I set up a pretty vanilla configuration (or so I thought).

Anything else I can provide?

/Siggy

ᐧ

[René Cannaò]

Hi Siggy,

Your users have default_hostgroup=20 . That means that unless there are rules to route traffic on different hostgroups, all traffic go to the default hostgroup (that in your case is the read pool).

In other words, the current configuration is trying to send all traffic to read pool.

You should configure the write pool as the default (default_hostgroup=10) and then configure rules to send some/all SELECTs to slave.

Although this guide is not full exhaustive, I recommend to start reading this mini howto:
https://github.com/sysown/proxysql/blob/master/doc/configuration_howto.md

Thanks

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+1

[Sigurður Guðbrandsson]

Hi René,

Thanks.

I thought there was some auto-detection magic going on in ProxySQL that would figure out what is a read server and what is a write server..

Guess I was wrong there.

I'll try what you suggested and update this thread with the results.

/Siggy

ᐧ

[René Cannaò]

Siggy,

ProxySQL can automatically understand which servers has read_only =0 o 1 and configure them as writer or readers (and even add the writer in the reader group if configured so), but doesn't automatically perform read/write split: query routing need to be explicitly configured, and can be very granular using regular expressions.

Thanks,

René

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+1

[Sigurður Guðbrandsson]

René,

I see.

Do you know of documentation on learning the basics of ProxySQL .. tutorials with different scenarios, etc?

I set up ProxySQL like you mentioned:

1. Users default to the write group

2. All select statements go to the read group

3. Set persistent transactions to all the users (so they will read from the server if they are in a transaction)

Our RDS instance had an issue yesterday and again today, so to minimize downtime I just set up all the servers to use ProxySQL as the gateway .. it started routing traffic and everything seemed to work brilliantly.

Thanks for the software René - it's a business-saver.

I want to follow up sometime soon with a small tutorial for a simple setup, so others can get this up and running quickly.

/Siggi

ᐧ

[Sigurður Guðbrandsson]

Unfortunately, I had to stop this configuration yesterday.

There were some select queries that crashed the connection, so I'm running all queries again on the main server until I figure out why the connection crashes.

Rene, what does the proxy do when I enter a transaction?
Does it hold the transaction on one server (the writer)?

(I couldn't see it clearly in the docs)

[René Cannaò]

Siggy,

Documentation is a bit fragmented, and will be prioritized in the next 1-2 months.

For now, documentation is fragmented between:

* blog (or former) : proxysql.blogspot.com

* wiki : https://github.com/sysown/proxysql/wiki

* doc : https://github.com/sysown/proxysql/tree/master/doc

External blog, like:

* percona: https://www.percona.com/search?s=proxysql

* severalnines: http://severalnines.com/blog?keywords=proxysql

* pythian: https://www.pythian.com/blog/tag/proxysql/

* lefred: http://lefred.be/?s=proxysql

Or guides, like:

* percona: https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html

What do you mean by "select queries that crashed the connection" ?

About: "Does it hold the transaction on one server when it enter a transaction?"

The short answer is: by default it does not, but it is configurable : https://github.com/sysown/proxysql/wiki/Users-configuration#disable-routing-across-hostgroups-once-a-transaction-has-started-for-a-specific-user

When a transaction is started, query routing is not disabled by default (this will change in version 1.4, where query routing will automatically disabled). This is not a limitation, but a configuration, as described in the link above.
In other words, query routing using query rules is extremely powerful, but needs to be configured properly to avoid surprises.

[Sigurður Guðbrandsson]

Rene,

Thanks for the documentation links.

I've gone through some of them, but I'll go through the rest this weekend.

Here's an example from the logs.. we couldn't do some operations on the website, so I had to kill the connection (unfortunately).

---------------------------------------------------------------------

2016-12-28 17:36:18 MySQL_Session.cpp:2173:handler(): [WARNING] Retrying query.

2016-12-28 17:36:18 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:36:28 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:28 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:38 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:38 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:38 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:36:48 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:48 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:48 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:36:48 MySQL_Session.cpp:2173:handler(): [WARNING] Retrying query.

2016-12-28 17:36:58 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:58 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:36:58 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:36:58 MySQL_Session.cpp:2173:handler(): [WARNING] Retrying query.

2016-12-28 17:37:08 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:37:08 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:37:08 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:37:08 MySQL_Session.cpp:2173:handler(): [WARNING] Retrying query.

2016-12-28 17:37:18 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:37:18 MySQL_Monitor.cpp:1129:monitor_ping(): [ERROR] Server read.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com:3306 missed 3 heartbeats, shunning it and killing all the connections

2016-12-28 17:37:18 MySQL_Session.cpp:2165:handler(): [ERROR] Detected an offline server during query: write.cuzwjwkrhxjp.eu-west-1.rds.amazonaws.com, 3306

2016-12-28 17:37:18 MySQL_Session.cpp:2173:handler(): [WARNING] Retrying query.

---------------------------------------------------------------------

These were select queries (probably mixed in with write queries)

As for the transactions, I guessed that setting transaction_persistent to 1 for the user would disable the query routing.

Are you available for paid consultation and assistance in getting proxysql set up correctly?

/Siggi

ᐧ

ᐧ