ProxySQL 1.4.5/MySQL Group Replication and readers hostgroup

[bargoud]

Hello,

I've seen a strange result with ProxySQL and MySQL Group Replication using native support. My version is:

$ proxysql -V
ProxySQL version 1.4.5-19-g3b9e408, codename Truls

I've a MySQL Group Replication cluster with 3 nodes, but for my example, I've shutted down 2/3 nodes to keep only one active. The result is the same with all nodes actives.

For the website, I'm using Prestashop, but that's not realy important I guess.

I'm using native Group Replication support with 3 servers:

mysql> select * from mysql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 10.0.0.101 | 3306 | ONLINE | 1      | 0           | 2500            | 0                   | 0       | 0              |         |
| 2            | 10.0.0.102 | 3306 | ONLINE | 1      | 0           | 2500            | 0                   | 0       | 0              |         |
| 2            | 10.0.0.103 | 3306 | ONLINE | 1      | 0           | 2500            | 0                   | 0       | 0              |         |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

And with one group replication hostgroup:

mysql> select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 2                | 4                       | 3                | 1                 | 1      | 1           | 1                     | 0                       | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

I've stopped my nodes 10.0.0.101 and 10.0.0.102, so I can see only 10.0.0.103 is online in hostsgroups 2 and 3 (writers and readers):

mysql> select * from stats_mysql_connection_pool;
+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host   | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 2         | 10.0.0.103 | 3306     | ONLINE       | 0        | 6        | 8      | 0       | 315918  | 160026276       | 407645486       | 534        |
| 2         | 10.0.0.101 | 3306     | OFFLINE_HARD | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 1309       |
| 4         | 10.0.0.101 | 3306     | SHUNNED      | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 1309       |
| 3         | 10.0.0.103 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 534        |
| 3         | 10.0.0.102 | 3306     | OFFLINE_HARD | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 691        |
| 1         | 10.0.0.102 | 3306     | SHUNNED      | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 691        |
+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

My client users are on default_hostgroup_id 2 (writers), so without query rules, everything is working nice.

If I add these 2 query rules:

mysql> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',2,1), (2,1,'^SELECT',3,1);                                                                                 Query OK, 2 rows affected (0.00 sec)

I can see strange results on my website (empty Category pages). That's strange because to make the example more simple, I only have 10.0.0.103 (which is the master node) on both writers and readers hostgroup, so all queries are routed to the same node.

I just need to delete mysql_query_rules and everything is OK on the website. I've also tried to add only the "SELECT FOR UPDATE" rule and everything is OK. I've only strange results with queries routed to the readers hostgroup.

The problem is the same with all nodes online, but I would like to be sure this wasn't a replication problem, that's why I've stopped 2/3 DB nodes to keep only 1 active.

[renecannao]

Hi @decanet . I think the fact you are using group replication is not relevant, because you are having the same problem also with just one server. I would assume that, because you are sending the read on the same server but the resultset is empty, you are probably using transaction:

• the write happens in a connection within a transaction
• the read goes to the same server, but on a different connection
• the read doesn't see the data written in the still open transaction

Please check the value of mysql_users.transaction_persistent.

Thanks

[bargoud]

Hi @renecannao Thank you for your answer. I think you're on the good way, but transaction_persistent is 1 for my user:

mysql> select * from mysql_users;
+----------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username       | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| my_user         | my_passwd                                | 1      | 0       | 2                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

I've also the same problem in the Prestashop Back Office, with these queries:

SELECT SQL_CALC_FOUND_ROWS 
and
SELECT FOUND_ROWS();

That's why I think you're on the good way.

[renecannao]

Hi @decanet . Without checking the full set of queries, it is difficult to understand what is happening. Is it possible to have the whole resultset from stats_mysql_query_digest and stats_mysql_global ? Thanks

[bargoud]

Here you can find the stats_mysql_query_digest : stats_mysql_query_digest.txt

And my stats_mysql_global:

+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| ProxySQL_Uptime              | 180898         |
| Active_Transactions          | 0              |
| Client_Connections_aborted   | 1225           |
| Client_Connections_connected | 0              |
| Client_Connections_created   | 16866          |
| Server_Connections_aborted   | 132            |
| Server_Connections_connected | 5              |
| Server_Connections_created   | 693            |
| Server_Connections_delayed   | 0              |
| Client_Connections_non_idle  | 0              |
| Queries_backends_bytes_recv  | 2222783237     |
| Queries_backends_bytes_sent  | 5866492759     |
| Query_Processor_time_nsec    | 0              |
| Backend_query_time_nsec      | 0              |
| mysql_backend_buffers_bytes  | 0              |
| mysql_frontend_buffers_bytes | 0              |
| mysql_session_internal_bytes | 11552          |
| Com_autocommit               | 4              |
| Com_autocommit_filtered      | 4              |
| Com_commit                   | 1              |
| Com_commit_filtered          | 1              |
| Com_rollback                 | 0              |
| Com_rollback_filtered        | 0              |
| Com_backend_change_user      | 22749          |
| Com_backend_init_db          | 383            |
| Com_backend_set_names        | 2              |
| Com_frontend_init_db         | 130            |
| Com_frontend_set_names       | 12359          |
| Com_frontend_use_db          | 1              |
| Com_backend_stmt_prepare     | 0              |
| Com_backend_stmt_execute     | 0              |
| Com_backend_stmt_close       | 0              |
| Com_frontend_stmt_prepare    | 0              |
| Com_frontend_stmt_execute    | 0              |
| Com_frontend_stmt_close      | 0              |
| Mirror_concurrency           | 0              |
| Mirror_queue_length          | 0              |
| Questions                    | 2146756        |
| Slow_queries                 | 608            |
| Servers_table_version        | 311            |
| MySQL_Thread_Workers         | 4              |
| MySQL_Monitor_Workers        | 8              |
| ConnPool_get_conn_immediate  | 2464           |
| ConnPool_get_conn_success    | 1577849        |
| ConnPool_get_conn_failure    | 180631         |
| SQLite3_memory_bytes         | 10979288       |
| ConnPool_memory_bytes        | 623248         |
| Stmt_Client_Active_Total     | 0              |
| Stmt_Client_Active_Unique    | 0              |
| Stmt_Server_Active_Total     | 0              |
| Stmt_Server_Active_Unique    | 0              |
| Stmt_Max_Stmt_id             | 1              |
| Stmt_Cached                  | 0              |
| Query_Cache_Memory_bytes     | 0              |
| Query_Cache_count_GET        | 0              |
| Query_Cache_count_GET_OK     | 0              |
| Query_Cache_count_SET        | 0              |
| Query_Cache_bytes_IN         | 0              |
| Query_Cache_bytes_OUT        | 0              |
| Query_Cache_Purged           | 0              |
| Query_Cache_Entries          | 0              |
+------------------------------+----------------+

That's strange I've SELECT queries on hostgroup_id 2 isn't it?

[renecannao]

hi @decanet , I think I found the issue. From your query digests I can see that you create temporary tables (ps_product_reductions and ps_cat_restriction) on hostgroup 2, but then you have reads from these 2 tables on hostgroup 3. Because the query to hostgroup 3 goes on a different connection (even if against the same server), the temporary table doesn't exist. I think you should have some error message in proxysql's error log that points this as well. I strongly recommend this blog post, where I try to highlight that trying to send all SELECT to a different server (or to a different connection, in your case) is a bad approach. You should try to define what needs to be sent to a "slave", instead of using very generic rules.

[bargoud]

OK, you're right. Prestashop can't use simple query_rules. For other users who are working with Prestashop, I've found a workaround. Prestashop have a native slave DB server support. I've enabled a simple split on two IP addresses (as described with differents ports on https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO) ). I've added a query rule to redirect all queries on the second IP/Port to the readers hostgroup and I've edit the Prestashop's file /config/db_slave_server.inc.php with:

return array(
    array('server' => 'second_ip', 'user' => 'user_db', 'password' => 'pwd_db', 'database' => 'my_db'),
);

Everything is OK now. Prestashop is an example of simple rules limit.

Thank you again for your help @renecannao

[utdrmac]

@decanet Can this issue be closed?

[bargoud]

@utdrmac yes you can