Saltar a: navegación, buscar

Diferencia entre revisiones de «Proxysql»


m (Ver las veces que impacta una consulta en una regla)
m (Ver las veces que impacta una consulta en una regla)
 
Línea 27: Línea 27:
 
<pre>
 
<pre>
 
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;
 
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 |
+
| hits | rule_id | active | match_digest | match_pattern                           | hg | cache_ttl | apply |
+--------+---------+--------------+-------------------------------------+----+-----------+-------+
+
+-------+---------+--------+--------------+------------------------------------------+----+-----------+-------+
| 346461 | 1      | NULL        | ^\(*SELECT.*                       | 11 | NULL      | 0    |
+
| 57287 | 1      | 1      | NULL        | ^\(*SELECT.*                             | 11 | NULL      | 0    |
| 0     | 2      | NULL        | ^SELECT.*FOR UPDATE                 | 10 | NULL      | 1    |
+
| 0     | 2      | 1      | NULL        | ^SELECT.*FOR UPDATE                     | 10 | NULL      | 1    |
| 513   | 3      | NULL        | ^SHOW TABLES LIKE.*                 | 11 | NULL      | 1    |
+
| 33   | 3      | 1      | NULL        | ^SHOW TABLES LIKE.*                     | 11 | NULL      | 1    |
| 817   | 4      | NULL        | ^SHOW TABLE STATUS.*               | 11 | NULL      | 1    |
+
| 68   | 4      | 1      | NULL        | ^SHOW TABLE STATUS.*                     | 11 | NULL      | 1    |
| 116    | 5      | NULL        | ^\(*SELECT.*extras\_campos\_valores | 12 | NULL      | 1    |
+
| 4    | 5      | 1      | NULL        | ^\(*SELECT.*extras\_campos\_valores     | 12 | NULL      | 1    |
| 1      | 6      | NULL        | ^SHOW CREATE TABLE.*               | 12 | NULL      | 1    |
+
| 0    | 6      | 1      | NULL        | ^SHOW CREATE TABLE.*                     | 12 | NULL      | 1    |
| 887   | 7      | NULL        | ^DESCRIBE.*                         | 12 | NULL      | 1    |
+
| 49   | 7      | 1      | NULL        | ^DESCRIBE.*                             | 12 | 25000    | 1    |
| 33    | 8      | NULL        | ^SELECT count.*                     | 12 | 25000    | 1    |
+
| 4450  | 8      | 1      | NULL        | ^SELECT count.*                         | 12 | 25000    | 1    |
| 7      | 9      | NULL        | ^SELECT HEX.*                       | 12 | NULL      | 1    |
+
| 0    | 9      | 1      | NULL        | ^SELECT HEX.*                           | 12 | NULL      | 1    |
+--------+---------+--------------+-------------------------------------+----+-----------+-------+
+
| 0    | 10      | 1      | NULL        | ^FLUSH.*                                | 11 | NULL      | 1    |
9 rows in set (0.00 sec)
+
| 0    | 11      | 1      | NULL        | ^RESET.*                                | 11 | NULL      | 1    |
 +
| 3    | 13      | 1      | NULL        | ^\(*SELECT mbd_email_id AS clave_id.*    | 12 | NULL      | 1    |
 +
| 83    | 14      | 1      | NULL        | ^\(*SELECT mbd_telefono_id AS clave_id.* | 12 | NULL      | 1    |
 +
+-------+---------+--------+--------------+------------------------------------------+----+-----------+-------+
 +
13 rows in set (0.00 sec)
 
</pre>
 
</pre>
 
<br />
 
<br />

Revisión actual del 16:04 11 mar 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 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 | active | match_digest | match_pattern                            | hg | cache_ttl | apply |
+-------+---------+--------+--------------+------------------------------------------+----+-----------+-------+
| 57287 | 1       | 1      | NULL         | ^\(*SELECT.*                             | 11 | NULL      | 0     |
| 0     | 2       | 1      | NULL         | ^SELECT.*FOR UPDATE                      | 10 | NULL      | 1     |
| 33    | 3       | 1      | NULL         | ^SHOW TABLES LIKE.*                      | 11 | NULL      | 1     |
| 68    | 4       | 1      | NULL         | ^SHOW TABLE STATUS.*                     | 11 | NULL      | 1     |
| 4     | 5       | 1      | NULL         | ^\(*SELECT.*extras\_campos\_valores      | 12 | NULL      | 1     |
| 0     | 6       | 1      | NULL         | ^SHOW CREATE TABLE.*                     | 12 | NULL      | 1     |
| 49    | 7       | 1      | NULL         | ^DESCRIBE.*                              | 12 | 25000     | 1     |
| 4450  | 8       | 1      | NULL         | ^SELECT count.*                          | 12 | 25000     | 1     |
| 0     | 9       | 1      | NULL         | ^SELECT HEX.*                            | 12 | NULL      | 1     |
| 0     | 10      | 1      | NULL         | ^FLUSH.*                                 | 11 | NULL      | 1     |
| 0     | 11      | 1      | NULL         | ^RESET.*                                 | 11 | NULL      | 1     |
| 3     | 13      | 1      | NULL         | ^\(*SELECT mbd_email_id AS clave_id.*    | 12 | NULL      | 1     |
| 83    | 14      | 1      | NULL         | ^\(*SELECT mbd_telefono_id AS clave_id.* | 12 | NULL      | 1     |
+-------+---------+--------+--------------+------------------------------------------+----+-----------+-------+
13 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;