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,
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.
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>
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.