Icy Phoenix

Old Support Topics - Excesive Consume Of SQL Recurses

Zuker [ Fri 11 Aug, 2006 17:09 ]
Post subject: Excesive Consume Of SQL Recurses

My Hosting wrote:

your account has been suspended for excesive consume of sql recurses

+--------+----------------+-----------+---------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------------+-----------+---------------+---------+------+-------+------------------+
| 291848 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5864 | | |
| 291849 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5864 | | |
| 292018 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5660 | | |
| 292019 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5660 | | |
| 292274 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5459 | | |
| 292275 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5458 | | |
| 292602 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5256 | | |
| 292603 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5255 | | |
| 292861 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5052 | | |
| 292867 | mh000086_zuker | localhost | mh000086_foro | Sleep | 5051 | | |
| 295347 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1791 | | |
| 295348 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1791 | | |
| 295354 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1782 | | |
| 295355 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1782 | | |
| 295460 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1589 | | |
| 295572 | mh000086_zuker | localhost | mh000086_foro | Sleep | 1388 | | |
| 297879 | root | localhost | | Query | 0 | | show processlist |
+--------+----------------+-----------+---------------+---------+------+-------+------------------+

Does anyone got an idea of what can i do/tell to my hosting?
The Kuka told me in phpb-es that if i change some settings in the acp, maibe i'll "fix" this problem, consuming less resources from de server. But i can't acces to my acp, 've got only acces to my ddbb with phpmyadmin.

Thanks
Zuker

difus [ Fri 11 Aug, 2006 17:25 ]
Post subject: Re: Excesive Consume Of SQL Recurses

may be you have more databases?

change the hosting

Zuker [ Fri 11 Aug, 2006 17:27 ]
Post subject: Re: Excesive Consume Of SQL Recurses

The easy fix is to chage my hosting.
But i think that maibe are other ways of fixing it

Zuker [ Fri 11 Aug, 2006 17:28 ]
Post subject: Re: Excesive Consume Of SQL Recurses

i've got two ddbb, but the problem is in the one i've got my xs:S

Tom [ Fri 11 Aug, 2006 19:44 ]
Post subject: Re: Excesive Consume Of SQL Recurses

MG posted an article on XS.com a while ago on how to reduce database load. However, in order to set it up, you need access to the ACP.

Let's see if I can put it back together:

ACP -> Configuration -> Database

Disable Logins Recording: Yes
Database Optimize: No (can be done manually, if needed)
Site History: No (Extended statistics that should be disabled)
Caching of the board's Configuration: Yes

ACP -> Configuration -> XS

Protect Main Admin Account: No
Deactivate Force Topic Read: Yes

Hakkinen [ Fri 11 Aug, 2006 20:58 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Do what Tom has posted.

Other thing...the portal is what cause overloading, deactivate, who's on line and statistics from the portal, just leave what is necessary... I did that and no problem so far.

Tom [ Fri 11 Aug, 2006 21:47 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Or get another provider without those stupid restrictions...

Hakkinen [ Fri 11 Aug, 2006 22:43 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Tom wrote: [View Post]
Or get another provider without those stupid restrictions...

is easy, bus the $$$$ is the problem sometimes

All hosting companies have "stupid restrictions" the difference that some of that companies are not so bad likeZuker has

Zuker [ Sat 12 Aug, 2006 00:18 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Thanks Tom, i've just finish discussing with my provider and finally give me my web back.
i've change what you told me before.
If i noticed any changes i'll post them here

Mighty Gorgon [ Sat 12 Aug, 2006 11:26 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Zuker wrote: [View Post]
Does anyone got an idea of what can i do/tell to my hosting?
The Kuka told me in phpb-es that if i change some settings in the acp, maibe i'll "fix" this problem, consuming less resources from de server. But i can't acces to my acp, 've got only acces to my ddbb with phpmyadmin.

Thanks
Zuker

Do what Tom suggested you... and then disable anything you don't need in CONFIG section:

Shoutbox on Index and in Who Is Online
Statistics on Index
Birthdays on Index
Links on Index
Disable Thanks Topic
Disable Topics Ratings
Disable Topics Notifications
Disable Access to pages you're not using (this could save you enormous resources)
Disable Statistics page
In Portal use only the blocks you really need and CACHE all blocks that could be CACHED... such as text only blocks or rarely updated blocks.

I'll try to write another How To with all these suggestions.

Remember that most of XS recursive SQL are cached, so XS should be optimized to use a minimum amount of extra SQL... but of course there are so many features... you should explore ACP and try to turn off all that could be turned off.

Zuker [ Sun 13 Aug, 2006 02:47 ]
Post subject: Re: Excesive Consume Of SQL Recurses

Thanks again
anywhere i am studyng moving from my actual server