Optymalizacja bazy danych MySQL

[hazael]

Witam,

Proszę o pomoc - jakieś uwagi, wskazówki odnośnie konfiguracji mojej bazy danych.

Potrzebuję ją trochę podkręcić ale tak, aby serwer mi nie padł

Posiadam serwer wirtualny:

System operacyjny: debian-6-x86 Prędkość CPU: 2 x 2.0 GHz Gwarantowana pamięć RAM: 2048 MB Dostępna pamięć RAM: 4096 MB

Oto moje obecne ustawienia Mysql:

[client]

port =3306

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

###################################################

[mysqld_safe]

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

nice = 0

###################################################

[mysqld]

user =mysql

local-infile =0

datadir =/var/lib/mysql

port =3306

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

basedir =/usr

tmpdir =/tmp

language =/usr/share/mysql/polish

skip-external-locking

skip-innodb

skip-networking

safe-show-database

query_cache_limit =1M

query_cache_size =64M ## 32MB/1GB RAM

query_cache_type =1

max_user_connections =80

max_connections =130

interactive_timeout =10

wait_timeout =10

connect_timeout =20

thread_cache_size =128

key_buffer =128M ## 128MB/1GB RAM

join_buffer =1M

max_connect_errors =10

max_allowed_packet =16M

record_buffer =2M

sort_buffer_size =2M ## 1MB/1GB RAM

read_buffer_size =2M ## 1MB/1GB RAM

read_rnd_buffer_size =2M ## 1MB/1GB RAM

thread_concurrency =2 ## CPUs x 2

myisam_sort_buffer_size =64M

server-id =1

collation-server =latin1_general_ci

myisam-recover =BACKUP

tmp_table_size =150M

max_heap_table_size =250M

table_cache =1024

myisam_max_sort_file_size =6GB

long_query_time =1

###################################################

[mysql.server]

user =mysql

##################################################

[safe_mysqld]

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

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

open_files_limit =8192

###################################################

[mysqldump]

quick

quote-names

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

###################################################

Oto wyniki testu bazy danych zrobione po kilku dniach w Mysqltuner:

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.49-3

[!!] 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: 58M (Tables: 1606)

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

[!!] Total fragmented tables: 27

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

[--] Up for: 3d 12h 35m 14s (13M q [44.598 qps], 56K conn, TX: 1B, RX: 1B)

[--] Reads / Writes: 86% / 14%

[--] Total buffers: 342.0M global + 7.2M per thread (130 max threads)

[OK] Maximum possible memory usage: 1.2G (31% of installed RAM)

[OK] Slow queries: 0% (963/13M)

[OK] Highest usage of available connections: 4% (6/130)

[OK] Key buffer size / total MyISAM indexes: 128.0M/19.9M

[OK] Key buffer hit rate: 99.7% (52M cached / 169K reads)

[OK] Query cache efficiency: 93.3% (12M cached / 13M selects)

[!!] Query cache prunes per day: 39230

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

[!!] Joins performed without indexes: 17505

[!!] Temporary tables created on disk: 37% (120K on disk / 323K total)

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

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

[OK] Open file limit used: 24% (2K/8K)

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

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

Variables to adjust:

query_cache_size (> 64M)

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

tmp_table_size (> 150M)

max_heap_table_size (> 250M)

table_cache (> 1024)

Nie wiem dokładnie co mam podkręcić a co zmniejszyć, aby zminimalizować błędy.

Nie chcę przesadzać z ilością pamięci - max 1,5GB chcę przeznaczyć na bazę danych.

Czekam na opinię.

[sirgomo]

Optymalizacja tabel,

z tego co wiem i co tu pisze te 2 wartosci powinny byc takie same "tmp_table_size 250M /max_heap_table_size 250"

Dodatkowo zwiekszyl bym table_cache (1024 przy 1600 tabel to cos nie tak)

Niewiem jak wygladaja zapytania ale tutaj masz wycinek z Manula

"For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute."

Co powinienes rozumiec ze jak masz max_connections ustawione na 130 to table cache powinno byc 130 * N gdzie n to maksymalna liczna tabel uzyta w zapytaniu(z join)

U mnie mam 30000 i wciaz pokazuje ze za malo

Zmniejszyl bym na pewno max_user_connections jak i max_connections (80 to za duzo skoro w ciagu 3dni max miales 6)

query_cache_size na 128 (jesli bedzie malo to i na wiecej)

Trzeba by przejrzec zapytania i poprawic by uzywaly indeksow, ewentualnie jakis cache dla zapytan

Sirgomo

[hazael]

Dzięki za cenną uwagę. Już robię modyfikację wg Twoich wytycznych

PS. Jak zmienić collation-server=latin1_general_ci na UTF-8-ci-general? Jak wpisuję takie ustawienie, to po restarcie baza się nie odpala. Czy muszę tu coś jeszcze wymusić?

[Mion]

https://www.google.pl/search?hl=pl&q=mysql+my.cnf+performance

https://www.google.pl/search?hl=pl&q=mysql+my.cnf+performance+tuning

https://www.google.pl/search?hl=pl&q=mysql+my.cnf+performance+optimization

itd ...