This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Housekeeping the Hyper9 Database

Hi Guys,

I would like to know how I can housekeeping or cleaning the Hyper9 database. My database is already growing up to 42Gb already. Is there any other way to compress or maintenance we can do it like NPM?

Best Regards,

Azfar

  • Hello Azfar,

    VMan's database should be taking care of itself and 42Gb is not that much. VMan stores data from sample jobs and rollup these data. By default, it keeps 30 days of hourly samples and 14 days of raw (all data samples) in the database. For data older than this, it uses aggregation (so important values are preserved) so space is freed. How big is your environment? How long your application is running?

  • Hi Jan.kucera,

    Thanks for your reply. we monitor less than 100 VM and we already running for 1 year. My disk space only 50Gb. Is there any other way we can do to housekeep the database or clean up some file to free some space on my hard disk?

  • Hello,

    postgresql database has cleaning mechanism called VACUUM and you can check whether some of the tables may have many "dead/old rows" which are not used in postgres anymore. The following query will show you table in your DB ordered by table size and you can check whether some of the big tables doesn't have many n_dead_tup comparing to n_live_tup. If so, this table could be vaccumed by VACUUM FULL <name_of_the_table> but BEWARE, this process takes really long time, so it could significantly affect VMan's performance during this cleaning process.

    SELECT relname, pg_relation_size(relid) as size_in_bytes, pg_size_pretty(pg_relation_size(relid)) AS size, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum

    FROM pg_stat_user_tables

    WHERE pg_relation_size(relid) > (1024 *1024 * 10)

    ORDER BY size_in_bytes DESC

    If any of the tables are have many dead_n_tup it means that DB is optimized

  • Hi Jan.kucera,

    How do I open the database at VMan? For NPM, we have Microsoft SQL Management Studio, so we can query the database. Sorry for stupid question.

    Best Regards,

    Azfar

  • Hi,

    you can launch the SQL query several ways. The fastest way is to run the query in VMAN console, see examples:

    psql -d hyper9db -U h9pgsu -c 'SELECT * FROM auth_user'

      - the command shows results of the command in screen

    psql -d hyper9db -U h9pgsu -c 'SELECT * FROM auth_user' > users.txt

      - the command stores results into users.txt file

    You can also login to the psql database via 'psql -d hyper9db -U h9pgsu' command and then launch your SQL queries. Note that the SQL queries must end with semicolon ';' .

    Another way is to install any administration tool, i.e. pgAdmin. In this case it is necessary to setup VMAN in order to enable remote connections to the hyper9db dababase. If you are interested I would send you steps how to configure VMAN.

  • THanks..can you send me the step how to configure VMAN?

  • 1. setup IP address(es) that can attach the database:
    in /var/lib/pgsql/data/pg_hba.conf add this line:
    host    all     all     xx.yy.zz.0/24  trust

    where xx.yy.zz.0 is set of IP addresses which will be able to connect the DB, 24 is a subnet mask (adjust it to your needs)

    2. enable listening to the addresses:
    in /var/lib/pgsql/data/postgresql.conf add this line:
    listen_addresses = '*'

    3. restart postgresql service
    /etc/init.d/postgresql restart

    After that the hyper9db database will be accessible from outside. Recommended SQL clients are "HeidiSQL", "SQuirreL SQL", "pgAdmin", etc.

    DB credentials

    username: h9pgsu

    Password: <blank>