- Activate the postgresql statistics
- Open the postgresql client and reset the statistics.
- Query the current statistics.
- Deactivate 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.
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:
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
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:
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.