Solved

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

Posted on 2006-06-26
7
417 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

708 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

20 Experts available now in Live!

Get 1:1 Help Now