temboard
temboard copied to clipboard
dalibo
Cannot purge data
Hi,
My running temboard server uses the package temboard-7.4-1.el7.noarch under CentOS Linux 7.6. My configuration file has these lines:
[monitoring]
# purge_after = 365
purge_after = 15
[statements]
# purge_after = 7
purge_after = 15
I restarted the temboard service 3 days ago but the purge doesn't seem to work. I still have old rows.
select * from metric_blocks_history order by 1;
["2020-12-17 11:36:29.537474+01","2020-12-17 14:03:07.066382+01") | 4 | area | {"(\"2020-12-17 11:36:29.537474+01\",00:01:00.99291
4,0,101,98.3852261098282)","(\"2020-12-17 11:37:30.683598+01\",00:01:01.135978,0,100,98.3852261581906)","(\"2020-12-17 11:38:31.687519+01\",00:01:01.01363
8,0,99,98.3852262060693)","(\"2020-12-17 11:39:32.593038+01\",00:01:etc.................
The size of my database keeps growing DbAlias Date Size Gb Used Gb Date PG_TEMBOARD_PFI 2021-03-25 37.60 37.60 2021-03-25 03:02:25 PG_TEMBOARD_PFI 2021-03-24 37.49 37.49 2021-03-24 03:02:05 PG_TEMBOARD_PFI 2021-03-23 37.43 37.43 2021-03-23 03:01:54 PG_TEMBOARD_PFI 2021-03-22 37.33 37.33 2021-03-22 03:02:14
Thank you for your help.
Regards.
There is a display issue. purge_after = 365 and purge_after = 7 are in fact commented. There is a # before the lines but for some reasons lines with a leading # are display in bold characters.
Dear @pgiraud
I have set purge_after to 186 days.
$ cat /etc/temboard/temboard.conf
...
[monitoring]
# Set the amount of data to keep, expressed in days
purge_after = 186
...
However, I still see entries which are exactly 365 days old.
(postgres@[local]:58888)[temboard]> select history_range, dbname from metric_db_size_history order by 1 asc limit 100000;
history_range | dbname
-------------------------------------------------------------------+---------------
["2020-04-01 13:11:28.829781+02","2020-04-01 16:06:42.231948+02") | xpr
["2020-04-01 13:11:28.829781+02","2020-04-01 16:06:42.231948+02") | postgres
["2020-04-01 13:11:28.829781+02","2020-04-01 16:06:42.231948+02") | repmgr
["2020-04-01 13:11:28.829781+02","2020-04-01 16:06:42.231948+02") | template1
["2020-04-01 13:11:30.307713+02","2020-04-01 16:06:59.742439+02") | xms
["2020-04-01 13:11:30.307713+02","2020-04-01 16:06:59.742439+02") | postgres
["2020-04-01 13:11:30.307713+02","2020-04-01 16:06:59.742439+02") | repmgr
["2020-04-01 13:11:30.307713+02","2020-04-01 16:06:59.742439+02") | template1
["2020-04-01 13:11:30.620942+02","2020-04-01 16:10:56.063988+02") | barman
["2020-04-01 13:11:30.620942+02","2020-04-01 16:10:56.063988+02") | postgres
["2020-04-01 13:11:30.620942+02","2020-04-01 16:10:56.063988+02") | template1
["2020-04-01 13:11:33.179833+02","2020-04-01 16:11:03.746454+02") | barman
["2020-04-01 13:11:33.179833+02","2020-04-01 16:11:03.746454+02") | postgres
["2020-04-01 13:11:33.179833+02","2020-04-01 16:11:03.746454+02") | template1
["2020-04-01 13:11:39.353723+02","2020-04-01 16:07:07.852638+02") | xnr
Did I missunderstand something about this parameter? Shouldn't be the olderst entries I find, 186 days old?
Cheers William
Hello team,
Is there any news about this issue please?
The temboard repository database is full despite purge_after = 15 is present in [monitoring] and [statements] section in the /etc/temboard-agent/
Versions in temboard UI:
temboard --version
INFO: Starting temboard 7.4.
temBoard 7.4
System Red Hat Enterprise Linux Server 7.9 (Maipo)
Python 2.7.5 (/usr/bin/python)
psycopg2 2.5.1 (dt dec pq3 ext)
Tornado 4.2.1
SQLAlchemy 0.9.8
alembic 0.8.3
temboard-agent version:
temboard-agent --version
INFO: Starting temboard-agent 7.5.
7.5
If it is a bug, is there a workaround to purge mannualy?
Thanks and Regards.
Hi everybody,
I would like to share with you my manually purge until this issue is resolved.
RETENTION_DAY=15 DBNAME=temboard
psql -t $DBNAME <<-EOF > /tmp/tmp$$ 2>&1 \set ON_ERROR_STOP
BEGIN ;
\echo Purge of tables monitoring.metric_bgwriter* ... DELETE FROM monitoring.metric_bgwriter_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_bgwriter_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_bgwriter_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_bgwriter_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_blocks* ... DELETE FROM monitoring.metric_blocks_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_blocks_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_blocks_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_blocks_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_btree_bloat* ... DELETE FROM monitoring.metric_btree_bloat_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_btree_bloat_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_btree_bloat_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_btree_bloat_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_cpu* ... DELETE FROM monitoring.metric_cpu_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_cpu_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_cpu_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_cpu_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_db_size* ... DELETE FROM monitoring.metric_db_size_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_db_size_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_db_size_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_db_size_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_filesystems_size* ... DELETE FROM monitoring.metric_filesystems_size_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_filesystems_size_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_filesystems_size_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_filesystems_size_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_heap_bloat* ... DELETE FROM monitoring.metric_heap_bloat_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_heap_bloat_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_heap_bloat_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_heap_bloat_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_loadavg* ... DELETE FROM monitoring.metric_loadavg_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_loadavg_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_loadavg_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_loadavg_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_locks* ... DELETE FROM monitoring.metric_locks_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_locks_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_locks_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_locks_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_memory* ... DELETE FROM monitoring.metric_memory_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_memory_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_memory_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_memory_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_process* ... DELETE FROM monitoring.metric_process_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_process_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_process_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_process_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_replication_connection* ... DELETE FROM monitoring.metric_replication_connection_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_connection_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_connection_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_connection_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_replication_lag* ... DELETE FROM monitoring.metric_replication_lag_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_lag_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_lag_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_replication_lag_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_sessions* ... DELETE FROM monitoring.metric_sessions_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_sessions_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_sessions_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_sessions_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_tblspc_size* ... DELETE FROM monitoring.metric_tblspc_size_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_tblspc_size_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_tblspc_size_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_tblspc_size_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_temp_files_size_delta* ... DELETE FROM monitoring.metric_temp_files_size_delta_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_temp_files_size_delta_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_temp_files_size_delta_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_temp_files_size_delta_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_vacuum_analyze* ... DELETE FROM monitoring.metric_vacuum_analyze_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_vacuum_analyze_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_vacuum_analyze_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_vacuum_analyze_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_wal_files* ... DELETE FROM monitoring.metric_wal_files_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_wal_files_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_wal_files_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_wal_files_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
\echo Purge of tables monitoring.metric_xacts* ... DELETE FROM monitoring.metric_xacts_30m_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_xacts_6h_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_xacts_current WHERE datetime < now()-'$RETENTION_DAY day'::interval; DELETE FROM monitoring.metric_xacts_history WHERE lower(history_range) < now()-'$RETENTION_DAY day'::interval;
COMMIT; EOF
Regards.
Hello! I have a similar problem, but with the "statements" table. The data in it is not cleared, although there is a function to delete data "statements_purge" and the table continues to grow. I have to truncate the cascade of the "statements" table, but the history is lost. Is there a solution to this problem?
Do you have some errors raised in logs ? This issue seems related to #931 .
Hi,
Yes indeed. Those two issues are related. According to what I can read, the bug will be fixed in version 7.9, right ?
Regards.
Hi! I updated to version 7.10. Launched on a clean database (PostgreSQL 14.2). The "statements_purge(_ndays integer)" function deletes data only from the "statements_history" table, but does not affect the data from the "statements" table and the table grows anyway (toast occupies 90% of the total volume).
What other information can I provide?
INFO: Starting temboard 7.10. temBoard 7.10 System Red Hat Enterprise Linux Server 7.9 (Maipo) Python 2.7.5 (/usr/bin/python) Tornado 4.2.1 psycopg2 2.5.1 (dt dec pq3 ext) libpq 14.2 SQLAlchemy 1.3.6 alembic 0.8.3