Display MySQL report

This feature display a report of your current instance (v1.6.052102 or above ).
The MySQL server internally maintains numerous “status variables”, many of which are very useful for improving database preformance.
This feature summarize the status variables.
This section of the MySQL checklist walks you through using this option to tune your database.

  1. Review http://hackmysql.com/mysqlreportguide for an in-depth understanding of the report generated.

On the Multiples MySQL engine section, click on the icon in the Column "STATS" of the desired MySQL instance.

21-05-2012_02-57-00.png

Click on "MySQL report" in order to display the status.

21-05-2012_02-59-16.png

Understanding mysqlreport output

  1. Verify your server version.
    Are you running the latest community release (5.0.45), or the latest stable Enterprise release (5.0.44sp1)?

  2. Key report section.

    • Are you using the MyISAM storage engine?
      The MyISAM storage engine is affected by the key buffer. The InnoDB storage engine is not.

    • Is Buffer used or Current at 75% usage or more?
      Increase the key_buffer_size to allocate more key buffer to MySQL.

      Tunable: key_buffer_size.

  3. Questions report section.
    Questions include all SQL queries as well as MySQL protocol communications. This section is very useful for understanding how MySQL is being used by your application. It also offers a nice overview of the effectiveness of the MySQL query cache.

    • What percentage of your queries are DMS?
      DMS stand for Data Manipulation Statements, and includes SELECT, INSERT, REPLACE, UPDATE, and DELETE queries.

    • What percentage of your queries are QC Hits?
      QC stands for Query Cache. If you don't have a QC Hits line, you have not enabled the MySQL query cache. Ideally, this should account for the majority of your questions.

    • What percentage of your queries are _Com?
      _Com accounts for all MySQL commands, primarily those that are protocol related. This should be very small. In Drupal 5 and earlier versions, most _Com overhead typically comes from table locking.

    • What type of queries are happening most frequently?
      Typical Drupal powered web sites will see the vast majority of their queries as SELECTS.

    • What type of queries show up in the _Com section?
      This only becomes important if a significant percentage of your queries are _Com.

  4. SELECT and Sort report section.

    • What percentage of your SELECT queries are Scans?
      A scan is a SELECT query that required scanning the entire table instead of just a subset of it. These types of queries will often show up in your slow query log.

    • What percentage of your SELECT queries are Full joins?
      A full join is a SELECT query that joins two or more tables together, and then scans the entirety of the joined tables. Again, these types of queries will often show up in your slow query log. If you are seeing a significant number of joins, you may benefit from increasing your join buffer.

      Tunable: join_buffer_size, not that this is a per-connection memory allocation, so don't increase it too quickly and monitor it closely.

    • What percentage of your SELECT queries are Sorts?
      If you have a significant number of sorts, you may benefit from increasing your sort buffer. To be certain, monitor “SHOW STATUS LIKE 'Sort_merge_passes;”. If this value is increasing, especially if it is increasing quickly, you should increase the size of your sort buffer.

      Tunable: sort_buffer_size, note that this is a per-connection memory allocation, so don't increase it too quickly and monitor closely.

  5. Query Cache report section.

    • Is your query cache enabled?
      The query cache stores your SELECT query and its result in memory. If an identical SELECT query is made, the server is able to quickly return the result from the cache.

    • Is your query cache more than 10-20% fragmented?

    • What are your Insert:Prune and Hit:Insert ratios?

    • Is your query cache too big?
      There is a temptation to give MySQL's query cache as large as you possibly can. Unfortunately, there is a known locking bug in MySQL (reported as fixed in 5.0.50) in which the time spent flushing the query cache can lock up the entire server, resulting in poor performance. It is advised that you monitor your database carefully as you increase this query cache, especially if you increase the size of the cache beyond 64M in size.

    Tunable: query_cache_size

  6. Table Locks report section.

    • What percentage of your table locks show up as Waited?

  7. Tables report section

    • What percentage of your table cache is already used?
      If your table cache is already 100% used, you may want to consider increasing it. However, monitor your total memory consumption carefully, and don't set this value larger than you need. Note that different threads can open the same table, thus on a busy database you can frequently have more tables open than actually exist in your database. Tunable: table_cache

    • How many new tables are opening per second?
      If you are seeing as much or more than 1 table opened per second, this is usually a good indication you should be increasing your table cache.

      Tunable: table_cache

  8. Connections report section

    • What is the maximum number of connections you've seen used?
      By default, MySQL allows 100 simultaneous connections, however on a well tuned server most queries last less than a second so even on a busy web server you rarely have more than a couple dozen connections simultaneous connections. It is generally not advisable to increase the connection limit beyond 100, unless you already have a well tuned server and still require this many simultaneous connections.

    • How many connections are opening per second?

  9. Created Temp report section

    • What is your temp table, to disk table, to file ratio?
      A “temp table” is a temporary table that is created in memory. A “disk table” is a temporary table that is created on disk. Obviously you will see better performance if you have more temporary tables created in memory than on disk.

      Tunables: tmp_table_size, max_heap_table_size, both need to be raised together. However, this memory is allocated per-connection, so be careful not to increase either too quickly.

  10. Threads report section

    • How many threads are being created per second?

    • What percentage of your threads are using the thread cache?
      There is minimal overhead in creating threads, however if you see a large number of threads being created per second that aren't using the thread cache, you can reduce your CPU load by increasing your thread cache.

      Tunable: thread_cache_size

  11. Aborted report section

    • Are you seeing a high number of aborted clients?

    • Are you seeing a high number of aborted connections?
      Neither should be common, and should be at 0 or close to 0/s. If either value is high, you need to determine what is causing these errors.

  12. Bytes report section

  13. InnoDB Buffer Pool report section

    • How much of your buffer pool is currently being used?
      As a general rule, if you are using InnoDB, approximately 70% of your available RAM should be given to MySQL's InnoDB Buffer Pool. If over 80% of your buffer pool is being used, you should look into making more RAM available to MySQL. You do not ever want your Buffer Pool to be 100% full. Tunable: innodb_buffer_pool_size

    • Is your read ratio lower than .1?
      The read ration is the number of reads from disk versus the number of reads from RAM. If this is higher than .1, odds are that your Buffer Pool is too low.

    • Are you seeing significant reads from files?
      If you are seeing a significant number of reads from disk, you likely should be increasing your Buffer Pool.

    • How often is your buffer pool being flushed to disk?

  14. InnoDB Lock report section

    • Are you seeing a significant number of waits for locks, or a significant amount of time being spent waiting for locks?

  15. InnoDB Data, Pages, Rows report section

    • What is your ration of writes to fsyncs?
      By default, InnoDB is ACID compliant. This means that each transaction needs to be flushed to disk. If you can afford to loose a second of transactions, you may want to consider telling InnoDB to only flush to disk once every second.

      Tunable: innodb_flush_log_at_trx_commit, set to 0 to have InnoDB only flush to disk every second.

Source from http://tag1consulting.com/sites/default/files/MySQL.pdf