50'000 query in an hour. This seems a bit strange, but maybe it happens, as everyone said, it would depend on your templates and how you manage the cache.
SPIP use a cache system that will store a static copy of the page, for a limited amount of time. And only recompute it after that time.
Let's say you have 10 different main templates for your page, with ~20 queries per template.
Now, if you had only 1 instance of the page generated by each template (only one section, one article, one news item, etc...) and each template has a delay of refresh of 1h, this will only be ~200 requests per hour.
Now, you probably have more than one article in the site... probably you have thousands of them, multiplying this 200 requests an hour to get to >50'000 requests per hour.
Ok, now, the question is: DO YOU REALLY NEED TO REFRESH EVERY ARTICLE PAGE EVERY HOURS?
the fact is, the article contents won't change really often, an article could probably have a refresh time very very big. Personally, I usually use 365 days, and force a refresh when I do a correction to a published article (by pressing the "visit the article" from the private area). This way, the article pages are not recomputed too often.
This will apply to all the other templates probably.
Now, some part of the templates: forums, list of articles in a section, etc... will need to be refreshed more often. Usually, these parts are a small part of the template (most of it being navigation menus, etc. etc.), so you should split the templates in multiple part, using <INCLUDE> and #INCLUDE as needed.
The part of the templates that would need a different refresh rate (i.e. the navigation menu will probably be static, with a long refresh rate; while a list of article in a section will need to be refreshed at least once a day), then use <INCLUDE> and be sure to specify different #CACHE delays in each part included.
The part of the template that will be the same for many pages (header, etc...) and don't need to have a different delay from the main page can them be included with #INCLUDE.
Another place where a load of request can come from is the search page if you use SPIP search feature. This template is often configured to have no cache (a 0s delay) as the search results might need to be updated often.
If the visitors use the search page a lot and this template is "heavy", then it can be the source of your problem. Here again, try to split the template in different <INCLUDE> with different delay, leaving only the loop doing the search in a template with a null delay. You can also cache the inside part of the search loop with a longuer delay, this won't change too much:
<BOUCLE_articles(ARTICLES) {recherche} {0,10}>
<INCLUDE{fond=search_item}{id_article}>
</BOUCLE_articles>
You should really go through all the templates and think about their individual refresh delays.
Also check from the stats of the site which part of it gets the most traffic and concentrate on optimizing that template.
Good Luck
Pierre
PS: the percolateur plugin seems great, but I think it's limited to the 1.9.3dev version. And if I understand well, it's only gone make a difference IF you have already efficient CACHE delays
kamran Mir Hazar wrote:
Hi Gilles,
I don't know I must be happy for this or no. I have *1,500 GB* and *15,000 GB* Transfer / Bandwidth. I sent also an email to host provider and received a reply that I need to make the coding of the site more static. I had a search on the net and find on the forum on mysql the following, but I am not familiar with coding so much and I don't know how to fit it.
See:
http://forums.mysql.com/read.php?35,33301,34184
What do you think?
Kamran
----- Original Message ----
From: Gilles Vincent <gilles.vincent@gmail.com>
To: kamran Mir Hazar <editor@kabulpress.org>
Cc: spip-en@rezo.net
Sent: Friday, January 18, 2008 7:11:59 AM
Subject: Re: [Spip-en] max_questions problem
Your website certainly meets a great success : you then have too much
traffic for your website-provider restrictions. You should seriously
think about changing it if you can't upgrade your service to an
unlimited bandwidth service.
.Gilles
On Jan 17, 2008 11:31 PM, kamran Mir Hazar <kamran_mirhazar@yahoo.com <mailto:kamran_mirhazar@yahoo.com>> wrote:
>
&g t;
>
> Hello,
>
> I have face MYSQL max_questions problem for the user www.kabulpress.org/my <http://www.kabulpress.org/my>
> and it is due to the max_questions setting which is set to 50,000 per user,
> per hour. I have created a new user for database and delete it, but still I
> have problem after sometime, when I'm login to the SPIP CMS and log out.
> Is there anybody who can help me in this issue?
> Regards
>
> Kamran
> _______________________________________________
> spip-en@rezo.net <mailto:spip-en@rezo.net> - http://listes.rezo.net/mailman/listinfo/spip-en
>
_______________________________________________
spip-en@rezo.net <mailto:spip-en@rezo.net> - http://listes.rezo.net/mailman/listinfo/spip-en
