10 odpowiedzi na ten temat

[maminowiec]

Witam , wynik z mysqltuner

Cytuj

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

Cytuj

[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ć ?

[Aimer]

Zastosowales sie do wskazowek skryptu?

[maminowiec]

Tak zwiększyłem

lecz nadal chce wiecej ...

Cytuj

-------- 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]

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]

Cytuj

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

Po tym był restart ....

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

Ten post był edytowany przez squeezer dnia: 22 lipiec 2011 - 00:07

[bellerofont]

squeezer, o 22 lipiec 2011 - 00:06, powiedział:

(...) 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]

@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)

Ten post był edytowany przez kafi dnia: 22 lipiec 2011 - 00:39

[squeezer]

bellerofont, o 22 lipiec 2011 - 00:37, powiedział:

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.

Ten post był edytowany przez squeezer dnia: 22 lipiec 2011 - 01:06

[krdc.pl]

squeezer, o 22 lipiec 2011 - 01:05, powiedział:

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

[squeezer]

krdc.pl, o 22 lipiec 2011 - 08:11, powiedział:

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.