Ad Widget

Collapse

Postgres DB huge size

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tristis Oris
    Member
    • Sep 2015
    • 66
    #1

    Postgres DB huge size

    PG 16, timescale. That begins
    1. after 7.0.5 update
    or
    2. migration fix from https://support.zabbix.com/browse/ZBX-24955

    DB growth 5>40Gb, the whole disk size, in 2 days.


    Click image for larger version Name: image.png Views: 354 Size: 76.0 KB ID: 493508
    Tags: None
    • Tristis Oris
      Member
      • Sep 2015
      • 66
      #2
      looks i missed some migrations.
      7 TimescaleDB setup
      https://www.zabbix.com/documentation/current/en/manual/appendix/install/timescaledb#upgrading-timescaledb-schema

      3 Database upgrade to primary keys
      https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_primary_keys#postgresql-timescaledb


      now db size descreasing.

        Comment

        • Tristis Oris
          Member
          • Sep 2015
          • 66
          #3
          nope. the size still grows +10Gb per day.

            Comment

            • Tristis Oris
              Member
              • Sep 2015
              • 66
              #4
              ts compression looks working exept audit. but i have audit disabled.

              SELECT * FROM timescaledb_information.job_stats;
              Code:
              hypertable_schema | hypertable_name | job_id | last_run_started_at | last_successful_finish | last_run_status | job_status | last_run_duration | next_start | total_runs | total_successes | total_failures
-------------------+-----------------+--------+-------------------------------+-------------------------------+-----------------+------------+-------------------+-------------------------------+------------+-----------------+----------------
public | auditlog | 1007 | 2024-11-05 03:37:02.075717+03 | -infinity | Failed | Scheduled | 00:00:00.015467 | 2024-11-06 01:38:54.591184+03 | 26 | 0 | 26
public | history | 1019 | 2024-11-04 13:46:24.265967+03 | 2024-11-04 13:46:24.290744+03 | Success | Scheduled | 00:00:00.024777 | 2024-11-05 13:46:24.290744+03 | 4 | 4 | 0
public | history_bin | 1017 | 2024-11-04 13:26:00.46277+03 | 2024-11-04 13:26:00.483029+03 | Success | Scheduled | 00:00:00.020259 | 2024-11-05 13:26:00.483029+03 | 4 | 4 | 0
public | history_log | 1021 | 2024-11-04 13:46:29.312604+03 | 2024-11-04 13:46:29.329346+03 | Success | Scheduled | 00:00:00.016742 | 2024-11-05 13:46:29.329346+03 | 4 | 4 | 0
public | history_str | 1022 | 2024-11-04 13:46:35.968645+03 | 2024-11-04 13:46:36.086627+03 | Success | Scheduled | 00:00:00.117982 | 2024-11-05 13:46:36.086627+03 | 4 | 4 | 0
public | history_text | 1023 | 2024-11-04 13:46:39.819908+03 | 2024-11-04 13:46:39.838644+03 | Success | Scheduled | 00:00:00.018736 | 2024-11-05 13:46:39.838644+03 | 4 | 4 | 0
public | history_uint | 1020 | 2024-11-04 13:46:29.309822+03 | 2024-11-04 13:46:29.326706+03 | Success | Scheduled | 00:00:00.016884 | 2024-11-05 13:46:29.326706+03 | 4 | 4 | 0
public | trends | 1024 | 2024-11-04 13:46:39.822446+03 | 2024-11-04 13:46:39.837477+03 | Success | Scheduled | 00:00:00.015031 | 2024-11-05 13:46:39.837477+03 | 4 | 4 | 0
public | trends_uint | 1006 | 2024-11-04 11:44:29.971816+03 | 2024-11-04 11:44:30.000776+03 | Success | Scheduled | 00:00:00.02896 | 2024-11-05 11:44:30.000776+03 | 13 | 13 | 0
| | 1 | 2024-11-04 22:00:07.179925+03 | 2024-11-04 22:00:07.994913+03 | Success | Scheduled | 00:00:00.814988 | 2024-11-05 22:00:07.994913+03 | 24 | 24 | 0
| | 3 | 2024-11-01 03:00:01.000562+03 | 2024-11-01 03:00:01.031397+03 | Success | Scheduled | 00:00:00.030835 | 2024-12-01 03:00:00+03 | 2 | 2 | 0
(11 rows)

                Comment

                • Tristis Oris
                  Member
                  • Sep 2015
                  • 66
                  #5
                  SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression';

                  Code:
                  job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | fixed_schedule | config | next_start | initial_start | hypertable_schema | hypertable_name | check_schema | check_name
--------+---------------------------+-------------------+-------------+-------------+--------------+------------------------+--------------------+----------------+-----------+----------------+-------------------------------------------------+-------------------------------+---------------+-------------------+-----------------+------------------------+--------------------------
1006 | Compression Policy [1006] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | zabbix | t | f | {"hypertable_id": 7, "compress_after": 612000} | 2024-11-06 11:44:30.026533+03 | | public | trends_uint | _timescaledb_functions | policy_compression_check
1017 | Compression Policy [1017] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres_admin | t | f | {"hypertable_id": 38, "compress_after": 612000} | 2024-11-06 13:26:00.508782+03 | | public | history_bin | _timescaledb_functions | policy_compression_check
1019 | Compression Policy [1019] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 44, "compress_after": 612000} | 2024-11-06 13:46:24.31356+03 | | public | history | _timescaledb_functions | policy_compression_check
1021 | Compression Policy [1021] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 48, "compress_after": 612000} | 2024-11-06 13:46:29.348212+03 | | public | history_log | _timescaledb_functions | policy_compression_check
1020 | Compression Policy [1020] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 46, "compress_after": 612000} | 2024-11-06 13:46:29.349671+03 | | public | history_uint | _timescaledb_functions | policy_compression_check
1022 | Compression Policy [1022] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 50, "compress_after": 612000} | 2024-11-06 13:46:36.222239+03 | | public | history_str | _timescaledb_functions | policy_compression_check
1024 | Compression Policy [1024] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 54, "compress_after": 612000} | 2024-11-06 13:46:39.856858+03 | | public | trends | _timescaledb_functions | policy_compression_check
1023 | Compression Policy [1023] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | postgres | t | f | {"hypertable_id": 52, "compress_after": 612000} | 2024-11-06 13:46:39.858218+03 | | public | history_text | _timescaledb_functions | policy_compression_check
1007 | Compression Policy [1007] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_functions | policy_compression | zabbix | t | f | {"hypertable_id": 15, "compress_after": 612000} | 2024-11-06 20:05:09.612389+03 | | public | auditlog | _timescaledb_functions | policy_compression_check
(9 rows)

                    Comment

                    Previous template Next
                    Working...