Cool Solutions

ZLM DataStore PostgreSQL: Check Cache Hit Rate


January 23, 2008 11:57 am




  1. Activate the postgresql statistics
  2. You have to enable the following four lines in the /var/lib/pgsql/data/postgresql.conf to allow the postgresql server to collect statistical data.

    Note: In some environments these values are not “true / false” but rather “on / off”.

    stats_start_collector = true
    stats_command_string = true
    stats_block_level = true
    stats_row_level = true

    Next reload the postgresql server (rcpostgresql reload). That should have no impact on the ZLM functionality but the statistics might cost you some server performance (a few percent). So activate it just for the moment and disable it when you do not need it anymore.

  3. Open the postgresql client and reset the statistics.
  4. In a shell session execute the following command:

    # psql -U zenadmin -d zenworks

    If you are prompted for a password take the string from the /etc/opt/novell/zenworks/serversecret and use that one.

    Next you should reset the current database statistics using the following command:

    zenworks=# select pg_stat_reset();

    Now let’s wait about an hour to see how your database works and you get statistical values.

    You can close the postgresql during that time using the following command:

    zenworks=# \q
  5. Query the current statistics.
  6. Open a new postgresql client session and issue the following command:

    zenworks=# select * from pg_stat_database;
     datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read |   blks_hit 
     17142 | zenworks  |          50 |     4783533 |       2330702 |      9070 |    6457235
         1 | template1 |           0 |           2 |             0 |         6 |          7
     17141 | template0 |           0 |           0 |             0 |         0 |          0
    (3 rows)

    Now you can see the number of sql queries for the database ZENworks that were serviced against the cache (blks_hit) and the one that needed some disk reads (blks_read). Now calculate your percentage by using the following formula: blks_hit / ( blks_hit + blks_read ) * 100.

    If the number is above 90 % I would say everything is fine. If it is below it might be a good idea to check the ZLM documentation for some cache values and how they need to be applied. That might improve your ZLM performance a lot.

    The sample values above is a very good configuration, the percentage is nearly 100%. So nearly no request needs disk access and can be serviced by the cache itself that is much faster then the disk.

    Now you can close the client with the following:

    zenworks=# \q
  7. Deactivate the postgresql statistics
  8. If you do not need them anymore deactivate the four lines in the postgresql.conf and reload the postgresql server. If you decide to do some postgresql reconfigurations use the same steps to check the cache hit rate again.

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this post.

Categories: Uncategorized


Disclaimer: This content is not supported by Micro Focus. It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test it thoroughly before using it in a production environment.