How to use zencheckdbstats to examine database performance

Applies To:

• Zenoss Resource Manager 5.x
• Zenoss Resource manager 4.x

Summary:

The zencheckdbstats python script (available as part of the zenoss.toolbox project on Github - https://github.com/zenoss/zenoss.toolbox) can to used to gather and display database configuration information and performance metrics important to stable Zenoss operation.

The zencheckdbstats tool gathers current performance metrics for the database(s) configured in the $ZENHOME/etc/global.conf file. If the zodb and zenoss-zep databases exist on different hosts, the tool gathers results for both database hosts. If zencheckdbstats is supplied with the --debug (-v10) command line parameter, it also gathers detailed DB configurations into the log file $ZENHOME/log/toolbox/zencheckdbstats.log.

Run the script against a live system. Note that it can be run while other toolbox tools are running.

NOTE: This tool is intended to be run against a database that is in "steady state" operation. If the tool is run soon after a DB restart, values will be under-reported compared with values from normal operation. We recommend that any final performance is analyzed multiple days after the last DB restart.

Fields Returned by zencheckdbstats :

Zencheckdbstats returns the following four fields to the command line when run:

[2016-02-12 12:25:31]  History List Length: 22194
[2016-02-12 12:25:31]  Bufferpool Used (%): 95.4
[2016-02-12 12:25:31]  ACTIVE TRANSACTIONS: 72
[2016-02-12 12:25:31]  ACTIVE TRANS > 100s: 18

History List Length -

This value references the SQL's Innodb history list - a high value for this field will often lead to many long-running transactions and can cause system outages if enough transactions are outstanding (correlating with "ACTIVE TRANS > 100s"). The default value for wait_timeout in zends.cnf is 86400 - if the "History List Length" value is high (> 100K) we recommend decreasing the wait_timeout value to 7200 to increase performance.

Bufferpool Used (%) -

We recommend that this important value value be kept under 90% on any production system. If the number is above 90%, confirm that there are available RAM resources and then increase the zends.cnf value "innodb_buffer_pool_size" as necessary.

For example: if the Bufferpool Used (%): value is 120% and the current setting is 4g, increase the "innodb_buffer_pool_size" setting to 6g for an additional safety margin.

Note: We recommend a value of 90% because this memory is also used for temporary query results storage, and can spike during load. If this value exceeds 100%, the system pages to disk.

Usage:

usage:

zencheckdbstats.py [-h] [-v] [-v10] [--tmpdir TMPDIR] [-s] [-n TIMES] [-g GAP] [-l3]

zencheckdbstats -

gathers performance information about your DB -
https://support.zenoss.com/hc/en-us/articles/208050803

optional arguments:

Example Script Output:

[2016-02-12 12:25:31] Initializing zencheckdbstats v2.0.0 (detailed log at /opt/zenoss/log/toolbox/zencheckdbstats.log)
[2016-02-12 12:25:31] Gathering MySQL/ZenDS metrics... (1/1)

[2016-02-12 12:25:31] Results for 'zodb' Database:
[2016-02-12 12:25:31]  History List Length: 191
[2016-02-12 12:25:31]  Bufferpool Used (%): 1.44
[2016-02-12 12:25:31]  ACTIVE TRANSACTIONS: 12
[2016-02-12 12:25:31]  ACTIVE TRANS > 100s: 4

[2016-02-12 12:25:31] Results for 'zenoss_zep' Database:
[2016-02-12 12:25:31]  History List Length: 1542
[2016-02-12 12:25:31]  Bufferpool Used (%): 0.37
[2016-02-12 12:25:31]  ACTIVE TRANSACTIONS: 0
[2016-02-12 12:25:31]  ACTIVE TRANS > 100s: 0

[2016-02-12 12:25:31] Execution finished in 0:00:01

** Additional information and next steps at https://support.zenoss.com/hc/en-us/articles/208050803 **