Graphora

I knew I had to try out Neto’s  Graphora when I saw that it was a docker container to collect Oracle performance data and send it to Graphite. Graphite has been on my radar as I see and hear about more and more companies using it to graph and monitor performance data.  Any graphical Oracle performance tool interests me and recently I’ve been spending some time investigating Docker so having everything rolled up in Graphora sounded perfect.

To run Graphora you need docker. It’s easy enough to do a yum install docker, but I decided to use a VM with docker already installed. The VM image I used was from Delphix Express Landshark environment and you can use this image as well if you’d like. Of the three machine images that come withDelphix Express Landshark environment , I  used the source machine image.

The basic architecture is Graphora docker container connects to a running Oracle database, collects I/O statistics and sends them to graphite running in a docker container.

Start Docker

# service docker start

Starting cgconfig service:  [ OK ]

Starting docker:  [ OK ]

Pull Graphora docker container image

# docker pull netofrombrazil/graphora

latest: Pulling from netofrombrazil/graphora

Enable sys connections over SQL*Net

$ORACLE_HOME/bin/./orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=change_on_install

otherwise getting error

ORA-01031: insufficient privileges

on the following command when trying to create the graphora user

Create graphora user

Then run

# docker run -ti netofrombrazil/graphora --host 172.16.100.11 --port 1521 --sid XEXE --create

Enter sys password: change_on_install

Creating user graphora

Grant access for user graphora to create sessions

Grant select privilege on V$SESSION_EVENT, V$SYSSTAT, V$STATNAME for user graphora

GraphOra is ready to collect your performance data!

I’m creating the  graphora user on my “source” landshark VM which has an ip of 172.16.100.11. It’s sid is XEXE.  The flag “-it” means interactive (-i) and allocate a tty (-t).

Startup Graphite

The “-p” flags map ports in the container to ports on the host running the container.

Graphite uses these ports

1. 80: the graphite web interface

2. 2003: the carbon-cache line receiver (the standard graphite protocol)

3. 2004: the carbon-cache pickle receiver

4. 7002: the carbon-cache query port (used by the web interface)

So the command is

docker run -p 80:80 -p 2003:2003 -p 2004:2004 -p 7002:7002 -d nickstenning/graphite

Now you can access graphite at the URL of the machine. It looks like

Startup Graphora

Now the big moment – startup graphora sending performance data from my XEXE database to graphite

# docker run -ti netofrombrazil/graphora \

              --host 172.16.100.11 \
              --port 1521 \
              --sid XEXE \
              --interval 10 \
              --graphite graphs-db \
              --graph-port 2003
 phyReads: 0 phyWrites: 0 dbfsr: 0.00 lfpw: 0.00
 Could not connect to graphs-db on port 2003.

whoops, what went wrong ?

What went wrong

First thing to do was see if graphite was actually working correctly. I decided to send data to graphite with netcat

export PORT=20003

export SERVER=172.16.100.11

echo "local.random.diceroll 4 `date +%s`" | nc ${SERVER} ${PORT}

And yes, this works. I can see a new “local” category of data in the graphite web interface. Graphite is working correctly so the problem must be in how I all graphora.

Having never used graphite before nor graphora, I wasn’t sure what all the command line options were and I got one of the arguments wrong, but how could I find out what the command line arguments were? They aren’t outlined on Neto’s blog. They aren’t on the docker download page https://hub.docker.com/r/netofrombrazil/graphora/

So now I dug around to try and find out what is in Neto’s package. I discovered the command

docker diff [name of container]

 to show what new and modified files there are in the container.

# docker diff graphora

C /opt
C /opt/graphOra
C /opt/graphOra/graphOra
C /opt/graphOra/lib
C /opt/graphOra/lib/database.pyc

Not that many files in the container. Question “how do I see these files that are inside the container?” In order to get to the file inside the container,  I have to exec a bash shell into the container (or know where the actual mapped files are)

docker exec -i -t graphora bash

This will give me a shell in the container and I can now vi the files. I then vi graphora file

# vi /opt/graphOra/graphOra

and find the command line arguments

def cli_args():

 parser.add_argument('--host', required=True, help='Oracle Database server')

 parser.add_argument('--port', required=True, help='Database listener port')

 parser.add_argument('--sid', required=True, help='Database SID')

 parser.add_argument('--user', default='graphora', help='User to connect into the database')

 parser.add_argument('--password', default='WeLoveThisCompany', help='user db password')

 group = parser.add_mutually_exclusive_group()

 group.add_argument('--create', action='store_true', help='Setup graphOra to monitor the database instance')

 group.add_argument('--interval', default=10, type=int, help='Interval time in seconds between each data collection')

 parser.add_argument('--graphite', default=' ', help='Specify IP or Hostname of Graphite Server')

 parser.add_argument('--graph-port', type=int, help='Specify Graphite Server port')

 parser.add_argument('--print-stats', default='true', choices=['true','false'], help='Print stats')

 parser.add_argument('--project', default='oracle', help='Specify a string to identify your data in Grafana')

Getting it right

My problem is that I thought “–graphite” was possibly the default name of graphite database. I was thinking since graphite collects data maybe it has it’s own database with a default name graphite-db which I saw in Neto’s command line example. Well turns out as we can see above that “–graphite” is the IP or host name of the host running graphite. In my case everything, the Oracle XE database, graphite and graphora are running on my one machine, 172.16.100.11. With that change it now works:

# docker run -ti netofrombrazil/graphora \ 
              --host 172.16.100.11 \
              --port 1521 \
              --sid XEXE \
              --interval 10 \
              --graphite 172.16.100.11 \ 
              --graph-port 2003

I can then plot the values. On the top left of the Graphite interface I can click  on “netapp” and continue expanding until I get to the I/O metrics.

I can then click on any of the metrics to see the graph.

If I click on the “dashboard” menu item in the top right, I can click on the metrics and get a graph for each metric building a dashboard,

Choose metrics by clicking on the names in top right. Each time you a choose a name, a list of next level list of names will be shown until you choose the actual metric.

The UI might be a little more intuitive in the tree mode. To change to tree mode, select “Dashboard” then “Configure UI”

Then select “Tree”

Now the interface looks like

Grafana

Looking at Neto’s blog, his graph UI looked much different and nicer than the graphite graphs. I poked around and it looks like his graph was a grafana graph. Grafana works with graphite and there is a docker version of it, so I started it up:

docker run -i -p 3000:3000 grafana/grafana

Now I can access grafana on my machine at port 3000. Go to the URL  your_machine:3000 and you will see a login page

login with admin/admin

If you don’t see the menus on the left then click the Grafana icon in the top right and the menus should appear. First step is to add the graphite data source. Click on “Data Source” on the right

Then click “Add new” in the top right.

Main thing is to add the URL for graphite which in my case is simple the IP of my machine, 172.16.100.11

Name the data source “graphora” in this example.

Now click on “home” on the tip menu and then “+New” on the bottom buttons

Click on the green line in the top middle and choose “Add Panel -> Graph”

Set the data source in the bottom left for the dashboard to be “graphora” data

Then click “select metric” in the middle of the screen just below the “metric” tab label.

When you click “select metric” you will see the metrics collected by graphora which all start out with the prefix “netapp”

You have to choose the full precision of the name like

netapp perf oracle 172 16 100 11 XEXE dbfsr_latency

but for the other metrics, you can choose duplicate and then change the value of the last field to go quicker

With all 5 metrics choose my dashboard looks like

Summary and Reflections

The whole combination of technologies is interesting – Docker, Oracle, Graphora,  Graphite, Grafana etc.

In this particular case with Graphora I wonder if it would just be easier to distribute the 2 files necessary to run Graphora instead of a Docker container. I don’t know. Docker is cool. Docker makes a lot of sense for things that have many files and things that I want to run multiple concurrent versions of without having the different versions interfere, but is it overkill for a couple of python files? It’s nice that with docker, one  doesn’t have to have any download or install instructions. It’s as simple as “docker run container”.

When it comes to data collection and graphing, it’s attractive that these packages take care of so much of the scripting and plumbing.  For example when it comes to collecting data, I tend to either stuff it in flat files or stuff it in Oracle. Neither of these approaches is great. Flat files are inefficient and require a lot of coding. Oracle his huge and makes deployment a bear if one depends on it. WIth graphite it’s great that it comes with aggregation routines and purging routines.

When it comes to graphing, I’ve been completely happy with Highcharts though again if using Highcharts I have to create my own HTML files with javascript etc where as in graphite an grafana, it’s just point and click. On the other hand a lot of the optimizations, aggregation, graph parent child relationships, and custom displays seem difficult to impossible with these packages.

All in all, quite intriguing. I guess if I want to get going fast, then graphite might be a good way to display a bunch of statistics in line graphs. On the other hand, the problem  that I see missed routinely in the industry is that creating performance dashboards is a lot more then pasting up a bunch of line graphs. Data visualization, in order to be powerful, needs a lot of intelligence in what metrics are chosen, how they are aggregated, correlated and displayed. Often the display requires a customization and  new approaches to displaying data visually. What I want to see more are  pre-set dashboards that intelligently layout and correlate data for particular use cases. Use cases in the realm of performance monitoring of IT systems tend to cover the same types of situation – where are you spending time, how much CPU, I/O, concurrency contention etc.

Cool discovery with Docker is that to get a list of files created or modified by a docker container run

docker diff container_name

Then I can get into the container with exec bash to look at or modify these files

docker exec -i -t graphora bash

If I modify the files then I can then restart the container

docker restart graphora

To see the docker containers that exist and/or are running, use the command

docker ps -a

Reference

Graphite uses Whisper database default

Graphite vs Grafana as well as storage database options such as InfluxDB and Cyanite (Cassandra)