DB table "vm_stats" is keeping data more than 4 months

[GerorgeEG]

problem

DB table "vm_stats" is keeping data more than 4 months and it is resulting in the failure of API request "listVirtualMachines". I am trying to run the API call using cloud monkey and getting error "failed to decode" due to timeout. Below is the Apache Cloud Stack and Maria DB version.
acs_version: '4.19.1.2'
mariadb_version: '10.3.39'

looking for the way to reduce the query run time and reduce the DB table size for housekeeping and better performance.

versions

The versions of ACS, hypervisors, storage, network etc..

The steps to reproduce the bug

...

What to do about it?

No response

[DaanHoogland]

@GerorgeEG I think you can tweak how the table is filled and cleared by the settings vm.stats.interval and vm.stats.max.retention.time.

Also i think it is safe to truncate it. @GutoVeronezi , @JoaoJandre , please advise on this.
I think you better stop CloudStack while doing this.

[JoaoJandre]

It is most likely the same situation that led me to create #8740, where there's a timeout deleting the stats one day and since there's even more stuff to delete the next day... it just times out every day.

In #8740 I created a configuration called vm.stats.remove.batch.size batch delete the data instead of trying to delete all of it in a single query. The configuration has since been renamed to delete.batch.query.size in #9283.

You may try to set this configuration to a sane value and see if ACS is able to delete the stats. I would advise against truncating the table. If you do not set the configuration, it is likely that this behavior will come back.