Skocz do zawartości
bobek358

Optymalizacja serwera mysql - duży system

Polecane posty

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/

 

 

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

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 przez gutek (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

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 przez blfr (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

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 przez bobek358 (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
[--] 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 przez oczkers (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Bądź aktywny! Zaloguj się lub utwórz konto

Tylko zarejestrowani użytkownicy mogą komentować zawartość tej strony

Utwórz konto

Zarejestruj nowe konto, to proste!

Zarejestruj nowe konto

Zaloguj się

Posiadasz własne konto? Użyj go!

Zaloguj się


×