Optymalizacja mysql

[maminowiec 9]

Witam , wynik z mysqltuner

 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.55

[OK] Operating on 64-bit architecture

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 1G (Tables: 2810)

[--] Data in InnoDB tables: 128K (Tables: 8)

[--] Data in MEMORY tables: 7M (Tables: 3)

[!!] Total fragmented tables: 280

 

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 37d 7h 16m 48s (308M q [95.725 qps], 14M conn, TX: 1332B, RX: 61B)

[--] Reads / Writes: 79% / 21%

[--] Total buffers: 1.4G global + 520.5M per thread (800 max threads)

[!!] Maximum possible memory usage: 408.0G (5228% of installed RAM)

[OK] Slow queries: 0% (526/308M)

[OK] Highest usage of available connections: 22% (181/800)

[OK] Key buffer size / total MyISAM indexes: 32.0M/402.9M

[OK] Key buffer hit rate: 99.9% (1293B cached / 1B reads)

[OK] Query cache efficiency: 58.7% (139M cached / 238M selects)

[!!] Query cache prunes per day: 26083

[OK] Sorts requiring temporary tables: 0% (51K temp sorts / 21M sorts)

[!!] Joins performed without indexes: 184685

[!!] Temporary tables created on disk: 27% (4M on disk / 16M total)

[OK] Thread cache hit rate: 99% (181 created / 14M connections)

[!!] Table cache hit rate: 15% (6K open / 38K opened)

[OK] Open file limit used: 68% (8K/13K)

[OK] Table locks acquired immediately: 97% (213M immediate / 218M locks)

[OK] InnoDB data size / buffer pool: 128.0K/1.0G

 

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Reduce your overall MySQL memory footprint for system stability

Enable the slow query log to troubleshoot bad queries

Increasing the query_cache size over 128M may reduce performance

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

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

query_cache_size (> 256M) [see warning above]

join_buffer_size (> 512.0M, or always use indexes with joins)

tmp_table_size (> 16M)

max_heap_table_size (> 500M)

table_cache (> 6144)

 

 

mój my.cnf

[mysqld]

 

local-infile=0

 

concurrent_insert=2

 

skip-external-locking

 

query_cache_limit=16M

query_cache_size=256M

query_cache_type=1

 

max_connections=800

#interactive_timeout=100

#wait_timeout=100

connect_timeout=10

thread_cache_size=256

 

key_buffer=32M

 

join_buffer=512M

tmp_table_size=16M

max_allowed_packet=16M

max_heap_table_size=500M

 

table_cache=6144

 

#record_buffer=1M

sort_buffer_size=4M

read_buffer_size=4M

#max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency

thread_concurrency=8

myisam_sort_buffer_size=128M

server-id=1

 

 

innodb_additional_mem_pool_size=16M

innodb_buffer_pool_size = 1G

innodb_log_buffer_size = 64M

innodb_log_file_size = 128M

innodb_file_per_table

innodb_data_file_path = ibdata1:128M:autoextend

innodb_autoextend_increment = 10

innodb_file_io_threads = 8

#innodb_write_io_threads = 4

#innodb_read_io_threads = 4

innodb_thread_concurrency = 16

#innodb_flush_method=O_DIRECT

innodb_flush_log_at_trx_commit = 2

innodb_log_files_in_group = 2

innodb_lock_wait_timeout = 120

innodb_max_dirty_pages_pct = 90

 

[safe_mysqld]

err-log=/var/log/mysqld.log

open_files_limit=8192

 

[mysqldump]

quick

max_allowed_packet=16M

 

[mysql]

no-auto-rehash

#safe-updates

 

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

 

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

 

[mysqlhotcopy]

interactive-timeout

 

 

Co mogę poprawić ?

[HaPe 242]

Zastosowales sie do wskazowek skryptu?

[maminowiec 9]

Tak zwiększyłem

 

lecz nadal chce wiecej ...

 

 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.55

[OK] Operating on 64-bit architecture

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 1G (Tables: 2810)

[--] Data in InnoDB tables: 128K (Tables: 8)

[--] Data in MEMORY tables: 0B (Tables: 3)

[!!] Total fragmented tables: 279

 

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 4m 52s (51K q [175.818 qps], 3K conn, TX: 449M, RX: 10M)

[--] Reads / Writes: 81% / 19%

[--] Total buffers: 1.4G global + 1.0G per thread (800 max threads)

[!!] Maximum possible memory usage: 808.1G (10355% of installed RAM)

[OK] Slow queries: 0% (0/51K)

[OK] Highest usage of available connections: 7% (58/800)

[OK] Key buffer size / total MyISAM indexes: 32.0M/403.6M

[OK] Key buffer hit rate: 100.0% (262M cached / 35K reads)

[OK] Query cache efficiency: 43.7% (16K cached / 37K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 5K sorts)

[!!] Joins performed without indexes: 111

[!!] Temporary tables created on disk: 33% (1K on disk / 5K total)

[OK] Thread cache hit rate: 98% (58 created / 3K connections)

[OK] Table cache hit rate: 98% (609 open / 616 opened)

[OK] Open file limit used: 4% (913/19K)

[OK] Table locks acquired immediately: 95% (43K immediate / 45K locks)

[OK] InnoDB data size / buffer pool: 128.0K/1.0G

 

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Reduce your overall MySQL memory footprint for system stability

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

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

join_buffer_size (> 1.0G, or always use indexes with joins)

tmp_table_size (> 64M)

max_heap_table_size (> 1000M)

[BlueMan 69]

Nie zastosowałeś się jednak do wskazówek skryptu

 [--] Up for: 4m 52s (51K q [175.818 qps], 3K conn, TX: 449M, RX: 10M)

Po ~5 min skryptu już ma wiedzieć ile i czego potrzebuje, tak? Przeczytaj sobie ile uptime'u serwera mysql zalecają przed robieniem kolejnego testu.

[maminowiec 9]

-------- Performance Metrics -------------------------------------------------

[--] Up for: 37d 7h 16m 48s (308M q [95.725 qps], 14M conn, TX: 1332B, RX: 61B)

 

Po tym był restart ....

[Gość squeezer]

Na początek chciałbym zapytać się o jedną rzecz. W czym w ogóle jest problem? Brakuje procesora? Brakuje pamięci? Dysk jest przeciążony?

 

Jeśli chodzi o podesłane przez Ciebie wyniki z mysqltunera, to rzeczywiście, jak Blueman zauważył, w drugim przypadku serwer działał za krótko w porównaniu z tym co zaleca skrypt. To raz.

 

Dwa, wyrzuć mysqltunera jak najdalej i już nigdy go nie używaj. Do tej pory miałem stosunek trochę ambiwalentny co do zwracanych przez niego wyników. Sam go nie stosowałem, ale sądziłem że to nic strasznego nie jest. Po Twoich postach wiem, że nie miałem racji. Zwróć uwagę na dwa elementy:

 

Przed Twoimi zmianami w konfiguracji:

[!!] Maximum possible memory usage: 408.0G (5228% of installed RAM)

 

Po Twoich zmianach:

[!!] Maximum possible memory usage: 808.1G (10355% of installed RAM)

 

W skrócie, skrypt zaproponował Ci zmiany znacznie zwiększające możliwość wywalenia całego serwera w kosmos. O co chodzi? Po zmianach MySQL może zaalokować dwukrotnie więcej pamięci (800GB) niż przed zmianami. Nie że poprzednia konfiguracja była bezpieczna - także istniała możliwość zabicia serwera przez brak pamięci. Różnica jest taka, że po zmianach wystarczy do tego mniejsza liczba połączeń.

 

Faktu uznania za OK czegoś takiego:

[OK] Key buffer size / total MyISAM indexes: 32.0M/403.6M

 

i nie zasugerowania że może jednak te indeksy w pamięci upchnąć, pozwolę sobie nie komentować.

 

Nie rozumiem też dlaczego miałbyś zwiększać w nieskończoność join_buffer_size. Trzeba na zapytania popatrzeć i sprawdzić dlaczego po pięciu minutach masz 111 JOINów opartych na skanie tabeli, a nie podbijać zmienne w konfigu.

 

Zapraszam Cię do lektury dwóch postów z mojego bloga. Pierwszy opisuje sposób zebrania informacji o systemie i MySQL w taki sposób, który umożliwia powiedzenie czegokolwiek na temat tego, co serwerowi dolega:

 

http://blog.ksiazek....serwerem-mysql/

 

Drugi post wyjaśnia dlaczego "optymalizacja MySQL" =/= zmiany w konfiguracji sugerowane przez jakikolwiek automat:

 

http://blog.ksiazek....iguracja-mysql/

 

Tak żeby było wszystko jasne i żebyśmy się dobrze zrozumieli, to dodam tylko że chętnie Ci pomogę w ustaleniu co jest nie tak. Konieczne jest tylko abyś napisał w czym jest problem i podesłał informacje o których pisałem na blogu. Bez tego wiele nie będę w stanie pomóc.

Edytowano Lipiec 21, 2011 przez squeezer (zobacz historię edycji)

[beliq 442]

(...) bla bla bla @squeezer

Potraktowałeś temat MySQL'a na swoim blog w tak pobieżny sposób,

że ja osobiście Cię proszę, abyś tu na forum nie wyskakiwał z takimi postami...

[kafi 2425]

@squeezer - wyniki mysqltuner też trzeba by poczytać ze zrozumieniem, bo wbrew pozorom całościowe rady nie są takie głupie... Jest przecież wyraźnie opisane, że...

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 512.0M, or always use indexes with joins)

Edytowano Lipiec 21, 2011 przez kafi (zobacz historię edycji)

[Gość squeezer]

Potraktowałeś temat MySQL'a na swoim blog w tak pobieżny sposób,

że ja osobiście Cię proszę, abyś tu na forum nie wyskakiwał z takimi postami...

 

Ja natomiast bardzo Cię proszę żebyś się odniósł do konkretów, a nie pisał tego typu ogólniki. Z czym konkretnie się nie zgadzasz? Chętnie się dowiem gdzie błądzę.

 

@kafi

 

Ok, tylko problem z tym, że tego typu sugestia jest zła. Ile można podbijać join_buffer_size? W pierwszym przypadku MySQL mógł zaalokować 400GB pamięci przy prawdopodobnie 8GB na serwerze. Oczywiście, to jest worst case scenario przy wykorzystaniu wszystkich 800 połączeń, ale dla mnie już to jest za dużo.

 

Pierwsze co to trzeba zrobić to przeglądnąć zapytania i sprawdzić dlaczego JOINy nie korzystają z indeksów. Może tak być, że faktycznie nie da się nic z tym zrobić. Zazwyczaj jednak okazuje się, że to problem po stronie zapytania i nie rozwiąże się go przez zmiany w konfiguracji a tylko przez dodanie indeksów lub przepisanie zapytania. Tak, wiem że w wyniku skrypt pisze o indeksach. Tylko że nadal uważa dalsze podbijanie konfiguracji za coś poprawnego.

Edytowano Lipiec 22, 2011 przez squeezer (zobacz historię edycji)

[krdc.pl 91]

Ja natomiast bardzo Cię proszę żebyś się odniósł do konkretów, a nie pisał tego typu ogólniki. Z czym konkretnie się nie zgadzasz? Chętnie się dowiem gdzie błądzę.

 

@kafi

 

Ok, tylko problem z tym, że tego typu sugestia jest zła. Ile można podbijać join_buffer_size?

 

ktora sugestia jest zla ?

 

bo mi sie wydaje ze program swoje, a Ty swoje ....

[Gość squeezer]

ktora sugestia jest zla ?

 

bo mi sie wydaje ze program swoje, a Ty swoje ....

 

Heh... Mamy coś takiego:

 

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

 

Na tym komunikacie powinno się zakończyć działanie skryptu. Po co mieszać dalej? Mieszanie to, swoją drogą, jest skuteczne, bo niektórzy zignorują to ostrzeżenie i wprowadzą proponowane zmiany.

 

Z resztą, nawet zakładając że z pamięcią jest ok, to propozycja podniesienia join_buffer_size do tak wysokich wartości jest zła. Zauważ, że podane jest to jako alternatywa: >1.0G, or always use indexes with joins. Czyli to, albo to. Pewnie że dobrze by było, żeby dokładnie przeanalizować zapytania. Tyle że prostsze jest oczywiście podbicie cyferek w my.cnf i to właśnie zrobi większość użytkowników.

 

Join buffer to pamięć, która jest alokowana per JOIN. Czyli, w obrębie jednego połączenia, jeśli wykonamy SELECTa z JOINem siedmiu tabel, to zaalokowane może zostać 7GB pamięci. Można o tym przeczytać choćby w oficjalnej dokumentacji MySQL. Można tam także przeczytać, że stosowanie dużych wartości dla globalnej zmiennej join_buffer_size skutkować może spadkiem wydajności ze względu na czas potrzebny do zaalokowania dużej ilości pamięci. Sugerują żeby tą zmienną podbijać tylko wtedy, gdy to jest potrzebne - na poziomie sesji, świadomie, gdy wiemy że wykonywać będziemy jakieś duże JOINy.

 

W necie, na MySQL Performance Blog konkretnie, można znaleźć informacje na temat działania tego mechanizmu na poziomie kodu źródłowego MySQL. Wynika z nich że nie tyle może zostać zaalokowane, co zawsze zostanie. Zakładając, że pamięci wystarczy. Jeśli system zaalokować nie pozwoli, to JOIN z bufora korzystać nie będzie. Cóż, nie wnikałem w to tak głęboko, w końcu oznajmiono mi że traktuję MySQL po łebkach, więc się staram dostosować Sprawdziłem tylko czy odpowiedni kod nie zmienił się między 5.1.47 a 5.1.57. Nie zmienił się.

 

Wyobraź sobie co się stanie, jeśli mamy serwer z 8GB RAM, ustawimy join_buffer_size na 1GB, zrobimy JOINa na siedem tabel a pamięci będzie na tyle, że te 7GB uda się zaalokować i faktycznie zostanie wykorzystane. Zużycie pamięci skacze z kilkuset MB na prawie 8GB. Cache dyskowy znika, dyski się rozkręcają, serwer przycina. Oczywiście, to jest najgorszy scenariusz. Normalnie po prostu albo bufor nie zostanie zaalokowany, bo pamięci braknie, albo siądzie wydajność bo za każdym razem trzeba alokować gigabajty pamięci, podczas gdy wykorzystywane są tylko megabajty.