Re: Pauses with large selects
S Lemmon writes: Sinisa Milivojevic wrote: Hi! With : select * from table; queries MySQL returns results immediately. Any problem that you experience might come only from poor build, some OS bug or some wrong settings. Thanks for the reply! That's good to know at least. I really didn't know if anything was wrong or not. I've been looking through the docs and the list for a few days, but it's tricky when you don't really know what to look for (searching for stuff like "select" and "pause" doesn't help much ;-). However, I think I finally stumbled upon at least part of my problem... It's "mysql_store_result" vs. "mysql_use_result"! Since both Perl's DBI and the mysql command use "mysql_store_result" by default, all rows are buffered up before being returned - the more rows the longer the delay. Informix behaves more like "mysql_use_result" where results are returned as they are found. The other problem is the delay when closing a select early. From what I gather it seems like it's not possible to abort a select without first reading all the rows. I'm guessing the mysql client and Perl DBI both hide this by just doing the equivalent of sending what remains to /dev/null (hence the delay). I guess it's not really a problem now that I know what to expect. I just have to remember to include limits on all my selects. It's just a bit of a re-think since until recently, Informix had no statement like LIMIT and it's normal to simply close the cursor (finish() under DBI) once you've found what you're after. Thanks, S. Lemmon Hi! Use mysql_use_result and both of the above problems will be resolved. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pauses with large selects
Sinisa Milivojevic wrote: Hi! With : select * from table; queries MySQL returns results immediately. Any problem that you experience might come only from poor build, some OS bug or some wrong settings. Thanks for the reply! That's good to know at least. I really didn't know if anything was wrong or not. I've been looking through the docs and the list for a few days, but it's tricky when you don't really know what to look for (searching for stuff like "select" and "pause" doesn't help much ;-). However, I think I finally stumbled upon at least part of my problem... It's "mysql_store_result" vs. "mysql_use_result"! Since both Perl's DBI and the mysql command use "mysql_store_result" by default, all rows are buffered up before being returned - the more rows the longer the delay. Informix behaves more like "mysql_use_result" where results are returned as they are found. The other problem is the delay when closing a select early. From what I gather it seems like it's not possible to abort a select without first reading all the rows. I'm guessing the mysql client and Perl DBI both hide this by just doing the equivalent of sending what remains to /dev/null (hence the delay). I guess it's not really a problem now that I know what to expect. I just have to remember to include limits on all my selects. It's just a bit of a re-think since until recently, Informix had no statement like LIMIT and it's normal to simply close the cursor (finish() under DBI) once you've found what you're after. Thanks, S. Lemmon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pauses with large selects
S Lemmon writes: Hi, My main experience is with Informix databases, but am trying MySQL on our web server (ver 3.23.33 running on Linux 2.2.16-3). In general it's working well, but being a MySQL newbie I'm a bit mystified by some odd select behavior. With Informix, doing a simple select like "select * from foo" (no where clause or order by) will always return results immediately regardless of how many rows are in table "foo". However, in my test MySQL database, when "foo" is a large table I get a long pause before any results come back and (even odder) another long pause when closing the statement handle after fetching a few rows. MySQL's CPU usage also shoots way up during these pauses. Are all the results are being buffered up somewhere before anything is returned, and then, I'm guessing, purged when the query is finished? It's odd that the closing pause is often longer and more CPU intensive than the opening one. With Informix, initial buffering only happens if something like a temporary table or index has to be created. Otherwise the results are returned as they are found by the db engine scanning through the table. In any case, there's no delay when closing. With the MySQL database, it seems the larger the potential result set, the longer both pauses become. In most cases I can get around it by just trimming the results with LIMIT, but when I actually need to iterate through a large table, wouldn't buffering up perhaps millions of rows cause problems (if that's what's really happening)? Really, I'm not sure if this is a bug, some problem with my tables, or just the way MySQL works. I'd be grateful if someone could shed some light on what might be causing the pause, or if it's normal, perhaps a better way to loop through all the rows in a large table without building up a potentially huge temporary buffer somewhere. Thanks for your time, S. Lemmon Hi! With : select * from table; queries MySQL returns results immediately. Any problem that you experience might come only from poor build, some OS bug or some wrong settings. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Pauses with large selects
Hi, My main experience is with Informix databases, but am trying MySQL on our web server (ver 3.23.33 running on Linux 2.2.16-3). In general it's working well, but being a MySQL newbie I'm a bit mystified by some odd select behavior. With Informix, doing a simple select like "select * from foo" (no where clause or order by) will always return results immediately regardless of how many rows are in table "foo". However, in my test MySQL database, when "foo" is a large table I get a long pause before any results come back and (even odder) another long pause when closing the statement handle after fetching a few rows. MySQL's CPU usage also shoots way up during these pauses. Are all the results are being buffered up somewhere before anything is returned, and then, I'm guessing, purged when the query is finished? It's odd that the closing pause is often longer and more CPU intensive than the opening one. With Informix, initial buffering only happens if something like a temporary table or index has to be created. Otherwise the results are returned as they are found by the db engine scanning through the table. In any case, there's no delay when closing. With the MySQL database, it seems the larger the potential result set, the longer both pauses become. In most cases I can get around it by just trimming the results with LIMIT, but when I actually need to iterate through a large table, wouldn't buffering up perhaps millions of rows cause problems (if that's what's really happening)? Really, I'm not sure if this is a bug, some problem with my tables, or just the way MySQL works. I'd be grateful if someone could shed some light on what might be causing the pause, or if it's normal, perhaps a better way to loop through all the rows in a large table without building up a potentially huge temporary buffer somewhere. Thanks for your time, S. Lemmon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php