PLEASE HELP :-(

[arcademint.com]

Hello,

today i got a mail from my host.. saying/

 

please give me  a fix / quick fix..

 

PLEASE !! ANYONE PLEASE !

======================================================

Hi,

 

Your account has been using an excessive amount of CPU Resources and Memory from our server above the allowed limit, please try to optimize your entire website from php to mysql database.

Failure to reduce CPU Consumption from your account will result in suspension and you'll be asked to move to another host.

Also you can view our semi-dedicated packages which allow much heavier CPU Usage on websites: www.cirtexhosting.com/semidedicated.shtml to be hosted on separate servers.

 

Let us know what optimization you have done or if you are willing to move to semidedicated packages in which we'll migrate your account over no problem.

 

Thank you

 

root@kitty [~]# mysqladmin processlist|grep arcatcom

| 60123 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 4 | Opening tables | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60126 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 4 | Sending data | SELECT * FROM info |

| 60131 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Opening tables | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60133 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM games WHERE cat='Optical Illusions' AND id!='21113' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60132 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE cat='Puzzle' AND id!='20209' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60143 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM games WHERE cat='Videos' AND id!='21567' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60144 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | Opening tables | SELECT * FROM games WHERE cat='3D' AND id!='21750' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60146 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE cat='Arcade' AND id!='20223' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60147 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM games WHERE cat='Videos' AND id!='20345' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60148 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE cat='Sports' AND id!='20233' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60149 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | Copying to tmp table | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60150 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE cat='Videos' AND id!='20043' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60152 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM info |

| 60156 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Sending data | SELECT * FROM games WHERE 1=1 AND `active`='Yes' ORDER BY `rating` DESC LIMIT 0, 20 |

| 60165 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 5 | Copying to tmp table | SELECT * FROM games WHERE cat='Adventure' AND id!='20938' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60187 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE cat='Videos' AND id!='21825' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60197 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Opening tables | SELECT * FROM sessions |

| 60201 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | SELECT * FROM games WHERE 1=1 AND `active`='Yes' ORDER BY `time` DESC |

| 60202 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM games WHERE `cat` = 'Videos' AND `active`='Yes' ORDER BY `time` DESC |

| 60207 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 6 | logging slow query | DELETE FROM sessions WHERE ip='204.8.198.2' OR time<1175720186 |

| 60208 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | logging slow query | SELECT * FROM games WHERE `cat` = 'Arcade' AND `active`='Yes' ORDER BY `time` DESC |

| 60215 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | Opening tables | SELECT * FROM games WHERE `cat` = '3D' AND `active`='Yes' ORDER BY `time` DESC |

| 60216 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Sleep | 2 | | |

root@kitty [~]# mysqladmin processlist|grep arcatcom

| 60123 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 7 | closing tables | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60126 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | closing tables | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60131 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Opening tables | SELECT * FROM games WHERE 1=1 AND `active`='Yes' ORDER BY `name` ASC LIMIT 0, 1500 |

| 60133 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM members WHERE active='Yes' ORDER BY `username` ASC |

| 60132 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | removing tmp table | SELECT * FROM games WHERE cat='Shooting' AND id!='20176' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60143 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE cat='3D' AND id!='22017' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60144 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE cat='News' AND id!='20691' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60146 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE cat='Puzzle' AND id!='21064' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60147 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Opening tables | SELECT * FROM games WHERE cat='News' AND id!='21445' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60148 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | Opening tables | SELECT * FROM games WHERE cat='Adventure' AND id!='21466' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60149 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 5 | Sending data | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60150 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE cat='3D' AND id!='22027' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60152 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM members WHERE active='Yes' ORDER BY `totalgames` DESC LIMIT 0, 2 |

| 60156 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE 1=1 AND `active`='Yes' ORDER BY `name` ASC LIMIT 0, 1500 |

| 60165 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE cat='Videos' AND id!='21382' ORDER BY RAND() DESC LIMIT 0, 5 |

| 60187 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 0 | Opening tables | SELECT * from sessions, members WHERE sessions.userid=members.id ORDER BY members.username |

| 60197 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 0 | Writing to net | SELECT * FROM games WHERE `cat` = 'Puzzle' AND `active`='Yes' ORDER BY `time` DESC |

| 60201 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 1 | Opening tables | SELECT * FROM games WHERE `cat` = '3D' AND `active`='Yes' ORDER BY `time` DESC |

| 60202 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE `cat` = '3D' AND `active`='Yes' ORDER BY `time` DESC |

| 60207 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | closing tables | INSERT INTO `sessions` ( `id` , `ip` , `time` , `userid` )

| 60208 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games WHERE `cat` = 'Puzzle' AND `active`='Yes' ORDER BY `time` DESC |

| 60215 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 5 | closing tables | SELECT * FROM games WHERE `cat` = '3D' AND `active`='Yes' ORDER BY `time` DESC |

| 60216 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 1 | Opening tables | DELETE FROM sessions WHERE ip='204.8.198.2' OR time<1175720196 |

| 60221 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 3 | | SELECT * FROM games_config |

| 60223 | arcatcom_tharsha | localhost | arcatcom_gamezadb | Query | 2 | | SELECT * FROM games_config |

--------------------------------------------------------

 

 

 

[btherl]

Do you have indexes for your common queries?

[fenway]

Well, I see a lot of != and ORDER BY RAND().

[gluck]

Stop using select *. Get only the values which are required. Make sure when you do a select on a fields they contain indexed fields. LIMIT doesn't help: The full query is executed anyway and then the resultset is formatted.

[fenway]

Stop using select *. Get only the values which are required.

This usually isn't such a problem, unless you're just pulling back a single column or you have a really large number of non-fixed width columns.

 

LIMIT doesn't help: The full query is executed anyway and then the resultset is formatted.

Not exactly, since the server can do quite a few optimizations with ORDER BY and LIMIT, but not in this particular case.

[gluck]

Stop using select *. Get only the values which are required.

This usually isn't such a problem, unless you're just pulling back a single column or you have a really large number of non-fixed width columns.

 

You shouldn't be fetching columns you are not going to need. Why would you make the Database fetch additional columns, send it over the network, and finally let clog your scripting language resources for something you don't need.

Another thing is the way database generates a query plan to apply optimization depends upon the query fired: I believe select * in comparison to select fieldname should generate different plans depending on db setup.

 

 

LIMIT doesn't help: The full query is executed anyway and then the resultset is formatted.

Not exactly, since the server can do quite a few optimizations with ORDER BY and LIMIT, but not in this particular case.

LIMIT just saves network traffic overhead and returns the client with a limited resultset. Behind the scenes though the DB has to scan the whole table and rearrange the full dataset irrespective of the limit clause.