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 当前线程数