MySQL Forums
Forum List  »  Performance

Re: High cpu load problem
Posted by: Erin ONeill
Date: July 19, 2005 10:01AM

OOOOh right away I see you have some problems with some of your settings. The most important are KEY_BUFFER and the QUERY_CACHE_SIZE -- although there are others that help improve speed.

You have 4 GB of ram and your key_buffer = 8 M?? Your key_buffer should be approaching 512M-1GB! So I'd change that one. Then Like Jay says, you need your query_buffer_size set (mine's 128m and I'd like to push it higher)

My situation is a LOT of short connections (bullentin board/forums type thing). I found a great increase in speed with setting up my table_cache and my thread_cache. The table_cache is limited by your OS (I managed to crash the mysql server by setting table_cache too large, it's currently 1240. This made a HUGE difference in speed. When it was set to 2400 the db screamed until it burned! :)). Thread_cache has been helpful.

Then I noticed that your thread buffers seem awfully small. You've got 4 gb you might want to set the sort_buffers to 4m, read_buffers & join_buffer to 2m to start. Your myiasm_sort_buffer is really large at 2gb. I'm not sure about that setting? I've got mine set to 64m (perhaps I need to raise mine?? anyone??).

The important thing to remember is that the buffers are calculated:
global buffers + (thread buffers * connections) = RAM (be sure to leave some ram for your OS).

Global buffers: key_buffer, innodb_buffer_pool, innodb_log_buffer,innodb_additional_mem_pool, net_buffer
Thread_buffers: sort_buffer, myisam_sort_buffer, read_buffer, join_buffer, read_rnd_buffer

Am I forgetting any buffers??? anyone?

Also your wait_timeout is set REALLY high. I have mine at 20 but then I have a lot of short bursts of connections and it's recommended to set this low under that sort of situation. I'm also NOT using pconnect. All the reading I've done on pconnect (persistent connections) vs connect have made me lean towards connect. I'm still reading up on this one.

Really setting just the key_buffer, query_cache_size, table_cache, thread_cache, wait_timeout can improve the speed dramatically. Turn on slow_query_log and slog thru that to find which queries need tweaking.... That'll help quite a bit too!

erin

Options: ReplyQuote


Subject
Views
Written By
Posted
25906
July 18, 2005 03:00AM
9466
July 18, 2005 05:16AM
9057
July 18, 2005 04:46PM
9552
July 19, 2005 12:49AM
9220
July 19, 2005 04:04AM
7843
July 19, 2005 06:35AM
8209
July 19, 2005 06:22PM
Re: High cpu load problem
10480
July 19, 2005 10:01AM
6616
July 25, 2005 03:15AM
7246
July 18, 2005 05:50PM
6162
July 19, 2005 12:59AM


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.