Optymalizacja MySQL....

[Elber 0]

Witam, potrzebuje pomocy z optymalizacją MySQL. Szczerze nie znam się na optymalizacji... Na serwerze znajdują się 3 fora na silniku vB jedno 200 tyś userów, drugie 11 tyś userów a trzecie małe 3 tyś. Procek - Intel® Core2 Quad Q6700 @ 2.66GHz | 4 GB ramu | Dysk 1 TB

 

To wynik z MySQLTuner

 

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

 

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-24+lenny4

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

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

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

[--] Data in MyISAM tables: 1008M (Tables: 918)

[--] Data in InnoDB tables: 24M (Tables: 30)

[--] Data in MEMORY tables: 379K (Tables: 12)

[!!] Total fragmented tables: 139

 

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

[--] Up for: 6m 50s (29K q [71.966 qps], 2K conn, TX: 1B, RX: 8M)

[--] Reads / Writes: 87% / 13%

[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)

[OK] Maximum possible memory usage: 320.5M (8% of installed RAM)

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

[OK] Highest usage of available connections: 12% (12/100)

[!!] Key buffer size / total MyISAM indexes: 16.0M/574.0M

[!!] Key buffer hit rate: 81.8% (237K cached / 43K reads)

[OK] Query cache efficiency: 64.7% (13K cached / 21K selects)

[!!] Query cache prunes per day: 191133

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

[!!] Joins performed without indexes: 6

[OK] Temporary tables created on disk: 1% (21 on disk / 1K total)

[OK] Thread cache hit rate: 99% (12 created / 2K connections)

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

[OK] Open file limit used: 11% (121/1K)

[OK] Table locks acquired immediately: 99% (12K immediate / 12K locks)

[!!] InnoDB data size / buffer pool: 24.3M/8.0M

 

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

General recommendations:

Run OPTIMIZE 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 utilize indexes

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

key_buffer_size (> 574.0M)

query_cache_size (> 16M)

join_buffer_size (> 128.0K, or always use indexes with joins)

table_cache (> 64)

innodb_buffer_pool_size (>= 24M)

 

A to mój my.conf

 

#

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

 

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes

# escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

 

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

 

# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

 

[mysqld]

#

# * Basic Settings

#

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

language = /usr/share/mysql/english

skip-external-locking

#

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

#

# * Fine Tuning

#

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 128K

thread_cache_size = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover = BACKUP

#max_connections = 100

#table_cache = 64

#thread_concurrency = 10

#

# * Query Cache Configuration

#

query_cache_limit = 1M

query_cache_size = 16M

#

# * Logging and Replication

#

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

#log = /var/log/mysql/mysql.log

#

# Error logging goes to syslog. This is a Debian improvement

#

# Here you can see queries with especially long duration

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 2

#log-queries-not-using-indexes

#

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

#server-id = 1

#log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

#binlog_do_db = include_database_name

#binlog_ignore_db = include_database_name

#

# * BerkeleyDB

#

# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.

skip-bdb

#

# * InnoDB

#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.

#skip-innodb

#

# * Security Features

#

# Read the manual, too, if you want chroot!

# chroot = /var/lib/mysql/

#

# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

#

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem

 

 

 

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

 

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

 

[isamchk]

key_buffer = 16M

 

#

# * NDB Cluster

#

# See /usr/share/doc/mysql-server-*/README.Debian for more information.

#

# The following configuration is read by the NDB Data Nodes (ndbd processes)

# not from the NDB Management Nodes (ndb_mgmd processes).

#

# [MYSQL_CLUSTER]

# ndb-connectstring=127.0.0.1

 

 

#

# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/

[Gość squeezer]

O tym, dlaczego nie bardzo da się w prosty sposób pomóc w tego rodzaju sytuacjach, próbowałem wyjaśnić w jednym z postów na moim blogu:

 

http://blog.ksiazek.info/2010/07/19/optymalna-konfiguracja-mysql/

 

Z podesłanych przez Ciebie informacji wynika w zasadzie tyle, że Twój serwer MySQL działa praktycznie na domyślnych debianowskich ustawieniach. Co gorsze, stosujesz 32 bitowy system operacyjny. MySQL 5.0.51 to także prehistoria i przydałoby się pomyśleć o czymś nowszym. Dodatkowo w momencie odpalenia Tunera serwer działał krócej niż 24 godziny ( czy wiesz ile to było konkretnie? kilkanaście godzin, czy raczej kilkadziesiąt minut?), co może powodować że statystyki nie są kompletne. Serwer "rozgrzany" działa kompletnie inaczej, niż taki, który właśnie wystartował. Dane o ilości tabel tymczasowych, full joinów itp. mogą być kompletnie różne od tego, jak to wygląda w trakcie normalnej pracy serwera.

 

W skrócie, powinieneś zwrócić uwagę na to co jest zaznaczone wykrzyknikami

 

1. zainstalować 64 bitowy OS.

2. zainstalować jakiś względnie nowoczesny MySQL

 

Jeśli powyższe jest nie realne, to pomiń i przejdź do poniższych punktów:

 

3. skonfiguruj poprawnie wielkość key_buffer - ile pamięci możesz na niego przeznaczyć zależy od tego, jak wygląda wykorzystanie pamięci na maszynie. Nie podałeś takich danych, nie będę więc niczego sugerował - te 574M podane przez skrypt nie musi być dobrym rozwiązaniem. Pamiętaj o tym, żeby zostawić pamięć na cache systemu plików - w przypadku MyISAM MySQL buforuje tylko indeksy. Dane - system operacyjny.

4. ustawić long_query_time na 1 i po kilkunastu godzinach przeglądnąć slowlogi - wyłapać co się powtarza, poprawić zapytania. Będzie tego trochę.

5. jeśli masz wolną pamięć, zwiększ wielkość query_cache_size tak do 128M - 256M (zakładając, że wiesz, że do bazy idą zapytania, które mogą realnie z cache skorzystać)

6. table_cache chwilowo zostawiłbym bez zmian, grzebanie tu może powodować skutek odwrotny do zamierzonego - to jedna z tych zmiennych, których podbicie może spowodować zwolnienie działania serwera. Zmieniać ją można, ale świadomie i testując wydajność po zmianach

[Elber 0]

Tak, serwer działał kilka minut ze względu na to że mysql się zapchał i musiałem go zrestartować. Czy przejście na 64-bitowy OS jest konieczna ?

[Gość squeezer]

Tak, serwer działał kilka minut ze względu na to że mysql się zapchał i musiałem go zrestartować. Czy przejście na 64-bitowy OS jest konieczna ?

 

Konieczne, nie - jakoś przez te parenaście lat to działało. Wskazane - tak. Jako że takie przejście jest bolesne, na razie skoncentruj się na poprawie konfiguracji i poprawie zapytań. Jeśli to nie wystarczy, ewentualnie gdy okaże się, że będziesz potrzebował większej ilości pamięci, trzeba będzie pomyśleć o przejściu na 64 bity.

 

Jeśli serwer działał tylko kilka minut, to tym gorzej się dzieje, że pojawiło Ci się takie coś:

 

Joins performed without indexes: 6

 

Wykonywanie JOINów bez indeksów może zabić każdy serwer.

 

Jeśli serwer przeżyje do popołudnia, podeślij jeszcze raz wynik z Tunera albo z mysqlreport. Szczególnie chodzi o kwestię tabel tymczasowych - ile ich jest, czy są tworzone na dysku itp. Przeglądnij też slowlogi i popatrz jakie zapytania się tam najczęściej pojawiają. Podeślij wynik EXPLAIN tych zapytań.

Jak sam serwer jest obciążony? CPU, dyski, pamięć - są wolne zasoby, czy to też się kończy?

[Elber 0]

Jeśli o obciążenie chodzi to spoko, bo nie przekracza raczej 0,5 procek i ram też raczej są wonle. Serwer przeżyje, bo przed restartem mysql wczorajszym, prędzej go nie resetowałem z miesiąc chyba. Zapchał się dlatego że jeden z adminów wysyłał spam na 200 tyś userów ... po południu dam wyniki tych skryptów.

Dane z przed chwili - Load average: 0.27 0.41 0.36

[Elber 0]

Jakby co to nic jeszcze nie zmieniałem:

 

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-24+lenny4

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

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

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

[--] Data in MyISAM tables: 1009M (Tables: 918)

[--] Data in InnoDB tables: 25M (Tables: 30)

[--] Data in MEMORY tables: 1011K (Tables: 12)

[!!] Total fragmented tables: 131

 

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

[--] Up for: 21h 48m 39s (4M q [52.906 qps], 280K conn, TX: 3B, RX: 1B)

[--] Reads / Writes: 85% / 15%

[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)

[OK] Maximum possible memory usage: 320.5M (8% of installed RAM)

[OK] Slow queries: 0% (2/4M)

[OK] Highest usage of available connections: 25% (25/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/574.3M

[OK] Key buffer hit rate: 97.6% (40M cached / 993K reads)

[OK] Query cache efficiency: 71.5% (2M cached / 3M selects)

[!!] Query cache prunes per day: 279059

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

[OK] Temporary tables created on disk: 1% (2K on disk / 194K total)

[OK] Thread cache hit rate: 99% (215 created / 280K connections)

[!!] Table cache hit rate: 0% (64 open / 113K opened)

[OK] Open file limit used: 11% (121/1K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[!!] InnoDB data size / buffer pool: 25.3M/8.0M

 

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

General recommendations:

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

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 16M)

table_cache (> 64)

innodb_buffer_pool_size (>= 25M)

 

[Elber 0]

Ok następny wynik z mysqltuner'a teraz już po 24 h. Co na podstawie tego zrobić ?

 

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-24+lenny4

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

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

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

[--] Data in MyISAM tables: 1009M (Tables: 918)

[--] Data in InnoDB tables: 25M (Tables: 30)

[--] Data in MEMORY tables: 1011K (Tables: 12)

[!!] Total fragmented tables: 136

 

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

[--] Up for: 1d 2h 2m 16s (5M q [59.634 qps], 377K conn, TX: 3B, RX: 1B)

[--] Reads / Writes: 85% / 15%

[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)

[OK] Maximum possible memory usage: 320.5M (8% of installed RAM)

[OK] Slow queries: 0% (2/5M)

[OK] Highest usage of available connections: 25% (25/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/574.4M

[OK] Key buffer hit rate: 97.3% (51M cached / 1M reads)

[OK] Query cache efficiency: 70.8% (3M cached / 4M selects)

[!!] Query cache prunes per day: 299400

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

[!!] Joins performed without indexes: 485

[OK] Temporary tables created on disk: 1% (2K on disk / 251K total)

[OK] Thread cache hit rate: 99% (310 created / 377K connections)

[!!] Table cache hit rate: 0% (64 open / 162K opened)

[OK] Open file limit used: 11% (114/1K)

[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

[!!] InnoDB data size / buffer pool: 25.3M/8.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

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 16M)

join_buffer_size (> 128.0K, or always use indexes with joins)

table_cache (> 64)

innodb_buffer_pool_size (>= 25M)

[Gość squeezer]

Włącz logowanie wolnych zapytań, long_query_time ustaw na 1. Zmodyfikuj wielkość key_buffer, jeśli jakoś konkretniej wykorzystujesz tabele InnoDB, to podbij wielkość innodb_bufer_pool do 30M. Poczekaj kilka - kilkanaście godzin i zobacz jakie zapytania pozbierały Ci się w slowlogach. Poważnie rozważ zastosowanie nowszej wersji MySQL. W szczególności, Percona Server udostępnia dodatkowe informacje w slowlogu - znacznie ułatwia wyszukiwanie przyczyny wolnego działania bazy. Najnowsze wydanie z serii 5.0 znajdziesz tu:

 

http://www.percona.c...ver-5.0/LATEST/

 

Jeśli nie chcesz zmieniać wersji na serwerze produkcyjnym, to rozważ instalację takiego zmodyfikowanego MySQL gdzieś na boku. Choćby nawet na lokalnym komputerze. Odtworzysz sobie tam z backupu strukturę bazy i dane, będziesz mógł sprawdzać sobie poszczególne, problematyczne zapytania. Będziesz wiedział więcej o zapytaniach, bez konieczności kombinowania z serwerem produkcyjnym.

[Elber 0]

Ok, odpaliłem mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

 

pozmieniałem to co pisałeś i key_buffer ustawiłem na 32M zobaczymy co jutro popołudniu mysqltuner pokaże

[Elber 0]

Ok, najświeższy wynik z mysqltuner'a, Co jeszcze mogę zmienić i na jakie wartości ?

 

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-24+lenny4

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

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

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

[--] Data in MyISAM tables: 1004M (Tables: 918)

[--] Data in InnoDB tables: 22M (Tables: 30)

[--] Data in MEMORY tables: 1011K (Tables: 12)

[!!] Total fragmented tables: 49

 

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

[--] Up for: 1d 13h 34m 1s (7M q [56.611 qps], 517K conn, TX: 844M, RX: 2B)

[--] Reads / Writes: 85% / 15%

[--] Total buffers: 96.0M global + 2.6M per thread (100 max threads)

[OK] Maximum possible memory usage: 358.5M (9% of installed RAM)

[OK] Slow queries: 0% (219/7M)

[OK] Highest usage of available connections: 34% (34/100)

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

[OK] Key buffer hit rate: 95.5% (74M cached / 3M reads)

[OK] Query cache efficiency: 71.0% (4M cached / 5M selects)

[!!] Query cache prunes per day: 274415

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

[!!] Joins performed without indexes: 627

[OK] Temporary tables created on disk: 1% (4K on disk / 348K total)

[OK] Thread cache hit rate: 99% (675 created / 517K connections)

[!!] Table cache hit rate: 0% (64 open / 231K opened)

[OK] Open file limit used: 11% (116/1K)

[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

[OK] InnoDB data size / buffer pool: 22.3M/30.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

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 16M)

join_buffer_size (> 128.0K, or always use indexes with joins)

table_cache (> 64)

[Gość squeezer]

Możesz podbijać key_buffer_size - zależy ile masz pamięci. Możesz spróbować podbić cache tabel, ale musiałbyś sobie przetestować czy u Ciebie ta zmiana faktycznie zwiększa wydajność. Może, ale nie musi. Jeśli włączyłeś logowanie slowlogów, to trzeba zabrać się za poprawianie zapytań.

 

Tak w ogóle, to na czym polega problem z Twoją bazą? Serwer pada? Zapytania się wolno wykonują? Jakie konkretnie? To, że ustawisz jakieś w miarę sensowne cyferki w konfiguracji, to dobrze. Tyle że to zazwyczaj problemu nie rozwiązuje.