information_schema.global_variables and information_schema.global_status are two useful tables for getting information on the MySQL® server configuration and status. They have been introduced in MySQL® version 5.1.

You know that some status variables are ‘raw’ and they need a little manipulation to bring a kind of useful information. For example, let’s consider the variable BYTES_RECEIVED (i.e. the number of bytes received from all clients).

If you just select that variable you don’t have a useful information, but if you calculate the ratio ‘Bytes Received per Second’

(`BYTES_RECEIVED`) / (`UPTIME`)

you have a more interesting value and you can see how this value is changing over time.

I’ve created a SQL Script that can help you calculate many interesting Hit Ratios and Derived Performance Metrics and I am posting it here so that you can have and idea on what ratios you can calculate to monitoring Server’s performance.

A similar Script is included in HoneyMonitor - Audit Pro Edition (there, all the Hit Ratios are calculated at a fixed interval - using a Scheduled Event - so that you can create several reports to view how that ratios are changing, say, hour after hour. Also you can see the Ratios ‘on-line’ just opening the Performance Tuning Monitor included in that Edition of HoneyMonitor).

The link for the download of the script is dpm-query-05-rc.zip (md5: 9c40109ac8266ec86cc0efb88fd580bb).

Here’s the script output and some screenshots of the Script Execution in HoneyMonitor for MySQL® 5.1.24-rc, 5.1.23-maria-preview-alpha and 6.0.4-alpha.

                               Falcon_Cache_Hit_Ratio: 99.961663418
                           Maria_Page_Cache_Hit_Ratio: NULL
              Maria_Page_Cache-Size_Pseudo_Efficiency: NULL
                         Maria_Key_Buffer_Blocks_Numb: NULL
                            Max_Maria_Page_Cache_Used: NULL
                       Current_Maria_Page_Cache_Usage: NULL
                         Maria_Page_Cache_Write_Ratio: NULL
                          InnoDB_Rows_Read_per_Second: 0.00512412757310717
                      InnoDB_Rows_Inserted_per_Second: 0.000677327207939453
                       InnoDB_Rows_Updated_per_Second: 0
                       InnoDB_Rows_Deleted_per_Second: 0
                      InnoDB_Pages_Created_per_Second: 0.00223812468710428
                         InnoDB_Pages_Read_per_Second: 0.00241481874134935
                      InnoDB_Pages_Written_per_Second: 0.0234414111965132
                         InnoDB_Data_Reads_per_Second: 0.00353388108490149
                        InnoDB_Data_Writes_per_Second: 0.0291839679594782
                        InnoDB_Data_Fsyncs_per_Second: 0.00774508937774244
                 InnoDB_Data_Pending_Reads_per_Second: 0
                InnoDB_Data_Pending_Writes_per_Second: 0
                InnoDB_Data_Pending_Fsyncs_per_Second: 0
          InnoDB_Buffer_Pool_Read_Requests_per_Second: 7.13561268663309
                  InnoDB_Buffer_Pool_Reads_per_Second: 0.00108961333451129
         InnoDB_Buffer_Pool_Read_Ahaed_Rnd_per_Second: 0.000117796036163383
         InnoDB_Buffer_Pool_Read_Ahaed_Seq_per_Second: 8.83470271225373e-005
         InnoDB_Buffer_Pool_Write_Requests_per_Second: 3.82734045999352
          InnoDB_Buffer_Pool_Pages_Flushed_per_Second: 0.0234414111965132
                     InnoDB_Row_Lock_Waits_per_Second: 0
                         InnoDB_Buffer_Pool_Hit_Ratio: 99.9847299260433
                               InnoDB_Page_Cache_Free: 68.9453125
                              InnoDB_Page_Cache_Usage: 31.0546875
                        InnoDB_Buffer_Pool_Pages_Free: 5783552
              InnoDB_Buffer_Pool_Pages_Free_per_Total: 68.9453125
                        InnoDB_Buffer_Pool_Pages_Data: 2588672
              InnoDB_Buffer_Pool_Pages_Data_per_Total: 30.859375
                       InnoDB_Buffer_Pool_Pages_Dirty: 409600
              InnoDB_Buffer_Pool_Pages_Dirty_per_Data: 15.8227848101266
                       InnoDB_Buffer_Pool_Pages_Clean: 2179072
              InnoDB_Buffer_Pool_Pages_Clean_per_Data: 84.1772151898734
                        InnoDB_Buffer_Pool_Pages_Misc: 16384
              InnoDB_Buffer_Pool_Pages_Misc_per_Total: 0.1953125
                     InnoDB_Buffer_Pool_Pages_Latched: 0
           InnoDB_Buffer_Pool_Pages_Latched_per_Total: 0
                          MyISAM_Key_Buffer_Hit_Ratio: 84.8989298454221
                               MyISAM_Lock_Contention: 0
                  MyISAM_Cache-Size_Pseudo_Efficiency: 11.3475177304965
                               Key_Buffer_Blocks_Numb: 16
                           Max_MyISAM_Key_Buffer_Used: 50
                      Current_MyISAM_Key_Buffer_Usage: 31.25
                               MyISAM_Key_Write_Ratio: 41.4893617021277
                           Aborted_Clients_per_Second: 0.000235592072326766
                       Aborted_Clients_per_Connection: 0.0666666666666667
                          Aborted_Connects_per_Second: 0
                            Bytes_Received_per_Second: 31.0664958624142
                      Aborted_Connects_per_Connection: 0
                        Bytes_Received_per_Connection: 8791.04166666667
                                Bytes_Sent_per_Second: 39.8645345584121
                            Bytes_Sent_per_Connection: 11280.6666666667
                                 Sort_Scan_per_Second: 0
                                Sort_Range_per_Second: 0
                         Sort_Merge-Passes_per_Second: 0
                                 Sort_Rows_per_Second: 0
                               Select_Scan_per_Second: 0.017374915334099
                               Select_Scan_per_Select: 125
                              Select_Range_per_Second: 0
                              Select_Range_per_Select: 0
                          Select_Full-Join_per_Second: 0
                          Select_Full-Join_per_Select: 0
                        Select_Range-Check_per_Second: 0
                        Select_Range-Check_per_Select: 0
                    Select_Full-Range-Join_per_Second: 0
                    Select_Full-Range-Join_per_Select: 0
                              Slow_Queries_per_Second: 0
                              Slow_Threads_per_Second: 0
                                Query_Cache_Hit_Ratio: 0
             Query_Cache_Hits_per_Query_Cache_Inserts: NULL
             Query_Cache_Low-Memory_Prunes_per_Second: 0
                              Query_Cache_Utilization: NULL
                     Query_Cache_Memory_Fragmentation: NULL
                                Temporary_Table_Ratio: 27.0181219110379
                 Temporary_In-Memory_Table_per_Second: 0.0715021939511735
Query_Cache_Inserts_per_Query_Cache_Low-Memory_Prunes: NULL
                      Temporary_Disk_Table_per_Second: 0.0193185499307948
                           Temporary_Files_per_Second: 0.000147245045204229
                                     Read_Write_Ratio: 20.5217391304348
                         Average_Table_Scan_Hit_Ratio: 100
                      s_COM_ADMIN_COMMANDS_per_Second: 0.00568365874488323
                            s_COM_ALTER_DB_per_Second: 0
                         s_COM_ALTER_EVENT_per_Second: 8.83470271225373e-005
                         s_COM_ALTER_TABLE_per_Second: 0
                             s_COM_ANALYZE_per_Second: 0
                               s_COM_BEGIN_per_Second: 0
                      s_COM_CALL_PROCEDURE_per_Second: 0.000795123244102836
                           s_COM_CHANGE_DB_per_Second: 0.00368112613010572
                       s_COM_CHANGE_MASTER_per_Second: 0
                               s_COM_CHECK_per_Second: 0
                            s_COM_CHECKSUM_per_Second: 0
                              s_COM_COMMIT_per_Second: 0
                        s_COM_CREATE_EVENT_per_Second: 0.00020614306328592
                           s_COM_CREATE_DB_per_Second: 0.000353388108490149
                     s_COM_CREATE_FUNCTION_per_Second: 0
                        s_COM_CREATE_INDEX_per_Second: 0
                        s_COM_CREATE_TABLE_per_Second: 0.000353388108490149
                         s_COM_CREATE_USER_per_Second: 0
                         s_COM_DEALLOC_SQL_per_Second: 0.00150189946108313
                              s_COM_DELETE_per_Second: 0
                        s_COM_DELETE_MULTI_per_Second: 0
                                  s_COM_DO_per_Second: 0
                             s_COM_DROP_DB_per_Second: 0.000265041081367612
                          s_COM_DROP_EVENT_per_Second: 0.000176694054245075
                       s_COM_DROP_FUNCTION_per_Second: 0.00106016432547045
                          s_COM_DROP_INDEX_per_Second: 0
                          s_COM_DROP_TABLE_per_Second: 0.000294490090408458
                           s_COM_DROP_USER_per_Second: 0
                         s_COM_EXECUTE_SQL_per_Second: 0.00150189946108313
                               s_COM_FLUSH_per_Second: 0
                               s_COM_GRANT_per_Second: 0
                            s_COM_HA_CLOSE_per_Second: 0
                             s_COM_HA_OPEN_per_Second: 0
                             s_COM_HA_READ_per_Second: 0
                                s_COM_HELP_per_Second: 0
                              s_COM_INSERT_per_Second: 0.000677327207939453
                       s_COM_INSERT_SELECT_per_Second: 0
                                s_COM_KILL_per_Second: 0
                                s_COM_LOAD_per_Second: 0
                         s_COM_LOCK_TABLES_per_Second: 0
                            s_COM_OPTIMIZE_per_Second: 0
                        s_COM_PRELOAD_KEYS_per_Second: 0
                         s_COM_PREPARE_SQL_per_Second: 0.00150189946108313
                               s_COM_PURGE_per_Second: 0
                   s_COM_PURGE_BEFORE_DATE_per_Second: 0
                        s_COM_RENAME_TABLE_per_Second: 0
                              s_COM_REPAIR_per_Second: 0
                             s_COM_REPLACE_per_Second: 0
                      s_COM_REPLACE_SELECT_per_Second: 0
                               s_COM_RESET_per_Second: 0
                              s_COM_REVOKE_per_Second: 0
                          s_COM_REVOKE_ALL_per_Second: 0
                            s_COM_ROLLBACK_per_Second: 0
                           s_COM_SAVEPOINT_per_Second: 0
                              s_COM_SELECT_per_Second: 0.0138999322672792
                          s_COM_SET_OPTION_per_Second: 0.00859911063992697
                  s_COM_SHOW_BINLOG_EVENTS_per_Second: 0
                        s_COM_SHOW_BINLOGS_per_Second: 0
                       s_COM_SHOW_CHARSETS_per_Second: 8.83470271225373e-005
                     s_COM_SHOW_COLLATIONS_per_Second: 0
                   s_COM_SHOW_COLUMN_TYPES_per_Second: 0
                      s_COM_SHOW_CREATE_DB_per_Second: 0
                   s_COM_SHOW_CREATE_EVENT_per_Second: 0.00020614306328592
                   s_COM_SHOW_CREATE_TABLE_per_Second: 5.88980180816916e-005
                      s_COM_SHOW_DATABASES_per_Second: 0.000235592072326766
                    s_COM_SHOW_ENGINE_LOGS_per_Second: 0
                  s_COM_SHOW_ENGINE_STATUS_per_Second: 0
                         s_COM_SHOW_ERRORS_per_Second: 0
                         s_COM_SHOW_EVENTS_per_Second: 0.000353388108490149
                         s_COM_SHOW_FIELDS_per_Second: 2.94490090408458e-005
                         s_COM_SHOW_GRANTS_per_Second: 0
                           s_COM_SHOW_KEYS_per_Second: 2.94490090408458e-005
                  s_COM_SHOW_MASTER_STATUS_per_Second: 0
                     s_COM_SHOW_NEW_MASTER_per_Second: 0
                    s_COM_SHOW_OPEN_TABLES_per_Second: 0
                        s_COM_SHOW_PLUGINS_per_Second: 0
                   s_COM_SHOW_ENGINE_MUTEX_per_Second: 0
                     s_COM_SHOW_PRIVILEGES_per_Second: 0.000176694054245075
                    s_COM_SHOW_PROCESSLIST_per_Second: 0
                    s_COM_SHOW_SLAVE_HOSTS_per_Second: 0
                   s_COM_SHOW_SLAVE_STATUS_per_Second: 0
                         s_COM_SHOW_STATUS_per_Second: 0
                s_COM_SHOW_STORAGE_ENGINES_per_Second: 0
                         s_COM_SHOW_TABLES_per_Second: 0.000294490090408458
                       s_COM_SHOW_TRIGGERS_per_Second: 0
                      s_COM_SHOW_VARIABLES_per_Second: 0.000265041081367612
                       s_COM_SHOW_WARNINGS_per_Second: 0
                         s_COM_SLAVE_START_per_Second: 0
                          s_COM_SLAVE_STOP_per_Second: 0
                          s_COM_STMT_CLOSE_per_Second: 0.00126630738875637
                          s_COM_STMT_FETCH_per_Second: 0
                          s_COM_STMT_RESET_per_Second: 0
                            s_COM_TRUNCATE_per_Second: 0.000382837117530995
                              s_COM_UPDATE_per_Second: 0
                       s_COM_UNLOCK_TABLES_per_Second: 0
                        s_COM_UPDATE_MULTI_per_Second: 0
                           s_COM_XA_COMMIT_per_Second: 0
                              s_COM_XA_END_per_Second: 0
                          s_COM_XA_PREPARE_per_Second: 0
                          s_COM_XA_RECOVER_per_Second: 0
                         s_COM_XA_ROLLBACK_per_Second: 0
                            s_COM_XA_START_per_Second: 0
                        s_COM_STMT_PREPARE_per_Second: 0.00150189946108313
                 s_COM_STMT_SEND_LONG_DATA_per_Second: 0
                        s_COM_STMT_EXECUTE_per_Second: 0.00126630738875637
                            s_COM_ROLLBACK_per_Second: 0
                       s_COM_UNLOCK_TABLES_per_Second: 0
                          s_FLUSH_COMMANDS_per_Second: 2.94490090408458e-005
                     s_PREPARED_STMT_COUNT_per_Second: 0
                       Maximum_Connection_Utilization: 4.63576158940397
                               Connection_Utilization: 4.63576158940397
                       Connection_Attempts_per_Second: 0.00353388108490149
                                   Threads_per_Second: 0.00306269694024796
                               Thread_Cache_Miss_Rate: 86.6666666666667
                              Thread_Cache_Efficiency: 13.3333333333333
                             Thread_Cache_Utilization: NULL
                             Opened_Tables_per_Second: 0.0533616043820125
                             Tables_Cache_Utilization: 100
                                 XACommits_per_Second: 0
                             XARollbacks_per_XACommit: NULL
                          Average_XA_Transaction_Size: NULL
                                 Inserts_per_XACommit: NULL
                                   Commits_per_Second: 0
                                 Rollbacks_per_Commit: NULL
                             Average_Transaction_Size: NULL
                                   Inserts_per_Commit: NULL
                                  Question_per_Second: 3.33353947639662
                                   Queries_per_Second: 0.0145772594752187
                                   Selects_per_Second: 0.0138999322672792
                                   Inserts_per_Second: 0.000677327207939453
                                   Updates_per_Second: 0
                                   Deletes_per_Second: 0
                                  Replaces_per_Second: 0
                              Question_per_Connection: 943.308333333333
                               Queries_per_Connection: 4.125
                               Selects_per_Connection: 3.93333333333333
                               Inserts_per_Connection: 0.191666666666667
                               Updates_per_Connection: 0
                               Deletes_per_Connection: 0
                              Replaces_per_Connection: 0
                              Binlog_Cache_Contention: NULL