The MySQL –q function should be used to help eliminate unnecessary memory by the mysql process when the client is extracting more than a few million rows.
The –q option will cause the results to be sent out immediately rather than storing the entire results set in the mysql client memory until all the results are returned.
In addition, there can be some time savings as well. Extracting data to standard out with -q as well as writing to an outfile both used nearly zero memory in the mysql client, as well as running faster than extracting to standard out without the -q flag. Writing to standard out with 100 million rows consumed 5.1G of memory, and larger extracts can consume even more memory.
[root@srvprodtest1 jtommaney]# time idbmysql ssb100c -e "select lo_orderkey into outfile 'out999.txt' from lineorder where lo_orderkey < 100000000"
real 0m37.281s
user 0m0.004s
sys 0m0.001s
[root@srvprodtest1 jtommaney]# time idbmysql ssb100c -q -e 'select lo_orderkey from lineorder where lo_orderkey < 100000000' > out99.txt
real 0m43.289s
user 0m17.996s
sys 0m2.187s
[root@srvprodtest1 jtommaney]# time idbmysql ssb100c -e 'select lo_orderkey from lineorder where lo_orderkey < 100000000' > out99.txt
real 0m54.995s
user 0m19.716s
sys 0m3.543s
