Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Transaction Number 19 49
Help with Data Warehouse / Data Marts 4 41
Errror when importing data from Oracle to SQL 6 67
EF5: Update Model from Database not working 3 80
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…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

734 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