80 seconds is way too long - it sounds more like the version 5 issue where it tries to synthesise missing data values. Although I'm not sure how that would tie into your observation of which query it was.
Is all your data in a single database? On Thursday 25 July 2024 at 8:42:09 am UTC+10 [email protected] wrote: > OK – getting bind variables is a bit difficult; but yeah, I can do that. > I’m kinda suspicious of the AQI stuff, and I can also see how it would add > up for daily, weekly, monthly and yearly intervals. And I get the no > caching; but perhaps there’s some happy medium. > > > > In my current configuration, with about 750K records in archive, a “loop > cycle” from the top of the 5th minute to “rsync complete” takes about 80 > seconds. > > > > I also wonder if partitioning might help (that’s my inner DBA talking 😊) > > > > > > *From:* Tom Keffer <[email protected]> > *Sent:* Wednesday, July 24, 2024 1:36 PM > *To:* [email protected] > *Cc:* weewx-development <[email protected]> > *Subject:* Re: [weewx-development] Database Query Performance > > > > The query would be used to calculate the average temperature over an > archive interval, where the interval is some multiple of a day. So, for a > year long plot of one-day averages, that would be 365 invocations. > > > > Temperature is ubiquitous for other plots, for example, ET, so that could > count for a few more.. But, that still feels like a long way from 2,600! > > > > Can you analyze a little deeper? In particular, what the specific time > periods are? > > > > On Wed, Jul 24, 2024 at 1:26 PM <[email protected]> wrote: > > I’m using the Standard Skin – modified a bit – Prosser, WA USA Current > Weather Conditions (n7qnm.net) > <https://www.n7qnm.net/weewx/Standard/index.html> > > > > I was surprised at that SPECIFIC query would get executed so many times- > is it looking up some sort of range, or …… > > > > > > *From:* Tom Keffer <[email protected]> > *Sent:* Wednesday, July 24, 2024 1:18 PM > *To:* Clay Jackson <[email protected]> > *Cc:* weewx-development <[email protected]> > *Subject:* Re: [weewx-development] Database Query Performance > > > > That doesn't completely surprise me. WeeWX makes no attempt at database > caching. That's what keeps it simple. > > > > Still, that does seem like a lot. Assuming a 5 minute archive interval, > that's 2600 times per reporting cycle. What skin, and have you modified it? > > > > On Wed, Jul 24, 2024 at 9:45 AM Clay Jackson <[email protected]> wrote: > > In my "day job" I do database tuning - so, I took some of the tools > (Foglight, Quest Software) has and did a quick analysis of the weewx > (mySQL) database. > > > > I was REALLY surprised to see that in a 24 hour period, this query was > executed 735,000 times: > > SELECT SUM ( `wsum` ) , SUM ( `sumtime` ) FROM `archive_day_outTemp` > WHERE DATETIME >= ? AND DATETIME < ? > > > > The next highest query was only executed 55,000 times: > SELECT MAX ( DATETIME ) FROM `archive` > > > > Any thoughts on this? > > > > -- > You received this message because you are subscribed to the Google Groups > "weewx-development" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-development/59953b14-9c47-41bf-ae73-b85ae56680ben%40googlegroups.com > > <https://groups.google.com/d/msgid/weewx-development/59953b14-9c47-41bf-ae73-b85ae56680ben%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > -- You received this message because you are subscribed to the Google Groups "weewx-development" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/a7bd160c-dd28-4d92-9162-63b500d50dfcn%40googlegroups.com.
