[Avoidy]

Hello everyone,
I've been literally googling for the past three days and cannot find any solution to my problem:
When opening the banlist tab my mysql server spikes at 100% and takes roughly 10-20 seconds to complete the request before sourcebans loads the ban list.

I am running 1.4.11, as I am running several garrys mod servers, also I've tried with SBans 1.5.1F, same issue.

MySQL-Server:

5.6.19-1~dotdeb.1

PHP -i:

mysqli

MysqlI Support => enabled
Client API library version => mysqlnd 5.0.11-dev - 20120503

mysqlnd

mysqlnd => enabled
Version => mysqlnd 5.0.11-dev

I am running the native drivers(php5-mysqlnd), as I've had issues with php5-mysql.
Basically I am having simular issues to this and this guy, the ban list simply requires way too much CPU usage.

Does anyone have a solution to this? I've tried so many things, yet, nothing has any effect on banlist load time.

Thank you very much in advance.

[El Diablo War3Evo]

Your server is probably getting hit by web bot crawlers.

If you setup your iptables better, you would lessen the load.

I recommend you add something like this to your iptables:

# Limit HTTP Connections Per IP / Host
-A INPUT -p tcp --syn --dport 80 -m connlimit --connlimit-above 2 -j REJECT --reject-with tcp-reset
-A INPUT -p tcp --syn --dport 443 -m connlimit --connlimit-above 2 -j REJECT --reject-with tcp-reset

This should help lessen the load from web bot crawlers. There are so many bot crawlers out there, they'll sit on your sourcebans, going page by page. They'll also crawl anything else you have on your webpage server.

I found this solution to be best the best for all website setups.

Here is an example of my iptables for the webserver:

*filter

# POLICY (policy may not work on some openvz servers)
#
# DON'T FLUSH IPTABLES (iptables -F)  WITH THIS POLICY SETUP OR YOU WILL NOT
# EVER BE ABLE TO GET INTO YOUR SERVER!
#
# BEFORE YOU FLUSH, SET YOUR POLICY TO ACCEPT FOR INPUT, FORWARD, AND OUTPUT!
#
#
-P INPUT DROP
-P FORWARD DROP
-P OUTPUT ACCEPT

# Allows all loopback (lo0) traffic and drop all traffic to 127/8 that doesn't use lo0
-A INPUT -i lo -j ACCEPT
-A INPUT ! -i lo -d 127.0.0.0/8 -j REJECT

# Accepts all established inbound connections
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# Allows all outbound traffic
# You could modify this to only allow certain traffic
-A OUTPUT -j ACCEPT

# Limit HTTP Connections Per IP / Host (helps reduce webserver load)
-A INPUT -p tcp --syn --dport 80 -m connlimit --connlimit-above 2 -j REJECT --reject-with tcp-reset

# Allows STMP
-A INPUT -p tcp --dport 25 -j ACCEPT

# Allows HTTP (80) and HTTPS (443) connections from anywhere (the normal ports for websites)
-A INPUT -p tcp --dport 80 -j ACCEPT
-A INPUT -p tcp --dport 443 -j ACCEPT

# Allows SSH (usually 22) connections
# THE -dport NUMBER IS THE SAME ONE YOU SET UP IN THE SSHD_CONFIG FILE
-A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT

# MySql Server
-A INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

# Allow ping
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT

# log iptables denied calls (access via 'dmesg' command)
-A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7

# Reject all other inbound - default deny unless explicitly allowed policy:
-A INPUT -j DROP
-A FORWARD -j DROP

COMMIT

I also highly recommend you change your SSH port to something much higher up than port 22 if you haven't done that yet. Be sure to research it before you do it.

[DarkDeviL]

Originally Posted by El Diablo War3Evo I found this solution to be best the best for all website setups. Here is an example of my iptables for the webserver:

Website setups?

A few comments to that "website setup":

Originally Posted by El Diablo War3Evo -P INPUT DROP -P FORWARD DROP -P OUTPUT ACCEPT

Per default, you are dropping all incoming (INPUT) and all forwarding (FORWARD)..

But all outgoing traffic (OUTPUT) is accepted.

Originally Posted by El Diablo War3Evo # Allows all outbound traffic # You could modify this to only allow certain traffic -A OUTPUT -j ACCEPT

Since there is a default rule of the OUTPUT chain to accept all outgoing traffic, why would anyone need to tell the firewall a second time to accept outgoing traffic?

Originally Posted by El Diablo War3Evo # Allows STMP -A INPUT -p tcp --dport 25 -j ACCEPT

On your post above, you said "website setups", which makes me curious - why would you accept port 25 on the INPUT chain?

Port 25 is not used unless the server handles incoming SMTP traffic such as by being a mail server for one or some domains (via MX records).

If the server is ONLY handling outgoing emails (e.g. forums, and such), and not as a mail server (MX records) of a domain, then it does not need port 25 on the INPUT chain at all.

Originally Posted by El Diablo War3Evo # MySql Server -A INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT -A OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

With the above rules, the firewall is already being told twice that ALL outgoing traffic is acceptable.

Is there any specific reason why you would like to tell the firewall the same thing three times?

[Avoidy]

Hello there,
while I do appreciate your answers, I must admit that those did not help me whatsoever.
I am using key-based authentication with SSH and webcrawlers are NOT the cause of the MySQL load, just loading the banlist is.

Could it perhaps be the MySQL-Server-5.6 instead of the 5.5? As Sbans runs blazing fast in my VM which runs mysql-server-5.5!

Nevertheless, thank you for your answers!

[El Diablo War3Evo]

Originally Posted by Avoidy Hello there, while I do appreciate your answers, I must admit that those did not help me whatsoever. I am using key-based authentication with SSH and webcrawlers are NOT the cause of the MySQL load, just loading the banlist is. Could it perhaps be the MySQL-Server-5.6 instead of the 5.5? As Sbans runs blazing fast in my VM which runs mysql-server-5.5! Nevertheless, thank you for your answers!

Maybe you can try adding indexes to your sourceban tables to make the lookups faster.

I use mysql workbench to help make alter table easier for remote management of my mysql database.

You can just right click on the table, click on alter table, then click the indexes tab, and create indexes for sid, authid, and a few others if you want.

for more information:

http://dev.mysql.com/doc/refman/5.5/...n-indexes.html
http://dev.mysql.com/doc/refman/5.6/...n-indexes.html

You can find mysql workbench here:

http://www.mysql.com/products/workbench/


if you run on a VPS, then it might be that your VPS service has overloaded the box with other VPSes which are fighting for resources when your server goes to use mysql? The only way to prevent that is to get a new VPS, or to rent your own BOX.