Comments on: MySQL Sharding with ProxySQL https://www.percona.com/blog/mysql-sharding-with-proxysql/ Fri, 12 Jul 2019 08:31:03 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: scarlett https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10970973 Fri, 12 Jul 2019 08:31:03 +0000 https://www.percona.com/blog/?p=37753#comment-10970973 We use proxysql occured very serious accidents for several times, so we had a test, after that we found three main problems:
1. we killed the procedure of Back-end mysql, but the state of that machine was still SHUNNED for more than half a day, plus the real connecting request was dispached to that machine which was killed the mysql procedure
2. proxysql.Client_Connections_connected/stats=stats_mysql_global was more than default max value 2048, but our business concurrent was far from this value with php short links , why that happened?
3. We found no log printed for more than one hour and half, so we can’t analyze the accidents through the log at certain times

thank you for taking proxysql to us, best wishes!

]]> By: scarlett https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10970972 Fri, 12 Jul 2019 08:25:49 +0000 https://www.percona.com/blog/?p=37753#comment-10970972 We use proxysql occured very serious accidents for several times, so we had a test, after that we found three main problems:
1. we killed the procedure of Back-end mysql, but the state of that machine was still SHUNNED for more than half a day, plus the real connecting request was dispached to that machine which was killed the mysql procedure
2. proxysql.Client_Connections_connected/stats=stats_mysql_global was more than default max value 2048, but our business concurrent was far from this value with php short links , why that happened?
3. We found no log printed for more than one hour and half, so we can’t analyze the accidents at certain times

business

]]> By: Rajan https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10969960 Thu, 15 Nov 2018 19:30:04 +0000 https://www.percona.com/blog/?p=37753#comment-10969960 i followed step by step but not able to login

mysql -u user_shardRW -p -h hostname -P 6033 -e “SELECT name,population from world.City WHERE Continent=’Europe’ and CountryCode=’ITA’ order by population desc limit 1;”

]]> By: Marco Tusa https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10968592 Wed, 25 Oct 2017 09:33:31 +0000 https://www.percona.com/blog/?p=37753#comment-10968592 Hi Luke,
I think you may find additional answers here https://www.slideshare.net/marcotusa/proxysql-sharding about limitations see slide 83
regards

]]> By: Luke https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10968589 Wed, 25 Oct 2017 03:43:20 +0000 https://www.percona.com/blog/?p=37753#comment-10968589 Thanks for your quick response.
1, If you mean you just create this QR for this specified query,then missing \3 is OK.
2, I understand how ProxySQL handles the transformation and just think it’s a little weird if i need to write the original SQL in my application.

As i know, ProxySQL can only route one SQL to one backend MySQL instance. If I need to query multi shards in one SQL(e.g. Continent in (‘Asia’,’Europe’)), then ProxySQL can not push down the SQL and merge the results from each shard and finally return to user, is that right?

]]> By: Marco Tusa https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10968584 Tue, 24 Oct 2017 12:57:01 +0000 https://www.percona.com/blog/?p=37753#comment-10968584 Luke, thanks to take some time to read the article.
I will answer to both questions here.

In the example I had done above, I had explain that you need to create several schemas respecting the continent:
Create schema [Asia|Europe|North_America|Africa];
As such a query like :
SELECT name,population from world.City WHERE Continent='Europe' and CountryCode='ITA' order by population desc limit 1;
will point to the EUROPE schema, not to the table city.
To be clear the above become:

Original :SELECT name,population from world.City WHERE Continent=’Europe’ and CountryCode=’ITA’ order by population desc limit 1;
Transformed :SELECT name,population from Europe.City WHERE ?=? and CountryCode=? order by population desc limit ?

Finally if you read carefully the regex you will see that group 3 is not needed and not included, here the breakdown :Group Group 1. 7-23 name,population
Group 2. 34-38 City
Group 3. 39-46 WHERE
Group 4. 57-63 Europe
Group 5. 65-120 and CountryCode='ITA' order by population desc limit 1;

Group 3 is the WHERE , and I don’t need it given it is already included in the replace_pattern:
"SELECT \1 from \4.\2 WHERE 1=1 \5"

Hope this will help you to better understand the article.

]]> By: Luke https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10968582 Tue, 24 Oct 2017 08:02:06 +0000 https://www.percona.com/blog/?p=37753#comment-10968582 Besides, I think the invasion to the SQL seems a bit too much, as the column Continent not even exists in the table city.

]]> By: Luke https://www.percona.com/blog/mysql-sharding-with-proxysql/#comment-10968581 Tue, 24 Oct 2017 07:55:11 +0000 https://www.percona.com/blog/?p=37753#comment-10968581 There must be something wrong with the following QR:
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (31,1,’user_shardRW’,”^SELECT\s*(.*)\s*from\s*world.(\S*)\s(.*).*Continent='(\S*)’\s*(\s*.*)$”,”SELECT \1 from \4.\2 WHERE 1=1 \5″,1);

\3 is missing…

]]>