(sorry for the late reply)

It would be interesting to see the same numbers with a vanilla Standard 
skin as a benchmark as well as whether sqlite3 can be benchmarked.

Just a point of reference, here's my whole cycle (13 seconds) on a sqlite3 
pip install running on a pi4 to SD card with a modified Standard skin as 
well, plus multiple MQTT pub/sub things, Belchertown, a custom skin for 
AQI, and rsync up to my AWS Lightsail instance.

Jul 26 17:25:16 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:17 PDT (1722039917) to database 'purpleair.sdb'
Jul 26 17:25:16 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:17 PDT (1722039917) to daily summary in 'purpleair.sdb'
Jul 26 17:25:16 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:17 PDT (1722039917) to database 'mem.sdb'
Jul 26 17:25:16 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:17 PDT (1722039917) to daily summary in 'mem.sdb'
Jul 26 17:25:17 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:00 PDT (1722039900) to database 'vp2.sdb'
Jul 26 17:25:17 pi4 weewxd-vp2[435]: INFO weewx.manager: Added record 
2024-07-26 17:25:00 PDT (1722039900) to daily summary in 'vp2.sdb'
Jul 26 17:25:17 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
1 files for report lastrain in 0.02 seconds
Jul 26 17:25:17 pi4 weewxd-vp2[435]: INFO weewx.restx: PWSWeather: 
Published record 2024-07-26 17:25:00 PDT (1722039900)
Jul 26 17:25:17 pi4 weewxd-vp2[435]: INFO weewx.restx: Wunderground-PWS: 
Published record 2024-07-26 17:25:00 PDT (1722039900)
Jul 26 17:25:18 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
12 files for report vds-local in 1.40 seconds
Jul 26 17:25:18 pi4 weewxd-vp2[435]: INFO weewx.reportengine: Copied 0 
files to /home/pi/weewx-data/public_html/vp2
Jul 26 17:25:19 pi4 weewxd-vp2[435]: INFO weewx.imagegenerator: Generated 
17 images for report vds-local in 0.52 seconds
Jul 26 17:25:19 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
1 files for report mem in 0.02 seconds
Jul 26 17:25:19 pi4 weewxd-vp2[435]: INFO weewx.imagegenerator: Generated 1 
images for report mem in 0.04 seconds
Jul 26 17:25:19 pi4 weewxd-vp2[435]: INFO weewx.reportengine: Copied 0 
files to /home/pi/weewx-data/public_html/vp2/mem
Jul 26 17:25:20 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
12 files for report forecast in 0.89 seconds
Jul 26 17:25:20 pi4 weewxd-vp2[435]: INFO weewx.reportengine: Copied 0 
files to /home/pi/weewx-data/public_html/vp2/forecast
Jul 26 17:25:20 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
6 files for report vds-bootstrap in 0.14 seconds
Jul 26 17:25:20 pi4 weewxd-vp2[435]: INFO weewx.reportengine: Copied 0 
files to /home/pi/weewx-data/public_html/vp2/bootstrap
Jul 26 17:25:25 pi4 weewxd-vp2[435]: INFO weewx.cheetahgenerator: Generated 
12 files for report Belchertown in 4.69 seconds
Jul 26 17:25:25 pi4 weewxd-vp2[435]: INFO weewx.reportengine: Copied 3 
files to /home/pi/weewx-data/public_html/vp2/belchertown
Jul 26 17:25:28 pi4 weewxd-vp2[435]: INFO user.stackedwindrose: Generated 1 
images for StackedWindRose in 0.07 seconds
Jul 26 17:25:29 pi4 weewxd-vp2[435]: INFO weeutil.rsyncupload: rsync'd 73 
files (2,969,426 bytes) in 0.94 seconds

My ecowitt instance on the same pi4 takes a second or two for its minimal 
one skin to run as well as to publish to MQTT and rsync to Lightsail.

Jul 26 17:25:24 pi4 weewxd-ecowitt[1615]: INFO weewx.manager: Added record 
2024-07-26 17:25:00 PDT (1722039900) to database 'ecowitt.sdb'
Jul 26 17:25:24 pi4 weewxd-ecowitt[1615]: INFO weewx.manager: Added record 
2024-07-26 17:25:00 PDT (1722039900) to daily summary in 'ecowitt.sdb'
Jul 26 17:25:24 pi4 weewxd-ecowitt[1615]: INFO weewx.cheetahgenerator: 
Generated 1 files for report ecowitt in 0.04 seconds
Jul 26 17:25:24 pi4 weewxd-ecowitt[1615]: INFO weewx.reportengine: Copied 0 
files to /home/pi/weewx-data/public_html/ecowitt
Jul 26 17:25:25 pi4 weewxd-ecowitt[1615]: INFO weeutil.rsyncupload: rsync'd 
1 files (4,893 bytes) in 1.31 seconds

On Wednesday, July 24, 2024 at 9:19:53 PM UTC-7 Cameron D wrote:

> 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/7ce01d15-9724-476c-8dbe-91cec2ca9a37n%40googlegroups.com.

Reply via email to