andycole
Użytkownicy-
Zawartość
60 -
Rejestracja
-
Ostatnio
-
nginx+php_fpm+mysql pomoc w optymalizacji configu
andycole odpisał andycole na temat w Administracja Serwerów
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.
-
nginx+php_fpm+mysql pomoc w optymalizacji configu
andycole odpisał andycole na temat w Administracja Serwerów
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? -
nginx+php_fpm+mysql pomoc w optymalizacji configu
andycole odpisał andycole na temat w Administracja Serwerów
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ć ?