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/2005aba4-7af5-4a6b-aacd-8bb04a200e40%40hillsandlakes.com.
