?
Solved

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

Posted on 2006-06-26
7
Medium Priority
?
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 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