mysql - 增加MySQL的缓存能力

Question

我的 mysql 只满足读取请求。我想，完全利用缓存是个好主意。我在 VM 中运行 MySQL，这是在 VM 中运行的唯一应用程序。我正在为该虚拟机分配 2GB 内存。我在 VM 上使用 64 位 centos。如果您认为它已经使用了可以使用的最大内存，我还可以为该 VM 分配更多内存。我不太了解 mysql 设置和找出进程使用的内存占用量，但我有兴趣学习如何做。非常感谢您的帮助。

这些是关于我的 MYSQL 的一些信息:

mysql> show global variables like "%cache%";
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                | 
| have_query_cache             | YES                  | 
| key_cache_age_threshold      | 300                  | 
| key_cache_block_size         | 1024                 | 
| key_cache_division_limit     | 100                  | 
| max_binlog_cache_size        | 18446744073709547520 | 
| query_cache_limit            | 1048576              | 
| query_cache_min_res_unit     | 4096                 | 
| query_cache_size             | 0                    | 
| query_cache_type             | ON                   | 
| query_cache_wlock_invalidate | OFF                  | 
| table_cache                  | 64                   | 
| thread_cache_size            | 0                    | 
+------------------------------+----------------------+
13 rows in set (0.00 sec)


mysql> show global variables like "%buffer%";
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| bulk_insert_buffer_size       | 8388608 | 
| innodb_buffer_pool_awe_mem_mb | 0       | 
| innodb_buffer_pool_size       | 8388608 | 
| innodb_log_buffer_size        | 1048576 | 
| join_buffer_size              | 131072  | 
| key_buffer_size               | 8384512 | 
| myisam_sort_buffer_size       | 8388608 | 
| net_buffer_length             | 16384   | 
| preload_buffer_size           | 32768   | 
| read_buffer_size              | 131072  | 
| read_rnd_buffer_size          | 262144  | 
| sort_buffer_size              | 2097144 | 
+-------------------------------+---------+
12 rows in set (0.00 sec)

mysql> show table status where name="items"
    -> ;
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| items | MyISAM |      10 | Dynamic    | 42667 |            346 |    14775916 | 281474976710655 |      1970176 |         0 |         341337 | 2009-07-22 13:31:00 | 2010-10-20 15:37:18 | NULL       | latin1_swedish_ci |     NULL |                |         | 
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

这是我的 ulimit -a 的输出

[sethu@work13 root]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 8191
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 8191
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

如果您需要更多信息，请告诉我。

Answer 1

您可以通过启用查询缓存来快速提升性能!

将此添加到您的 my.cnf:

query_cache_type=1
query_cache_limit=1M
query_cache_size=32M