dtwuser Posted March 23, 2013 Share Posted March 23, 2013 Hi php friends, I'll be very thankful to hear your opinion my php problem: I have a php website with mysql database with 1 table with about 20,000 rows. My code looks like this: $mysqli = new mysqli($mysql_hostname, $mysql_user, $mysql_password, $mysql_database);if ($mysqli->connect_errno) { exit();} if ($stmt = $mysqli->prepare('SELECT title, description, content FROM '.$tableName.' WHERE slug=?')) { $stmt->bind_param("s", $slug); $stmt->execute(); $stmt->bind_result($title, $description, $content); $stmt->fetch(); $stmt->close();}$mysqli->close(); ... /* just visualize the read variable above */ <h1><?php echo $title; ?></h1><?php echo $content; ?> The problem is that in the hosting statistic, there's really huge php CPU usage, i.e. for yesterday the statistic is: PHP CPU usage is 144 min. MySQL CPU usage is 2 min. I try to find the problem for more than a week, I'll be very, very thankful for your help Thanks in advance for any reply! Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/ Share on other sites More sharing options...
Christian F. Posted March 23, 2013 Share Posted March 23, 2013 The questions is, how many hits does this page get? The CPU time without knowing how often the script is executed is rather meaningless. If the page has been accessed a 100 000 000 times, then 144 minutes is nothing. On the the other hand, if it has been accessed only 100 times, you have a serious problem. MySQL fetches the data from an internal cache, if it doesn't change, so that helps keep the CPU time low for it. Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420506 Share on other sites More sharing options...
dtwuser Posted March 23, 2013 Author Share Posted March 23, 2013 Hi Christian, thanks for your reply sorry, I forgot to mention the traffic, according to google analytics, the traffic for yesterday is 1300 page views/900 unique visitors. One interesting point, when I login in cpanel -> phpMyAdmin, the statistic is: Server: Localhost via UNIX socket Software: MySQL Software version: 5.5.28-cll - MySQL Community Server (GPL) Protocol version: 10 User: v3rscoma@localhost Server charset: UTF-8 Unicode (utf8) cpsrvd 11.34.1.7 Database client version: libmysql - 5.5.28 PHP extension: mysql I'm not sure if it's necessary, but should be mysqli included in PHP extensions? I tried to use mysql instead of mysql for 1 day in my php code, but it even increased the php cpu usage. I just can't find out what causes the problem Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420508 Share on other sites More sharing options...
dtwuser Posted March 23, 2013 Author Share Posted March 23, 2013 (edited) Here's the all php code: $slug = urldecode($_SERVER["REQUEST_URI"]); $slug = preg_replace('/\/{2,}/', '/', $slug); // remove multiple occurences of '/' from request uri if (strlen($slug) >= 1 && substr_compare($slug, '/', 0, 1) === 0){ // remove '/' from the start $slug = substr($slug, 1); } if (!strlen($slug)) { // homepage slug $slug = 'index.html'; } $mysqli = new mysqli($mysql_hostname, $mysql_user, $mysql_password, $mysql_database); if ($mysqli->connect_errno) { exit(); } if ($stmt = $mysqli->prepare('SELECT title, description, content FROM '.$tableName.' WHERE slug=?')) { $stmt->bind_param("s", $slug); $stmt->execute(); $stmt->bind_result($title, $description, $content); $stmt->fetch(); $stmt->close(); } $mysqli->close(); $pageExist = ($title != null); <!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title><?php if ($pageExist) { echo $title.' - '.$siteTitle; } else { echo 'Error 404 - '.$siteTitle; } ?></title> <?php if ($pageExist && isset($description)) { ?> <meta name="description" content="<?php echo $description; ?>" /> <?php } ?> </head> <body> <div id="wrap"> <header> <div id="logo"> <?php echo $siteTitle; ?> </div> </header> <nav> static html code here </nav> <article> <?php if ($pageExist) { ?> <h1><?php echo $title; ?></h1> <?php echo $content; ?> } else { ?> <h1>Error 404</h1> <p> </p> <p>We are sorry, but that location does not exist on this site.</p> <?php echo '['.$_SERVER["REQUEST_URI"].']'; ?> Edited March 23, 2013 by dtwuser Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420510 Share on other sites More sharing options...
mac_gyver Posted March 23, 2013 Share Posted March 23, 2013 the mysqi extension that is listed in the phpMyAdmin output is just the extension that phpMyAdmin is using to connect to the database server. in order to profile your application to find out where the problem lies, it would be ncessary to know everything your appliction is doing. nothing can be told from a few snippetts of code. Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420511 Share on other sites More sharing options...
dtwuser Posted March 23, 2013 Author Share Posted March 23, 2013 in order to profile your application to find out where the problem lies, it would be ncessary to know everything your appliction is doing. nothing can be told from a few snippetts of code. Hi Mac, I posted the all php code I use for my websites. All I do is to select from one db table and visualize the data. Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420512 Share on other sites More sharing options...
Barand Posted March 24, 2013 Share Posted March 24, 2013 Do you have an index on slug column? Quote Link to comment https://forums.phpfreaks.com/topic/276049-phpmysqli-select-strange-high-cpu-usages/#findComment-1420696 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.