Re: [PHP] Stop PHP execution on client connection closed
Il 14/09/2011 19:34, Alex Nikitin ha scritto: > Perhaps if I, or we can understand your application a little better, > we could suggest better solutions, just remember that you are not the > first person to have to solve these similar issues. I can help you if > you want, glimpse over your database design and queries for a fresh > look, i have fairly extensive php (and many other languages) > programming experience, as well as database design and administration, > system development and administration, optimization, security, caching > (many other things, that don't directly pertain to this) though we > should probably keep it off the list. Table and queries are very simple. Table is like this: CREATE TABLE `TABLE1` ( `ID` int unsigned NOT NULL, `Service` char(2) NOT NULL, `Lang` char(2) NOT NULL, `Text1` varchar(100) DEFAULT NULL, `Char1` char(1) NOT NULL, `Date1` date NOT NULL, `Num1` int unsigned NOT NULL, `Num2` smallint unsigned NOT NULL, `Num3` smallint unsigned NOT NULL, `Num4` tinyint unsigned NOT NULL, `Num5` int unsigned NOT NULL, `Num6` tinyint NOT NULL, `Num7` int unsigned NOT NULL, PRIMARY KEY (`ID`,`Service`,`Lang`), KEY `index_1` (`Char1`), KEY `index_2` (`Date1`), KEY `index_3` (`Num1`), KEY `index_4` (`Num2`), KEY `index_5` (`Num3`), KEY `index_6` (`Num4`), KEY `index_7` (`Num5`), KEY `index_8` (`Num6`), KEY `index_9` (`Num7`), KEY `index_10` (`Text1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; And this is an example query: SELECT COUNT(*) FROM TABLE1 WHERE Char1='A' AND Num2=10 The WHERE clause can contain any indexed column. Bye, Marco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
On Wed, Sep 14, 2011 at 4:04 AM, Marco Lanzotti wrote: > Il 13/09/2011 20:58, Alex Nikitin ha scritto: > > Correction on Marco's post. You can absolutely stop a mysql query > > I know I can stop a query, but I don't know how to realize HTTP client > has closed connection during query execution. > > My query count how many records match selected fields in a 50M records > table. > Any query field is indexed and innodb uses 20GB of RAM to store data and > indexes, but some queries take about 30 seconds to run. > When user changes filters and asks for a new count, the old queries > continue to run using DB resurces unnecessarily. > > Bye, > Marco > Marco, I ran queries on a table that had 12M rows added to it each month with a year+ worth of data going back, pulling 80-90 thousand records with over a dozen columns on an older dual dual core box with 8gb ram (so 6 for MySQL) joining multiple tables for various criteria, matching on various values with query execution in a second range (depending on load, from under a second, to under 2 seconds). I think, and i am not trying to sound like pompous buffoon or to put anyone down or say that you or anyone here don't know what they are talking about or anything like that, but i think that you should first look into how you can optimize your database and your query, as well as maybe the access to this information (volume of information that you are presenting vs getting, also how you filter it, etc). Sometimes it's a very simple thing that can make or brake query execution time, and it's not immediately apparent. I was once tasked to fix a process in which about 2-300 queries were ran against the database in periodic ajax calls, they took about a 1/4 second to execute for each query. This ofcourse means that the refresh took almost a minute to run, which was getting very annoying, so i glimpsed over the queries and the tables at hand and 5 minutes later issued 2 queries, one to delete a useless index that was created for the main table, and another to create a new index on the database that reduced the execution time of those queries from 1/4 sec for each to 1.4 or 1.6 sec for all 2-300. And most of that time was actually caused by the network lag for the 2-300 queries, since they were individually executed from php, i wanted to reduce that whole thing to one query, but wasn't allowed to. Other times its a lot more complex, and sometimes blowing a query up from something simple or straight forward to something more complex can wield similar increases in performance, this ofcourse has to be with thorough understanding of how the database works. Perhaps if I, or we can understand your application a little better, we could suggest better solutions, just remember that you are not the first person to have to solve these similar issues. I can help you if you want, glimpse over your database design and queries for a fresh look, i have fairly extensive php (and many other languages) programming experience, as well as database design and administration, system development and administration, optimization, security, caching (many other things, that don't directly pertain to this) though we should probably keep it off the list. - Alex -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray
Re: [PHP] Stop PHP execution on client connection closed
Il 14/09/2011 17:35, Jim Lucas ha scritto: > > SELECT ... FROM ... WHERE ... AND (1=1 OR 'unique value'); > > add 'unique value' to your session data and then, when the person changes the > selected fields and starts to execute another query, first, you could search > to > see if an SQL statement is running that has your unique value in it. Not so clean, but it could work! Thank you, Marco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
On 9/14/2011 1:04 AM, Marco Lanzotti wrote: > Il 13/09/2011 20:58, Alex Nikitin ha scritto: >> Correction on Marco's post. You can absolutely stop a mysql query > > I know I can stop a query, but I don't know how to realize HTTP client > has closed connection during query execution. > > My query count how many records match selected fields in a 50M records > table. > Any query field is indexed and innodb uses 20GB of RAM to store data and > indexes, but some queries take about 30 seconds to run. > When user changes filters and asks for a new count, the old queries > continue to run using DB resurces unnecessarily. > > Bye, > Marco Well, from the sounds of that, you really do not have an easy option. Here is my suggestion. In your initial script, you could add a unique value to your SQL statement. You SQL would be something like... SELECT ... FROM ... WHERE ... AND (1=1 OR 'unique value'); add 'unique value' to your session data and then, when the person changes the selected fields and starts to execute another query, first, you could search to see if an SQL statement is running that has your unique value in it. if it cannot find a matching statement, simply execute the SQL query. If it does find an SQL statement that matches the unique value, kill it, then issue your SQL statement. Read the following to figure out how to find your unique process: http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html Read the following to find out how to kill your processes: http://dev.mysql.com/doc/refman/5.0/en/kill.html But, it does seem like it would be possible. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
Il 13/09/2011 20:58, Alex Nikitin ha scritto: > Correction on Marco's post. You can absolutely stop a mysql query I know I can stop a query, but I don't know how to realize HTTP client has closed connection during query execution. My query count how many records match selected fields in a 50M records table. Any query field is indexed and innodb uses 20GB of RAM to store data and indexes, but some queries take about 30 seconds to run. When user changes filters and asks for a new count, the old queries continue to run using DB resurces unnecessarily. Bye, Marco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
Absolutely, it was only a minor correction of a sub-point. -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray On Tue, Sep 13, 2011 at 3:20 PM, Jim Lucas wrote: > On 9/13/2011 11:58 AM, Alex Nikitin wrote: > > On Tue, Sep 13, 2011 at 11:44 AM, Jim Lucas wrote: > > > >> On 9/12/2011 7:40 AM, Marco Lanzotti wrote: > >>> Hi all, I'm new in the list and I already have a question for you. > >>> I'm running an heavy query on my DB in a PHP script called by AJAX. > >>> Because client often abort AJAX connection to ask a new query, I need > to > >>> stop query because DB will be too loaded. > >>> When AJAX connection is aborted, PHP script doesn't stop until it send > >>> some output to client, so I need to wait query execution to know client > >>> aborted connection. > >>> How can I abort query (or script) when AJAX connection is aborted? > >>> > >>> Thank you, > >>> Marco > >>> > >>> > >> > >> You cannot stop a DB query. > >> > >> What this means is PHP will not be able to do anything else until the db > >> has > >> finished its step and handed data back to the processing script. At > that > >> point, > >> you can check to see if the connection is still active and take > appropriate > >> action. > >> > >> Jim Lucas > >> > >> -- > >> PHP General Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > > Correction on Marco's post. You can absolutely stop a mysql query, it is > > done with a large amount of success at Facebook for example, where they > have > > very strict query execution rules, e.g. if your query takes too long to > run, > > it is killed. However unless you are dealing with enormous data sets, or > > very very slow mysql server, this is not worth the tremendous amount of > > trouble you would have to go through. And if you are dealing with > enormous > > data sets or slow servers, it would be far more beneficial to address > those > > issue then to implement the query killing thing. > > > > MySQL commands in question are: > > SHOW PROCESSLIST; > > KILL [thread]; > > > > You can also hook into if you really wanted to with some C through the > API, > > but again, it is far more trouble than most people need, and problems > often > > lay else-where (for example inefficient query or bad database design or > > matching on non-indexed cols etc...) A query that ties together 3 tables > and > > pulls 80-90k rows @10 columns shouldn't take more than 0.25 sec to > execute, > > maybe a second for the whole operation from connect to result, if your > mysql > > server is one hop away (i.e. they are on the same switch), the tcp hand > > shake can take up to 100ms, plus you need to get the process list, > traverse > > it for your query, and send a kill command. I'm going to guess that the > kill > > process will take longer to connect, list, parse and kill, then it will > take > > the query to finish and return data... > > > > What is your data set like, what are you trying to accomplish by this > other > > than complicating your code? > > > > Also yes, AJAX is your friend (avoid pulling large or any data sets if > you > > can), as well as some query and database optimization, and caching ;) > > > > > > > > -- > > The trouble with programmers is that you can never tell what a programmer > is > > doing until it’s too late. ~Seymour Cray > > > > My statement still stands. > > >> What this means is PHP will not be able to do anything else until the db > >> has finished its step and handed data back to the processing script. > >
Re: [PHP] Stop PHP execution on client connection closed
On 9/13/2011 11:58 AM, Alex Nikitin wrote: > On Tue, Sep 13, 2011 at 11:44 AM, Jim Lucas wrote: > >> On 9/12/2011 7:40 AM, Marco Lanzotti wrote: >>> Hi all, I'm new in the list and I already have a question for you. >>> I'm running an heavy query on my DB in a PHP script called by AJAX. >>> Because client often abort AJAX connection to ask a new query, I need to >>> stop query because DB will be too loaded. >>> When AJAX connection is aborted, PHP script doesn't stop until it send >>> some output to client, so I need to wait query execution to know client >>> aborted connection. >>> How can I abort query (or script) when AJAX connection is aborted? >>> >>> Thank you, >>> Marco >>> >>> >> >> You cannot stop a DB query. >> >> What this means is PHP will not be able to do anything else until the db >> has >> finished its step and handed data back to the processing script. At that >> point, >> you can check to see if the connection is still active and take appropriate >> action. >> >> Jim Lucas >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > Correction on Marco's post. You can absolutely stop a mysql query, it is > done with a large amount of success at Facebook for example, where they have > very strict query execution rules, e.g. if your query takes too long to run, > it is killed. However unless you are dealing with enormous data sets, or > very very slow mysql server, this is not worth the tremendous amount of > trouble you would have to go through. And if you are dealing with enormous > data sets or slow servers, it would be far more beneficial to address those > issue then to implement the query killing thing. > > MySQL commands in question are: > SHOW PROCESSLIST; > KILL [thread]; > > You can also hook into if you really wanted to with some C through the API, > but again, it is far more trouble than most people need, and problems often > lay else-where (for example inefficient query or bad database design or > matching on non-indexed cols etc...) A query that ties together 3 tables and > pulls 80-90k rows @10 columns shouldn't take more than 0.25 sec to execute, > maybe a second for the whole operation from connect to result, if your mysql > server is one hop away (i.e. they are on the same switch), the tcp hand > shake can take up to 100ms, plus you need to get the process list, traverse > it for your query, and send a kill command. I'm going to guess that the kill > process will take longer to connect, list, parse and kill, then it will take > the query to finish and return data... > > What is your data set like, what are you trying to accomplish by this other > than complicating your code? > > Also yes, AJAX is your friend (avoid pulling large or any data sets if you > can), as well as some query and database optimization, and caching ;) > > > > -- > The trouble with programmers is that you can never tell what a programmer is > doing until it’s too late. ~Seymour Cray > My statement still stands. >> What this means is PHP will not be able to do anything else until the db >> has finished its step and handed data back to the processing script. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Stop PHP execution on client connection closed
On Tue, Sep 13, 2011 at 11:44 AM, Jim Lucas wrote: > On 9/12/2011 7:40 AM, Marco Lanzotti wrote: > > Hi all, I'm new in the list and I already have a question for you. > > I'm running an heavy query on my DB in a PHP script called by AJAX. > > Because client often abort AJAX connection to ask a new query, I need to > > stop query because DB will be too loaded. > > When AJAX connection is aborted, PHP script doesn't stop until it send > > some output to client, so I need to wait query execution to know client > > aborted connection. > > How can I abort query (or script) when AJAX connection is aborted? > > > > Thank you, > > Marco > > > > > > You cannot stop a DB query. > > What this means is PHP will not be able to do anything else until the db > has > finished its step and handed data back to the processing script. At that > point, > you can check to see if the connection is still active and take appropriate > action. > > Jim Lucas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Correction on Marco's post. You can absolutely stop a mysql query, it is done with a large amount of success at Facebook for example, where they have very strict query execution rules, e.g. if your query takes too long to run, it is killed. However unless you are dealing with enormous data sets, or very very slow mysql server, this is not worth the tremendous amount of trouble you would have to go through. And if you are dealing with enormous data sets or slow servers, it would be far more beneficial to address those issue then to implement the query killing thing. MySQL commands in question are: SHOW PROCESSLIST; KILL [thread]; You can also hook into if you really wanted to with some C through the API, but again, it is far more trouble than most people need, and problems often lay else-where (for example inefficient query or bad database design or matching on non-indexed cols etc...) A query that ties together 3 tables and pulls 80-90k rows @10 columns shouldn't take more than 0.25 sec to execute, maybe a second for the whole operation from connect to result, if your mysql server is one hop away (i.e. they are on the same switch), the tcp hand shake can take up to 100ms, plus you need to get the process list, traverse it for your query, and send a kill command. I'm going to guess that the kill process will take longer to connect, list, parse and kill, then it will take the query to finish and return data... What is your data set like, what are you trying to accomplish by this other than complicating your code? Also yes, AJAX is your friend (avoid pulling large or any data sets if you can), as well as some query and database optimization, and caching ;) -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray
Re: [PHP] Stop PHP execution on client connection closed
On 9/12/2011 7:40 AM, Marco Lanzotti wrote: > Hi all, I'm new in the list and I already have a question for you. > I'm running an heavy query on my DB in a PHP script called by AJAX. > Because client often abort AJAX connection to ask a new query, I need to > stop query because DB will be too loaded. > When AJAX connection is aborted, PHP script doesn't stop until it send > some output to client, so I need to wait query execution to know client > aborted connection. > How can I abort query (or script) when AJAX connection is aborted? > > Thank you, > Marco > > You cannot stop a DB query. What this means is PHP will not be able to do anything else until the db has finished its step and handed data back to the processing script. At that point, you can check to see if the connection is still active and take appropriate action. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
