Assuming that OP identified the DBMS as the top CPU consumer, tuning the
DBMS will likely bear no measurable gains. The gains will come from tuning
the SQL - in this case, adding missing indexes.
I'm pretty certain that John previously mentioned that an in-memory full
table scan manifests itself as 100% CPU. Adding indexes reduces the SQL
data path, thus reducing the (CPU) demand.
John's process is the correct method to identify, tune and validate the
solution. You iterate through the log, picking off the top consumer until
you're satisfied.
The above is predicated on this application's database being memory resident.
FYI: l am semi retired database geek.
---
pablo
On October 18, 2026 07:05:25 John Smith <[email protected]> wrote:
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.
--
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.
--
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.
--
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/1a14ee1f690.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com.
