Saltar a: navegación, buscar

Diferencia entre revisiones de «Proxysql»


(Ver las reglas de rutas)
Línea 6: Línea 6:
 
=== Ver las reglas de rutas ===
 
=== Ver las reglas de rutas ===
 
<pre>
 
<pre>
mysql> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, destination_hostgroup AS dh, apply FROM mysql_query_rules;
+
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;
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
+
+--------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
| rule_id | match_digest | match_pattern                      | replace_pattern | cache_ttl | dh | apply |
+
| hits  | rule_id | match_digest | match_pattern                      | replace_pattern | destination_hostgroup | cache_ttl | apply |
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
+
+--------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
| 1      | NULL        | ^\(*SELECT.*                        | NULL            | NULL      | 11 | 0    |
+
| 346425 | 1      | NULL        | ^\(*SELECT.*                        | NULL            | 11                    | NULL      | 0    |
| 2      | NULL        | ^SELECT.*FOR UPDATE                | NULL            | NULL      | 10 | 1    |
+
| 0      | 2      | NULL        | ^SELECT.*FOR UPDATE                | NULL            | 10                    | NULL      | 1    |
| 3      | NULL        | ^SHOW TABLES LIKE.*                | NULL            | NULL      | 11 | 1    |
+
| 513    | 3      | NULL        | ^SHOW TABLES LIKE.*                | NULL            | 11                    | NULL      | 1    |
| 4      | NULL        | ^SHOW TABLE STATUS.*                | NULL            | NULL      | 11 | 1    |
+
| 810    | 4      | NULL        | ^SHOW TABLE STATUS.*                | NULL            | 11                    | NULL      | 1    |
| 5      | NULL        | ^\(*SELECT.*extras\_campos\_valores | NULL            | NULL      | 12 | 1    |
+
| 116    | 5      | NULL        | ^\(*SELECT.*extras\_campos\_valores | NULL            | 12                    | NULL      | 1    |
| 6      | NULL        | ^SHOW CREATE TABLE.*                | NULL            | NULL      | 12 | 1    |
+
| 1      | 6      | NULL        | ^SHOW CREATE TABLE.*                | NULL            | 12                    | NULL      | 1    |
| 7      | NULL        | ^DESCRIBE.*                        | NULL            | NULL      | 12 | 1    |
+
| 887    | 7      | NULL        | ^DESCRIBE.*                        | NULL            | 12                    | NULL      | 1    |
+---------+--------------+-------------------------------------+-----------------+-----------+----+-------+
+
| 33    | 8      | NULL        | ^SELECT count.*                    | NULL            | 12                   | 25000    | 1    |
7 rows in set (0.00 sec)
+
| 7      | 9      | NULL        | ^SELECT HEX.*                      | NULL            | 12                    | NULL      | 1    |
 +
+--------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
 +
9 rows in set (0.00 sec)
 
</pre>
 
</pre>
 
<br />
 
<br />

Revisión del 16:17 25 ene 2021

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 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 |
+--------+---------+--------------+-------------------------------------+-----------------+-----------------------+-----------+-------+
| 346425 | 1       | NULL         | ^\(*SELECT.*                        | NULL            | 11                    | NULL      | 0     |
| 0      | 2       | NULL         | ^SELECT.*FOR UPDATE                 | NULL            | 10                    | NULL      | 1     |
| 513    | 3       | NULL         | ^SHOW TABLES LIKE.*                 | NULL            | 11                    | NULL      | 1     |
| 810    | 4       | NULL         | ^SHOW TABLE STATUS.*                | NULL            | 11                    | NULL      | 1     |
| 116    | 5       | NULL         | ^\(*SELECT.*extras\_campos\_valores | NULL            | 12                    | NULL      | 1     |
| 1      | 6       | NULL         | ^SHOW CREATE TABLE.*                | NULL            | 12                    | NULL      | 1     |
| 887    | 7       | NULL         | ^DESCRIBE.*                         | NULL            | 12                    | NULL      | 1     |
| 33     | 8       | NULL         | ^SELECT count.*                     | NULL            | 12                    | 25000     | 1     |
| 7      | 9       | NULL         | ^SELECT HEX.*                       | NULL            | 12                    | NULL      | 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, 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


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;