Posted by Bugz on 09/26/77 11:27

Recently I was faced with a challenging query (a join between three
100k line tables)
After some tweaking around, I found out that one problem I had was to
try and sotre more queries in memory (somebody had tweaked the my.cnf
file to store all it could in memory)
This bottle neck actually occured because all the memory got exhausted
after some use and the system started using virtual memory (swap) to
store the queries.
I guess this same rule could apply to you. When physical (ram) memory
exhausts, the system will resort to SWAP, causing a slower response
time in your system.
You'd probably be better off trying to limit the number and size of
queries being executed...
Reduce the memory usage in my.cnf and prefer to use a per query memory
limit by running the "SET SESSION read_buffer_size=#of_bytes"
before running the query. This will set memory allocation only to a
specific query, thus, reducing overall memory consumption.

[Back to original message]