Solved

Windows MySql Low CPU Usage

Posted on 2011-02-18
9
980 Views
Last Modified: 2012-05-11
MySql only uses about 4%[Max, usually 2%] of my CPU to process a VERY LARGE cron job.

System/Software Specs:
Quad-Core Intel Core i7 920
6GB DDR3-1600 Ram
4x 320GB hard drives in Raid 0
Windows 7 Ultimate 64-bit
Apache Version : 2.2.11  
PHP Version: 5.3.0  
MySQL Version :5.1.36  

The Task:
Cron Job on a MySql DB consisting about about 250,000 rows.
I use PHP PDO for database access.
Each row will be ranked in several catagories.
For speed purposes, I generate all rankings in mysql using @variables
Example code attached.

I assume that mysql is possibly hitting some kind of memory limit? So my beast of a computer isn't being feed enough information for the task to get done faster? I just think it could take a lot less than 30 minutes in I could get more CPU usage.

Also, when I'm running this cron job, I can't open up any other pages on my site. Why would that be and how do I fix that?  [EDIT: I can navigate PHP MyAdmin all I want though]

Thanks for any help!!

function get_ranking($rank_parameters, $rank_order, $DBH, $test_mode=false) {

	$test_mode == true ? $limter = ' LIMIT 50' : $limter = '';
	
	$query0 = 'SET @rank=0';
	$query1 = 'SELECT @rank:=@rank+1 AS rank, id, rowName, '.$rank_parameters.' FROM tableName ORDER BY '.$rank_order.$limter;
	
	echo $test_mode == true ? $query0.'<br>' : '';
	echo $test_mode == true ? $query1.'<br>' : '';
	
	try {
	
		$DBH->exec($query0); 
		$STH = $DBH->prepare($query1);
		$STH->setFetchMode(PDO::FETCH_ASSOC);
		$STH->execute();
		
		while ($user = $STH->fetch()) {
			$ranks[] = $user;
		}

		echo '<br>get_ranking:: parameters::'.$rank_parameters.' - rank_order::'.$rank_order;
		echo $test_mode == true ? print_r($ranks) : '';
		return !empty($ranks) ? $ranks : false;
		
	} catch (PDOException $e) {
		save_PDO_error_to_file($e);
		return false;
	}	
}

function update_ranking($new_ranks, $config, $db, $DBH) {
		
	foreach ($new_ranks as $key => $value) {
		
		//$db, $id, $col, $val, $DBH
		if (!update_stat_col_val($db, $value['id'], $config['db_rank_field'], $value['rank'], $DBH)) {
			echo ' failed: '.$value['id'].' ';
		}
	}
	
	echo '<br>update_ranking:: '.$config['db_rank_field'].' ';
	return true; 
}

Open in new window

0
Comment
Question by:PiZzL3
  • 5
  • 3
9 Comments
 
LVL 20

Accepted Solution

by:
Muhammad Wasif earned 500 total points
ID: 34929422
Your cron is not using much resources of MySQL. For effective utilization of MySQL, you need to run multiple instances of this.

Checkout this thread
http://www.experts-exchange.com/Database/MySQL/Q_26803616.html
0
 
LVL 17

Expert Comment

by:psimation
ID: 34929450
try to set priority higher for the task in Task Manager and see if that helps? (right click on task in task manager). Also check to make sure the affinity is set for multiple cpu's.
0
 

Assisted Solution

by:PiZzL3
PiZzL3 earned 0 total points
ID: 34929764
@wasifq

You're absolutely right about the ineffective usage of mysql. I'll go ahead and add php to that too.

I ran raw rank queries in PHP MyAdmin and it only took 0.5 seconds to rank everyone.

I believe that the slowest part of my code is row updating. It loops through the result array and updates each row individually.

What would be perfect is to query the rank and update at the same time/same sql statement.
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34931628
@PiZzL3

Does your UPDATE query using index?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:PiZzL3
ID: 34931651
I really don't know. I'm not very knowledgable with SQL. I do know that each row is updated with a separate query. Looping through 250,000 updates several times over is a large task.
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34931661
Post your UPDATE query and the output of the following query

SHOW CREATE TABLE table_name;

Open in new window

Replace table_name with the table name you have in database.
0
 

Author Comment

by:PiZzL3
ID: 34933346
I figured out how to cut the slower PHP update loop out of it. Now it only takes a couple seconds at most to update each ranking.

SET @rank=0;
UPDATE dbTable SET rankfd= @rank:=@rank+1 ORDER BY fd2 DESC, fd3 DESC;

Open in new window


I''ve attached an image of what you requested.
showcreate.gif
0
 

Author Comment

by:PiZzL3
ID: 34933762
My CPU uses an average of 12% now (during the cron job). It also updates the ranks of all 29 categories in less than 1 minute.
0
 

Author Closing Comment

by:PiZzL3
ID: 34959211
Thanks for the help!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

896 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

12 Experts available now in Live!

Get 1:1 Help Now