HUGE difference between local database and remote database

[Maarten van der Hoeven]

Hi,

WeeWX is running very well. Up-to-date. Archiving record to MySQL-database, 7 million records. Database server created with docker compose, MySQL-config untouched.

I moved the database to a VPS. Created with the same compose file, so database server config is identical. Weewx remained on my host. Restoring the database on VPS took about 15 minutes (acceptable), executed from my host 

mysql -uxxx -pxxx -hyyy weewx < weewx.sql (where yyy is IP from VPS)

Now, when I point weewx (still on my host) to the vps database, it sees the database is 6 hours old (time I made the backup) and it catches up. Catching up records is extremely slow, about 1 record every 10 seconds.

I dont understand. 

- Full restore from host to vps in 15 minutes (about 7500 records a second). 

- Catching up (host to vps) 1 record every 10 seconds. 

- Catching up (host to host) 20 records a second (more or less)

Any idea what the issue might be? 

Database server specs (host and vps) are identical. 

Weewx and all compute remained on my host.

Latency to VPS is very good (about 6ms). 

Specs VPS: 1 Core, 2Gb RAM,10Gb storage NVMe. (not great, might upgrade, but first understand this performance difference). 

Firewall VPS is irrelevant (did the test with firewall off and on)

Any clues/pointers and I can dig into it. Thanks!

[John Smith]

You didn't state your goal here, or what configuration you plan to have in future, but another option is to do MySQL replication between servers.

--
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 weewx-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/7a3848f1-415d-4fd4-b029-f3719d313c05n%40googlegroups.com.

[Maarten van der Hoeven]

Ah yes, you are right, forgot my goal. Which is reducing the heavy load on my host-it-all-machine and get the fans more quiet. Replication is not an option, as this still means the database (and everything else) runs on the same host.

Replacing the host is a solution of course.

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.

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.

Op maandag 12 oktober 2026 om 15:20:00 UTC+2 schreef John Smith:

[Pablo Sanchez]

Hi,

Given that your CPU fans are howling, I would confirm which process(es) are consuming the most CPU. My DBMS lives in memory and hardly consumes any CPU. OTOH, during report generation, weewx uses a bit for about 20 seconds (per five minute cycle).

I'd suggest looking for tools to identify the top CPU consumer, resolve it and go to the next.

---

pablo

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/8fa0c196-045c-4896-a0d3-2e7192e7daaen%40googlegroups.com.

[John Smith]

Does it require quite a different database server setup?

Without more details on the VPS database schema it would be difficult to tell, but when things slow down that much it could simply be missing indexes causing the database to check all rows before allowing an insertion.

Which is reducing the heavy load on my host-it-all-machine and get the fans more quiet.

If the local system runs hot it may be the same problem, missing indexes on tables.

I could take a look at SQLite on my host, solving the screaming fans problem another way.

Well that may fix the problem because while setting the SQLite file up the missing indexes could be added

Replication is not an option, as this still means the database (and everything else) runs on the same host.

Well in a replication setup the local system would do hardly anything as you would run reports on the remote system 

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/8fa0c196-045c-4896-a0d3-2e7192e7daaen%40googlegroups.com.

[Greg Troxel]

Maarten van der Hoeven <hoev...@gmail.com> 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".

[Maarten van der Hoeven]

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:

[John Smith]

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? 

--

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 weewx-user+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/49e384a7-7163-4178-96b4-8e6f0d135598n%40googlegroups.com.

[David Hathaway]

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?

[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.

[Maarten van der Hoeven]

That's quite a difference indeed! Odd...

Maybe it's hardware related, guessing here. Can you share what CPU is in your box? Mine is an Intel Core I7-10710U, 6 cores, clocking at 1.6GHz (32Gb RAM, NVMe storage). It's inside a NUC I bought 6 years ago.

No crypto mining here I am 100% sure :)

Op dinsdag 13 oktober 2026 om 16:17:26 UTC+2 schreef David Hathaway:

[Maarten van der Hoeven]

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:

[John Smith]

Why didn't you do the simple first, enable the slow log and then add missing indexes?

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/4aae8adc-f5c1-4fb5-b7d5-ba1dda1be8c7n%40googlegroups.com.

[Pablo Sanchez]

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

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAGTinV6%3D2c81DHaC8C8D2MYxyRfzZMme%3Ddn758tncpKpcQBNJQ%40mail.gmail.com.

[David Hathaway]

Mine is a KVM hosted on a AMD Ryzen 7 8845HS.  This is from fastfetch:

OS

: Ubuntu 24.04.5 LTS (Noble Numbat) x86_64

Host

: KVM/QEMU Standard PC (Q35 + ICH9, 2009) (pc-q35-8.1)

Kernel

: Linux 6.8.0-146-generic

CPU

: AMD Ryzen 7 8845HS (2) @ 3.79 GHz

GPU

: Red Hat, Inc. QXL paravirtual graphic card

Memory

: 1.04 GiB / 3.82 GiB (

27%

)

Swap

: 0 B / 2.37 GiB (

0%

)

Disk (/)

: 9.01 GiB / 11.21 GiB (

80%

) - ext4

The host OS is Project Bluefin, based on Fedora Silverblue atomic Linux.

CPU is currently a 1% for the host.