mysql Handler_read_rnd_next value 值超大（10G+）的处理

mysql Handler_read_rnd_next value 值超大（10G+）的处理

发现问题是在top检查服务器性能时，数据库占内存特别厉害，峰值到100%，很奇怪。

phpmyadmin中发现状态值，红色 ，Handler_read_rnd_next value，特别大，就进mysql，show processlist;，一直刷新，发现 一张表修改特别频繁

然后发现，即使匿名访问网站首页时也会做记录，没必要，修改后，性能立即有很大提高。

 

 

附：

The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

 

Normally when you have sessions stored in Memcache/d there will still be a record of the session written tomdl_sessions. The session data is not written to the database, it's only written to the memcache/d server itself. The performance gain is seen because the session data is not written every page view for every user, and thus the table rows are not continually being locked and unlocked.

But, to check, this is how we do it. Add the following to config.php:

$CFG->dbsessions = false;
$CFG->session_handler_class = '\core\session\memcache';
$CFG->session_memcache_save_path = '192.168.1.2:11211, 192.168.1.3:11211';
$CFG->session_memcache_prefix = 'mdl.sess.key';
$CFG->session_memcache_acquire_lock_timeout = 120;
$CFG->session_memcache_lock_expire = 7200;

We're using the 'memcache' PHP module:

$ php -m | grep mem
memcache

If you're using the php 'memcached' module, and consequently \core\session\memcached Moodle class, the variables to set above are likely different.

 

首先用telnet 127.0.0.1 11211这样的命令连接上memcache，然后直接输入stats就可以得到当前memcache的状态。
这些状态的说明如下：

pid  memcache服务器的进程ID 
uptime  服务器已经运行的秒数 
time  服务器当前的unix时间戳 
version  memcache版本 
pointer_size  当前操作系统的指针大小（32位系统一般是32bit） 
rusage_user  进程的累计用户时间 
rusage_system  进程的累计系统时间 
curr_items  服务器当前存储的items数量 
total_items  从服务器启动以后存储的items总数量 
bytes  当前服务器存储items占用的字节数 
curr_connections  当前打开着的连接数 
total_connections  从服务器启动以后曾经打开过的连接数 
connection_structures  服务器分配的连接构造数 
cmd_get  get命令（获取）总请求次数 
cmd_set  set命令（保存）总请求次数 
get_hits  总命中次数 
get_misses  总未命中次数 
evictions  为获取空闲内存而删除的items数（分配给memcache的空间用满后需要删除旧的items来得到空间分配给新的items） 
bytes_read  总读取字节数（请求字节数） 
bytes_written  总发送字节数（结果字节数） 
limit_maxbytes  分配给memcache的内存大小（字节） 
threads  当前线程数