atinoco
asked on
MySQL Running a 99% CPU, webserver unresponsible while certain query runs.
Greetings, I run a small webhosting server, I'm experincing an important problem here, eveytime one of my clients executes certain query to their database the cpu usage of the mysqld-nt.exe process jumps to 99% and all the other services (http, pop, etc) on my server freezes for a couple of minutes (while the query runs).
Is there a way to limit the resources the mysql process uses? I any ideas on how to solve this?
I use: Windows 2003 Server, plesk 7.5.6 (windows).
Thanks in Advance
Is there a way to limit the resources the mysql process uses? I any ideas on how to solve this?
I use: Windows 2003 Server, plesk 7.5.6 (windows).
Thanks in Advance
What is the query? Behavior like this is often due to a query that lacks proper indexing and takes much longer than it would otherwise. You might be able to fix this problem with a bit of optimization.
ASKER
I dont have access to the query that gets executed since i'm not executing it myself, one of my hosted clients it's running it.
He it's already working on optimizing it. But I am looking into a more permanent solution to this problem since I don't like the idea of my server becoming unavailable because my clients run big queries. There has to be a way to limit the max cpu use for mysql or do some tweaks in order to avoid this problem.
Hoping for a solution
He it's already working on optimizing it. But I am looking into a more permanent solution to this problem since I don't like the idea of my server becoming unavailable because my clients run big queries. There has to be a way to limit the max cpu use for mysql or do some tweaks in order to avoid this problem.
Hoping for a solution
Try increasing your query cache size!
ASKER
here's my my.ini file for reference
[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Data bases\\MyS QL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Data bases\\MyS QL\\Data
default-character-set=lati n1
default-storage-engine=INN ODB
query_cache_size=8M
table_cache=32
tmp_table_size=7M
thread_cache=32
myisam_max_sort_file_size= 100G
myisam_max_extra_sort_file _size=100G
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool _size=2M
innodb_flush_log_at_trx_co mmit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16 M
innodb_log_file_size=10M
innodb_thread_concurrency= 8
max_connections=300
key_buffer=4M
max_allowed_packet=1M
sort_buffer=256K
net_buffer_length=4K
old_passwords=1
[client]
port=3306
[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Data
datadir=C:\\Program Files\\SWsoft\\Plesk\\Data
default-character-set=lati
default-storage-engine=INN
query_cache_size=8M
table_cache=32
tmp_table_size=7M
thread_cache=32
myisam_max_sort_file_size=
myisam_max_extra_sort_file
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool
innodb_flush_log_at_trx_co
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16
innodb_log_file_size=10M
innodb_thread_concurrency=
max_connections=300
key_buffer=4M
max_allowed_packet=1M
sort_buffer=256K
net_buffer_length=4K
old_passwords=1
[client]
port=3306
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.