Большая нагрузка на сервер MySQL

Все блоги / DLE движок для сайтов 16 сентября 2014 652   
Большая нагрузка на сервер MySQL
Добрый день, сегодня хостер отключил мне сайт - за то что целую неделю была нагрузка. Изменения на сайте не проводил. Раз и началось. При допустимых нагрузках до 500 к БД стало значение 1200. Выслали мне копи пастом такой лог. Здравствуйте. Ниже предоставлен анализ медленных запросов

 Показать / Скрыть текст

# 697s user time, 4.3s system time, 27.10M rss, 214.48M vsz # Current date: Tue Sep 16 15:26:44 2014 # Hostname: mysql1.ihc.ru # Files: /var/log/mysql-slow.log-20140915 # Overall: 803 total, 20 unique, 0.01 QPS, 0.00x concurrency _____________ # Time range: 2014-09-14 04:02:14 to 2014-09-15 04:00:24 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 4s 930us 828ms 5ms 11ms 33ms 1ms # Lock time 1s 25us 820ms 1ms 626us 29ms 80us # Rows sent 51.14k 0 21.73k 65.22 49.17 802.06 13.83 # Rows examine 1.22M 1.00k 29.06k 1.55k 3.88k 1.54k 1012.63 # Rows affecte 0 0 0 0 0 0 0 # Rows read 1.22M 1.00k 29.06k 1.55k 3.88k 1.54k 1012.63 # Bytes sent 13.73M 55 5.73M 17.51k 8.06k 211.47k 8.06k # Query size 124.92k 54 362 159.31 183.58 30.36 158.58 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0xAD4B1F2B87E0B46C 1.7700 46.2% 607 0.0029 0.38 SELECT dle_post dle_post_extras # 2 0x1B3CE657FE8096EB 0.4752 12.4% 46 0.0103 0.00 SELECT dle_users # 3 0x8A83C4B6235845BB 0.4339 11.3% 1 0.4339 0.00 SELECT dle_post # 4 0x450DA2B0B5AF7C83 0.3052 8.0% 35 0.0087 0.01 SELECT dle_users # 5 0xCAED0E2F2BB5F3BF 0.2337 6.1% 26 0.0090 0.00 SELECT dle_post dle_post_extras # 6 0x1157D2AC4247FB75 0.1335 3.5% 54 0.0025 0.00 SELECT dle_logs_comm dle_comments # 7 0x01E5885A23B4C28E 0.0984 2.6% 2 0.0492 0.00 SELECT p?_kinobaza.film_categories p?_kinobaza.films # 8 0x656C80DE22273C5B 0.0962 2.5% 1 0.0962 0.00 SELECT dle_logs # 9 0xEBD15D56B0E9419E 0.0669 1.7% 1 0.0669 0.00 SELECT dle_users # 10 0xD0BE4DEF74B5D775 0.0567 1.5% 3 0.0189 0.02 SELECT dle_users # MISC 0xMISC 0.1586 4.1% 27 0.0059 0.0 # Query 1: 0.01 QPS, 0.00x concurrency, ID 0xAD4B1F2B87E0B46C at byte 812964726 # Scores: V/M = 0.38 # Time range: 2014-09-14 04:02:14 to 2014-09-15 04:00:24 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 75 607 # Exec time 46 2s 948us 828ms 3ms 2ms 33ms 1ms # Lock time 92 1s 44us 820ms 2ms 236us 33ms 73us # Rows sent 16 8.30k 14 14 14 14 0 14 # Rows examine 50 625.02k 1.03k 1.03k 1.03k 1012.63 0.00 1012.63 # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 50 625.02k 1.03k 1.03k 1.03k 1012.63 0.00 1012.63 # Bytes sent 35 4.90M 8.27k 8.27k 8.27k 8.27k 0 8.27k # Query size 77 96.62k 163 163 163 163 0 163 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # # 1ms ################################################################ # 10ms # # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_post'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_post`G # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_post_extras'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_post_extras`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT t1.id,t1.title,t1.date, t1.alt_name,t1.category, t1.short_story FROM dle_post t1, dle_post_extras t2 WHERE t1.id=t2.news_id ORDER BY t2.rating DESC LIMIT 14G # Query 2: 0.00 QPS, 0.00x concurrency, ID 0x1B3CE657FE8096EB at byte 57291396 # Scores: V/M = 0.00 # Time range: 2014-09-14 05:28:19 to 2014-09-15 00:48:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 5 46 # Exec time 12 475ms 6ms 26ms 10ms 14ms 4ms 11ms # Lock time 1 18ms 56us 3ms 382us 725us 406us 247us # Rows sent 0 46 1 1 1 1 0 1 # Rows examine 14 186.78k 4.04k 4.08k 4.06k 4.07k 15.50 3.88k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 14 186.78k 4.04k 4.08k 4.06k 4.07k 15.50 3.88k # Bytes sent 0 2.70k 60 60 60 60 0 60 # Query size 6 8.00k 138 213 178.17 202.40 17.03 174.84 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # 1ms ############################ # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_users'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_users`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) as count FROM dle_users WHERE email = 'safagese_1984@mail.ru' OR LOWER(name) REGEXP '[[::]]' OR name = 'williamhic'G # Query 3: 0 QPS, 0x concurrency, ID 0x8A83C4B6235845BB at byte 375628181 # Scores: V/M = 0.00 # Time range: all events occurred at 2014-09-14 11:38:27 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 11 434ms 434ms 434ms 434ms 434ms 0 434ms # Lock time 0 26us 26us 26us 26us 26us 0 26us # Rows sent 1 1.00k 1.00k 1.00k 1.00k 1.00k 0 1.00k # Rows examine 0 1.00k 1.00k 1.00k 1.00k 1.00k 0 1.00k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 0 1.00k 1.00k 1.00k 1.00k 1.00k 0 1.00k # Bytes sent 41 5.73M 5.73M 5.73M 5.73M 5.73M 0 5.73M # Query size 0 75 75 75 75 75 0 75 # String: # Databases p64254_newdeafo # Hosts localhost # Last errno 0 # Users mysql_backup # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_post'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_post`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `dle_post`G # Query 4: 0.00 QPS, 0.00x concurrency, ID 0x450DA2B0B5AF7C83 at byte 335739940 # Scores: V/M = 0.01 # Time range: 2014-09-14 10:55:25 to 2014-09-15 00:41:12 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 35 # Exec time 7 305ms 4ms 56ms 9ms 14ms 8ms 8ms # Lock time 1 15ms 48us 905us 416us 761us 263us 424us # Rows sent 0 15 0 4 0.43 1.96 0.85 0 # Rows examine 11 142.19k 4.05k 4.08k 4.06k 4.07k 11.50 3.88k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 11 142.19k 4.05k 4.08k 4.06k 4.07k 11.50 3.88k # Bytes sent 0 3.33k 90 163 97.43 124.25 15.25 88.31 # Query size 3 4.38k 95 211 128.26 166.51 22.48 118.34 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ########## # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_users'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_users`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT name FROM dle_users WHERE LOWER(name) REGEXP '[[::]]' OR name = 'lazarevyurasik'G # Query 5: 0.00 QPS, 0.00x concurrency, ID 0xCAED0E2F2BB5F3BF at byte 436973964 # Scores: V/M = 0.00 # Time range: 2014-09-14 10:47:50 to 2014-09-15 02:20:03 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 3 26 # Exec time 6 234ms 7ms 16ms 9ms 11ms 2ms 8ms # Lock time 0 11ms 73us 3ms 419us 2ms 813us 98us # Rows sent 2 1.27k 50 50 50 50 0 50 # Rows examine 2 28.58k 1.10k 1.10k 1.10k 1.09k 0 1.09k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 2 28.58k 1.10k 1.10k 1.10k 1.09k 0 1.09k # Bytes sent 1 183.35k 7.00k 7.08k 7.05k 6.96k 0 6.96k # Query size 4 5.97k 235 235 235 235 0 235 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ############### # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_post'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_post`G # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_post_extras'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_post_extras`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT p.id, p.date, p.title, p.category, p.autor, p.alt_name, p.comm_num, p.approve, p.fixed, e.news_read, e.votes FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) ORDER BY fixed desc, approve asc, date desc LIMIT 0,50G # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x1157D2AC4247FB75 at byte 30636872 # Scores: V/M = 0.00 # Time range: 2014-09-14 04:43:49 to 2014-09-15 02:03:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 6 54 # Exec time 3 133ms 2ms 13ms 2ms 3ms 1ms 2ms # Lock time 3 35ms 200us 11ms 656us 925us 1ms 384us # Rows sent 1 609 0 97 11.28 80.10 28.19 0 # Rows examine 9 113.70k 2.10k 2.11k 2.11k 2.06k 0 2.06k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 9 113.70k 2.10k 2.11k 2.11k 2.06k 0 2.06k # Bytes sent 0 5.87k 55 540 111.39 463.90 145.12 54.21 # Query size 5 6.25k 114 125 118.57 118.34 3.00 118.34 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_logs_comm'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_logs_comm`G # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_comments'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_comments`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT (SELECT SUM(value) FROM dle_logs_comm WHERE comm_id=c.id) as rating FROM dle_comments c where autor ='�������'G # Query 7: 0.00 QPS, 0.00x concurrency, ID 0x01E5885A23B4C28E at byte 89770356 # Scores: V/M = 0.00 # Time range: 2014-09-14 06:00:18 to 06:51:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 2 # Exec time 2 98ms 41ms 58ms 49ms 58ms 12ms 49ms # Lock time 0 194us 77us 117us 97us 117us 28us 97us # Rows sent 0 24 12 12 12 12 0 12 # Rows examine 2 36.71k 7.64k 29.06k 18.35k 29.06k 15.15k 18.35k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 2 36.71k 7.64k 29.06k 18.35k 29.06k 15.15k 18.35k # Bytes sent 0 38.09k 12.76k 25.32k 19.04k 25.32k 8.88k 19.04k # Query size 0 724 362 362 362 362 0 362 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p28362_kinobaza # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p28362_kinobaza` LIKE 'film_categories'G # SHOW CREATE TABLE `p28362_kinobaza`.`film_categories`G # SHOW TABLE STATUS FROM `p28362_kinobaza` LIKE 'films'G # SHOW CREATE TABLE `p28362_kinobaza`.`films`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM (SELECT IdFilm FROM p28362_kinobaza.film_categories WHERE IdCategory IN (189,370,363) AND IdFilm <> 78356 ORDER BY RAND() LIMIT 48) t1 JOIN p28362_kinobaza.films t2 ON t1.IdFilm = t2.id WHERE (present = 'y' AND Warehouse > 0) OR sz>0 LIMIT 12G # Query 8: 0 QPS, 0x concurrency, ID 0x656C80DE22273C5B at byte 375624882 # Scores: V/M = 0.00 # Time range: all events occurred at 2014-09-14 11:38:27 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 2 96ms 96ms 96ms 96ms 96ms 0 96ms # Lock time 0 25us 25us 25us 25us 25us 0 25us # Rows sent 42 21.73k 21.73k 21.73k 21.73k 21.73k 0 21.73k # Rows examine 1 21.73k 21.73k 21.73k 21.73k 21.73k 0 21.73k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 1 21.73k 21.73k 21.73k 21.73k 21.73k 0 21.73k # Bytes sent 6 924.67k 924.67k 924.67k 924.67k 924.67k 0 924.67k # Query size 0 75 75 75 75 75 0 75 # String: # Databases p64254_newdeafo # Hosts localhost # Last errno 0 # Users mysql_backup # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_logs'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_logs`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `dle_logs`G # Query 9: 0 QPS, 0x concurrency, ID 0xEBD15D56B0E9419E at byte 375629777 # Scores: V/M = 0.00 # Time range: all events occurred at 2014-09-14 11:38:27 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 1 67ms 67ms 67ms 67ms 67ms 0 67ms # Lock time 0 31us 31us 31us 31us 31us 0 31us # Rows sent 7 4.05k 4.05k 4.05k 4.05k 4.05k 0 4.05k # Rows examine 0 4.05k 4.05k 4.05k 4.05k 4.05k 0 4.05k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 0 4.05k 4.05k 4.05k 4.05k 4.05k 0 4.05k # Bytes sent 5 736.05k 736.05k 736.05k 736.05k 736.05k 0 736.05k # Query size 0 76 76 76 76 76 0 76 # String: # Databases p64254_newdeafo # Hosts localhost # Last errno 0 # Users mysql_backup # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_users'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_users`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `dle_users`G # Query 10: 0.00 QPS, 0.00x concurrency, ID 0xD0BE4DEF74B5D775 at byte 188214819 # Scores: V/M = 0.02 # Time range: 2014-09-14 08:06:10 to 23:47:18 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 3 # Exec time 1 57ms 3ms 50ms 19ms 48ms 21ms 3ms # Lock time 0 540us 40us 284us 180us 273us 99us 214us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 12.18k 4.05k 4.08k 4.06k 4.07k 13 3.88k # Rows affecte 0 0 0 0 0 0 0 0 # Rows read 0 12.18k 4.05k 4.08k 4.06k 4.07k 13 3.88k # Bytes sent 0 270 90 90 90 90 0 90 # Query size 0 170 54 59 56.67 56.92 2.49 56.92 # String: # Databases p64254_newdeafo # Hosts # Last errno 0 # Users p64254_newdeafo # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `p64254_newdeafo` LIKE 'dle_users'G # SHOW CREATE TABLE `p64254_newdeafo`.`dle_users`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT name from dle_users where LOWER(name) = 'hermanwawn'G

Подскажите - кто нибудь может понять что создает нагрузку? и почему в конце чей то ник?возможно это атака?
  • Оцените публикацию
  • 0

Похожие публикации

@
  • bowtiesmilelaughingblushsmileyrelaxedsmirk
    heart_eyeskissing_heartkissing_closed_eyesflushedrelievedsatisfiedgrin
    winkstuck_out_tongue_winking_eyestuck_out_tongue_closed_eyesgrinningkissingstuck_out_tonguesleeping
    worriedfrowninganguishedopen_mouthgrimacingconfusedhushed
    expressionlessunamusedsweat_smilesweatdisappointed_relievedwearypensive
    disappointedconfoundedfearfulcold_sweatperseverecrysob
    joyastonishedscreamtired_faceangryragetriumph
    sleepyyummasksunglassesdizzy_faceimpsmiling_imp
    neutral_faceno_mouthinnocent

Архив публикаций