7 Replies Latest reply on Oct 24, 2012 1:53 AM by Milan_Volejnik

    Housekeeping the Hyper9 Database

    ampeh12

      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

        • Re: Housekeeping the Hyper9 Database
          jan.kucera

          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?

            • Re: Housekeeping the Hyper9 Database
              ampeh12

              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?

                • Re: Housekeeping the Hyper9 Database
                  jan.kucera

                  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

                    • Re: Housekeeping the Hyper9 Database
                      ampeh12

                      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

                        • Re: Housekeeping the Hyper9 Database
                          Milan_Volejnik

                          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.

                            • Re: Housekeeping the Hyper9 Database
                              ampeh12

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

                                • Re: Housekeeping the Hyper9 Database
                                  Milan_Volejnik

                                  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>