Hi Vince,
From John's findings, a single table (the archive table) went from 1.1
GB to 420 MB - 62.7% decrease.
What I do not know is of your 500 MB, how many MBs is your archive table
contributing. Let us suppose that it is 1 MB - you are 100% correct.
Meh! OTOH, let us suppose it was 480 MB, reduced by 62.7% would be 179
MB. For small form factor devices, that is sizeable: 500 - 480 + 179 =
199 MB.
For me, I have an old laptop with 8 GB of memory. weewx is now in
steady-state (up 15 days since last OS update) and I use very little RAM
(692MB). However, I *just* started using WeeWX a few months ago.
I hope that helps!
---
pablo
On 2025-11-17 12:15, vince wrote:
Pablo, I know you said you're a database guy, but does any of this
really matter at all ?
My sqlite3 db is 500 MB in size after a couple weeks short of 19
years. I can live with that. It's stable. It's fast enough. It works.
On Monday, November 17, 2025 at 9:06:17 AM UTC-8 Pablo Sanchez wrote:
One thought is to to ensure that the data types also cover metric.
---
pablo
On 2025-11-17 11:15, John Smith wrote:
I haven't touched indexes, just the number type on the columns..
I went about this by getting unique values for the column and
then used that to optimise the column.
The only exception was the winddir column, it had decimal places,
but a fraction of a degree doesn't seem worth having.
On Tue, 18 Nov 2025 at 02:51, Pablo Sanchez
<[email protected]> wrote:
That's impressive!
The key is to ensure each data type for all Use Cases
captures all data.
On my mental to-do is to enable query tracing to ensure when
needed, supporting indexes are available.
---
pablo
On November 17, 2025 10:18:13 John Smith
<[email protected]> wrote:
I spoke too soon, I changed the winddir and windgustdir
fields to small ints, saving 2 bytes per value which may not
sound like much but it adds up over time...
I also changed the humidity columns to be tiny ints as well...
The above changes saved a further 20MB, and the archive
table is just a little over 170MB in size...
On Tue, 18 Nov 2025 at 01:05, John Smith
<[email protected]> wrote:
I have always been hesitant to mess with the weeWX DB in
case I broke it, but I ended up modifying the archive
table and made a custom schema to match just in case.
It turned out more fruitful than I expected as I dropped
the columns that were only full of NULLs. Worst case I
need to re-add them and then the NULLs will be re-added too.
The extended schema has some interesting columns, such
as 5 regarding hail, but does anyone know of any hail
sensors?
The archive table went from 1.1GB down to 420MB in the
process...
The only problem after doing this was weeWX exited on
start because I didn't set a default value on the new
columns I added, however after setting default values
weeWX was happy again.
Then I changed the usUnits and interval columns from Int
to tiny int, what that means is instead of MariaDB
allocating 4 bytes per value it only uses 1 byte per
value. I'm not sure setting those columns as ENUM type
would reduce things further, but the usUnits column only
has the number 17 stored and the interval column is
always set to 5...
It was then I noticed that nearly all the columns were
of type double, which seems like over kill to me as most
of the time there really is only a need for 1 decimal
place, not 16...
So I altered all the double columns and changed them to
floats, since doubles use 8 bytes per value verses 4
bytes for floats and this again greatly dropped the
table size down to 190MB all up this works out to be
almost 1/10th of it's original size.
At this point I think that's the extent that I can take
things and thought others might like to know that
trimming the archive table, without loosing data, but
recover a lot of storage space which will then also
speed up other things like backups and reduce the space
needed to store backups.
--
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/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.com
<https://groups.google.com/d/msgid/weewx-user/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.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/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com
<https://groups.google.com/d/msgid/weewx-user/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.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/CAGTinV6Eq-R9p_5%2BgrMSRxByTXa-O2CQjCUersr-A2Mj9zy3_g%40mail.gmail.com
<https://groups.google.com/d/msgid/weewx-user/CAGTinV6Eq-R9p_5%2BgrMSRxByTXa-O2CQjCUersr-A2Mj9zy3_g%40mail.gmail.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/615dea8a-e05f-46e6-84e1-f5e4cd54c377n%40googlegroups.com
<https://groups.google.com/d/msgid/weewx-user/615dea8a-e05f-46e6-84e1-f5e4cd54c377n%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/785b6063-0833-4091-acd4-6dc1cb90e2ac%40hillsandlakes.com.
