List:General Discussion« Previous MessageNext Message »
From:mos Date:February 8 2008 5:14am
Subject:Re: very strange slow plain select
View as plain text  
At 03:49 PM 2/7/2008, dvd@stripped wrote:
>Thanks very much. The default mysql behavior is a little
>unusual. What is the parameter in my.cnf to control the cache
>size before forcing a flush.  I waited for 2 mininutes second
>before the output came out without the -q option. My server
>is fast enough to read in more than 2 GB data during this time
>so the cache would have been filled long before that. Kind
>of puzzling.


Have you tried "Select SQL_NO_CACHE col1,col2 from table ..."?
If you are returning more than 10,000 rows at a time, why not use the LIMIT 
Offset,Limit to pull in just a few thousand rows at a time, then re-execute 
with a new offset to get the next thousand rows etc.. Why tie up the server 
with one huge query?  You're also transferring 2gb over the network in one 
gulp so that can't be efficient either. Break it into smaller queries 
should help.

Mike


> >In the last episode (Feb 07), dvd@stripped said:
> >> I just stumbled on this hard to explain problem. Below
> >>
> >> select * from BigTable      (BigTable has
> 5 million rows)
> >>
> >> takes forever to even print out the first row. as you could see from
> >> the sql, it is supposed to print out some rows right away regardless
> >> of server config in my.cnf.   What is interesting is the
> following
> >> sql
> >>
> >> select * from BigTable limit 1000
> >>
> >> return the rows immediately as expected.
> >
> >You want the --quick option.  From the mysql manpage:
> >
> >       o  --quick, -q
> >
> >          Do not cache each query result, print each row as it is
> >          received.  This may slow down the server if the output is
> >          suspended. With this option, mysql does not use the history
> >          file.
> >
> >--
> >       Dan Nelson
> >       dnelson@stripped
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
very strange slow plain selectdvd7 Feb
  • Re: very strange slow plain selectDan Nelson7 Feb
Re: very strange slow plain selectdvd7 Feb
  • Re: very strange slow plain selectmos8 Feb