Saltar a: navegación, buscar

Proxysql


Revisión del 16:01 11 mar 2021 de Gerardo (discusión | contribuciones) (Ver las veces que impacta una consulta en una regla)

Comandos útiles y varios detalles sobre proxySQL

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

Ver las reglas de rutas

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


Ver las veces que impacta una consulta en una regla

mysql> SELECT hits, mysql_query_rules.rule_id, active, match_digest, match_pattern, destination_hostgroup as hg, 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                       | hg | cache_ttl | apply |
+--------+---------+--------------+-------------------------------------+----+-----------+-------+
| 346461 | 1       | NULL         | ^\(*SELECT.*                        | 11 | NULL      | 0     |
| 0      | 2       | NULL         | ^SELECT.*FOR UPDATE                 | 10 | NULL      | 1     |
| 513    | 3       | NULL         | ^SHOW TABLES LIKE.*                 | 11 | NULL      | 1     |
| 817    | 4       | NULL         | ^SHOW TABLE STATUS.*                | 11 | NULL      | 1     |
| 116    | 5       | NULL         | ^\(*SELECT.*extras\_campos\_valores | 12 | NULL      | 1     |
| 1      | 6       | NULL         | ^SHOW CREATE TABLE.*                | 12 | NULL      | 1     |
| 887    | 7       | NULL         | ^DESCRIBE.*                         | 12 | NULL      | 1     |
| 33     | 8       | NULL         | ^SELECT count.*                     | 12 | 25000     | 1     |
| 7      | 9       | NULL         | ^SELECT HEX.*                       | 12 | NULL      | 1     |
+--------+---------+--------------+-------------------------------------+----+-----------+-------+
9 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 USERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE ADMIN VARIABLES TO DISK;
SAVE CONFIG TO FILE /etc/proxysql.bckps.conf;


Reseteo de estadísticas

SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;


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;