Immediate partial results with mysql command line client

All we need is an easy explanation of the problem, so here it is.

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 mysql command line client, as follows:

mysql> select id from my_table \G

But the mysql client 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 mysql client process does not change (it remains at 5.8 GB).

During the first 75 seconds, using a second mysql session, I can see that my query is in the sending data state. When the client starts printing results, the process command (as reported by show processlist changes from Query to Sleep.

Why does the mysql interactive client wait to print results until all the results are in? This is tremendously wasteful of RAM, and fails to yield immediate partial results.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Invoke the MySQL client with the --quick option.

See https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_quick

–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.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply