MySQL execution plan: come abbiamo risolto un caso di degrado delle prestazioni sulle query di un’infrastruttura slave
Oggi siamo qui a raccontarvi l’esperienza diretta di un problema abbastanza complesso da risolvere, non molto per la natura del problema quanto perché questo risulta poco documentato e con pochi riferimenti in letteratura.
Speriamo così, raccontandovi di questo caso, di fare cosa gradita a chi potrebbe incappare in un problema del genere.
Il problema riguarda un cluster MySQL 8, composto da 1 proxy, 1 server master e 2 server slave, ci cui si compone l’infrastruttura di un nostro cliente. Su cui ci siamo trovati di fronte alla necessità di lavorare a una risincronizzazione degli slave. Non capita troppo di rado infatti che, per svariati motivi, i server slave possano perdere il loro livello di sincronizzazione.
Cosa è successo? Dopo aver risincronizzato degli slave, molte query (non tutte) risultavano decisamente più lente. Come ordine di grandezza, queste erano passate da 0,1 a 26 secondi.
Generamente un degrado così importante di performance è sintomo di un problema sugli indici o di un notevole calo di performance del server, cosa che potrebbe essere correlata allo storage o alle cpu.
Analizzando l’execution plan sui due server slave venivano mostrati piani di esecuzione differenti; mentre nel master veniva utilizzato l’indice, negli slave no.
Execution plan
MASTER
mysql> explain select * from TABELLA where nome = ‘PIPPO’;
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
| 1 | SIMPLE | TABELLA | NULL | ref | key_pippo | key_pippo | 640 | const | 9141 | 100.00 | NULL |
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
SLAVE
mysql> explain select * from TABELLA where nome = ‘PIPPO’;
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
| 1 | SIMPLE | TABELLA | NULL | ALL | key_pippo | NULL | NULL | NULL | 1 | 100.00 | Using where |
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
Andando ad analizzare le tabelle, sia sul master che sugli slave, queste avevano l’indice in oggetto correttamente attivo… A questo punto, la scelta sull’utilizzo o meno ricade sugli algoritmi di ottimizzazione.
Analizzando gli algoritmi di optimizzation, risultava che, mentre sul master veniva prediletto l’uso dell’indice, sullo slave veniva utilizzato il full table scan, poiché meno costoso.
MASTER
Costo dell’ indice
“range_scan_alternatives”: [
{
“ranges”: [
“PIPPO <= nome <= PIPPO”
],
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: true,
“using_mrr”: false,
“index_only”: false,
“rows”: 9141,
“cost”: 1371.3,
“chosen”: true
}
Costo del full table scan
“table”: “`TABELLA`”,
“range_analysis”: {
“table_scan”: {
“rows”: 328136,
“cost”: 35207
},
SLAVE
“range_scan_alternatives”: [
{
“index”: key_pippo”,
“ranges”: [
Costo dell’ indice
“range_scan_alternatives”: [
{
“index”: “key_pippo”,
“ranges”: [
“PIPPO <= nome <= PIPPO”
],
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: true,
“using_mrr”: false,
“index_only”: false,
“rows”: 9141,
“cost”: 501.11,
“chosen”: false,
“cause”: “cost“
}
Di seguito, ecco il calcolo del costo del full table scan sul server slave:
“table”: “`TABELLA`”,
“range_analysis”: {
“table_scan”: {
“rows”: 1,
“cost”: 2.45
},
“PIPPO <= nome <= PIPPO”
],
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: true,
“using_mrr”: false,
“index_only”: false,
“rows”: 1431,
“cost”: 501.11,
“chosen”: false,
“cause”: “cost”
}
Come potete vedere, il costo del full table scan è sensibilmente minore dell’accesso all’indice… ma questo NON E’ POSSIBILE.
Approfondendo, si evince che il numero di righe, secondo l’algoritmo, è pari a 1 – ?
“table”: “`TABELLA`”,
“range_analysis”: {
“table_scan”: {
“rows”: 1,
“cost”: 2.45
},
Continuando le analisi, a questo punto possiamo constatare che effettivamente l’algoritmo conta il numero di righe in maniera errata, anche con un semplice full table scan.
MASTER
mysql> explain select * from TABELLA;
+—-+————-+———————+————+——+—————+——+———+——+——–+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+—————+——+———+——+——–+———-+——-+
| 1 | SIMPLE | TABELLA | NULL | ALL | NULL | NULL | NULL | NULL | 328136 | 100.00 | NULL |
+—-+————-+———————+————+——+—————+——+———+——+——–+———-+——-+
SLAVE
mysql> explain select * from TABELLA;
+—-+————-+———————+————+——+—————+——+———+——+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+——-+
| 1 | SIMPLE | TABELLA| NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+——-+
Per risolvere il problema è stato necessario lanciare “analyze table”, attività asincrona di mysql che procede a ricalcolare l’execution plan.
Di seguito ecco il MySQL execution plan con il conteggio corretto.
mysql> explain select * TABELLA where item_name = ‘PIPPO’;
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
| 1 | SIMPLE | TABELLA| NULL | ALL | key_pippo | NULL | NULL | NULL | 1 | 100.00 | Using where |
+—-+————-+———————+————+——+————————————–+——+———+——+——+———-+————-+
mysql> analyze table TABELLA;
+——————————————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————————————-+———+———-+———-+
| TABELLA | analyze | status | OK |
+——————————————-+———+———-+———-+
mysql> explain select * from TABELLA where item_name = ‘PIPPO’;
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
| 1 | SIMPLE | cwh_auth_assignment | NULL | ref | key_pippo | key_pippo | 8192 | const | 9141 | 100.00 | NULL |
+—-+————-+———————+————+——+————————————–+————————————–+———+——-+——+———-+——-+
Dopo che l’execution plan è stato rielaborato, tutto è tornato alla normalità.
Per ovviare al problema in maniera definitiva e ottimizzare l’execution plan, abbiamo ritenuto necessario aumentare il valore della variabile innodb_stats_persistent_sample_pages
