Urgent: Connecting problem to database !

[hartiberlin]

Hi All,
I have a problem with my
SMF 1.1 RC3 forum at
overunity.com


My users and me  get now very often the error message:


: User 'hartiberlin1' has exceeded the 'max_questions' resource (current value: 70000)
/xxxxxxxxxxx/home/sub3456/sdddddddd/overunity/Sources/Load.php
1962


WHat could this be ?

Are there too many accesses to the MySQL database ?

Could it be, that one user of my forum is
trying with a tool to download the whole
forum and this way makes too many access with such
an utillity program like httrack ?

What can I do against this ?

How can I see, which user makes the most traffic on my forum ?
( so I could ban him, if he really downloads the whole forum ...)

Please let me know.

Many thanks.

Regards, Stefan.

[hartiberlin]

Hmm,
where is a statistic in the admin panel of the forum,
where I can see, which user did the most hits to the forum in the last hour ?

It seems,one user is using an automatic download software to
download the whole forum and is dragging down the whole
forum this way, as my hoster only allows 70.000 queries / hour
to the database.

Is this normal ?

How can I find the user who is doing havoc ?

Is there no statistic at all in SMF 1.1RC3 to see ,
where the most traffic did come from which IP address ?

Many thanks,
it is pretty urgent.

[Christian A. Herrnboeck]

Hello,


Limiting queries in that way isn't normal.

Regarding the mass-download, that may very well be true, as your forum shouldn't be needing 70,000 queries. You can check who had the most hits in Awstats (It's a Statistics software you should have access to from your hosting control panel). Usually, you'll find one IP that has used 200 or 300 MBs of bandwidth in one day - that would be the trouble-maker


Best Regards,
Christian A. Herrnboeck

[sammie99]

you also get that error if you have installed something like flashchat and the full bot files, it takes a lot to install them and that can overload the DB per hour limit, also you could be the target of a botnet attack thats a DOS attack thats the easiest why for someone to take your site down if it is on a hosting plan, attack the DB bye bye website for upto an hour,

they limit the db usage so no one user on a shared server can take all the power and slow down the others 16-32 people hosted on that server.

as posted here "Awstats (It's a Statistics software you should have access to from your hosting control panel)" that is one way to find out, but if it is a botnet DOS it will used 100's of pc that have a trojen backdoor installed on them, and they will be used for the attack so it wont show one pc with a lot of usage it will show a lot of them

[Christian A. Herrnboeck]

sammie: A botnet, or DDoS attack will cause a high amount of guests to be shown on the board. He also doesn't seem to use FlashChat

-Christian

[sammie99]

sammie: A botnet, or DDoS attack will cause a high amount of guests to be shown on the board. He also doesn't seem to use FlashChat

-Christian

i just used flashchat as an example, because that is what happend to me when i installed flashchat with all the bots,
as to looking at the stats, i just pointed out that it might not be a member, so no point just looking for one person if it could be something else to look for that is cause the issue.

and sorry to say but "Limiting queries in that way isn't normal." it is very normal practice for shared hosting on high volume host providers. to limit the use of a DB in that way

[Ben_S]

and sorry to say but "Limiting queries in that way isn't normal." it is very normal practice for shared hosting on high volume host providers. to limit the use of a DB in that way

It's generally normal for huge oversellers who basically dangle a carrot in your face with massive disk space and bandwidth allocations but then have to cripple it in other ways because there is no way they could deliver otherwise. Decent hosts however generally don't do this.

[sammie99]

and sorry to say but "Limiting queries in that way isn't normal." it is very normal practice for shared hosting on high volume host providers. to limit the use of a DB in that way

It's generally normal for huge oversellers who basically dangle a carrot in your face with massive disk space and bandwidth allocations but then have to cripple it in other ways because there is no way they could deliver otherwise. Decent hosts however generally don't do this.

like i said, "high volume host providers" thats the mistake i made but i wont when i have used up whats left of this 12 months, i'll look for someone better, and wont mind paying a little more for a good package either.

[sammie99]

hartiberlin i found your problem i think, and you are not going to like it, the problem user is yourself lmfao

you have 250 Recent Posts listed on your main page, with each one needing 4 database queries that means every time someone hits your front page the database queries for the 250 Recent Posts alone comes to 800 database queries.

then you have 70 forums on that page, each one needing 3-5 database queries that comes to 280.

so each visitor to the main page makes 1080 database queries that mean if you have about 68 people visit or search bot, you hit your max limit.
with almost 4,000 members i think thats is your problem, you have to get rid of the waste.

how to fix it? have 10 Recent Posts listed, not 250 lmfao cut the forums to 50% 70 is not needed, you can cut them down.
remember after each person makes a post or read a thread they return to the main page to look at the next forum they want to read, so they are calling 1080 database queries every time.

if not then pay $100 a month for a dedicated server.

your site is "The international free energy research forum" and you waste more energy than the uk does, just by the way you have the site set up. lol

hope that input helps

Sammie

[Ben_S]

The number of boards doesn't affect the number of queries, likewise for the recent posts.

If SMF made 1080 queries displaying any page, it would be the most inefficient forum software I've seen in a long while.

E.g. look at the forum here, I see 33 boards on the index and 5 recent posts, Page created in 2.807 seconds with 9 queries.

[Christian A. Herrnboeck]

I'd like to say something, sammie, but I'm going to refrain from it.

If you have never done any Database / PHP programing (which, obviously, you haven't), don't answer people's problems with jibberish...

SMF really isn't *THAT* kind of hog on resources, and very simply, the problems he's having are due to the host being an overseller (no overselling = no MySQL limit = no page lockouts).

Regards,
Christian A. Herrnboeck

[Joshua Dickerson]

Just a note: if you don't know the answer it is better to wait for someone else to give the answer than to provide a false answer.

[hartiberlin]

Many thanks for all the answers,
so it is no problem to have displayed the last 200 recent posts on the front page
or should I cut it down ?

Where can I see, how many queries it will do to the database ?

Is it true, that if I have my own server, I can do as many queries to
a MySQL database as I want ?

Who is restricting the queries ?

In what INI file is it set ?

Maybe I can ask my hosting company to upgrade this to me ?

Unfortunately I am on a special clustered hoster and they don´t have AWstats
package installed, cause it is a Clustered server system and they have written their
own stats package , but which only a low possibility solution and it does not show any
traffic from single IP numbers, otherwise I could catch the guy , who probably tried to
downloaded the whole forum and made so much traffic...

Any other solution ?

Is there some kind of plugin or Mod for SMF, which
tracks the made traffic of a user ?

Many thanks for all your quick answers.

Regards, Stefan.

[Christian A. Herrnboeck]

Hello Stefan,


I see you're a bit confused, let me try and clarify:

so it is no problem to have displayed the last 200 recent posts on the front page
or should I cut it down ?

It makes absolutely no difference if you show 2, 200, or 2,000 recent posts. It does one query to get them, and the only difference is the LIMIT statement.

For instance, if I'd like to return 20 posts, I might do this:

SELECT*FROM `smf_topics` WHERE (logic for the latest time) LIMIT 0, 20;
If I'd like 2,000 posts, I'd do similarily:
SELECT*FROM `smf_topics` WHERE (logic for the latest time) LIMIT 0, 2000;

(And no, this isn't a real database query, but it shows how the difference between 20 and 2000 is made)

Is it true, that if I have my own server, I can do as many queries to
a MySQL database as I want ?

If you have a dedicated server? Yes. However, your site is not nearly large enough to warrant a dedicated server. You just need good, shared hosting from a host that doesn't oversell (Only oversellers limit queries).

Who is restricting the queries ?

Short answer: Your hosting provider. They do this to limit people from using their full resources, so that the servers are somewhat stable. It's a bad practice, as both Ben and I have stated here

In what INI file is it set ?

It's usually set in my.cnf, a MySQL configuration file. Only the server administrator has access to that.

Maybe I can ask my hosting company to upgrade this to me ?

You can, but I doubt they'll listen to you

Is there some kind of plugin or Mod for SMF, which
tracks the made traffic of a user ?

Not to my knowledge - this is something that has to be done by analysing server logs (httpd logs). As far as I know, just because the servers are clustered, you should still be able to use Awstats, then again, the only cluster I work with is a  H-sphere cluster


Best Regards,
Christian A. Herrnboeck

PS: Sprechen Sie Deutsch?

[青山 素子]

Is it true, that if I have my own server, I can do as many queries to
a MySQL database as I want ?

If you have a dedicated server? Yes. However, your site is not nearly large enough to warrant a dedicated server. You just need good, shared hosting from a host that doesn't oversell (Only oversellers limit queries).

Oversellers aren't the only ones who will limit queries, but they are the only ones who place such a small limit on that. (And 70,000 is rather low. I would consider 250,000 the bare minimum.) Also, good hosts will be happy to raise the limit if you are in need of more and aren't causing problems.

Who is restricting the queries ?

Short answer: Your hosting provider. They do this to limit people from using their full resources, so that the servers are somewhat stable. It's a bad practice, as both Ben and I have stated here

I disagree on it being a bad practice in total. It is good if used appropiately, but can be used poorly. In this case, it is likely being used to cover up that they are lacking the capacity you are paying for.

Maybe I can ask my hosting company to upgrade this to me ?

You can, but I doubt they'll listen to you

Worth a try, but with that severe of a limitation, they are likely not going to change things for you. Putting more of a load on their system might expose their practices.

Is there some kind of plugin or Mod for SMF, which
tracks the made traffic of a user ?

Not to my knowledge - this is something that has to be done by analysing server logs (httpd logs). As far as I know, just because the servers are clustered, you should still be able to use Awstats, then again, the only cluster I work with is a  H-sphere cluster

I'm surprised that they can't offer standard logs. If they can't manage that, I doubt their capability to troubleshoot issues. Perhaps they can't do a single log because of the way their cluster is configured, but there are still ways to create a log in the normal method that almost all statistics packages use. (As an example, you can log to a cental database and run a scheduled process to generate standard log files from that.)

[hartiberlin]

Hi All,
well, I still have this problem.

My Hosting Servive Provider said, I have 70.000 queries PER HOUR,
not per day !
But they will not increase this.

I mostly have 20 to 50 users and guests online at maximum.

So I wonder, what is going on...

As you said, that my display of the most recent 100 postings
on my front page does not increase the queries,
I wonder, if it could be also any search machine crawler like
the Google crawler, which crawls always my forum and makes this huge traffic ?
How could I tell it, just only to crawl parts of the whole forum ?

Unfortunately my hosting provider only has a very basic Statistic package
installed, where I can only see, how much traffic all in all was made and
which pages did get the most hits...
So I can not install AWStats, because also it is a clustered servefarm,
so only their stats package does work..

Hmm, the traffic did not increase during the last days, due to this statistic.

So what could be the problem for these many queries which are now
around 70.000 per hour ?

if around 50 persons surf on the forum in a normal way, does this generate
so many queries per hour ?

I have SMF 1.1 RC3 and Tinyportal 0.96 installed and display the last recent
100 topics on my front page and on the forum Info Center box.

Please help.
Many thanks.

Regards, Stefan.

[青山 素子]

That is a heck of a lot of queries. Depending on how active the users are it might go high, but even going with an optimistic 50 users, that works out to 1,400 queries per user per hour. With a high 5 queries per page (should really be no more than 2), that works out to each of those users visiting 280 pages each.

Either your traffic is much higher than you wrote, or something is seriously wrong.

[hartiberlin]

Hi,
my normal normal traffic is about 5 to 10 Gbytes per days,
but this is normal inclusive file downloads.

This also did not change much during the last days...
Only this annoying 70.000 queries per hour limit problem is now occuring..

Could this be, that someone is downloading the whole forum again
or Google spider is crawling the whole forum and makes all this traffic ?

Many thanks.

Regards, Stefan.

[青山 素子]

It could be a lot of things (I find Yahoo's bot particularly aggressive when crawling sites).

The real problem here is your host limiting you like that on queries. There is no reason to enforce such a small limit unless they are overloading their servers.

[Christian A. Herrnboeck]

Hi,
my normal normal traffic is about 5 to 10 Gbytes per days,
but this is normal inclusive file downloads.

This also did not change much during the last days...
Only this annoying 70.000 queries per hour limit problem is now occuring..

Could this be, that someone is downloading the whole forum again
or Google spider is crawling the whole forum and makes all this traffic ?

Many thanks.

Regards, Stefan.

Stefan,
70,000 queries is quite a bit, but shouldn't cause your site to be locked down. If you're not using more traffic than usual, then I don't think there is a problem with your site. However, the fact that you are using 7.5GB of bandwidth in one day, that's about 225GB of bandwidth in one month. That simply cannot run on a shared host, unless the host is overselling (in which case, you'd be experiencing the issues you are now).

I'd recommend that you upgrade to a VPS, or, if your budget allows, a dedicated server.

You can find more information in the Hosts & Hosting board.


Best Regards,
Christian A. Herrnboeck