Tuning serwera MySQL
Dostrajanie serwerów baz danych to sprawa wyjątkowo skomplikowana. Zostało na ten temat napisanych wiele książek, niejeden „spec” poświęcił niejedną godzinę, żeby to ustrojstwo chodziło wydajnie i gładko. Zadanie wymaga naprawdę wielkiej wiedzy i sporego doświadczenia, co jest – szczególnie w obecnych czasach – dość kosztownym zasobem.
Ale co w przypadku, kiedy nasz serwer dostaje czkawki, nie mamy na koncie wystarczających środków, żeby zatrudnić specjalistę, a musimy coś zrobić? Jest taki fajny skrypcik napisany w PERLu, który umożliwia (bardzo) wstępną diagnozę w stylu „co mogę zrobić, żeby działało lepiej”. Nie poda nam wprawdzie przepisu na usprawnienie wszystkiego i nie spowoduje, że MySQL zacznie odpowiadać 10x szybciej, ale wskaże nam, gdzie możemy poszukać i skąd wyciągnąć jeszcze odrobinę mocy.
Skrypt ten magiczny to mysqltuner i działa o tak:
[kbechler@ns ~]$ ~/mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login:
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 48163)
[--] Data in InnoDB tables: 129M (Tables: 185)
[--] Data in MEMORY tables: 0B (Tables: 6)
[!!] Total fragmented tables: 120
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 61d 3h 39m 32s (472M q [89.360 qps], 31M conn, TX: 2707B, RX: 63B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 282.0M global + 2.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 969.5M (12% of installed RAM)
[OK] Slow queries: 0% (684/472M)
[OK] Highest usage of available connections: 60% (152/250)
[!!] Key buffer size / total MyISAM indexes: 8.0M/313.6M
[!!] Key buffer hit rate: 92.3% (6B cached / 491M reads)
[OK] Query cache efficiency: 36.4% (131M cached / 362M selects)
[!!] Query cache prunes per day: 2758813
[OK] Sorts requiring temporary tables: 0% (132K temp sorts / 18M sorts)
[!!] Joins performed without indexes: 217457
[!!] Temporary tables created on disk: 44% (44M on disk / 99M total)
[OK] Thread cache hit rate: 93% (2M created / 31M connections)
[!!] Table cache hit rate: 0% (2K open / 4M opened)
[!!] Open file limit used: 89% (3K/4K)
[OK] Table locks acquired immediately: 99% (256M immediate / 256M locks)
[!!] Connections aborted: 28%
[!!] InnoDB data size / buffer pool: 129.8M/64.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 313.6M)
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 192M)
max_heap_table_size (> 256M)
table_cache (> 2048)
open_files_limit (> 4356)
innodb_buffer_pool_size (>= 129M)
Teoretycznie powinienem postąpić zgodnie z powyższymi sugestiami i pozwiększać różne parametry do podanych tam poziomów. Ale w praktyce warto zmienić jeden (no, może dwa) parametr i popatrzeć, czy maszyna zaczęła się zachowywać choć trochę lepiej. Patrzenie mozna uprawiać np. przy pomocy Cacti i szablonów, które niedawno opisywałem.
A tak zupełnie przy okazji – jakiś czas temu natrafiłem na świetny blog (hmmm, „świetnego bloga?”) na temat MySQLa właśnie. Znajduje się pod adresem blog.ksiazek.info, prowadzi go Krzysztof Książek i zdecydowanie warto tam zaglądać regularnie.