List:Internals« Previous MessageNext Message »
From:Rick James Date:March 5 2012 6:55pm
Subject:Re: query_cache_type and Aborted Clients
View as plain text  
I don't know how to spot a hacker.  The available protection:
* Limit SUPER to root@localhost
* All applications are GRANT ... ON dbname.* ... -- so that if a hacker 
does get in (or there is "sql injection", he cannot get to `mysql` db.
* read-write separation.  Where practical, have two different logins -- 
one for read+write parts of your app, one for readonly.  Again, the 
attempt is to limit the damage.
* Use sufficient hardened passwords (obviously) for _all_ logins.

Query cache --
If there is 'continual' write activity, the QC is useless -- _Any_ write 
to a table purges _all_ entries in the QC for that table.  That purging 
takes CPU effort, and blocks some operations.
Tables that never change, and have a lot of _identical_ queries, can 
benefit greatly from the QC.

Slow queries --
Obfuscate the names (but not the datatypes); we can help without 
compromising security.
There a zillion and one different cases.  I could write a book, and it 
would not be complete.

On 3/5/12 10:36 AM, Revathi Rangachari wrote:
> Hi Rick
>
> Thanks for the explanation.
>
> (1)  wait_timeout is global..in my setup.
>
> (2)>  Aborted_connects/Uptime -- when this is>  1, you might
>> have a hacker>  trying to get in -
> ...........In my case this is 2. How will I find out a hacker is trying to get in? My
> log_warnings is set to 1.
>
> (3)  Disabling query_cache_type  - will it increase the CPU utilization?
>
> (4)  For investigating slow queries, I will not be in a position to share all the
> information that you had asked for, due to security policies, however if you can provide
> some guideline as to how to proceed with the investigations using the commands given
> below, I will give it a try.
>
> Thanks
> Revathi R
>
>
> --- On Fri, 3/2/12, Rick James<rjames@stripped>  wrote:
>
>> From: Rick James<rjames@stripped>
>> Subject: Re: query_cache_type and Aborted Clients
>> To: internals@stripped
>> Date: Friday, March 2, 2012, 2:53 AM
>> Which wait_timeout?  There are
>> about 3 -- session vs global and
>> interactive versus batch.
>>
>> query_cache is (probably) totally independent of
>> connections.
>>
>> Aborted_clients is since startup, and will be "ever
>> increasing" --
>> useless without dividing by something:
>>
>> Aborted_clients/Uptime -- if>  1, then increase
>> wait_timeout
>> Aborted_clients/Connections -- if>  30, then increase
>> wait_timeout
>> Aborted_connects/Uptime -- when this is>  1, you might
>> have a hacker
>> trying to get in
>> Connections/Uptime -- if>  1, you might think about why
>> you need to
>> reconnect so often.
>>
>> If a client sits there too long (more than wait_timeout), he
>> will be
>> disconnected.  30 seconds is plenty for web-type
>> applications.
>>
>> For investigating slow queries, please provide
>> * SHOW CREATE TABLE
>> * SHOW TABLE STATUS
>> * EXPLAIN SELECT ...
>> * SHOW VARIABLES LIKE '%buffer%'; and how much RAM do you
>> have?
>>
>> On 3/1/12 10:16 AM, Revathi Rangachari wrote:
>>> Hi
>>>
>>> Can anyone please let me know:
>>> (1) if query_cache_type is disabled, will it result in
>> the number of 'Aborted Clients' getting increased.
>>> The possible explanation I have is that because the
>> slow queries are taking a long time to execute, and the fact
>> that the query_cache_type is also disabled, will it result
>> in the clients getting disconnected from the server due to
>> wait_timeout set both at the mysql level and application
>> level?
>>> In my mysql setup the wait_timeout is 30 secs and the
>> query_cache_type is disabled. I have an ever increasing
>> number of Aborted Clients
>>>
>>> +------------------+--------+
>>> | Variable_name    | Value  |
>>> +------------------+--------+
>>> | Aborted_clients  | 661117 |
>>> | Aborted_connects | 156    |
>>> +------------------+--------+
>>>
>>> The wait_timeout at application level is 5 secs.
>>>
>>> Thanks in advance for any explanation on my query
>>> Revathi R
>>>
>>>
>>>
>>>
>> -- 
>> Rick James - MySQL Geek
>>
>>
>> -- 
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:    http://lists.mysql.com/internals
>>
>>

-- 
Rick James - MySQL Geek

Thread
MySQL Audit FunctionAruna Karunarathna5 Feb
  • Re: MySQL Audit FunctionAntony T Curtis5 Feb
    • Re: MySQL Audit FunctionPaul DuBois12 Apr
      • Re: MySQL Audit FunctionAntony T Curtis13 Apr
        • Re: MySQL Audit FunctionPaul DuBois13 Apr
          • RE: MySQL Audit FunctionRick James18 Apr
            • Re: MySQL Audit FunctionSergei Golubchik18 Apr
  • re: MySQL Audit FunctionMichael Widenius21 Feb
    • Re: re: MySQL Audit FunctionSergei Golubchik22 Feb
      • memcache vs.query_cacheRevathi Rangachari23 Feb
        • re: memcache vs.query_cacheMichael Widenius24 Feb
          • re: memcache vs.query_cacheRevathi Rangachari25 Feb
          • Re: memcache vs.query_cacheRick James27 Feb
            • query_cache_type and Aborted ClientsRevathi Rangachari1 Mar
              • Re: query_cache_type and Aborted ClientsRick James1 Mar
                • Re: query_cache_type and Aborted ClientsRevathi Rangachari5 Mar
                  • Re: query_cache_type and Aborted ClientsRick James5 Mar
                    • have_query_cache and query_cache_typeRevathi Rangachari6 Mar
                      • Re: have_query_cache and query_cache_typeYves Trudeau7 Mar
                • Slow query log file not getting updatedRevathi Rangachari20 Mar
                  • Re: Slow query log file not getting updatedBaron Schwartz21 Mar