Excessive mysql usage with no users, due to spambots?

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Excessive mysql usage with no users, due to spambots?

Post by thundermonkey »

Support Request Template
What version of phpBB are you using? phpBB 3.0.12
What is your board's URL? http://vtownhall.org/forum
Who do you host your board with? Arvixe
How did you install your board? I used a tool provided by my host
What is the most recent action performed on your board? Fresh Install
Is registration required to reproduce this issue? No
Do you have any MODs installed? Yes
What MODs do you have installed? jQuery Pack for phpBB
phpBB Ajax Like
Advance Block Mod
Groups on Registration and Custom Profile Fields
What styles do you currently have installed? subsilver2
What language(s) is your board currently using? English
Which database type/version are you using? MySQL 5
What is your level of experience? New to PHP and phpBB
When did your problem begin? Within last week.
Please describe your problem.

I am building a forum, currently I am the only person using it. Despite this, I have been warned by my host that I was using excessive mysql resources. I would welcome any input on troubleshooting this.

I have added the mods listed above, but I don't see the users in the forums associated with them remarking on mysql resource usage. Some of my problems seem to be from spambots, but there may be self-inflicted issues as I have added some code of my own, and I am a php/mysql noob.

1. How can I identify what mysql resources I am using excessively?

My host was not forthcoming about what exactly the resource problem was. After a few queries, they sent me the log snippet appended at the bottom. Looking at this support board, most mysql resource problems are due to excessive connections. Sure enough, there are 30 incoming connections from spambots, using 6 IP addresses. Are connections the only mysql resource problem indicated in the snippet?

2. How can I best monitor connections?

Running this sql query does it: show status like "thread%". Are there better ways?

3. What are reasonable results for connections?

I get the following:
Threadpool_idle_threads 0
Threadpool_threads 0
Threads_cached 132
Threads_connected 67
Threads_created 199
Threads_running 6
These numbers seem high since there is only me and spambots (unsuccessfully trying to register) using the forum.

4. How can I close connections?

"Purge all sessions" in the UCP didn't impact the connections, and running mysql_close() didn't work.

Spambots: after I created the forum, I immediately began getting fake registrations, and so I installed Advance Block Mod and added a Q&A. However, they are still knocking on the door as you can see from the below snippet.

5. How can I control the spambot contribution to the mysql resource problem?

And finally:

6. Are there indications from the log snippet that code I have added is acting badly?

I assume so, because if spambots are the only problem, that suggests that having 30 (fake) users at one time constitutes excessive resource use with my host. That is a small number of users. I am spending ~$27 a month on a shared server. Is 20 the number of users at one time I can expect to be able to have without generating excessive resource complaints?

Many thanks in advance!

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

From technical support:

Code: Select all

    ---Pass 1---
    tbmitche 5777 3.8 0.0 384140 27728 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5959 3.4 0.0 384404 27776 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5700 2.9 0.0 384144 27736 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5768 2.8 0.0 384408 27780 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5542 2.0 0.0 385204 28776 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6098 8.3 0.0 384400 27776 ? SN 22:51 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6155 26.0 0.0 384404 27780 ? SN 22:52 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    The above processes were found to be running for user tbmitche when this was executed by the Arvixe staff member during each pass at a 2 second interval Sat Sep 13 22:52:01 PDT 2014 ...

    ---Pass 2---
    tbmitche 5777 3.0 0.0 384140 27728 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5959 2.6 0.0 384404 27776 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5700 2.4 0.0 384144 27736 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5768 2.3 0.0 384408 27780 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6098 5.0 0.0 384400 27776 ? SN 22:51 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6155 8.6 0.0 384404 27780 ? SN 22:52 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    The above processes were found to be running for user tbmitche when this was executed by the Arvixe staff member during each pass at a 2 second interval Sat Sep 13 22:52:04 PDT 2014 ...

    ---Pass 3---
    tbmitche 5777 2.2 0.0 384140 27728 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5959 2.0 0.0 384404 27776 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5768 1.7 0.0 384408 27780 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6098 3.1 0.0 384400 27776 ? SN 22:51 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6155 4.3 0.0 384404 27780 ? SN 22:52 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    The above processes were found to be running for user tbmitche when this was executed by the Arvixe staff member during each pass at a 2 second interval Sat Sep 13 22:52:06 PDT 2014 ...

    ---Pass 4---
    tbmitche 5777 2.0 0.0 384140 27728 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 5959 1.7 0.0 384404 27776 ? SN 22:51 0:00 | \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6098 2.5 0.0 384400 27776 ? SN 22:51 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    tbmitche 6155 3.2 0.0 384404 27780 ? SN 22:52 0:00 \_ /opt/ntphp/php52/bin/php /home/tbmitche/public_html/vtownhall.org/forum/ucp.php


    The above processes were found to be running for user tbmitche when this was executed by the Arvixe staff member during each pass at a 2 second interval Sat Sep 13 22:52:08 PDT 2014 ...

    MySQL Processes:
    Warning: Using unique option prefix verb instead of verbose is deprecated and will be removed in a future release. Please use the full name instead.
    | 18520742| tbmitche_phpb947| localhost| tbmitche_phpb947| Sleep| 12||| 0| 0| 0|
    | 18520763| tbmitche_phpb947| localhost| tbmitche_phpb947| Sleep| 10||| 0| 0| 0|
    The above queries were found to be running for user tbmitche when this was executed by the Arvixe staff member at Sat Sep 13 22:52:11 PDT 2014 ...


    Incoming Connections:
    118.97.95.182 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    120.203.214.182 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/feed.php?f=72
    172.246.113.218 vtownhall.leadhat.com

    /forum/feed.php?f=72
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=login
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=login
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=login
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    172.246.113.218 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=login
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    198.56.164.114 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    221.176.14.80 vtownhall.leadhat.com

    /forum/ucp.php?mode=register
    94.23.147.196 vtownhall.leadhat.com

    /forum/ucp.php?mode=login
    ====================

    I would suggest you to make sure that you are doing the following steps periodically.

    1. Change all account related password to strongest one.
    2. Do not store passwords on browser.
    3. Scan your PC for malicious scripts using antivirus program.
    4. Optimize website code/database queries.
    5. Make sure that you are always installing applications/themes/plugins etc from authorized resources.
    6. Update any application installed to the latest stable version.
    7. Remove unwanted scripts/plugins/addon installed.
    8. Upgrade scripts/plugins/addon installed to the latest stable version.
Generated by SRT Generator
Last edited by thundermonkey on Mon Sep 15, 2014 12:06 am, edited 1 time in total.

User avatar
Lumpy Burgertushie
Registered User
Posts: 67064
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Excessive mysql usage with no users, spambots?

Post by Lumpy Burgertushie »

what is this extra code you mentioned?

also, your registration question is too simple for the spambots to solve.

the question about the state serves no anti spam purpose except that the spam bot will have to choose one.


when you are on a shared server. the total number of connections allowed applies to the total of all users of that shared server, not just you.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Re: Excessive mysql usage with no users, spambots?

Post by thundermonkey »

Robert, thanks for the input.

The state question is not for anti-spam purposes.

Yes, the registration question is simple. However, when I was using Active Block Mod plus reCaptcha I found that a few bots would make it through the registration process. Switching to that very simple Q&A stopped that trickle of spam users, so I thought I was done.

However, is the constant stream of attempted spam registrations acting as a pseudo-DDOS attack that is hogging my mysql resources? If so, would switching to a different antispam task, or some other re-configuration help?

Regarding my added code, yes that could be the problem (rather than spambots). I would be happy to describe what I have done if that would be useful. Some of it may raise red flags to an experienced php/msql programmer, which I am definitely not.

What would really help me is some functionality (analogous to a CPU or memory usage log) which works to monitor mysql resource use. Arvixe, my hoster, was not able to suggest anything, or any approach. Does anybody have any suggestions?

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Excessive mysql usage with no users, spambots?

Post by Oyabun1 »

For any phpBB anti-spam plugin to work the user has to visit the site. It can't stop someone who is outside the gate.

Try uninstalling or disabling the jQuery Pack for phpBB and phpBB Ajax Like MODs and see if that resolves the problem. Some MODs, particularly those which have not been validated, put a high load on the database.

You can gain more information about queries by enabling debug mode on the board.

Open config.php

Find

Code: Select all

// @define('DEBUG', true);
// @define('DEBUG_EXTRA', true);   
Replace with

Code: Select all

@define('DEBUG', true);
@define('DEBUG_EXTRA', true); 
Use a proper text editor to make the edit, such as one of those suggested here, Tools needed to set up and customise phpBB. The encoding to save files in is UTF8 without BOM.

Then another line will appear in the footer of the board and clicking on the Explain link will show an SQL report on the queries run.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Re: Excessive mysql usage with no users, due to spambots?

Post by thundermonkey »

Oyabun1, thanks for the info about the debugging mode, it was exactly what I needed.

The good news is that my installed mod, phpBB Ajax Like, plus my cloned version of it which added a second Like capability, are behaving as expected.

The bad news is that they increase the number of queries by a substantial amount. A topic page with 3 posts needed 46 queries, at least 23 of which were due to my two mods. This resource drain could be reduced by limiting the like capability to, for example, the first post in a topic.

Update - scratch the below - my hoster, after we've had 1 chat sessions and 10 emails, just saw fit to send me a statement about resource usage which indicates that a query is not considered an instance, rather a page load would be. So the mystery continues.

The worse news is that now I think I understand why I triggered my resource usage warning. My hoster's notice states: "MySQL Usage: 0.0-1.0 is Moderate, 2.0 is High, higher than 2.5s is Unacceptable. This is the average number of Mysql instances you were running at all specific seconds of that day."

If "usage" equals "query", this means that with my business class shared hosting at ~1$ a day, I get a budget of 1 query per second, and that one page can be viewed no more than twice a minute. So by working on the forum and refreshing pages a lot, accompanied by my entourage of spam and search engine bots, I singlehandedly exhausted the mysql resource budget of the forum.

Can this be right? Is it normal to get only about 1,000 queries per penny paid to the host?

User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Re: Excessive mysql usage with no users, due to spambots?

Post by thundermonkey »

OK, here is the updated situation:

I have a phpbb forum I am building, with no usage so far. When I look at the logs and who is online, there is only me doing admin activities, and a few spambots showing up as guests trying to register (and being blocked by Advanced Block Mod).

I have a shared business class hosting plan, the stated resource limit from the hoster is 200,000 executions (e.g. page views) and 2,000,000 MySQL queries per day.

I was notified that I was exceeding the resource cap by a factor of 2, for the entire preceeding week. The usage rate was at a very stable level, no spikes.

I turned on debug mode, and all of the pages show only expected numbers of queries going out (8-42 per page).

The hoster has been very unhelpful about providing me with data so I can troubleshoot where these queries are coming from. They also are not helpful about what I can do in phpbb, they simply said remove all mods, update scripts, check my computer for malware, etc. They did not appear to know about debug mode.

So my questions are:

1. Is there anything else I can do as a phpbb user to see where this mysql usage is coming from?
2. What could be causing it? Can there be processes I've spawned that are out of control?
3. Could I expect better support from another hoster, like the ones recommended by this board?

Thanks in advance!

User avatar
Lumpy Burgertushie
Registered User
Posts: 67064
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Excessive mysql usage with no users, due to spambots?

Post by Lumpy Burgertushie »

sounds to me like your hoster is either completely ignorant or is full of BS or is just trying to get you to upgrade your account. I would ask for a refund if possible since you haven't even been able to get your board up and running. but with or without the refund, I would be looking for a better host.

I have never heard of a host that uses those types of numbers for account limits etc.


keep in mind that the hosting companies advertised here are not "recommended" by phpbb.com, they are just advertisers.

do your research as always.

check out http://webhostingtalk.com for doing research.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Excessive mysql usage with no users, due to spambots?

Post by Oyabun1 »

thundermonkey wrote:I turned on debug mode, and all of the pages show only expected numbers of queries going out (8-42 per page).
I don't think a standard board has more than 22 queries for any page load.

This isn't a problem we see with the standard software, so it is likely to be from an incorrectly installed MOD or a MOD itself.

I'm not familiar with the AJAX Like MOD but I've seen similar MODs in the past that were definite resource hogs constantly quering the database.

For testing purposes disable the original board or remove it from the server. Set up a new clean install with no MODs and see what the resource use is then. That will at least give you a baseline for the standard software on your server.

(As an anti-spam on a test board I set up the included Q&A CAPTCHA where the answer doesn't match the question, for example Q: What is the capital of France? A: lamb chop. You can still register test accounts because you know the answer, but I have never had a spambot or even a human spammer get in.)
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Re: Excessive mysql usage with no users, due to spambots?

Post by thundermonkey »

Robert, thanks for the recommendation. I am in the process of migrating to a different hoster, which got pretty good reviews at the site you linked to. Judging from the person setting up the new account, they are more knowledgable about forums and also are more willing to work with customers about resource problems.

Oyabun1, your suggestions are good ones. However, the number of queries I got is what I expected, given the functionality of the two mods I installed. Yes, comparatively they make the forum a resource hog, but the functions they serve (allowing people to like and to vote up a post) are essential to the new forum.

In terms of queries, they at most double the amount. So my working alone on the forum had a MySQL resource impact of two users working on a vanilla install. Hardly likely to cause a problem, so it remains a mystery.

I am migrating the phpBB install entact to the new hoster, so if the problem was real, it should show up again. I will have more ability to troubleshoot, however (I am moving from a shared to VPS environment), and the hoster will be more able to help. I'll post here again if I learn anything interesting about the issue.

cleverwise
Registered User
Posts: 37
Joined: Fri Feb 28, 2014 10:55 pm
Location: Atlanta, GA
Name: Jeremy

Re: Excessive mysql usage with no users, due to spambots?

Post by cleverwise »

If you are moving to a VPS environment I would look into turning on Opcache. That will greatly reduced server resources and make phpBB execute faster. As for MySQL query numbers there can be a lot in modern applications.

That is why it is good to run as few mods as you need and not over do it. However to help tweak MySQL query cache to avoid common queries having to do table reads. You know I have never looked by it would be useful for phpBB to have a memcache or Aerospike extension.

I don't know your VPS specifications but for Opcache I would say 64 to 128MB and MySQL query cache of around 32MB. If you have the RAM.
Respectfully,
Jeremy

DevOPs - Linux Admin, PHP Developer, Oracle DBA OCP Training
Display phpBB statistics on WordPress (Read more...)

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Excessive mysql usage with no users, due to spambots?

Post by Oyabun1 »

Other than the phpBB cache I'm not aware that server side caching has not shown any benefit for small boards (<75k posts) and the benefit or otherwise of bytecode caching seems totally unrelated to OP's issue.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

cleverwise
Registered User
Posts: 37
Joined: Fri Feb 28, 2014 10:55 pm
Location: Atlanta, GA
Name: Jeremy

Re: Excessive mysql usage with no users, due to spambots?

Post by cleverwise »

Oyabun1 wrote:Other than the phpBB cache I',m not aware that server side caching has not shown any benefit for small boards (<75k posts) and the benefit or otherwise of bytecode caching seems totally unrelated to OP's issue.
Well caching can assist even with smaller boards. For starters it reduces I/O load. You can configure caching engines to not read a file until the next web server restart or only read a file if changed since last cache. It can save upgrading plans as fast and decrease load time a bit. In today's world seconds can count for some visitors.

I realize this isn't directly related to the thread. However just offering some friendly advice. Obviously it is up to the poster (or anyone) if they want to deploy this technique.
Respectfully,
Jeremy

DevOPs - Linux Admin, PHP Developer, Oracle DBA OCP Training
Display phpBB statistics on WordPress (Read more...)

User avatar
thundermonkey
Registered User
Posts: 18
Joined: Thu Sep 11, 2014 4:08 am

Re: Excessive mysql usage with no users, due to spambots?

Post by thundermonkey »

Jeremy, thanks for the caching suggestion. I may try it down the road.

I migrated to a much more helpful and phpbb-knowledgeable host, Ethernet Servers, and they have been great so far. The bots followed, and continued their leaching of resources, mainly CPU and bandwidth. The leaching was enough that I was nearing the limits of my old $20/month shared account, and I was not doing anything to attract it - the forum was not in use, and no bots were successfully registering.

So I tried the tarbaby given on this StopForumSpam post here. And I immediately saw a big decrease in the leaching, although I can't rule out that the bots backed down of their own account at roughly the same time.

Ethernet Servers subsequently suggested, and implemented (since it is a managed account), a blacklist using the StopForumSpam list. However this didn't reduce bot activity below the improvement I got after the tarbaby - I still see bots, but now they are not yet on the SFS list.

So if you are getting significant resource leaching from blocked bots, try out the tarbaby. There is a third iteration tarbaby later in the SFS topic, I used the second iteration posted by John Darkhorse.

I am curious as to whether others will see the big improvement I got from using one.

Locked

Return to “[3.0.x] Support Forum”