I have seen that when executing a mysql query the cpu usage becomes 100 % and it takes a long time to execute the query. I have executed the same query thorugh phpmyadmin and that time also it took a long time to execute also mysql consumes 100 % cpu . I have done some change to my.cnf file and restarted mysql and the problem wentaway. You can also adjust your my.cnf values as follows.
[mysqld]
max_allowed_packet = 128M
sort_buffer_size = 512M
max_connections=500
query_cache_size = 512M
query_cache_limit = 512M
query-cache-type = 2
table_cache = 80
thread_cache_size=8
key_buffer_size = 512M
read_buffer_size=64M
read_rnd_buffer_size=64M
myisam_sort_buffer_size=64M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=700M
innodb_additional_mem_pool_size=20M
Once you made the changes in my.cnf do not forget to restart mysql server. Mysql can be restarted in any of the following methods
If you have installed mysql using RPM you can restart mysql using one of the following commands
service mysqld restart OR /etc/init.d/mysqld restart
If you have installed mysql from source files you can shutdown mysql and start it as follows
Stop Mysql : mysqladmin -uroot -p shutdown
Start msyql : /usr/local/mysql/bin/mysqld_safe –user=mysql & (assume mysql is installed at /usr/local/mysql)




December 29th, 2009
Aneesh
Posted in 

web hosting…
[...]MySQL takes 100 % CPU | MySQL :: High cpu load problem | MySQL Performance: MySQL => cpu load 100% | 100 % cpu usage by mysql | Hello System Admin | Where system admins share technical article and tips | Create free Blog | Updates on domains, web…
WATCH TIME IDENTICAS …
[...]MySQL takes 100 % CPU | MySQL :: High cpu load problem | MySQL Performance: MySQL => cpu load 100% | 100 % cpu usage by mysql | Hello System Admin | Where system admins share technical article and tips | Create free Blog | Updates on domains, web…
Never use this script. My server went unresponsive as soon as I changed my.cnf as instructed above. Needed manual reboot to get back in…
NEVER TRY IT.
It depends on the server