Re: restrict Connect Time
you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
you can check the slow query log, this will give you all the sql's which are taking more time to execute On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
why dont u setup a staging env, which is very much similar to your production and tune all long running sql On Mon, Jul 23, 2012 at 8:02 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 16:37, schrieb Ananda Kumar: why dont u setup a staging env, which is very much similar to your production and tune all long running sql They are tuned and they are fast :) but the never logout and therefore the time get accumulated. re, wh On Mon, Jul 23, 2012 at 8:02 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
so. its more of inactive connections, right. What do you mean by NEVER LOGOUT On Mon, Jul 23, 2012 at 8:17 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:37, schrieb Ananda Kumar: why dont u setup a staging env, which is very much similar to your production and tune all long running sql They are tuned and they are fast :) but the never logout and therefore the time get accumulated. re, wh On Mon, Jul 23, 2012 at 8:02 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 16:58, schrieb Ananda Kumar: so. its more of inactive connections, right. What do you mean by NEVER LOGOUT The programms watch certain states in the database, the connect automatic at db startup, disconnecting is an error case. re, wh On Mon, Jul 23, 2012 at 8:17 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:37, schrieb Ananda Kumar: why dont u setup a staging env, which is very much similar to your production and tune all long running sql They are tuned and they are fast :) but the never logout and therefore the time get accumulated. re, wh On Mon, Jul 23, 2012 at 8:02 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 17:35, schrieb walter harms: Am 23.07.2012 16:58, schrieb Ananda Kumar: so. its more of inactive connections, right. What do you mean by NEVER LOGOUT The programms watch certain states in the database, the connect automatic at db startup, disconnecting is an error case. so why do you want to restrict connect time if this is a error-case for you? signature.asc Description: OpenPGP digital signature
Re: restrict Connect Time
not very clear. You use connection pooling, right. If the same session/thread is held for long time, it means the thread is not getting closed even after doing its job, and adding to execution time. On Mon, Jul 23, 2012 at 9:05 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:58, schrieb Ananda Kumar: so. its more of inactive connections, right. What do you mean by NEVER LOGOUT The programms watch certain states in the database, the connect automatic at db startup, disconnecting is an error case. re, wh On Mon, Jul 23, 2012 at 8:17 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:37, schrieb Ananda Kumar: why dont u setup a staging env, which is very much similar to your production and tune all long running sql They are tuned and they are fast :) but the never logout and therefore the time get accumulated. re, wh On Mon, Jul 23, 2012 at 8:02 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 16:10, schrieb Ananda Kumar: you can check the slow query log, this will give you all the sql's which are taking more time to execute Yes but you will see the results only when the query is finished. my first idea was to use something like this: select * from information_schema.processlist where state like 'executing' and time 1000 ; unfortunately time i cumulative and i would kill long running processes that we have also. i guess i will make some assumptions about the statement and kill the rest. re, wh On Mon, Jul 23, 2012 at 7:38 PM, walter harms wha...@bfs.de wrote: Am 23.07.2012 15:47, schrieb Ananda Kumar: you can set this is in application server. You can also set this parameter in my.cnf wait_timeout=120 in seconds. But the above parameter is only for inactive session acutualy i want to catch scripts running wild. re, wh On Mon, Jul 23, 2012 at 6:18 PM, walter harms wha...@bfs.de wrote: Hi list, is there a switch where i can restrict the connect/execution time for a query ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restrict Connect Time
Am 23.07.2012 17:38, schrieb Reindl Harald: Am 23.07.2012 17:35, schrieb walter harms: Am 23.07.2012 16:58, schrieb Ananda Kumar: so. its more of inactive connections, right. What do you mean by NEVER LOGOUT The programms watch certain states in the database, the connect automatic at db startup, disconnecting is an error case. so why do you want to restrict connect time if this is a error-case for you? no, this is a misunderstanding, i want to catch running querries that already run longer than a certain time. When i use my simple approach like: select * from information_schema.processlist where state like 'executing' and time 1000 ; it did work work as intended. Unfortunately 'time' is cumulative meaning there is a real risk catching legitimate users. I guess i could filter it based on 'info' (what contains the query) but i was hoping that there is a more simple way. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: restrict Connect Time
And don't kill replication when you see a big time in processlist. therefore the time get accumulated ?? Each query stands alone in timing. The idle time is caught by wait_timeout, which was already mentioned. You can distinguish the cases by whether processlist says Sleep. A Sleeping thread is virtually harmless. * It is occupying some RAM for the thread (usually not an issue); * If there are too many, you could hit max_connections, thereby disallowing new connections. See Percona -- they have some user statistic features that are on beyond the MySQL offerings. -Original Message- From: walter harms [mailto:wha...@bfs.de] Sent: Monday, July 23, 2012 9:31 AM To: mysql@lists.mysql.com Subject: Re: restrict Connect Time Am 23.07.2012 17:38, schrieb Reindl Harald: Am 23.07.2012 17:35, schrieb walter harms: Am 23.07.2012 16:58, schrieb Ananda Kumar: so. its more of inactive connections, right. What do you mean by NEVER LOGOUT The programms watch certain states in the database, the connect automatic at db startup, disconnecting is an error case. so why do you want to restrict connect time if this is a error-case for you? no, this is a misunderstanding, i want to catch running querries that already run longer than a certain time. When i use my simple approach like: select * from information_schema.processlist where state like 'executing' and time 1000 ; it did work work as intended. Unfortunately 'time' is cumulative meaning there is a real risk catching legitimate users. I guess i could filter it based on 'info' (what contains the query) but i was hoping that there is a more simple way. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
