Solved

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

696 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