**This is a production setup and I have to perform a failover from Master A to new potential master B.** **MySQL Version is 5.7 and CentOS 7 is the OS.** I have a simple master slave setup with A as Master and B,C,D,E,F as async slaves. ProxySQL Cluster is also configured. Master A (writer in ProxySQL) and Slaves B and C (readers in ProxySQL) are added to ProxySQL. On all the slaves **log_slave_updates** is **ON** and **log_bin** is also **ON**. On **Master A** **read_only=0** and **Slaves B,C,D,E,F** have **read_only=1**. This is a **GTID** based replication with **master_auto_position = 0**. **A --> B,C,D,E,F this is the current replication topology** Now, I know for failover I have to choose either slave B or slave C because they are on proxy. I choose B as my new potential master. So, I have to move all the slaves from current Master A to new Master C. So the replication topology will be like this **B --> A,C,D,E,F -- proposed new topology** **My question is how do I move the slaves from Master A to new potential Master B?** **How should I use the coordinates from new Master B? What procedure should I follow?** Once I move the slaves I guess failover will be easy, I just tweak the **read_only** flag on old master and new master and proxy will take care of the connections. I would really appreciate if a detailed procedure with explanation is provided. I have gone through following links but couldn't understand the process https://stackoverflow.com/questions/43960577/mysql-failover-how-to-choose-slave-as-new-master https://serverfault.com/questions/303669/how-to-actually-use-mysql-slave-as-soon-the-master-is-failover-or-got-burnt https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html

As I understand **ProxySQL** has support for *SSL encryption* for inbound and outbound traffic (front/backend) and supports *password hashing*. However back in 2017, one had direct database access: https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql/212991 According to *Icarus*'s answer, it would be rather easy for someone who broke into the hosting server to gain access to all endpoints listed on this database by making a select call directly to it. Is there any way of encrypting the SQLite3 DB? Or perhaps of encrypting the data stored in the database (similar to how SSL is used for in-transit data)? Basically looking for *data-at-rest* securing strategies. Perhaps this is already implemented in recent versions? I could not find any documentation supporting it, but it did seem that may be the case: https://github.com/sysown/proxysql/blob/v2.0.13/lib/ProxySQL_Admin.cpp Which, if I understood correctly implements SHA1 encryption: https://stackoverflow.com/questions/3179021/sha1-hashing-in-sqlite-how If this is correct, is this restricted to password hashing? Or is it part of the SSL encryption implementation? Or is there actually a way of enabling encrypted "DISK" read/writes? Is this by any chance implemented by default on v2+ builds? Ultimately, I plan on encrypting the volume this is installed in, and have a second proxy layer so that I don't expose endpoint information; this would be an additional layer of protection. Thanks, I appreciate any insights you may have!

I am trying to perform the initial configuration in proxysql and I found that while doing the Configure monitoring, I am unable to find the variable mysql-monitor_username in the global_variables table. The article says that we need to execute the update statement in the mysql database but I don't see that option existing already and I cannot make changes to the information_schema.global_variables table because basically that's a view. Can someone please help me here in completing this step? Reference Link : https://proxysql.com/documentation/ProxySQL-Configuration/

What are your strategies when a MySQL/MariaDB database server grows to have too much traffic for a single host to handle, i.e. scaling CPU/RAM is not an option anymore? Do you deploy ProxySQL to start splitting the traffic according to some rule to two different hosts? What would the rule be, and how would you split the data? Has anyone migrated to TiDB? In that case, what was the strategy to detect if the SQL your app uses is fully compatible with TiDB?

I'm searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I'm testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, ...)? With Sysbench it is possible if setting the --mysql-ignore-errors=all parameter. (from Comment) I'll be calling that SP from a custom lua script, where I'll test the 'error 2013' condition and in that case I'll rerun that query. Does this make sense, or the value of @err set by the error handler can't be passed to the script, because the session will just die, when the mysqld will get killed? DELIMITER // CREATE PROCEDURE sbtest.InsertIntoTable ( IN trnid INT, IN unixtime INT, OUT err INT) BEGIN DECLARE CONTINUE HANDLER FOR 2013 SET @err = 1; INSERT INTO sbtest.failover_test ( node, trn_id, unix_time ) VALUES (@@hostname, trnid, unixtime); END// my table: failover_test | CREATE TABLE failover_test ( id int NOT NULL AUTO_INCREMENT, node varchar(255) DEFAULT NULL, trn_id int DEFAULT NULL, unix_time int DEFAULT NULL, created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=116837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | -- CALL sbtest.InsertIntoTable(1, 1599207191, @err);SELECT @err

We currently have replication working through haproxy so that if a node goes down replication continues with one of the other nodes. We're trying to completely replace haproxy with proxysql. Due to this I'm trying to replicate this functionality in proxysql. Test: 3 on-prem nodes setup as a Percona Galera cluster. 1 on-prem ProxySQL node pointing to the Percona cluster 1 aws EC2 node with Percona installed. I setup replication between the EC2 node, and one of the 3 on-prem nodes without issue. I can also connect to the DB through proxysql from the aws node. When ever I stop the slave, and run the following: CHANGE MASTER TO MASTER_HOST="XX.XXX.XXX.193", MASTER_USER="[username]", MASTER_PASSWORD="[password]", MASTER_AUTO_POSITION = 1, MASTER_PORT = 6033; I get the following error when I run show slave status: Slave_IO_State: Waiting to reconnect after a failed registration on master Slave_IO_Running: Connecting Slave_SQL_Running: Yes Last_IO_Errno: 1597 Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Lost connection to MySQL server during query (Errno: 2013) I can then swap back to directly connecting between DB nodes and replication works fine. Is the mysql backend of proxysql preventing this from working in the same way it does with haproxy?

Hello Community Members, We have recently migrated our DB server to Azure from Onprem, and we are struggling with memory fragmentation in MariaDB Database. DB is consuming more than 2 times of buffer pool memory allocated. We see this fragmentation only on primary master. This behaviour is only observed when app connectivity via Proxysql to MariaDB. For sure the culprit is proxysql. We have one master with 2 standby databases in DB topology and 4 proxysql nodes one being hostgroup 1 and rest 3 with hostgroup 2. We have enabled the multiplexing, We initiall have heavy memory fragmentation, which got limited after changing the following variables in proxysql : During Migration : -------------------------- mysql-max_stmts_per_connection = 100; and max_prepared_stmt_count = 500000 Memory fragmentation was quite huge and we have to perform switch over the master database and bounce the DB post sswitchover to release the memory occupied by MariaDB. Post Migration : -------------------- mysql-max_stmts_per_connection = 50; at proxysql and max_prepared_stmt_count = 250000, at mariadb this has slowed down the fragmentation, but still mariadb consuming the memory based on the prepared statements. ProxySQL doesn't automatically close prepared statements unless the client explicitly requests it ? Connection Multiplexing in ProxySQL may be preventing statements from being properly closed. The application might not be explicitly closing prepared statements, relying on ProxySQL to handle cleanup. If multiplexing is enabled, ProxySQL keeps connections open and doesn't send COM_STMT_CLOSE properly. In our case, we have enabled multiplexing. No Query rules in place. sh-4.4$ cat /var/lib/mysql/my.cnf |grep buffer_pool;free -g;top |grep mysqld innodb_buffer_pool_size = 96G total used free shared buff/cache available Mem: 660 378 269 0 13 279 Swap: 0 0 0 1 mysql 20 0 386.8g 374.2g 28312 S 313.3 56.6 13770:26 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 320.0 56.6 13770:36 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 288.7 56.6 13770:44 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 236.0 56.6 13770:51 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 336.3 56.6 13771:02 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 321.9 56.6 13771:11 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 309.3 56.6 13771:21 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 298.0 56.6 13771:30 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 279.7 56.6 13771:38 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 152.0 56.6 13771:42 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 172.1 56.6 13771:48 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 238.0 56.6 13771:55 mysqld\ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Memory_used';\ +---------------+--------------+\ | Variable_name | Value |\ +---------------+--------------+\ | Memory_used | 117572547280 |\ +---------------+--------------+\ 1 row in set (0.001 sec) MariaDB [(none)]> select 117572547280/1024/1024/1024 'Memory_used in GB';\ +-------------------+\ | Memory_used in GB |\ +-------------------+\ | 109.497967436910 |\ +-------------------+\ 1 row in set (0.000 sec)\ MariaDB [(none)]>\ MariaDB [(none)]> SELECT -> NOW() AS Query_Time,\ -> -> -- Temporary Tables Statistics\ -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') AS Tmp_Tables_Created, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS Tmp_Tables_Disk, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_memory_tables') AS Tmp_Tables_Memory, -> -> -- Open Tables & Table Cache -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Open_tables') AS Open_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Opened_tables') AS Opened_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_open_cache') AS Table_Open_Cache, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_definition_cache') AS Table_Definition_Cache, -> -> -- Prepared Statements Memory Usage -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') AS Active_Prepared_Statements, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_prepare_sql') AS Total_Prepares, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_deallocate_sql') AS Total_Deallocates, -> -> -- Memory Consumed by Prepared Statements (Estimation) -> ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') * 512 / 1024, 2) AS Estimated_Memory_MB -> ; +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | Query_Time | Tmp_Tables_Created | Tmp_Tables_Disk | Tmp_Tables_Memory | Open_Tables | Opened_Tables | Table_Open_Cache | Table_Definition_Cache | Active_Prepared_Statements | Total_Prepares | Total_Deallocates | Estimated_Memory_MB | +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | 2025-02-25 08:49:26 | 17772934 | 1985203 | NULL | 400 | 39233 | 400 | 400 | 240131 | 0 | NULL | 120065.50 |\ +---------------------+--------------------+-----------------+-------------------+-------------+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ 1 row in set (0.003 sec) MariaDB [(none)]> Proxysql : ---------------- mysql> SELECT srv_host, srv_port, ConnUsed, ConnFree, ConnOK, Queries, -> ROUND(Queries / NULLIF(ConnOK, 0), 2) AS MultiplexingEfficiencyRatio -> FROM stats_mysql_connection_pool -> ORDER BY MultiplexingEfficiencyRatio ASC -> LIMIT 10; +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | srv_host | srv_port | ConnUsed | ConnFree | ConnOK | Queries | MultiplexingEfficiencyRatio |\ +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | 2c_oltp.mariadb | 3052 | 2 | 4 | 1555 | 55156669 | 35470.0 \ | | 2b_oltp.mariadb | 3052 | 2 | 6 | 1933 | 70648384 | 36548.0 \ | | 2a_oltp.mariadb | 3052 | 43 | 4 | 4099 | 150018800 | 36598.0 \ | | 2d_oltp.mariadb | 3052 | 0 | 4 | 2080 | 77161687 | 37096.0 | +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_connection_pool;\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------+ | 1 | 2a_oltp.mariadb | 3052 | ONLINE | 45 | 0 | 4097 | 0 | 62 | 149975306 | 0 | 48544471112 | 187097398415 | 270 | | 2 | 2c_oltp.mariadb | 3052 | ONLINE | 2 | 4 | 1555 | 0 | 10 | 55133002 | 0 | 32800700913 | 20589949783 | 421 | | 2 | 2b_oltp.mariadb | 3052 | ONLINE | 2 | 6 | 1933 | 0 | 14 | 70624781 | 0 | 41584296177 | 27213051703 | 272 | | 2 | 2d_oltp.mariadb | 3052 | ONLINE | 0 | 3 | 2079 | 0 | 13 | 77137911 | 0 | 45755921665 | 31073609951 | 168 | +-----------+-------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_global WHERE variable_name LIKE '%stmt%'; +---------------------------+----------------+\ | Variable_Name | Variable_Value |\ +---------------------------+----------------+\ | Com_backend_stmt_prepare | 63914669 |\ | Com_backend_stmt_execute | 233922211 |\ | Com_backend_stmt_close | 0 |\ | Com_frontend_stmt_prepare | 233883162 |\ | Com_frontend_stmt_execute | 233923781 |\ | Com_frontend_stmt_close | 233876578 |\ | Stmt_Client_Active_Total | 3 |\ | Stmt_Client_Active_Unique | 2 |\ | Stmt_Server_Active_Total | 93421 |\ | Stmt_Server_Active_Unique | 77596 |\ | Stmt_Max_Stmt_id | 174002 |\ | Stmt_Cached | 82713 |\ +---------------------------+----------------+\ 12 rows in set (0.01 sec) mysql> show variables like "%stmt%"; +--------------------------------+-------+\ | Variable_name | Value |\ +--------------------------------+-------+\ | mysql-max_stmts_cache | 10000 |\ | mysql-max_stmts_per_connection | 50 |\ +--------------------------------+-------+\ 2 rows in set (0.00 sec) mysql> MariaDB [(none)]> show status like 'Prepared_stmt_count';\ +---------------------+--------+\ | Variable_name | Value |\ +---------------------+--------+\ | Prepared_stmt_count | 220071 |\ +---------------------+--------+\ 1 row in set (0.000 sec) MariaDB [(none)]>\ mysql> SELECT\ -> username, schemaname, count(*)\ -> FROM -> stats_mysql_prepared_statements_info\ -> GROUP BY\ -> 1, 2\ -> ORDER BY\ -> 3 DESC;\ +----------+------------+----------+\ | username | schemaname | count(*) |\ +----------+------------+----------+\ | app | appprd | 87573 |\ +----------+------------+----------+\ 1 row in set (0.29 sec)\ mysql> Will proxysql disable multiplexing for all queries that have @ in their query_digest will disable multiplexing ??? We are using 2.5.5 version of proxysql and maridb 10.6.18. How we can stabilize this memory fragmentation permanently without any significant impact on performance?

I am setting up ProxySQL in front of a Percona MySQL server with 88 user accounts. I don't even know most of their passwords. Passwords are stored in MySQL as hashes but are stored in ProxySQL in plain text. How can I easily bring in all 88 accounts into ProxySQL at once? Or is there a pass-through authentication switch I can turn on?

I have the following setup: 1. Three node galera cluster (galera-1, galera-2, galera-3) 2. galera-2 is master to slave-1 (master-slave replication) 3. slave-1 is master to both slave-2 and slave-3 4. slave-1, slave-2 and slave-3 have read_only = 1 I have the following ProxySQL configuration: mysql_servers = ( { address = "galera-1" port = 3306 hostgroup = 2 }, { address = "galera-2" port = 3306 hostgroup = 2 }, { address = "galera-3" port = 3306 hostgroup = 2 }, { address = "galera-2" port = 3306 hostgroup = 5 max_replication_lag = 5 }, { address = "slave-1" port = 3306 hostgroup = 5 max_replication_lag = 5 }, { address = "slave-2" port = 3306 hostgroup = 5 max_replication_lag = 5 }, { address = "slave-3" port = 3306 hostgroup = 5 max_replication_lag = 5 } ) mysql_galera_hostgroups = ( { active = 1 backup_writer_hostgroup = 4 max_transactions_behind = 100 max_writers = 1 offline_hostgroup = 1 reader_hostgroup = 3 writer_hostgroup = 2 writer_is_also_reader = 0 } ) mysql_replication_hostgroups = ( { writer_hostgroup = 5 reader_hostgroup = 6 } ) With this configuration: - Available servers for **writing**: - hostgroup 2 (I will always be routing to this one) - hostgroup 5 - Available servers for **reading**: - hostgroup 3 - hostgroup 4 - hostgroup 6 How can I route **read** queries to hostgoups 3, 4 or 6 in a round-robin fashion? mysql_query_rules = ( { rule_id = 100 active = 1 match_pattern = "^SELECT .* FOR UPDATE" destination_hostgroup = 2 apply = 1 }, { rule_id = 200 active = 1 match_pattern = "^SELECT .*" destination_hostgroup = 6 apply = 1 }, { rule_id = 300 active = 1 match_pattern = ".*" destination_hostgroup = 2 apply = 1 } ) I've tried mixing groups between Galera and MySQL replication but things get mixed up quite badly. Is there a way to create a hostgroup of hostgroups so I can route to the "group of groups"?

We have encountered rather weird behavior which is very hard to reproduce. When inserting specific **strings that resemble scientific notation** into a varchar column, it would be inserted as 65 of 9 which would indicate an overflow, meaning, that our string is randomly being inserted as a decimal which is way too big. Another weird part of this is that even with a same string, behavior varies. Most of the time, it would be inserted properly, yet on some occasions the very same string would be inserted as 99999999999999999999999999999999999999999999999999999999999999999. Example of incorrectly inserted values 4840e430eac9f22a5e8609a1c95faaeb8c921f66e24e55cf839a00eb35790c00 2e540795-afe5-4644-a3d0-2aaae007c76e These values are inserted into columns that were created with either

columnName varchar(510) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

or

columnName varchar(1022) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL

We have determined the issue **likely** to be on the side of MySQL, rather than Laravel or ProxySQL, as query being sent to MySQL is like this (according to query logs produced by Laravel and ProxySQL). Below is an example of a query from taken directly from said log.

insert into table1 (irrelevantColumn, columnName, UUID) 
values (
 'someString', 
 '4840e430eac9f22a5e8609a1c95faaeb8c921f66e24e55cf839a00eb35790c00', 
 '2e540795-afe5-4644-a3d0-2aaae007c76e'
)

We would like to determine what causes this behavior and how it can be prevented. Stack used: - Laravel 11 + Octane (strict mode enabled in the config) - ProxySQL 2.7.1 - AWS RDS MySQL version: 8.0.35, Engine being used is InnoDB Laravel starts a session with the following parameters `SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci', time_zone='+00:00', SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'`

I have old data in single mysql database (~700GB), and want to move to ProxySQL with 3 mysql nodes - separate 2 big big tables on node 2 and 3, while rest of data on node 1. I readed how to do this by simply mysqldump, but I have lack of disk space, and dump didn't fit, so I wonder if there is another way to copy data.

I'm deploying ProxySQL on Docker. When I try connecting to my database via ProxySQL, MariaDB rejected my login attempt as follows: Access denied for user 'usarr'@'172.18.0.1' (172.18.0.1 is the container's "internal" IP address.) The Docker host itself has LAN address of 192.168.9.22. And the user configured in MariaDB is 'usarr'@'192.168.9.%' How do I configure ProxySQL to connect as usarr@192.168.9.22? I've tried reading the (very confusing) documentation for ProxySQL and couldn't figure out how.

I just came across ProxySQL. I have one simple question, if I were to use ProxySQL, do I still need to set up Replication(master-slave) first for MySQL server? Because there is hostgroup(0,1) in ProxySQL which to determine which MySQL server master and slave based on the hostgroups. Or just configure in ProxySQL? Kindly clarify on which steps should I use. Thank you.

I have stuck with the replication lag problem. I am trying to refactor my DB infrastructure. The first step was read-write split. I used ProxySQL то implement it. Now I have masterDb where all INSERT and UPDATES executing and 2 replicas where I route all SELECT. But the main problem I faced with, replication lag. Because usually when you change something in DB you immediately read data and expect to have new data in the query result, but with replication lag, I receive outdated data. After googling I found this article https://www.percona.com/blog/2018/11/29/mysql-high-availability-stale-reads-and-how-to-fix-them/ and according to this info the best option in 2018 was "ProxySQL 2.0 GTID consistent reads" Any updates from 2018? Maybe you guys know a better solution? Please share!

I'm going to install Percona XtraDB Cluster, and I have two questions about it: **1-** Is it the last step? https://www.percona.com/doc/percona-xtradb-cluster/LATEST/verify.html Or installing ProxySQL is mandatory? I have only 3 nodes and not planning to add an extra node only for ProxySQL! And I think I don't even need it, my round-robin load-balancer is sending the requests to these servers randomly (without health controlling). So if a node is down it can't even read/write to the DB. in a nutshell, my application nodes are the same as my DB nodes, and a load-balancer in front of them, So I think I don't need ProxySQL, Am I? _______________________________________________ **2-** What happens if all 3 nodes are down together, I think if this situation happens in a common Group Replication of MySQL, all the cluster collapses and all nodes are being removed from the cluster and we need to reconfigure the cluster manually again, Is the same problem exists in PXC or not?

Can be configured garbd with proxysql + MySQL Replication?, I installed proxysql with 1 master and two slaves using MySQL replication but I was disputed about to connect garb with proxysql how I was done with galera cluster

We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5. The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database structure is bad. The client doesn't care about query optimization, he just wants to lower CPU usage, he runs locust , a load testing tool, give it 1500 users and 1000 concurrent, sees the CPU at in the 90s % and says this is bad. The search functionality is the problem he said. Again I'm telling you, the queries are bad, I just got hired and doing what I can, but is he looking at the right metrics? What have I tried to do other than fixing queries: Adding indexes, there were no indexes, also my.cnf innodb_stats_on_metadata = 0 innodb_buffer_pool_dump_at_shutdown =1 innodb_buffer_pool_load_at_startup =1 ## innodb_buffer_pool_instances should match the number of cores on the server innodb_log_file_size = 2047M innodb_flush_method=O_DIRECT sync_binlog=0 innodb_thread_concurrency = 48 innodb_read_io_threads = 24 innodb_write_io_threads = 24 ## set innodb_buffer_pool to 50% to 70% of the ram on the server so if the server have 32G, then set it to 16G. 50% is a good start ## turn off slow query log, if you're not logging, and only log for small durations when you want to monitor things slow_query_log =0 I tried to optimize what I can from the slow query log as well. Also trying to use proxySQL, which I heard it intercepts the query and sends 1/10 of the queries to the server, because of caching. The client has a streaming app, during peak events, he hits 7k users, not sure if that counts as concurrent users and highly doubt that all of them would search. They have an on-premise server DL380 Gen10 2x Intel Xeon Silver 4110 / 2.1 GHz 64 GB RAM 300 GB HDD My questions: 1. Is there any measure one could take that I didn't know about to reduce the CPU usage of search queries? 2. If I were to optimize all the queries, would I expect lower CPU under such a load test? They want the CPU usage to drop to like 50%, I just feel that they're looking at the wrong metrics. They have the right to do so because it used to be that whenever cpu got to 100%, server would go down.

can I have same databases connected to two different proxysql configuration?

This article describes the set-up of MySQL cluster with one cluster node, two data nodes and one MySQL server. This article describes the configuration of ProxySQL where, MySQL servers are added into mysql_servers. Are the servers added in mysql_servers the MySQL server installed in the cluster? If yes, then there will be only one entry in mysql_servers and then, how do we define query rules to point to different host groups?

we are using MariaDB 10.2 with Galera plugin. Cluster consists of 4 nodes - 3 are busy service complex queries and 1 is more for a backup. Recently we came across ProxySQL and this sounds very interesting for us in terms of splitting insert queries to one server and selecting data from others. We are hoping to avoid deadlocks and autoincrement problems during high activity. But one of our developers has discovered a delay - when using ProxySQL he tries to insert a simple record and do instant select of that record. In general every second or third such select-request fails due to latency. If he adds tiny 0.01s delay - he never gets failure, as all galera nodes are able to sync within this time. Now the question is - is it possible to cache inserted data with ProxySQL, so instant "select" would be returned from the cache, at least for the first 0.01seconds. Or are there any other ways or suggestions in this scenario? Thank you. :)