Last active
April 27, 2021 07:33
-
-
Save miklos-szel/d33e04269286c43f99968d6c6e828f46 to your computer and use it in GitHub Desktop.
ProxyWeb Presentation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| docker run -h proxyweb --name proxyweb --network="host" -d proxyweb/proxyweb:latest | |
| git clone https://github.com/edmodo/proxyweb | |
| cd proxyweb | |
| make install | |
| docker run -h proxyweb --name proxyweb -p 5000:5000 -d proxyweb/proxyweb:latest | |
| mysql -h 127.0.0.1 -uadmin -padmin -P6032 | |
| set admin-admin_credentials="admin:admin;radmin:radmin"; | |
| load admin variables to runtime; save admin variables to disk; | |
| git clone https://github.com/edmodo/proxyweb.git | |
| cd proxyweb | |
| make compose-up | |
| ### configure the monitoring user: | |
| UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; | |
| UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'; | |
| ### Increase the timeouts so ProxySQL won't consider the backend servers unhealhy when stopping/starting the containers | |
| UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval'); | |
| UPDATE global_variables SET variable_value='100' WHERE variable_name IN ('mysql-connect_retries_on_failure','monitor_ping_max_failures'); | |
| # Don't add the source as a reader | |
| UPDATE global_variables SET variable_value='false' WHERE variable_name = 'mysql-monitor_writer_is_also_reader'; | |
| LOAD MYSQL VARIABLES TO RUNTIME; | |
| SAVE MYSQL VARIABLES TO DISK; | |
| ### Create a replication hostgroup | |
| INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1'); | |
| ### add the MySQL backend servers | |
| INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db1',3306); | |
| INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db2',3306); | |
| INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db3',3306); | |
| INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db4',3306); | |
| LOAD MYSQL SERVERS TO RUNTIME; | |
| SAVE MYSQL SERVERS TO DISK; | |
| ### Add the MySQL user to the ProxySQL | |
| INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('world','world',1); | |
| LOAD MYSQL USERS TO RUNTIME; | |
| SAVE MYSQL USERS TO DISK; | |
| ### Set up a query rule that will send all ^SELECT to the reader hostgroup=2 | |
| INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) | |
| VALUES | |
| (1,1,'^SELECT.*FOR UPDATE',1,1), | |
| (2,1,'^SELECT',2,1); | |
| LOAD MYSQL QUERY RULES TO RUNTIME; | |
| SAVE MYSQL QUERY RULES TO DISK; | |
| #test the setup | |
| mysql -vvv -uworld -pworld -P 13306 -h 127.0.0.1 world -e "insert into city (Name, CountryCode, District, Population) values ('Eger', 'HUN', 'Heves', 61234);" | |
| mysql -vvv -uworld -pworld -P 13306 -h 127.0.0.1 world -e "select * from world.city where name = 'Budapest';" | |
| #Cluster | |
| UPDATE global_variables SET variable_value='radmin' WHERE variable_name = 'admin-cluster_username'; | |
| UPDATE global_variables SET variable_value='radmin' WHERE variable_name = 'admin-cluster_password'; | |
| LOAD ADMIN VARIABLES TO RUNTIME; | |
| SAVE ADMIN VARIABLES TO DISK; | |
| insert into proxysql_servers values ('proxysql_donor','6032','','donor'); | |
| LOAD PROXYSQL SERVERS TO RUNTIME; | |
| SAVE PROXYSQL SERVERS TO DISK; | |
| INSERT INTO mysql_query_rules (active,match_digest,multiplex,cache_ttl) VALUES | |
| ('1','^SELECT @@max_allowed_packet',2,60000); | |
| LOAD MYSQL QUERY RULES TO RUNTIME; | |
| SAVE MYSQL QUERY RULES TO DISK; | |
| #sysbench | |
| make sysbench-up | |
| make sysbench-logs | |
| - { "title": "Top 10 SELECTs by avg_time", | |
| "info": "Caching/rewriting/even removing frequently running queries can improve the overall performance significantly. ProxySQL support all the mentioned methods. Example: it's a good idea to cache queries often issued by connectors like `^SELECT @@max_allowed_packet`", | |
| "sql": "SELECT digest,username,schemaname, SUBSTR(digest_text,0,80),count_star,sum_time, (sum_time/count_star) as avg_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY avg_time_ms DESC LIMIT 10;" | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment