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]> 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] <[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]> > *Sent:* Friday, December 29, 2023 5:09 AM > *To:* Ryan Stasel <[email protected]> > *Cc:* Vince Skahan <[email protected]>; weewx-development < > [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]> 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]> > 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]. > 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]. > 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/CAPq0zECCg%3DNLk0igic%2BkojqU9Ds2ygHL9R%2Bappgy3g7nfGethA%40mail.gmail.com.
