Skocz do zawartości
mlody_hoster

MySQL nie wyrabia - jak rozbudować serwer?

Polecane posty

Witam,

Mamy serwer dedykowany o parametrach:
Procesor: Intel Core i7 4790k 4,00 GHz
Dysk: 4 x 120GB SSD (RAID 1+0)
Pamięć RAM: 32GB DDR 3

Stoi na nim kilka stron na WP i aplikacji webowych - większość z nich nie ma zbyt wielkich wymagań. Niestety są tam 4 apki (w sumie to jedna tylko w czterech instancjach) które działają 24h/dobę i przez większość czasu odczytują/zapisują dane do bazy MySQL.

Od pewnego czasu ilość zapytań zaczęła rosnąć (wraz ze wzrostem danych które muszą przetworzyć te 4 aplikacje) i cały serwer zaczął zamulać - strony ładują się wolniej, pozostałe aplikacje też działają ślamazarnie.

Sprawdziliśmy za pomocą mytop ilość zapytań i obciążenie bazy danych, 99% zapytań generują te 4 apki - co jest zgodne z naszymi testami (po wyłączeniu tych aplikacji serwer działa bez żadnych problemów). Aktualnie myślimy o tym co możemy zrobić by wszystko działało prawidłowo, póki co otrzymaliśmy propozycję przerzucenia serwera MySQL na inną maszynę i połączenia obu za pomocą sieci lokalnej o przepustowości 1Gbit.

Proponowane parametry serwera dla baz MySQL:
Procesor: Intel Core i7 4770 3,40Ghz
Dysk: 2x 120GB SSD (RAID 1+0)
Pamięć RAM: 16GB

Z racji tego że nie mam zbyt dużego doświadczenia w sprawach serwerowych chciałbym się dowiedzieć czy mamy jeszcze jakieś inne opcje (może jakiś klaster?) i jak to najlepiej rozwiązać od strony technicznej.

Priorytetem jest szybkość i stabilność działania, koszty mają drugorzędne znaczenie.


Jeszcze dopowiem - nie mamy możliwości edycji i poprawy kodu tych aplikacji - kod jest zamknięty (IonCube).

Edytowano przez mlody_hoster (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
Gość Kamikadze

Może wystarczy optymalizacja serwera mysql?

 

mysqltuner.pl na początek.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Musisz zobaczyć jaki typ zapytań przeważa, może query cache na początek wystarczy.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Select/update/delete

Baza aplikacji ma (w dużym uproszczeniu) taką strukturę:
- w jednej tabeli są dane które muszą zostać sprawdzone - są pobierane selectem
- po sprawdzeniu danych są one zapisywane w innej tabeli - update
- po zapisaniu danych są one kasowane z tabeli do sprawdzenia - delete

Jak już pisałem modyfikacja aplikacji nie wchodzi w grę.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
Gość Kamikadze

Powinno wystarczyć zoptymalizować my.cnf i serwer będzie już sobie radził.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Tak jak napisał Kamikadze zainteresuj się poprawną konfiguracją bazy bo rozmiar jest "śmiesznie" mały dla takiego sprzętu.. Ja mam na e3-1240v2, 32 gb ram, 2 x 120 gb ssd tabele o rozmiarach 10-20 gb i działa rewelacyjnie..

 

Może problemem jest rodzaj tabeli np. myisam zamiast innodb ?

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Ok, dzięki za odpowiedzi. Aktualnie wszystko działa prawidłowo - 4 aplikacje zakończyły już swoje działanie. Wrzucam info z mytop i mysqltuner.pl, jutro rano wrzucę też raporty z obciążonego serwera.

mytop

MySQL on localhost (5.6.17)                             up 7+04:19:57 [17:07:46]
 Queries: 9.0     qps:    0 Slow:     8.0         Se/In/Up/De(%):    31435122/00/00/00
              qps now:    0 Slow qps: 0.4  Threads:    1 (   1/  67) 300/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.1   Now in/out:   8.3/ 1.9k

        Id      User         Host/IP         DB      Time    Cmd Query or State
        --      ----         -------         --      ----    --- --------------
    689849  da_admin       localhost                    0  Query show full processlist

mysqltuner.pl

 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.17
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 1G (Tables: 2809)
[--] Data in InnoDB tables: 2G (Tables: 369)
[--] Data in MEMORY tables: 0B (Tables: 26)
[!!] Total fragmented tables: 79

-------- Security Recommendations  -------------------------------------------
[!!] User '@XXX' is an anonymous account.
[!!] User '@localhost' is an anonymous account.
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 4h 22m 41s (19M q [31.424 qps], 690K conn, TX: 13B, RX: 3B)
[--] Reads / Writes: 67% / 33%
[--] Binary logging is disabled
[--] Total buffers: 1.1G global + 4.8M per thread (500 max threads)
[OK] Maximum reached memory usage: 1.4G (4.48% of installed RAM)
[OK] Maximum possible memory usage: 3.4G (10.87% of installed RAM)
[!!] Slow queries: 131% (25M/19M)
[OK] Highest usage of available connections: 13% (68/500)
[OK] Aborted connections: 0.23%  (1579/690118)
[OK] Query cache efficiency: 21.8% (2M cached / 12M selects)
[!!] Query cache prunes per day: 35652
[OK] Sorts requiring temporary tables: 0% (33 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 3838
[OK] Temporary tables created on disk: 22% (33K on disk / 149K total)
[OK] Thread cache hit rate: 99% (68 created / 690K connections)
[!!] Table cache hit rate: 2% (2K open / 77K opened)
[OK] Open file limit used: 41% (3K/8K)
[OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/731.7M
[OK] Read Key buffer hit rate: 99.9% (2B cached / 1M reads)
[!!] Write Key buffer hit rate: 55.6% (1M cached / 799K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 512.0M/2.1G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 96.87% (31743 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.96% (497011352686 hits/ 497205774031 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 315951 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Increasing the query_cache size over 128M may reduce 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
    Beware that open_files_limit (8192) variable 
    should be greater than table_open_cache ( 2000)
Variables to adjust:
    query_cache_size (> 128M) [see warning above]
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 2G) if possible.
    innodb_buffer_pool_instances (=1)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
Gość Spoofy

Jakie oprogramowanie bazy danych?

Oracle MySQL?
MariaDB?

PerconaDB?

 

Wersja?

5.5?

5.6?

5.7 (mało prawdopodobne)?

 

IMHO PerconaDB 5.6 + dobra optymalizacja. Jak nie wystarczy - ulokować na jakimś ssd.


Edit:

Akurat posta wrzuciłeś jak pisałem swojego :)

Widzę 5.6, ale nie widzę dokładnie jaki to mysql.

 

Tak czy siak - trzymam się swojego + włącz cache'owanie query tak jak moi przedmówcy powiedzieli bo na szybko zerknąłem na output mysqltuner'a.

Edytowano przez Spoofy (zobacz historię edycji)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
Gość Kamikadze

Spokojnie możesz zwiększyć parametry konfiguracji. MySQL u Ciebie jest bardzo mały a miałby chyba apetyt na więcej ramu :)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Nic nie mówię, ale te bazy ważą w sumie niecałe 3 GB, a ty masz 32 GB pamięci, czyli prawie 11x tyle. W takim przypadku trzeba się zastanowić co jest nie tak z SQLkami i serwerem MySQL, a nie, że trzeba serwer zmienić na lepszy. Masz tak dużo pamięci, że wszystkie się w całości mieszczą w ramie, razem z ew. wynikami tych SQLek.

 

Od siebie polecam MariaDB 10.1.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Wyniki z rana

mytop

MySQL on localhost (5.6.17)                             up 7+20:41:50 [09:29:39]
 Queries: 21.0    qps:    0 Slow:    20.0         Se/In/Up/De(%):    14294757/00/00/00
              qps now:    0 Slow qps: 0.4  Threads:    8 (   4/  60) 950/00/00/00
 Key Efficiency: 99.9%  Bps in/out:   0.0/  0.2   Now in/out:   8.4/ 2.0k

        Id      User         Host/IP         DB      Time    Cmd Query or State
        --      ----         -------         --      ----    --- --------------
    749886  da_admin       localhost                    0  Query show full processlist
    749911 fz4537ja_       localhost fz4537ja_n         1  Query UPDATE `wt_u_proxy` SET `cnt` = `cnt`+'1' WHERE `id` = '746233' LIMIT 1
    749933 km8te535_       localhost km8te535_m         1  Query DELETE FROM `wt_s_competition` WHERE `frazaId` = '3217' AND `check_date` = CURRENT_DATE()
    749934 utslgbp4_       localhost utslgbp4_S         1  Query DELETE FROM `wt_s_competition` WHERE `frazaId` = '4743' AND `check_date` = CURRENT_DATE()
    749925 fz4537ja_       localhost fz4537ja_n         2  Sleep
    749937 fz4537ja_       localhost fz4537ja_n         3  Sleep
    749938 fz4537ja_       localhost fz4537ja_n         3  Sleep
    749939 fz4537ja_       localhost fz4537ja_n         3  Sleep

mysqltuner.pl

 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.17
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 1G (Tables: 2809)
[--] Data in InnoDB tables: 2G (Tables: 369)
[--] Data in MEMORY tables: 0B (Tables: 26)
[!!] Total fragmented tables: 78

-------- Security Recommendations  -------------------------------------------
[!!] User '@XXX' is an anonymous account.
[!!] User '@localhost' is an anonymous account.
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 20h 39m 54s (21M q [31.192 qps], 749K conn, TX: 14B, RX: 3B)
[--] Reads / Writes: 67% / 33%
[--] Binary logging is disabled
[--] Total buffers: 1.1G global + 4.8M per thread (500 max threads)
[OK] Maximum reached memory usage: 1.4G (4.48% of installed RAM)
[OK] Maximum possible memory usage: 3.4G (10.87% of installed RAM)
[!!] Slow queries: 132% (27M/21M)
[OK] Highest usage of available connections: 13% (68/500)
[OK] Aborted connections: 0.24%  (1789/749732)
[OK] Query cache efficiency: 21.3% (3M cached / 14M selects)
[!!] Query cache prunes per day: 35365
[OK] Sorts requiring temporary tables: 0% (41 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 3951
[OK] Temporary tables created on disk: 22% (34K on disk / 152K total)
[OK] Thread cache hit rate: 99% (68 created / 749K connections)
[!!] Table cache hit rate: 1% (2K open / 100K opened)
[OK] Open file limit used: 39% (3K/8K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 55.3% (222M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/732.5M
[OK] Read Key buffer hit rate: 99.9% (2B cached / 1M reads)
[!!] Write Key buffer hit rate: 55.3% (1M cached / 881K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 512.0M/2.1G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 96.53% (31631 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.96% (531962966267 hits/ 532157953267 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 344739 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Increasing the query_cache size over 128M may reduce 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
    Beware that open_files_limit (8192) variable 
    should be greater than table_open_cache ( 2000)
Variables to adjust:
    query_cache_size (> 128M) [see warning above]
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 2G) if possible.
    innodb_buffer_pool_instances (=1)

Pokombinuję trochę z my.conf i zobaczę co dalej, jeśli ktoś ma jeszcze jakieś sugestie to chętnie poczytam :)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Zobacz, czy posiadasz nałożone w bazie danych indeksy na kolumny, po których robione jest WHERE

(czyli np. wt_s_competition.frazaId i wt_s_competition.check_date)

Udostępnij ten post


Link to postu
Udostępnij na innych stronach
Gość mariaczi

Z "Performance Metrics" warto zbadać
[!!] Slow queries: 132% (27M/21M)
[!!] Joins performed without indexes: 3951

o cache przedmówcy już wspominali

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Indeksy i wyższe limity w my.cnf na początek. Potem dostęp do kodu aplikacji, bo skoro to jest coś specjalistycznego to aż nie wierzę w zamkniętość kodu.

 

Jak Archi powiedział - RAMu jest tyle, że można dane i bazę zmieścić tyle razy w pamięci, że zmiana serwera nie ma jeszcze sensu.

Udostępnij ten post


Link to postu
Udostępnij na innych stronach

Hej,

Dzięki za wszystkie odpowiedzi, poprawiłem trochę konfigurację my.cnf, włączyłem też slow-query-log oraz log-queries-not-using-indexes i powoli poprawiam te zapytania które się da oraz nakładam indeksy w miarę możliwości.

Wcześniej 4 skrypty kończyły sprawdzanie danych ok. 16-17, teraz jest to godzina 10 więc jakiś efekt jest ;)

@sylver74 - około 30000

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ę


×