help with slow queries?

[dazza1223]

hey sorry for keep bringing up this topic but I've tried 3 difference servers one on raspberry pi 3 + windows server 2012 12gb off ram and i5 ssd and my WD NAS drive and all off them have the same thing on doing the queries.

on recent graphs and it very slow on ( Temperature)

http://www.davisworthing.co.uk/new/graphs_recent.htm

and here Historical Data

( By Mon & Hr) very slow

( Comfort ) very slow

plz can someone help me as it driving me mad thanks

[freddie]

Looking at the developer console when your page is loading, you are making multiple requests (milliseconds apart) for the same data - the responses I looked at were all for custom_realtime.txt at 00:31:32 on 29 September 2018. These requests (which continue even after the graph has drawn) appear to be not required by this page (I could be wrong on this point!), and appear to be in an infinite loop.

FWIW - the temperature graph does request a large chunk of json, so the response of this graph tends to be slower than that of, say, pressure.

I would chase down whatever is causing those multiple requests of custom_realtime.txt. Once you have fixed that then the page will be more responsive.

[dazza1223]

thanks freddie

i have fix the custom_realtime.txt what you was saying and i know what you mean bye temperature graph does request a large chunk data but im seeing after websites site and thay are faster then mine and i pulled all the files from mark site? so all it might just be thay are running lote powefull database's then min

[mcrossley]

My DB is MariaDB running on a Rasberry Pi 3

Have you added any indexes to the tables?

How many rows do you have in the data tables?
Mine has roughly...
Realtime 20,000
Full data 740,000
Daily 3,200

[dazza1223]

hey mark
i just look at my table rows

ive got

Realtime 1,821,521
Full data 1,205,046
Daily 1,284

have i got to install MariaDB as i just installed my sql server on my pi ?

and i saw about adding indexes but i dont know how to do the indexes

[mcrossley]

That's an awful lot of realtime data, do you really need that, and can you really display it all on a graph!?

What is your logging interval? 1.2 million rows would be almost 21 years worth with a 10 minute interval.

[dazza1223]

o i dint know how long should i set it mark?

what should it be set to then the logging interval?


and my log Log interval set to 10 min is that set right?

[mcrossley]

I am keeping 14 days of realtime data, but I only use the last 7 days on my graphs. I suggest you reset your retention period to 7 days to cut id down to a manageable size.

You also need to look at your full data table, are there lots of duplicate rows for the same date/time? There shouldn't be because the table should have a primary key on the datetime column.

[dazza1223]

hey there mark thank u for the info


i cant see any duplicate date in there all look ok to me?


what the best thing to remove it all and ree in port them again?


p:s just google it about remove old datbase

#!/bin/bash
SQL="DELETE FROM user_action WHERE action_date < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"

MYSQL_USER="<your-user>"
MYSQL_PASS="<your-password>"
MYSQL_DB="<your-db>"

echo $SQL | /usr/bin/mysql --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB

somthink like this?