0

My new VPS has 4GB of RAM. Before importing database system use about 1.2 GB of RAM. When I import into the database, using 250 MB SQL file, 25000 tables use of RAM goes to 97%, htop showing 3302 mb in cache.

Questions

  • Is this normal ?
  • If not, how to reduce RAM use?
  • 1
    Why would you want to free cache? Linux will automatically allocate the cache to processes when it's needed. – Philᵀᴹ Apr 5 '14 at 15:37
  • I don't want to clean cache. I just don't understand why she use that much cache. It is normal?? I only import 250 MB sql file with 25000 tables. Now htop show : MEM 4096M used 328M buffers: 0M, cache 3594M, when in column MEM% mysql use only 4.6 of RAM. – adar Apr 5 '14 at 17:05
  • You have 3.5G of cache - that's freeable if needed. Don't worry about it. It's normal and good. Completely free RAM is wasted RAM. – Mat Apr 5 '14 at 18:26
1

Yes, this is normal. Linux will use any free memory if needed for cache and buffers - it will be reallocated if needed for anything else (i.e. stating a new program, an existing program needing more memory for something, caching more recently accessed information, or the hypervisor requesting some memory back by inflating the local balloon if you VM is setup that way, and so on).

This is all managed automatically, but if you really want to fully clear it yourself you can by running sync; echo 3 > /proc/sys/vm/drop_caches as root. The only reason I can think of for doing that is if you are running performance tests and want a repeatable "from cold" starting point without completely restarting.

If mysql is using memory mapped files for accessing its data files (which is likely as there can be performance benefits to this method) then any block currently loaded from them will show up as cached data, so as well as the 250Mb input file your ~3G of cache will include things already in memory counted in the "cached" figure before you started, the data and index blocks created as a result of the import, and any related blocks that needed to be read as part of the process.

0

Yes this is normal. When RAM is no longer needed it is not freed at the same time. It is kept as cached in case the server would decide that it needs to access it again. This would save you extra time that you would otherwise need for data to appear in RAM.

Cached memory is freed only when new applications request more RAM.

0

You loaded 25000 tables ? The INFORMATION_SCHEMA has to be taking a little beating. Why ?

All table metadata must be maintained and updated in memory for every table, column and index.

I have discussed this in the past

Please be sure you use all 25000 tables. Otherwise, holding INFORMATION_SCHEMA data on unused tables will take up memory.

BTW I have tried running

select table_name,data_length,index_length
from information_schema.tables
where table_schema='information_schema'
and engine='MEMORY';

Everything comes up 0 bytes. It's funny (and ironic) that INFORMATION_SCHEMA will not tell you what it actually consumes. You will have to measure it in a fuzzy way. I have run this

SELECT
    FORMAT(m,0) MemoryBytes,
    FORMAT(m/power(1024,1),2) MemoryKB,
    FORMAT(m/power(1024,2),2) MemoryMB,
    FORMAT(m/power(1024,3),2) MemoryGB
FROM
(
    SELECT SUM(max_data_length) m
    FROM information_schema.tables
    WHERE table_schema='information_schema'
    AND engine='MEMORY'
) A;

Use this to estimate the maximum amount of memory used for the INFORMATION_SCHEMA. Use half to estimate the average.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.