Oracle VM manager: tabla OVM_STATISTIC y su crecimiento descontrolado ovm

por | 6 mayo, 2020

En instalaciones de Oracle VM, la tabla que almacena las estadísticas de Oracle OVM Manager suele crecer de forma descontrolada. Si no se toma ninguna acción al respecto, termina con toda probabilidad (si no está bien monitorizado) provocando el llenado del filesystem /u01.

[root@ovm-manager ovs]# ls -ltrh | grep -i stat
-rw-rw----. 1 oracle dba  13K Jul 11  2016 OVM_STATISTIC.frm
-rw-rw----. 1 oracle dba 8.4K Jul 11  2016 Mgr_StatisticManager.frm
-rw-rw----. 1 oracle dba 8.4K Jul 11  2016 Mgr_StatsIntervalAdjusterTask.frm
-rw-rw----. 1 oracle dba  96K Apr 11 02:07 Mgr_StatisticManager.ibd
-rw-rw----. 1 oracle dba  96K Apr 11 02:07 Mgr_StatsIntervalAdjusterTask.ibd
-rw-rw----. 1 oracle dba  27G Apr 11 02:27 OVM_STATISTIC.ibd

# /sbin/service ovmcli stop 
# /sbin/service ovmm stop
# /sbin/service ovmm_mysql stop


# /sbin/service ovmm_mysql start

Si no tienes demasiado apego a estas estadísticas (como la mayoría, supongo), puedes tranquilamente truncar la tabla y esperar a que vuelva a llenarse (o crear un script vía crontab que lo haga periódicamente por ti). Este problema (¡o feature!) tiene lugar en instalaciones de OVMM bajo MySQL así que para truncar la tabla, accederemos a la shell de MySQL, base de datos ovs y ejecutaremos el truncado:

[root@ovm-manager ovs]# mysql ovs -u ovs -p -S /u01/app/oracle/mysql/data/mysqld.sock
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3201
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
 
mysql> use ovs
Database changed
mysql>  truncate table OVM_STATISTIC;
Query OK, 0 rows affected (1.03 sec)
 
mysql>

Truncate via crontab


 
/usr/bin/mysql -uovs -pexample -hlocalhost -Dovs -e"TRUNCATE TABLE OVM_STATISTIC"



Now there is another option - using a MySQL event
CREATE EVENT update_date_column ON SCHEDULE EVERY 1 HOUR STARTS NOW() DO TRUNCATE TABLE juicebox;
If you'll decide to go with an event approach:
use SHOW EVENTS to list created events with their attributes (e.g. status)
use SHOW PROCESSLIST to check if the event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler".
use SET GLOBAL event_scheduler = ON;to enable the scheduler if it's currently not enabled.
More on configuring event scheduler read here