optymalizacja mysql, wolne zapytania

[andycole 0]

Witam,

 

Mam problem z VPSem. Config to:

 

2x Intel® Xeon E5520 @ 2.27GHz

80 GB HD SAS RAID1

4 GB RAM

 

Mysql 5.3, php 5.3 fcgi z xCache, nginx, memcache.

 

Problem, polega na tym, że load jest na poziomie 1-2, mimo że na liście htop jest tylko kilka procesów po ok +/- 1% zużycia CPU. Dodatkowo co jakiś czas do slow queries log zapisywane są bardzo proste zapytania, których czas wykonania oscyluje między 7, a nawet 40 sekund! Często zdarza się, że serwer odpowiada na ping, ale serwis nie działa (mysql i prawdopobnie ssh wiszą). Jest to bardzo irytujące i nie mam już pomysłu jak sobie z tym radzić.

 

Wiem, że minęło dopiero 8 godzin od startu serwera, ale mam nadzieję, że uda Wam się mi pomóc mimo to. Mam stosunkowo dużo zapytań zapisujących do bazy, 3 gb wolnego ramu... Wiem, że niektóre bufory strasznie duże, ale to efekt mojej walki, metodą prób i błędów Poniżej zrzuty ze skryptów:

 

mysqlreport:

 

MySQL 5.0.67-log		 uptime 0 7:55:53	   Mon Mar 19 23:51:53 2012
__ Key _________________________________________________________________
Buffer used	 9.70M of 512.00M  %Used:   1.89
 Current	  67.51M			%Usage:  13.18
Write hit	  27.66%
Read hit	   99.51%__ Questions ___________________________________________________________
Total		   1.04M	36.6/s
 Com_		373.67k	13.1/s  %Total:  35.79
 QC Hits	 369.90k	13.0/s		   35.43
 COM_QUIT	187.93k	 6.6/s		   18.00
 DMS		 113.67k	 4.0/s		   10.89
 -Unknown	  1.21k	 0.0/s			0.12
Slow 1 s		2.63k	 0.1/s			0.25  %DMS:   2.32  Log:  ON
DMS		   113.67k	 4.0/s		   10.89
 INSERT	   61.43k	 2.2/s			5.88		 54.05
 SELECT	   29.23k	 1.0/s			2.80		 25.71
 UPDATE	   22.68k	 0.8/s			2.17		 19.96
 DELETE		  322	 0.0/s			0.03		  0.28
 REPLACE		   0	   0/s			0.00		  0.00
Com_		  373.67k	13.1/s		   35.79
 set_option  186.74k	 6.5/s		   17.89
 change_db   186.49k	 6.5/s		   17.86
 show_variab	 163	 0.0/s			0.02
__ SELECT and Sort _____________________________________________________
Scan			2.26k	 0.1/s %SELECT:   7.74
Range			 655	 0.0/s			2.24
Full join		   0	   0/s			0.00
Range check		 0	   0/s			0.00
Full rng join	   0	   0/s			0.00
Sort scan		 494	 0.0/s
Sort range		955	 0.0/s
Sort mrg pass	   0	   0/s__ Query Cache _________________________________________________________
Memory usage	3.65M of  64.00M  %Used:   5.71
Block Fragmnt  25.27%
Hits		  369.90k	13.0/s
Inserts		27.53k	 1.0/s
Insrt:Prune  27.53k:1	 1.0/s
Hit:Insert	13.44:1
__ Table Locks _________________________________________________________
Waited			283	 0.0/s  %Total:   0.22
Immediate	 129.41k	 4.5/s__ Tables ______________________________________________________________
Open			  388 of  512	%Cache:  75.78
Opened			501	 0.0/s
__ Connections _________________________________________________________
Max used		   39 of   70	  %Max:  55.71
Total		 187.94k	 6.6/s__ Created Temp ________________________________________________________
Disk table		104	 0.0/s
Table			 714	 0.0/s	Size:  64.0M
File				0	   0/s
__ Threads _____________________________________________________________
Running			 1 of	1
Cached			 38 of  384	  %Hit:  99.98
Created			39	 0.0/s
Slow				0	   0/s__ Aborted _____________________________________________________________
Clients			36	 0.0/s
Connects		1.08k	 0.0/s
__ Bytes _______________________________________________________________
Sent		  489.71M   17.2k/s
Received	   64.48M	2.3k/s__ InnoDB Buffer Pool __________________________________________________
Usage			   0 of	   0  %Used:   0.00
Read hit		0.00%
Pages
 Free			  0			%Total:   0.00
 Data			  0					  0.00 %Drty:   0.00
 Misc			  0					  0.00
 Latched		   0					  0.00
Reads			   0	   0/s
 From file		 0	   0/s			0.00
 Ahead Rnd		 0	   0/s
 Ahead Sql		 0	   0/s
Writes			  0	   0/s
Flushes			 0	   0/s
Wait Free		   0	   0/s
__ InnoDB Lock _________________________________________________________
Waits			   0	   0/s
Current			 0
Time acquiring
 Total			 0 ms
 Average		   0 ms
 Max			   0 ms__ InnoDB Data, Pages, Rows ____________________________________________
Data
 Reads			 0	   0/s
 Writes			0	   0/s
 fsync			 0	   0/s
 Pending
Reads		   0
Writes		  0
fsync		   0
Pages
 Created		   0	   0/s
 Read			  0	   0/s
 Written		   0	   0/sRows
 Deleted		   0	   0/s
 Inserted		  0	   0/s
 Read			  0	   0/s
 Updated		   0	   0/s

 

mysql-tuner

 

>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering-------- General Statistics --------------------------------------------------
[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;32mOK[0m] Currently running supported MySQL version 5.0.67-log
[[0;31m!![0m] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;31m-Archive [0m[0;31m-BDB [0m[0;31m-Federated [0m[0;31m-InnoDB [0m[0;31m-ISAM [0m[0;31m-NDBCluster [0m
[[0;34m--[0m] Data in MyISAM tables: 95M (Tables: 254)
[[0;31m!![0m] Total fragmented tables: 5-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 8h 32m 46s (1M q [35.169 qps], 193K conn, TX: 503M, RX: 67M)
[[0;34m--[0m] Reads / Writes: 24% / 76%
[[0;34m--[0m] Total buffers: 650.0M global + 35.5M per thread (70 max threads)
[[0;31m!![0m] Allocating > 2GB RAM on 32-bit systems can cause system instability
[[0;31m!![0m] Maximum possible memory usage: 3.1G (78% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (2K/1M)
[[0;32mOK[0m] Highest usage of available connections: 55% (39/70)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 512.0M/58.6M
[[0;32mOK[0m] Key buffer hit rate: 99.5% (2M cached / 14K reads)
[[0;32mOK[0m] Query cache efficiency: 92.5% (379K cached / 409K selects)
[[0;32mOK[0m] Query cache prunes per day: 0
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[[0;32mOK[0m] Temporary tables created on disk: 10% (139 on disk / 1K total)
[[0;32mOK[0m] Thread cache hit rate: 99% (39 created / 193K connections)
[[0;32mOK[0m] Table cache hit rate: 77% (390 open / 503 opened)
[[0;32mOK[0m] Open file limit used: 59% (662/1K)
[[0;32mOK[0m] Table locks acquired immediately: 99% (143K immediate / 143K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate

 

mysql-tuning-primer

 

[0m
[m(B[0m[0mUsing login values from /.my.cnf
[m(B[0m[1;31m- INITIAL LOGIN ATTEMPT FAILED -[m(B[0m[0mTesting for stored webmin passwords:[m(B[0m[1;31m None Found
[m(B[0m[0mCould not auto detect login info![m(B[0m[0mFound Sockets:
/tmp/mysql.sock
[m(B[0m[31mUsing: /tmp/mysql.sock
[m(B[0m[0m
[m(B[0m[0m
[m(B[0m[0m [m(B[0m[1;34m-- MYSQL PERFORMANCE TUNING PRIMER --
[m(B[0m[0m	  - By: Matthew Montgomery -
[m(B[0m
[0mMySQL Version 5.0.67-log i686
[m(B[0m
[0mUptime = 0 days 8 hrs 30 min 52 sec
[m(B[0m[0mAvg. qps = 35
[m(B[0m[0mTotal Questions = 1080695
[m(B[0m[0mThreads Connected = 1
[m(B[0m
[1;31mWarning: [m(B[0m[1;31mServer has not been running for at least 48hrs.
[m(B[0m[1;31mIt may not be safe to use these recommendations
[m(B[0m
[31mTo find out more information on how each of these
[m(B[0m[31mruntime variables effects performance visit:
[m(B[0m[1;34mhttp://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
[m(B[0m[1;34mVisit http://www.mysql.com/products/enterprise/advisors.html
[m(B[0m[1;34mfor info about MySQL's Enterprise Monitoring and Advisory Service
[m(B[0m
[1;34mSLOW QUERIES
[m(B[0m[0mThe slow query log is enabled.
[m(B[0m[0mCurrent long_query_time = 1 sec.
[m(B[0m[0mYou have [m(B[0m[1;31m2692 [m(B[0m[0mout of [m(B[0m[1;31m1080721 [m(B[0m[0mthat take longer than 1 sec. to complete
[m(B[0m[32mYour long_query_time seems to be fine
[m(B[0m
[1;34mBINARY UPDATE LOG
[m(B[0m[0mThe binary update log is [m(B[0m[1;31mNOT [m(B[0m[0menabled.
[m(B[0m[31mYou will not be able to do point in time recovery
[m(B[0m[33mSee http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
[m(B[0m
[1;34mWORKER THREADS
[m(B[0m[0mCurrent thread_cache_size = 384
[m(B[0m[0mCurrent threads_cached = 38
[m(B[0m[0mCurrent threads_per_sec = 0
[m(B[0m[0mHistoric threads_per_sec = 0
[m(B[0m[32mYour thread_cache_size is fine
[m(B[0m
[1;34mMAX CONNECTIONS
[m(B[0m[0mCurrent max_connections = 70
[m(B[0m[0mCurrent threads_connected = 1
[m(B[0m[0mHistoric max_used_connections = 39
[m(B[0m[0mThe number of used connections is [m(B[0m[32m55% [m(B[0m[0mof the configured maximum.
[m(B[0m[32mYour max_connections variable seems to be fine.
[m(B[0m
[1;34mMEMORY USAGE
[m(B[0m[0mMax Memory Ever Allocated : 1.92 G
[m(B[0m[0mConfigured Max Per-thread Buffers : 2.42 G
[m(B[0m[0mConfigured Max Global Buffers : 586 M
[m(B[0m[0mConfigured Max Memory Limit : 2.99 G
[m(B[0m[0mPhysical Memory : 3.90 G
[m(B[0m[32mMax memory limit seem to be within acceptable norms
[m(B[0m
[1;34mKEY BUFFER
[m(B[0m[0mCurrent MyISAM index space = 58 M
[m(B[0m[0mCurrent key_buffer_size = 512 M
[m(B[0m[0mKey cache miss rate is 1 : 211
[m(B[0m[0mKey buffer free ratio = 86 %
[m(B[0m[32mYour key_buffer_size seems to be fine
[m(B[0m
[1;34mQUERY CACHE
[m(B[0m[32mQuery cache is enabled
[m(B[0m[0mCurrent query_cache_size = 64 M
[m(B[0m[0mCurrent query_cache_used = 4 M
[m(B[0m[0mCurrent query_cache_limit = 3 M
[m(B[0m[0mCurrent Query cache Memory fill ratio = 6.50 %
[m(B[0m[0mCurrent query_cache_min_res_unit = 4 K
[m(B[0m[31mYour query_cache_size seems to be too high.
[m(B[0m[31mPerhaps you can use these resources elsewhere
[m(B[0m[33mMySQL won't cache query results that are larger than query_cache_limit in size
[m(B[0m
[1;34mSORT OPERATIONS
[m(B[0m[0mCurrent sort_buffer_size = 7 M
[m(B[0m[0mCurrent [m(B[0m[0mread_rnd_buffer_size [m(B[0m[0m= 8 M
[m(B[0m[32mSort buffer seems to be fine
[m(B[0m
[1;34mJOINS
[m(B[0m[0mCurrent join_buffer_size = 4.00 M
[m(B[0m[0mYou have had 0 queries where a join could not use an index properly
[m(B[0m[32mYour joins seem to be using indexes properly
[m(B[0m[1;31mjoin_buffer_size >= 4 M
[m(B[0m[1;31mThis is not advised
[m(B[0m
[1;34mOPEN FILES LIMIT
[m(B[0m[0mCurrent open_files_limit = 1104 files
[m(B[0m[33mThe open_files_limit should typically be set to at least 2x-3x
[m(B[0m[33mthat of table_cache if you have heavy MyISAM usage.
[m(B[0m[32mYour open_files_limit value seems to be fine
[m(B[0m
[1;34mTABLE CACHE
[m(B[0m[0mCurrent table_cache value = 512 tables
[m(B[0m[0mYou have a total of 271 tables
[m(B[0m[0mYou have [m(B[0m[32m390 [m(B[0m[0mopen tables.
[m(B[0m[32mThe table_cache value seems to be fine
[m(B[0m
[1;34mTEMP TABLES
[m(B[0m[0mCurrent max_heap_table_size = 256 M
[m(B[0m[0mCurrent tmp_table_size = 64 M
[m(B[0m[0mOf 1221 temp tables, 10% were created on disk
[m(B[0m[32mCreated disk tmp tables ratio seems fine
[m(B[0m
[1;34mTABLE SCANS
[m(B[0m[0mCurrent read_buffer_size = 16 M
[m(B[0m[0mCurrent table scan ratio = 41 : 1
[m(B[0m[31mread_buffer_size is over 8 MB [m(B[0m[31mthere is probably no need for such a large read_buffer
[m(B[0m
[1;34mTABLE LOCKING
[m(B[0m[0mCurrent Lock Wait ratio = [m(B[0m[31m1 : 503
[m(B[0m[0mYou may benefit from selective use of InnoDB.
[m(B[0m[0mIf you have long running SELECT's against MyISAM tables and perform
[m(B[0m[0mfrequent updates consider setting 'low_priority_updates=1'
[m(B[0m[0mIf you have a high concurrency of inserts on Dynamic row-length tables
[m(B[0m[0mconsider setting 'concurrent_insert=2'.
[m(B[0m

 

Zmienne zaznaczone na czerwono ze Status Mysql:

 

Created_tmp_disk_tables 139

Opened_tables 503

Table_locks_waited 284

Handler_read_rnd_next 1 317 k

Handler_read_rnd 116 k

Slow_queries 2 740

Edytowano Marzec 19, 2012 przez andycole (zobacz historię edycji)

[regdos 1848]

A te długo wykonujące się zapytania to jakie są? Może brak odpowiednich indeksów? A może wtedy cała maszyna zalicza jakiś IO wait z powodu innego klienta?

[andycole 0]

są naprawdę nieskomplikowane zapytania, typu SELECT x, y FROM table WHERE id=821; gdzie id jest indeksem, a tabele ma kilkaset rekordów.

 

a jak sprawdzić, monitorować system pod względem IO?

[pietrovek 19]

spójrz co wypluwa iostat

Edytowano Marzec 20, 2012 przez Gość (zobacz historię edycji)

[andycole 0]

W kilku minut po restarcie:

 

 

Linux 2.6.18-194.8.1.el5.028stab070.5PAE (vps) 03/20/12 _i686_

 

avg-cpu: %user %nice %system %iowait %steal %idle

5.65 0.00 2.20 33.74 0.00 58.42

 

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

[Gość Kamikadze]

No to nie masz się czym chwalić hehe

 

Pisz do hostingu że jakość jest ch...

 

 

Tak dla Ciebie wkleję dane z dedyka który wykonuje średnio 2 zapytania / sekundę (prawie 8k / godzinę)

 

 

 

avg-cpu: %user %nice %system %iowait %steal %idle

1,24 0,05 0,51 0,01 0,00 98,18

 

 

Moim zdaniem tutaj nie obejdzie się bez kontaktu z hostingiem, aby przenieśli Twojego VPSa na inną maszynę Nie wiem jak oni chcą żebyś wykorzystał zasoby jak prostych rzeczy nie da się wykonać

[andycole 0]

To co, może być to wina sprzętu? Dziwne, bo od miesiąca jestem na tej maszynie, wcześniej miałem 1gb ramu i o wiele słabszy procesor i było ok.

 

Jakie zapytania masz na myśli? Mysql czy do serwera apache/nginx?

 

Z supportem kontaktowałem się wiele razy. Wg nich wina leży po stronie moich zapytań do bazy, ewentualnie po stronie konfigu mysql. Czy 33% iowait jest oznaką problemów ze sprzętem?

Edytowano Marzec 20, 2012 przez andycole (zobacz historię edycji)

[Piotrek Mazurkiewicz 19]

Na logikę biorąc skoro zapytania/oprogramowanie jest takie samo a sprzęt sporo wydajniejszy i chodzi to całościowo znacznie gorzej od 4x słabszego zestawu to wypada przedstawić DOKŁADNIE problem osobie/firmie w której masz wykupionego virtual'a. Z ciekawości, możesz powiedzieć co to za firma?

[Gość Kamikadze]

Podejrzewam overselling albo któryś z klientów rżnie maszynę ile może

[andycole 0]

nie chcę robić czarny PR firmie, bo jest to znana na tym forum marka

 

wygląda na to, że 32mb na memcache było niewsystarczające, zwiększyłem do 128mb i iowait spada, aktualnie jest 7% w dobrym kierunku idę?

[malu 460]

Ogólnie ciężko to stwierdzić jednoznacznie, czy to problem po stronie oversellingu, bo problem może równie dobrze wynikać z konfiguracji kontenera i innych obciążających kontenerów. Jednak wszystko wskazuje na to, że po prostu jesteś na dociążonej matce.

Moim zdaniem to nie żadne robienie czarnego PR, po prostu w firmie X masz tak, to możesz śmiało o tym mówić. Co więcej, może to zmobilizuje firmę do naprawy, być może forum będzie czytała bardziej kompetentna osoba, aniżeli ta obsługująca Twoje zgłoszenia. ; )

[andycole 0]

Jestem po całym weekendzie obserwacji serwera. Skoki iowait są niezależne od ruchu na moim serwisie. Potrafią wzbić się do 90% nawet, powoduje to wzrost loadu całego serwera. Oczywiście support uparcie twierdzi, że to moje skrypty i mój load generują taki iowait. Uciekam stamtąd... a co do firmy to neteasy. Byłem z nimi prawie 3 lata, ale na chwilę obecną jedynie z ich konta SEO jestem zadowolony.