MySQL nie wyrabia - jak rozbudować serwer?

[mlody_hoster 0]

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 Luty 24, 2016 przez mlody_hoster (zobacz historię edycji)

[Gość Kamikadze]

Może wystarczy optymalizacja serwera mysql?

 

mysqltuner.pl na początek.

[behemoth 230]

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

[mlody_hoster 0]

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

[gutek 23]

a jaki jest rozmiar tabel / bazy?

[mlody_hoster 0]

4 bazy - 1.05 GB, 787.6 MB, 648.0 MB, 456.3 MB

[Gość Kamikadze]

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

[gutek 23]

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 ?

[mlody_hoster 0]

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)

[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 Luty 24, 2016 przez Spoofy (zobacz historię edycji)

[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

[Pan Kot 1535]

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.

[mlody_hoster 0]

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

[kafi 2425]

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)

[Gość mariaczi]

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

o cache przedmówcy już wspominali

[Misiek08 285]

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.

[sylver74 3]

@mlody_hoster

A możesz odezwać się na pw i napisać ile masz tam "fraz", miałem podobny problem.

[mlody_hoster 0]

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