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 <cl...@nwlink.com > <javascript:>> 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:* weewx-de...@googlegroups.com <javascript:> [mailto: >> weewx-de...@googlegroups.com <javascript:>] *On Behalf Of *Thomas Keffer >> *Sent:* Tuesday, November 14, 2017 8:05 AM >> *To:* Clay Jackson <cl...@nwlink.com <javascript:>> >> *Cc:* weewx-development <weewx-de...@googlegroups.com <javascript:>> >> *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 <cl...@nwlink.com >> <javascript:>> 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 >> >> >> >> >> >> >> > >
