Skip to content

Advisors details

List of database Advisors

Percona Monitoring and Management (PMM) offers four categories of database Advisors to help you improve database performance: Configuration, Performance, Query and Security Advisors.

Each Advisor includes a set of automated checks, which investigate a specific range of possible issues and areas of improvement: security threats, non-compliance issues, performance degradation, query and index optimization strategies etc.

This page presents the complete list of database Advisors along with the corresponding subscription tier for which they are available.

You can also access this list through the Advisor checks for PMM section in the Percona Portal documentation, as the Advisors are hosted on the Percona Platform. PMM Server automatically downloads them from this source when the Advisors and Telemetry options are enabled in PMM under Configuration > Settings > Advanced Settings. Both options are enabled by default.

Configuration Advisors

Advisor Name Description Subscription Database Technology
Version Configuration Notifies of newly released database versions to streamline database maintenance and ensure the most up-to-date performance. All Users MySQL, MongoDB, PostgreSQL
Generic Configuration Provides basic recommendations for improving your database configuration. All Users MySQL, MongoDB, PostgreSQL
Resources Configuration Watches your database and gives you recommendations for efficient management of resources like binaries architecture, CPU number versus DB Configuration, etc. All Users MySQL, MongoDB
Connection Configuration Provides recommendations on configuring database connection parameters for improving database performance. Customers only MySQL, MongoDB, PostgreSQL
Replication Configuration Provides recommendations for scalable replication in database clusters. Customers only MySQL, MongoDB
InnoDB Configuration Advises on configuring InnoDB optimization for high performance. Customers only MySQL
Vacuum Configuration Provides recommendations on optimizing Vacuum operations. Customers only PostgreSQL

Performance Advisors

Advisor Name Description Subscription Database Technology
Generic Performance Provides basic database configuration recommendations for high-performance query execution. All Users MongoDB, PostgreSQL
Vacuum Performance Helps improve the efficiency and execution speed of database Vacuum commands. Customers only PostgreSQL
Replication Performance Checks efficient replication usage of your database. Customers only MongoDB, PostgreSQL

Security Advisors

Advisor Name Description Subscription Database Technology
CVE Security Informs you of any database versions affected by CVE. All Users MongoDB, PostgreSQL
Configuration Security Checks your database configuration to ensure that security best practices are correctly implemented. All Users MySQL, MongoDB, PostgreSQL
Authentication Security Ensures that all database authentication parameters are configured securely. Customers only MySQL, MongoDB, PostgreSQL
Replication Security Helps safeguard data replication by assessing security risks and providing recommendations for improving protection. Customers only MySQL
Connection Security Helps identify security issues on network connections and provides recommendations for enhancing security. Customers only MySQL, MongoDB

Query Advisors

Advisor Name Description Subscription Database Technology
Index Query Provides query and index optimization strategies for peak database performance. Customers only MySQL, MongoDB, PostgreSQL
Schema Design Query Helps create efficient database schemas by analyzing queries and offering suggestions for optimization. All Users MySQL

List of checks

Every Advisor consists of one or more Advisor checks. We have listed the checks and their details here.

MongoDB

Advisor Check Name Description Summary
Connection Configuration mongodb_connection_sudden_spike Warns about any significant increase in the number of connections exceeding 50% of the recent or typical connection count. MongoDB Sudden Increase in Connection Count
Connection Configuration mongodb_connections Returns the current number of connections as an informational notice when connection counts exceed 5000. MongoDB High Connections
Generic Configuration mongo_cache_size Warns when Mongo wiredtiger cache size is greater than the default 50%. Mongo Storage Cache
Generic Configuration mongodb_active_vs_available_connections Warns if the ratio between active and available connections is higher than 75%. MongoDB Active vs Available Connections
Generic Configuration mongodb_journal Warns if the journal is disabled. MongoDB Journal
Generic Configuration mongodb_loglevel Warns if MongoDB is not using the default Log level. MongoDB Non-Default Log Level
Generic Configuration mongodb_read_tickets Warns if MongoDB is using more than 128 read tickets. MongoDB Read Tickets
Generic Configuration mongodb_write_tickets Warns if MongoDB is using more than 128 write tickets. MongoDB Write Tickets
Generic Configuration mongodb_write_tickets_runtime Warns if MongoDB is using more than 128 write tickets during runtime. MongoDB - Configuration Write Ticket Check
Replication Configuration mongodb_psa_architecture_check Raises an error if the replicaSet is utilizing a PSA (Primary-Secondary-Arbiter) architecture. MongoDB PSA Architecture
Replication Configuration mongodb_replicaset_topology Warns if the Replica Set has less than three data-bearing nodes. MongoDB Replica Set Topology
Resources Configuration mongodb_collection_fragmented Warns if the storage size exceeds the data size of a collection, indicating potential fragmentation. This suggests the need for compaction or an initial sync to reclaim disk space. MongoDB Collections Fragmented
Resources Configuration mongodb_cpucores Warns if the number of CPU cores does not meet the minimum recommended requirements according to best practices. MongoDB CPU Cores
Resources Configuration mongodb_dbpath_mount Warns if dbpath does not have a dedicated mount point. MongoDB - Separate Mount Point Other Than “/” Partition for dbpath.
Resources Configuration mongodb_fcv_check Warns if there is a mismatch between the MongoDB version and the internal FCV (Feature Compatibility Version) parameter setting. MongoDB - FCV Mismatch
Resources Configuration mongodb_maxsessions Warns if MongoDB is configured with a maxSessions value other than the default value of 1000000. MongoDB maxSessions
Resources Configuration mongodb_swap_allocation Warns if there is no swap memory allocated to your instance. MongoDB - Allocate Swap Memory
Resources Configuration mongodb_taskexecutor Warns if the count of MongoDB TaskExecutorPoolSize exceeds the number of available CPU cores. MongoDB TaskExecutorPoolSize High
Resources Configuration mongodb_xfs_ftype Warns if dbpath is not using the XFS filesystem type. MongoDB - XFS
Version Configuration mongodb_EOL Raises an error or a warning if your current PSMDB or MongoDB version has reached or is nearing its End-of-Life (EOL) status. MongoDB Version EOL
Version Configuration mongodb_unsupported_version Raises an error if your current PSMDB or MongoDB version is not supported. MongoDB Unsupported Version
Version Configuration mongodb_version Provides information on current MongoDB or Percona Server for MongoDB versions used in your environment. It also offers details on other available minor or major versions that you may consider for upgrades. MongoDB Version Check
Generic Performance mongodb_multiple_services Warns if multiple mongod services are detected running on a single node. MongoDB - Multiple mongod Services
Replication Performance mongodb_chunk_imbalance Warns if the distribution of chunks across shards is imbalanced. MongoDB Sharding - Chunk Imbalance Across Shards
Replication Performance mongodb_oplog_size_recommendation Warns if the oplog window is below a 24-hour period and provides a recommended oplog size based on your instance. MongoDB - Oplog Recovery Window is Low
Replication Performance mongodb_replication_lag Warns if the replica set member lags behind the primary by more than 10 seconds. MongoDB Replication Lag
Index Query mongodb_shard_collection_inconsistent_indexes Warns if there are inconsistent indexes across shards for sharded collections. Missing or inconsistent indexes across shards can have a negative impact on performance. MongoDB Sharding - Inconsistent Indexes Across Shards
Index Query mongodb_unused_index Warns if there are unused indexes on any database collection in your instance. This requires enabling the “indexStats” collector. MongoDB - Unused Indexes
Authentication Security mongodb_auth Warns if MongoDB authentication is disabled. MongoDB Authentication
Authentication Security mongodb_localhost_auth_bypass Warns if MongoDB localhost bypass is enabled. MongoDB localhost authentication bypass enabled
Configuration Security mongodb_authmech_scramsha256 Warns if MongoDB is not using the default SHA-256 hashing function as its SCRAM authentication method. MongoDB Security AuthMech Check
Connection Security mongodb_bindip Warns if the MongoDB network binding is not set as Recommended. MonogDB IP Bindings
CVE Security mongodb_cve_version Shows an error if MongoDB or Percona Server for MongoDB version is older than the latest version containing CVE (Common Vulnerabilities and Exposures) fixes. MongoDB CVE Version

MySQL

Advisor Check Name Description Summary
Connection Configuration mysql_configuration_max_connections_usage Checks the MySQL max_connections configuration option to ensure maximum utilization is achieved. Check Max Connections Usage
Generic Configuration mysql_automatic_sp_privileges_enabled Checks if the automatic_sp_privileges configuration is ON. Checks if automatic_sp_privileges configuration is ON.
Generic Configuration mysql_config_binlog_retention_period Checks whether binlogs are being rotated too frequently, which is not recommended, except in very specific cases. Binlogs Retention Check
Generic Configuration mysql_config_binlog_row_image Advises when to set binlog_row_image=FULL. Binlogs Raw Image is Not Set to FULL
Generic Configuration mysql_config_binlogs_checksummed Advises when to set binlog_checksum=CRC32 to improve consistency and reliability. Server is Not Configured to Enforce Data Integrity
Generic Configuration mysql_config_general_log Checks whether the general log is enabled. General Log is Enabled
Generic Configuration mysql_config_log_bin Checks whether the binlog is enabled or disabled. Binary Log is disabled
Generic Configuration mysql_config_sql_mode Checks whether the server has specific values configured in sql_mode to ensure maximum data integrity. Server is Not Configured to Enforce Data Integrity
Generic Configuration mysql_config_tmp_table_size_limit Checks whether the size of temporary tables exceeds the size of heap tables. Temp Table Size is Larger Than Heap Table Size
Generic Configuration mysql_configuration_log_verbosity Checks whether warnings are being printed on the log. Check Log Verbosity
Generic Configuration mysql_test_database Notifies if there are database named ‘test’ or ‘test_%’. MySQL Test Database
Generic Configuration mysql_timezone Verifies whether the time zone is correctly loaded. MySQL configuration check
InnoDB Configuration innodb_redo_logs_not_sized_correctly Reviews the InnoDB redo log size and provides suggestions if it is configured too low. InnoDB Redo Log Size is Not Configured Correctly.
InnoDB Configuration mysql_ahi_efficiency_performance_basic_check Checks the efficiency and effectiveness of InnoDB’s Adaptive Hash Index (AHI). InnoDB Adaptive Hash Index (AHI) Efficiency
InnoDB Configuration mysql_config_innodb_redolog_disabled Warns when the MySQL InnoDB Redo log is set to OFF, which poses a significant security risk and compromises data integrity. The MySQL InnoDB Redo log is a crucial component for maintaining the ACID (Atomicity, Consistency, Isolation, Durability) properties in MySQL databases. Redo Log is Disabled in This Instance
InnoDB Configuration mysql_configuration_innodb_file_format Verifies whether InnoDB is configured with the recommended file format. MySQL InnoDB File Format
InnoDB Configuration mysql_configuration_innodb_file_maxlimit Checks whether InnoDB is configured with the recommended auto-extend settings. InnoDB Tablespace Size Has a Maximum Limit.
InnoDB Configuration mysql_configuration_innodb_file_per_table_not_enabled Warns when innodb_file_per_table is not enabled. innodb_file_per_table Not Enabled
InnoDB Configuration mysql_configuration_innodb_flush_method Checks whether InnoDB is configured with the recommended flush method. MySQL InnoDB Flush Method
InnoDB Configuration mysql_configuration_innodb_strict_mode Warns about password lifetime. InnoDB strict mode
Replication Configuration mysql_config_relay_log_purge Identifies whether a replica node has relay-logs purge set. Automatic Relay Log Purging is OFF
Replication Configuration mysql_config_replication_bp1 Identifies whether a replica node is in read-only mode and if checksum is enabled. Checks Basic Best Practices When Setting Replica Node.
Replication Configuration mysql_config_slave_parallel_workers Identifies whether replication is single-threaded. Replication is Single-Threaded
Replication Configuration mysql_config_sync_binlog Checks whether the binlog is synchronized before a transaction is committed. Sync Binlog Disabled
Replication Configuration mysql_log_replica_updates Checks if a replica is safely logging replicated transactions. MySQL Configuration Check
Replication Configuration replica_running_skipping_errors_or_idempotent_mode Reviews replication status to check if it is configured to skip errors or if the slave_exec_mode is set to be idempotent. Replica is skipping errors or slave_exec_mode is Idempotent.
Resources Configuration mysql_32binary_on_64system Notifies if version_compile_machine equals i686. Check if Binaries are 32 Bits
Version Configuration mysql_unsupported_version_check Warns against an unsupported Mysql version. Checks Mysql Version
Version Configuration mysql_version Warns if MySQL, Percona Server for MySQL, or MariaDB version is not the latest available one. MySQL Version
Version Configuration mysql_version_eol_57 Checks if the server version is EOL. End Of Life Server Version (5.7).
Index Query mysql_performance_temp_ondisk_table_high Warns if there are too many on-disk temporary tables being created due to unoptimized query execution. Too Many on Disk Temporary Tables
Index Query mysql_tables_without_pk Checks tables without primary keys. MySQL check for a table without Primary Key
Schema Design Query mysql_indexes_larger Check all the tables to see if any have indexes larger than data. This indicates a sub-optimal schema and should be reviewed. Tables With Index Sizes Larger Than Data
Authentication Security mysql_automatic_expired_password Warns if the MySQL parameter for automatic password expiry is not active. MySQL Automatic User Expired Password
Authentication Security mysql_security_anonymous_user Verifies if anonymous users are present, as this would contradict security best practices. Anonymous User (You Must Remove Any Anonymous User)
Authentication Security mysql_security_open_to_world_host Checks whether host definitions are set as ‘%’ since this is overly permissive and could pose security risks. UserS Have Host Definition ‘%’ Which is Too Open
Authentication Security mysql_security_root_not_local Checks whether the root user has a host definition that is not set to 127.0.0.1 or localhost. Root User Can Connect From Non-local Location
Authentication Security mysql_security_user_ssl Reports users who are not using a secure SSL protocol to connect. Users Not Using Secure SSL
Authentication Security mysql_security_user_super_not_local Reports users with super privileges who are not connecting from the local host or the host is not fully restricted (e.g., 192.168.%). Users have Super privileges With Remote and Too Open Access
Authentication Security mysql_security_user_without_password Reports users without passwords. Users Without Password
Configuration Security mysql_config_local_infile Checks if the “LOAD DATA INFILE” functionality is active. Load Data in File Active
Configuration Security mysql_configuration_secure_file_priv_empty Warns when secure_file_priv is empty as this enables users with FILE privilege to create files at any location where MySQL server has Write permission. secure_file_priv is Empty
Configuration Security mysql_password_expiry Checks if MySQL user passwords are expired or expiring within the next 30 days. Check MySQL User Password Expiry
Configuration Security mysql_require_secure_transport Checks the status of mysql_secure_transport_only. MySQL configuration check
Configuration Security mysql_security_password_lifetime Warns about password lifetime. InnoDB Password Lifetime
Configuration Security mysql_security_password_policy Checks for password policy. MySQL Security Check for Password
Connection Security mysql_private_networks_only Notifies about MySQL accounts that are allowed to connect from public networks. MySQL Users With Granted Public Networks Access
Replication Security mysql_replication_grants Checks if replication is configured on a node without user grants. MySQL Security Check for Replication User
Replication Security mysql_security_replication_grants_mixed Checks if replication privileges are mixed with more elevated privileges. Replication Privileges

PostgreSQL

Advisor Check Name Description
Connection Configuration postgresql_max_connections_1 Notifies if the max_connections configuration option is set to a high value (above 300). PostgreSQL doesn’t cope well with having many connections even if they are idle. The recommended value is below 300.
Generic Configuration postgresql_archiver_failing_1 Verifies if the archiver has failed.
Generic Configuration postgresql_fsync_1 Returns an error if the fsync configuration option is set to OFF, as this can lead to database corruptions.
Generic Configuration postgresql_log_checkpoints_1 Notifies if the log_checkpoints configuration option is not enabled. It is recommended to enable the logging of checkpoint information, as that provides a lot of useful information with almost no drawbacks.
Generic Configuration postgresql_logging_recommendation_checks Verifies whether the recommended minimum logging features are enabled.
Generic Configuration postgresql_wal_retention_check Checks if there are too many WAL files retained in the WAL directory.
Vacuum Configuration postgresql_log_autovacuum_min_duration_1 Notifies if the log_autovacuum_min_duration configuration option is set to -1 (disabled). It is recommended to enable the logging of autovacuum run information, as it provides a lot of useful information with almost no drawbacks.
Vacuum Configuration postgresql_table_autovac_settings Returns tables where autovacuum parameters are specified along with the corresponding autovacuum settings.
Vacuum Configuration postgresql_txid_wraparound_approaching Verifies the age of databases and alerts if the transaction ID wraparound issue is nearing.
Vacuum Configuration postgresql_vacuum_sanity_check This performs a quick check of some vacuum parameters.
Version Configuration postgresql_eol_check Checks if the currently installed PostgreSQL version has reached its EOL and is no longer supported.
Version Configuration postgresql_extension_check Lists outdated extensions with newer versions available.
Version Configuration postgresql_unsupported_check Verifies if the currently installed version is supported by Percona.
Version Configuration postgresql_version_check Checks if the currently installed version is outdated for its release level.
Generic Performance postgresql_cache_hit_ratio_1 Checks the hit ratio of one or more databases and raises a complaint when they are too low.
Generic Performance postgresql_config_changes_need_restart_1 Warns if there are any settings or configurations that have been changed and require a server restart or reload.
Generic Performance postgresql_tmpfiles_check Reports the number of temporary files and the number of bytes written to disk since the last statistics reset.
Replication Performance postgresql_stale_replication_slot_1 Warns if there is a stale replication slot. Stale replication slots will lead to WAL file accumulation and can result in a database server outage.
Vacuum Performance postgresql_table_bloat_bytes Verifies the size of the table bloat in bytes across all databases and raises alerts accordingly.
Vacuum Performance postgresql_table_bloat_in_percentage Verifies the size of the table bloat in the percentage of the total table size and alerts accordingly.
Index Query postgresql_number_of_index_check Lists relations with more than ten indexes.
Index Query postgresql_sequential_scan_check Checks for tables with excessive sequential scans.
Index Query postgresql_unused_index_check Lists relations with indexes that have not been used since the statistics were last reset.
Authentication Security postgresql_super_role Notifies if there are users with Superuser role.
Configuration Security postgresql_expiring_passwd_check Checks for passwords that are expiring and displays the time left before they expire.
CVE Security postgresql_cve_check Checks if the currently installed version has reported security vulnerabilities.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.


Last update: 2024-05-21