Arbitrary/private DNS Blacklist performance problems

[ShadowM]

On our machine we've setup an arbitrary/private/supplematary DNS blacklist,

we've written a mass-add page in php that adds/delete ip's in a MySQL DB first, then via Cron the ip's are sent to DA via the CMD_DNS_API

just the add of a bunch of IP's ( like ~100 it takes AGES to get them added in DA, in contrast the add to the DB is done in an few seconds)

-- example statusmail from cron script ----
Stats for the Blacklist cronjob

Time of start : 8:00:03 CEST
Time of End : 8:07:36 CEST
Entry's added: 26
Entry's deleted: 0

Total Entry's in database: 6653

This mail is automatically generated.

-- End Example statusmail ----


any suggestions ?

We already had to limit adds by checking other blacklists so if the ip's we add are already present then they are skipped (so it really is a supplementary list)

at this time ( and we're running it now for a month ) it isn't even possible to get the zone data in DA itself due to the amount of reccords.
And it's growing every day.




A little inside info :

the dns zone is setup in DA, config is taken from this Tutorial : http://www.kloth.net/internet/dnsbl-howto.php

After that we wrote a small php-script to check for validity and add the ip's in the correct (reversed) way.
A secondary script is made to get the ip's added from DB and pump them into DA

 

[nobaloney]

Seven and a half minutes may seem like ages, but it really isn't .

That said, to add 26 entries, it's probably too long.

Can you set up your cron job to send you a timestamp after everything it does? Perhaps write the timestamps to a file and then send you the file when its done.

That way you can see where the time delay is.

If you add your blocklist at the end of the exim.conf blocklists then it doesn't matter if you filter out duplicates first, because the emails found in others won't make it to your check but will be rejected earlier.

Jeff

 

[ShadowM]

If you add your blocklist at the end of the exim.conf blocklists......

Jeff

It isn't used by exim, it's used by an irc-server to keep out annoying bots

For (email-)spam we've got other measures in place, and they suffice at the moment, the blacklist that was setup is purely for kepping the irc-server safe from bot-attacks.

And an addition of 1 ip takes 29 secs :

Stats for the Blacklist cronjob

Time of start : 16:00:01 CEST
Time of End : 16:00:30 CEST
Entry's added: 1
Entry's deleted: 0

Total Entry's in database: 8264

This mail is automatically generated.

code of the php-file that's run from cron :

<?php
$start = date('G:i:s T');

//Needed class
require("httpsocket.php");
//Needed configfile (for database)
require('include.inc.php');

//start socket connection
$sock = new HTTPSocket;  
	
//Login to DA
$sock->connect('***************',2222);  
$sock->set_login('blacklist','***********');  
$sock->set_method('POST');  
	
//make a db connection
db_connect();

$add_query = mysql_query("SELECT * FROM proxy_transaction WHERE proxy_in_rbl ='1' LIMIT 0, 750 ") or die ("invalid Query". mysql_error());
$add_count = mysql_num_rows($add_query);
while($add_data = mysql_fetch_assoc($add_query)) 
{
	$sock->query('/CMD_API_DNS_CONTROL',  
	array(  
	        'domain' => 'blacklist.**********',  
	        'action' => 'add',  
	        'type' => 'A',
		'name' => $add_data['proxy_ip'],  
		'value' => '127.0.0.10'
	    ));  
		
	$result = chop($sock->fetch_body());  
	if($result == "error=0&text=record added&details=") 
	{
		mysql_query("INSERT INTO proxy_full_table (proxy_id,proxy_ip,proxy_add_date) VALUES ('','".$add_data['proxy_ip']."',NOW())") or die ("invalid Query". mysql_error());

	   mysql_query("DELETE FROM proxy_transaction WHERE proxy_id = '".$add_data['proxy_id']."'") or die ("invalid Query". mysql_error());;
		} else {
	
		}
	}
	
	
	$del_query = mysql_query("SELECT * FROM proxy_transaction WHERE proxy_out_rbl ='1' LIMIT 0, 50 ") or die ("invalid Query". mysql_error());
	$del_count = mysql_num_rows($del_query);
	
	while($del_data = mysql_fetch_assoc($del_query)) {
		$sock->query('/CMD_API_DNS_CONTROL',  
	    array(  
	        'domain' => 'blacklist.*********',  
	        'action' => 'select',  
	        'arecs0' => 'name='.$del_data['proxy_ip'].'&value=127.0.0.10'
	    ));  
		
		$result = chop($sock->fetch_body());  
		if($result == "error=0&text=Records Deleted&details=") {
		   mysql_query("DELETE FROM proxy_full_table WHERE proxy_id = '".$del_data['proxy_id']."'") or die ("invalid Query". mysql_error());
		   mysql_query("DELETE FROM proxy_transaction WHERE proxy_id = '".$del_data['proxy_id']."'") or die ("invalid Query". mysql_error());
		} else {
			
		}
	}
	
	$total_query = mysql_query("SELECT * FROM proxy_full_table");
	$total_count = mysql_num_rows($total_query);
	
	//email settings
	$email_receiver = "***************";
	$email_sender   = "rbl@***********";
	$email_subject  = "RBL Cronjob Stats";
	
	// email body
	$email_content  = "<b>Stats for the Blacklist cronjob</b><br />";
	$email_content .= "<br />";
	$email_content .= "Time of start : ".$start."<br />";
	$email_content .= "Time of End   : ".date('G:i:s T')."<br /> ";
	$email_content .= "Entry's added: ".$add_count."<br />";
	$email_content .= "Entry's deleted: ".$del_count."<br />";
	$email_content .= "<br />";
	$email_content .= "Total Entry's in database: ".$total_count."<br />";
	$email_content .= "<br />";
	$email_content .= "This mail is automatically generated.";
	$email_content .= "<br />";
	$email_content .= "<br />";
	// create mailheader
	$mailheader  = "MIME-Version: 1.0\r\n";
	$mailheader .= "Content-type: text/html; charset=iso-8859-1\r\n";
	$mailheader .= "From: $email_sender\n";
	$mailheader .= "Reply-To:$email_sender\n";
	//create the stats mail
	mail($email_receiver,$email_subject,$email_content,$mailheader);
	
}
?>

As you can see there's no additional checking done inside the cron-job php-add routine, all checking is done in the DB-add.
So why is it taking so long ?


last time we've added a list we came across ( ~1200 ip's) and as you can see the cron is already limited to 750 ip's at one time

here's the 'result' :

Stats for the Blacklist cronjob

Time of start : 20:24:47 CEST
Time of End : 1:48:28 CEST
Entry's added: 750
Entry's deleted: 0

Total Entry's in database: 8249

This mail is automatically generated.

for that reason in i've disabled the cron to let this run finish ( it usually runs every 8 hours)

not a big performance hit eh ?

 

[nobaloney]

I suppose I could easily check out of this and ask others to respond, since now I know it has nothing to do with code I've written .

And since I'm not a php programmer, perhaps that would be my best code of action.

However, I reiterate, can you write your code to send you (or save to a file) timestamps at various places in the code so you can see where the problem exists?

Jeff

 

[ShadowM]

However, I reiterate, can you write your code to send you (or save to a file) timestamps at various places in the code so you can see where the problem exists?

Jeff

As you might have seen in the code itself ( as posted) at the beginning of the script it fills the $start variable with the time of start of the script

In the mailsend routine, the same is done directly ( not filling a var 1st), in so creating an end-time that is displayed in the mail generated

also the whole script ( as given in previous post) taked 29secs to add a single ip (as shown in status-mail)

In diagnosing the problem i've watched the server and it's behaviour ( ssh connection, watching server-processes via top)

When the job is run either from cron or directly (on demand) started via commandline the process that hits (full cpu-load) is directadmin itself.

the script itself is long processed it's the DA process itself that seems to be having problems with handling the load.

Seccond thing, we can't even load the dns-zone in DA anymore, it just times out as if DA can't handle the amount of reccords/data in the zone itself ?

 

[nobaloney]

I don't know of any limits.

Perhaps you should ask DirectAdmin support to help you figure out the issue.

Jeff