Automated Database Performance Advisors

The ClusterControl Developer Studio provides you a set of monitoring and performance advisors to use and lets you create custom advisors to add security and stability to your database infrastructures.

ClusterControl Developer Studio

ClusterControl comes with a library of Advisors to help you keep track of your database performance, alert you when there are anomalies, keep your data secure by checking and monitoring who has access and ensuring that all the necessary security features are enabled. You can also easily extend the functionality of ClusterControl by creating custom advisors that can run on-demand or on a schedule.

Featured Advisors

MySQL
Galera & NDB Cluster
MongoDB
Host Statistics
Auto Tune table_cache_size
This advisor calculates the percentage of open_tables over tables_open_cache and adjusts table_open_cache automatically based on the running workload.
File Name in ClusterControl
s9s/mysql/auto_tuners/table_cache.js
Auto Tune thread_cache_size
This advisor calculates the percentage of threads_created over threads_cached and adjust the thread_cache_size automatically based on the running workload.
File Name in ClusterControl
s9s/mysql/auto_tuners/thread_cache.js
Connections Currently Used
This advisor calculates the percentage of threads_connected over max_connections, if the percentage is higher than 90% you will be notified, preventing your database server from becoming unstable.
File Name in ClusterControl
s9s/mysql/connections/connections_used_pct.js
Connections Ever Used
This advisor calculates the percentage of max_used_connections over max_connections to determine the maximum ever used connections, if the percentage is higher than 90% you will be notified, preventing your database server from becoming unstable.
File Name in ClusterControl
s9s/mysql/connections/max_connections_used_pct.js
InnoDB Buffer Pool Hit Ratio
This advisor calculates the ratio of innodb_buffer_pool_reads over innodb_buffer_pool_read_requests and notifies you if the ratio is lower than 999. Hit ratio provides insight on how often MySQL pages are retrieved from memory instead of disk.
File Name in ClusterControl
s9s/mysql/innodb/innodb_bufferpool_hitratio.js
InnoDB Buffer Pool Waits
This advisor reads the innodb_buffer_pool_wait_free value from the runtime status and notifies you if the value is more than 0, which indicates the innodb_buffer_pool_size is too small, and that it had to wait for checkpointing.
File Name in ClusterControl
s9s/mysql/innodb/innodb_bufferpool_waits.js
InnoDB Log File Size Check
This advisor calculates the InnoDB log growth per hour and compares it with the innodb_log_file_size configured on the host and notifies you if the InnoDB log growth is higher than what is configured, which is important to avoid IO spikes during flushing.
File Name in ClusterControl
s9s/mysql/innodb/check_logfile_size.js
InnoDB Percent Dirty Pages
This advisor calculates the ratio of innodb_buffer_pool_pages_dirty over innodb_buffer_pool_pages_total and notifies you if the ratio is higher. This informs you when to increase the innodb_buffer_pool_size, if the ratio stays high for a long time.
File Name in ClusterControl
s9s/mysql/innodb/innodb_dirty_pages_pct.js
Retrieve DB Size
This advisor provides a snapshot of your database by selecting the total number of rows, data size, index size, total size and index ratio per database object from information_schema.
File Name in ClusterControl
s9s/mysql/i_s/db_size.js
System Health Check
This advisor is a Health Check which executes a series of queries to test DDL (metadata locks) and DML and notifies you if the check fails; which could indicate that although the server may be up the database may not be responding correctly.
File Name in ClusterControl
s9s/mysql/health/system_check.js
Check Performance Schema
This advisor reads the performance_schema value in runtime and suggests that you to enable performance_schema in the configuration file is disabled, allowing you to inspect internal execution of the server at runtime.
File Name in ClusterControl
s9s/mysql/p_s/check_ps.js
InnoDB Log Waits
This advisor reads the innodb_log_waits value from the runtime status and notifies if the value is more than 0, which indicates a peak write load and possibility of disks are too slow to sustain the IO.
File Name in ClusterControl
s9s/mysql/innodb/innodb_log_waits.js
Processlist From PS
This advisor selects the MySQL process list on all database servers from the performance_schema and provides a summary of all running processes across the MySQL server.
File Name in ClusterControl
s9s/mysql/p_s/processlist.js
Secure MySQL Installation
This advisor runs multiple checks and provides security recommendations based on mysql_secure_installation script.
File Name in ClusterControl
s9s/mysql/programs/mysql_secure_installation.js
Table Access Without Using Index
This advisor selects all tables being accessed without using an index from performance_schema to show you where to improve the queries running against these tables.
File Name in ClusterControl
s9s/mysql/p_s/no_indexes_used.js
Top Accessed DB Files
This advisor selects the top 10 most frequently accessed files by MySQL and shows you the number of times accessed and total time from performance_schema.
File Name in ClusterControl
s9s/mysql/p_s/top_accessed_db_files.js
Top Queries
This advisor selects the top 5 queries, sorted by execution count, from performance_schema.
File Name in ClusterControl
s9s/mysql/p_s/top_queries.js
Top Tables by IO Wait
This advisor selects the top 10 tables, sorted by the sum of IO wait, from performance_schema.
File Name in ClusterControl
s9s/mysql/p_s/top_tables_by_iowait.js
Top Tables by Lock Wait
This advisor selects the top 10 tables, sorted by the sum of lock wait, from performance_schema.
File Name in ClusterControl
s9s/mysql/p_s/top_tables_by_lockwait.js
Unused Indexes
This advisor selects the unused existing indexes from performance_schema to allow you to remove the unused indexes which improves the schema and overall performance.
File Name in ClusterControl
s9s/mysql/p_s/unused_indexes.js
Access From Any Host
This advisor identifies all users that use a wildcard host from the mysql system table, and let you have more control over which hosts are able to connect to the servers.
File Name in ClusterControl
s9s/mysql/security/grant_any_host.js
Account Without Password
This advisor identifies all users who do not have a password in the mysql system table, which helps to increase data security.
File Name in ClusterControl
s9s/mysql/security/grant_no_password.js
Binlog Expiration
This advisor reads the value of expire_log_days to see if it exists and provides a recommendation to the user to set one, preventing indefinite binary logs growth.
File Name in ClusterControl
s9s/mysql/replication/binlog_expiration.js
Check Compatible Schema
This advisor checks the table design to ensure it's compatible with MySQL Group Replication technology and notifies you if any tables require alteration.
File Name in ClusterControl
s9s/mysql/group_repl/compatible_schema.js
Check for MyISAM Tables
This advisor identifies all tables with MyISAM storage engine from the information_schema, which is not a recommended "crash-safe" type of storage engine.
File Name in ClusterControl
s9s/mysql/schema/schema_check_myisam.js
Check for Primary Keys
This advisor identifies tables that have no explicit primary keys from the information_schema which is important to have as a unique identifier for each row in a dataset.
File Name in ClusterControl
s9s/mysql/schema/schema_check_nopk.js
Query Cache Hit Ratio
This advisor calculates the ratio of Qcache_hits over the sum of Qcache_hits and Qcache_inserts and provides recommendations for you to configure query_cache_size correctly, a benefit for users with read-intensive workloads.
File Name in ClusterControl
s9s/mysql/query_cache/qc_hitratio.js
Query Cache Size
This advisor calculates the query_cache_size whether it's between 64M and 256M and provides you with recommendations to configure the correct query_cache_size.
File Name in ClusterControl
s9s/mysql/query_cache/qc_size.js
Security Audit
This advisor highlights any account that has been allowed to access the database with global host access and without password, allowing you to remove or patch the unsafe accounts and increase security.
File Name in ClusterControl
s9s/mysql/programs/security_audit.js
Setup MySQL Logrotate
This advisor creates a MySQL logrotate configuration file under /etc/logrotate.d/mysql on every MySQL server which prevents your MySQL error log from growing out of hand.
File Name in ClusterControl
s9s/mysql/programs/setup_mysql_logrotate.js
Check GRA Log Files
This advisor scans the MySQL data directory and notifies you if the GRA_*.log file exists. This file only appears if there is a failure or warning in your Galera replication.
File Name in ClusterControl
s9s/mysql/galera/check_gra_log_files.js
Check wsrep_cluster_address
This advisor reads the value of wsrep_cluster_address inside each database node's configuration file and notifies you if the parameter does not exist, preventing potential catastrophic failure and data loss during service startup.
File Name in ClusterControl
s9s/mysql/galera/wsrep_cluster_address.js
Check wsrep_node_name
This advisor reads the value of wsrep_node_name inside each database node's configuration file and notifies you if the node does not contain the parameter; which is a recommended way to simplify recovery in especially in WAN setup.
File Name in ClusterControl
s9s/mysql/galera/wsrep_node_name.js
Check wsrep_slave_threads
This advisor reads the value of wsrep_slave_threads in runtime and compares the value with the warning threshold and notifies you if the slave thread is lower than 4 (a value that can be increased later depending on the workload).
File Name in ClusterControl
s9s/mysql/galera/wsrep_slave_threads.js
Check wsrep_sst_method
This advisor reads the value of wsrep_sst_method and and notifies you if wsrep_sst_xtrabackup-v2 is not installed, which is a recommended SST method for all Galera variants.
File Name in ClusterControl
s9s/mysql/galera/wsrep_sst_method.js
DataMemory Usage
This advisor calculates the ratio of DataMemory usage over DataMemory total so you can increase or clear the history tables as needed.
File Name in ClusterControl
s9s/ndbcluster/data_memory_usage.js
Fix wsrep_cluster_address
This advisor reads wsrep_cluster_address in runtime and adjusts the value of wsrep_cluster_address inside configuration file automatically to ensure the MySQL service will be started correctly.
File Name in ClusterControl
s9s/mysql/galera/fix_wsrep_cluster_address.js
Galera replication window
This advisor determines the write load on the Galera cluster and estimates if the Galera cache file is sufficient in size to sustain 4 hours of transactions.
File Name in ClusterControl
s9s/mysql/galera/check_replication_window.js
IndexMemory Usage
This advisor calculates the ratio of IndexMemory usage over IndexMemory total so you can increase or clear the history tables as needed.
File Name in ClusterControl
s9s/ndbcluster/index_memory_usage.js
Validate wsrep_sst_auth
This advisor checks the users and passwords from wsrep_sst_auth and compares it with the xtrabackup directive inside configuration file. This ensures they are the same and minimized the risk of failure during state transfer and syncs.
File Name in ClusterControl
s9s/mysql/galera/validate_sst_auth.js
Authentication/Authorization Check
This advisor collects all database information, users/roles per database per host, and verifies if these databases are protected and notifies you if users have excessive rights or weak passwords.
File Name in ClusterControl
s9s/mongodb/security/authorization_check.js
Collection Lock Percentage
This advisor collects lock information from MongoDB every 20 minutes and notifies you if the percentage of locking exceeds the threshold you have established.
File Name in ClusterControl
s9s/mongodb/mmap/collection_locks.js
Collection Sharding Check
This advisor checks all collections daily on the mongo router and verifies that they are enabled for sharding, which prevents you from have an unsharded collection that may fill up to capacity.
File Name in ClusterControl
s9s/mongodb/sharding/non_sharded_collections.js
Connections Used
This advisor collects the number of connections every minute and notifies you if the connections aren't exceeding the threshold in percentage.
File Name in ClusterControl
s9s/mongodb/connections/connections.js
MongoDB Authentication Enabled
This advisor collects all configuration and runtime options to determine if authentication has been enabled on this host and notifies you of any security anomalies it finds, ensuring your database is protected.
File Name in ClusterControl
s9s/mongodb/security/auth_enabled_check.js
Replication Check
This advisor collects the replication information of the primary every minute, then calculates the lag between the primary and secondaries in seconds and notifies you if the lag exceeds 60 seconds. This is important as Mongo client drivers tend to pick the least lagging secondaries and bringing more stress on these less laggy nodes.
File Name in ClusterControl
s9s/mongodb/replication/check_lag.js
Replication Window
This advisor collects the first and the last oplog entry every minute from every host, calculates the time difference between the two and notifies you if the window is within the set threshold (less than 24 hours for warning, less than 1 hour for critical).
File Name in ClusterControl
s9s/mongodb/replication/replication_window.js
Tickets Used
This advisor collects the number of tickets every minute and notifies you if the number of tickets exceeds 90%. Tickets are used in the WiredTiger storage engine to control read/write operations (like semaphores), if you run out of tickets it will prevent your ability to read/write to the database.
File Name in ClusterControl
s9s/mongodb/connections/tickets.js
Disk Space Used and Predicted Usage
This advisor performs a disk check every 30 minutes and notifies you if the disk space usage is greater than 95%. It also predicts the storage usage in "X" days based on linear estimation.
File Name in ClusterControl
host/disk_space_usage.js
Excessive CPU Usage
This advisor performs CPU check every 5 minutes and notifies you if the average CPU usage for the last hour exceeds 90%, which enables you to prevent database performance issues caused by high CPU usage.
File Name in ClusterControl
host/cpu_usage.js
Swappiness Check
This advisor checks if swappiness is disallowed (set to 1) on the host to ensure that the host does not "swap" which is not optimal for database workload performance.
File Name in ClusterControl
host/swappiness.js

Demo

Introduction to the Developer Studio
Included free with all versions of ClusterControl, the Developer Studio saves you time by automating the tasks you have to perform on a regular basis via so called Advisors, using the powerful ClusterControl DSL (Domain Specific Language). These Advisors let you detect and solve any unique problems which may occur in your database environment.

Explore All ClusterControl Features

Backup Management

ClusterControl’s comprehensive backup management provides continuous data protection for your open source databases across local and cloud environments.

Monitoring & Alerting

ClusterControl provides a unified view of all your database deployments across multiple datacenters, letting you see the big picture or drilling down into individual nodes.

Deployment & Scaling

ClusterControl’s point-and-click interface lets you automate deployment and scaling of database setups securely to get production-ready environments up and running in a very short time.

Upgrades & Patching

ClusterControl ensures your databases stay up-to-date and compliant, reducing maintenance time by helping you apply upgrades and patches using battle-tested procedures.

Security & Compliance

ClusterControl ensures that your open source database deployments always adhere to the security model setups for each technology and keeps your critical data safe through encryption.

Operational Reporting

ClusterControl provides you with comprehensive operational reports on the health and stability of your database operations.

Configuration Management

ClusterControl makes configuring your databases easy by providing you battle-tested templates as well as making it easy to edit the configuration for your unique setup.

Automatic Recovery & Repair

The advanced automated failover technology in ClusterControl ensures your mission critical applications achieve high availability with zero downtime.

Performance Management

ClusterControl provides advanced monitoring and reporting features to let you easily keep a close eye on the performance of your database setups.

Automated Performance Advisors

The ClusterControl Developer's Studio provides you a set of monitoring and performance advisors to use and lets you create custom advisors to add security and stability to your database infrastructures.