> > 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/CAGTinV7DDhkZoHSd8iHNLkyOowM_HqDzXNXfMD-HOQJyARP9gQ%40mail.gmail.com.
