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] <[email protected]> Sent: Friday, December 29, 2023 8:33 AM To: 'Tom Keffer' <[email protected]> Cc: 'Vince Skahan' <[email protected]>; 'weewx-development' <[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/027701da3a7f%243536eaa0%249fa4bfe0%24%40gmail.com.
