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-04-18