Welcome to the new home of the Cumulus Support forum.

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3044 7 December 2018. See the Wiki for download

help with slow queries?

Discussion of version 3 of Cumulus, which runs on Windows, Linux, and OS X. All Cumulus MX queries in here, please.

Moderator: mcrossley

Post Reply
User avatar
dazza1223
Posts: 428
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Vue
Operating System: raspberry pi 3 +
Location: worthing
Contact:

help with slow queries?

Post by dazza1223 » Sun 11 Nov 2018 11:28 am

hey sorry for keep bringing up this topic :bash: 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
have fun and keep learning

dazza :D

freddie
Posts: 329
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2
Operating System: GNU/Linux (Ubuntu 18.04)
Location: Ystrad, Rhondda, Wales
Contact:

Re: help with slow queries?

Post by freddie » Sun 11 Nov 2018 11:48 am

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.
Freddie
Image

User avatar
dazza1223
Posts: 428
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Vue
Operating System: raspberry pi 3 +
Location: worthing
Contact:

Re: help with slow queries?

Post by dazza1223 » Sun 11 Nov 2018 11:04 pm

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
have fun and keep learning

dazza :D

User avatar
mcrossley
Posts: 5234
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by mcrossley » Mon 12 Nov 2018 9:40 pm

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

User avatar
dazza1223
Posts: 428
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Vue
Operating System: raspberry pi 3 +
Location: worthing
Contact:

Re: help with slow queries?

Post by dazza1223 » Mon 12 Nov 2018 10:50 pm

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

:lol:
have fun and keep learning

dazza :D

User avatar
mcrossley
Posts: 5234
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by mcrossley » Mon 12 Nov 2018 10:58 pm

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.

User avatar
dazza1223
Posts: 428
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Vue
Operating System: raspberry pi 3 +
Location: worthing
Contact:

Re: help with slow queries?

Post by dazza1223 » Mon 12 Nov 2018 11:01 pm

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?
have fun and keep learning

dazza :D

User avatar
mcrossley
Posts: 5234
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by mcrossley » Tue 13 Nov 2018 10:07 pm

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.

User avatar
dazza1223
Posts: 428
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Vue
Operating System: raspberry pi 3 +
Location: worthing
Contact:

Re: help with slow queries?

Post by dazza1223 » Tue 13 Nov 2018 10:21 pm

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?
have fun and keep learning

dazza :D

Post Reply