As usual, Gary has nailed it. I can think of many ways of cutting down the number of queries (in particular, fetch all the daily sums and counts in one query), but I don't think we have an actual problem. Yes, this is not the most efficient algorithm, but it is easy to understand, and it does not cache data.
I would be very interested in how you tuned Maria. -tk On Sat, Nov 18, 2017 at 9:29 PM, gjr80 <[email protected]> wrote: > Some semi-random thoughts. Won't almanac be using the current temperature > so the query will be on the archive, and at a 5 minute archive period that > would be around 288 queries a day in any case. The query that seems to be > of concern (5.3k per day mentioned in the first post and 30k per hour in > the second post) is querying the daily summaries not the archive. Any of > the pressures would be using temperature from 12 hours ago or the current > outTemp; both of which will be queries on the archive and not the daily > summaries. > > I think the real culprit(?) here is heatdeg and cooldeg and the NOAA > format reports. Heating and cooling degree days are calculated using the > daily average temperature. Looking at how (for example) $month.heatdeg.sum > is calculated I see weeWX iterates over the days of the month calculating > the average daily temperature for each day. Same for cooldeg. It's even > worse for $year.xxxxdeg.sum, the iteration is over all days of the year. > The NOAA format monthly report includes heatdeg and cooldeg for each day > with a monthly total and the NOAA format year report gives the heatdeg > and cooldeg for each month with a year total. Given the the current > month/year NOAA format report is generated in its entirety each report > cycle I can see a lot of daily average temperatures being required. The > number of such queries will be smallest at the start of the year and > steadily increase throughout the year, 31 Dec being the worst. Within each > month the number of queries will also increase as your work through from > the start of the month to the end of the month. > > Adding in some rough numbers, and using 18 November and a 5 minute archive > period, I would expect to see around 72 queries each time the monthy NOAA > report is generated (or about 864 per hour) and for the yearly report about > 1288 per report (or around 15456 queries per hour). Total about 16.3k per > hour. Not quite 30k per hour but I could have missed a factor of 2 > somewhere. Would be an interesting exercise to disable the NOAA reports and > see how the number of queries is affected. > > Gary > > > On Sunday, 19 November 2017 10:04:56 UTC+10, Tom Keffer wrote: >> >> That's quite a tool! Never heard of it before. I'll have to check it out. >> >> Outside temperature is used to calculate the effects of refraction on >> sunrise and sunset. To be sure, a small effect (maybe a few seconds), but, >> being a weather program, weewx has the advantage of having temperature at >> hand, so why not? :-) >> >> -tk >> >> On Sat, Nov 18, 2017 at 2:11 PM, Clay Jackson <[email protected]> wrote: >> >>> Hi, Tom – here’s a screenshot from Foglight, drilled down into just this >>> statement and another of the “overview” – this is for an hour this >>> morning. >>> >>> >>> >>> Maria will cache the data, so it’s not a big deal in terms of overall >>> performance; but, I think it’s interesting, and 30,000 executions/hour is >>> certainly something interesting. The whole reason I started down this path >>> (other than as an exercise for Foglight) is that I was seeing from the log >>> that weewx was taking 2 or 3 minutes to finish each 5 minute “process”. >>> I’ve done some pretty simple tuning in Maria (which I’ll document in a post >>> soon) and have gotten that down to under a minute now. >>> >>> >>> >>> Any thoughts on where to look? And out of curiosity, why would sunrise >>> and sunset need Outside Temp? >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> *From:* [email protected] [mailto:weewx-de...@googlegrou >>> ps.com] *On Behalf Of *Thomas Keffer >>> *Sent:* Tuesday, November 14, 2017 8:05 AM >>> *To:* Clay Jackson <[email protected]> >>> *Cc:* weewx-development <[email protected]> >>> *Subject:* Re: [weewx-development] Query against archive_day_outTemp >>> >>> >>> >>> That's interesting! >>> >>> >>> >>> Several things need the outside temperature: sunrise and sunset >>> calculations, reduction of barometric pressure to the sea-surface pressure, >>> and ET. >>> >>> >>> >>> I think Sqlite is smart enough to cache the needed pages. Can your tool >>> make an estimate of the time spent in each query? >>> >>> >>> >>> -tk >>> >>> >>> >>> On Tue, Nov 14, 2017 at 7:20 AM, Clay Jackson <[email protected]> wrote: >>> >>> My “day job” is supporting Quest Foglight (a database monitoring tool) – >>> just for fun, I pointed it at the weewx database. >>> >>> >>> >>> It generally looks pretty good – but, there’s ONE query against >>> archive_day_outTemp that’s executing 5,300 times/day (the next nearest is >>> about 750/day). >>> >>> >>> >>> Here’s the query >>> >>> >>> >>> SELECT SUM(`wsum`), SUM(`sumtime`) >>> >>> FROM `archive_day_outTemp` >>> >>> WHERE DATETIME >= ? AND DATETIME < ? >>> >>> >>> >>> I AM using Alarm_Multi to check outdoor temps below freezing; but, >>> there’s a 3600 second “timer” on that. >>> >>> >>> >>> Anyone have any ideas? >>> >>> >>> >>> Clay Jackson >>> >>> >>> >>> >>> >>> >>> >> >>
