[Sqlgrey-users] Db Load Reduction

[Dan F. <da...@ha...>]

Hey all..

During "bounce storms" like backscatter and such, we startet getting 
timeouts between postfix and sqlgrey. After a little poking, i loosly 
concluded that sql hangs for a bit and thus sqlgrey hans for a bit.

The sql-hang occurs, afaik, when the sql-master (we run in clustering 
mode) gets hammered with inserts and updates.
Im gonna upgrade the master sql-server at some point, but i also looked 
a bit at what "write" statements that hammers the master.

Of course inserts into the connect (new connection triplet) is by far 
the top hitter when it comes to writes. But during a bounce storm, 
UPDATES to From_AWL actually makes out around 20-25% of all writes.
But its not necessary to update EVERY time, since in a bounce scenario, 
most sender adresses will be blank. (ie NULL senders) and many IPs will 
return often.

So yesterday i whipped up a way of not updating every time for the same 
record. And it turns out it made a huge difference.
From_AWL writings are now reduced by 97%

What ive done:
is_in_from_awl normally SELECT's "1" if record exists in AWL.
I now select the "last_seen" timestamp. The function now also returns 
this timestamp, instead of 1 for all "true" scenarios.
It will still return 0 on false, thus no logic needs changing elsewhere 
due to this change.

The timestamp is compared to time() to see how long ago last update was 
done. If enough time has passed, it makes the UPDATE to the table.

Right now the UPDATE delay is configured as $dflt{awl_age}(in seconds) * 
0.004167.
This makes ~6 hours, if awl_age=60 days and ~3 hours if awl_age=30 days 
and so forth.

I dont really see a downside to this approach. All From_AWL entries will 
still be updated on the first hit within the time period.  so worst case 
scenario, would be that a from_awl entry would be cleaned out 3 hours 
early. Compared to a period of 30 days, it doenst really matter.
In the log it will say: "from awl match: updating" (as before) when 
actually updating the table and when NOT updating it will simply say 
"from awl match:" (without the word "updating")


Oh yeah, and the feature can be enabled/disabled with the flag 
"db_load_reduction".

So.. This is a "request for comments" to make sure i didnt miss any 
pitfalls and such.  A patch for consideration is attached.


- Dan Faerch