• MySQL知识库 :: performance
  • What are the MySQL Enterprise Monitor Advisor Rules?

  • Discussion

    The MySQL Enterprise Monitor Advisor Rules are a set of MySQL supplied best practices that allow DBAs to implement new MySQL servers with confidence and to proactively manage the dynamic nature of all of their MySQL servers over time. The MySQL Advisor Rules monitor all MySQL servers for adherence to MySQL recommended configuration and server settings and notify the DBA with specific instructions to proactively address found exceptions and to align these exceptions with MySQL best practices. To this end, the Enterprise Monitor employs over 100 MySQL Advisor Rules that monitor over 600 MySQL and OS specific variables and metrics that track and report on the overall health, security, availability, and performance of each MySQL server.

    The Enterprise Monitor ships with the following set of MySQL Advisors:

    • Administration Advisor
      Monitors and advises on problems relating to general database administration, recoverability and performance configuration settings.
    • Upgrade Advisor
      Monitors and advises on specific bugs that can potentially impact versions of MySQL Enterprise Server deployed within an environment. Advises on specific upgrade path to version of MySQL that provides fix for identified bugs.
    • Performance Advisor
      Monitors and advises on deviations from MySQL performance best-practice rules.
    • Schema Advisor
      Monitors and advises on unexpected changes to database schema and objects.
    • Memory Usage Advisor
      Monitors and advises on the optimal use of memory and cache settings for specific MySQL implementations.
    • Security Advisor
      Monitors and advises on security vulnerabilities in a MySQL Server.
    • Replication Advisor
      Monitors and advises on issues relating to replication setup, synchronization or performance problems.
    • Custom Advisor
      Allows a DBA to define best practices to fit their organization's specific needs.

    Each of the Advisors is designed to cover specific DBA areas of concern and is comprised of a set of Advisor Rules that help DBAs proactively find problems and tuning opportunities that they may not have the time or expertise to find themselves. The Advisors and examples of the Rules they provide are summarized below:

    Administration Advisor Rules

    Administration Advisor rules are designed to ensure all MySQL servers are running with optimal availability, uptime, performance and recoverability settings. For example, the Administration Advisor provides an Advisor Rule designed to monitor the number of active connections to a MySQL server and to proactively notify you when this number approaches the maximum allowed for a specific server. You can then use the advice and command provided by the Advisor Rule to adjust this value before additional user connections are aborted or denied.

    Upgrade Advisor Rules

    Upgrade Advisor Rules ensure you are running the most secure, up to date versions of the MySQL Enterprise Server. Rules are designed to identify and alert you to specific bugs in the MySQL bug database that affect a version of MySQL you have running in your production environment. They also advise you on the upgrade path to the specific version of the MySQL Enterprise Server that contains the fix for any bugs that have been identified.

    Performance Advisor Rules

    Performance Advisor Rules ensure the highest levels of performance by monitoring dynamic server metrics (cache usage/hit ratios, sorting activity, row accesses, concurrent queries, temp tables, etc.) and advising on adjustments to configuration settings. The Performance Advisor provides advanced monitoring of your stored code objects to ensure the proper use of indexes in your SELECT and ORDER BY statements and advises you when row accesses or sorting activities exceed best practice threshold values.

    Schema Advisor Rules

    Schema Advisor Rules identify and notify you of schema level add, drop, and alter activities that may affect database performance and availability. For example, the Schema Advisor provides Advisor Rules that are designed to identify and notify you of altered tables or dropped indexes that may affect the design and performance of your database. In addition, the Schema Advisor Rules provide you with the advice needed to help quickly identify the unexpected changes and recover the affected objects.

    Memory Usage Advisor Rules

    Memory Usage Advisor rules ensure the highest levels of performance by monitoring dynamic server metrics (memory usage, cache usage, hit ratios, etc.) and advising on adjustments to configuration settings. For example, the Memory Usage Advisor provides an Advisor Rule that proactively monitors your read intensive, common query Web or Intranet based applications. If these applications suddenly spike in disk I/O, and consequently degrade in performance, the Memory Usage Advisor will proactively identify the problem and advise you how to tune and optimize your MySQL configurations settings for things like key_buffer_size or query_cache_size for improved performance.

    Security Advisor Rules

    Security Advisor Rules identify and notify you of security vulnerabilities and unplanned changes to security settings across all of your MySQL servers. For example, if a new MySQL user is added to one of your production MySQL servers or if privileges are granted without your knowledge, the Security Advisor provides Advisor Rules that will immediately notify you of this security hole and provide you with instructions on how to quickly fix the issue.

    Replication Advisor Rules

    Replication Advisor Rules find issues and recommend solutions to replication setup and performance problems. For example, the Replication Advisor provides Advisor Rules that monitor Slave/Master latency against MySQL or user-defined time thresholds. If a slave is too far behind the master, the Replication Advisor will notify you of the problem and show you how to troubleshoot your MySQL environment, including network related issues, so that your high-volume mission-critical applications maintain their performance.

    Custom Advisor Rules

    This feature is for DBAs with advanced monitoring needs: The MySQL Advisors can be extended to include custom Advisor Rules that monitor existing metrics or that the DBA opts to collect from the MySQL server. DBAs can easily copy and edit the MySQL provided Advisor Rules to better meet their organization's specific needs. Or, they can create Advisor Rules and evaluated expressions from scratch using the MySQL provided template and the MySQL and OS variables and counters collected by the Enterprise Monitor.

    For example, a good indicator of how well a MyISAM powered data warehouse is performing is to analyze the MyISAM cache hit rate by calculating the percentage of key read requests that were satisfied by key reads. Users can assign the thresholds that best align with their specific server level agreements (SLAs). If the Query Cache hit ratio falls below 99%, an email notification can be delivered to the DBA to increase the query_cache_size value to correct the problem and improve throughput.