Yeah, thanks, found mysqltuner to get some advice on server settings. Op woensdag 14 oktober 2026 om 02:08:13 UTC+2 schreef Clay Jackson:
> I'm running a 3 node mysql cluster with mysqlrouter running on a separate > server, with 1M records and not seeing any performance issues at all. I'd > recommend checking the database settings on your database server. > > On Tuesday, October 13, 2026 at 7:17:26 AM UTC-7 David Hathaway wrote: > >> 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/4aae8adc-f5c1-4fb5-b7d5-ba1dda1be8c7n%40googlegroups.com.
