homepage Welcome to WebmasterWorld Guest from 54.196.57.4
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
100% Cpu
One query cause raising of CPU 100%
dolcevita




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

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.

 

phranque




msg:4553401
 12:03 pm on Mar 11, 2013 (gmt 0)

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]

dolcevita




msg:4553421
 2:23 pm on Mar 11, 2013 (gmt 0)

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?

brotherhood of LAN




msg:4553432
 2:41 pm on Mar 11, 2013 (gmt 0)

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.

dolcevita




msg:4553461
 3:40 pm on Mar 11, 2013 (gmt 0)

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."?

brotherhood of LAN




msg:4553463
 3:47 pm on Mar 11, 2013 (gmt 0)

[php.net...]

dolcevita




msg:4553474
 3:56 pm on Mar 11, 2013 (gmt 0)

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.

brotherhood of LAN




msg:4553477
 4:00 pm on Mar 11, 2013 (gmt 0)

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();

dolcevita




msg:4553486
 4:12 pm on Mar 11, 2013 (gmt 0)

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

brotherhood of LAN




msg:4553488
 4:20 pm on Mar 11, 2013 (gmt 0)

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

dolcevita




msg:4553499
 4:35 pm on Mar 11, 2013 (gmt 0)

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.

brotherhood of LAN




msg:4553500
 4:37 pm on Mar 11, 2013 (gmt 0)

Can you log into MySQL via the command line, or a GUI interface like phpMyAdmin?

dolcevita




msg:4553627
 8:35 pm on Mar 11, 2013 (gmt 0)

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

SHOW CREATE TABLE visits ?

phranque




msg:4553704
 1:06 am on Mar 12, 2013 (gmt 0)

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;

dolcevita




msg:4553808
 8:53 am on Mar 12, 2013 (gmt 0)

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

phranque




msg:4553857
 10:12 am on Mar 12, 2013 (gmt 0)

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.

dolcevita




msg:4553897
 12:28 pm on Mar 12, 2013 (gmt 0)

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);

phranque




msg:4553903
 12:46 pm on Mar 12, 2013 (gmt 0)

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]

dolcevita




msg:4553915
 1:38 pm on Mar 12, 2013 (gmt 0)

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.

dolcevita




msg:4555684
 6:40 pm on Mar 16, 2013 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved