0

I am storing some data in influx and it is quite confusing that influx is 4-5 times slow as Mysql. I try to test by inserting 10000 rows in mysql and then in influxdb. and the stats are below.

Mysql

real: 6m 39sec
user: 2.956sec  
sys: 0.504sec

Influxdb

real: 6m 17.193sec
user: 11.860sec
sys: 0.328sec

my code for influx is given below, I used the same pattern to store in mysql.

#!/usr/bin/env python
# coding: utf-8
import time
import csv
import sys
import datetime
import calendar
import pytz
from influxdb import client as influxdb
from datetime import datetime

host = 'localhost'
port = 8086
user = "admin"
password = "admin"
db_name = "testdatabase"
db = influxdb.InfluxDBClient(database=db_name)


def read_data():
    with open(file) as f:
        reader = f.readlines()[4:]
       for line in reader:
            yield (line.strip().split(','))


fmt = '%Y-%m-%d %H:%M:%S'
file = '/home/rob/mycsvfile.csv'

csvToInflux = read_data()
for metric in csvToInflux:
    timestamp = datetime.strptime(metric[0][1: len(metric[0]) - 1], fmt)

    new_value = float(metric[1])
    influx_metric = [{
        'measurement': 'mytable1',
        'time': timestamp,
        'fields': {
             'col1': metric[1],
             'col2': metric[2],
             'col3': metric[3],
             'col4': metric[4],
             'col5': metric[5],
             'col6': metric[6],
             'col7': metric[7],
             'col8': metric[8],
             'col9': metric[9]
        }
        }]
    db.write_points(influx_metric)

Can someone give me an idea how can I improve it. I think it might be due to cache. is cache option is off by default in Influx db? and can someone guide me to do batch processing in influx. I try to look over SO and google but couldn't solve my problem. I am newbie to influx db. I am trying to make it faster.

2
  • 1
    Why are you inserting data row-by-row (aka slow-by-slow)? Commented Apr 18, 2017 at 10:22
  • @Colin'tHart I want to compare its speed with mysql, in mysql i am inserting row by row and it is faster than influxdb. But I think influxdb should be fast enough than mysql. I am new to influxdb that why i guess i am missing something.
    – rob
    Commented Apr 19, 2017 at 14:36

1 Answer 1

2

InfluxDB: 3% cpu; MySQL: 1%. Do you have something else to use the CPU for?

Can you get InfluxDB to perform a MySQL LOAD DATA INFILE? That will do all that work in a single SQL statement, and probably do it faster than 6 minutes elapsed.

3
  • how can I know that Influx uses 3 % cpu and Mysql uses 1 % cpu?. , I dont understand this. ..'Do you have something else to use the CPU for?' . .....I am using cpu for normal work. I am not sure whether I can use Load data Infile command in influxdb or not. and actually my data contains NaN values which are problematic for load data infile command.
    – rob
    Commented Apr 7, 2017 at 8:43
  • wwhy influx uses more cpu than mysql? and where I can get this information. thanks a lot for the help
    – rob
    Commented Apr 7, 2017 at 8:50
  • 2
    Why more CPU? Look at all the code you wrote - read file, convert to 'float' etc, build insert statement, call library to 'write', etc. LOAD DATA has all that work built in. Meanwhile, look at all the code you wrote -- the more lines you write, the more changes for error there are.
    – Rick James
    Commented Apr 7, 2017 at 17:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.