ZLM DataStore PostgreSQL: Check Cache Hit Rate

0 Likes
over 13 years ago

  1. Activate the postgresql statistics

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.



  • Open the postgresql client and reset the statistics.

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


  • Query the current statistics.

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


  • Deactivate the postgresql statistics

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.


Labels:

How To-Best Practice
Comment List
Anonymous
Related Discussions
Recommended