CentOS
как это сделать какой коммандой? и как оценить результаты, допустим если есть нагрузка
из лога медленных запросов 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)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)#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
(
)______________________________________________________________________________#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¦вряд ли, да и что тут может быть неправильно настроено
[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я переименовал, и нагрузка сразу свалилась. вы оказались правы. что мне нужно теперь делать? в коде копаться я не силен, не смогу ничего