Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

help with slow queries?

Topics about the Beta trials up to Build 3043, the last build by Cumulus's founder Steve Loft. It was by this time way out of Beta but Steve wanted to keep it that way until he made a decision on his and Cumulus's future.

Moderator: mcrossley

Locked
User avatar
dazza1223
Posts: 860
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Pro 2 plus
Operating System: Raspberry pi 4 (4gb)
Location: Worthing
Contact:

help with slow queries?

Post by dazza1223 »

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

https://www.davisworthing.co.uk

Image
freddie
Posts: 2433
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: help with slow queries?

Post by 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.
Freddie
Image
User avatar
dazza1223
Posts: 860
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Pro 2 plus
Operating System: Raspberry pi 4 (4gb)
Location: Worthing
Contact:

Re: help with slow queries?

Post by 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
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by 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
User avatar
dazza1223
Posts: 860
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Pro 2 plus
Operating System: Raspberry pi 4 (4gb)
Location: Worthing
Contact:

Re: help with slow queries?

Post by 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

:lol:
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by 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.
User avatar
dazza1223
Posts: 860
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Pro 2 plus
Operating System: Raspberry pi 4 (4gb)
Location: Worthing
Contact:

Re: help with slow queries?

Post by 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?
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: help with slow queries?

Post by 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.
User avatar
dazza1223
Posts: 860
Joined: Sun 25 Jan 2015 8:41 pm
Weather Station: Davis Vantage Pro 2 plus
Operating System: Raspberry pi 4 (4gb)
Location: Worthing
Contact:

Re: help with slow queries?

Post by 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?
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
Locked