Sorry. I should have prefaced my comments that they pertain to SQLite. I have no experience with MySQL.
On Fri, Oct 9, 2020 at 8:52 AM d k <[email protected]> wrote: > The size of the indexes on the archive table are <51mb in both cases. > There is no difference here. I totally agree. > > I think the reason you don't see a difference in size is because of how > null values are stored, I think in 1 byte but haven't found a reference. So > yes even if you remove 20 unused types you only remove 20 bytes which as > you point out is nothing. But the extra columns still affect read and write > performance. Write isn't a big big deal as we don't do lots of writes > anyway. But we might do lots of reads depending on what we are doing with > our station data and we probably are all running this on inexpensive slow > hardware. In my case a RPi but a new one which isn't all that slow other > than if you're comparing it to something else that's new. But, for instance > it still cut the time to make the daily summiers by more than half. Again > not like we do that often so not a huge deal. > > This is where the real change probably came from. I also changed the data > types of the observations from double (8 bytes) to float (4 bytes). Mysql > made the sqllite data type doubles instead of floats. I don't have > REAL_AS_FLOAT set and that's my fault. > > I am going to move to FLOAT(n) and set the precision on the columns next > which won't change the row length, as the columns are all still 4 bytes, > but to make things easier when I use other applications against this data > set. > > In my case the length of the data went from ~1.1 gb to <650mb in this > case. It also reduced the size of the binlogs, which get purged anyway. It > also reduced the size of the *ib* files. It cut the time to and size of > dumping the table almost by half, I haven't tried restoring yet but expect > the same. Queries run faster. > > In my opinion there are other reasons to trim the schema to fit your needs > other than the size of the data file. But yes it's more work and that > depends on how you use your data if it's worth it or not. Obviously I think > it's worth it and YMMV. > > -dk > On Friday, October 9, 2020 at 9:01:49 AM UTC-4 [email protected] wrote: > >> Trimming the schema does not make as big a difference in database size >> as you might think. >> >> For example, using my own database of 1.4M rows, trimming the schema from >> 48 observation types to 27, reduces the size from 268MB to 201MB. >> >> The reason is that most of the space is taken up by the indexes, not the >> column data. >> >> -tk >> >> On Thu, Oct 8, 2020 at 8:02 PM d k <[email protected]> wrote: >> >>> Yup.. I just found that and was about to report back I was trying it >>> that was it. Just restarted the test system to see if it went away. I think >>> I got rid of all of them now. >>> >>> Gary you are the best. Thanks so much. >>> >>> On Thursday, October 8, 2020 at 10:54:27 PM UTC-4 gjr80 wrote: >>> >>>> Hi, >>>> >>>> First up, thank you for not posting images of text, it’s makes >>>> reading/searching logs a real pain. >>>> >>>> The error is due to a skin trying to generate a plot that involves >>>> extraTemp1 and from the short log extract I would guess that this is >>>> from the Seasons skin. If you look in the Seasons skin config file >>>> (skins/Seasons/skin.conf) under [ImageGenerator] you will find the daytemp, >>>> weektemp, monthtemp and yeartemp plots use extraTemp1 (and extraTemp2 and >>>> extraTemp3). Easiest fix is to comment out those plots, eg: >>>> >>>> # [[[daytemp]]] >>>> # yscale = None, None, 0.5 >>>> # [[[[extraTemp1]]]] >>>> # [[[[extraTemp2]]]] >>>> # [[[[extraTemp3]]]] >>>> >>>> Save skin.conf and the error should go away on the next report cycle. >>>> >>>> Gary >>>> On Friday, 9 October 2020 at 12:29:14 UTC+10 [email protected] wrote: >>>> >>>>> I tried to post this as an image but it doesn't show. So here is the >>>>> text. >>>>> >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> Caught unrecoverable exception in generator >>>>> 'weewx.imagegenerator.ImageGenerator' >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** extraTemp1 >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** Traceback (most recent call last): >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** File "/usr/share/weewx/weewx/reportengine.py", line 197, >>>>> in >>>>> run >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** obj.start() >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** File "/usr/share/weewx/weewx/reportengine.py", line 280, >>>>> in >>>>> start >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** self.run() >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** File "/usr/share/weewx/weewx/imagegenerator.py", line 41, >>>>> in run >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** self.genImages(self.gen_ts) >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** File "/usr/share/weewx/weewx/imagegenerator.py", line 176, >>>>> in genImages >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** start_vec_t, stop_vec_t ,data_vec_t = >>>>> weewx.xtypes.get_series(var_type, >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** File "/usr/share/weewx/weewx/xtypes.py", line 91, in >>>>> get_series >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** raise weewx.UnknownType(obs_type) >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** weewx.UnknownType: extraTemp1 >>>>> Oct 8 20:03:19 prometis weewx[271870] ERROR weewx.reportengine: >>>>> **** Generator terminated >>>>> Oct 8 20:03:19 prometis weewx[271870] DEBUG weewx.reportengine: >>>>> Report 'SmartphoneReport' not enabled. Skipping. >>>>> >>>>> >>>>> On Thursday, October 8, 2020 at 9:39:14 PM UTC-4 Duane Kerzic wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> Thanks for all the help you provided last time around. Thanks in >>>>>> advance this time for your help. >>>>>> >>>>>> I wanted to clean up weewx.archive table and make it a bit smaller. >>>>>> So I deleted the columns I don't think I'll ever use. But now I'm getting >>>>>> this in the system log. >>>>>> >>>>>> >>>>>> I'm guessing that extraTemp1 is coded into one of those files but I >>>>>> haven't looked to find out yet. >>>>>> >>>>>> I've shortened the average row length of the archive table to 126 >>>>>> from 217 bytes. Huge difference when you have 10 years of data. >>>>>> >>>>>> -dk >>>>>> >>>>> -- >>> 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 on the web visit >>> https://groups.google.com/d/msgid/weewx-user/02d0a56e-c9fc-4e48-a74a-cdb6291474bbn%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/02d0a56e-c9fc-4e48-a74a-cdb6291474bbn%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 on the web visit > https://groups.google.com/d/msgid/weewx-user/853cbe79-ee93-49b3-90c3-6a9a02dab1b7n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/853cbe79-ee93-49b3-90c3-6a9a02dab1b7n%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 on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBEV6hdoiKhdXoLzubTwz5e7ScPP-T0QEHg%2BD%2B_ukwCXA%40mail.gmail.com.
