Solved

MySQL Running a 99% CPU, webserver unresponsible while certain query runs.

Posted on 2006-06-26
7
431 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:atinoco
  • 2
  • 2
7 Comments
 
LVL 2

Expert Comment

by:bdsmithback
ID: 16989659
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.
0
 

Author Comment

by:atinoco
ID: 16989697
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
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 16992118
Try increasing your query cache size!
0
 

Author Comment

by:atinoco
ID: 16994957
here's my my.ini file for reference

[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
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_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
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
0
 
LVL 13

Accepted Solution

by:
John Mc Hale earned 500 total points
ID: 16999522
At 8M, the query_cache_size variable is probably a little bit small. If you can afford it, I would probably up this to 16/32M, also consider the query_cache_type and query_cache_limit variables.

If the query U refer to is frequently used, and the result set it returns is large, then changing your query cache settings may have potential benfits in your situation. Fine tuning your query cache variables is an incremental process, increasing memory allocated to the query cache until there are no further visible optimizations.

See http://www.mysql.com/news-and-events/newsletter/2003-01/a0000000108.html

Regards.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now