SEO

When I execute a query that returns a quite huge quantity of rows, it
seems mysql sends ALL the data to the client before that client is able
to loop over the result set.

This acts the same with e.g Perl DBI's execute(), or PHP's
mysql_query(), so this looks like it has something to do with the
server's behaviour.

I was wondering if there was a way with mysql to do some kind of
"progressive fetch" so that I do not end up with a script that uses 3GB
of memory? Is it actually something that one cannot change, that has to
be changed in the server setup, or in the client?

Regards,
Renaud

"Renaud" <renaudd@gmail.com> wrote:

> When I execute a query that returns a quite huge quantity of rows, it
> seems mysql sends ALL the data to the client before that client is able
> to loop over the result set.
>
> This acts the same with e.g Perl DBI's execute(), or PHP's
> mysql_query(), so this looks like it has something to do with the
> server's behaviour.
>
> I was wondering if there was a way with mysql to do some kind of
> "progressive fetch" so that I do not end up with a script that uses 3GB
> of memory? Is it actually something that one cannot change, that has to
> be changed in the server setup, or in the client?

This can be chosen for each single statement:

http://dev.mysql.com/doc/refman/5.0/...re-result.html
vs.
http://dev.mysql.com/doc/refman/5.0/...se-result.html

Both PHP and DBD have bindings to chose whether mysql_store_result()
or mysql_use_result() is used to retrieve a result set. The former is
the default because a table becomes read-locked as long as a result
set is being "used" by a client.

Beginning with 5.0 you can use server side cursers to materialize the
result set in the server (without locking the underlying table(s)).


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Thanks so much! I can now get rid of that 5GB temp swapfile...

Renaud

Axel Schwenke wrote:
> "Renaud" <renaudd@gmail.com> wrote:
>
> > When I execute a query that returns a quite huge quantity of rows, it
> > seems mysql sends ALL the data to the client before that client is able
> > to loop over the result set.
> >
> > This acts the same with e.g Perl DBI's execute(), or PHP's
> > mysql_query(), so this looks like it has something to do with the
> > server's behaviour.
> >
> > I was wondering if there was a way with mysql to do some kind of
> > "progressive fetch" so that I do not end up with a script that uses 3GB
> > of memory? Is it actually something that one cannot change, that has to
> > be changed in the server setup, or in the client?
>
> This can be chosen for each single statement:
>
> http://dev.mysql.com/doc/refman/5.0/...re-result.html
> vs.
> http://dev.mysql.com/doc/refman/5.0/...se-result.html
>
> Both PHP and DBD have bindings to chose whether mysql_store_result()
> or mysql_use_result() is used to retrieve a result set. The former is
> the default because a table becomes read-locked as long as a result
> set is being "used" by a client.
>
> Beginning with 5.0 you can use server side cursers to materialize the
> result set in the server (without locking the underlying table(s)).
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

One more thing,
I had the same problem. If you do NOT want to fetch all results at once
but n-by-n rows you can do the following:
1.Set the following 2 attributes:


1.1:
unsigned long type= (unsigned long) CURSOR_TYPE_READ_ONLY;
mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*) &type);
AND
unsigned long prefetch_rows = 5;
1.2: mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS,(void*)
&prefetch_rows);

2. DO NOT call mysql_stmt_store_result(), because the entire result set
will be passed to the client. Instead do mysql_stmt_fetch() ,which will
fetch 5 rows at a time.

3. If you want to know how many rows are left, i.e. if that is the end
of the query result do:
mysql_stmt_num_rows, which will return how many rows are actually
fetched to the client.

This way you can allocate smaller space and fetch until EOF is reached.
One thing to mark ,though. Be careful with BLOB/TEXT fields and the
stmt_functions() , which return RESULT metadata. If you use Cursor, the
field maxlength/length will NOT be set correctly.

Cheers,
Dragomir Stanchev
http://www.linkedin.com/in/dragomirstanchev

Renaud wrote:
> Thanks so much! I can now get rid of that 5GB temp swapfile...
>
> Renaud
>
> Axel Schwenke wrote:
> > "Renaud" <renaudd@gmail.com> wrote:
> >
> > > When I execute a query that returns a quite huge quantity of rows, it
> > > seems mysql sends ALL the data to the client before that client is able
> > > to loop over the result set.
> > >
> > > This acts the same with e.g Perl DBI's execute(), or PHP's
> > > mysql_query(), so this looks like it has something to do with the
> > > server's behaviour.
> > >
> > > I was wondering if there was a way with mysql to do some kind of
> > > "progressive fetch" so that I do not end up with a script that uses 3GB
> > > of memory? Is it actually something that one cannot change, that has to
> > > be changed in the server setup, or in the client?
> >
> > This can be chosen for each single statement:
> >
> > http://dev.mysql.com/doc/refman/5.0/...re-result.html
> > vs.
> > http://dev.mysql.com/doc/refman/5.0/...se-result.html
> >
> > Both PHP and DBD have bindings to chose whether mysql_store_result()
> > or mysql_use_result() is used to retrieve a result set. The former is
> > the default because a table becomes read-locked as long as a result
> > set is being "used" by a client.
> >
> > Beginning with 5.0 you can use server side cursers to materialize the
> > result set in the server (without locking the underlying table(s)).
> >
> >
> > XL
> > --
> > Axel Schwenke, Senior Software Developer, MySQL AB
> >
> > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> > MySQL User Forums: http://forums.mysql.com/