mysql uses alot of RAM and doesn't give it back?

[p1zza]

i'm using mysql 4.0.16 on a gentoo 1.4 box with a gentoo-patched 2.4.23 kernel, 1.3GHz Athlon, 512MB RAM and 20GB HD space (old hd).

i boot up to commandline, login, and top shows 440MB+ of RAM free. i launch an app i've written which begins by hitting mysql with a query against a table of ~2M records up to a few hundred bytes wide. the calculation uses date arithmetic and is calculated against almost every record, and the whole thing takes about 20 seconds. it also does an ORDER BY and a LIMIT. during this time, mysql sucks up hundreds of MB of RAM. the problem is, once it returns the resultset (which is the top-scoring records, up to a few thousand of them), i don't see the RAM getting released. i'm assuming mysql uses the RAM for an im-memory temp table to sort the results... but the ram is never freed up. from that point on, performance in general degrades on the machine, because it starts swapping. any ideas on how i can get mysql to give my ram back when it's done?

 

[KarveR]

it is normal for linux to show very close to 100% memory useage at all times.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[BNPMike]

Is it normal for Linux to reach 100% and then start swapping?

 

[KarveR]

yes , its more efficient to swap stuff to hard disk that hasnt been used for a while, to free up ram for more urgent tasking.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cyoung]

If your disk is swaping a lot, then you may want to consider either adding more memory, or (better option) building another server to handle the load.

If you are trying to run a mySQL server, and any other services on anything other than a test server, then you will always have issues with swapping if the server gets a lot of use.

You have to remember that mySQL in itself, is a server application, and that your app's needs, plus mySQL, plus any other services that are running will use all available ram, plus swap.

Linux is very efficient at allocating memory, and you may find that the majority of memory used is either in buffers or in cache, which allows the OS to retrieve the data faster.

Hope this helps!