Welcome to the Cumulus Support forum.
Latest Cumulus MX V4 release 4.0.0 (build 4022) - 11 May 2024
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V4 release 4.0.0 (build 4022) - 11 May 2024
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Legacy Cumulus 1 release 1.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?
Moderator: mcrossley
- dazza1223
- Posts: 867
- 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?
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
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
-
- Posts: 2496
- 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?
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.
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
- Posts: 867
- 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?
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
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
- Posts: 12862
- 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?
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
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
- Posts: 867
- 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?
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
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
- Posts: 12862
- 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?
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.
What is your logging interval? 1.2 million rows would be almost 21 years worth with a 10 minute interval.
- dazza1223
- Posts: 867
- 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?
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?
what should it be set to then the logging interval?
and my log Log interval set to 10 min is that set right?
- mcrossley
- Posts: 12862
- 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?
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.
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
- Posts: 867
- 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?
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?
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?