'unauthenticated user' x 100 in SHOW PROCESSLIST

I keep getting 'too many connections' errors.

When I 'SHOW PROCESS LIST' I get an endless list of these:

| 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
Connect | | login | NULL| |||


The xxx.xxx.xxx.xxx is always one of the 4 other servers that connect to
this one, the 'unauthenticated user' connections are fairly evenly
distributed over the 4 servers.

I have no idea why this is happening.

If I ssh into one of those 4 servers and try to connect to this database I
get asked for the password as expected and then hit enter. Then it pauses
for 5-10 seconds (and shows up as unauthenticated user in the processlist).

This is quite a busy cluster of servers so the max connections is reached
within seconds.

Then suddenly they all authenticate at once and all the unauthenticated
users start building up again and the cycle goes on and on.

Server is 5.0.24-standard-log and all tables are MyISAM, OS is RHEL4.

The load on this server is quite low, 5 10 and 15 min averages are around
0.90 and the server is mainly idle when the problem occurs.

I've tried restarting MySQL, all this does is kill the connections and the
problem starts again straight away.

Any ideas?


--
Brian Wakem

Brian Wakem wrote:

> I keep getting 'too many connections' errors.
>
> When I 'SHOW PROCESS LIST' I get an endless list of these:
>
> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
> Connect | | login | NULL| |||


After some research it seems to be a DNS issue. MySQL is trying to reverse
lookup every IP connecting to it.

The manual says it will cache a failure and not attempt it again until FLUSH
HOSTS or restarted. This is clearly not happening, should I file a bug?

The resolution is to pass --skip-name-resolve at start up.



--
Brian Wakem

Brian Wakem wrote:
> Brian Wakem wrote:
>
>> I keep getting 'too many connections' errors.
>>
>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>
>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>> Connect | | login | NULL| |||
>
>
> After some research it seems to be a DNS issue. MySQL is trying to reverse
> lookup every IP connecting to it.
>
> The manual says it will cache a failure and not attempt it again until FLUSH
> HOSTS or restarted. This is clearly not happening, should I file a bug?
>
> The resolution is to pass --skip-name-resolve at start up.
>
>
>

But it doesn't look like it's failing. Rather, it's made the rdns
request, and but it's taking several seconds for the request to come
back. Then when it does come back, all the requests are released.

The first question would be why is the rdns lookup taking so long? It
should be pretty fast.

But if you're only using ip addresses in your grant tables (no host
names), you can add that in. It will cut down on the cpu load and
network traffic some, and should resolve your problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:

> Brian Wakem wrote:
>> Brian Wakem wrote:
>>
>>> I keep getting 'too many connections' errors.
>>>
>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>
>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>>> Connect | | login | NULL| |||
>>
>>
>> After some research it seems to be a DNS issue. MySQL is trying to
>> reverse lookup every IP connecting to it.
>>
>> The manual says it will cache a failure and not attempt it again until
>> FLUSH
>> HOSTS or restarted. This is clearly not happening, should I file a bug?
>>
>> The resolution is to pass --skip-name-resolve at start up.
>>
>>
>>
>
> But it doesn't look like it's failing. Rather, it's made the rdns
> request, and but it's taking several seconds for the request to come
> back. Then when it does come back, all the requests are released.


I agree, but the manual says that at this point it is cached until flush
hosts or restart. It is not being cached as the problem occurs again a few
seconds later.


> The first question would be why is the rdns lookup taking so long?


I've no idea, but I think mysql should fail silently, not catastrophically.


> But if you're only using ip addresses in your grant tables (no host
> names), you can add that in. It will cut down on the cpu load and
> network traffic some, and should resolve your problem.


In fact mysql will do a reverse lookup even if you only use IP addresses (as
I do), the only way to stop it is with --skip-name-resolve.


--
Brian Wakem

Brian Wakem <no@email.com> wrote:
> Brian Wakem wrote:
>
>> I keep getting 'too many connections' errors.
>>
>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>
>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>> Connect | | login | NULL| |||
>
>
> After some research it seems to be a DNS issue. MySQL is trying to reverse
> lookup every IP connecting to it.

Yep.

> The manual says it will cache a failure and not attempt it again until FLUSH
> HOSTS or restarted. This is clearly not happening, should I file a bug?

Where in the manual did you find this? It's not here:
http://dev.mysql.com/doc/refman/5.0/en/dns.html

> The resolution is to pass --skip-name-resolve at start up.

Or fix your broken DNS.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Brian Wakem wrote:
> Jerry Stuckle wrote:
>
>> Brian Wakem wrote:
>>> Brian Wakem wrote:
>>>
>>>> I keep getting 'too many connections' errors.
>>>>
>>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>>
>>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>>>> Connect | | login | NULL| |||
>>>
>>> After some research it seems to be a DNS issue. MySQL is trying to
>>> reverse lookup every IP connecting to it.
>>>
>>> The manual says it will cache a failure and not attempt it again until
>>> FLUSH
>>> HOSTS or restarted. This is clearly not happening, should I file a bug?
>>>
>>> The resolution is to pass --skip-name-resolve at start up.
>>>
>>>
>>>
>> But it doesn't look like it's failing. Rather, it's made the rdns
>> request, and but it's taking several seconds for the request to come
>> back. Then when it does come back, all the requests are released.
>
>
> I agree, but the manual says that at this point it is cached until flush
> hosts or restart. It is not being cached as the problem occurs again a few
> seconds later.
>

I would expect that. How long do you want MySQL to cache the results?

What happens, for instance, if MySQL is running for weeks at a time
(like most of my systems) - and somewhere in that timeframe the DNS
record for one of the servers is changed?

>
>> The first question would be why is the rdns lookup taking so long?
>
>
> I've no idea, but I think mysql should fail silently, not catastrophically.
>

But it's not failing. It's just waiting for the rdns request to come
back, and when it does, MySQL continues on.

>
>> But if you're only using ip addresses in your grant tables (no host
>> names), you can add that in. It will cut down on the cpu load and
>> network traffic some, and should resolve your problem.
>
>
> In fact mysql will do a reverse lookup even if you only use IP addresses (as
> I do), the only way to stop it is with --skip-name-resolve.
>
>

Please read what I said again.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Axel Schwenke wrote:

> Brian Wakem <no@email.com> wrote:
>> Brian Wakem wrote:
>>
>>> I keep getting 'too many connections' errors.
>>>
>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>
>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>>> Connect | | login | NULL| |||
>>
>>
>> After some research it seems to be a DNS issue. MySQL is trying to
>> reverse lookup every IP connecting to it.
>
> Yep.
>
>> The manual says it will cache a failure and not attempt it again until
>> FLUSH
>> HOSTS or restarted. This is clearly not happening, should I file a bug?
>
> Where in the manual did you find this? It's not here:
> http://dev.mysql.com/doc/refman/5.0/en/dns.html


I found it on a page that referenced the 4.0 manual, I can't find it now.

Even if that is wrong it is irrelevant because the DNS lookups did succeed
eventually, but when they did they were not being cached. This contradicts
the 5.0 manual.

Perhaps if the lookup fails on the first namesever but succeeds on the
second then it is not cached? I'm only speculating.


>> The resolution is to pass --skip-name-resolve at start up.
>
> Or fix your broken DNS.


Not mine to fix unfortunately.



--
Brian Wakem

Jerry Stuckle wrote:

> Brian Wakem wrote:
>> Jerry Stuckle wrote:
>>
>>> Brian Wakem wrote:
>>>> Brian Wakem wrote:
>>>>
>>>>> I keep getting 'too many connections' errors.
>>>>>
>>>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>>>
>>>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>>>>> Connect | | login | NULL| |||
>>>>
>>>> After some research it seems to be a DNS issue. MySQL is trying to
>>>> reverse lookup every IP connecting to it.
>>>>
>>>> The manual says it will cache a failure and not attempt it again until
>>>> FLUSH
>>>> HOSTS or restarted. This is clearly not happening, should I file a
>>>> bug?
>>>>
>>>> The resolution is to pass --skip-name-resolve at start up.
>>>>
>>>>
>>>>
>>> But it doesn't look like it's failing. Rather, it's made the rdns
>>> request, and but it's taking several seconds for the request to come
>>> back. Then when it does come back, all the requests are released.
>>
>>
>> I agree, but the manual says that at this point it is cached until flush
>> hosts or restart. It is not being cached as the problem occurs again a
>> few seconds later.
>>
>
> I would expect that. How long do you want MySQL to cache the results?


I don't know. More than 3 seconds that's for sure, otherwise what is the
point of caching?


> What happens, for instance, if MySQL is running for weeks at a time
> (like most of my systems) - and somewhere in that timeframe the DNS
> record for one of the servers is changed?
>
>>
>>> The first question would be why is the rdns lookup taking so long?
>>
>>
>> I've no idea, but I think mysql should fail silently, not
>> catastrophically.
>>
>
> But it's not failing. It's just waiting for the rdns request to come
> back, and when it does, MySQL continues on.


I know, but it's not a clever way of doing it. If you have 500 threads
waiting to lookup the same IP, do you need to look it up again?

Caching failure (or properly caching success) to prevent repetitive lookups
seems like what it should do.


--
Brian Wakem

Brian Wakem wrote:
> Jerry Stuckle wrote:
>
>> Brian Wakem wrote:
>>> Jerry Stuckle wrote:
>>>
>>>> Brian Wakem wrote:
>>>>> Brian Wakem wrote:
>>>>>
>>>>>> I keep getting 'too many connections' errors.
>>>>>>
>>>>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>>>>
>>>>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL |
>>>>>> Connect | | login | NULL| |||
>>>>> After some research it seems to be a DNS issue. MySQL is trying to
>>>>> reverse lookup every IP connecting to it.
>>>>>
>>>>> The manual says it will cache a failure and not attempt it again until
>>>>> FLUSH
>>>>> HOSTS or restarted. This is clearly not happening, should I file a
>>>>> bug?
>>>>>
>>>>> The resolution is to pass --skip-name-resolve at start up.
>>>>>
>>>>>
>>>>>
>>>> But it doesn't look like it's failing. Rather, it's made the rdns
>>>> request, and but it's taking several seconds for the request to come
>>>> back. Then when it does come back, all the requests are released.
>>>
>>> I agree, but the manual says that at this point it is cached until flush
>>> hosts or restart. It is not being cached as the problem occurs again a
>>> few seconds later.
>>>
>> I would expect that. How long do you want MySQL to cache the results?
>
>
> I don't know. More than 3 seconds that's for sure, otherwise what is the
> point of caching?
>

But again - what's the limit? 3 seconds? 3 minutes? 3 years?

Perhaps it could be a mysql configuration parameter. Maybe better is to
just figure out why your rdns lookup isn't working. But that's not an
option right now.

And if you do have rdns problems, it can affect other programs as well.
For instance, Apache can be configured to do a rdns lookup for it's
logs. And MTA's often use rdns lookups for spam filtering. And even if
it's not a problem now, it could be later.

I think it's better to find the source of the problem, rather than
trying to get around the problem.

>
>> What happens, for instance, if MySQL is running for weeks at a time
>> (like most of my systems) - and somewhere in that timeframe the DNS
>> record for one of the servers is changed?
>>
>>>> The first question would be why is the rdns lookup taking so long?
>>>
>>> I've no idea, but I think mysql should fail silently, not
>>> catastrophically.
>>>
>> But it's not failing. It's just waiting for the rdns request to come
>> back, and when it does, MySQL continues on.
>
>
> I know, but it's not a clever way of doing it. If you have 500 threads
> waiting to lookup the same IP, do you need to look it up again?
>
> Caching failure (or properly caching success) to prevent repetitive lookups
> seems like what it should do.
>
>

No, you don't need to look it up again. But you need to wait for the
first response to come back!

It may be just waiting for one rdns lookup to return, then allow all of
them to go. I haven't looked at the code, but that may be the case.
And from your symptoms (everything taking off at once), it looks like
someone's caching the response. If not MySQL, the OS.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:

> Brian Wakem wrote:
>> Jerry Stuckle wrote:
>>
>>> Brian Wakem wrote:
>>>> Jerry Stuckle wrote:
>>>>
>>>>> Brian Wakem wrote:
>>>>>> Brian Wakem wrote:
>>>>>>
>>>>>>> I keep getting 'too many connections' errors.
>>>>>>>
>>>>>>> When I 'SHOW PROCESS LIST' I get an endless list of these:
>>>>>>>
>>>>>>> | 2974081 | unauthenticated user | xxx.xxx.xxx.xxx:36048 | NULL
>>>>>>> | |
>>>>>>> Connect | | login | NULL| |||
>>>>>> After some research it seems to be a DNS issue. MySQL is trying to
>>>>>> reverse lookup every IP connecting to it.
>>>>>>
>>>>>> The manual says it will cache a failure and not attempt it again
>>>>>> until FLUSH
>>>>>> HOSTS or restarted. This is clearly not happening, should I file a
>>>>>> bug?
>>>>>>
>>>>>> The resolution is to pass --skip-name-resolve at start up.
>>>>>>
>>>>>>
>>>>>>
>>>>> But it doesn't look like it's failing. Rather, it's made the rdns
>>>>> request, and but it's taking several seconds for the request to come
>>>>> back. Then when it does come back, all the requests are released.
>>>>
>>>> I agree, but the manual says that at this point it is cached until
>>>> flush
>>>> hosts or restart. It is not being cached as the problem occurs again a
>>>> few seconds later.
>>>>
>>> I would expect that. How long do you want MySQL to cache the results?
>>
>>
>> I don't know. More than 3 seconds that's for sure, otherwise what is the
>> point of caching?
>>
>
> But again - what's the limit? 3 seconds? 3 minutes? 3 years?



Well the fact that is was doing lookups as soon as I could type SHOW
PROCESSLIST (actually just the up arrow and enter in fact) suggests that it
wasn't caching at all. I'd be interested to know how long it is supposed
to cache for.



> Perhaps it could be a mysql configuration parameter. Maybe better is to
> just figure out why your rdns lookup isn't working. But that's not an
> option right now.
>
> And if you do have rdns problems, it can affect other programs as well.
> For instance, Apache can be configured to do a rdns lookup for it's
> logs. And MTA's often use rdns lookups for spam filtering. And even if
> it's not a problem now, it could be later.
>
> I think it's better to find the source of the problem, rather than
> trying to get around the problem.



Thankfully I already have apache configured no to do lookups, and this
machine doesn't collect mail.


I don't control the nameservers. It wouldn't suprise we if they were
throttling the lookups from us, as it seems mysql (mine at least) isn't
caching the lookups so it would have been sending up to 1000 requests per
minute to the nameservers.



--
Brian Wakem