☰

mysql缓存问题

tag-icon • tag-icon mysql cache
mysql缓存问题

是否存在缓存过多的情况？我有一台 2GB RAM 的服务器。

我运营着以下 2-3 个网站：

[mysqld]
set-variable = max_connections=50
log-slow-queries
safe-show-database
skip-networking
skip-name-resolve
set-variable=local-infile=0
local-infile=0
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
table_cache=1024
table_definition_cache=512
max_heap_table_size=512
tmp_table_size=32M
max_heap_table_size=32M
thread_cache=32
key_buffer=64M
read_buffer_size=1M
sort_buffer_size=1M
myisam_sort_buffer_size=64M
thread_concurrency=4
interactive_timeout=100
wait_timeout=15
connect_timeout=10
long_query_time=1
flush_time=3600

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

。

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| InnoDB         |             0.188 MB |             0.250 MB |             0.438 MB |
| MyISAM         |           181.114 MB |            61.270 MB |           242.384 MB |
| Total          |           181.302 MB |            61.520 MB |           242.821 MB |
+----------------+----------------------+----------------------+----------------------+

答案1

我们需要知道 MyISAM 和 InnoDB 有多少数据

以 MB 为单位发送此查询的结果。

SELECT IFNULL(B.engine,'Total') "存储引擎", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "数据大小", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "索引大小", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "表大小" FROM (SELECT引擎，SUM（数据长度）DSize，SUM（索引长度）ISize，SUM（数据长度+索引长度）TSize FROM information_schema.tables WHERE table_schema NOT IN（'mysql'，'information_schema'）AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B，（SELECT 2 pw）A ORDER BY TSize;

相关内容