Using Cacti To Graph MySQL s Metrics

(1)

Using Cacti To Graph MySQL’s Metrics

Kenny Gryp

[email protected]

Principal Consultant @ Percona

Collaborate 2011

(2)

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

(3)

Topics

•

Why Graph?

•

What is Cacti and mysql-cacti-templates?

•

How Cacti works

(4)

Topics

•

Why Graph?

•

How Cacti works

(5)

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

(6)

Topics

•

Why Graph?

•

What is Cacti and mysql-cacti-templates?

•

How Cacti works

(7)

Cacti?

•

open source graphing solution

•

uses RRDTool

•

advanced graphs, scriptable

•

web interface

•

quite complex to add graphs

•

adding servers is a manual process

(8)

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...

(9)

Topics

•

Why Graph?

•

How Cacti works

(10)

How does Cacti work?

10 Database Server mysqld sshd snmpd /proc/ diskstats Cacti MySQL (contains cacti configuration) .rrd Files (contains rrd data) Apache (serves web interface) Poller (cronjob which fetches data from

(11)

Topics

•

Why Graph?

•

How Cacti works

(12)

Installation

•

Cacti

•

Mysql-cacti-templates

•

Configuring Servers

(13)

Installation

•

Cacti

•

Configuring Servers

(14)

Setting up Cacti: Requirements

•

httpd

•

php, php-mysql, php-snmp

•

mysql-server (server and client utilities)

•

net-snmp (snmpget)

•

rrdtool

(15)

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

(16)

Setup: Extract

•

Recommended is using apt/yum/... to install cacti

•

if not:

16

cacti# cd /var/www/html/ # may vary cacti# tar xzvf cacti-0.8.7g.tar.gz cacti# ln -s cacti-0.8.7g cacti

(17)

Setup: Webserver

•

Apache (may be something else)

•

Enable php

•

Set DirectoryIndex index.php

17

LoadModule php5_module modules/libphp5.so AddHandler php5-script .php

(18)

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";

(19)

Setup: Poller

•

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

19

(20)

Setup: UI

(21)

Setup: UI

(22)

Setup: UI

(23)

Setup: UI

(24)

Setup: UI

(25)

Setup: UI

(26)

Setup: UI

(27)

Installation

•

Cacti

•

Mysql-cacti-templates

•

Configuring Servers

(28)

download & installation

28

cacti# wget

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

cacti# tar -xzf better-cacti-templates-1.1.8.tar.gz cacti# cd better-cacti-templates-1.1.8/

cacti# cp \

(29)

(30)

install: generate ssh key

30

cacti# mkdir /etc/cacti cacti# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/ id_rsa): /etc/cacti/id_rsa

Enter 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}

(31)

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

(32)

install: templates

(33)

install: templates

(34)

install: templates

(35)

Installation

•

Cacti

•

Configuring Servers

(36)

Configure Servers

•

configure net-snmpd

•

add a cacti user in MySQL

•

configure ssh access

(37)

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:161

rocommunity public

server# /etc/init.d/snmpd restart

cacti# snmpget -v2c -c public

ubuntu-server-test iso.3.6.1.2.1.1.5.0

(38)

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

(39)

Configure Servers: ssh access

•

Add shell user cacti

•

Copy the public key

•

Verify

39

server# useradd cacti

server# getent passwd cacti

cacti:x:1001:1002::/home/cacti:/bin/sh server# mkdir /home/cacti/.ssh/

server# cat > /home/cacti/.ssh/authorized_keys <<

EOF

ssh-rsa AAA...r2E7z5 root@ubuntu-server-test EOF

server# chmod 0400 /home/cacti/.ssh/authorized_keys cacti# ssh -i /etc/cacti/id_rsa

(40)

Topics

•

Why Graph?

•

How Cacti works

(41)

Adding Graphs

(42)

Adding Graphs

(43)

Adding Graphs

(44)

Adding Graphs

44

•

Apply wanted templates on the device

-

MySQL & GNU/Linux templates

•

Click on Create Graphs:

•

Select graphs to create and Add!

(45)

Adding Graphs

(46)

Adding Graphs

46

•

Assign ucd/net SNMP template to have:

-

cpu usage/load

-

memory used/available

-

network statistics

-

free disk space

(47)

Adding Graphs

(48)

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

(49)

Adding Graphs: Graph Trees

49

•

Tree to browse through graphs

(50)

Topics

•

Why Graph?

•

How Cacti works

(51)

Topics

•

Why Graph?

•

How Cacti works

(52)

MySQL Handlers

(53)

MySQL Handlers

53

(54)

MySQL Handlers

(55)

MySQL Handlers

55

(56)

MySQL Connections

(57)

MySQL Connections

57

max_connections not reached yet

(58)

MySQL Replication

(59)

MySQL Replication

59

(60)

MySQL Replication

(61)

MySQL Replication

61

(62)

MySQL Temporary Objects

(63)

MySQL Temporary Objects

63

keep an eye on: * temp tables

* temp disk tables

(64)

MySQL Select Types

(65)

MySQL Select Types

65

(66)

MySQL Select Types

(67)

MySQL Select Types

67

(68)

MySQL Sorts

(69)

MySQL Sorts

69

(70)

MySQL Command Counters

(71)

MySQL Binary Logs

(72)

MySQL Binary Logs

72

(73)

Response Time Distribution

73

(74)

MySQL Query Response Time

(75)

MySQL Query Response Time

(76)

InnoDB Buffer Pool

(77)

InnoDB Buffer Pool

77

DB Restart

(78)

InnoDB Checkpoint Age

(79)

(80)

InnoDB Buffer Pool Activity

(81)

InnoDB I/O

(82)

InnoDB I/O

82

InnoDB Row Operations

(84)

InnoDB Row Operations

84

(85)

(86)

86

(87)

87

(88)

(89)

89

(90)

CPU Usage

(91)

Memory

(92)

Disk Operations

(93)

Topics

•

Why Graph?

•

How Cacti works

(94)

(95)

non-MySQL mysql-cacti-templates

(96)

non-MySQL mysql-cacti-templates

(97)

non-MySQL mysql-cacti-templates

(98)

non-MySQL mysql-cacti-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

(101)

Using Cacti To Graph MySQL’s Metrics

•

Powerful Tool

•

Easy to install

•

Open source!

(102)

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

(103)

Percona Live MySQL Conference NYC May 26, 2011

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