High CPU on 5.10

[Mark Jenks]

Just built a new weewx on my Fedora 38 this morning, and attached it to my mariadb.  It has 13 years worth of data in it.

I am running Belchertown and mqtt, and installed Windy.  But I just removed Windy to see if that was it, but still does it.  Python and/or moriadb goes to 100% CPU.
Mariadb says the high query during that time is:  SELECT * FROM archive WHERE dateTime > 1320346500 AND dateTime <= 1716824400 ORDER BY dateTime ASC
But that makes no sense, since I can query my entire archive table (select *) in about 3 seconds.

Any thoughts on how to catch what is going on?   I could enable debug, but hoping for a better way other that looking at a ton of logs.

Thanks!

[Tom Keffer]

That query is asking for every single record over 4 years of data --- about 400,000+ records with a 5 minute archive interval. That's not the use pattern when an xtype is causing the problem.

It's hard to imagine why the Belchertown skin would need data at that density. Try isolating the problem by shutting off the imagegenerator, then the cheetahgenerator (you can do that by modifying generator_list in skin.conf).

Then once you know which one is the culprit, then start trimming their respective sections in skin.conf until you isolate the plot or tag that is causing the problem.

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/a882fb44-d36b-4bf4-8588-41199524b5f7n%40googlegroups.com.

[vince]

Wild guess here. Belchertown writes its own NOAA directory with its own copies of those files that it creates, so verify that you have all the year and month files for all your data in the NOAA dir under the belchertown output directory.  It’s ok to prepopulate it from old copies as needed to speed things up.

I have almost 18 years of data in sqlite3 locally on SD and the pi4 doesn’t work too hard every archive period keeping up with belchertown, a custom hacked variant of standard, a couple extensions and other skins, and both mqtt subscribe and publish.

[Mark Jenks]

Bad news?  I uninstalled belchertown, and it's still hitting high CPU on mariadb and python3.  This is the hit to the DB.

SELECT * FROM archive WHERE dateTime > 1704088800 AND dateTime <= 1735711200 ORDER BY dateTime ASC

[Mark Jenks]

I just ripped mqtt out also.  I'll check it in the morning and see if that makes a difference.

[Mark Jenks]

false alarm so far.  mqtt is tied to belchertown.    So, seems to be belchertown like you suspected.   

Digging more tomorrow.

[Mark Jenks]

I removed the last two from skin.conf, and it still does the large query.   I'll keep digging tonight.

generator_list = weewx.cheetahgenerator.CheetahGenerator, weewx.reportengine.CopyGenerator, user.belchertown.HighchartsJsonGenerator

[vince]

You seem to be hacking randomly and praying.  I'd suggest deleting Belchertown at this point, verifying it is deleted, then add it back in.  Do not customize it yet.  Just run the default setup, it works.  Really.

My guess is you are missing the appTemp element in the db.  There are a bunch of threads on this re: Belchertown and v5 weewx.

Check your mariadb schema and see if it is missing appTemp.  If you have the old wview-compatible db you'd have about 50 elements in it.  If you later switched to the v4 and above bigger wview-extended schema, you'd have 114 elements in it.  Sorry but I don't know the mariadb commands to count them nor check.

For sqlite3 it would be:  'echo ".schema" | sqlite3 mydbname  | wc -l'

[Tom Keffer]

I thought of the appTemp angle, but those queries involve thousands of small aggregate intervals, not one query over 4 years. 

Still, it's worth a try.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/e42cdc71-32a2-4d36-9169-f2c195ccf28dn%40googlegroups.com.

[vince]

The other test would be to disable belchertown, enable seasons, and see if that’s ok. That would check the db vs. the v5 calculated aka synthetic readings feature that slowed some folks down.

[mh081...@gmail.com]

I think this was the Problem with missing Database extended schema object. I had the same problem as i upgraded from weewx 4 to 5. I solved this with.

weectl database add-column appTemp
weectl database add-column cloudbase
weectl database add-column visibility
weectl database add-column windrun
weectl database add-column cloud_cover
weectl database add-column aqi
weectl calc-missing
weectl calc-missing appTemp

[Mark Jenks]

Ripping out Belchertown completely.  I did it before, but didn't let it sit for very long.    Will see if the problem exists with Seasons.

[Mark Jenks]

Here is my Archive table;

localhost/weewx/ http://phpmyadmin.mjenks.net/index.php?route=/database/sql&db=weewx
Your SQL query has been executed successfully.

show columns from archive;



dateTime int(11) NO PRI NULL
usUnits int(11) NO NULL
interval int(11) NO NULL
barometer double YES NULL
pressure double YES NULL
altimeter double YES NULL
inTemp double YES NULL
outTemp double YES NULL
inHumidity double YES NULL
outHumidity double YES NULL
windSpeed double YES NULL
windDir double YES NULL
windGust double YES NULL
windGustDir double YES NULL
rainRate double YES NULL
rain double YES NULL
dewpoint double YES NULL
windchill double YES NULL
heatindex double YES NULL
ET double YES NULL
radiation double YES NULL
UV double YES NULL
extraTemp1 double YES NULL
extraTemp2 double YES NULL
extraTemp3 double YES NULL
soilTemp1 double YES NULL
soilTemp2 double YES NULL
soilTemp3 double YES NULL
soilTemp4 double YES NULL
leafTemp1 double YES NULL
leafTemp2 double YES NULL
extraHumid1 double YES NULL
extraHumid2 double YES NULL
soilMoist1 double YES NULL
soilMoist2 double YES NULL
soilMoist3 double YES NULL
soilMoist4 double YES NULL
leafWet1 double YES NULL
leafWet2 double YES NULL
rxCheckPercent double YES NULL
txBatteryStatus double YES NULL
consBatteryVoltage double YES NULL
hail double YES NULL
hailRate double YES NULL
heatingTemp double YES NULL
heatingVoltage double YES NULL
supplyVoltage double YES NULL
referenceVoltage double YES NULL
windBatteryStatus double YES NULL
rainBatteryStatus double YES NULL
outTempBatteryStatus double YES NULL
inTempBatteryStatus double YES NULL

[Mark Jenks]

Seasons itself doesn't do it, and Belchertown with no settings on it is doing it.   
So, I'm adding those columns now and recalculating now.

On Tuesday, May 28, 2024 at 1:15:56 PM UTC-5 mh081...@gmail.com wrote:

[Mark Jenks]

 I ran this, and did the recalc-missing, and it seems to be running better.   Except there is only one record in those tables.

Not sure why that would cause that issue.

weectl database add-column appTemp
weectl database add-column cloudbase
weectl database add-column visibility
weectl database add-column windrun
weectl database add-column cloud_cover
weectl database add-column aqi
weectl calc-missing

[Ian Millard]

Should the last command be: -

weectl database calc-missing 

Sent from my iPad.

On 30 May 2024, at 03:28, Mark Jenks <mjenk...@gmail.com> wrote:

 I ran this, and did the recalc-missing, and it seems to be running better.   Except there is only one record in those tables.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/ae663ace-fe2d-4951-be0f-2fbe54c57944n%40googlegroups.com.

[Mark Jenks]

Yes, that was it.