Ненормально большая нагрузка на Mysql

John Smith · 17 Янв 2012

Здравствуйте. Подскажите пожалуйста, процесс mysqld загружает процессор на 102% из-за чего виснет сервер. Это при 60 000 пирах на torrentpier. Стоит связка с xbtt. Админ смотрел, сказал что дело в таблице bb_bt_tracker. Я ее проверил, не битая, все нормально. Интересно, что после Оптимизации всех таблиц в базе нагрузка становится нормальной. Дедик мощный стоит, делали тюнинг mysql - ничего не помогло, вот скрин того что происходит

Lange · 17 Янв 2012

John Smith, ОС какая стоит?

John Smith · 17 Янв 2012

Locer написал(а):
John Smith, ОС какая стоит?

CentOS

kenny · 17 Янв 2012

очевидно что не правильно настроен мускул

nshut · 17 Янв 2012

переименуйте файл announce.php на время. если нагрузка свалится, то копайте запросы аннонса, там их всего парочка, мож чуть больше. ну и поглядите какие запросы прут. может обычный ддос ложит базу. также посмотрите на загрузку диска при работе сервера, возможно стоит еще подтюнить параметры sql. ну и лонг запросы проверить есть ли и какие

John Smith · 17 Янв 2012

nshut написал(а):
также посмотрите на загрузку диска при работе сервера

как это сделать какой коммандой? и как оценить результаты, допустим если есть нагрузка

nshut написал(а):
. ну и лонг запросы проверить есть ли и какие

из лога медленных запросов sql_slow_bb.log

Код:

12-30 | 15:40:03 | 38  | 2    | 05562 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-30 | 16:24:04 | 27  | 1    | 16107 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-30 | 19:40:11 | 15  | 1    | 04824 | UPDATE buf_last_seeder b, bb_bt_torrents tor SET tor.seeder_last_seen = b.seeder_last_seen WHERE tor.topic_id = b.topic_id # Rows matched: 3532 Changed: 3532 Warnings: 0 # includes/cron/jobs/tr_update_seeder_last_seen.php(13)
12-31 | 09:40:03 | 15  | 1    | 20721 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-31 | 10:56:03 | 45  | 1    | 00383 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-31 | 12:46:03 | 19  | 2    | 20596 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-31 | 14:00:03 | 11  | 1    | 03050 | LOCK TABLES bb_bt_tracker WRITE, new_bt_last_torstat WRITE # includes/cron/jobs/tr_cleanup_and_dlstat.php(27)
12-31 | 14:00:14 | 30  | 1    | 03050 | REPLACE INTO buf_last_seeder (topic_id, seeder_last_seen) SELECT topic_id, 1325340001 FROM bb_bt_tracker WHERE seeder = 1 GROUP BY topic_id # Records: 3493 Duplicates: 3487 Warnings: 0 # includes/cron/jobs/tr_cleanup_and_dlstat.php(56)
12-31 | 15:02:03 | 10  | 1    | 15343 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
12-31 | 18:14:04 | 17  | 1    | 15654 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
01-01 | 17:40:04 | 10  | 1    | 14102 | SELECT MIN(topic_id) AS start_id, MAX(topic_id) AS finish_id FROM bb_bt_tracker # 1 rows # includes/cron/jobs/tr_make_snapshot.php(18)
01-02 | 05:00:11 | 12  | 0    | 27503 | INSERT INTO tmp_sync_topics SELECT t.topic_id, COUNT(p.post_id) AS total_posts, MIN(p.post_id) AS topic_first_post_id, MAX(p.post_id) AS topic_last_post_id, MAX(p.post_time) AS topic_last_post_time, IF(MAX(a.attach_id), 1, 0) AS topic_attachment FROM bb_topics t LEFT JOIN bb_posts p ON(p.topic_id = t.topic_id) LEFT JOIN bb_attachments a ON(a.post_id = p.post_id) WHERE t.topic_status != 2 GROUP BY t.topic_id # Records: 5171 Duplicates: 0 Warnings: 0 # includes/functions_admin.php(102)
01-02 | 10:48:05 | 37  | 1    | 08755 | REPLACE INTO buf_last_seeder (topic_id, seeder_last_seen) SELECT topic_id, 1325501281 FROM bb_bt_tracker WHERE seeder = 1 GROUP BY topic_id # Records: 3507 Duplicates: 3500 Warnings: 0 # includes/cron/jobs/tr_cleanup_and_dlstat.php(56)

из лога медленных запросов sql_slow_tr.log

Код:

speed_up = 0, speed_down = 85907 WHERE peer_hash = 'aad77f13ca615552edd5cfce00ee1736' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:07 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, uploaded = 0, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = 'efe3a589e223cb3c63fa6f344a7e936b' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:07 | 019 | 1.3 | REPLACE INTO bb_bt_tracker (peer_hash, topic_id, user_id, ip, port, seeder, releaser, tor_type, uploaded, downloaded, remain, speed_up, speed_down, up_add, down_add, update_time) VALUES ('8d547e872a5377a577c3908b13fc76ae', 4979, 74521, 'b28d4b1a', 10048, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1326793009) |  # 1 rows | bt/includes/tr_announcer.php(148)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, uploaded = 1064960, remain = 0, up_add = up_add + 1064960, speed_up = 295, speed_down = 0 WHERE peer_hash = '3ea03790d7e33597a5ded0a02358d3b7' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '4fd4d36007e122073457e40ab8c4e009' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '517a5312e601faa030ed47864d15c21f' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '6b5525de45ed64f234458defe2065fda' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '626245aa9a51ac5a69c1b9b6af615513' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
01-17 09:37:08 | 019 | 1.3 | UPDATE bb_bt_tracker SET update_time = 1326793009, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '45aceeb4e4df6ea0c91671f54644ce32' LIMIT 1 |  # Rows matched: 1  Changed: 1  Warnings: 0 | bt/includes/tr_announcer.php(136)
 
01-17 09:43:19 | 011 | 1.6 | SELECT ip, port FROM bb_bt_tracker WHERE topic_id = 5324 ORDER BY RAND() LIMIT 50 |  # 7 rows | bt/includes/tr_announcer.php(192)
01-17 09:43:19 | 017 | 1.6 | SELECT ip, port FROM bb_bt_tracker WHERE topic_id = 5911 ORDER BY RAND() LIMIT 50 |  # 34 rows | bt/includes/tr_announcer.php(192)
01-17 09:43:19 | 017 | 1.6 | SELECT ip, port FROM bb_bt_tracker WHERE topic_id = 271 ORDER BY RAND() LIMIT 50 |  # 7 rows | bt/includes/tr_announcer.php(192)
01-17 09:43:19 | 011 | 1.6 | SELECT ip, port FROM bb_bt_tracker WHERE topic_id = 4989 ORDER BY RAND() LIMIT 50 |  # 8 rows | bt/includes/tr_announcer.php(192)
01-17 09:43:19 | 015 | 1.6 | SELECT ip, port FROM bb_bt_tracker WHERE topic_id = 5004 ORDER BY RAND() LIMIT 50 |  # 13 rows | bt/includes/tr_announcer.php(192)
01-17 09:43:19 | 016 | 1.6 | SELECT COUNT(DISTINCT topic_id) AS active_torrents FROM bb_bt_tracker WHERE user_id = 79362 AND seeder = 0 AND topic_id != 3398 GROUP BY user_id |  # 1 rows | bt/includes/tr_ratio.php(36)
01-17 09:43:19 | 013 | 1.6 | SELECT COUNT(DISTINCT topic_id) AS active_torrents FROM bb_bt_tracker WHERE user_id = 55284 AND seeder = 0 AND topic_id != 4892 GROUP BY user_id |  # 1 rows | bt/includes/tr_ratio.php(36)
01-17 09:43:19 | 015 | 1.6 | SELECT COUNT(DISTINCT topic_id) AS active_torrents FROM bb_bt_tracker WHERE user_id = 20462 AND seeder = 0 AND topic_id != 1450 GROUP BY user_id |  # 1 rows | bt/includes/tr_ratio.php(36)

из sql_error_bb.log

Код:

#001104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
 
SELECT p.post_id FROM bb_posts p LEFT JOIN bb_attachments a USING(post_id) WHERE p.post_attachment = 1 AND a.post_id IS NULL
 
Source  : includes/cron/jobs/bb_maintenance.php(197)
IP      :
Date    : 2011-08-28 22:52:07
Agent  :
Req_URI :
Referer :
Method  :
PID    : 21127
Request : Array
(
)______________________________________________________________________________
 
#001104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
 
SELECT p.post_id FROM bb_posts p LEFT JOIN bb_attachments a USING(post_id) WHERE p.post_attachment = 1 AND a.post_id IS NULL
 
Source  : includes/cron/jobs/bb_maintenance.php(197)
IP      :
Date    : 2011-08-28 22:54:07
Agent  :
Req_URI :
Referer :
Method  :
PID    : 21232
Request : Array
(
)______________________________________________________________________________
 
#001104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
 
SELECT p.post_id FROM bb_posts p LEFT JOIN bb_attachments a USING(post_id) WHERE p.post_attachment = 1 AND a.post_id IS NULL
 
Source  : includes/cron/jobs/bb_maintenance.php(197)
IP      :
Date    : 2011-08-28 22:56:04
Agent  :
Req_URI :
Referer :
Method  :
PID    : 21345
Request : Array
(
)______________________________________________________________________________

из лога sql_error_tr.log

Код:

#001317 Query execution was interrupted
 
UPDATE bb_bt_tracker SET update_time = 1326739985, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = '8884d276ad3822b0571c2d5101aa5bff' LIMIT 1
 
Source  : bt/includes/tr_announcer.php(136)
IP      : 188.226.38.70
Date    : 2012-01-16 18:53:13
Agent  : uTorrent/1830(15772)
Req_URI : /bt/announce.php?uk=ojFx0rkho1&&info_hash=%29%ff%e0n%12%1bL%e8%5c%b3%96P%bc%c2IY%3a%3c%d2%7c&peer_id=-UT1830-%9c%3d%ffu%e1_%f2%5cb%3eM%ed&port=26806&uploaded=0&downloaded=0&left=0&corrupt=0&key=01A0E10A&numwant=200&compact=1&no_peer_id=1
Referer :
Method  : GET
Request : Array
(
    [uk] => ojFx0rkho1
    [info_hash] => )яаnLи\і–PјВIY:<Т|
    [peer_id] => -UT1830-њ=яuб_т\b>Mн
    [port] => 26806
    [uploaded] => 0
    [downloaded] => 0
    [left] => 0
    [corrupt] => 0
    [key] => 01A0E10A
    [numwant] => 200
    [compact] => 1
    [no_peer_id] => 1
)______________________________________________________________________________
 
#001317 Query execution was interrupted
 
UPDATE bb_bt_tracker SET update_time = 1326739988, seeder = 1, remain = 0, speed_up = 0, speed_down = 0 WHERE peer_hash = 'a8e155bf4720d2ddd1f3516f0afdf96d' LIMIT 1
 
Source  : bt/includes/tr_announcer.php(136)
IP      : 212.45.15.3
Date    : 2012-01-16 18:53:13
Agent  : uTorrent/3000(26473)
Req_URI : /bt/announce.php?uk=lg8jLLmTVm&&info_hash=zE%10-%ca%3d%04%c6a%06%a6%00D%3c%0cH%b2V%ed%99&peer_id=-UT3000-ig2%01%e2%93%ac%a0%14%21%5b%b6&port=53233&uploaded=0&downloaded=0&left=0&corrupt=0&key=9A007424&event=started&numwant=200&compact=1&no_peer_id=1
Referer :
Method  : GET
Request : Array
(
    [uk] => lg8jLLmTVm
    [info_hash] => zE-К=Жa¦

John Smith · 17 Янв 2012

kenny написал(а):
очевидно что не правильно настроен мускул

вряд ли, да и что тут может быть неправильно настроено

Код:

[client]
#password    = your_password
port        = 3306
socket        = /var/lib/mysql/mysql.sock
default-character-set = utf8
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
max_connections = 500
port        = 3306
socket        = /var/lib/mysql/mysql.sock
tmp_table_size                  = 16M                                                         
key_buffer                          = 6M                                                     
max_allowed_packet              = 16M                                                         
table_cache                    = 256                                                         
sort_buffer_size                = 512K                                                       
net_buffer_length              = 8K                                                         
read_buffer_size                = 256K                                                       
read_rnd_buffer_size    = 512K                                                               
myisam_sort_buffer_size = 8M                                                                 
thread_cache                    = 8                                                           
query_cache_size                = 16M                                                         
connect_timeout                = 20                                                         
flush_time                          = 600                                                     
wait_timeout                    = 300                                                         
interactive-timeout    = 300                                                                 
# Try number of CPU's*2 for thread_concurrency                                               
thread_concurrency              = 8
bind-address                    = 127.0.0.1                                                     
tmpdir                              = /tmp/                                                   
innodb_buffer_pool_size        = 16M                                                         
innodb_additional_mem_pool_size = 2M                                                         
innodb_data_file_path          = ibdata1:10M:autoextend:max:1024M                           
innodb_log_file_size            = 5M                                                         
innodb_log_buffer_size          = 8M                                                         
set-variable = innodb_log_files_in_group=2                                                   
innodb_flush_log_at_trx_commit  = 1                                                           
innodb_lock_wait_timeout        = 50
default-character-set = utf8
[mysqldump]                                                                                   
quick                                                                                         
max_allowed_packet              = 16M
default-character-set = utf8
[isamchk]                                                                                     
key_buffer                          = 20M                                                     
sort_buffer_size                = 20M                                                         
read_buffer                    = 2M                                                         
write_buffer                    = 2M
 
[myisamchk]                                                                                   
key_buffer                          = 20M                                                     
sort_buffer_size                = 20M                                                         
read_buffer                    = 2M                                                         
write_buffer                    = 2M
 
[mysqlhotcopy]                                                                               
interactive-timeout
 
[mysql]
default-character-set = utf8

kenny · 17 Янв 2012

параметры железа?

John Smith · 17 Янв 2012

kenny написал(а):
параметры железа?

XEON X3440 (4x2.53 GHz), 16GB RAM

John Smith · 17 Янв 2012

nshut написал(а):
переименуйте файл announce.php на время. если нагрузка свалится, то копайте запросы аннонса, там их всего парочка, мож чуть больше

я переименовал, и нагрузка сразу свалилась. вы оказались правы. что мне нужно теперь делать? в коде копаться я не силен, не смогу ничего