Why didn't you do the simple first, enable the slow log and then add missing indexes?
On Sat, 17 Oct 2026 at 23:51, Maarten van der Hoeven <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/weewx-user/4aae8adc-f5c1-4fb5-b7d5-ba1dda1be8c7n%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/CAGTinV6%3D2c81DHaC8C8D2MYxyRfzZMme%3Ddn758tncpKpcQBNJQ%40mail.gmail.com.
