Re: [Sqlgrey-users] [SPAM] Re: Patches for SQLgrey

[Lionel B. <lio...@bo...>]

Dan Faerch a écrit, le 08/18/2009 12:16 AM :
>> So far I fixed some issues with IPv6 handling - namely fixed
>> 'classc'/'smart' trimming of IPv6 addresses and added support for v6
>> addrs for clients_ip_whitelist[.local].
>>   
>>     
> Great.. I forgot about that one..
>   
>> I bet there must be some other work as well that popped up in the two
>> years since 1.7.6 release. Anyone?
>>   
>>     
> Ive got 3 things i want to fix:
>
> There is a bug ill be hunting this week. Its an undocumented feature for 
> when running multiple servers against one database server. I needed that 
> only 1 specific server would do the cleanup,

Why again (I don't remember the reason) ? Reading the following it seems
this feature brings some problems.

>  so i added (i think in 
> 1.7.5) the possibillity to add a file called "dont_db_clean" in the 
> sqlgrey config directory. Ive recently noticed that somehow i run into a 
> deadlock where nobody cleans the db, but the "last_clean" timestamp 
> still gets updated. resultning in nothing getting cleaned.
> The result is a very, very large connect table and from AWL. Of course 
> this means that theres something wrong, if an sqlgrey updates the 
> timestamp when its not cleaning.
>
> Due to this, i also found a couple of minor performance boosters i want 
> to implement. I dont remember of the top of my head, where excactly, but 
> it has something to do with the SELECT in connect table and/or the 
> from_awl not using WHERE "timestmp < max_age" 

I'm not sure what you are referring to : they check the last_seen timestamp.

> (pseudo), making it search 
> all records instead of just limiting to the onced within the max_age 
> window. It can make a small difference to people with long cleanup 
> intervals, and in my case a huge difference when cleanup failed for a 
> week without me noticing ;).
>   

I've no explanation for the performance difference. The last_seen of awl
tables and first_seen of connect are even indexed to speed up the queries.

> Also i want to add a "cleanup limiter", that is, the maximum number of 
> elements to cleanup in one run. Sometimes, when running very large 
> cleanup operations, mysql will simply hang with the table locked. (ive 
> expirenced this on several mysql 4 versions- none of my mysql's are v.5 
> for sqlgrey.).

That's risky. If you set it too low, the cleanups won't be able to keep
with the new entries. I think we should avoid any configuration
parameter that could allow the users shooting themselves in the feet.
To solve your problem, why don't you set a lower db_cleandelay value ?
This would have the same effect. By default it's 30 minutes. You could
set it to 30 seconds. On a highly loaded server it should be ok.
Anyway, I don't think you would block other SQLgrey processes if you
used InnoDB (or any other backend supporting concurrent writes).

>  Ive tried running cleanup on a connect table with 53mil. 
> records and after 45 minutes i had to kill it. And for 45 minutes, none 
> of the sqlgrey's could do selects to the connect tabled, which makes 
> mailusers quite unhappy ;)..
>   

Don't use MyISAM. Seriously, I mean it.
With your amount of traffic, you'll have to work against it (you are
doing it right now). It doesn't support concurrent writes (including
deletes) and locks the whole table. Even if you solve your cleanup
problems the next bottleneck will be the actual INSERTs and UPDATEs from
each of your SQLgrey's instances battling for the locks. At this point
you *will* be utterly toasted.

In fact we should explicitly add this in the README.PERF.

> I need to limit it be able to add a LIMIT to the cleanup statement,  eg. 
> LIMIT 50000,

Don't do this : it will bite you later and harder.

Lionel