Welcome to WebmasterWorld Guest from 54.166.224.46

Forum Moderators: open

Message Too Old, No Replies

100% Cpu

One query cause raising of CPU 100%

     
11:43 am on Mar 11, 2013 (gmt 0)

10+ Year Member



Actually i'm not sure what to do here. I'm using shared hosting and just received message from hoster that my MySql code, actually each query make 100% CPU. Thus if there are 3 queries then it is 300% CPU. Strange.

It is usually not problem for 26 core system but hoster start to complain that i need to do something about using a lot resources.

Code that i used is well optimized for fast performance:

$query=" SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];

echo "$ip,";


Another piece of code is almost similar:

$query=" SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'
ORDER BY r1.id ASC
LIMIT 8";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){

$ip=$row['ip'];
echo "$ip,";


Is there anything wrong with code? Any correction within code above to making not raise of CPU for 100% by each query

btw

Table is very big. There are 176.000 rows as it means anything.
Is possible that large table can be also reason for raising of CPU 100%?

Thanks for any input.
12:03 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



have you tried the EXPLAIN statement?

for example if you are using mysql...

MySQL :: MySQL 5.0 Reference Manual :: 8.2.1 Optimizing Queries with EXPLAIN:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html [dev.mysql.com]
2:23 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



I have not tried the explain statement but the main issue here
that there are 5.3 million rows (not 170.000 as i posted above).
Execution of query is under 1 second what is very fast and excellent but main problem is CPU. Anyone have idea from the code above what to do to keep same speed of execution but to cut down raise of CPU?
2:41 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



The EXPLAIN syntax is definitely what you need to be looking at and sharing (also, table format and # rows is useful... but a quick look at your query, it looks like it'd invariably be CPU intensive as you're:

1st query
- Evaluating 5.3 million records to find the unique values for r1
- Generating 5.3 million floating point random numbers with RAND()
- SELECT MAX(id) FROM visits) hopefully has an index on `id` or it will be slow

The 2nd query also has the additional REGEXP.

To start with try changing
$result=mysql_query($query);
to
$result=mysql_unbuffered_query($query);

And see if there's any improvement. See the manual page [php.net]

If the ORDER BY is not necessary try removing it.

Experiment to see whether any of your subqueries/functions are making the whole thing slower.

Sometimes it's also worth creating temporary tables to make these larger queries run more smoothly.

EXPLAIN syntax and output should provide some enlightenment.

It seems silly for a host to complain that your query is taking 100% of CPU for only a second (do you run the a lot?). Perhaps putting a small usleep(); in your while() loop will keep them happy.
3:40 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



Thank you for your reply. I've followed your advice and did changed:
$result=mysql_query($query);
to
$result=mysql_unbuffered_query($query);

I have also removed ORDER BY second query. By first query is needed to show up last 10 visits (where counter is 1) but by second query is randomly chosen and there is not needed.

Actually first code must show only last 10 visits and i have there replace old code:

$query=" SELECT * FROM visits WHERE counter='1' ORDER BY id DESC LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];


with new one (from the first post) but not sure of that was wise move?


If someone have another idea it will be more than appreciate

btw

What about mysql_unbuffered_query in future - "This extension is deprecated as of PHP 5.5.0, and will be removed in the future."?
3:47 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



[php.net...]
3:56 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



Thank you brotherhood of LAN for link to another option in future for deprecated mysql_unbuffered_query

Any idea of it was wise to change

$query=" SELECT * FROM visits WHERE counter='1' ORDER BY id DESC LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];


into


$query=" SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10";
$result=mysql_unbuffered_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];

echo "$ip,";


because first code is needed to show only last 10 visits (where counter is means = means they visited website for the first time) and nothing else.
4:00 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Options for helping you are pretty limited until you post the info asked for.

- EXPLAIN of query
- Table structure

To be honest though, if you're dealing with a large number of records on a shared host, it's reasonable that a query would take 1 second. Figure out whether it's the query itself or the while() loop that's hogging CPU.

If it's the latter the only choice really is to slow the loop down by waiting with usleep();
4:12 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



Knowledge of MySql and what to do is very limited by my side. Actually i chose always to do everything without MySql if it is possible.
I can post here table structure:


Column Type Null Default Comments
id int(11) No
page varchar(50) Yes NULL
flag char(2) Yes NULL
country varchar(50) Yes NULL
abrev char(3) Yes NULL
region varchar(50) Yes NULL
city varchar(50) Yes NULL
latitude double Yes NULL
longitude double Yes NULL
time varchar(20) Yes NULL
ip varchar(100) Yes NULL
counter int(11) Yes NULL
url varchar(100) Yes NULL


Inside MySql are inserted information about IP from visitor along with another information's (country, flag letters, latitude, langitude, region etc...).

Counter count how many time visitors have visited website.
Query from my examples above call only field ip in combination with counter (1 means to look only for visitors who have visited website one time).

Hope that you have something from this informations
4:20 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



To get the table structure:

SHOW CREATE TABLE tablename

Replace `tablename` with the name of your table.

To show the EXPLAIN info of a query just attach the word EXPLAIN to the start of the query and run the query
4:35 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



Sound easy but because of limited knowledge with MySql not sure exactly what to do here.

Table structure is already posted above if it is what is needed but with EXPLAIN in front of a query and where to execute i'm a bit lost.
4:37 pm on Mar 11, 2013 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Can you log into MySQL via the command line, or a GUI interface like phpMyAdmin?
8:35 pm on Mar 11, 2013 (gmt 0)

10+ Year Member



I have access to phpMyAdmin. You mean go there and then under SQL execute query such as

SHOW CREATE TABLE visits ?
1:06 am on Mar 12, 2013 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



yes - after you do this and report the results:

SHOW CREATE TABLE visits;


do this and report the results:

EXPLAIN SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10;


then do this and report the results:

EXPLAIN SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'
ORDER BY r1.id ASC
LIMIT 8;
8:53 am on Mar 12, 2013 (gmt 0)

10+ Year Member



For this:

SHOW CREATE TABLE visits;


i got following result:

Table Create Table
visits CREATE TABLE `visits` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`page` varchar(50) DEFAULT NULL,
`flag` char(2) DEFAULT NULL,
`country` varchar(50) DEFAULT NULL,
`abrev` char(3) DEFAULT NULL,
`region` varchar(50) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`time` varchar(20) DEFAULT NULL,
`ip` varchar(100) DEFAULT NULL,
`counter` int(11) DEFAULT NULL,
`url` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5344957 DEFAULT CHARSET=latin1



For this

EXPLAIN SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10;


i got following result:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULLNULLNULLNULL1 Using temporary
1 PRIMARY r1 range PRIMARY PRIMARY 4 NULL4588549 Using where
2 DERIVED NULLNULLNULLNULLNULLNULLNULLNo tables used
3 SUBQUERY NULLNULLNULLNULLNULLNULLNULLSelect tables optimized away




For this one:

EXPLAIN SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'
ORDER BY r1.id ASC
LIMIT 8;


i received following result:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULLNULLNULLNULL1 Using temporary
1 PRIMARY r1 range PRIMARY PRIMARY 4 NULL3008459 Using where
2 DERIVED NULLNULLNULLNULLNULLNULLNULLNo tables used
3 SUBQUERY NULLNULLNULLNULLNULLNULLNULLSelect tables optimized away
10:12 am on Mar 12, 2013 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



for starters i would try creating an index for the 'ip' and 'counter' columns.

then you might consider using the alphanumeric character class within a bracket expression in your second SELECT statement.
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp [dev.mysql.com]:

...
AND ip REGEXP '[[:alnum:]]' ...

while i haven't tested this i would assume that it's faster than "... AND ip REGEXP '[A-Za-z0-9]' ..." so it's probably worth a try.
12:28 pm on Mar 12, 2013 (gmt 0)

10+ Year Member



thank you. I've made changes to REGEXP and replaced with '[[:alnum:]]'

Regarding creating an index for the 'ip' and 'counter' columns i will need to read a bit around to find how to update existed 'ip' and 'counter' column with an index.

Is there need to change anything else except creating an index for the 'ip' and 'counter' columns?

btw

Regarding creating an index for the 'ip' and 'counter'. Is this right query that need to be exexuted within phpMyadmin for creating index:

ALTER TABLE visit ADD INDEX(ip);
ALTER TABLE visit ADD INDEX(counter);
12:46 pm on Mar 12, 2013 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



simply creating the indexes should provide significant improvement.
try it and test the results to see where you stand.

MySQL :: MySQL 5.1 Reference Manual :: 13.1.13 CREATE INDEX Syntax:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html [dev.mysql.com]
1:38 pm on Mar 12, 2013 (gmt 0)

10+ Year Member



I;ve created index through phpMyadmin. There is already option on right side to 'add index' with simple clicking on 'add index'. (i hope that it is same instead of running query to add index to columns 'ip' and 'counter')

I see directly improvement in speed. Before was under 1second and now is 2x faster.

Great and thanks again. Your input to help me was excellent.

I will monitor through Cpanel (Resources Usage) of CPU raising to 100% has been dropped.
6:40 pm on Mar 16, 2013 (gmt 0)

10+ Year Member



Sorry that i come back here again:

EXPLAIN SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10;


I have doubt about this code because code is optimized to chose rand from MySql table but i need only to chose last 10 records. I think that here is well some time lost because of (SELECT ROUND(
RAND( ) * (

I'm starter and do not know a lot. Just have doubt about this. Any idea about this and about optimized query for the last records.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month