Cacti监控MySQL实现过程中碰到的问题解汇总

前言：cacti监控mysql服务器的大概50张graphs都弄出来了，也出图了，当中遇到一些问题，印象比較深刻的记录例如以下：

（一）：加入io监控

点击Create Graphs for this Host 进去创建IO的图，结果报错

This data query returned 0 rows, perhaps there was a problem executing this data query. You can run this data query in debug mode to get more information.

进入*Turn On Graph Debug Mode模式。报错例如以下：

RRDTool Command:

/usr/bin/rrdtool graph -

--imgformat=PNG

--start=-86400

--end=-300

--title='db-m2-slave-1 - Traffic'

--rigid

--base=1000

--height=120

--width=500

--alt-autoscale-max

--lower-limit='0'

--vertical-label='bits per second'

--slope-mode

--font TITLE:10:

--font AXIS:7:

--font LEGEND:8:

--font UNIT:7:

CDEF:cdefa='a,8,*'

AREA:cdefa#00CF00FF:'Inbound'

GPRINT:cdefa:LAST:' Current:%8.2lf %s'

GPRINT:cdefa:AVERAGE:'Average:%8.2lf %s'

GPRINT:cdefa:MAX:'Maximum:%8.2lf %s '

LINE1:cdefa#002A97FF:'Outbound'

GPRINT:cdefa:LAST:'Current:%8.2lf %s'

GPRINT:cdefa:AVERAGE:'Average:%8.2lf %s'

GPRINT:cdefa:MAX:'Maximum:%8.2lf %s '

RRDTool Says:

ERROR: invalid rpn expression in: a,8,*，例如以下图所看到的

编辑linux主机下的/etc/snmp/snmpd.conf文件
找到：com2sec notConfigUser default       public
改动成：com2sec notConfigUser all       public
找到：access notConfigGroup ""      any       noauth    exact systemview none none
改动成：access notConfigGroup ""      any       noauth    exact all none none
找到：#view all    included .1     80把该行的#去掉,
找到：#view mib2   included .iso.org.dod.internet.mgmt.mib-2 fc 把改行的#去掉,
重起snmpd：/etc/init.d/snmpd restart

（二）：MySQL加入主机出不来图

[root@squid-2 test]# service httpd restart

停止 httpd： [确定]

正在启动 httpd：httpd: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1 for ServerName

[确定]

1）进入配置文件文件夹

cd /etc/httpd/conf/

2）编辑httpd.conf文件。搜索"#ServerName"，加入ServerName localhost:80
[root@server conf]# ls
extra httpd.conf magic mime.types original
[root@server conf]# vi httpd.conf
#ServerName www.example.com:80
ServerName localhost:80
3）再又一次启动apache 就可以。

（三）：启动报错

[root@squid-2 error]# tail -f /var/log/httpd/error_log

[Sat May 31 22:49:02 2014] [notice] caught SIGTERM, shutting down

[Sat May 31 22:49:02 2014] [notice] SELinux policy enabled; httpd running as context unconfined_u:system_r:httpd_t:s0

[Sat May 31 22:49:02 2014] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)

[Sat May 31 22:49:02 2014] [notice] Digest: generating secret for digest authentication ...

[Sat May 31 22:49:02 2014] [notice] Digest: done

[Sat May 31 22:49:02 2014] [notice] Apache/2.2.15 (Unix) DAV/2 PHP/5.3.3 configured -- resuming normal operations

解决方法：直接关闭SELinux以及防火墙

（四）：MySQL监控项出图报错

[Sat May 31 23:20:10 2014] [error] [client 192.168.171.71] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 523800 bytes) in /var/www/html/cacti/lib/adodb/adodb.inc.php on line 833

须要导入cacti.sql文件

mysql -u root -p cacti < /var/www/html/cacti/cacti.sql

（五）：SNMP – Interface Statistics报错

创建SNMP – Interface Statistics报错，例如以下：

Created graph: db-m2-slave-2 - Traffic - |query_ifName|

ERROR: no Data Source associated. Check Template

[root@squid-2 html]# snmpwalk -c public -v 2c 10.254.3.73 ifHCInOctets

IF-MIB::ifHCInOctets = No more variables left in this MIB View (It is past the end of the MIB tree)

[root@squid-2 html]#

[root@squid-2 html]# snmpwalk -c public -v 2c 10.254.3.73 if

IF-MIB::ifTable = No Such Object available on this agent at this OID

于是再次改动snmpd.conf,并重新启动snmpd
access notConfigGroup "" any noauth exact systemview none none-->
access notConfigGroup "" any noauth exact all none none

[root@db-m2-slave-2 ~]# service snmpd restart

停止 snmpd： [确定]

正在启动 snmpd： [确定]

[root@db-m2-slave-2 ~]#

[root@squid-2 html]# snmpwalk -c public -v 2c 10.254.3.73 if

IF-MIB::ifTable = No more variables left in this MIB View (It is past the end of the MIB tree)

【】解决

在snmpd.conf配置文件中面，查找下面字段：[/color]

## incl/excl subtree mask

#view all included .1 80

将该行前面的"#"去掉.

之后重新启动snmpd服务解决。

报错（六）：

移除出错的图

进去Consoleà Graph Management àHost(选择出错的主机地址)—>Search(搜索报错的keywordUsed Space)。就会找到报错不出错的Graph Title栏目，勾选右側的全选框，点击Gobutton删除掉这些无效的图就可以。例如以下图所看到的：

然后在新出来的提示界面，点击Continuebutton。删除。

（七）：Memory Free值为nan

分析：memery free 无数据，原因： rrdtool 的内存上限为10G。

[root@squid-2 local]# find / -name *mem*.rrd

/var/www/html/cacti/rra/db-m2-slave-1_mem_buffers_189.rrd

/var/www/html/cacti/rra/db-master-2_mem_free_156.rrd

/var/www/html/cacti/rra/db-m2-slave-1_lock_system_memory_20.rrd

/var/www/html/cacti/rra/db-m2-slave-2_total_mem_alloc_74.rrd

/var/www/html/cacti/rra/db-m2-slave-1_total_mem_alloc_23.rrd

/var/www/html/cacti/rra/db-m2-slave-2_lock_system_memory_71.rrd

/var/www/html/cacti/rra/localhost_mem_swap_4.rrd

/var/www/html/cacti/rra/db-master-2_total_mem_alloc_117.rrd

/var/www/html/cacti/rra/db-master-2_mem_cache_155.rrd

/var/www/html/cacti/rra/db-master-2_mem_buffers_154.rrd

/var/www/html/cacti/rra/db-m2-slave-1_mem_free_191.rrd

/var/www/html/cacti/rra/localhost_mem_buffers_3.rrd

/var/www/html/cacti/rra/db-m2-slave-2_mem_free_164.rrd

/var/www/html/cacti/rra/db-m2-slave-2_mem_buffers_162.rrd

/var/www/html/cacti/rra/db-m2-slave-1_mem_buffers_54.rrd

/var/www/html/cacti/rra/db-m2-slave-1_mem_swap_55.rrd

/var/www/html/cacti/rra/db-master-2_lock_system_memory_114.rrd

/var/www/html/cacti/rra/db-m2-slave-2_mem_cache_163.rrd

/var/www/html/cacti/rra/db-m2-slave-1_mem_cache_190.rrd

/var/www/html/cacti/rra/db-master-2_mem_free_146.rrd

[root@squid-2 local]#

[root@squid-2 local]# rrdtool info /var/www/html/cacti/rra/db-m2-slave-1_mem_free_191.rrd |grep mem_free

filename = "/var/www/html/cacti/rra/db-m2-slave-1_mem_free_191.rrd"

ds[mem_free].type = "GAUGE"

ds[mem_free].minimal_heartbeat = 120

ds[mem_free].min = 0.0000000000e+00

ds[mem_free].max = 1.0000000000e+07

ds[mem_free].last_ds = "34166500"

ds[mem_free].value = NaN

ds[mem_free].unknown_sec = 2

[root@squid-2 local]#

注：ds[mem_free].max = 1.0000000000e+07 数据的最大值设置为10G

查看rrdtool怎样进行改动。运行—help查看：

[root@squid-2 local]# rrdtool --help

Compiled Aug 21 2010 10:57:18

Usage: rrdtool [options] command command_options

Valid commands: create, update, updatev, graph, graphv, dump, restore,

last, lastupdate, first, info, fetch, tune,

resize, xport

RRDtool is distributed under the Terms of the GNU General

Public License Version 2. (www.gnu.org/copyleft/gpl.html)

For more information read the RRD manpages

[root@squid-2 local]#

採用tune命令參数进行改动：

[root@squid-2 rra]# rrdtool tune *_mem_free_*.rrd mem_free:100000000

DS[mem_free] typ: GAUGE hbt: 120 min: 0.0000 max: 10000000.0000

[root@squid-2 rra]#

有提示信息，表名tune失败，原来少了个-a參数。又一次改动例如以下：

[root@squid-2 rra]# rrdtool tune *_mem_cache_*.rrd -a mem_cache:3000000000

[root@squid-2 rra]# rrdtool tune *_mem_free_*.rrd -a mem_free:3000000000

[root@squid-2 rra]# rrdtool tune *_mem_buffers_*.rrd -a mem_buffers:3000000000

[root@squid-2 rra]#

这里发现rrdtool运行之后。仅仅有一个host主机的的nan变成数字。其它主机的都没有变，之所以如此是由于rrdtool tune * -a …命令仅仅有一个.rrd文件起作用。其余的须要自己手动再一次次运行rrdtool tune命令。

为了简化操作。特意写了一个ssh脚本例如以下：

vim /root/rrdtool_increate_mem.sh
cd /var/www/html/cacti/rra
ls *_mem_free_*.rrd -1 >a_mem_free.txt
for i in `cat a_mem_free.txt`
do
	rrdtool tune $i -a mem_free:300000000;
done;

ls *_mem_cache_*.rrd -1 >a_mem_cache.txt
for i in `cat a_mem_cache.txt`
do
	rrdtool tune $i -a mem_cache:300000000;
done;

ls *_mem_buffers_*.rrd -1 >a_mem_buffers.txt
for i in `cat a_mem_buffers.txt`
do
	rrdtool tune $i -a mem_buffers:300000000;
done;

直接sh /root/rrdtool_increate_mem.sh就可以。

【补充】

调试cacti的graph，过程例如以下：

(1) Console ，再进入Graph Manager ,再进入，选择对于的Host，搜索Memory，选中你要的图，点击链接。比方我这里是Memory Usage，例如以下图所看到的：