Using Trim on MYSQL Timeseries Feed doesn't free up storage

Emoncms
, ,
1

Hey everyone,

I’m having some trouble with the trim function.

My goal is to free up some space on my cloud server hosting emoncms database by deleting data older than a certain date using the trim function, in this case data older than August 16.

After using trim, when viewing feed data using the graph view, the data points are gone but the feed size on disk is still the same. How can the data be gone but still take up storage space?

I’m wondering what the cause of this is. I’m currently using the MYSQL Timeseries as the feed engine, and I’m wondering if it’s a problem the Trim function has with this time series engine. Anyone know what’s going on?

2

The MySQL reference page says trim:

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

examples from that page:

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

trim doesn’t delete the entire data field.
It deletes whitespace or user specified prefixes/suffixes from your data.

Ref:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_trim

3

This isn’t the trim feature that Mihai is asking about. I think they mean the one when you select a feed and click on the trash can button:

image

@MBadea you might need to ask MySQL to optimise the table after you trimmed it, with this SQL query: OPTIMIZE TABLE feed_123 (where 123 is the id of your feed).

EmonCMS should probably do this step automatically after a successful trim.

4

Yes. That would indeed make a LOT more sense. Thanks for pointing it out.
I quit using emonCMS about 4 years ago so am more than a bit behind the power curve.

5

Thanks Tim, that is exactly what I said asking about!

I’m quite uninitiated at interacting with MYSQL directly, and I’m not quite certain what’s the best way to give the query to the database on my current setup.

For reference, my setup is that I have Emoncms installed and running on an Ubuntu remote cloud server that I interact with using my SSH client Putty. How would you recommend I go about giving the query?