Immediate partial results with mysql command line client
I have a table with 100 million rows. I want to process one row at a time in no particular order. I don't want to load all the data into memory at once, and I want immediate partial results. To eliminate potential problems with the ORM framework I'm using, I thought I would test my ability to get immediate partial results using the
mysqlcommand line client, as follows:
mysql> select id from my_table \G
mysqlclient sits there for 75 seconds without printing anything, growing to a final size of 5.8 GB (reported by
top), and then starts printing the results. While printing the results, the memory consumption of the
mysqlclient process does not change (it remains at 5.8 GB).
During the first 75 seconds, using a second
mysqlsession, I can see that my query is in the
sending datastate. When the client starts printing results, the process command (as reported by
show processlistchanges from
Why does the
mysqlinteractive client wait to print results until all the results are in? This is tremendously wasteful of RAM, and fails to yield immediate partial results.
Invoke the MySQL client with the
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.
The reason the default is to store the result in the client is implied by this documentation. If the interactive client is suspended (for example using Ctrl-Z job control), the MySQL Server must use resources to keep the result set active.
You should consider not fetching 100 million rows in a single query to the interactive client.