Re: query_cache_type and Aborted Clients
Rick James <[email protected]>
| Newsgroups | gmane.comp.db.mysql.devel |
|---|---|
| Message-ID | <[email protected]> |
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<[email protected]> wrote: > >> From: Rick James<[email protected]> >> Subject: Re: query_cache_type and Aborted Clients >> To: [email protected] >> 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 -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals