cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Housekeeping the Hyper9 Database

Jump to solution

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

0 Kudos
1 Solution

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>

View solution in original post

0 Kudos
7 Replies
Level 9

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?

0 Kudos

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?

0 Kudos

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

0 Kudos

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.

0 Kudos

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

0 Kudos

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>

View solution in original post

0 Kudos