andycole

jestem na etapie przenosin do innej firmy, tam zobaczymy czy problem pojawi się ponownie dzięki za pomoc

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.

tak, właśnie openvz mam, shmpages (held 20260, maxheld 36644, barrier 262144, limit 262144), info z pliku /proc/user_beancounters wartość jest zbyt mała czy mogą być problemy?

serwis piłkarski, teraz wg GA ok 100online staty miesięczne to 160k UU, 1,5mln odsłon

Witam, Mój VPS to: 2x Intel® Xeon E5520 @ 2.27GHz 4 GB RAM Próbuję jak najoptymalniej go skonfigurować, ale miotam się trochę jak dziecko we mgle. Poszukuję jakiejś dobrej duszy, która pomoże w zoptymalizowaniu konfiguracji serwera albo dobrego specjalisty, który pomoże profesjonalnie - odpłatnie. Ustawienia mysql: [mysqld] collation_server=utf8_unicode_ci character_set_server=utf8 skip-character-set-client-handshake max_connections=70 max_user_connections=30 read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer = 16M join_buffer_size = 2M key_buffer = 256M query_cache_size = 32M max_heap_table_size = 256M tmp_table_size = 256M max_allowed_packet = 128M table_cache = 1024 ### Global variables ### thread_stack = 512K thread_cache_size = 384 wait_timeout = 80 myisam_sort_buffer_size = 64M binlog_cache_size = 65536 max_join_size = 8M thread_concurrency = 4 query_cache_limit = 3M query_cache_type = 1 query_cache_min_res_unit = 1024 #low_priority_updates = 1 long_query_time = 1 log_error = error.dat log-slow-queries = slow.dat log-queries-not-using-indexes skip-innodb skip-name-resolve skip-bdb nginx worker_processes 2; #worker_rlimit_nofile 3000; user apache; events{ worker_connections 1024; } http{ include mime.types; default_type application/octet-stream; server_tokens off; access_log off; open_file_cache max=2000 inactive=1m; open_file_cache_valid 1m; open_file_cache_min_uses 3; open_file_cache_errors on; fastcgi_buffer_size 4k; fastcgi_buffers 256 4k; fastcgi_max_temp_file_size 0; #proxy_buffers 256 4k; #proxy_max_temp_file_size 0; gzip on; gzip_min_length 2000; gzip_types text/plain text/css application/x-javascript text/xml application/xml application/xml+rss text/javascript; include /usr/local/nginx/conf/vhosts/*; } php fpm emergency_restart_threshold 10 emergency_restart_interval 1m process_control_timeout 5s daemonize yes max_children 25 StartServers 10 MinSpareServers 5 MaxSpareServers 20 request_terminate_timeout 15s request_slowlog_timeout 5s rlimit_files 0 rlimit_core 0 max_requests 600 memcached ustawiony na 128mb, zużycie na poziomie 3% zrzut z iostat: Linux 2.6.18-194.8.1.el5.028stab070.5PAE (vps) 03/31/12 _i686_ avg-cpu: %user %nice %system %iowait %steal %idle 3.63 0.00 1.60 18.47 0.00 76.30 Load utrzymuje się na poziomie ok 1. Na liście procesów nie widać jakiegoś specjalnie dużego obciążenia, ale iowait skacze i to wysoko. Status mysqla zwraca mi: [/left] [left]Created_tmp_tables 351 Created_tmp_disk_tables 64 Open_tables 387 Opened_tables 393 Podejrzewam, że wina leży właśnie po stronie mysqla - tworzone są tabele na dysku. Tylko jak sobie z tym poradzić? Wydaje mi się że max_heap_table_size = 256M tmp_table_size = 256M table_cache = 1024 są wystarczające.... Jak uważacie? Może dałoby radę też zoptymalizować ustawienia nginx/php fcgi dla większej wydajności? Pozdrawiam andycole

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

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?

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

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?

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

Tym LIKE'iem jechałem dosłownie kilka razy, sam, w zaciszu PhpMyAdmina, a cała baza ma niecałe 150mb.

nie mam dev/sda tylko dev/sd1 i sd2, ale wywala mi: Standard Inquiry (36 bytes) failed [invalid argument] Retrying with a 64 byte Standard Inquiry Standard Inquiry (64 bytes) failed [invalid argument] A mandatory SMART command failed: exiting. To continue, add one or more '-T permissive' options. po dodaniu '-T permissive': smartctl version 5.38 [i686-pc-linux-gnu] Copyright (C) 2002-8 Bruce Allen Home page is http://smartmontools.sourceforge.net/ Standard Inquiry (36 bytes) failed [invalid argument] Retrying with a 64 byte Standard Inquiry Standard Inquiry (64 bytes) failed [invalid argument] Request Sense failed, [invalid argument]Error Counter logging not supported Device does not support Self Test logging

Load spadał dopiero po 45-60 minutach i dopiero wtedy serwer zaczynał odpowiadać. Nie, nie próbowałem logować się przez VNC, nawet nie wiem za bardzo jak to się robi Zapytania i baza w moim skrypcie nie są do końca zoptymalizowane (brak dobrze dobranych kluczy), bo zostawiłem to na koniec pracy. Z tego co zauważyłem to problem pojawia się przy jakiś skomplikowanych zapytaniach do bazy, typu LIKE %%. Wszedłem w staty mysql i w ciągu 14 godzin nastukało mi takie coś: Handler_read_rnd 31 k Handler_read_rnd_next 16 M Czy to może być powodem?

Dzięki Oto wynik: Linux 2.6.18-6-xen-686 (neteasy.pl) 01/23/12 _i686_ avg-cpu: %user %nice %system %iowait %steal %idle 0.43 0.14 0.29 2.00 1.64 95.49 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda1 7.86 62.50 98.34 3110474 4894344 sda2 0.00 0.01 0.00 512 64

jak to sprawdzić ?