Database advisor checks

SolarWinds Observability SaaS automatically reviews the settings of your host and monitored database instances and identifies settings that are inconsistent with established best practices. For clients with query samples enabled, SolarWinds Observability SaaS also proactively alerts you to possible query-writing and index optimizations. The Advisors tab on the Databases Overview displays the results of these tests, allowing you to quickly find areas where you can improve the performance and security of your database.

To access the Advisors tab, click Database > Advisors. Use the Advisors tab to view details about potential problems and optimization opportunities within your databases monitored with SolarWinds Observability SaaS.

View the advisor count in the Summary

The Advisors Summary details the total recommendation count, displaying the advisor checks that failed and are actionable to improve database best practices. The Advisors tab displays whether the advisor categories are informational (blue), critical (red), or cautionary (yellow). Critical recommendations should take priority, and require you to optimize an aspect of your database for improved database performance.

Advice categories are displayed beneath the Summary and provide you with recommendations grouped into specific database categories. Use the Advice categories to filter for specific database aspects that you may want to optimize. For example, you could expand the MySQL Configuration category to see any critical, cautionary, or informational recommendations. Each recommendation category has its own count, with listed recommendations underneath the category. Expand any recommendation to view affected database(s) by ID, their status, and an explanation of the recommendation. Click any database ID to filter the view for that database, and see all the advisor checks for that database.

Filter advisors

Apply filters to all available advisors with the severity filter drop down menu. Quickly filter all advisors to display only information, cautionary, or critical categories. Click the X in the severity filter dropdown to remove the applied filter.

Click the Hide passed checks checkbox to hide all occurrences that passed, and are within suggested database best practices.

Mute advisors that don't apply to your environment

You can mute any advisor, or advisor category that may not apply to your environment. Navigate to the advisor category, and click the mute assertion button to mute the single advisor occurrence. Click the Mute All button for an advisor category to mute the entire advisor category.

View the available advisor checks

See the database specific sections for information about available advisor checks.

• MongoDB

• MySQL

• PostgreSQL

MongoDB Advisor checks

MongoDB checks are based on the official MongoDB documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the MongoDB documentation.

MongoDB and OS configuration

Check	Metric name	Description	More information
Collection sizes	mongo.collection.size	MongoDB recommends your working set should stay in memory to achieve good performance.	https://docs.mongodb.com/manual/faq/diagnostics/#must-my-working-set-size-fit-ram
dbPath mount point	mongo.dbpath.fs.noatime	Use noatime for the dbPath mount point to improve performance.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Disk scheduler	mongo.dbpath.fs.scheduler	Use the noop or deadline disk scheduler for the database’s storage device for maximum performance.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Filehandle limit	mongo.limits.file_max	Ensure that MongoDB has enough file handles to handle your workload. For production systems a limit of 98,000 is usually reasonable.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Filesystem	mongo.dbpath.fs.type	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.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Index sizes	mongo.index.size	Ensure that each of your indexes fit entirely in memory so that the system can avoid reading the index from disk.	https://docs.mongodb.com/manual/tutorial/ensure-indexes-fit-ram/
Index total size	mongo.index.size	Ensure that all your indexes fit entirely in memory so that the system can avoid reading the index from disk.	https://docs.mongodb.com/manual/tutorial/ensure-indexes-fit-ram/
Journaling	mongo.os.journaling	Journaling should be enabled on the server to help avoid data loss in the case of a crash.	https://docs.mongodb.com/manual/administration/production-notes/#journaling
PID limit	mongo.limits.pid_max	Ensure that the server has a high enough PID limit for your workload. For production systems a limit of 64,000 is usually reasonable.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Readahead settings	mongo.dbpath.fs.readahead	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.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Self resolution	mongo.net.resolve_self	When running in a replica set, it is important to ensure that the MongoDB server can resolve its own hostname.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
Swap	mongo.os.swap	MongoDB recommends having some swap space configured to help avoid getting terminated by the OOM Killer when the server is under memory pressure.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
TCP keepalive	mongo.net.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.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
Thread limit	mongo.limits.thread_max	Ensure the server has a high enough thread limit for your workload. For production systems a limit of 64,000 is usually reasonable.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Total size	mongo.collection.total_size	MongoDB recommends the total size of your collections should stay in memory to achieve good performance.	https://docs.mongodb.com/manual/faq/diagnostics/#must-my-working-set-size-fit-ram
Transparent huge pages	mongo.os.huge_pages	Transparent huge pages should be disabled on the server, as MongoDB generally performs better with normally sized virtual memory pages.	https://docs.mongodb.com/manual/tutorial/transparent-huge-pages/

MongoDB replication configuration

Check	Metric name	Description	More information
Cluster vote config	mongo.repl.allocated_votes	It is recommended that replica set members have either 0 or 1 votes allocated to them.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
Cluster voting members	mongo.repl.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.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
Hostnames	mongo.repl.hostnames	Use hostnames when configuring replica set members rather than IP-addresses, as it makes them easier to maintain in production.	https://docs.mongodb.com/manual/administration/production-checklist-operations/#sharding

MongoDB security configuration

Check	Metric name	Description	More information
Authorization	mongo.security.authorization	Ensure that MongoDB is configured with role-based access control enabled so that specific user actions can be limited as necessary.	https://docs.mongodb.com/manual/administration/security-checklist/
Encryption	mongo.security.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.	https://docs.mongodb.com/manual/administration/security-checklist/
Javascript	mongo.security.javascript	MongoDB supports the execution of JavaScript code for certain server operations; this should be disabled if possible to avoid unsafe use.	https://docs.mongodb.com/manual/administration/security-checklist/
Network exposure	mongo.security.net.exposure	Ensure that MongoDB’s HTTP status interface, REST API and JSON API are disabled to limit untrusted access to the database.	https://docs.mongodb.com/manual/administration/security-checklist/

MySQL Advisor checks

MySQL checks are based on the official MySQL documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the MySQL documentation.

Check	Metric name	Description	More information
Anonymous users	mysql.security.anonymous_users	It is recommended to not run MySQL without usernames.	https://dev.mysql.com/doc/refman/5.7/en/default-privileges.html
Binary log durability	mysql.binlog_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.	https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
Buffer pool size	mysql.innodb.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.	https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
CloudWatch credentials	mysql.offhost_cloudwatch	Remote MySQL instance lacks CloudWatch credentials.
Doublewrite buffer	mysql.innodb.doublewrite	The doublewrite buffer should be enabled to prevent torn page writes which can lead to data loss or corruption.	https://dev.mysql.com/doc/refman/5.7/en/innodb-doublewrite-buffer.html
Durable table definitions	mysql.sync_frm	sync_frm should be enabled to make sure table definitions are crash-safe.
File descriptor limits	mysql.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.	https://dev.mysql.com/doc/refman/5.7/en/not-enough-file-handles.html
File system privileges	mysql.security.privileges	The MySQL server configuration file and data directory should not be world-writable. The slow query log file should not be world readable.	https://dev.mysql.com/doc/refman/5.7/en/security-against-attack.html
Flush method	mysql.innodb.flush_method	To maximize durability, innodb_flush_method should not be set to “littlesync” or “nosync.”	https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
I/O capacity	mysql.innodb.io_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.	https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_io_capacity
Log flush on commit	mysql.innodb.flush_on_tx_commit	innodb_flush_log_at_trx_commit should be set to “1” to maximize ACID compliance.	https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
Log output to file	mysql.log_output	log_output should be set to 'FILE' in order to write queries to a file as destination.
Log Queries Without Indexes	mysql.log_queries_not_using_indexes	log_queries_not_using_indexes should be enabled (ON), otherwise queries that do not use indexes won't be logged to the slow log file.
Log Short Format	mysql.log_short_format	log_short_format should be set to FALSE, otherwise the server will write less information to the slow log file.
Log Slow Admin Statements	mysql.log_slow_admin_statements	log_slow_admin_statements should be enabled (ON), otherwise administrative statements won't be logged to the slow log file.
Log Throttle Queries Without Indexes	mysql.log_throttle_queries_not_using_indexes	log_throttle_queries_not_using_indexes should be set to 0, otherwise the number of queries not using indexes will be limited to this value.
Log UTC Timestamps	mysql.log_timestamps	log_timestamps should be set to 'UTC' (default value).
Long Query Time	mysql.long_query_time_disabled	long_query_time should be set to 0 in order to log all the queries on the log file.
Minimum Examined Row Limit for Slow log	mysql.min_examined_row_limit	min_examined_row_limit should be set to 0, otherwise not all the queries will be written to the slow log file.
MySQL upgrade	mysql.upgrade	mysql_upgrade should be run after a major version update of MySQL.	https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html
Network exposure	mysql.network_exposure	MySQL should not be listening on a public IP.	https://dev.mysql.com/doc/refman/5.7/en/security-guidelines.html
Off-host query digests	mysql.offhost_digests_disabled	Off-host query digests require the "statements_digest" consumer.
Off-host query samples	mysql.offhost_samples_disabled	Off-host query samples require the "events_statements_current" and "events_statements_history_long" consumers".
Performance Schema	mysql.performance_schema_disabled	Disabling the Performance Schema will affect SolarWinds Observability SaaS's ability to monitor MySQL.
Performance Schema for Slow log	mysql.performance_schema_disabled_slow_log	Performance schema should be enabled when slow log is the capture method. This won't affect the ability to monitor but it's a good practice having it enabled as a few metrics are obtained from it.
Performance Schema	mysql.offhost_performance_schema_disabled	Disabling the Performance Schema will affect DPM’s ability to monitor MySQL.
Query cache	mysql.query_cache	The query cache can cause occasional stalls which affect query performance. It’s recommended to disable this feature (except for Aurora).	https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
Relative buffer pool size	mysql.innodb.buffer_pool_memory_size	The configured buffer pool size should not be greater than 90% of total memory.	https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
Server running as root	mysql.security.backend_is_root	MySQL should not be running with a user or group with root privileges.	https://dev.mysql.com/doc/refman/5.7/en/security-against-attack.html
Slow Query Log	mysql.slow_log_disabled	Reading queries from the slow query log requires this log to be enabled. Set the server's slow_query_log parameter to ON.
Sort buffer size	mysql.sort_buffer_size	The sort buffer size is dynamic and per-connection, so it should be changed per connection instead of by default.	https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size
Swappiness	mysql.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.	https://www.kernel.org/doc/Documentation/sysctl/vm.txt

MySQL query checks

SolarWinds Observability SaaS 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 Advisors tab in the Databases Overview.

Check	Category	Description
Column or table aliases	MySQL Query Rules	The table or column’s alias is the same as its real name, which makes the query harder to read.
Column wildcard aliases	MySQL Query Rules	Aliasing a column wildcard, such as SELECT tbl.* col1, col2 probably indicates a bug in your SQL. You probably meant for the query to retrieve col1, but instead it renames the last column in the *-wildcarded list.
Constant expressions in GROUP BY or ORDER BY	MySQL Query Rules	Constant expressions in GROUP BY or ORDER BY clauses are at best useless operations that do not change query results.
GROUP BY clauses with cardinals	MySQL Query Rules	GROUP BY clauses that use cardinals instead of columns or expressions can cause problems if the query or table is changed.
GROUP BY or ORDER BY clauses requiring temporary tables	MySQL Query Rules	GROUP BY or ORDER BY clauses with columns from different tables will force the use of a temporary table and filesort, which can be a huge performance problem and can consume large amounts of memory and temporary space on disk.
GROUP BY or ORDER BY clauses sorting in different directions	MySQL Query Rules	GROUP BY or ORDER BY clauses that sort the results in different directions prevent indexes from being used. All expressions in the ORDER BY clause must be ordered either ASC or DESC so that MySQL can use an index.
LIMIT without ORDER BY	MySQL Query Rules	LIMIT without ORDER BY causes non-deterministic results, depending on the query execution plan.
ORDER BY RAND()	MySQL Query Rules	ORDER BY RAND() is a very inefficient way of retrieving a random row from the results because it sorts the entire result and then throws most of it away.
Pagination with LIMIT and OFFSET	MySQL Query Rules	Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger. Pagination techniques such as bookmarked scans are much more efficient.
Selecting all columns with * wildcard	MySQL Query Rules	Selecting all columns with the * wildcard will cause the query’s meaning and behavior to change if the table’s schema changes, and might cause the query to retrieve too much data.
Selecting non-grouped columns from a GROUP BY	MySQL Query Rules	Selecting non-grouped columns from a GROUP BY query can cause non-deterministic results.
SELECTs without WHERE/GROUP/LIMIT clauses	MySQL Query Rules	A SELECT statement without WHERE/GROUP/LIMIT clauses could examine many more rows than intended.
SQL injection	MySQL Query Rules	Evidence of SQL injection.

PostgreSQL advisor checks

PostgreSQL checks are based on the official PostgreSQL documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the PostgreSQL documentation.

Check	Metric name	Description	More information
Autovacuum	pgsql.config.autovacuum	Autovacuum should be enabled to perform periodic maintenance automatically.	https://www.postgresql.org/docs/current/static/routine-vacuuming.html
Backend privileges	pgsql.backend.privileges	It is recommended to run PostgreSQL as a non-root user and group.
Backend process limit	pgsql.backend.process_limit	max_connections should be less than the soft process limit set in the OS.	https://www.postgresql.org/docs/current/static/runtime-config-connection.html
CloudWatch credentials	pgsql.offhost_cloudwatch	Remote PostgreSQL instance lacks CloudWatch credentials.
Effective cache size	pgsql.config.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.	https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#effective_cache_size
Effective I/O concurrency	pgsql.config.effective_io_concurrency	I/O concurrency parameters should be set according to the capabilities of the database disk volume.	https://www.postgresql.org/docs/current/static/runtime-config-resource.html#runtime-config-resource-async-behavior
File system privileges	pgsql.security.fs_privileges	PostgreSQL configuration files and data directories should have proper permissions to prevent unauthorized access.
Fsync	pgsql.config.fsync	fsync should be enabled to ensure data integrity and avoid data corruption, especially after crashes or hard restarts.	https://www.postgresql.org/docs/current/static/runtime-config-wal.html
I/O page cost	pgsql.config.page_cost	I/O page cost parameters should be set according to the capabilities of the database disk volume.	https://www.postgresql.org/docs/current/static/runtime-config-query.html#runtime-config-query-constants
Idle transaction timeout	pgsql.config.idle_in_transaction_session_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.	https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
Log Destination	pgsql.slow_var_log_destination	Server's "log_destination" parameter should be set to "stderr".
Log Min Duration Statement	pgsql.slow_var_log_min_duration_statement	Server's "log_min_duration_statement" parameter should be set to "0" to log all statements.
Maintenance work memory	pgsql.config.maintenance_work_mem	maintenance_work_mem should be set to at least 10% of total memory available.	https://pydanny-event-notes.readthedocs.io/en/latest/DjangoConEurope2012/10-steps-to-better-postgresql-performance.html#maintenance-work-mem
Max connections	pgsql.config.max_connections	Connections are relatively expensive in PostgreSQL, so connection pooling is recommended to keep the maximum number of connections low.	https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
Memory consumption	pgsql.config.shared_buffers	PostgreSQL should be configured to use 25% to 40% of total memory for shared memory buffers.	https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers
Network exposure	pgsql.security.network_exposure	PostgreSQL should not be configured to listen on a publicly routable IP address to limit network exposure.	https://www.postgresql.org/docs/current/static/runtime-config-connection.html
Off-host query metrics	pgsql.feature.query_metrics	Off-host query metrics require the agent have access to the pg_stat_statements extension.
Old snapshot threshold	pgsql.config.old_snapshot_threshold	old_snapshot_threshold should be enabled to avoid a gradual slowdown on production databases due to snapshot data bloat.	https://www.postgresql.org/docs/current/static/runtime-config-resource.html
Outdated extensions	pgsql.extensions.versions	Extensions should not be older than the version of PostgreSQL. You should update extensions when upgrading PostgreSQL.	https://www.postgresql.org/docs/current/static/sql-alterextension.html
Permanent user passwords	pgsql.security.user_passwords	Permanent passwords (passwords without an expiration) should be avoided.	https://www.postgresql.org/docs/current/static/sql-createrole.html
Slow Log Duration	pgsql.slow_var_log_duration	Server's "log_duration" parameter should be set to "on" to log the duration of statements.
Slow Query Log	pgsql.slow_log_disabled	Reading queries from the slow query log requires this log to be enabled. Set the server's "log_statement" parameter to "all".
Statement logging	pgsql.security.log_statement	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.	https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement
Synchronous commit	pgsql.config.synchronous_commit	Synchronous commit should be enabled to ensure transaction durability.	https://www.postgresql.org/docs/current/static/runtime-config-wal.html
Time since last vacuum	pgsql.vacuum.time_since_last_vacuum	Vacuums and/or autovacuums should be performed regularly to purge old data and avoid transaction ID wraparound, among other reasons.	https://www.postgresql.org/docs/current/static/routine-vacuuming.html
Unsecure clear-text passwords	pgsql.security.clear_text_passwords	Users should not be allowed to use clear-text passwords over unencrypted connections.	https://www.postgresql.org/docs/current/static/auth-methods.html
WAL Buffers	pgsql.config.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.	https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Work mem	pgsql.config.work_mem	work_mem should generally be changed only for specific sessions that need larger buffers.	https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/

PostgreSQL query checks

SolarWinds Observability SaaS 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 Advisors tab in the Databases Overview

Check	Category	Description
Column or table aliases	PostgreSQL Query Rules	The table or column’s alias is the same as its real name, which makes the query harder to read.
Column wildcard aliases	PostgreSQL Query Rules	Aliasing a column wildcard, such as “SELECT tbl.* col1, col2” probably indicates a bug in your SQL. You probably meant for the query to retrieve col1, but instead it renames the last column in the *-wildcarded list.
GROUP BY clauses with cardinals	PostgreSQL Query Rules	GROUP BY clauses that use cardinals instead of columns or expressions can cause problems if the query or table is changed.
Matching with leading wildcards	PostgreSQL Query Rules	Matching an argument with a leading wildcard, such as %foo, prevents the database from using an index to identify matching rows. Avoid leading wildcards where possible.
ORDER BY Random()	PostgreSQL Query Rules	ORDER BY Random() is a very inefficient way of retrieving a random row from the results because it sorts the entire result and then throws most of it away.
Selecting all columns with * wildcard	PostgreSQL Query Rules	Selecting all columns with the * wildcard will cause the query’s meaning and behavior to change if the table’s schema changes, and might cause the query to retrieve too much data.
SELECTs without WHERE/GROUP/LIMIT clauses	PostgreSQL Query Rules	A SELECT statement without WHERE/GROUP/LIMIT clauses could examine many more rows than intended.
SQL injection	PostgreSQL Query Rules	Evidence of SQL injection.