Hey Tom,
That worked (on my MBP and Raspi, though the latter was substantially slower
(MBP total processing time was 475.95 seconds, raspi was a comical 7999.35))! I
was reading on sqlite3 locking processes for writing, and it seemed oddly
complex, but I’m sure there are reasons for it.
Doing a quick comparison of report generation
MBP (M2 Pro):
Default Seasons, DB missing ET column: initial 14.39s, update run 9.29s.
Default Seasons, DB with ET, calc-missing run: initial 5.54s, update run 0.44s.
Raspi (Pi 4, 4GB):
Default Seasons, DB missing ET column: initial 2m26s, update run 1m23s.
Default Seasons, DB with ET, calc-missing run: initial 1m6s, update run 3.9s.
Started back up weewx after all the work and testing, after it grabbed the
backlog of records (about 2.5 hours worth), the results speak for themselves:
Dec 29 16:20:22 raspi-server-misc weewxd[23387]: INFO weewx.cheetahgenerator:
Generated 8 files for report SeasonsReport in 1.87 seconds
Dec 29 16:20:23 raspi-server-misc weewxd[23387]: INFO weewx.imagegenerator:
Generated 13 images for report SeasonsReport in 0.73 seconds
Where previously
Dec 29 12:45:31 raspi-server-misc weewxd[13706]: INFO weewx.cheetahgenerator:
Generated 8 files for report SeasonsReport in 10.84 seconds
Dec 29 12:46:43 raspi-server-misc weewxd[13706]: INFO weewx.imagegenerator:
Generated 12 images for report SeasonsReport in 72.09 seconds
This is basically on par with 4.x numbers.
(version 4.10.2 run)
Dec 24 00:05:23 raspi-server-misc weewx[10332] INFO weewx.cheetahgenerator:
Generated 8 files for report SeasonsReport in 1.78 seconds
Dec 24 00:05:24 raspi-server-misc weewx[10332] INFO weewx.imagegenerator:
Generated 12 images for report SeasonsReport in 0.68 seconds
The interesting piece, and this is starting to give me déjà vu, is ET is still
0.0. I seem to recall I had this issue when I first started with weewx and the
goal was to prevent generating the graph that was always 0…
select dateTime, datetime(dateTime, 'unixepoch','localtime'), ET from archive
order by dateTime desc limit 20;
1703895600|2023-12-29 16:20:00|0.0
1703895300|2023-12-29 16:15:00|0.0
1703895000|2023-12-29 16:10:00|0.0
1703894700|2023-12-29 16:05:00|0.0
1703894400|2023-12-29 16:00:00|0.0
1703894100|2023-12-29 15:55:00|0.0
1703893800|2023-12-29 15:50:00|0.0
1703893500|2023-12-29 15:45:00|0.0
1703893200|2023-12-29 15:40:00|0.0
1703892900|2023-12-29 15:35:00|0.0
1703892600|2023-12-29 15:30:00|0.0
1703892300|2023-12-29 15:25:00|0.0
1703892000|2023-12-29 15:20:00|0.0
1703891700|2023-12-29 15:15:00|0.0
1703891400|2023-12-29 15:10:00|0.0
1703891100|2023-12-29 15:05:00|0.0
1703890800|2023-12-29 15:00:00|0.0
1703890500|2023-12-29 14:55:00|0.0
1703890200|2023-12-29 14:50:00|0.0
1703889900|2023-12-29 14:45:00|0.0
Likely because the Vantage Vue doesn’t provide ET, or Radiation (which I
believe is required to calculate ET).
It appears calc-missing calculated basically 0 ET for old values (likely what I
imported from Weathercat).
sqlite> select dateTime, datetime(dateTime, 'unixepoch','localtime'), ET from
archive where ET <> 0 order by dateTime DESC limit 20;
1596513480|2020-08-03 20:58:00|1.39925533258712e-05
1596513360|2020-08-03 20:56:00|1.39902428567705e-05
1596513240|2020-08-03 20:54:00|1.39902428567705e-05
1596513120|2020-08-03 20:52:00|1.39891701349058e-05
1596513000|2020-08-03 20:50:00|1.40334277836239e-05
1596512880|2020-08-03 20:48:00|1.40334277836239e-05
1596512760|2020-08-03 20:46:00|1.40309985664365e-05
1596512640|2020-08-03 20:44:00|1.40309985664365e-05
1596512520|2020-08-03 20:42:00|1.4028214706252e-05
1596512400|2020-08-03 20:40:00|1.4028214706252e-05
1596512280|2020-08-03 20:38:00|1.40269286093443e-05
1596512160|2020-08-03 20:36:00|1.40241618388119e-05
1596512040|2020-08-03 20:34:00|1.40227686497999e-05
1596511920|2020-08-03 20:32:00|1.40698529257927e-05
1596511800|2020-08-03 20:30:00|1.40682932226393e-05
1596511680|2020-08-03 20:28:00|1.41120992729917e-05
1596511560|2020-08-03 20:26:00|1.41120992729917e-05
1596511440|2020-08-03 20:24:00|1.41092006622079e-05
1596511320|2020-08-03 20:22:00|1.41092006622079e-05
1596511200|2020-08-03 20:20:00|1.41077338780795e-05
Everything newer than that is 0.0.
Looking at the data, it seems weathercat must have been “calculating”
maxSolarRad (based on one of the entries I’m seeing) and that’s leading to the
errant ET calculations. There’s also some stupid levels of precision on some of
the values (out to like 8 decimal places).
Since I’ve never had a station that will generate ET, or solar rad for that
matter, should I just blank those columns with something like
Update archive set maxSolarRad=0.0, ET=0.0 where maxSolarRad<>0;
?
Then maybe regenerate the daily summaries?
Thanks Tom. I (think?) we’ve highlighted both some possibly unexpected behavior
from weewx when a column is missing (same issue Cameron is/was seeing), and how
my data is not as tidy as I would like…
-Ryan Stasel
p.s. Here’s what one of the bad records looks like
dateTime
1596511200
usUnits
1
interval
2
altimeter
30.05136968
appTemp
65.54286608
appTemp1
barometer
30.03188507
batteryStatus1
batteryStatus2
batteryStatus3
batteryStatus4
batteryStatus5
batteryStatus6
batteryStatus7
batteryStatus8
cloudbase
1429.432901
co
co2
consBatteryVoltage
dewpoint
58.55
dewpoint1
extraHumid1
extraHumid2
extraHumid3
extraHumid4
extraHumid5
extraHumid6
extraHumid7
extraHumid8
extraTemp1
extraTemp2
extraTemp3
extraTemp4
extraTemp5
extraTemp6
extraTemp7
extraTemp8
forecast
hail
hailBatteryStatus
hailRate
heatindex
62.798
heatindex1
heatingTemp
heatingVoltage
humidex
69.66087234
humidex1
inDewpoint
50.13910232
inHumidity
41
inTemp
75.506
leafTemp1
leafTemp2
leafWet1
leafWet2
lightning_distance
lightning_disturber_count
lightning_energy
lightning_noise_count
lightning_strike_count
luminosity
maxSolarRad
0.29728566
nh3
no2
noise
o3
outHumidity
86
outTemp
62.798
pb
pm10_0
pm1_0
pm2_5
pressure
29.55013196
radiation
0
rain
0
rainBatteryStatus
rainRate
0
referenceVoltage
rxCheckPercent
signal1
signal2
signal3
signal4
signal5
signal6
signal7
signal8
snow
snowBatteryStatus
snowDepth
snowMoisture
snowRate
so2
soilMoist1
soilMoist2
soilMoist3
soilMoist4
soilTemp1
soilTemp2
soilTemp3
soilTemp4
supplyVoltage
txBatteryStatus
UV
0
uvBatteryStatus
windBatteryStatus
windchill
62.798
windDir
windGust
0
windGustDir
windrun
0
windSpeed
0
ET
1.41077E-05
From: Tom Keffer [email protected] <mailto:[email protected]>
Sent: Friday, December 29, 2023 12:56 PM
To: [email protected] <mailto:[email protected]>
Cc: Vince Skahan [email protected] <mailto:[email protected]> ;
weewx-development [email protected]
<mailto:[email protected]>
Subject: Re: [weewx-development] Re: V5.0 release candidate available
I tried this on my own largish database by dropping ET, adding it back in, then
using calc-missing. No problems.
It's not quite as big (380MB; 1.7M rows) as yours, but has many more rows than
your "hang up" rows.
Long shot thing to try: using smaller "tranches." For example,
weectl database calc-missing --tranche=1
This would use a one day tranche, instead of the default of 10 days. This
reduces memory requirements and keeps the transaction sizes smaller.
But, I think there is something more fundamental going on. The calc-missing
action uses two connections to the database --- they may be interacting in
subtle ways.
-tk
On Fri, Dec 29, 2023 at 9:48 AM <[email protected] <mailto:[email protected]>
> wrote:
Hey Tom,
Got new weewx install on my MBP (just to speed things up), copied over
weewx.conf (making adjustments for venv install) and my db and can say
calc-missing hangs at a different spot on here. This time hanging at record
49000.
Oh a whim, I added –dry-run, and it processed all records without error. On M2
Pro, that took 181.99 seconds for nearly 15 years of data (2.55M rows).
Are we hitting some DB write lock race/contention? (I say, as I re-read what I
previously had copied from errors and see “sqlite3.OperationalError: database
is locked”. Clearly reading IS fundamental. =P
And to be clear, on Raspi I had stopped weewx service, and on Macbook Pro, I
never started weewxd to begin with. So this would seemingly be entirely weectl
locking the db.
Thanks!
-Ryan Stasel
From: [email protected] <mailto:[email protected]> <[email protected]
<mailto:[email protected]> >
Sent: Friday, December 29, 2023 8:33 AM
To: 'Tom Keffer' <[email protected] <mailto:[email protected]> >
Cc: 'Vince Skahan' <[email protected] <mailto:[email protected]> >;
'weewx-development' <[email protected]
<mailto:[email protected]> >
Subject: RE: [weewx-development] Re: V5.0 release candidate available
Hey Tom,
Thanks. Below is what I tried… sadly I hit a few roadblocks.
I stopped weewx on the machine, and checked my weewx.conf, and changed the ET
value from “hardware” to “prefer_hardware”. I believe this was due to previous
issue, but I cannot find the thread… =(
Saved that change.
Backed up the db prior to the add, then ran
Weectl database add-column ET
Confirmed to add as REAL
Weectl database calc-missing
Confirmed. Process hung at/around record 68000. No CPU usage. Guessing there’s
data missing there? No output in logs indicating the issue.
Processing record: 68000; Last record: 2010-02-17 15:58:00 PST (1266451080)
Exited with ctrl-C, got
Traceback (most recent call last):
File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
return fn(*args, **kwargs)
File "/usr/share/weewx/weedb/sqlite.py", line 233, in execute
return sqlite3.Cursor.execute(self, *args, **kwargs)
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/share/weewx/weectl.py", line 74, in <module>
main()
File "/usr/share/weewx/weectl.py", line 66, in main
namespace.func(namespace)
File "/usr/share/weewx/weectllib/__init__.py", line 92, in dispatch
namespace.action_func(config_dict, namespace)
File "/usr/share/weewx/weectllib/database_cmd.py", line 395, in calc_missing
no_confirm=namespace.yes)
File "/usr/share/weewx/weectllib/database_actions.py", line 480, in
calc_missing
calc_missing_obj.run()
File "/usr/share/weewx/weecfg/database.py", line 433, in run
wxcalculate.do_calculations(record)
File "/usr/share/weewx/weewx/wxservices.py", line 136, in do_calculations
val = weewx.xtypes.get_scalar(obs_type, data_dict, self.db_manager)
File "/usr/share/weewx/weewx/xtypes.py", line 86, in get_scalar
return xtype.get_scalar(obs_type, record, db_manager, **option_dict)
File "/usr/share/weewx/weewx/wxxtypes.py", line 305, in get_scalar
% db_manager.table_name, (start_ts, end_ts))
File "/usr/share/weewx/weewx/manager.py", line 579, in getSql
_cursor.execute(sql, sqlargs)
File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
return fn(*args, **kwargs)
Started process again, this time just targeting last couple years of data.
Weectl database calc-missing –from=2020-01-01
Hung again at record 13000.
Processing record: 13000; Last record: 2020-01-19 01:26:00 PST (1579425960)
This time checked ‘ps aux | grep python3’ and see the recalc job using a few
percent of cpu and slowly dropping.
Exited via ctrl-C.
File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
return fn(*args, **kwargs)
File "/usr/share/weewx/weedb/sqlite.py", line 233, in execute
return sqlite3.Cursor.execute(self, *args, **kwargs)
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/share/weewx/weectl.py", line 74, in <module>
main()
File "/usr/share/weewx/weectl.py", line 66, in main
namespace.func(namespace)
File "/usr/share/weewx/weectllib/__init__.py", line 92, in dispatch
namespace.action_func(config_dict, namespace)
File "/usr/share/weewx/weectllib/database_cmd.py", line 395, in calc_missing
no_confirm=namespace.yes)
File "/usr/share/weewx/weectllib/database_actions.py", line 480, in
calc_missing
calc_missing_obj.run()
File "/usr/share/weewx/weecfg/database.py", line 433, in run
wxcalculate.do_calculations(record)
File "/usr/share/weewx/weewx/wxservices.py", line 136, in do_calculations
val = weewx.xtypes.get_scalar(obs_type, data_dict, self.db_manager)
File "/usr/share/weewx/weewx/xtypes.py", line 86, in get_scalar
return xtype.get_scalar(obs_type, record, db_manager, **option_dict)
File "/usr/share/weewx/weewx/wxxtypes.py", line 305, in get_scalar
% db_manager.table_name, (start_ts, end_ts))
File "/usr/share/weewx/weewx/manager.py", line 579, in getSql
_cursor.execute(sql, sqlargs)
File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
return fn(*args, **kwargs)
KeyboardInterrupt
I assume there’s some value it can’t handle (or null value?) but I’m unsure how
to select that row in sqlite. Or maybe the calc_missing tool just stops
updating the line it’s on and I need to wait?
Thinking about trying with a copy of the db on a quicker computer…
For now, I’ve put the production db (pre-ET add) back in place and started
weewx back up.
Thanks!
From: Tom Keffer <[email protected] <mailto:[email protected]> >
Sent: Friday, December 29, 2023 5:09 AM
To: Ryan Stasel <[email protected] <mailto:[email protected]> >
Cc: Vince Skahan <[email protected] <mailto:[email protected]> >;
weewx-development <[email protected]
<mailto:[email protected]> >
Subject: Re: [weewx-development] Re: V5.0 release candidate available
If you don't have an ET column in the database, but request a plot of one in
[ImageGenerator], then the image generator will calculate ET in software using
data in the database. That's likely to be expensive.
Running "weectl station reconfigure" will not add it back to the database. You
need "weectl database add-column", followed by "weectl database calc-missing".
Keep track of what you're doing, including report run times. It will be very
interesting to see if it makes a big difference.
-tk
On Thu, Dec 28, 2023 at 8:11 PM Ryan Stasel <[email protected]
<mailto:[email protected]> > wrote:
Testing this some more, and based on suggestion from Cameron, I have made a
copy of the default Seasons template, and enabled in weewx.conf.
Going through and removing pieces I don't have (ET, UV, etc) from skin.conf,
[DisplayOptions], got my generation from 2m27s to 2m20s (no appreciable change)
and subsequent runs of 1m45s. So it doesn't appear to be anything in
DisplayOptions.
Going a step further and commenting out pieces I don't have from
[ImageGenerator] got me down to 1m7s. with subsequent runs of 3s.
This was gathered running "time weectl report run SeasonsTest", and removing
the output after each run.
Going through and toggling specific ImageGenerator stanzas, issue appears to be
ET. My station doesn't provide ET, so the column is blank... but it's there
because I have a Vantage (weewx assumes vantagepro2, or the loop packets
include just a null value, super unclear here).
Looking at my DB, I don't seem to have an ET column (maybe I dropped it at some
point in the past... vague recollection of there being bad data in there, and
). Maybe this explains the behavior? Is my best bet a "weectl database
reconfigure" to bring things back to default, or just re-add via "weectl
database add-column ET"?
Would love some help!
Thanks!
-Ryan Stasel
Here's what I get from listing columns in archive:
pragma table_info(archive);
0|dateTime|INTEGER|1||1
1|usUnits|INTEGER|1||0
2|interval|INTEGER|1||0
3|altimeter|REAL|0||0
4|appTemp|REAL|0||0
5|appTemp1|REAL|0||0
6|barometer|REAL|0||0
7|batteryStatus1|REAL|0||0
8|batteryStatus2|REAL|0||0
9|batteryStatus3|REAL|0||0
10|batteryStatus4|REAL|0||0
11|batteryStatus5|REAL|0||0
12|batteryStatus6|REAL|0||0
13|batteryStatus7|REAL|0||0
14|batteryStatus8|REAL|0||0
15|cloudbase|REAL|0||0
16|co|REAL|0||0
17|co2|REAL|0||0
18|consBatteryVoltage|REAL|0||0
19|dewpoint|REAL|0||0
20|dewpoint1|REAL|0||0
21|extraHumid1|REAL|0||0
22|extraHumid2|REAL|0||0
23|extraHumid3|REAL|0||0
24|extraHumid4|REAL|0||0
25|extraHumid5|REAL|0||0
26|extraHumid6|REAL|0||0
27|extraHumid7|REAL|0||0
28|extraHumid8|REAL|0||0
29|extraTemp1|REAL|0||0
30|extraTemp2|REAL|0||0
31|extraTemp3|REAL|0||0
32|extraTemp4|REAL|0||0
33|extraTemp5|REAL|0||0
34|extraTemp6|REAL|0||0
35|extraTemp7|REAL|0||0
36|extraTemp8|REAL|0||0
37|forecast|REAL|0||0
38|hail|REAL|0||0
39|hailBatteryStatus|REAL|0||0
40|hailRate|REAL|0||0
41|heatindex|REAL|0||0
42|heatindex1|REAL|0||0
43|heatingTemp|REAL|0||0
44|heatingVoltage|REAL|0||0
45|humidex|REAL|0||0
46|humidex1|REAL|0||0
47|inDewpoint|REAL|0||0
48|inHumidity|REAL|0||0
49|inTemp|REAL|0||0
50|leafTemp1|REAL|0||0
51|leafTemp2|REAL|0||0
52|leafWet1|REAL|0||0
53|leafWet2|REAL|0||0
54|lightning_distance|REAL|0||0
55|lightning_disturber_count|REAL|0||0
56|lightning_energy|REAL|0||0
57|lightning_noise_count|REAL|0||0
58|lightning_strike_count|REAL|0||0
59|luminosity|REAL|0||0
60|maxSolarRad|REAL|0||0
61|nh3|REAL|0||0
62|no2|REAL|0||0
63|noise|REAL|0||0
64|o3|REAL|0||0
65|outHumidity|REAL|0||0
66|outTemp|REAL|0||0
67|pb|REAL|0||0
68|pm10_0|REAL|0||0
69|pm1_0|REAL|0||0
70|pm2_5|REAL|0||0
71|pressure|REAL|0||0
72|radiation|REAL|0||0
73|rain|REAL|0||0
74|rainBatteryStatus|REAL|0||0
75|rainRate|REAL|0||0
76|referenceVoltage|REAL|0||0
77|rxCheckPercent|REAL|0||0
78|signal1|REAL|0||0
79|signal2|REAL|0||0
80|signal3|REAL|0||0
81|signal4|REAL|0||0
82|signal5|REAL|0||0
83|signal6|REAL|0||0
84|signal7|REAL|0||0
85|signal8|REAL|0||0
86|snow|REAL|0||0
87|snowBatteryStatus|REAL|0||0
88|snowDepth|REAL|0||0
89|snowMoisture|REAL|0||0
90|snowRate|REAL|0||0
91|so2|REAL|0||0
92|soilMoist1|REAL|0||0
93|soilMoist2|REAL|0||0
94|soilMoist3|REAL|0||0
95|soilMoist4|REAL|0||0
96|soilTemp1|REAL|0||0
97|soilTemp2|REAL|0||0
98|soilTemp3|REAL|0||0
99|soilTemp4|REAL|0||0
100|supplyVoltage|REAL|0||0
101|txBatteryStatus|REAL|0||0
102|UV|REAL|0||0
103|uvBatteryStatus|REAL|0||0
104|windBatteryStatus|REAL|0||0
105|windchill|REAL|0||0
106|windDir|REAL|0||0
107|windGust|REAL|0||0
108|windGustDir|REAL|0||0
109|windrun|REAL|0||0
110|windSpeed|REAL|0||0
On Thu, Dec 28, 2023 at 9:33 AM Vince Skahan <[email protected]
<mailto:[email protected]> > wrote:
On Thursday, December 28, 2023 at 5:26:09 AM UTC-8 Tom Keffer wrote:
Alternatively, one could write a specialized algorithm for windchill. The
sensible thing to do would be to read a year's worth of temperature and wind
speed, all in one go --- one database access. Then use the results to calculate
the year's worth of windchill. The downside is that it's not general at all: it
would only know how to calculate windchill. The upside is that the existing
xtypes API can be used right now to do this. You'd have to write extensions for
all of your missing synthesized types.
I see two things here. One is extension(s) to handle the missing synthesized
types in archive periods moving forward. The second is some kind of standalone
utility to 'one time' catch up a legacy db with those items that you wished it
would have calculated in the ancient past. Have the standalone utility to get
the legacy pain over with 'once' so you don't have to feel that pain every 5
minutes moving forward....
Kinda like how rebuild-daily or calc-missing work (?)
--
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/d07cd082-dd2c-42bf-bef0-a051241b5388n%40googlegroups.com
<https://groups.google.com/d/msgid/weewx-development/d07cd082-dd2c-42bf-bef0-a051241b5388n%40googlegroups.com?utm_medium=email&utm_source=footer>
.
--
-Ryan Stasel
--
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/CALSG5j46SfviYdb3Ob8QX59wWzKpk_edYV8cH3QK_%3DmXvi_Zag%40mail.gmail.com
<https://groups.google.com/d/msgid/weewx-development/CALSG5j46SfviYdb3Ob8QX59wWzKpk_edYV8cH3QK_%3DmXvi_Zag%40mail.gmail.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/0d6101da3ab9%2457b82e60%2407288b20%24%40gmail.com.
