The Database Performance Monitor application automatically reviews the settings of your OS and database instances and identifies settings which are inconsistent with established best practices. For clients with query samples enabled, DPM also proactively alerts you to possible query-writing and index optimizations.

The Best Practices page displays the results of these tests, allowing you to quickly find areas where you can improve the performance and security of your database. These checks are performed every 15 minutes.

To visit the Best Practices page, click on the “Health” icon in the left-hand navigation. Then at the top of the window, click “Best Practices.”

The Best Practices page lists all of the checks which one or more of your database instances or queries are currently failing (you can also see checks which have passed by clicking “Show passed best practices”). To view specific Best Practices and which checks have failed, click one of the Categories, and then click one of the Types to expand. For example, in this screenshot, there are three Categories, and the first Category is expanded to show three Types:

Click on one of the Types to see a list of instances which have failed that check:

You can also mute checks which you no longer want to be displayed. You can mute a check for every host (by selecting Mute all), or just for specific hosts. To show those checks again, select the box marked “Show muted best practices.” If you have a best practice that is both passed and muted, you will need to select to see both passed and muted best practices in order to view it again.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

Below you will find a list of checks for each database technology:

• MongoDB

  • Database & OS Configuration
  • Replication Configuration
  • Security Configuration

• MySQL

  • Database Configuration
  • Query Analysis

• PostgreSQL

  • Database Configuration
  • Query Analysis

MongoDB Checks

These are based on the official MongoDB documentation, which makes a number of configuration recommendations. Each of the “Reference Source” links in the table below are links to the MongoDB documentation for further reading.

MongoDB & OS Configuration

Check	Description	Official Documentation
Transparent huge pages	Transparent huge pages should be disabled on the server, as MongoDB generally performs better with normally sized virtual memory pages.	Reference Source
Journaling	Journaling should be enabled on the server to help avoid data loss in the case of a crash.	Reference Source
Swap	MongoDB recommends having some swap space configured to help avoid getting terminated by the OOM Killer when the server is under memory pressure.	Reference Source
TCP keepalive	MongoDB recommends having a short keepalive time set for network connections, as this results in generally better performance for replica sets and sharded clusters.	Reference Source
Self resolution	When running in a replica set, it is important to ensure that the MongoDB server can resolve its own hostname.	Reference Source
Filehandle limit	Ensure that MongoDB has enough file handles to handle your workload. For production systems a limit of 98,000 is usually reasonable.	Reference Source
Thread limit	Ensure the server has a high enough thread limit for your workload. For production systems a limit of 64,000 is usually reasonable.	Reference Source
PID limit	Ensure that the server has a high enough PID limit for your workload. For production systems a limit of 64,000 is usually reasonable.	Reference Source
Readahead settings	Ensure the readahead settings for your storage device are appropriate. For the WiredTiger storage engine, set readahead between 8 and 32 regardless of storage media type (spinning disk, SSD, etc.), unless testing shows a measurable, repeatable, and reliable benefit in a higher readahead value. For MMAPv1, consider a value of 32 or lower.	Reference Source
Disk scheduler	Use the noop or deadline disk scheduler for the database’s storage device for maximum performance.	Reference Source
dbPath mount point	Use noatime for the dbPath mount point to improve performance.	Reference Source
Filesystem	The XFS filesystem is recommended with MongoDB as it provides generally better performance. For WiredTiger it is strongly recommended due to known performance problems with EXT4.	Reference Source
Index sizes	Ensure that each of your indexes fit entirely in memory so that the system can avoid reading the index from disk.	Reference Source
Index total size	Ensure that all your indexes fit entirely in memory so that the system can avoid reading the index from disk.	Reference Source
Collection sizes	MongoDB recommends your working set should stay in memory to achieve good performance.	Reference Source
Total size	MongoDB recommends the total size of your collections should stay in memory to achieve good performance.	Reference Source

MongoDB Replication Configuration

Check	Description	Official Documentation
Hostnames	Use hostnames when configuring replica set members rather than IP-addresses, as it makes them easier to maintain in production.	Reference Source
Cluster voting members	Ensure that the replica set has an odd number of voting members; otherwise during an election it may not be possible to reach a quorum successfully.	Reference Source
Cluster vote config	It is recommended that replica set members have either 0 or 1 votes allocated to them.	Reference Source

MongoDB Security Configuration

Check	Description	Official Documentation
Authorization	Ensure that MongoDB is configured with role-based access control enabled so that specific user actions can be limited as necessary.	Reference Source
Encryption	MongoDB enterprise edition supports at-rest encryption for the WiredTiger storage engine. Enabling this option is recommended to limit the possibility of data theft.	Reference Source
Network exposure	Ensure that MongoDB’s HTTP status interface, REST API and JSON API are disabled to limit untrusted access to the database.	Reference Source
Javascript	MongoDB supports the execution of JavaScript code for certain server operations; this should be disabled if possible to avoid unsafe use.	Reference Source

MySQL Checks

Check	Description	Official Documentation
Anonymous users	It is recommended to not run MySQL without usernames.	Reference Source
Server running as root	MySQL should not be running with a user or group with root privileges.	Reference Source
File system privileges	The MySQL server configuration file and data directory should not be world-writable. The slow query log file should not be world readable.	Reference Source
Binary log durability	Some values of the sync_binlog, binlog_checksum, innodb_locks_unsafe_for_binlog, and innodb_support_xa variables can reduce the durability of transactions in the binary log and result in data loss or corruption.	Binlog Reference InnoDB Reference
File descriptor limits	MySQL’s file descriptor limit should be high enough to handle the configured number of open files, max connections, and open tables. Five times max_conections is a good starting point.	Reference Source
Network exposure	MySQL should not be listening on a public IP.	Reference Source
Performance Schema	Disabling the Performance Schema will affect DPM’s ability to monitor MySQL.
Query cache	The query cache can cause occasional stalls which affect query performance. It’s recommended to disable this feature (except for Aurora).	Reference Source
Sort buffer size	The sort buffer size is dynamic and per-connection, so it should be changed per connection instead of by default.	Reference Source
Swappiness	Swap should be enabled with a low swappiness value (0 or 1) in order to avoid out-of-memory termination when low on memory.	Reference Source
Durable table definitions	sync_frm should be enabled to make sure table definitions are crash-safe.	Reference Source
MySQL upgrade	mysql_upgrade should be run after a major version update of MySQL.	Reference Source
Relative buffer pool size	The configured buffer pool size should not be greater than 90% of total memory.	Reference Source
Buffer pool size	The buffer pool size should be set according to the amount of memory available instead of the default of 128 MB. 60%-80% is a reasonable range.	Reference Source
Doublewrite buffer	The doublewrite buffer should be enabled to prevent torn page writes which can lead to data loss or corruption.	Reference Source
Flush method	To maximize durability, innodb_flush_method should not be set to “littlesync” or “nosync.”	Reference Source
Log flush on commit	innodb_flush_log_at_trx_commit should be set to “1” to maximize ACID compliance.	Reference Source
I/O capacity	The default value of innodb_io_capacity (200) may not be optimal for modern systems with SSDs. Consider increasing it to match the I/O capabilities of the disk.	Reference Source
CloudWatch Integration	DPM integrates with Amazon CloudWatch, which allows you to see system metrics such as CPU Utilization for your RDS and Aurora instances. This is vitally important for diagnosing database and query issues.	Setup Instructions

MySQL Query Checks

DPM automatically analyzes captured MySQL queries to see if they fail one or more rules designed to ensure adherence to query-writing best practices. Queries which fail one or more of these checks will be displayed on the Best Practices page. For a complete list of rules which DPM uses when examining your database queries, please see this list in the Query Analysis Rules documentation.

PostgreSQL Checks

Check	Description	Official Documentation
Statement logging	log_statement should be set to “none” because certain values will expose passwords when they are changed with ALTER ROLE, and can expose query text in some logging modes.	Reference Source
Max connections	Connections are relatively expensive in PostgreSQL, so connection pooling is recommended to keep the maximum number of connections low.	Reference Source
Autovacuum	Autovacuum should be enabled to perform periodic maintenence automatically.	Reference Source
Synchronous commit	Synchronous commit should be enabled to ensure transaction durability.	Reference Source
Fsync	fsync should be enabled to ensure data integrity and avoid data corruption, especially after crashes or hard restarts.	Reference Source
Memory consumption	PostgreSQL should be configured to use 25% to 40% of total memory for shared memory buffers.	Reference Source
Effective I/O concurrency	I/O concurrency parameters should be set according to the capabilities of the database disk volume.	Reference Source
Old snapshot threshold	old_snapshot_threshold should be enabled to avoid a gradual slowdown on production databases due to snapshot data bloat.	Reference Source
I/O page cost	I/O page cost parameters should be set according to the capabilities of the database disk volume.	Reference Source
Work mem	work_mem should generally be changed only for specific sessions that need larger buffers.	Reference Source
Effective cache size	effective_cache_size should be configured with a value between 50% and 75% of the total memory. If it is set too low indexes may not be used for executing queries.	Reference Source
WAL Buffers	wal_buffers should be set to 16MB (size of a single WAL segment) or even higher specially if you have a lot of concurrent write activity.	Reference Source
Maintenance work memory	maintenance_work_mem should be set to at least 10% of total memory available.	Reference Source
Idle transaction timeout	idle_in_transaction_session_timeout is 0 or disabled. Setting a timeout allows any locks held by a terminated session with an idle open transaction to be released, so that connection slot will be reused.	Reference Source
Backend privileges	It is recommended to run PostgreSQL as a non-root user and group.
Backend process limit	max_connections should be less than the soft process limit set in the OS.	Reference Source
Time since last vacuum	Vacuums and/or autovacuums should be performed regularly to purge old data and avoid transaction ID wraparound, among other reasons.	Reference Source
Network exposure	PostgreSQL should not be configured to listen on a publicly routable IP address to limit network exposure.	Reference Source
File system privileges	PostgreSQL configuration files and data directories should have proper permissions to prevent unauthorized access.
Outdated extensions	Extensions should not be older than the version of PostgreSQL. You should update extensions when upgrading PostgreSQL.	Reference Source
Permanent user passwords	Permanent passwords (passwords without an expiration) should be avoided.	Reference Source
Unsecure clear-text passwords	Users should not be allowed to use clear-text passwords over unencrypted connections.	Reference Source
CloudWatch Integration	DPM integrates with Amazon CloudWatch, which allows you to see system metrics such as CPU Utilization for your RDS and Aurora instances. This is vitally important for diagnosing database and query issues.	Setup Instructions

PostgreSQL Query Checks

DPM automatically analyzes captured PostgreSQL queries to see if they fail one or more rules designed to ensure adherence to query-writing best practices. Queries which fail one or more of these checks will be displayed on the Best Practices page. For a complete list of rules which DPM uses when examining your database queries, please see this list in the Query Analysis Rules documentation.