High Mysql usage
i have a p4 2.4 with 1 Gb Ram with the following my.cnf:
[mysqld]
max_connections=300
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=256
key_buffer=128M
max_allowed_packet=15M
table_cache=1024
sort_buffer=2M
record_buffer=1M
join_buffer=1M
thread_cache=1028
thread_concurrency=2
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
but sometime i get a high server load 20-30 and cheking my server with Mytop seem that all the sites send a lot of query like this:
Queries: 8.8M qps: 18 Slow: 147.0 Se/In/Up/De(%): 60/03/05/02
qps now: 7 Slow qps: 0.0 Threads: 36 ( 35/ 5) 71/03/00/03
Cache Hits: 3.8M Hits/s: 7.6 Hits now: 3.4 Ratio: 70.9% Ratio now: 70.8%
Key Efficiency: 99.9% Bps in/out: 1.9k/1023.4 Now in/out: 855.3/ 6.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
482575 root localhost test 0 Query show full processlist
482599 todionli_ localhost 1 Query INSERT INTO todionli_database.phpads_adviews SET bannerid = '12', zoneid = '3', host = '', sou
482169 cuoribia_ localhost cuoribia_F 2 Query DELETE FROM yabbse_log_online WHERE (logTime < (1096901313 - 900) || identity='JOES')
482598 tusei_myf localhost tusei_foru 2 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '496f3bf59a9a6ff831a
482287 cuoribia_ localhost cuoribia_F 3 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482582 bitclan localhost bitclan_bi 3 Query SELECT * FROM phpbb_users WHERE username = '' AND user_id <> -1
482586 tusei_myf localhost tusei_foru 3 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.pos
482594 cuoribia_ localhost cuoribia_F 3 Query SELECT passBoard FROM yabbse_log_online WHERE identity='Pierpaolo' AND (1096901322-logTime)<90
482595 tusei_myf localhost tusei_foru 3 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = 'b80158966a01c4e6637
482596 tusei_myf localhost tusei_foru 3 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '6e94ddf97c570b20f4c
482226 cuoribia_ localhost cuoribia_F 6 Query DELETE FROM yabbse_log_online WHERE (logTime < (1096901312 - 900) || identity='81.74.102.42')
482486 cuoribia_ localhost cuoribia_F 6 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482515 cuoribia_ localhost cuoribia_F 6 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482578 artdust_i localhost artdust_ib 6 Query UPDATE ibf_sessions SET member_name='DragS',member_id='11',member_group='7',in_forum='1',in_to
482592 tusei_myf localhost tusei_foru 6 Query SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type,
482488 cuoribia_ localhost cuoribia_F 7 Query SELECT COUNT(*) as topiccount FROM yabbse_topics WHERE (ID_BOARD=1)
482569 forzalan_ localhost forzalan_o 7 Query SELECT * FROM bet_PredictionData where username = "paolorossi47" and matchdate<="2004-10-04 16
482585 tusei_myf localhost tusei_foru 7 Query SELECT f.*, p.post_time, p.post_username, u.username, u.user_id FROM (( tusei_forums f LEFT JO
482591 erosser_d localhost erosser_ba 8 Query UPDATE phpads_adstats SET views = views + 1 WHERE day = NOW() AND hour = HOUR(NOW()) AND banne
482366 cuoribia_ localhost cuoribia_F 10 Query SELECT passBoard FROM yabbse_log_online WHERE identity='Pierpaolo' AND (1096901315-logTime)<90
482502 ilcontra_ localhost ilcontra_d 10 Query select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o
482584 bariano_n localhost bariano_nu 10 Sleep
482589 tusei_myf localhost tusei_foru 10 Query SELECT u.user_id, u.username FROM tusei_auth_access aa, tusei_user_group ug, tusei_groups g, t
482238 cuoribia_ localhost cuoribia_F 11 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482389 cuoribia_ localhost cuoribia_F 11 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482548 cuoribia_ localhost cuoribia_F 11 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482131 cuoribia_ localhost cuoribia_F 12 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482336 cuoribia_ localhost cuoribia_F 12 Query SELECT COUNT(*) FROM yabbse_instant_messages WHERE (toName='Pierpaolo' && deletedBy!=1)
482487 tusei_myf localhost tusei_foru 12 Query SELECT m.word_id FROM tusei_search_wordmatch m, tusei_search_wordlist w WHERE w.word_text IN (
482555 tusei_myf localhost tusei_foru 12 Query INSERT INTO tusei_search_wordmatch (post_id, word_id, title_match) SELECT 73826, word_id, 0 FR
482563 tusei_myf localhost tusei_foru 12 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p
482045 cuoribia_ localhost cuoribia_F 14 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482552 tusei_myf localhost tusei_foru 27 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p
482477 tusei_myf localhost tusei_foru 77 Query INSERT INTO tusei_search_wordmatch (post_id, word_id, title_match) SELECT 73825, word_id, 0 FR
482274 tusei_myf localhost tusei_foru 165 Query SELECT word_id FROM tusei_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 25755
482158 tusei_myf localhost tusei_foru 207 Query SELECT word_id FROM tusei_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 25754
MySQL on localhost (4.0.18-standard) up 6+00:52:52 [16:48:55]
Queries: 8.8M qps: 18 Slow: 147.0 Se/In/Up/De(%): 60/03/05/02
qps now: 6 Slow qps: 0.0 Threads: 45 ( 45/ 0) 61/00/07/04
Cache Hits: 3.8M Hits/s: 7.6 Hits now: 2.6 Ratio: 70.9% Ratio now: 76.5%
Key Efficiency: 99.9% Bps in/out: 1.9k/1023.5 Now in/out: 423.8/ 6.9k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
482575 root localhost test 0 Query show full processlist
482607 bariano_n localhost bariano_nu 1 Query DELETE FROM nuke_session WHERE time < 1096899534
482608 artdust_i localhost artdust_ib 1 Query SELECT id, member_id, running_time, location FROM ibf_sessions WHERE id='857f1075650b511eab9a7
482609 tusei_myf localhost tusei_foru 1 Query UPDATE tusei_sessions SET session_time = 1096901334, session_page = 3 WHERE session_id = '0dbf
482606 tusei_myf localhost tusei_foru 2 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '496f3bf59a9a6ff831a
482604 tusei_myf localhost tusei_foru 3 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '6e94ddf97c570b20f4c
482605 tusei_myf localhost tusei_foru 3 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '47371c4677fcdeb3a94
482603 tusei_myf localhost tusei_foru 4 Query UPDATE tusei_sessions SET session_time = 1096901331, session_page = 3 WHERE session_id = '0dbf
482600 artdust_i localhost artdust_ib 8 Query SELECT id, member_id, running_time, location FROM ibf_sessions WHERE id='356d4364928fd1d33da39
482601 tusei_myf localhost tusei_foru 8 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '6e94ddf97c570b20f4c
482602 forzalan_ localhost forzalan_p 8 Query select count(*) as count from pm_members where member_id = '28749477340ed064b641a2' and userna
482599 todionli_ localhost 11 Query INSERT INTO todionli_database.phpads_adviews SET bannerid = '12', zoneid = '3', host = '', sou
482169 cuoribia_ localhost cuoribia_F 12 Query DELETE FROM yabbse_log_online WHERE (logTime < (1096901313 - 900) || identity='JOES')
482598 tusei_myf localhost tusei_foru 12 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '496f3bf59a9a6ff831a
482287 cuoribia_ localhost cuoribia_F 13 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482582 bitclan localhost bitclan_bi 13 Query SELECT * FROM phpbb_users WHERE username = '' AND user_id <> -1
482586 tusei_myf localhost tusei_foru 13 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.pos
482594 cuoribia_ localhost cuoribia_F 13 Query SELECT passBoard FROM yabbse_log_online WHERE identity='Pierpaolo' AND (1096901322-logTime)<90
482595 tusei_myf localhost tusei_foru 13 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = 'b80158966a01c4e6637
482596 tusei_myf localhost tusei_foru 13 Query SELECT u.*, s.* FROM tusei_sessions s, tusei_users u WHERE s.session_id = '6e94ddf97c570b20f4c
482226 cuoribia_ localhost cuoribia_F 16 Query DELETE FROM yabbse_log_online WHERE (logTime < (1096901312 - 900) || identity='81.74.102.42')
482486 cuoribia_ localhost cuoribia_F 16 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482515 cuoribia_ localhost cuoribia_F 16 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482578 artdust_i localhost artdust_ib 16 Query UPDATE ibf_sessions SET member_name='DragS',member_id='11',member_group='7',in_forum='1',in_to
482592 tusei_myf localhost tusei_foru 16 Query SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type,
482488 cuoribia_ localhost cuoribia_F 17 Query SELECT COUNT(*) as topiccount FROM yabbse_topics WHERE (ID_BOARD=1)
482569 forzalan_ localhost forzalan_o 17 Query SELECT * FROM bet_PredictionData where username = "paolorossi47" and matchdate<="2004-10-04 16
482585 tusei_myf localhost tusei_foru 17 Query SELECT f.*, p.post_time, p.post_username, u.username, u.user_id FROM (( tusei_forums f LEFT JO
482591 erosser_d localhost erosser_ba 18 Query UPDATE phpads_adstats SET views = views + 1 WHERE day = NOW() AND hour = HOUR(NOW()) AND banne
482366 cuoribia_ localhost cuoribia_F 20 Query SELECT passBoard FROM yabbse_log_online WHERE identity='Pierpaolo' AND (1096901315-logTime)<90
482502 ilcontra_ localhost ilcontra_d 20 Query select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o
482589 tusei_myf localhost tusei_foru 20 Query SELECT u.user_id, u.username FROM tusei_auth_access aa, tusei_user_group ug, tusei_groups g, t
482238 cuoribia_ localhost cuoribia_F 21 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482389 cuoribia_ localhost cuoribia_F 21 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482548 cuoribia_ localhost cuoribia_F 21 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482131 cuoribia_ localhost cuoribia_F 22 Query UPDATE yabbse_log_activity SET hits = hits + 1 WHERE month = 10 AND day = 4 AND year = 2004
482336 cuoribia_ localhost cuoribia_F 22 Query SELECT COUNT(*) FROM yabbse_instant_messages WHERE (toName='Pierpaolo' && deletedBy!=1)
482487 tusei_myf localhost tusei_foru 22 Query SELECT m.word_id FROM tusei_search_wordmatch m, tusei_search_wordlist w WHERE w.word_text IN (
482555 tusei_myf localhost tusei_foru 22 Query INSERT INTO tusei_search_wordmatch (post_id, word_id, title_match) SELECT 73826, word_id, 0 FR
482563 tusei_myf localhost tusei_foru 22 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p
482045 cuoribia_ localhost cuoribia_F 24 Query SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName,
482552 tusei_myf localhost tusei_foru 37 Query SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p
482477 tusei_myf localhost tusei_foru 87 Query INSERT INTO tusei_search_wordmatch (post_id, word_id, title_match) SELECT 73825, word_id, 0 FR
482274 tusei_myf localhost tusei_foru 175 Query SELECT word_id FROM tusei_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 25755
482158 tusei_myf localhost tusei_foru 217 Query SELECT word_id FROM tusei_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 25754
what can i do? i see also that i use the 90% of ram
