Blocarea serverului cauzată de utilizarea procesorului MySQL

Premisă:

Nu sunt un DBA și nu sunt interesat de servere, dar sunt singura persoană din companie care este capabilă să folosească puțin astfel de lucruri.

Avem un Windows VPS cu următoarele specificații:

CPU: Intel Xeon E5-2630 v4 2.20GHz
RAM: 60 GB
SO: Windows Server 2016 Datacenter
HDD: 2TB SSD

Aici am găzduit aplicațiile mele web care accesează DB găzduit pe același server, aplicațiile web sunt utilizate de +- 1000 de utilizatori care necesită datele de la DB prin API-ul aplicațiilor web, versiunea MySQL este: 8.0.20 (MySQL Comminity Server - GPL)

Și iată my.ini

# 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/8.0/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=2
[mysqld]

# Următoarele trei opțiuni se exclud reciproc pentru SERVER_PORT de mai jos.
# skip-networking=
# enable-named-pipe=
# shared-memory=

# 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 8.0/"

# Calea către rădăcina bazei de date
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data

# 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=

# Pluginul implicit de autentificare care trebuie utilizat la conectarea la server
default_authentication_plugin=parola_mysql_native

# 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_ENGINE_SUBSTITUTION"

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

general-log=0

general_log_file="VMI384596.log"

slow-query-log=1

slow_query_log_file="VMI384596-slow.log"

long_query_time=10

# Înregistrare erori.
log-error="VMI384596.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="VMI384596-bin"

# ***** Legat de replicarea grupului *****
# Setează formatul de înregistrare binar și poate fi oricare dintre STATEMENT, ROW,
# sau MIXTE. ROW este sugerat pentru replicarea grupului.
# binlog_format=

# ***** Legat de replicarea grupului *****
# Determină masterul să scrie o sumă de control pentru fiecare eveniment din jurnalul binar.
# binlog_checksum acceptă valorile NONE (dezactivat) și CRC32.
# Valoarea implicită este CRC32. Când este dezactivat (valoarea NONE), serverul verifică
# că scrie doar evenimente complete în jurnalul binar prin scriere
# și verificarea duratei evenimentului (mai degrabă decât o sumă de control) pentru fiecare eveniment.
# NIMIC nu trebuie utilizat cu Replicarea grupului.
# binlog_checksum=

# ***** Legat de replicarea grupului *****
# Numele de bază pentru jurnalul de releu. Serverul creează fișiere jurnal de retransmisie
# secvență prin adăugarea unui sufix numeric la numele de bază. Dacă specificați acest lucru
#, valoarea specificată este, de asemenea, utilizată ca nume de bază pentru jurnalul de releu
# fișier index. Jurnalele de releu măresc viteza prin utilizarea echilibrării încărcării între discuri.
# relay_log=

# ***** 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 master sau slave de replicare.
server-id=1

# ***** Legat de replicarea grupului *****
# Numele gazdei sau adresa IP a slave-ului care urmează să fie raportate masterului
# în timpul înregistrării sclavilor. Această valoare apare în rezultatul SHOW SLAVE HOSTS
# pe serverul principal. Lăsați valoarea nesetată dacă nu doriți ca sclavul să o facă
# se înregistrează la master.
# report_host=0.0

# ***** Legat de replicarea grupului *****
# Numărul portului TCP/IP pentru conectarea la slave, care urmează să fie raportat masterului în timpul
# înregistrarea sclavilor. Setați acest lucru numai dacă slave ascultă pe un port care nu este implicit sau dacă
# aveți un tunel special de la master sau alți clienți la slave.
report_port=3306

# ***** Legat de replicarea grupului *****
# Această opțiune specifică dacă sunt identificatorii de tranzacții globale (GTID).
# folosit pentru a identifica tranzacțiile. ON trebuie utilizat cu Replicarea grupului.
# gtid_mode=

# ***** Legat de replicarea grupului *****
# Când este activat, serverul impune consistența GTID permițând executarea
# numai instrucțiuni care pot fi înregistrate în siguranță folosind un GTID. Trebuie să setați asta
# opțiune la ON înainte de a activa replicarea bazată pe GTID.
# enforce_gtid_consistency=

# ***** Legat de replicarea grupului *****
# Dacă actualizările primite de un server slave de la un server master ar trebui să fie
# conectat la propriul jurnal binar al sclavului. Înregistrarea binară trebuie să fie activată
# sclavul pentru această variabilă să aibă vreun efect. ON trebuie utilizat cu
# Replicarea grupului.
# log_slave_updates=

# ***** Legat de replicarea grupului *****
# Stabilește dacă serverul slave înregistrează informații despre starea master și despre conexiune
# într-un tabel InnoDB din baza de date mysql sau într-un fișier din directorul de date.
# Setarea TABLE este necesară când sunt configurate mai multe canale de replicare.
# master_info_repository=

# ***** Legat de replicarea grupului *****
# Stabilește dacă serverul slave își înregistrează poziția în jurnalele de releu la un InnoDB
# tabel din baza de date mysql sau într-un fișier din directorul de date. Setarea TABLE este
# necesar când sunt configurate mai multe canale de replicare.
# relay_log_info_repository=

# ***** 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=

# NOTĂ: Modificați această valoare după ce inițializarea serverului nu va avea efect.
nume_tabele_minuscule=1

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/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 = 2000

# 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

# 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

# *** 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=10G

# 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=256K

# 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 = 64M

# 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

# *** 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 = 1

# 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, 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.
innodb_buffer_pool_size=38G

# 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=48M

# 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=21

# 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

# 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 = niciunul

skip-innodb-doublewrite=

# 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=4M

# 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, un slave de replicare îș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, un slave de replicare îș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

# 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.

Cum ar trebui să-mi îmbunătățesc performanța MySQL și să previn blocarea serverului prin limitarea utilizării procesorului?