Load MySQL data into graphite

Asked by Zee on 2017-07-26

I have a working graphite-carbon set up on ubuntu. I was able to insert data one line at a time using the command line
echo "local.random.diceroll 4 `date +%s`" | nc -q0 ${SERVER} ${PORT}
But I have about 2M rows to load into graphite from a mySQL database.
Whats the best tool or approach for this requirement?

Question information

Language:
English Edit question
Status:
Solved
For:
Graphite Edit question
Assignee:
No assignee Edit question
Solved by:
Denis Zhdanov
Solved:
2017-07-31
Last query:
2017-07-31
Last reply:
2017-07-27
Denis Zhdanov (deniszhdanov) said : #1

I don't know any ready-to-use tooling for that, so, you need to write a script on e.g. python to prepare and insert data. But in general, it's the same idea - get data, form a proper line for graphite, send it to port 2003 with "\n" at the end, go to next. You can parallelize it if needed, but in general 2M rows is not that big.
You can also directly put data to whisper - but it will take more efforts and knowledge.

Zee (spreez) said : #2

Thank you. If I have a follow-up question on Python usage with the graphitesend tool, is that a separate question or can it be asked in the same thread?

Denis Zhdanov (deniszhdanov) said : #3

Up to you. IMO it's still relevant.

Zee (spreez) said : #4

I'm trying to test grapgitesend with this example https://github.com/daniellawrence/graphitesend/blob/master/examples/proc_loadavg.py
so copied the code into a python file and trying to execute it

root@d19052-v19052:/# python3 proc_loadavg.py
Traceback (most recent call last):
  File "proc_loadavg.py", line 3, in <module>
    import graphitesend
ImportError: No module named 'graphitesend'
root@d19052-v19052:/# graphitesend name.of.the.metric 666
Traceback (most recent call last):
  File "/usr/local/bin/graphitesend", line 11, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python2.7/dist-packages/graphitesend/graphitesend.py", line 616, in cli
    graphitesend_instance = init()
  File "/usr/local/lib/python2.7/dist-packages/graphitesend/graphitesend.py", line 543, in init
    _module_instance = GraphiteClient(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/graphitesend/graphitesend.py", line 112, in __init__
    self.connect()
  File "/usr/local/lib/python2.7/dist-packages/graphitesend/graphitesend.py", line 162, in connect
    "No address associated with hostname %s:%s" % self.addr)
graphitesend.graphitesend.GraphiteSendException: No address associated with hostname graphite:2003
root@d19052-v19052:/# No address associated with hostname graphite:2003^C

Any idea what I'm missing. the only non-default thing I did while installing graphite was used Postgres database for Django instead of the default SQLite3

Denis Zhdanov (deniszhdanov) said : #5

You forgot to initialize graphitsend library. Try instead of

g = graphitesend.init(group='loadavg_', suffix='min')

use

g = graphitesend.init(group='loadavg_', suffix='min',graphite_server='XXXXXX',graphite_port=YYYYY)

Put your graphite and port instead of XXXXXXX and YYYYY respectively.

Zee (spreez) said : #6

I changed it, my new code looks like this

#!/usr/bin/env python
import time
import graphitesend
# comments
g = graphitesend.init(group='loadavg_', suffix='min',graphite_server='10.3.11.90',graphite_port='2003')

while True:
    (la1, la5, la15) = open('/proc/loadavg').read().strip().split()[:3]
    print(g.send_dict({'1': la1, '5': la5, '15': la15}))
    time.sleep(1)

It still errors out ..

root@d19052-v19052:/# python3 proc_loadavg.py
Traceback (most recent call last):
  File "proc_loadavg.py", line 3, in <module>
    import graphitesend
ImportError: No module named 'graphitesend'

Best Denis Zhdanov (deniszhdanov) said : #7

Of course, graphitesend module should be installed. And I doubt that it supports python3.
So, install it with `pip install graphitesend` and run with `python proc_loadavg.py`

Zee (spreez) said : #8

Thank you, this helped.

Graphitesend was already installed but I realized had a couple of issues. One of them was I was using Python3. Other I was using the graphite server name incorrectly.
Currently, I'm initializing as follows
g = graphitesend.init(group='test.LoadTest1',graphite_server='10.3.11.90',graphite_port=2003)
and calling it using
python filename.py
and it works.

g.send_list(
[
('test.jLoadTest1',4.169,1500676316),
('test.jLoadTest1',3.526,1500676051)
]
)
so the idea is to actually have the data in this format and save it as a py file and execute it. Is this the best way to load the data?

Denis Zhdanov (deniszhdanov) said : #9

If you have enough RAM - this will work. If not - then you need to read data from database using python and send it to graphite right away.

Zee (spreez) said : #10

Thanks Denis Zhdanov, that solved my question.

Zee (spreez) said : #11

Thank you.