January 2021 - discuss - lists.mariadb.org

[Maria-discuss] Streaming telemetry and ColumnStore - viable or not
by Richard Ellerbrock 19 Jan '21

19 Jan '21

Hi all, I am investigating the viability of using MariaDB as a datastore for streaming telemetry data sourced via gRPC. The data ingestion rate will be quite high (write once, read many with no change to the data), but the data can be buffered and bulk loaded in 10-60sec intervals. We are currently using MariaDB for OLTP purposes (Innodb) and also graphs (OQGRAPH) with no issues. My journey so far has been mainly focused on Kafka but the more I dig the more future trouble I uncover. So many components required to solve even a simple problem. Now I am investigating if using our current tooling that our team knows already will get the job done. Reading up on ColumnStore has also uncovered more gotchas and deprectations that I am comfortable with. Things that concern me are: 1. Is ColumnStore going to go the way of TokuDB? 2. The docs warn that data needs to be bulk inserted - not super ideal for streaming applications. Is streaming via cpimport viable? 3. The bulk write API's have been deprecated ( https://mariadb.com/kb/en/columnstore-bulk-write-sdk/) which then implies that streaming is not advised (Kafka connectors are thus also not viable). This slideshow talks about streaming with ColumnStore 1.1 https://www.slideshare.net/MariaDB/big-data-analytics-with-mariadb-columnst… 4. Streaming data is a combination of fixed columns and key / value pairs. How to deal with the dynamic nature of the key / value pairs (see below for sample data that was sourced via gRPC and converted to OpenTSDB format - we can convert the data into any required format, this is just one example). So some questions: What is the current recommended streaming telemetry data store solution from MariaDB, or should I rather look at a custom solution (different tooling, more skills/re-skilling required)? Is there a writeup or reference design? Any insights will be helpful. Thanks. put eos.intflagpktcounter.counter.pkt 1611032330 547181 intf=Port-Channel553 direction=in type=Multicast host=30.7.253.145 put eos.intflagcounter.counter.octets 1611032330 121113118 intf=Port-Channel553 direction=in host=30.7.253.145 put eos.intflagpktcounter.counter.pkt 1611032330 3649031 type=Ucast host=30.7.253.145 intf=Port-Channel553 direction=out put eos.intflagpktcounter.counter.pkt 1611032330 297600 host=30.7.253.145 intf=Port-Channel553 direction=in type=Ucast put eos.intflagcounter.counter.errors 1611032330 0 intf=Port-Channel553 direction=out host=30.7.253.145 put eos.totalcpu.cpu.utilization.total 1611032330 21 type=util host=30.7.253.145 put eos.totalcpu.cpu.utilization.total 1611032330 225275556 host=30.7.253.145 type=user put eos.corecpu.cpu.utilization.total 1611032330 42929390 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.total 1611032330 4005894390 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.0 1611032330 55264287 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.0 1611032330 10865672 host=30.7.253.145 type=system put eos.corecpu.cpu.utilization.cpu.0 1611032330 1002366784 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 20 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 50596732 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 10883378 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.1 1611032330 1006988024 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 21 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 58921103 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 10405231 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.2 1611032330 999355549 type=idle host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 21 type=util host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 60493433 type=user host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 10775108 type=system host=30.7.253.145 put eos.corecpu.cpu.utilization.cpu.3 1611032330 997184032 type=idle host=30.7.253.145 put eos.tempsensor.environment.temperature 1611032330 45.526831652731964 sensor=cell/1/TempSensor1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan2\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 host=30.7.253.145 fan=Fan2\/1 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan1\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan1\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan3\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan3\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan4\/1 host=30.7.253.145 put eos.fanspeed.environment.cooling 1611032330 60.57142857142857 fan=Fan4\/1 host=30.7.253.145 put eos.sfpfixedtemperature.xcvr.temperature 1611032330 38.0390625 host=30.7.253.145 intf=Ethernet3 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -2.0398103065285085 intf=Ethernet3 powerdirection=tx host=30.7.253.145 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -1.92938760082761 powerdirection=rx host=30.7.253.145 intf=Ethernet3 put eos.sfpfixedrxtxpower.xcvr.power 1611032330 -1.92938760082761 intf=Ethernet3 powerdirection=rx host=30.7.253.145 -- Richard Ellerbrock

1 0

[Maria-discuss] Setting a System Global Variable
by brian＠smitherconsulting.com 16 Jan '21

16 Jan '21

Hello, Concerning: SET GLOBAL general_log_file='/path/to/general.log'; Some locations in the documentation suggests this can only be done by a super-user. https://mariadb.com/kb/en/general-query-log/ "A user with the SUPER privilege can disable logging to the general query log for a connection by setting the SQL_LOG_OFF system variable to 1." https://mariadb.com/kb/en/grant/#super SUPER: "Execute superuser statements: ..., SET global system variables,...." Q: Does setting the general_log_file, in fact, require super user privileges? Then, once the setting is made, and the task completed, the desire is to restore the setting back to what it was without having to know what it was. https://mariadb.com/kb/en/set/#global-session DEFAULT: "Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value." Q: By "server default" is this {host_name}.log or is it the value set in my.cnf? Thank you.

1 0

[Maria-discuss] Issue authenticating as mysql in 10.5 on Ubuntu 20.04
by Alexander Nolting 11 Jan '21

11 Jan '21

Hi all, This was probably already discussed. I did a fresh 20.04 and MariaDB 10.5.8 install and now I'm having a question related to the new authentication concept starting from 10.4. As stated at Authentication from MariaDB 10.4 and Authentication in MariaDB 10.4 — Understanding the Changes there are two administrative accounts created: root and mysql. As I understand using both account sockets. Logging as normal user as root into mariadb using unix_sockets is no problem: testusr@host:~$ sudo mariadb -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-MariaDB-1:10.5.8+maria~focal mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Ctrl-C -- exit! Aborted Doing the same with as mysql testusr@host:~$ sudo mariadb -u mysql returns ERROR 1698 (28000): Access denied for user 'mysql'@'localhost'. If someone can point me to a source detailing me what I may not have understood - any help is appreciated. --- Best Alex

2 2