Something seems weird. My eldest record is from 2014, and I have used it more or less continuously since then. I have 3.4M records. Even so, the VM where weewx and MariaDb live are at 0% CPU and are using 1Gb of memory. Oops, it just spiked to 10% CPU when it added a record.
I think your 7M records and hot box point to something else. Is someone mining crypto that you don't know about? On Monday, October 12, 2026 at 6:52:18 PM UTC-5 John Smith wrote: > I suppose I cannot improve on table indexing...? >> > > That depends how long since the table was created, because there have been > changes every so often, and as Greg pointed out you may be asking for data > in skins that isn't indexed and adding some can really speed things up. > > I would start by enabling the slow logging in MySQL as that will tell you > what's causing so much load. > > I just checked every weewx-table, all have a Primary key on dateTime (int >> 11). > > > Most tables have so few rows it doesn't matter, but that would be a big > clue if true on the archive table. > > my reports are a 1 minute cycle, > > > Another clue, what do you gain from doing that, that using MQTT to publish > loop data wouldn't be better for? > > On Tue, 13 Oct 2026 at 02:43, Maarten van der Hoeven <[email protected]> > wrote: > >> All, thx for the reponses and suggestions. >> >> I run weewx out of the box. Table layout, structures, field definitions, >> indices are untouched. Its how the maker wanted it, didnt change that. >> I just checked every weewx-table, all have a Primary key on dateTime (int >> 11). >> My weewx.conf is pretty factory default. Polling every minute a Davis VP2 >> (IP). >> Running two StdReports: the [[SeasonsReport]] and [[new=belchertown]]. No >> StdRESTful services. >> >> I suppose I cannot improve on table indexing...? >> >> The VPS database schema is the provided >> weewx.schemas.wview_extended.schema (same on host). >> >> This helps me boiling down where to look! Which are: >> * putting the DMBS into memory. There is 32Gb, it will fit :) >> * checking on CPU consumers >> * I might check on the charts for too many datapoints when not needed >> (aggregations on year charts differs from day charts, for example) >> * my reports are a 1 minute cycle, having archive interval at every 1 >> minute. Maybe I can take another interval int consideration... >> * will check (innodb logging) on query execution time, hope to find long >> running queries >> >> Thanks, much appreciated! >> >> Op maandag 12 oktober 2026 om 16:55:05 UTC+2 schreef Greg Troxel: >> >>> Maarten van der Hoeven <[email protected]> writes: >>> >>> > But besides I want the database load away from my host, my question >>> has >>> > also a curious/technical/want-to-know background. What explains this >>> huge >>> > difference I described? Does it require quite a different database >>> server >>> > setup? I find that hard to believe. To some extent perhaps, but not >>> like 1 >>> > record in 10 seconds vs 30 records in 1 second. >>> >>> It does not really make sense to compare restoration speed with >>> transaction speed. I would ignore "load speed and transaction speed are >>> different" and instead look at "what is causing my transaction speed to >>> be what it is". >>> >>> Besides the index comment which seems wise, you haven't established if >>> the problem is from round trips or from database execution time. >>> >>> > Might be I am getting out of weewx scope here...? I could take a look >>> at >>> > SQLite on my host, solving the screaming fans problem another way. >>> >>> It doesn't make sense that mysql just being the weewx database should >>> take so much cpu time. Unless you are running reports that aggregate >>> data over all time maybe, but the solution is "don't do that". >>> >> -- >> > 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 [email protected]. >> > To view this discussion visit >> https://groups.google.com/d/msgid/weewx-user/49e384a7-7163-4178-96b4-8e6f0d135598n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/weewx-user/49e384a7-7163-4178-96b4-8e6f0d135598n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/c7bfa614-6342-4bd5-a702-8f48b11fa1e9n%40googlegroups.com.
