Optymalizacja serwera mysql - duży system

[bobek358 0]

Witam

 

Zajmuje się utrzymaniem aplikacji na której pracuje pewna siec sprzedaży w całej Polsce.

Po 1.5 roku działania, mamy bazę około 10GB i co prawda od spraw technicznych mamy osobę, jednak chciałem, aby ktoś spojrzał na tą konfigurację serwera mysql - musimy przygotować się na duży wzrost obciążenia systemu w najbliższym czasie. Aplikacja i mysql stoi na jednej maszynie - są to dwa osobne serwery wirtualne.

 

Serwer Mysql ma przydzielone 11 gb ram oraz 4 procesory (chociaż z tego co widzę, to raczej po prostu 4 rdzenie procesora - poniżej wpis z proc/cpuinfo. Jeśli chodzi o ramo to jest ok bo zajęte jest około 700 mb - ponad 10 gb jest jako cache dla mysql. Zastanawia mnie jednak wydajność samych procesorów. Co o nich sądzicie? Obciążenie serwera mysql jest teraz na poziomie 0.2 - 1 jak ma stres to dojdzie do 2-3. Boję się, że jeśli sieć rozrośnie się o 100% to będzie mega problem.

 

CO myślicie o tej konfiguracji?

 

 

Wynik free -m

 

total used free shared buffers cached

Mem: 11997 11848 148 0 217 11004

-/+ buffers/cache: 627 11370

Swap: 1023 0 1023

 

 

Wynik proc.cpuinfo

 

processor : 0

vendor_id : GenuineIntel

cpu family : 6

model : 26

model name : Intel® Xeon® CPU E5520 @ 2.27GHz

stepping : 5

microcode : 0x11

cpu MHz : 2261.084

cache size : 8192 KB

physical id : 0

siblings : 4

core id : 0

cpu cores : 1

apicid : 0

initial apicid : 23

fpu : yes

fpu_exception : yes

cpuid level : 11

wp : yes

flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good nopl pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm ida dtherm

bogomips : 4522.16

clflush size : 64

cache_alignment : 64

address sizes : 40 bits physical, 48 bits virtual

power management:

processor : 1

vendor_id : GenuineIntel

cpu family : 6

model : 26

model name : Intel® Xeon® CPU E5520 @ 2.27GHz

stepping : 5

microcode : 0x11

cpu MHz : 2261.084

cache size : 8192 KB

physical id : 0

siblings : 4

core id : 0

cpu cores : 1

apicid : 0

initial apicid : 23

fpu : yes

fpu_exception : yes

cpuid level : 11

wp : yes

flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good nopl pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm ida dtherm

bogomips : 4522.16

clflush size : 64

cache_alignment : 64

cache_alignment : 64

address sizes : 40 bits physical, 48 bits virtual

power management:

processor : 2

vendor_id : GenuineIntel

cpu family : 6

model : 26

model name : Intel® Xeon® CPU E5520 @ 2.27GHz

stepping : 5

microcode : 0x11

cpu MHz : 2261.084

cache size : 8192 KB

physical id : 0

siblings : 4

core id : 0

cpu cores : 1

apicid : 0

initial apicid : 23

fpu : yes

fpu_exception : yes

cpuid level : 11

wp : yes

flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good nopl pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm ida dtherm

bogomips : 4522.16

clflush size : 64

aaddress sizes : 40 bits physical, 48 bits virtual

power management:

processor : 3

vendor_id : GenuineIntel

cpu family : 6

model : 26

model name : Intel® Xeon® CPU E5520 @ 2.27GHz

stepping : 5

microcode : 0x11

cpu MHz : 2261.084

cache size : 8192 KB

physical id : 0

siblings : 4

core id : 0

cpu cores : 1

apicid : 0

initial apicid : 23

fpu : yes

fpu_exception : yes

cpuid level : 11

wp : yes

flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good nopl pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm ida dtherm

bogomips : 4522.16

clflush size : 64

cache_alignment : 64

address sizes : 40 bits physical, 48 bits virtual

power management:

 

 

A to plik konfiguracji mysql.

 

 

[client]

port = 3306

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

[mysqld_safe]

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

nice = 0

[mysqld]

user = mysql

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

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

port = 3306

basedir = /usr

#datadir = /var/lib/mysql

datadir = /media/data/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

#max_connection_errors = 5000

tmp_table_size = 32M

max_heap_table_size = 32M

#

# 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

bind-address = XXX.XXX.XXX.XXX

#

# * Fine Tuning

#

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

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.

# As of 5.1 you can enable the log at runtime!

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

#general_log = 1

#

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

#

# 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

#

# * 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!

#

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

#

# * 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/

 

 

[gutek 23]

u mnie przy jednej aplikacji baza ma 14 gb, podczas zwiększonego obciążeniu proces mysql zużywa prawie wszystkie dostępne rdzenie a jest ich 32

 

według mnie warto zmienić konfigurację mysqla, u mnie apka mimo rozmiaru działa bez problemu..

Edytowano Kwiecień 4, 2015 przez gutek (zobacz historię edycji)

[blfr 225]

Serwer Mysql ma przydzielone 11 gb ram oraz 4 procesory (chociaż z tego co widzę, to raczej po prostu 4 rdzenie procesora

To są cztery procesory logiczne, czyli cztery wątki sprzętowe, czyli pół tego procesora, który masz tam zainstalowany.

 

Obciążenie serwera mysql jest teraz na poziomie 0.2 - 1 jak ma stres to dojdzie do 2-3. Boję się, że jeśli sieć rozrośnie się o 100% to będzie mega problem.

Load 2-3 na takim serwerze w szczycie obciążenia jest w normie. Istotniejszą miarą są czasy odpowiedzi i ładowania strony.

 

W przyszłości będziesz musiał oczywiście znaleźć mocniejszy serwer. Ale skoro masz kogoś, kto się tym zajmuje, to pewnie też i przy tym doradzi.

Edytowano Kwiecień 4, 2015 przez blfr (zobacz historię edycji)

[Bartosz Z 236]

Może NoSQL? ;>

Albo dodatkowe serwery slave przeznaczone tylko do odczytu danych?

[bobek358 0]

Dzięki blfr za wyjaśnienie tematu

Bartosz Z z tego co się orientuje to taka zmiana na NoSQL to w tym momencie temat nie do przejścia -> sama baza ma już 600 tabel a sam skrypt ma za dużo zależności.

 

A co sądzicie o tej konfiguracji aktualnej.

Czy coś tam można zmienić w my.cnf bez obaw o pogorszenie pracy tego serwera?

 

PS a istnieje jakaś możliwość aby z poziomu serwera wirtualnego sprawdzić jakie zasoby ma maszyna fizyczna? Niestety nie mam dostępu do wirtualizacji aby to spr.

 

Dodatkowo wynik z MYSQL TUNER

 

 

[OK] Currently running supported MySQL version 5.5.37-0+wheezy1-log

[OK] Operating on 64-bit architecture

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

[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM

[--] Data in MyISAM tables: 5G (Tables: 302)

[--] Data in InnoDB tables: 1G (Tables: 225)

[!!] Total fragmented tables: 233

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

ERROR 1142 (42000) at line 1: SELECT command denied to user 'XXXXXXX'@'localhost' for table 'user'

[OK] All database users have passwords assigned

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

[--] Up for: 37d 11h 35m 36s (2B q [679.883 qps], 7M conn, TX: 961B, RX: 421B)

[--] Reads / Writes: 70% / 30%

[--] Total buffers: 208.0M global + 2.7M per thread (151 max threads)

[OK] Maximum possible memory usage: 613.8M (5% of installed RAM)

[OK] Slow queries: 0% (9K/2B)

[OK] Highest usage of available connections: 49% (75/151)

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

[OK] Key buffer hit rate: 98.0% (28B cached / 583M reads)

[OK] Query cache efficiency: 75.3% (1B cached / 1B selects)

[!!] Query cache prunes per day: 9869580

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

[!!] Joins performed without indexes: 367077

[OK] Temporary tables created on disk: 0% (83K on disk / 19M total)

[OK] Thread cache hit rate: 99% (4K created / 7M connections)

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

[OK] Open file limit used: 44% (456/1K)

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

[!!] InnoDB buffer pool / data size: 128.0M/1.7G

[OK] InnoDB log waits: 0

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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Adjust your join queries to always utilize indexes

Increase table_open_cache gradually to avoid file descriptor limits

Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C

Variables to adjust:

query_cache_size (> 16M)

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

table_open_cache (> 400)

innodb_buffer_pool_size (>= 1G)

 

Edytowano Kwiecień 4, 2015 przez bobek358 (zobacz historię edycji)

[oczkers 0]

[--] Total buffers: 208.0M global + 2.7M per thread (151 max threads)

[OK] Maximum possible memory usage: 613.8M (5% of installed RAM)

To chyba troszkę mniej niż 11 gb, możesz spokojnie podbić wartości cache

 

 

 

 

[!!] Joins performed without indexes: 367077

Warto nałożyć indeksy na wszystkie joiny

 

Edytowano Czerwiec 12, 2015 przez oczkers (zobacz historię edycji)