MySQL - Selectați interogările de 10 ori mai lent pe VM Azure față de VM on-prem

Lucrăm la un proiect de migrare a unei baze de date MySQL de la un server Linux on-premise la o VM Windows pe Azure (IaaS). (Există un motiv specific din cauza căruia am optat pentru opțiunea IaaS în loc de oferta Azure MySQL PaaS).

După migrare, vedem că interogările din baza de date MySQL sunt semnificativ mai lente (aproximativ 10x) pe noul server. VM-ul este configurat cu 64 de procesoare și 256 GB RAM (VM-ul on-premise avea 48 de procesoare și 256 GB RAM).

Toate tabelele din baza de date folosesc motorul InnoDB. Am citit destul de multe despre încetinirea interogărilor cu tabelele InnoDB și cea mai mare parte pare să indice innodb_buffer_pool_size - pe care l-am configurat deja la 185 GB (aproximativ 70% din RAM totală). De asemenea, am încercat să facem o serie de alte modificări în my.ini configurație ca

key_buffer_size = 20MB
innodb_io_capacity = 2000
query_cache_size = 0
query_cache_type = 0
crescând dimensiunea_cache_filului
innodb_read_io_threads
innodb_write_io_threads

Etc. Dar nimic nu pare să ajute, cu performanța interogării.

Am comparat indecșii de pe ambele servere și sunt la fel. Și la un nivel înalt, nu pare că indexurile sunt sparte pe Azure VM. De asemenea, încercăm să măsurăm performanța rulând MySQL workbench în interiorul Azure VM, astfel încât lățimea de bandă a rețelei nu ar trebui să fie o problemă.

• Poate cineva să sugereze alte opțiuni pe care le-am putea încerca să îmbunătățim performanța?

Câteva puncte suplimentare.

• Ceea ce observăm este că, deși unele interogări preiau 30+ minute a rula, (se pare că rulează pe serverul local în doar 5 minute), utilizarea CPU pe VM rămâne foarte scăzută (mai puțin de 10%).Există vreo setare precum innodb_buffer_pool_size` pentru a aloca o anumită cantitate de CPU serverului MySQL?

După cum am menționat anterior, VM-ul local este bazat pe Linux, iar VM-ul Azure rulează pe Windows - ar putea fi aceasta o problemă? Nu găsesc nicio dovadă definitivă că MySQL pe Windows va provoca o degradare atât de gravă a performanței.

Configurația mea completă my.ini este mai jos:

# Alte valori implicite de reglare
# Fișierul de configurare a instanței MySQL Server
# --------------------------------------------- ---------------------
# Generat de asistentul de configurare a instanțelor MySQL Server
#
#
# Instructiuni de instalare
# --------------------------------------------- ---------------------
#
# Pe Linux puteți copia acest fișier în /etc/my.cnf pentru a seta opțiuni globale,
# mysql-data-dir/my.cnf pentru a seta opțiuni specifice serverului
# (@localstatedir@ pentru această instalare) sau către
# ~/.my.cnf pentru a seta opțiuni specifice utilizatorului.
#
# Pe Windows ar trebui să păstrați acest fișier în directorul de instalare 
# de serverul dvs. (de exemplu, C:\Program Files\MySQL\MySQL Server X.Y). La
# asigurați-vă că serverul citește fișierul de configurare, utilizați opțiunea de pornire 
# "--defaults-file". 
#
# Pentru a rula serverul din linia de comandă, executați acest lucru în a 
# shell de linie de comandă, de ex.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# Pentru a instala manual serverul ca serviciu Windows, executați acest lucru în a 
# shell de linie de comandă, de ex.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# Și apoi executați acest lucru într-un shell de linie de comandă pentru a porni serverul, de ex.
# net start MySQLXY
#
#
# Instrucțiuni pentru editarea acestui fișier
# --------------------------------------------- ---------------------
#
# În acest fișier, puteți utiliza toate opțiunile lungi pe care le acceptă programul.
# Dacă doriți să aflați opțiunile pe care le acceptă un program, porniți programul
# cu opțiunea „--help”.
#
# Informații mai detaliate despre opțiunile individuale pot fi, de asemenea
# găsit în manual.
#
# Pentru sfaturi despre cum să schimbați setările, consultați
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# SECȚIUNEA CLIENT
# --------------------------------------------- ---------------------
#
# Următoarele opțiuni vor fi citite de aplicațiile client MySQL.
# Rețineți că numai aplicațiile client livrate de MySQL sunt garantate
# pentru a citi această secțiune. Dacă doriți propriul program client MySQL
# onora aceste valori, trebuie să le specificați ca opțiune în timpul
# Inițializarea bibliotecii client MySQL.
#
[client]

# pipe=

# socket=MYSQL

port=3306

[mysql]
fără bip

# default-character-set=

# SECȚIUNEA SERVER
# --------------------------------------------- ---------------------
#
# Următoarele opțiuni vor fi citite de serverul MySQL. Asigura-te ca
# ați instalat corect serverul (vezi mai sus), așa că citește acest lucru 
# fișier.
#
# server_type=1
[mysqld]
plugin-load-add=validate_password.dll
validate-password=FORCE_PLUS_PERMANENT
# Următoarele trei opțiuni se exclud reciproc pentru SERVER_PORT de mai jos.
# skip-networking
# enable-named-pipe
# memorie partajată

# shared-memory-base-name=MYSQL

# Tubul pe care îl va folosi serverul MySQL
# socket=MYSQL

# Portul TCP/IP pe care îl va asculta serverul MySQL
port=3306

# Calea către directorul de instalare. Toate căile sunt de obicei rezolvate în raport cu aceasta.
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Calea către rădăcina bazei de date
datadir=F:\MySQL\Date

# Setul de caractere implicit care va fi folosit atunci când este o nouă schemă sau tabel
# creat și nu este definit niciun set de caractere
# caracter-set-server=

# Motorul de stocare implicit care va fi folosit când se creează tabele noi când
default-storage-engine=INNODB

# Setați modul SQL la strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# Înregistrare generală și lentă.
log-output=FIȘIER

general-log=0

general_log_file="ZEUWPJIRA01.log"

slow-query-log=1

slow_query_log_file="ZEUWPJIRA01-slow.log"

long_query_time=10

# Înregistrare erori.
log-error="ZEUWPJIRA01.err"

# ***** Legat de replicarea grupului *****
# Specifică numele de bază de utilizat pentru fișierele jurnal binare. Cu înregistrare binară
# activat, serverul înregistrează toate instrucțiunile care modifică datele în binar
# jurnal, care este folosit pentru backup și replicare.
# log-bin

# ***** Legat de replicarea grupului *****
# Specifică ID-ul serverului. Pentru serverele care sunt utilizate într-o topologie de replicare,
# trebuie să specificați un ID de server unic pentru fiecare server de replicare, în
# interval de la 1 la 2^32 â 1. âUnicâ înseamnă că fiecare ID trebuie să fie diferit
# din orice alt ID utilizat de orice altă sursă de replicare sau replică.
server-id=26
# ***** Legat de replicarea grupului *****
# Numele de gazdă sau adresa IP a replicii care urmează să fie raportate la sursă
# în timpul înregistrării replicilor. Această valoare apare în rezultatul SHOW SLAVE HOSTS
# pe serverul sursă. Lăsați valoarea nesetată dacă nu doriți replica
# se înregistrează cu sursa.
# report_host=0.0

# ***** Legat de replicarea grupului *****
# Definește algoritmul folosit pentru hashing scrierile extrase în timpul unei tranzacții. daca tu
# folosesc Group Replication, această variabilă trebuie setată la XXHASH64 deoarece procesul
Numărul de extragere a scrierilor dintr-o tranzacție este necesar pentru detectarea conflictelor pe toate
# membrii grupului.
# transaction_write_set_extraction=0.0
nume_tabele_minuscule=1

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

# Cantitatea maximă de sesiuni simultane pe care serverul MySQL le va avea
# permite. Una dintre aceste conexiuni va fi rezervată unui utilizator cu
# Privilegii SUPER pentru a permite administratorului să se autentifice chiar dacă
Limita de # conexiune a fost atinsă.
#max_connections=151
#[29062021]modificat de Sridharan pentru a crește numărul maxim de sesiuni simultane
max_connections=1000
#[29062021]modificat de Sridharan pentru a crește numărul maxim de erori de conectare
max_connect_errors=500

# Numărul de mese deschise pentru toate firele. Creșterea acestei valori
# crește numărul de descriptori de fișier pe care mysqld îi cere.
# Prin urmare, trebuie să vă asigurați că setați cantitatea de fișiere deschise
# permis la cel puțin 4096 în variabila „open-files-limit” în
# secțiune [mysqld_safe]
#table_open_cache=2000
table_open_cache =2048
table_definition_cache = 2048
myisam_sort_buffer_size = 8M
#skip-extern-locking
# Dimensiunea maximă pentru tabelele temporare interne (în memorie). Dacă o masă
# crește mai mare decât această valoare, este convertit automat pe disc
# based table Această limitare este pentru un singur tabel. Pot fi multe
# dintre ei.
tmp_table_size=4G

# Câte fire ar trebui să păstrăm într-un cache pentru reutilizare. Când un client
# se deconectează, firele clientului sunt puse în cache dacă nu există
# mai mult decât fire_cache_size de înainte. Acest lucru reduce foarte mult
# cantitatea de creări de fire necesare dacă aveți multe noi
# conexiuni. (În mod normal, acest lucru nu oferă o performanță notabilă
# îmbunătățire dacă aveți o implementare bună a firului.)
# thread_cache_size=10
thread_cache_size=64
query_cache_size =0
query_cache_type = 0

#*** MyISAM Opțiuni specifice
# Dimensiunea maximă a fișierului temporar MySQL poate fi utilizat în timp ce
# recrearea indexului (în timpul REPAIR, ALTER TABLE sau LOAD DATA INFILE.
# Dacă dimensiunea fișierului ar fi mai mare decât aceasta, indexul va fi creat
# prin memoria cache a cheilor (care este mai lent).
myisam_max_sort_file_size=100G

# Mărimea buffer-ului care este alocat la sortarea indecșilor MyISAM
# în timpul unui REPAIR TABLE sau când se creează indecși cu CREATE INDEX
# sau ALTER TABLE.
myisam_sort_buffer_size=6G

# Dimensiunea Key Buffer-ului, folosită pentru a stoca în cache blocurile de index pentru tabelele MyISAM.
# Nu setați mai mult de 30% din memoria dvs. disponibilă, ca o parte din memorie
# este, de asemenea, cerut de sistemul de operare pentru a stoca în cache rândurile.Chiar dacă nu folosești
# MyISAM tables, ar trebui să-l setați în continuare la 8-64M, așa cum va fi, de asemenea
# folosit pentru tabelele de discuri temporare interne.
key_buffer_size=20M

# Dimensiunea buffer-ului folosit pentru scanarea completă a tabelelor MyISAM.
# Alocat per fir, dacă este necesară o scanare completă.
read_buffer_size=64K

read_rnd_buffer_size=256K
tmp_table_size=64M
max_heap_table_size=64M

#*** Opțiuni specifice INNODB ***
# innodb_data_home_dir=

# Utilizați această opțiune dacă aveți un server MySQL cu suportul InnoDB activat
# dar nu intenționați să îl utilizați. Acest lucru va economisi memorie și spațiu pe disc
# și accelerează unele lucruri.
# skip-innodb

# Dacă este setată la 1, InnoDB va șterge (fsync) jurnalele de tranzacții în
# disc la fiecare comitere, care oferă un comportament ACID complet. Daca esti
# dispus să compromiți această siguranță și rămâi puțin
# tranzacții, puteți seta acest lucru la 0 sau 2 pentru a reduce I/O pe disc la
# jurnale. Valoarea 0 înseamnă că jurnalul este scris numai în fișierul jurnal și
# fișierul jurnal a fost șters pe disc aproximativ o dată pe secundă. Valoarea 2
# înseamnă că jurnalul este scris în fișierul jurnal la fiecare comitere, dar jurnalul
# fișierul este șters pe disc doar aproximativ o dată pe secundă.
innodb_flush_log_at_trx_commit=2

# Mărimea bufferului pe care InnoDB îl folosește pentru stocarea datelor din jurnal. De îndată ce
# este plin, InnoDB va trebui să îl scoată pe disc. Pe măsură ce este spălat
# oricum o dată pe secundă, nu are sens să fie foarte mare
# (chiar și cu tranzacții lungi).
innodb_log_buffer_size=200M

# InnoDB, spre deosebire de MyISAM, folosește un pool de buffer pentru a stoca în cache ambii indici și
# rând de date. Cu cât setați acest lucru mai mare, cu atât este nevoie de mai puține I/O pe disc
# accesează datele din tabele. Pe un server de baze de date dedicat puteți seta acest lucru
# parametru până la 80% din dimensiunea memoriei fizice a mașinii. Nu-l seta
# prea mare, totuși, deoarece concurența memoriei fizice poate
# provoacă paginarea în sistemul de operare. Rețineți că pe sistemele pe 32 de biți dvs
# poate fi limitat la 2-3,5 G de memorie la nivel de utilizator per proces, deci nu
# setați-l prea sus.
#[29062021]modificat de Sridharan pentru a crește dimensiunea pool-ului de buffer innodb de la 16G la 24G
#[06072021]modificat de Sridharan pentru a crește dimensiunea pool-ului de buffer innodb de la 24G la 48G
innodb_buffer_pool_size=185G

# Dimensiunea fiecărui fișier jurnal dintr-un grup de jurnal. Ar trebui să setați dimensiunea combinată
Numărul de fișiere jurnal la aproximativ 25%-100% din dimensiunea pool-ului de buffer de evitat
# activitate nenecesară de spălare a pool-ului de buffer la suprascrierea fișierului jurnal. In orice caz,
# rețineți că o dimensiune mai mare a fișierului jurnal va crește timpul necesar pentru
# proces de recuperare.
innodb_log_file_size=4G

# Numărul de fire permise în interiorul nucleului InnoDB. Valoarea optimă
# depinde foarte mult de aplicație, hardware, precum și de sistemul de operare
# proprietăți de planificare. O valoare prea mare poate duce la zdrobirea firului.
#innodb_thread_concurrency=17
innodb_thread_concurrency=32

# Dimensiunea de increment (în MB) pentru extinderea dimensiunii unui fișier de spațiu tabelă de sistem InnoDB cu extindere automată atunci când acesta devine plin.
innodb_autoextend_increment=64

# Numărul de regiuni în care este împărțit pool-ul de buffer InnoDB.
# Pentru sistemele cu pool-uri de buffer în intervalul multi-gigabyte, împărțirea pool-ului de buffer în instanțe separate poate îmbunătăți concurența,
# prin reducerea disputelor pe măsură ce diferite fire citesc și scriu în paginile stocate în cache.
#innodb_buffer_pool_instances=8
#[29062021 Sridharan] a crescut instanțele pool-ului de buffer de la 8 la 12
innodb_buffer_pool_instances=12

#[29062021 Sridharan] a adăugat configurațiile io_capacity, read_io_threads și write_io_threads pentru a accelera interogările
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Determină numărul de fire care pot intra în InnoDB concomitent.
innodb_concurrency_tickets=5000

# Specifică cât timp, în milisecunde (ms) trebuie să rămână acolo un bloc inserat în vechea sublistă după primul său acces înainte
# poate fi mutat în noua sublistă.
innodb_old_blocks_time=1000

# Specifică numărul maxim de fișiere .ibd pe care MySQL le poate menține deschise la un moment dat. Valoarea minimă este 10.
innodb_open_files=300

# Când această variabilă este activată, InnoDB actualizează statisticile în timpul declarațiilor de metadate.
innodb_stats_on_metadata=0

# Când innodb_file_per_table este activat (prestabilit în 5.6.6 și versiuni ulterioare), InnoDB stochează datele și indecșii pentru fiecare tabel nou creat
# într-un fișier .ibd separat, mai degrabă decât în ​​spațiul tabelă de sistem.
innodb_file_per_table=1

# Utilizați următoarea listă de valori: 0 pentru crc32, 1 pentru strict_crc32, 2 pentru innodb, 3 pentru strict_innodb, 4 pentru none, 5 pentru strict_none.
innodb_checksum_algorithm=0

# Numărul de solicitări de conexiune restante pe care MySQL le poate avea.
# Această opțiune este utilă atunci când firul principal MySQL primește multe cereri de conectare într-un timp foarte scurt.
# Apoi, durează ceva timp (deși foarte puțin) pentru ca firul principal să verifice conexiunea și să înceapă un nou thread.
# Valoarea back_log indică câte cereri pot fi stivuite în acest scurt timp înainte de MySQL pentru moment
# nu mai răspunde la solicitări noi.
# Trebuie să creșteți acest lucru numai dacă vă așteptați la un număr mare de conexiuni într-o perioadă scurtă de timp.
back_log=80

# Dacă aceasta este setată la o valoare diferită de zero, toate tabelele sunt închise la fiecare flush_time secunde pentru a elibera resurse și
# sincronizați datele neștercate pe disc.
# Această opțiune este utilizată cel mai bine numai pe sisteme cu resurse minime.
flush_time=0

# Dimensiunea minimă a memoriei tampon care este utilizată pentru scanări de index simplu, scanări de index de interval și îmbinări care nu folosesc
# indexează și astfel efectuează scanări complete de tabel.
join_buffer_size=256K

# Dimensiunea maximă a unui pachet sau a oricărui șir generat sau intermediar sau a oricărui parametru trimis de către
# mysql_stmt_send_long_data() Funcția API C.
max_allowed_packet=1024M

# Dacă mai multe solicitări de conexiune succesive de la o gazdă sunt întrerupte fără o conexiune reușită,
# serverul blochează gazda să mai efectueze conexiuni ulterioare.
max_connect_errors=100

# Modifică numărul de descriptori de fișiere disponibili pentru mysqld.
# Ar trebui să încercați să creșteți valoarea acestei opțiuni dacă mysqld vă dă eroarea „Prea multe fișiere deschise”.
open_files_limit=4161

# Dacă vedeți multe sort_merge_passes pe secundă în rezultatul SHOW GLOBAL STATUS, puteți lua în considerare creșterea
# sort_buffer_size value pentru a accelera operațiunile ORDER BY sau GROUP BY care nu pot fi îmbunătățite cu optimizarea interogărilor
# sau indexare îmbunătățită.
sort_buffer_size=256K

# Numărul de definiții de tabel (din fișiere .frm) care pot fi stocate în memoria cache a definițiilor.
# Dacă utilizați un număr mare de tabele, puteți crea un cache mare de definiții de tabel pentru a accelera deschiderea tabelelor.
# Cache-ul definiției tabelului ocupă mai puțin spațiu și nu utilizează descriptori de fișiere, spre deosebire de memoria cache normală a tabelului.
# Valorile minime și implicite sunt ambele 400.
table_definition_cache=1400

# Specificați dimensiunea maximă a unui eveniment de jurnal binar pe rând, în octeți.
# Rândurile sunt grupate în evenimente mai mici decât această dimensiune, dacă este posibil. Valoarea ar trebui să fie un multiplu de 256.
binlog_row_event_max_size=8K

# Dacă valoarea acestei variabile este mai mare decât 0, o replică își sincronizează fișierul master.info pe disc.
# (folosind fdatasync()) după fiecare eveniment sync_master_info.
sync_master_info=10000

# Dacă valoarea acestei variabile este mai mare decât 0, serverul MySQL își sincronizează jurnalul de releu pe disc.
# (folosind fdatasync()) după fiecare scriere sync_relay_log în jurnalul de releu.
sync_relay_log=10000

# Dacă valoarea acestei variabile este mai mare decât 0, o replică își sincronizează fișierul relay-log.info pe disc.
# (folosind fdatasync()) după fiecare tranzacție sync_relay_log_info.
sync_relay_log_info=10000

# Încărcați pluginurile mysql la pornire."plugin_x ; plugin_y".
# plugin_load

# Portul TCP/IP pe care va asculta protocolul MySQL Server X.
# loose_mysqlx_port=33060
# relay-log=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-relay-bin.log
relay-log=F:\MySQL\Data\mysql-relay-bin.log

# log_bin=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-bin.log
log_bin=F:\MySQL\Data\mysql-bin.log

binlog_do_db=jira_datacenter
binlog_format=MIXED

Ți-aș sugera inițial duplicat configurația dvs. existentă locală, MySql, pe mașina dvs. virtuală Azure. Toate aceleași setări. În acest fel, compari ca și cum ai.
Chiar acum, ai prea multe modificări care au loc în același timp și nu puteți vedea care dintre ele cauzează problema.

... observăm că, deși unele dintre interogări durează mai mult de 30 de minute pentru a rula, (se pare că rulează pe serverul local în doar 5 minute) ...

Chiar și 5 minute este a perioadă lungă de timp pentru a rula o interogare.
Pot fi aceste interogări acordat, chiar și în instanță on-prem?
Extrag cantități mari de date prin rețea? Simplist, VM-ul dvs. Azure se află la capătul unei bucăți mai lungi de șir electronic decât instanța dvs. locală (și poate fi supusă tuturor tipurilor de rutare a rețelei, gestionării traficului etc.).

... utilizarea procesorului pe VM rămâne foarte scăzută...

Din nou, se pare că multe date sunt mutate cu foarte puțină procesare pe partea serverului.
Sunt cele discuri ocupat? Interogările care fac o mulțime de actualizări sau folosesc o mulțime de seturi de rezultate tranzitorii vor fi thrash discurile fără a folosi prea mult CPU. (Dimpotrivă, o mulțime de [în memorie] scanare de masă se evidențiază de obicei prin încărcare mare a procesorului și activitate scăzută a discului).