MySQL, igual que la majoria de gestors de bases de dades, permet modificar fàcilment els seus paràmetres que controlen mides de memòria dedicats a determinades tasques, utilització de recursos, límits de concurrència, etc.
Ajustant adequadament aquests paràmetres es poden obtenir moltes millores de rendiment, sobretot si el servidor / s de la base de dades no va sobrat de recursos, i si per la part d'optimització SQL no es pot millorar molt més.
Jo fa poc he fet alguns ajustaments bàsics en una base de dades MySQL, així que vaig aprofitar per explicar una mica el procés que he seguit per a qui busqui una manera senzilla de fer una primera optimització de paràmetres a la base de dades. Amb això no vull dir que aquesta sigui la millor manera de fer-ho, només la que a mi m'ha resultat bé;)
El primer comentar que pot ser molt útil fer una ullada dins de phpMyAdmin a les seccions 'Mostra informació de temps d'execució de MySQL' i 'Mostra les variables del sistema MySQL', normalment accessibles des de la pàgina principal de l'aplicació.
La primera mostra informació i estadístiques que el sistema recull i manté des de la seva arrencada. Cal prestar especial atenció als valors de variables que es mostren en color vermell, i als consells que s'indiquen a la dreta d'aquests valors.
Consulteu les variables serveix per saber els valors actuals dels paràmetres que més endavant podríem modificar per millorar el rendiment.
Un cop fet això segurament ja tindrem alguna pista de per on cal atacar, però el que interessa és una mica més concret que ens suggereixi qualsevol paràmetre podríem tocar exactament per millorar el rendiment de la nostra base de dades. Per això jo he utilitzat mysqltunner.pl, que és un script en perl que analitza el rendiment de la BD i els valors dels paràmetres i després de l'anàlisi realitza suggeriments de modificació d'alguns d'aquests valors.
Si es compleixen els requerimientos, utilitzar l'script en un sistema Linux / Unix és tan senzill com executar des línia de comandes
> wget mysqltuner.pl > chmod 755 mysqltuner.pl > perl mysqltuner.pl
Un cop fet això, l'script et pot tornar uns resultats similars a aquests:
MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> Bug reports, feature requests, and downloads at https://mysqltuner.com/ Run with '--help' for additional options and output filtering -------- General Statistics ---------------------------------------- ---------- [-] Skipper version check for MySQLTuner script [OK] Currently running supported MySQL versió 5.0.45 [OK] Operating on 32 bits architecture with less than 2 GB RAM -------- Storage Engine Statistics --------------------------------------- ---- [-] Status:-Archive-BDB-Federat + InnoDB-ISAM-NDBCluster [-] Data in MyISAM bles: 156M (Tables: 390) [-] Data in InnoDB tables: 2M (Tables: 126) [!!] Total Fragmented bles: 46 -------- Performance Metrics ---------------------------------------- --------- [-] Up for: 18m 8s (296K q [272.847 qps], 689 Conn, TX: 1B, RX: 39M) [-] Reads / Writer: 94% / 6% [-] Total buffers: 35.0M global + 2.7M per thread (100 màx threads) [OK] Maximum possible memory usage: 303.7M (33% of installed RAM) [OK] Slow queries: 0% (0/296K) [OK] Highest usage of available connections: 8% (8 / 100) [OK] Key buffer size / total MyISAM indexes: 8.0M/52.0M [OK] Key buffer hit rate: 99.2% (2M cache / 21k Reads) [OK] Query cache efficiency: 81.6% (237K cache / 291K Selects) [!!] Query cache Prunes per day: 3650717 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts) [!!] Joins performed without indexes: 33 [!!] Temporary bles created on disk: 26% (1K on disk / 7K total) [!!] Thread cache is disabled [OK] Table cache hit rate: 48% (128 open / 265 opened) [OK] Open file limit used: 11% (229/2K) [OK] Table locks acquired immediately: 99% (73K immediate / 73K locks) [!!] InnoDB data size / buffer pool: 2.0M/2.0M -------- Recommendations ----------------------------------------- ------------ General recommendations: Run OPTIMITZAR TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilitzi indexes When making Adjustments, fer tmp_table_size / max_heap_table_size equal Redueix your SELECT DISTINCT queries without LIMIT clauses Setembre thread_cache_size to 4 as a starting value Variables to Adjust: query_cache_size (> 8M) join_buffer_size (> 128.0K, or always use indexes with joins) tmp_table_size (> 60M) max_heap_table_size (> 16M) thread_cache_size (start at 4) innodb_buffer_pool_size (> = 2M)
En temes d'optimització mai no s'ha de tocar les coses alegrement, de manera que ara el que toca és estudiar la informació i les recomanacions que proposa l'script i anar aplicant les que ens semblin més encertades.
La primera recomanació és que s'executi la comanda OPTIMITZAR TABLE sobre diverses taules en les que ha detectat que hi ha fragmentació. Per a això cal esbrinar què taules poden requerir una defragmentació i aplicar aquesta comanda sobre elles.
Les següents recomanacions ja són suggeriments sobre els paràmetres de la BD. L'aplicació mostra uns valors d'inici en els casos en que la proposta és augmentar el valor. El millor és anar pujant a poc a poc i anar validant si obtenim millores, i també que no arribem a utilitzar més memòria de la que tenim disponible.
Per modificar els valors dels paràmetres es pot editar el fitxer my.cnf, que sol trobar al directori / etc
En aquest cas, per exemple, podríem haver inserit en el nostre my.cnf les línies
# Parametros modificados por recomendaciones de sqltuner query-cache-size = 16M join-buffer-size = 256K tmp-table-size = 90M max-heap-table-size = 90M thread-cache-size = 4 innodb-buffer-pool-size = 4M # Fin de modificaciones de optimizacion
Després cal reiniciar la base de dades per que aquests canvis tinguin efecte.
Enviar comentaris finalment que perquè la informació que ens proporciona l'script sigui fiable, la base de dades hauria de portar funcionant ininterrompudament com a mínim 24 hores, de manera que per anar ajustant els valors amb execucions de sqltuner no serveix modificar els paràmetres i tornar a executar l'script al moment.
Després de reiniciar la BD per aplicar els canvis cal esperar un dia o dos abans de tornar a demanar ajuda al script per tunejar la nostra base de dades.
