CentOS虚拟机只使用三分之一的可用内存

显示的缓冲区和caching来自操作系统的angular度。

要查看MySQLcaching的内容，您必须运行以下查询

 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" 

如果你想看看你有多less数据运行这个：

 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; 

这将显示你在MySQL实例中有多lessInnoDB和MyISAM数据。

请记住

• MyISAM索引的总和是您可以为您的实例分配的最大key_buffer_size
• InnoDB数据和索引页的总和是您的实例的最大innodb_buffer_pool_size

有关如何调整key_buffer_size和innodb_buffer_pool_size的更多信息，请在DBA StackExchange中查看我的post 。