using cacti to graph mysql’s metrics - percona · pdf fileusing cacti to graph...

Using Cacti To Graph MySQL’s MetricsKenny [email protected] Consultant @ Percona

Collaborate 2011

1

mailto:[email protected]

mailto:[email protected]

Percona

•MySQL/LAMP Consulting•MySQL Support•Percona Server (XtraDB)•Percona XtraBackup• InnoDB Recovery Toolkit, tcprstat•(maatkit, innotop, aspersa, mysql-mmm, mysql-cacti-

templates) • ...•http://www.percona.com•http://www.mysqlperformanceblog.com

2

http://www.percona.com


http://www.mysqlperformanceblog.com


Topics

•Why Graph?•What is Cacti and mysql-cacti-templates?•How Cacti works• Installation•Add Graphs•Demo•Graphs!•Extra’s

3

Topics


4

Why Graph?

•Troubleshooting-behavior over time-sudden spikes in graphs after application updates

•Capacity Planning-traffic patterns-predictions

•Not only for Operations, also for Development and Management

5

Topics


6

Cacti?

•open source graphing solution•uses RRDTool•advanced graphs, scriptable•web interface•quite complex to add graphs•adding servers is a manual process

7

mysql-cacti-templates?

•collection of templates for cacti•graph MySQL metrics•Has a lot of non-MySQL Metrics too: IO stats,

apache, memcache, mongodb, java...

8

Topics


9

How does Cacti work?

10

Database Servermysqld

sshd

snmpd

/proc/diskstats

Cacti

MySQL(contains cacti configuration)

.rrd Files(contains rrd data)

Apache(serves web

interface)

Poller(cronjob which

fetches data from servers)

Web Serverhttpd

sshd

snmpd

Web Browser

what

stores

fetch

df

Topics


11

Installation

•Cacti•Mysql-cacti-templates•Configuring Servers

12

Installation


13

Setting up Cacti: Requirements

•httpd•php, php-mysql, php-snmp•mysql-server (server and client utilities)•net-snmp (snmpget)•rrdtool

14

Setting up Cacti: Installation

• Install rpm/deb/... package or extract yourself•Configure webserver: php5, cacti scripts•Create cacti mysql user• Import cacti database•Edit configuration•Setup cronjob for poller•Finish installation using your webbrowser

15

Setup: Extract

•Recommended is using apt/yum/... to install cacti• if not:

16

cacti# cd /var/www/html/ # may varycacti# tar xzvf cacti-0.8.7g.tar.gzcacti# ln -s cacti-0.8.7g cacticacti# chown -R cactiuser rra/ log/

Setup: Webserver

•Apache (may be something else)•Enable php•Set DirectoryIndex index.php

17

LoadModule php5_module modules/libphp5.soAddHandler php5-script .phpAddType text/html .phpDirectoryIndex index.php

Setup: MySQL

•Create Cacti MySQL user:

• Import Cacti Schema

•Edit include/config.php

18

cactimysql> GRANT ALL PRIVILEGES ON cacti.* TO cactiuser@localhost IDENTIFIED BY 'password';

cacti# mysql -e ‘CREATE DATABASE cacti;’cacti# mysql cacti < cacti.sql

$database_type = "mysql";$database_default = "cacti";$database_hostname = "localhost";$database_username = "cactiuser";$database_password = "password";

Setup: Poller

•Add cronjob (/etc/cron.d/cacti)

19

*/5 * * * * cactiuser php /var/www/html/cacti/poller.php > /dev/null 2>&1

Setup: UI

20

Setup: UI

21

Setup: UI

22

Setup: UI

23

Setup: UI

24

Setup: UI

25

Setup: UI

26

Installation


27

download & installation

28

cacti# wget http://mysql-cacti-templates.googlecode.com/files/better-cacti-templates-1.1.8.tar.gzcacti# tar -xzf better-cacti-templates-1.1.8.tar.gz cacti# cd better-cacti-templates-1.1.8/cacti# cp \ scripts/{ss_get_mysql_stats.php,ss_get_by_ssh.php}\ /var/www/html/cacti/scripts/

http://mysql-cacti-templates.googlecode.com/files/better-cacti-templates-1.1.8.tar.gz






install: mysql script

29

cacti# vi /var/www/html/cacti/scripts/\ss_get_mysql_stats.php ...$mysql_user = 'cactiuser';$mysql_pass = 'password';...

install: generate ssh key

30

cacti# mkdir /etc/cacticacti# ssh-keygen Generating public/private rsa key pair.Enter file in which to save the key (/root/.ssh/id_rsa): /etc/cacti/id_rsaEnter passphrase (empty for no passphrase): Your identification has been saved in /etc/cacti/id_rsa.Your public key has been saved in /etc/cacti/id_rsa.pub.cacti# chown -R www-data: /etc/cacti/{id_rsa,id_rsa.pub}cacti# chmod -R 0400 /etc/cacti/id_rsa

install: ssh script

31

cacti# vi /var/www/html/cacti/scripts/ss_get_by_ssh.php ...$ssh_user= 'cacti'; # SSH username$ssh_port= 22; # SSH port$ssh_iden='-i /etc/cacti/id_rsa';# SSH identity...

install: templates

32

install: templates

33

install: templates

34

Installation


35

Configure Servers

•configure net-snmpd•add a cacti user in MySQL•configure ssh access

36

Configure Servers: snmpd

• Install snmpd

•Make sure snmpd listens on the network and that a community password is set:

•Verify on the cacti server if snmp is working remotely

37

server# apt-get install snmpd

server# vi /etc/snmp/snmpd.conf:agentAddress udp:161rocommunity publicserver# /etc/init.d/snmpd restart

cacti# snmpget -v2c -c public ubuntu-server-test iso.3.6.1.2.1.1.5.0

iso.3.6.1.2.1.1.5.0=STRING:"ubuntu-server-test"

Configure Servers: mysql user

•Add cacti user

•Verify if login works from cacti servers

38

servermysql> GRANT SUPER, PROCESS ON *.* TO 'cactiuser'@'cactiserver' IDENTIFIED BY "password";

cacti# mysql -h ubuntu-server-test -e "SELECT VERSION()" -u cactiuser -ppassword+-------------------+| VERSION() |+-------------------+| 5.1.49-1ubuntu8.1 |+-------------------+

Configure Servers: ssh access

•Add shell user cacti

•Copy the public key

•Verify

39

server# useradd cactiserver# getent passwd cacticacti:x:1001:1002::/home/cacti:/bin/sh

server# mkdir /home/cacti/.ssh/server# cat > /home/cacti/.ssh/authorized_keys << EOFssh-rsa AAA...r2E7z5 root@ubuntu-server-testEOFserver# chmod 0400 /home/cacti/.ssh/authorized_keys

cacti# ssh -i /etc/cacti/id_rsa cacti@ubuntu-server-test

Topics


40

Adding Graphs

41

Adding Graphs

42

Adding Graphs

43

Adding Graphs

44

•Apply wanted templates on the device- MySQL & GNU/Linux templates

•Click on Create Graphs:

•Select graphs to create and Add!•Might ask some more questions

Adding Graphs

45

Adding Graphs

46

•Assign ucd/net SNMP template to have:-cpu usage/load-memory used/available-network statistics-free disk space

•Different way of creating

Adding Graphs

47

Adding Graphs

48

•What if I have multiple disk devices to monitor?-Go to ‘Create graphs for this host’-Select graph to add in select menu-Create and fill in other device when asked

Adding Graphs: Graph Trees

49

•Tree to browse through graphs•By host/individual graph, mixed

Topics


50

Topics


51

MySQL Handlers

52

MySQL Handlers

53

Table scans, backup?

MySQL Handlers

54

MySQL Handlers

55

Take backup here

MySQL Connections

56

MySQL Connections

57

max_connections not reached yet

Why more incoming connections?

MySQL Replication

58

MySQL Replication

59

Replication was stopped

Replication never caught up

MySQL Replication

60

MySQL Replication

61

In 2 hours, almost 6900 seconds replication lag

MySQL Temporary Objects

62

MySQL Temporary Objects

63

keep an eye on:* temp tables* temp disk tablesif it grows > query optimization

MySQL Select Types

64

MySQL Select Types

65

Why this sudden peak in rangescans?

MySQL Select Types

66

MySQL Select Types

67

Query behavior changed?Other explain plan chosen?

MySQL Sorts

68

MySQL Sorts

69

The peaks should be understood, not necessarily be removed as they might be really fast in terms of rows

MySQL Command Counters

70

MySQL Binary Logs

71

MySQL Binary Logs

72

expire_logs_days

Response Time Distribution

73

+----------------+-------+------------+| time | count | total |+----------------+-------+------------|| 0.000001 | 0 | 0.000000 || 0.000010 | 17 | 0.000094 || 0.000100 | 4301 | 0.236555 || 0.001000 | 1499 | 0.824450 || 0.010000 | 14851 | 81.680502 || 0.100000 | 8066 | 443.635693 || 1.000000 | 0 | 0.000000 || 10.000000 | 0 | 0.000000 || 100.000000 | 1 | 55.937094 || 1000.000000 | 0 | 0.000000 || 10000.000000 | 0 | 0.000000 || 100000.000000 | 0 | 0.000000 || 1000000.000000 | 0 | 0.000000 || TOO LONG QUERY | 0 | 0.000000 |+----------------+-------+------------+

MySQL Query Response Time

74

MySQL Query Response Time

75

InnoDB Buffer Pool

76

InnoDB Buffer Pool

77

DB Restart

Time to fill buffer

InnoDB Checkpoint Age

78

InnoDB Checkpoint Age

79

sudden increase in unflushed writes

db restart, no write flushing

InnoDB Buffer Pool Activity

80

InnoDB I/O

81

InnoDB I/O

82

More read requests Increase in

load

InnoDB Row Operations

83

InnoDB Row Operations

84

Good view on capacity of the system

86

more row reads,coming from disk

87

this spike in IO readsdid not cause more disk reads

89looking at amount of statements does not always show load

CPU Usage

90

Memory

91

Disk Operations

92

Topics


93

non-MySQL mysql-cacti-templates

•Apache•JMX•Memcached•Mongodb•Nginx•OpenVZ•Redis•Unix

94


95


96


97


98

Other Graphing solutions

•Munin and munin-mysql•OpenNMS and MySQL-SNMP•Zabbix with appaloosa-zabbix-templates

99

Creating Your Own Graphs

•Creating cacti graphs not trivial/portable•mysql-cacti-templates to the rescue!

http://code.google.com/p/mysql-cacti-templates/wiki/CreatingGraphs

100





Using Cacti To Graph MySQL’s Metrics

•Powerful Tool•Easy to install•Open source!

101

Using Cacti To Graph MySQL’s Metrics

•Cacti: http://www.cacti.net/•RRDtool: http://oss.oetiker.ch/rrdtool/•mysql-cacti-templates:

http://code.google.com/p/mysql-cacti-templates/• IRC: Freenode #percona•Mailinglist:

http://groups.google.com/group/percona-discussion•http://www.percona.com•http://www.mysqlperformanceblog.com

102

http://www.cacti.net/%5D

http://www.cacti.net/%5D

http://oss.oetiker.ch/rrdtool/

http://oss.oetiker.ch/rrdtool/

http://code.google.com/p/mysql-cacti-templates/




http://groups.google.com/group/percona-discussion

http://groups.google.com/group/percona-discussion





Percona Live MySQL Conference NYCMay 26, 2011

http://www.percona.com/live/nyc-2011/

103



using cacti to graph mysql’s metrics - percona · pdf fileusing cacti to graph...

Documents