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] <mailto:[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] <mailto:[email protected]> > Sent: Wednesday, July 24, 2024 1:18 PM To: Clay Jackson <[email protected] <mailto:[email protected]> > Cc: weewx-development <[email protected] <mailto:[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] <mailto:[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] <mailto:[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/3a2a01dade1a%24b65d6160%2423182420%24%40n7qnm.net.
