- 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:
- 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
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:
- 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.
Disclaimer: As with everything else at Cool Solutions, this content is definitely not supported by Novell (so don't even think of calling Support if you try something and it blows up).
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, test, test before you do anything drastic with it.