Skocz do zawartości


 

Zdjęcie

Optymalizacja serwera mysql - duży system

Optymalizacja serwera mysql - duży system

  • Proszę się zalogować aby odpowiedzieć
5 odpowiedzi na ten temat

Optymalizacja serwera mysql - duży system

#1 bobek358

bobek358

    Nowy użytkownik

  • Użytkownicy
  • 6 postów

Napisany 04 kwiecień 2015 - 11:25

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/
 
 
 

 

 


  • 0

#2 gutek

gutek

    Stały użytkownik

  • Użytkownicy
  • PipPipPipPipPip
  • 209 postów
  • Skąd:Wrocław

Napisany 04 kwiecień 2015 - 12:36

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

Edytowany przez gutek, 04 kwiecień 2015 - 12:37.

  • 0

#3 blfr

blfr

    Weteran WHT

  • WHT Pro
  • PipPipPipPipPipPipPipPip
  • 1208 postów

Napisany 04 kwiecień 2015 - 12:45

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.

Edytowany przez blfr, 04 kwiecień 2015 - 12:46.

  • 0

#4 Bartosz Z

Bartosz Z

    Weteran WHT

  • WHT Pro
  • PipPipPipPipPipPipPipPip
  • 863 postów
  • Skąd:Lubelskie
  • Imię:Bartosz

Napisany 04 kwiecień 2015 - 12:46

Może NoSQL? ;>

Albo dodatkowe serwery slave przeznaczone tylko do odczytu danych?


  • 0

#5 bobek358

bobek358

    Nowy użytkownik

  • Użytkownicy
  • 6 postów

Napisany 04 kwiecień 2015 - 19:43

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)
 

 


Edytowany przez bobek358, 04 kwiecień 2015 - 20:54.

  • 0

#6 oczkers

oczkers

    Nowy użytkownik

  • Użytkownicy
  • 19 postów

Napisany 12 czerwiec 2015 - 10:41

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

 


Edytowany przez oczkers, 12 czerwiec 2015 - 10:42.

  • 0





0 użytkowników czyta ten temat

0 użytkowników, 0 gości, 0 anonimowych użytkowników