homepage Welcome to WebmasterWorld Guest from 54.196.199.46
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL causes high cpu usage and load average
MySQL causes high cpu usage and load average
sharebay




msg:3344564
 12:41 pm on May 20, 2007 (gmt 0)

I get high load average and high cpu usage from MySql and i thing its because of this query i recenty added (mostly the str replace) i already optimized it a bit

while(list($id, $type, $title, $url, $sname, $surl, $date, $views) = mysql_fetch_row($ddl->get)) {
if(strlen($title)>52) { $title = substr($title,0,52)."..."; }
$rem = array("DVDRip", "DVDrip", "dvdrip", "DvDrip", "DVDRIP", "Dvdrip", "DvdRip", "DVDRiP", "DVD-RIP", "DVD rip", "Dvd Rip",
"DVD RIP", "(DVD Rip)", "DVD Rip", "DVDSCR", "DvdScr", "DvdSCR", "XViD", "-XViD", "XviD", "-XviD", " Xvid", " DivX", ".xVID",
".Xvid", "TELESYNC", "(TeleSync)", "[TeleSync]", " TS", "(TS)", " Tc", " TC", " -TS", " AC3", " SCR", " CAM", "iSO", "ISO",
"IsO", " Iso", " FULL", " Full", " full", "[FULL]", "-PUKKA", "-FSGN", "-UNiVERSAL", "-iTWINS", " SiNK", "iMMORTALs", "LiMiTED",
"SAPHiRE", "PROPER", "(Proper)", "SiLENTGATE", "(PC)", "(RS)", "(rs)", "[RS]", "[rs]", "[MU]", "( Version)", "[Retail Version]",
"(Retail)", "Retail", "700 MB", "700MB", " RELOADED", "-RELOADED", "New!", " FINAL", " Final", "(Full Version)", "(FULL VERSION)",
"v.", "/", "ver.", "!", "¦", "[1\]", " ()", "..", " .");
$title = str_replace($rem, "", $title);
$rem2 = array("+", " Xvid ", " XviD ", " XViD ", " Xvid ", " Ts ", " TS ", " .v", " v ", " -", "_", "%",);
$title = str_replace($rem2, " ", $title);
$find = array('v0', 'v1', 'v2', 'v3', 'v4', 'v5', 'v6', 'v7', 'v8', 'v9', 'V1', 'V2', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9', '[', ']');
$replace = array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, '(', ')');
$title = str_replace($find, $replace, $title);
$title = str_replace("&","&",$title);
$rem3 = array("-", " ");
$sname = str_replace($rem3, "", $sname);
$chars = array(" ", "#", ":");
$newtitle = str_replace($chars, "-", $title);
$title2 = $title;
$Fix = array(" ", "/", ":");
$title2 = str_replace($Fix, "+", $title2);
$today = date("");
if( $date == $today )
{
$date = "Today";
}

echo

I checked in phpmyadmin and this are values which are marked red

Slow_queries 945
The number of queries that have taken more than long_query_time seconds.

Handler_read_rnd 833 k
The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler_read_rnd_next 1,878 M
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tables 11
The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

tmp table size 33,554,432

Select_full_join 1
The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Sort_merge_passes 16
The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

sort buffer size 1,048,568

Opened_tables 355
The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

table cache 1,024

Table_locks_waited 1,757
The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Edit: i have this message on a few tables as well "PRIMARY and INDEX keys should not both be set for column `id`"

top - 02:40:07 up 4 days, 2:02, 1 user, load average: 8.10, 10.52, 10.92
Tasks: 153 total, 19 running, 126 sleeping, 0 stopped, 8 zombie
Cpu(s): 54.2% us, 33.2% sy, 0.0% ni, 12.3% id, 0.0% wa, 0.3% hi, 0.0% si
Mem: 1034672k total, 931536k used, 103136k free, 139572k buffers
Swap: 2104504k total, 2912k used, 2101592k free, 582560k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
358 mysql 7 -10 172m 92m 2352 S 19.6 9.1 28:47.10 mysqld
412 mysql 7 -10 172m 92m 2352 S 19.6 9.1 27:43.41 mysqld
13527 apache 15 0 61208 12m 4132 S 4.0 1.2 0:00.61 httpd
13747 apache 15 0 61208 12m 4164 S 3.3 1.2 0:00.10 httpd
21615 apache 16 0 70416 20m 2780 S 1.0 2.0 0:19.32 httpd
13750 apache 16 0 57616 7096 1932 S 0.7 0.7 0:00.02 httpd
13785 apache 18 0 58272 8484 2776 R 0.7 0.8 0:00.02 httpd
18641 root 16 0 57480 14m 9.9m S 0.3 1.5 10:48.69 httpd
13742 apache 15 0 57752 7120 1932 S 0.3 0.7 0:00.01 httpd
13748 apache 16 0 57616 7096 1932 S 0.3 0.7 0:00.01 httpd
13769 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>
13772 apache 18 0 58160 8080 2480 R 0.3 0.8 0:00.01 httpd
13775 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>
13778 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>
13779 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>
13780 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>
13782 apache 17 0 0 0 0 Z 0.3 0.0 0:00.01 httpd <defunct>

Name Value
Processor NameIntel(R) Pentium(R) 4 CPU 2.80GHz
Vendor IDGenuineIntel
Processor Speed (MHz)2813.567
Total Memory1034672 kB
Free Memory68712 kB
Total Swap Memory2104504 kB
Free Swap Memory2101592 kB
System Uptime4 Days, 2 Hours and 8 Minutes
Apache 1.3.37Running
DirectAdmin 1.29.7 Running
Exim 4.67Running
MySQL 5.0.37Running
Named 9.2.4 Running
ProFTPd 1.3.0a Running
sshd Running
vm-Pop3d 1.1.7f-DA-2 Running

Installed:

* phpMyAdmin 2.10.0.2
* IMAP 2004c1
* Zend optimizer
* eAccelerator 0.9.5.1

This insert has ~63000 rows
$insertSQL = "insert into search (title) values ('$searchtitle')"; mysql_query($insertSQL);

show processlist;
ID User Host Database Command Time Status SQL query
Kill 487282 db_kevin localhost db_kevin Sleep 4 --- ---
Kill 487287 db_kevin localhost db_kevin Sleep 2 --- ---
Kill 487294 db_kevin localhost None Query 0 --- SHOW PROCESSLIST

my.conf
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=128M ## 128MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

search table
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
search MyISAM 10 Dynamic 62412 30 1879980 281474976710655 900096 0 62413 2007-04-28 23:32:07 2007-05-17 04:33:10 2007-04-28 23:45:23 latin1_swedish_ci NULL

 

spander




msg:3344623
 2:24 pm on May 20, 2007 (gmt 0)

This may or may not be the answer, but I had a similar problem recently with MySql. Try turning off DNS lookups and see if that helps. It solved my problem.

sharebay




msg:3345019
 2:26 am on May 21, 2007 (gmt 0)

but then my dns dont work or

vincevincevince




msg:3345029
 2:49 am on May 21, 2007 (gmt 0)

I may be missing something but the code you posted above is PHP and not MySQL. What are the actual queries you are using?

sharebay




msg:3345052
 3:46 am on May 21, 2007 (gmt 0)

Thats at the top of the php file

<? ob_start("ob_gzhandler");?>
<?php
require "config.class.php";
require "main.class.php";
require "link.class.php";
$q = stripslashes($q);
$q = eregi_replace("\"", " ", $q);
$q = eregi_replace("\'", " ", $q);
$q = trim($q);
$ddl = new ddl();
$le = new linker();
$ddl->open();
$ddl->get($q, $types); if($_POST['q']){ $searchtitle = $_POST['q']; $Fix = array("<", ">", "#", "{", "}", "//"); $searchtitle = str_replace($Fix, "", $searchtitle); $insertSQL = "insert into search (title) values ('$searchtitle')"; mysql_query($insertSQL); }
?>

thats in config.class related to mysql
function open() {

$this->connect = @mysql_connect($this->mysql_host, $this->mysql_user, $this->mysql_pass)

or die("Site is currently under maintenance check back later...");

@mysql_select_db($this->mysql_db)

or die("Failed to connect to databases, contact <a href=\"mailto:".$this->admin_email."\">".$this->admin_email."</a>");

}

function close() {

@mysql_close($this->connect);

}

Thats in main.class
<?php
class ddl extends config {

var $get = false;
var $total = 0;

function init($q) {
global $page, $type;
$this->page = $page;
$this->page*=$this->limit;
$this->page-=$this->limit;

for ($i=0; $i<count($this->type); $i++) {
if ($type == $this->type[$i]) {
$hvahvor = "type = '".$this->type[$i]."'";
break;
} else
$hvahvor = "type!= ''";
}

if ($q) {
$exp = explode(" ",$q);
for ($i=0; $i<count($exp); $i++) {
$hvahvor .= " && title LIKE '%$exp[$i]%'";
}
}
$g_total = mysql_query("SELECT COUNT(id) AS TOTAL FROM $this->mysql_tb_dl WHERE $hvahvor");
$this->total = mysql_result($g_total,0);
$this->get = mysql_query("SELECT * FROM $this->mysql_tb_dl WHERE $hvahvor ORDER BY id DESC LIMIT $this->page, $this->limit");
}

function get($q = "",$types) {
global $page, $row, $ddl_id, $ddl_name, $ddl_sname, $ddl_surl, $ddl_date, $ddl_views, $ddl_type;
if (!$page)
$page = "1";
$this->init($q);
echo $hvahvor;
}
}
?>

vincevincevince




msg:3345077
 4:10 am on May 21, 2007 (gmt 0)

Have you got an index on the TITLE column? The LIKE matches will take a long time with a lot of rows and no index.

sharebay




msg:3345123
 6:30 am on May 21, 2007 (gmt 0)

how can i check this, i am not that good when it comes to phpmyadmin

sharebay




msg:3350410
 5:14 am on May 26, 2007 (gmt 0)

anyone?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved