3 Replies Latest reply on Jan 15, 2019 2:26 AM by Mark Francome

    Postgres Tuning

    Haw Kor

      Hi All,

       

      I've been using Control-m for almost 4 years now.  We've always used the Postgres that's come with the BMC install.  Has anyone taken to tuning this instance of Postgres in terms of resource consumption?

       

      Thanks,

       

      Haw

        • 1. Re: Postgres Tuning
          Mark Francome

          The only tuning I have ever done is to answer the question during the installation (i.e. are you a small/medium/large site?).

           

          The supplied Postgres really tends to look after itself, I would only change some settings if I had specific issues (and then refer the changes to BMC first).

          • 2. Re: Postgres Tuning
            Haw Kor

            Hi Mark,

             

            Thanks for your feedback.  I'm actually looking more at memory utilization of the postgres server.  With Control-M V9 the included version is postgres 9.2. I think by default a large installation will set the following options in postgres.conf:

             

            # - Memory -

            shared_buffers = 2000MB #@shared_buffers@ # (S,M,L)=(60MB,120MB,200MB)

            temp_buffers = 250MB #@temp_buffers@ # (S,M,L)=(20MB,40MB,60MB)

             

            # - Settings -

             

             

            fsync = off

             

            other options such as

             

            work_mem

            effective_cachesize

            effective_io_concurrency

             

            are set to default values which probably don't take advantage of a larger configuration server.

             

            With a fresh install of 9.0.18, it appears that the Postgres version is now upgraded to 9.5, which will try to take advantage of huge pages in Linux, however I wasn't able to find much information regarding huge pages in BMC doc so I was wondering if anyone in the community actually started tweaking these settings.

             

            Below are some of the articles which turned up for Postgres performance tuning which I've used as a starting point:

             

            https://blog.dbi-services.com/configuring-huge-pages-for-your-postgresql-instance-redhatcentos-version/

            Architecture and Tuning of Memory in PostgreSQL Databases | Severalnines

            Tune Linux Kernel Parameters For PostgreSQL Optimization

             

            Thanks,

             

            Haw

            1 of 1 people found this helpful
            • 3. Re: Postgres Tuning
              Mark Francome

              I have not made it to 9.0.18 yet. In fact my current 9.0.00 installation is running on an external Oracle DB (not good as the DBAs like to take it down to patch the DB every quarter. Nobody likes to lose their Control-M system, not even for 10 minutes).

               

              The last time I used Postgres for the DB was my test 9.0.00 and the only tweak was to have archive mode enabled (for backup purposes).

               

              I know that the new Postgres blackbox DB is supposed to be able (either now or very soon) to support HA. Somebody (either BMC or one of the user groups) usually produces a "best practices for DB settings" document, which would be nice to see ...