Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a VM slice running CentOS, on Parallels.

Here is the output of free -m

[root@server ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           960        272        687          0          0          0
-/+ buffers/cache:        272        687
Swap:            0          0          0

This is a LAMP server, and the database itself is over 1000 MB.

Shouldn't the "buffers" and "cached" be showing higher values, caching the database files? It looks like only 1/3 of memory is being used.

share|improve this question
    
What database? What makes you think it should be cached? –  Michael Hampton Sep 11 '12 at 10:48
    
MySQL, shouldn't Linux cache files in memory, if there is free memory? –  soupagain Sep 11 '12 at 12:03

1 Answer 1

The buffers and caches displayed are from the point-of-view of the OS.

To see what MySQL is caching you must run the following queries

MYSQL_CONN="-uroot -prootpassword"
BPSIZE=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'" | awk '{print $2}'`
BPDATA=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data'" | awk '{print $2}'`
BPPAGE=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL STATUS LIKE 'Innodb_page_size'" | awk '{print $2}'`
BPDATABYTES=`echo ${BPDATA}*${BPPAGE}|bc`
BPPCTUSED=`echo ${BPDATABYTES}00/${BPSIZE}|bc`
KBSIZE=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'" | awk '{print $2}'`
KBUSED=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL STATUS LIKE 'Key_blocks_used'" | awk '{print $2}'`
KCSIZE=`mysql ${MYSQL_CONN} -ANe"SHOW GLOBAL STATUS LIKE 'key_cache_block_size'" | awk '{print $2}'`
KBDATABYTES=`echo ${KBUSED}*${KCSIZE}|bc`
KBPCTUSED=`echo ${KBDATABYTES}00/${KBSIZE}|bc`
echo "InnoDB Buffer Pool Size : ${BPSIZE}, (${BPPCTUSED}) Percent Full"
echo "MyISAM Key Buffer Size : ${KBSIZE}, (${KBPCTUSED}) Percent Full"

If you want to see how much data you actually have run this:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A
ORDER BY TSize;

This will show how much InnoDB and MyISAM data you have in the MySQL Instance.

Please keep in mind

  • the sum of MyISAM Indexes is the Maximum key_buffer_size you can allocate for your instance
  • the sum of InnoDB Data and Index Pages is the Maximum innodb_buffer_pool_size for your instance

For more info on how to size the key_buffer_size and innodb_buffer_pool_size, please see my post in the DBA StackExchange.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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