MYSQL Question - slow query

RobGordon · 2020-04-29T12:34:40.401Z

Looking at MYSQL slow query log
I have hundreds of the following entries

# User@Host: emoncms[emoncms] @ localhost []
# Query_time: 0.641481  Lock_time: 0.000096 Rows_sent: 13909  Rows_examined: 27818
 SET timestamp=1588163470;
 SELECT id,nodeid,name,description,processList FROM input WHERE `userid` = '1' ORDER BY nodeid,name asc;

I note that userid field is not indexed on the input table. But I can’t index the table because the server is blocking my update query.

Does anyone know what is calling this query and why?

TrystanLea · 2020-04-29T13:41:59.564Z

Hello @RobGordon
It looks like you may be running emoncms with redis disabled. Redis improves the performance of emoncms quite a bit and is really worth getting running if you can. It caches a lot of the emoncms input and feed meta data and reduces the disk reads and writes considerably.

nchaveiro · 2020-04-29T13:52:20.478Z

That query is used in input_model.php getlist() and triggered from host/input/list.json API that is polling each 5secs when the inputs screen is open.
I would not worry as Trystan said the Redis cache mitigates that.
It that hardware a raspberypi?

RobGordon · 2020-04-30T23:52:11.816Z

server graph 1stMay201027×961 69.7 KB

Thanks @TrystanLea and @nchaveiro
FYI: the hardware is Virtual Server with 6 CPU, 16MB memory and 320GB HDD
We have in excess of 9500 feeds and are storing the data in MYSQL
We do not have Redis enabled as yet
as you can see from the graph, the issue we are experiencing is high CPU,
I can’t tell if the disk I/O is particularly high

Is it possible to implement redis while the controllers are sending data?

borpin · 2020-05-01T09:12:45.284Z

@RobGordon - can you install netdata? It will give you more detailed info on what is causing the high CPU.

Also of course an htop will do the same instantaneously.

TrystanLea · 2020-05-01T12:42:19.451Z

RobGordon:

We do not have Redis enabled as yet
as you can see from the graph, the issue we are experiencing is high CPU,
I can’t tell if the disk I/O is particularly high

That’s the kind of thing I used to see on emoncms.org. Redis does really help a lot.
Are you using virtual feeds? how much data access do you have on your server? is it just accessed by an administrator or are you providing a lot of dashboards etc to users?

TrystanLea · 2020-05-01T12:43:48.342Z

Is the 320GB HDD networked storage or dedicated to the machine that you have the VM running on? Is it a cloud VM server or your own VM on your own dedicated hardware?

RobGordon · 2023-04-16T05:18:05.090Z

Hi Trystan
I’ve opened another thread

Migrating data from MYSQL to MariaDB - unable to see feeds Emoncms

Hi everyone, Background I have an old version [9.7.3 | 2016.09.28] of emonCMS running on a Digital Ocean droplet (VM). It’s utilises the MYSQL engine to store data. I’ve followed this guide and installed a Redis enabled version: low-write 11.3.0 https://docs.openenergymonitor.org/emonsd/digital-ocean-install.html I then exported the MYSQL database tables (feeds, inputs & individual feed tables) and imported them into the relevant MariaDB tables. But the Feeds list is not populated: [imag…

Summary → Managed to get a version of MYSQL and Redis installed and working; but having an issue with seeing existing feeds that have been manually imported.

In regards to your other questions, I was utilising a Digital Ocean droplet so I would presume its network storage rather than a dedicated HDD