Cloud Intelligence™

Come acquisire le istruzioni SQL con AWS RDS MySQL

By Ronald BradfordNov 9, 202321 min read

Questa pagina è disponibile anche in English, Deutsch, Español, Français, 日本語 e Português.

Acquisire le istruzioni SQL con RDS MySQL

In questo articolo illustrerò tutte le funzionalità native di MySQL per acquisire istruzioni SQL quando si utilizza AWS Relational Database Service (RDS). Identificare le istruzioni SQL è un principio fondamentale di progettazione e di esercizio, perché aiuta a capire come viene utilizzata l'applicazione, qual è il carico, quali sono gli impatti sulle prestazioni e dove si trovano i colli di bottiglia legati alla configurazione del database.

L'obiettivo dell'articolo è mostrare come "acquisire" le informazioni sulle istruzioni SQL. Non tratterò invece le ulteriori informazioni di supporto che è possibile ottenere insieme a un'istruzione SQL, come il Query Execution Plan (QEP), la struttura delle tabelle, la cardinalità degli indici, la configurazione dell'optimizer e le statistiche. Questi temi saranno affrontati in un articolo successivo.

Raccolta delle istruzioni SQL

MySQL offre diversi modi per raccogliere le istruzioni SQL in esecuzione o già eseguite. Nessun metodo è in assoluto il migliore e spesso è necessario combinarne più di uno per ottenere informazioni sufficienti a un'osservabilità dettagliata del sistema e a un'analisi più approfondita delle istruzioni SQL. È possibile ricorrere ai metodi seguenti, ognuno dei quali può a sua volta prevedere più modalità per ottenere gli stessi dettagli.

Processlist
Performance Schema
Sys Schema
Slow query log
General query log
Binary log

Salvo diversa indicazione, negli esempi seguenti viene utilizzato il client a riga di comando mysql.

NOTA: gli esempi SQL riportati sono compatibili sia con MySQL 5.7 sia con MySQL 8.0. A causa del fine vita (EOL) della community edition di MySQL 5.7 e dell'imminente EOL di AWS RDS MySQL 5.7, tutti gli output e i riferimenti alla documentazione si basano sull'attuale versione MySQL 8.0. Alcuni output mostrati nell'articolo potrebbero quindi differire da quelli prodotti su MySQL 5.7.

1\. SQL con la processlist di MySQL

La processlist di MySQL mostra le istruzioni SQL in esecuzione in quel momento, ovvero quelle attive nell'istante in cui viene eseguita l'istruzione di processlist. Per le query di lunga durata è un approccio semplice per individuare le istruzioni SQL. Per istruzioni SQL ad alta frequenza e bassa latenza, invece, può essere molto difficile osservarle tramite la processlist di MySQL. Oltre all'istruzione SQL in esecuzione, è possibile ottenere i dettagli relativi all'utente e all'host di origine, allo schema del database utilizzato, al tempo di esecuzione (in secondi) e allo stato interno corrente della query.

L'output della processlist di MySQL può essere recuperato in diversi modi.

performance schema
sys schema
information schema
istruzione SHOW
comando mysqladmin

Quando l'output fornisce le stesse colonne di informazioni, non c'è un vantaggio specifico nello scegliere una di queste opzioni rispetto a un'altra. Alcune non erano disponibili nelle versioni precedenti di MySQL e sono state aggiunte per allinearsi agli standard di settore comunemente accettati come istruzioni SQL ISO valide, requisito che SHOW non soddisfa. Il comando mysqladmin offre invece diverse opzioni di amministrazione e consente di combinare più output, come mostrato nell'esempio.

Processlist tramite Performance Schema

Quando è abilitato, le tabelle threads e processlist del performance_schema forniscono queste informazioni.

mysql> SELECT * FROM performance_schema.processlist;
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID  | USER            | HOST               | DB      | COMMAND | TIME | STATE                  | INFO                                                                                                                                                                      |
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   5 | event_scheduler | localhost          | NULL    | Daemon  |  456 | Waiting on empty queue | NULL                                                                                                                                                                      |
|   9 | rdsadmin        | localhost          | NULL    | Sleep   |    0 |                        | NULL                                                                                                                                                                      |
|  11 | rdsadmin        | localhost          | NULL    | Sleep   |    0 |                        | NULL                                                                                                                                                                      |
|  19 | rdsadmin        | localhost          | NULL    | Sleep   |    3 |                        | NULL                                                                                                                                                                      |
|  23 | rdsadmin        | localhost          | NULL    | Sleep   |   65 |                        | NULL                                                                                                                                                                      |
| 263 | rdsadmin        | localhost          | NULL    | Sleep   |    0 |                        | NULL                                                                                                                                                                      |
| 404 | dba             | 172.31.16.14:43464 | airport | Query   |    0 | executing              | SELECT SQL_NO_CACHE a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
| 406 | dba             | 172.31.16.14:43484 | airport | Query   |    0 | executing              | SELECT * FROM performance_schema.processlist                                                                                                                              |
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Processlist tramite Sys Schema

Le viste processlist e session del sys schema offrono accesso alle istruzioni SQL e ad altre informazioni di supporto. Queste viste si appoggiano al performance schema, che deve essere abilitato per acquisire SQL tramite il sys schema. Le viste mettono inoltre a disposizione attributi di metadati aggiuntivi sull'istruzione SQL. L'esempio seguente utilizza la vista sys.x$processlist.

mysql> SELECT * FROM sys.x$processlist WHERE command = 'Query'\G
*************************** 1. row ***************************
                thd_id: 675
               conn_id: 650
                  user: [email protected]
                    db: airport
               command: Query
                 state: executing
                  time: 0
     current_statement: SELECT a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10
     statement_latency: 116727428000
              progress: NULL
          lock_latency: 506000000
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 1
       tmp_disk_tables: 0
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 165019
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 116250140000
             trx_state: ACTIVE
        trx_autocommit: YES
                   pid: 15730
          program_name: mysql

Il terminatore di istruzione utilizzato sopra produce un formato di tabella verticale, ovvero colonna: valore per riga, anziché il classico formato orizzontale con righe e colonne. Al posto del consueto punto e virgola ‘;’ qui si usa il terminatore SQL ‘\G’. Si tratta di una sintassi specifica del client mysql, non supportata dagli altri client GUI di terze parti.

NOTA: se compare il messaggio di errore seguente, si può eseguire senza problemi l'istruzione SET SQL_BIG_SELECTS=1; prima dell'istruzione SQL precedente.

ERROR 1104 (42000) at line 1: The SELECT would examine more than
MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1
or SET MAX_JOIN_SIZE=# if the SELECT is okay

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Processlist tramite Information Schema

L'Information Schema è uno standard di fatto utilizzato in molti prodotti RDBMS, ma la tabella processlist non è standard. In MySQL le informazioni si visualizzano con la seguente istruzione.

mysql > SELECT * FROM information_schema.processlist;
+----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER            | HOST               | DB      | COMMAND | TIME | STATE                  | INFO                                                                                                                                                                      |
+----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 88 | dba             | 172.31.16.14:59474 | airport | Query   |    0 | executing              | SELECT SQL_NO_CACHE a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
|  9 | rdsadmin        | localhost          | NULL    | Sleep   |    0 |                        | NULL                                                                                                                                                                      |
| 26 | rdsadmin        | localhost          | NULL    | Sleep   |    1 |                        | NULL                                                                                                                                                                      |
| 11 | rdsadmin        | localhost          | NULL    | Sleep   |    0 |                        | NULL                                                                                                                                                                      |
| 19 | rdsadmin        | localhost          | NULL    | Sleep   |    9 |                        | NULL                                                                                                                                                                      |
|  5 | event_scheduler | localhost          | NULL    | Daemon  |  352 | Waiting on empty queue | NULL                                                                                                                                                                      |
| 94 | dba             | 172.31.16.14:59522 | airport | Query   |    0 | executing              | SELECT * FROM information_schema.processlist                                                                                                                              |
| 23 | rdsadmin        | localhost          | NULL    | Sleep   |  261 |                        | NULL                                                                                                                                                                      |
+----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual

Processlist tramite l'istruzione SHOW

Prima dell'introduzione dell'Information Schema nella versione 5.1 e del Performance Schema nella versione 5.5, in MySQL il modo principale per acquisire la processlist era il comando SHOW.

mysql> SHOW FULL PROCESSLIST;

L'output è identico al risultato di SELECT * FROM performance_schema.processlist; mostrato sopra. La parola chiave FULL serve a ottenere istruzioni non troncate.

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Processlist tramite il client _mysqladmin_

Anche un altro strumento client di MySQL, mysqladmin, usato per varie operazioni amministrative, può produrre un output della processlist in formato tabellare.

$ mysqladmin --verbose processlist status
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id  | User            | Host               | db      | Command | Time | State                  | Info                                                                                                                                                          |
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5   | event_scheduler | localhost          |         | Daemon  | 1419 | Waiting on empty queue |                                                                                                                                                               |
| 9   | rdsadmin        | localhost          |         | Sleep   | 1    |                        |                                                                                                                                                               |
| 11  | rdsadmin        | localhost          |         | Sleep   | 1    |                        |                                                                                                                                                               |
| 19  | rdsadmin        | localhost          |         | Sleep   | 6    |                        |                                                                                                                                                               |
| 23  | rdsadmin        | localhost          |         | Sleep   | 128  |                        |                                                                                                                                                               |
| 771 | rdsadmin        | localhost          |         | Sleep   | 3    |                        |                                                                                                                                                               |
| 830 | dba             | 172.31.16.14:54000 | airport | Query   | 0    | executing              | SELECT  a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
| 832 | dba             | 172.31.16.14:54026 |         | Query   | 0    | init                   | show full processlist                                                                                                                                         |
+-----+-----------------+--------------------+---------+---------+------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
Uptime: 1421  Threads: 8  Questions: 14058  Slow queries: 1290  Opens: 261  Flush tables: 3  Open tables: 181  Queries per second avg: 9.893

Senza l'opzione --verbose, le istruzioni SQL vengono troncate, anche quando sono brevi come questa. Ad esempio:

| 786 | dba             | 172.31.16.14:59446 | airport | Query   | 0    | executing              | SELECT  a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_c |

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual

2\. SQL con il Performance Schema

Quando il Performance Schema è abilitato e i consumer applicabili sono attivi, è possibile sfruttare diverse tabelle per ottenere informazioni dettagliate sulle istruzioni SQL eseguite e in esecuzione.

Opzioni di configurazione

In MySQL nativo il performance schema si abilita così.

mysql> SET GLOBAL performance_schema=1;

Con il servizio gestito AWS RDS, per modificare i parametri occorre invece intervenire sui parameter group. Ad esempio:

$ modify-parameter ${PG_NAME} performance_schema 1 pending-reboot

NOTA: per un'istanza RDS si fa riferimento all'instance parameter group. Quando un'istanza fa parte di un cluster AWS RDS Aurora, esiste un cluster parameter group che funge da default, salvo che i suoi valori vengano sovrascritti dall'instance parameter group.

Quando il performance schema viene abilitato per la prima volta, l'istanza MySQL — e quindi l'istanza AWS RDS — deve essere riavviata.

$ aws rds reboot-db-instance --db-instance-identifier ${INSTANCE_ID}

I consumer e gli instrument del performance schema per acquisire le istruzioni SQL sono abilitati per impostazione predefinita; è comunque possibile modificarli su un'istanza MySQL in esecuzione. Per verificare la configurazione attuale:

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%';
mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';

Verifica delle impostazioni

L'istruzione seguente consente di verificare se il performance schema è abilitato.

mysql> SELECT @@performance_schema;
+----------------------+
| @@performance_schema |
+----------------------+
|                    1 |
+----------------------+

Tabella processlist del Performance Schema

Come già illustrato, la tabella processlist fornisce informazioni di base sulle istruzioni in esecuzione in quel momento.

Tabella threads del Performance Schema

La tabella threads mostra attributi aggiuntivi per le istruzioni SQL in esecuzione.

mysql> SELECT * FROM performance_schema.threads WHERE PROCESSLIST_STATE="executing"\G
*************************** 1. row ***************************
          THREAD_ID: 960
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 935
   PROCESSLIST_USER: dba
   PROCESSLIST_HOST: 172.31.16.14
     PROCESSLIST_DB: airport
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: executing
   PROCESSLIST_INFO: SELECT  a.country_code, c.name, COUNT(*) AS cnt FROM airport a INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 353
     RESOURCE_GROUP: NULL

Tabelle events_statements_* del Performance Schema

Il vero punto di forza del performance schema è la capacità di acquisire le istruzioni SQL eseguite di recente: uno strumento essenziale per le query che si concludono in tempi molto rapidi.

mysql> SELECT * FROM performance_schema.events_statements_current WHERE SQL_TEXT like '%airport%'\G
*************************** 1. row ***************************
              THREAD_ID: 981
               EVENT_ID: 4
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:110
            TIMER_START: 2471111163284000
              TIMER_END: 2471111453481000
             TIMER_WAIT: 290197000
              LOCK_TIME: 175000000
               SQL_TEXT: SELECT * FROM performance_schema.events_statements_current WHERE SQL_TEXT like '%airport%'
                 DIGEST: 2fdfe28204e80c382a14597873be575ea8c7ede6e4757e77700a333bbe50b025
            DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_current` WHERE `SQL_TEXT` LIKE ?
         CURRENT_SCHEMA: NULL
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 22413
1 row in set (0.01 sec)

Anche altre tabelle del performance schema contengono le istruzioni SQL eseguite, fra cui events_statements_current, events_statements_history ed events_statements_long. Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

3\. SQL con il Sys Schema

Il sys schema di MySQL è un insieme di oggetti e viste che interpretano i dati disponibili nel Performance Schema, offrendoli all'utente finale in una forma più semplice da consultare. Mostra anche le istruzioni SQL eseguite e statistiche aggregate sulle esecuzioni. La prima vista da prendere in considerazione è statement_analysis, o x$statement_analysis.

mysql> select * from sys.x$statement_analysis where query like '%airport%'\G
*************************** 1. row ***************************
            query: SELECT `a` . `country_code` , `c` . `name` , COUNT ( * ) AS `cnt` FROM `airport` `a` INNER JOIN `country` `c` USING ( `country_code` ) GROUP BY `country_code` , NAME ORDER BY ? DESC LIMIT ?
               db: airport
        full_scan: *
       exec_count: 8
        err_count: 0
       warn_count: 0
    total_latency: 1330746923000
      max_latency: 304276507000
      avg_latency: 166343365000
     lock_latency: 1937000000
        rows_sent: 80
    rows_sent_avg: 10
    rows_examined: 1223680
rows_examined_avg: 152960
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 8
  tmp_disk_tables: 0
      rows_sorted: 80
sort_merge_passes: 0
           digest: 616aa31e4f99b22a194c91ae9f1cfbfb95d6957621b55393b4a01603d8e1fd47
       first_seen: 2023-10-12 14:55:56.021003
        last_seen: 2023-10-12 15:23:14.225503

Molte altre tabelle del sys schema contengono istruzioni SQL eseguite e aggregazioni di sottoinsiemi di istruzioni SQL. Alcuni esempi: statements_with_full_table_scans e statements_with_temp_tables. Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

4\. SQL con lo slow query log

Quando è abilitato, lo slow query log fa esattamente ciò che il nome suggerisce: registra tutte le query lente che superano un tempo di esecuzione predefinito. Nelle versioni meno recenti l'unità di misura era il secondo. In MySQL 5.7 e successive si esprime in microsecondi. È valido anche il valore 0, che significa registrare tutte le istruzioni SQL eseguite. Va però tenuto presente che ciò comporta un overhead prestazionale e un costo aggiuntivo su un database con elevata concorrenza.

Opzioni di configurazione

In MySQL nativo lo slow query log si abilita con:

mysql> SET GLOBAL slow_query_log=1;
mysql> SET GLOBAL long_query_time=0.001;
mysql> SET GLOBAL log_output=FILE;   /* This is the default */

Con il servizio gestito AWS RDS, per modificare i parametri occorre utilizzare i parameter group. Le modifiche sono immediate. Ad esempio:

$ modify-parameter ${PG_NAME} slow_query_log 1
$ modify-parameter ${PG_NAME} long_query_time 0.001
$ modify-parameter ${PG_NAME} log_output FILE

Esistono diversi parametri di configurazione aggiuntivi che non tratteremo, fra cui slow_query_log_file, log_queries_not_using_indexes e log_slow_admin_statements.

NOTA: occorre valutare con attenzione l'impatto dell'abilitazione di queste opzioni aggiuntive del slow query log. Ciascuna di esse può generare grandi volumi di istruzioni SQL che vanno a ingrossare il file di log. Possono finire registrate anche le query che eseguono volutamente un full-table scan a causa di un numero ridotto di righe, oppure le frequenti istruzioni amministrative essenziali per l'instrumentazione e l'osservabilità con strumenti di monitoraggio.

Verifica delle impostazioni

Per confermare che lo slow query log sia abilitato e configurato.

mysql> SELECT @@slow_query_log, @@long_query_time, @@log_output;
+------------------+-------------------+--------------+
| @@slow_query_log | @@long_query_time | @@log_output |
+------------------+-------------------+--------------+
|                1 |          0.001000 | FILE         |
+------------------+-------------------+--------------+

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Scaricare lo slow query log da AWS RDS

Quando è abilitato, lo slow query log scrive su un file di log nella propria istanza RDS. È possibile recuperarlo tramite la AWS CLI.

$ INSTANCE_ID=
$ aws rds download-db-log-file-portion  \
   --db-instance-identifier ${INSTANCE_ID} \
   --starting-token 0 \
   --output text \
   --log-file-name slowquery/mysql-slowquery.log > slowquery.log

I file di slow log di RDS vengono ruotati ogni ora. Per visualizzare tutti i file di log disponibili al download si possono usare i seguenti comandi.

$ aws rds describe-db-log-files --db-instance-identifier ${INSTANCE_ID}

{
    "DescribeDBLogFiles": [\
...\
        {\
            "LogFileName": "slowquery/mysql-slowquery.log",\
            "LastWritten": 1697125638054,\
            "Size": 761426\
        },\
        {\
            "LogFileName": "slowquery/mysql-slowquery.log.2023-10-12.14",\
            "LastWritten": 1697121600696,\
            "Size": 60517\
        },\
        {\
            "LogFileName": "slowquery/mysql-slowquery.log.2023-10-12.15",\
            "LastWritten": 1697122801087,\
            "Size": 343085\
        }\
    ]
}

Informazioni disponibili nello slow query log

Una singola istruzione SQL nello slow query log riporta le seguenti informazioni:

Tempo di esecuzione
Utente e host
Thread Id
Query time
Lock time
Rows sent
Rows examined
Istruzione SQL completa

Esempio di output dello slow query log

# Time: 2023-10-05T15:56:58.831165Z
# User@Host: dba[dba] @  [172.31.16.14]  Id:   295
# Query_time: 0.259097  Lock_time: 0.000178 Rows_sent: 10  Rows_examined: 76733
SET timestamp=1696521418;
SELECT a.country_code, c.name, COUNT(*) AS cnt
FROM airport a
  INNER JOIN country c USING (country_code)
GROUP BY country_code, name
ORDER BY 3 DESC
LIMIT 10;

NOTA: lo slow query log ha una struttura ben definita: tutte le righe non commentate sono istruzioni SQL effettive, terminate correttamente, il che ne rende molto semplice la raccolta, la registrazione e la successiva esecuzione. L'unico limite è che, quando le tabelle non sono qualificate dallo schema, occorre conoscere lo schema del database delle proprie tabelle.

Slow query log tramite tabella SQL

Anche se in genere non è consigliato, è possibile raccogliere le slow query tramite un'istruzione SQL quando lo slow query log è abilitato e si utilizza l'impostazione log_output=TABLE.

Raccolta SQL delle informazioni dello slow query log

L'istruzione SQL seguente consente di recuperare le query registrate nella tabella mysql.slow_log.

# Table does not have a PK, so this is a crud hack
SELECT sl.*,
       CONVERT(q.sql_text USING utf8mb4)AS query
FROM mysql.slow_log sl
INNER JOIN mysql.slow_log q  USING (start_time)
WHERE CONVERT(q.sql_text USING utf8mb4) like '%airport%'\G

Esempio di output dello slow query log tramite tabella

    start_time: 2023-10-05 15:59:30.878226
     user_host: dba[dba] @  [172.31.16.14]
    query_time: 00:00:00.295653
     lock_time: 00:00:00.000164
     rows_sent: 10
 rows_examined: 76733
            db: airport
last_insert_id: 0
     insert_id: 0
     server_id: 1843089174
      sql_text: 0x53454C45435420612E636F756E7472795F636F64652C20632E6E616D652C20434F554E54282A2920415320636E742046524F4D20616972706F72742061202020494E4E4552204A4F494E20636F756E7472792063205553494E472028636F756E7472795F636F6465292047524F555020425920636F756E7472795F636F64652C206E616D65204F5244455220425920332044455343204C494D4954203130
     thread_id: 295
         query: SELECT a.country_code, c.name, COUNT(*) AS cnt FROM airport a   INNER JOIN country c USING (country_code) GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10

5\. SQL con il general query log

Se configurato, MySQL può registrare tutte le istruzioni SQL nel general query log.

NOTA: occorre valutare con attenzione in quale ambiente abilitare il general query log e per quanto tempo. L'esempio seguente è stato eseguito su un sistema completamente inattivo, in cui sono state comunque eseguite altre 12 istruzioni SQL nello stesso secondo della nostra istruzione di esempio.

AVVERTENZA: come per lo slow query log, è possibile registrare questo output in una tabella MySQL. Si tratta di una pratica sconsigliata, perché può avere un impatto significativo sulle prestazioni di un sistema in esecuzione.

Opzioni di configurazione

In MySQL nativo il general query log si abilita con:

mysql> SET GLOBAL general_log=1;
mysql> SET GLOBAL log_output=FILE;   /* This is the default */

Con il servizio gestito AWS RDS, per modificare i parametri occorre utilizzare i parameter group. Le modifiche sono immediate. Ad esempio:

$ modify-parameter ${PG_NAME} general_log 1
$ modify-parameter ${PG_NAME} log_output FILE

C'è un ulteriore parametro di configurazione che non approfondiremo qui: la variabile general_log_file.

Verifica delle impostazioni

Per confermare che il general query log sia abilitato e configurato.

mysql> SELECT @@general_log, @@log_output, @@general_log_file;
+---------------+--------------+------------------------------------------+
| @@general_log | @@log_output | @@general_log_file                       |
+---------------+--------------+------------------------------------------+
|             1 | FILE         | /rdsdbdata/log/general/mysql-general.log |
+---------------+--------------+------------------------------------------+

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Scaricare il general query log da AWS RDS

Quando è abilitato, il general query log scrive su un file di log nella propria istanza RDS. È possibile recuperarlo tramite la AWS CLI.

$ INSTANCE_ID=
$ aws rds download-db-log-file-portion  \
   --db-instance-identifier ${INSTANCE_ID} \
   --starting-token 0 \
   --output text \
   --log-file-name general/mysql-general.log > general.log

Come indicato nella sezione precedente, l'argomento describe-db-log-files consente di elencare i file di general query log che sono stati ruotati.

Esempio di output del general query log

2023-10-05T17:31:06.006853Z         9 Query     set local oscar_local_only_replica_host_status=1;
2023-10-05T17:31:06.006963Z         9 Query     SELECT durable_lsn, current_read_point, server_id, last_update_timestamp FROM information_schema.replica_host_status;
2023-10-05T17:31:06.007500Z         9 Query     set local oscar_local_only_replica_host_status=0
2023-10-05T17:31:06.836552Z       540 Query     SELECT
       a.country_code, c.name, COUNT(*) AS cnt
FROM   airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10
2023-10-05T17:31:06.932194Z         9 Query     set local oscar_local_only_replica_host_status=1;
2023-10-05T17:31:06.932368Z         9 Query     SELECT durable_lsn, current_read_point, server_id, last_update_timestamp FROM information_schema.replica_host_status;
2023-10-05T17:31:06.933104Z         9 Query     set local oscar_local_only_replica_host_status=0
2023-10-05T17:31:06.935817Z        11 Query     SET @@sql_log_bin=off
2023-10-05T17:31:06.936615Z        11 Query     select @@session.transaction_read_only
2023-10-05T17:31:06.937118Z        11 Query     INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1696527066936) ON DUPLICATE KEY UPDATE value = 1696527066936
2023-10-05T17:31:06.947853Z        11 Query     select @@session.transaction_read_only
2023-10-05T17:31:06.948293Z        11 Query     COMMIT
2023-10-05T17:31:06.948725Z        11 Query     SET @@sql_log_bin=on

NOTA: il general query log è semi-strutturato. Le istruzioni SQL possono estendersi su più righe, mantenendo il formato che il programma client ha inviato a MySQL. Non basta quindi rimuovere i primi 41 caratteri di ogni riga per estrarre le istruzioni SQL.

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

6\. SQL con il binary log

Spesso trascurato, il binary log di MySQL — quando è abilitato — è una miniera di informazioni sulle istruzioni SQL che eseguono operazioni di scrittura sul database. Può essere fondamentale per individuare i confini delle transazioni e la sequenza delle istruzioni SQL al loro interno, agevolando l'analisi del locking. Fornisce inoltre i dettagli completi delle colonne nelle istruzioni INSERT, UPDATE e DELETE.

Qualsiasi configurazione di un'istanza RDS che utilizza read replica ha il binary logging abilitato per impostazione predefinita.

Verifica delle impostazioni

Per confermare che il binary log sia abilitato e configurato.

mysql> SELECT @@log_bin, @@binlog_format, @@log_statements_unsafe_for_binlog, @@log_bin_trust_function_creators;
+-----------+-----------------+------------------------------------+-----------------------------------+
| @@log_bin | @@binlog_format | @@log_statements_unsafe_for_binlog | @@log_bin_trust_function_creators |
+-----------+-----------------+------------------------------------+-----------------------------------+
|         0 | ROW             |                                  1 |                                 0 |
+-----------+-----------------+------------------------------------+-----------------------------------+

Per individuare i binary log disponibili sulla propria istanza MySQL:

mysql> SHOW BINARY LOGS;

I binary log si scaricano con il comando mysqlbinlog. Ad esempio.

$ TMP_DIR=${TMP_DIR:-/tmp}
$ AUTHENTICATION="-h<host> -u<user> -p<password"
$ BINLOG_FILE="binlog.00042"
mysqlbinlog --read-from-remote-server --raw --result-file=${TMP_DIR}/ ${AUTHENTICATION} ${BINLOG_FILE} 2>/dev/null # Kill warning message and not mess with $?

Per visualizzare il contenuto del binary log si usa sempre il comando mysqlbinlog. A seconda delle opzioni di configurazione alcuni argomenti risultano più adatti, ma in generale questo è il più indicato per tutti i formati:

$ mysqlbinlog -vvv --base64-output=DECODE-ROWS ${TMP_DIR}/${BINLOG_FILE}

L'analisi del binary log è un processo più articolato che non approfondiremo qui. Dipende inoltre dal valore della variabile di configurazione che definisce il formato di riga del binary log. Con argomenti aggiuntivi si possono ottenere informazioni di sintesi a livello di tabella per un singolo file o per un periodo che ne comprende diversi. Lo snippet seguente fornisce un riepilogo per tabella quando viene utilizzato binlog_format=ROW.

mysqlbinlog ${BINLOG_ADDITIONAL_ARGS} ${TMP_DIR}/${BINLOG_FILE}  | \
  sed -e "s/^### //;" | \
  sed -e "/^#/d;s/\/\*.*\*\/[;]//;/^$/d" | \
  cut -c1-100 | \
  tr '[A-Z]' '[a-z]' | \
  egrep "^(insert|update|delete|replace|commit|alter|drop|create)" |  \
  sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//;s/ join .*$//;s/ values .*$//;" | sed -e "s/ where .*$//;s/ignore //g;s/ inner//g;s/ left//g;s/ right//g;s/ from//g;s/ into//g" |  \
  sed -e "s/or replace//;s/sql security definer//;s/algorithm=.* //;s/definer=.* //g" |  \
  sed -e "s/ \w.*\.\*//" | \
  awk '{ print $1,$2 }' | \
  sort | uniq -c | sort -nr

Una copia dello script completo, che include il download e l'elaborazione di un file di binary log, è disponibile in questo Github gist.

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

Riepilogo delle opzioni di raccolta SQL

Di seguito i principali vantaggi e limiti di ciascuna opzione. Il modo migliore per capire qual è la più adatta alle proprie esigenze è provarle tutte, seguendo i passaggi descritti in questo articolo.

Vantaggi della Processlist

Abilitata per impostazione predefinita

Limiti della Processlist

Acquisisce solo le istruzioni SQL in esecuzione in quel momento
Richiede privilegi elevati per vedere tutte le query di tutti gli utenti

Vantaggi del Performance Schema

Acquisisce le istruzioni eseguite di recente, oltre a quelle in esecuzione
Fornisce risultati aggregati, ideali per query ad alta frequenza

Limiti del Performance Schema

Deve essere abilitato; la prima attivazione richiede un riavvio
Nei sistemi ad alte prestazioni richiede un tuning per limitare l'overhead sulle risorse

Vantaggi del Sys Schema

Estende il performance schema con viste e oggetti molto pratici, dai nomi chiari e pensati per scopi specifici

Limiti del Sys Schema

Richiede che il performance schema sia abilitato

Vantaggi dello slow query log

Acquisisce tutte le query lente che superano un tempo di esecuzione predefinito

Limiti dello slow query log

Praticamente nessun limite. Andrebbe sempre abilitato in tutte le istanze MySQL con una soglia adeguata.

Vantaggi del general query log

Fornisce un elenco sequenziale completo di tutte le istruzioni SQL. È ideale per l'analisi delle transazioni e per individuare antipattern di accesso ai dati.

Limiti del general query log

Overhead significativo sui sistemi di produzione molto utilizzati. Se ne consiglia l'uso con una test suite, idealmente con un singolo thread.

Vantaggi del binary log

In genere è sempre abilitato per garantire HA e capacità di PITR

Limiti del binary log

Quando è abilitato, acquisisce solo le operazioni di scrittura
Formato più complesso da elaborare per acquisire le istruzioni SQL complete

Appendice

Dati di esempio utilizzati

Per questo articolo abbiamo utilizzato il dataset airport disponibile all'indirizzo https://github.com/ronaldbradford/data/tree/main/mysql-data/airport

SQL di esempio utilizzato

Per ciascun metodo di raccolta è stata utilizzata la seguente istruzione SQL di esempio.

SELECT a.country_code, c.name, COUNT(*) AS cnt
FROM airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10;

Questa istruzione SQL restituisce un set di risultati simile a:

+--------------+----------------+-------+
| country_code | name           | cnt   |
+--------------+----------------+-------+
| US           | United States  | 30581 |
| BR           | Brazil         |  6849 |
| JP           | Japan          |  3430 |
| CA           | Canada         |  3075 |
| AU           | Australia      |  2576 |
| MX           | Mexico         |  2288 |
| RU           | Russia         |  1556 |
| KR           | South Korea    |  1400 |
| GB           | United Kingdom |  1398 |
| DE           | Germany        |  1042 |
+--------------+----------------+-------+
10 rows in set (0.47 sec)

NOTA: il numero effettivo di aeroporti e l'ordine dei paesi possono variare in base al momento in cui è stato recuperato il dataset.

Raccolta SQL

Trattandosi di un sistema di test con utilizzo minimo, alcune istruzioni hanno restituito tutte le righe disponibili, mentre altre sono state limitate da una clausola WHERE mirata ad acquisire questo specifico esempio di istruzione SQL. Quando si acquisiscono SQL nel proprio sistema può essere necessario applicare filtri appropriati per limitare l'output delle istruzioni.

Funzione helper Bash modify-parameter

La funzione seguente viene utilizzata in questo articolo per semplificare la sintassi di modifica delle variabili dei parameter group AWS RDS.

modify-parameter() {
  [[ $# -lt 3 ]] || [[ $# -gt 4 ]]  && echo "ERROR: <parameter-group> <parameter> <value> [pending-reboot]" && return 1

  local PARAMETER_GROUP="$1"
  local PARAMETER_NAME="$2"
  local PARAMETER_VALUE="$3"
  local APPLY_METHOD="immediate"

  [[ $# -eq 4 ]] && APPLY_METHOD="$4"
  aws rds modify-db-parameter-group --db-parameter-group-name ${PARAMETER_GROUP} --parameters "ParameterName=${PARAMETER_NAME},ParameterValue='${PARAMETER_VALUE}',ApplyMethod=${APPLY_METHOD}"
  aws rds describe-db-parameters --db-parameter-group-name ${PARAMETER_GROUP}  --query 'Parameters[] | [?ParameterName == `'${PARAMETER_NAME}'`]'
}

Per maggiori informazioni si rimanda alla AWS CLI command reference per la sintassi di modify-db-parameter-group e describe-db-parameters.

Suggerimento per l'identificazione delle SQL

MySQL consente di incorporare un commento all'interno di un'istruzione SQL, ma non tutti gli strumenti acquisiscono e registrano questa informazione. È buona pratica, da parte del team di engineering, creare una sorta di etichetta univoca per ogni istruzione SQL. Il valore del commento non è importante: ciò che conta è la sua unicità. Poter vedere una query con un commento e rintracciarla nel codice sorgente riduce il tempo di identificazione e il mean-time-to-resolve (MTTR) di qualsiasi problema.

SELECT /* Countries with the most airports */
       a.country_code, c.name, COUNT(*) AS cnt
FROM   airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name
ORDER BY 3 DESC LIMIT 10;

Per maggiori informazioni si rimanda al MySQL 8.0 Reference Manual.

MySQL mette a disposizione diversi metodi nativi che, tramite i suoi strumenti client, consentono di raccogliere istruzioni SQL da MySQL in esecuzione su AWS RDS. Esistono anche altri strumenti e tecniche per raccogliere istruzioni SQL in MySQL, che però non verranno trattati in questo articolo.

La raccolta di un'istruzione SQL è il primo passo essenziale per ottenere informazioni sufficienti sui pattern di accesso ai dati della propria applicazione. Si tratta di informazioni che possono rivelarsi fondamentali per la progettazione architetturale, il testing e l'ottimizzazione delle prestazioni.

In un articolo successivo affronterò la raccolta dei metadati di supporto necessari, insieme a qualsiasi istruzione SQL, per l'analisi delle prestazioni. Tra questi: il Query Execution Plan (QEP), i metadati di tabelle e indici, le statistiche di distribuzione delle colonne e le specifiche opzioni di configurazione che possono incidere sull'esecuzione di un'istruzione SQL.