MySQL Forums
Forum List  »  memcached

cpu getting hammered, but memory is fine please help
Posted by: Paul B
Date: September 11, 2007 01:47PM

We have a new website, very very busy, just gone on line, and we are using memcached to take a lot of load from the db. It works fine and most of the time, my cpu usage is at less than 10%, even at busy times,

we have extensive db access, several hits for every page, which is why we use memcache. Our problem comes when we force a cache clear to see a change straight away. with the cache empty, all the long slow queries that would normally get served from cache hit the db. And because there are a lot of them, and a lot of users on line, the cpu ( dual cpu xeo 64 bit) soon hits 100% and cannot cope with the queries. Tables are locked, queries take over 60 seconds, and of course the very same queries come in and get queued up behind so what happens is that even though the cache gets filled, more queries have already hit the db instead of the memcahce and then run slowly. I have queries taking 80 seconds, 90 seconds, and behind them, up to 400 queries waiting, with sleep as their status in process list.

Meanwhile, my memory usage does not move, sitting at 2% of my 2gb that i have.

So , my question, how can i use the memory to help out the cpu??

here are my.cnf settings,

skip-locking
skip-name-resolve
key_buffer = 512M
max_allowed_packet = 4M
table_cache = 2048
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_cache = 128
query_cache_size = 128M
max_connections = 1500
wait_timeout = 60
interactive_timeout = 60

slow log snippet

# Query_time: 132 Lock_time: 77 Rows_sent: 2 Rows_examined: 905
SELECT db1_games.*,db1_gamescores.score, db1_users.username FROM db1_games LEFT OUTER JOIN db1_gamescores ON db1_gamescores.user_id = (SELECT user_id FROM db1_gamescores WHERE score = (SELECT MAX(score) FROM db1_gamescores WHERE game_id=db1_games.id) AND db1_gamescores.game_id=db1_games.id) LEFT OUTER JOIN db1_users ON db1_users.id = db1_gamescores.user_id WHERE db1_games.id='29' AND isactive='1';
# Time: 070911 19:18:06
# User@Host:xxxx
# Query_time: 208 Lock_time: 106 Rows_sent: 5 Rows_examined: 908
SELECT db1_games.*,db1_gamescores.score, db1_users.username FROM db1_games LEFT OUTER JOIN db1_gamescores ON db1_gamescores.user_id = (SELECT user_id FROM db1_gamescores WHERE score = (SELECT MAX(score) FROM db1_gamescores WHERE game_id=db1_games.id) AND db1_gamescores.game_id=db1_games.id) LEFT OUTER JOIN db1_users ON db1_users.id = db1_gamescores.user_id WHERE db1_games.id='4' AND isactive='1';
# Time: 070911 19:18:15
# User@Host: xxxxx
# Query_time: 199 Lock_time: 88 Rows_sent: 3 Rows_examined: 906
SELECT db1_games.*,db1_gamescores.score, db1_users.username FROM db1_games LEFT OUTER JOIN db1_gamescores ON db1_gamescores.user_id = (SELECT user_id FROM db1_gamescores WHERE score = (SELECT MAX(score) FROM db1_gamescores WHERE game_id=db1_games.id) AND db1_gamescores.game_id=db1_games.id) LEFT OUTER JOIN db1_users ON db1_users.id = db1_gamescores.user_id WHERE db1_games.id='7' AND isactive='1';



What can i max out, to use the memory better and/or stop processes from backing up because i think is a cascading problem

Options: ReplyQuote


Subject
Views
Written By
Posted
cpu getting hammered, but memory is fine please help
5074
September 11, 2007 01:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.