Until V4.0 (released 30-Apr-2020), the default database was what we call the "wview" database, with 49 observation types. After that, the default became the "wview_extended" database with 122 entries. That's over twice the size.
Some of the disk space is taken up by the indices, so it's not a straight linear relationship between entries and database size. If you're really concerned about disk space, it certainly makes sense to drop unused columns. -tk On Mon, Nov 17, 2025 at 11:03 AM Pablo Sanchez <[email protected]> wrote: > Hi Vince, > > Everything is in memory eh? The only question is how much of it. ;) > > The DBMS either performs an in-memory I/O (logical I/O) or a physical I/O > followed by a lio. > > As for memory leaks, it's doubtful that it is an O/S issue but more than > likely a user land leak. Ah, in fact, you mention that it may be related to > a Python library (user land). > > I'm curious why John's archive table is large compared to yours. Perhaps > the MariaDB data types are different than sqlite. > > Side note: I mentioned elsewhere, I'm very new to weewx (June of this > year). I thought about using Sqlite rather than MariaDB. In the end, I > thought that I could (eventually?) contribute by ensuring that the queries > had supporting indexes when needed. Not all queries need indexing. I could > use the MariaDB tools to possibly fold in changes to Sqlite. > > Thx! > --- > pablo > > On November 17, 2025 13:19:30 vince <[email protected]> wrote: > >> Disk is cheap. The db is not in memory so size of the db does not matter. >> >> Weewx uses a small amount of memory. I used to run it on an ancient ARM >> box that had 128 MB of RAM. It ran fine. >> >> The absolute amount of RAM you use depends on os and platform. On a pi5 >> mine is around 110 MB RSS and 12 MB shared memory used. If you watch for >> weeks you might see what looks like memory leaking depending on your os, >> what actual graphing you are doing, and what underlying python imaging >> library versions are installed. This is python imaging library at fault >> under the hood. Again this doesn't really matter, since the os handles it >> anyway automatically. >> >> For my sqlite3 db.... >> >> archive table is 439 MB in 1,866,971 records >> the entire db is 501 MB >> for a couple weeks short of 19 years data >> >> We looked at the size of the archive table in detail back in early 2019 >> (wow) during weewx 4.0 development and concluded it was worth the >> flexibility to cook up the wview_extended bigger schema rather than to try >> to try to needlessly optimize for size. You could delete all kinds of >> columns that aren't used, but it's not really going to save you anything >> worth the time to do so, and you'll put yourself at risk of all kinds of >> side effects if you delete things the various skins expect to be there >> (example - Belchertown takes 'ages' to run its reports if you are missing a >> couple columns that it expects) >> >> On Monday, November 17, 2025 at 9:28:09 AM UTC-8 Pablo Sanchez wrote: >> >>> 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/05d7deb5-0d16-40c4-9b54-b4c861f0af18n%40googlegroups.com >> <https://groups.google.com/d/msgid/weewx-user/05d7deb5-0d16-40c4-9b54-b4c861f0af18n%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/19a9332bfc0.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com > <https://groups.google.com/d/msgid/weewx-user/19a9332bfc0.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/CAPq0zEDRd-wT%2B56uik8cCp6q0%2BZKsZEgcf7yPc8SbcB0%2BabVMQ%40mail.gmail.com.
