Saltar a: navegación, buscar

Proxysql


Revisión del 12:31 25 ene 2021 de Gerardo (discusión | contribuciones) (Ver las reglas de rutas)

Comandos útiles y varios detalles sobre proxySQL

Información sobre proxySQL aquí: https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

Ver las reglas de rutas

mysql> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, destination_hostgroup AS dh, apply FROM mysql_query_rules;
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
| rule_id | match_digest | match_pattern                       | replace_pattern | cache_ttl | dh | apply |
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
| 1       | NULL         | ^\(*SELECT.*                        | NULL            | NULL      | 11 | 0     |
| 2       | NULL         | ^SELECT.*FOR UPDATE                 | NULL            | NULL      | 10 | 1     |
| 3       | NULL         | ^SHOW TABLES LIKE.*                 | NULL            | NULL      | 11 | 1     |
| 4       | NULL         | ^SHOW TABLE STATUS.*                | NULL            | NULL      | 11 | 1     |
| 5       | NULL         | ^\(*SELECT.*extras\_campos\_valores | NULL            | NULL      | 12 | 1     |
| 6       | NULL         | ^SHOW CREATE TABLE.*                | NULL            | NULL      | 12 | 1     |
| 7       | NULL         | ^DESCRIBE.*                         | NULL            | NULL      | 12 | 1     |
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
7 rows in set (0.00 sec)


Ver las veces que impacta una consulta en una regla

mysql> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, destination_hostgroup, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern                       | replace_pattern | destination_hostgroup | cache_ttl | apply |
+------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
| 906  | 1       | NULL         | ^\(*SELECT.*                        | NULL            | 11                    | NULL      | 0     |
| 0    | 2       | NULL         | ^SELECT.*FOR UPDATE                 | NULL            | 10                    | NULL      | 1     |
| 6    | 3       | NULL         | ^SHOW TABLES LIKE.*                 | NULL            | 11                    | NULL      | 1     |
| 33   | 4       | NULL         | ^SHOW TABLE STATUS.*                | NULL            | 11                    | NULL      | 1     |
| 6    | 5       | NULL         | ^\(*SELECT.*extras\_campos\_valores | NULL            | 12                    | NULL      | 1     |
+------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
5 rows in set (0.00 sec)


Ver las sqls impactadas de un grupo/nodo

mysql> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest WHERE hg=12;
+----+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| hg | sum_time  | count_star | digest_text                                                                                                                                         |
+----+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 12 | 288       | 1          | SELECT mbecv_campoid,mbecv_valor FROM mailing_bbdds_altas_pendientes_aids_extras_campos_valores WHERE (mbecv_fkid=UNHEX(?))                         |
| 12 | 438632771 | 9          | SELECT mbecv_campoid,mbecv_valor FROM mailing_bbdds_altas_pendientes_tlfs_extras_campos_valores WHERE (mbecv_fkid=UNHEX(?))                         |
| 12 | 740552377 | 3          | SELECT mbecv_campoid,mbecv_valor FROM mailing_bbdds_altas_pendientes_extras_campos_valores WHERE (mbecv_fkid=UNHEX(?))                              |
| 12 | 1016      | 3          | SELECT * FROM mailing_bbdds_115_extras_campos_valores LIMIT ?                                                                                       |
| 12 | 686       | 1          | SELECT `mbecv_id`,`mbecv_campoid`,`mbecv_fkid`,`mbecv_aid`,`mbecv_valor` FROM `mailing_bbdds_altas_pendientes_aids_extras_campos_valores` WHERE ?=? |
| 12 | 324       | 10         | SELECT ? FROM mailing_bbdds_115_extras_campos_valores LIMIT ?                                                                                       |
+----+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)


Comandos de carga de MySQL a RunTime

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD ADMIN VARIABLES TO RUNTIME;


Comando de guardado en disco

SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE CONFIG TO FILE /etc/proxysql.bckps.conf


Solución de errores

connection is locked to hostgroup X but trying to reach hostgroup Y

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;