MYSQL Question - slow query

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?

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.

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?

1 Like

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?

@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.

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?

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?

Hi Trystan
I’ve opened another thread

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