Many applications execute unncessary admin commands and unncessary ping() calls The extra ping()-ing adds another round-trip over the network, which can really add up for the application, though it doesn't really hurt the server's performance.The effect is basically longer page load latency (assuming it's a web app)8mThe total number of 'admin' (non-query) commands I Iv\ function () { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Com_prepare_sql) == "undefined") return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_prepare_sql, MONyog.MySQL.GlobalStatus.Uptime); } +Com_dealloc_sql2 aThe total number of 'Prepare SQL' commands+Com_prepare_sql Executing 'USE <db>' before each query is common mistake in many applications. It is wiser to just fully qualify your queries: instead of
\nuse foo;\nselect * from tbl;\nyou should write
\nselect * from foo.tbl;\nand save a query0]The total number of 'Change DB' commandswifunction() { // warn if more than 40% of all Qeustions and Admin commands if(SafeDiv0(MONyog.MySQL.GlobalStatus.Com_change_db, MONyog.MySQL.GlobalStatus.Questions) > .4) return "Warning"; else return "None"; } U< 1 ]function() { // warn if more than 40% of all Qeustions and Admin commands if((MONyog.MySQL.GlobalStatus.Com_prepare_sql > 0) && SafeDiv0(MONyog.MySQL.GlobalStatus.Com_dealloc_sql,MONyog.MySQL.GlobalStatus.Com_prepare_sql) < .6) return "Warning"; else return "None"; }")Com_stmt_close`!;If this is much lower than Com_prepare_sql, then prepared statements are being 'leaked'2 aThe total number of 'Dealloc SQL' commands function () { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Com_dealloc_sql) == "undefined") return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_dealloc_sql, MONyog.MySQL.GlobalStatus.Uptime); } f O5 f K(function () { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_lock_tables, MONyog.MySQL.GlobalStatus.Uptime); }'+Com_lock_tables`&;If this is much lower than Com_prepare_sql, then prepared statements are being 'leaked'~%wfunction() { // warn if more than 40% of all Qeustions and Admin commands if(SafeDiv0(MONyog.MySQL.GlobalStatus.Com_stmt_close/MONyog.MySQL.GlobalStatus.Com_prepare_sql) < .6) return "Warning"; else return "None"; }8$mThe total number of 'Close PREPARE SQL' commands #function () { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Com_stmt_close) == "undefined") return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_stmt_close, MONyog.MySQL.GlobalStatus.Uptime); } ) +1function () { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Com_show_create_table) == "undefined") return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_show_create_table, MONyog.MySQL.GlobalStatus.Uptime); } *7Com_show_create_table )9The total number of LOCK TABLE commands. If there are only InnoDB tables, Com_lock_tables should not increase on an ongoing basis. There are certain programs like mysqldump that may use them anyway, but it's a typical mistake to use lock tables with transactional tables k T:k K.function () { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return NumCounterWithSeconds(MONyog.MySQL.GlobalStatus.Com_show_fields, MONyog.MySQL.GlobalStatus.Uptime); }-+Com_show_fields(,KSome 'frameworks' want to run SHOW CREATE TABLE or SHOW COLUMNS or DESCRIBE before each query. This is a bad idea. If this value increases nearly as fast as Questions or nearly as fast as Connections (which would indicate one per new connection from the app) then it's a problem. The application ought to cache its schema permanently instead of inspecting every time it queries W/)Some 'frameworks' want to run SHOW FIELDS or SHOW COLUMNS before each query. This is a bad idea. If this value increases nearly as fast as Questions or nearly as fast as Connections (which would indicate one per new connection from the app) then it's a problem. The application ought to cache its schema permanently instead of inspecting every time it queries.
A high value for Com_show_fields is the signature of Ruby on Rails!(0M
MONyog.MySQL.GlobalStatus.Uptime{1q
Com_create_db > || Com_alter_db > 0 || Com_drop_db > 0 || Com_create_function > 0 || Com_drop_function > 0 || Com_create_index > 0 || Com_drop_index > 0 || Com_alter_table > 0 || Com_create_table > 0 || Com_drop_table > 0 || Com_drop_user > 0
q3]
function()
{
if(this.Value == "Yes")
return "Critical";
else
return "Noi2M
function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(MONyog.MySQL.GlobalStatus.Com_create_db > 0 ||
MONyog.MySQL.GlobalStatus.Com_alter_db > 0 ||
MONyog.MySQL.GlobalStatus.Com_drop_db > 0 ||
MONyog.MySQL.GlobalStatus.Com_create_function > 0 ||
MONyog.MySQL.GlobalStatus.Com_drop_function > 0 ||
MONyog.MySQL.GlobalStatus.Com_create_index > 0 ||
MONyog.MySQL.GlobalStatus.Com_drop_index > 0 ||
MONyog.MySQL.GlobalStatus.Com_alter_table > 0 ||
MONyog.MySQL.GlobalStatus.Com_create_table > 0 ||
MONyog.MySQL.GlobalStatus.Com_drop_table > 0 ||
MONyog.MySQL.GlobalStatus.Com_drop_user > 0)
return "Yes";
return "No";
}q3]
function()
{
if(this.Value == "Yes")
return "Critical";
else
return "None";
}
M M@ o4Y
While it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.5)
Dump the binary log for that timeframe and determine who made the changes, what changes actually took place and the reasons for those changes.6% Com_alter_db)7M function()
{
if(typeof(MONyog.MySQL.GlobalStatus.Com_alter_db) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_alter_db;
}(8M MONyog.MySQL.GlobalStatus.Uptimeo9Y While it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.:'
Com_create_db(;M
MONyog.MySQL.GlobalStatus.Uptime
E *5@KValw '2=HS^it$/:EP[fq|
j lyc8 jE' @function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_db;
}(CM MONyog.MySQL.GlobalStatus.UptimeB3Com_create_functionoAYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.(?M MONyog.MySQL.GlobalStatus.Uptime>#Com_drop_dbo=Y
While it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.<
function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_create_db;
}
[ fsW, [9 H#function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_function;
}(KM MONyog.MySQL.GlobalStatus.UptimeJ-Com_create_indexoIYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.(GM MONyog.MySQL.GlobalStatus.UptimeF/Com_drop_functionoEYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.D'function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_create_function;
}
e iv]2 e?% P function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_index;
}(SM MONyog.MySQL.GlobalStatus.UptimeR+Com_alter_tableoQYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.(OM MONyog.MySQL.GlobalStatus.UptimeN)Com_drop_indexoMYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.L!function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_create_index;
}
jw\1 > X!function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_create_table;
}oYYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.(WM MONyog.MySQL.GlobalStatus.UptimeV-Com_create_tableoUYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.T function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_alter_table;
}
{b k function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Delayed_errors;
}\m3The number of rows written with INSERT DELAYED for which some error occurredilMfunction()
{
if(this.Value > 1)
return "Warning";
else
return "None";
}j)Delayed_errorsWi)This value denotes the memory requirement by MySQL if max. users are connected'hIfunction ()
{
if(!MONyog.MySQL.GlobalVariables.innodb_buffer_pool_size)
MONyog.MySQL.GlobalVariables.innodb_buffer_pool_size = 0;
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MemoryRequired(MONyog.MySQL.GlobalVariables.max_connections);
}
N *5@KValw '2=HS^it$/:EP[fq|
Vo'function ()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.tx_isolation) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.tx_isolation;
}grIfunction()
{
if(this.Value != "REPEATABLE-READ" && this.Value != "READ-COMMITTED"
&& typeof(MONyog.MySQL.GlobalVariables.tx_isolation) != "undefined")
return "Warning";
else
return "None";
}NqThis variable controls how transactions are isolated from one anotherp%tx_isolationsnaIf this is nonzero it needs investigation. This means that statements are causing errors that the application can't catch or observe. Check your INSERT DELAYED statements and be sure they aren't causing duplicate key or other errors
v v bv?function()
{
if(this.Value == "Yes")
return "Warning";
return "None";
}tucfunction x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var value = MONyog.MySQL.GlobalVariables.lower_case_table_names;
if(value == 1)
return "No";
else
return "Yes";
}#tClower_case_table_names != 1sSetting it to anything but REPEATABLE-READ or READ-COMMITTED is generally not necessary; in fact the default setting of REPEATABLE-READ is usually the best idea. Other settings may cause inconsistent behavior and generally do not improve performance
e V j{Ofunction()
{
if(parseInt(this.Value, 10) > 1)
return "Warning";
return "None";
}[z1function x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(MONyog.MySQL.GlobalVariables.myisam_repair_threads > 1)
return "Yes";
else
return "No";
} e !y?myisam_repair_threads > 1exESet lower_case_table_names=1 in your configuration file and restart your MySQL server&wGCase sensitivity of identifiers is determined by the operating system where MySQL is running. This means database, table, and trigger names are not case sensitive in Windows, but are case sensitive in Unix. So if you are migrating data from one platform to the other, you may encounter problems.
The lower_case_table_names system variable also affects how the server handles identifier case sensitivity
X G X a =function()
{
if(this.Value == "Yes")
return "Warning";
return "None";
}@{MySQL normally uses the write() system call, and allows the operating system to schedule writes to the disk. However, by setting flush=ON in your configuration file, you can force MySQL to flush data to the disk after the execution of each SQL statement. This would severely impact the performanceH function x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(MONyog.MySQL.GlobalVariables.flush != "OFF")
return "Yes";
else
return "No";
}
~flushh}KEnsure that you set myisam_repair_threads=1 in your configuration file and restart MySQL5|eYou can choose to use multiple threads when repairing MyISAM tables to improve performance. The side effect, however, is that it can also lead to table and index corruption
]BU\ U% function()
{
// should not be more than 2MB
if(this.Value != "(n/a)" && ToInt(MONyog.MySQL.GlobalVariables.sort_buffer_size) > 2097152)
return "Warning";
else
return "None";
}ue This buffer is used by ORDER BY, GROUP_BY, DISTINCT. You can improve the performance of 'ORDER BY' by increasing this value. The recommended value is 1MB. This buffer is allocated per client, so be careful about not making it very largeiM function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.sort_buffer_size) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.sort_buffer_size);
}- sort_buffer_size 9From a performance standpoint, you should ensure that this feature is disabled. Set flush=OFF in your configuration file and then restart MySQL
\ 7# \
+ function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Sort_scan);
}(M MONyog.MySQL.GlobalStatus.Uptime Sort_scan '
The number of sorts that were performed by queries that used range accesses. This variable gets increased if a range-access query does a sort(
M
MONyog.MySQL.GlobalStatus.Uptime -
function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Sort_range);
}!
Sort_rangeN This variable should not be set larger than 2M for most purposes, and then only with benchmarks to prove that it helps. Making it too large can actually slow down queries. Instead of setting it to a large value globally, leave the global setting small, and set the local value larger for queries you know will benefit from it
vV # function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Sort_merge_passes;
} 7 NumCounterWithSeconds(M MONyog.MySQL.GlobalStatus.Uptime/ Sort_merge_passes@} The number of sorts that were done by scanning the table
t C! t )M
function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.myisam_sort_buffer_size);
} ;
myisam_sort_buffer_size9m MySQL first tries to sort all rows in memory. If the local @@sort_buffer_size is not large enough, a couple of temporary files will be created, one for storing the rows and second for storing the sorted rows. If the value is high, you should increase the local value of @@sort_buffer_size.
It is NOT wise to increase sort_buffer_size based on this. The default sort_buffer_size should stay small, and for queries that need to do a large sort, the *local* value of @@sort_buffer_size should be increased just before the query is run (and then set back to the original value). The best sort_buffer_size is different for each query, and should be determined by benchmarking
r_function ()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.read_rnd_buffer_size) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.read_rnd_buffer_size);
}'Slave_running+QYou can improve 'ORDER BY' performance by setting this variable to a large value. However, since it is allocated per client, be careful with extremely high values 5read_rnd_buffer_size"?
This buffer is created on demand when MySQL needs to rebuild the index after REPAIR, OPTIMIZE, ALTER table statements. Since this buffer is allocated only on demand, you can set it to a higher value. However, since it is allocated per client, be careful with extremely high values
_ == n Wfunction()
{
if(this.Value == "No")
return "Critical";
else
return "None";
}- UThis value determines whether this server is running as a Replication Slave connected to a master.
(Note: You have to configure MONyog to enable this feature.) g Yes ' Ifunction()
{
if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave))
{
if(IsSlaveRunning(MONyog.MySQL.GlobalStatus.Slave_running))
return "Yes";
else
return "No";
}
else
{
return "(not configured)";
}
}
d d #-function()
{
if(this.Value == "ON" || this.Value == 1 || this.Value == "(n/a)")
return "None";
else
return "Critical";
} "{function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false)
return "(n/a)";
else
return MONyog.MySQL.GlobalVariables.read_only;
}! read_onlyc AIf this server is no longer running as a SLAVE connected to the MASTER, use SHOW SLAVE STATUS and try to determine whether there was an error or the slave was stopped manually. Check the last 100 lines of the slave's error log to see if that gives a clue.
Look at Last Error Number and Last Error Message for specific error information.
Determine if and why an explicit STOP SLAVE was issued. Try restarting the slave with START SLAVE ' _ _ p)[function() { if(this.Value == "No") return "Critical"; else return "None"; }a'=function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Slave_IO_Running) return "(n/a)"; else return MONyog.MySQL.Slave.Slave_IO_Running; }m*UThis value indicates if the slave I/O thread was started and is successfully connected to the master g (Yes&-Slave_IO_RunningD%This variable may help prevent some unsafe writes from occurring on slaves, but only if users are not needlessly granted the SUPER privilege. It is a good idea to set slaves as read_only~$wIf it is enabled, the server allows no updates except from SUPER users from updates performed by slave threads . . S-!function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Slave_IO_State) return "(n/a)"; return (MONyog.MySQL.Slave.Slave_IO_State); }r._This value is same as the State Field of SHOW PROCESSLIST of slave I/O Thread. This gives you more details on what the thread is doing - whether its trying to connect to the master, waiting for events, reconnecting to the master, etc,)Slave_IO_Stateh+KIf the Slave I/O thread is no longer running on this server, check the slave's error log for messages that may indicate why this thread may have stopped. Check the last 100 lines of the error log to see if that gives a clue. Usually the problem is a wrong position given in the CHANGE MASTER TO command. Often the easiest fix is to set up the slave again just as if it was a new slave.
The last error can be determined by Last Error Number and Last Error _ 5l _^PB _69 function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Seconds_Behind_Master) return "(n/a)"; return MONyog.MySQL.Slave.Seconds_Behind_Master; } 9 600 8 450 7 Yes 57 Seconds_Behind_Master%4ETry to find out why the slave SQL thread is no longer running on this server.
The last error can be determined by 'Last Error Number' and 'Last Error'
R3 This value indicates whether the slave SQL thread is running successfully 2Yesn1Wfunction() { if(this.Value == "No") return "Critical"; else return "None"; }W0)function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Slave_SQL_Running) return "(n/a)"; return MONyog.MySQL.Slave.Slave_SQL_Running; }//Slave_SQL_Running N |qf[PE:/lw '2=HS @ K*V 5 a $^it ! " # % $ & ! ! ! ! ! ! ! ! ! 7 /S7 =/ slave_skip_errorsX<+ This value is useful only if the network between Slave and Master is fast. If the network is fast, investigate why this server is so far behind the master. Sometimes, other processes on the slave can impact the MySQL server. Try to improve the connectivity between the Master and Slave.This value can also get very high if the Slave was unable to to connect to MASTER at some point in time. In that case, the slave would eventually catch up with the master
c;A This value gives an indication of how much (if any) is the slave lagging behind the master c:A function() { return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true); } : :lW @- function() { // should not be less than 1MB if(this.Value != "(n/a)" && (MONyog.MySQL.GlobalVariables.slave_skip_errors == "ON" || MONyog.MySQL.GlobalVariables.slave_skip_errors == 1)) return "Warning"; else return "None"; }B! Last_Errno!A= It is generally not safe to set this. If you have slave errors, you should solve the problems instead of skipping errors. Skipping errors usually means the slave will end up with different data than the master, making it untrustworthy for queries and unusable for taking backupsI? This variable controls which errors the slave thread should skip >9 function() { if(typeof(MONyog.MySQL.GlobalVariables.slave_skip_errors) == "undefined") return "(n/a)"; if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false) return "(n/a)"; return MONyog.MySQL.GlobalVariables.slave_skip_errors; } 3 BIfunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false) return "(n/a)"; return TimeUnits(MONyog.MySQL.GlobalVariables.slave_net_timeout); }H/slave_net_timeoutLG The last Error Message returned by the most recently executed queryKF function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Last_Error) return "(n/a)"; return (MONyog.MySQL.Slave.Last_Error); }E! Last_ErrorKD The last Error Number returned by the most recently executed queryIC function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Last_Errno) return "(n/a)"; return MONyog.MySQL.Slave.Last_Errno; } yJmfunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false) return "None"; else if(MONyog.MySQL.GlobalVariables.slave_net_timeout >= 3600) return "Warning"; else return "None"; }"MASlave_retried_transactions MLWhen the network connection between a master and slave database is interrupted in a way that neither side can detect, the slave will wait slave_net_timeout seconds before trying to reconnect to the master. The default value of 3600 seconds (1 hour) is not practical. A value of less than 60 seconds would be optimum.kKQThe number of seconds to wait for more data from a master/slave connection before aborting the read. This timeout applies only to TCP/IP connections, not to connections made via Unix socket files, named pipes, or shared memory r oQYfunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.GlobalStatus.Slave_open_temp_tables) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Slave_open_temp_tables; }S#Master_HostTR#The number of temporary tables that the Slave SQL thread currently has open P9Slave_open_temp_tablesoOYThe total number of times since startup that the replication slave SQL thread has retried transactionswNifunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.GlobalStatus.Slave_retried_transactions) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Slave_retried_transactions; } _ 1 _L6 SZ!function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Master_Log_File) return "(n/a)"; return MONyog.MySQL.Slave.Master_Log_File; }\#Master_User^[7The name of the master binary log file from which the I/O thread is currently readingY+Master_Log_File@X}The MySQL Port of the Replication Master for this serverKWfunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Master_Port) return "(n/a)"; return MONyog.MySQL.Slave.Master_Port; }V#Master_Port?U{The Host Name of the Replication Master for this serverKTfunction() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Master_Host) return "(n/a)"; return MONyog.MySQL.Slave.Master_Host; } . S^itH=2'/$ :EP[fq| ! " # $ % & ( ) ' * + , - . / 1 2 4 3 0 5 6 7 9 8 : < 1r _c9function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Relay_Master_Log_File) return "(n/a)"; return MONyog.MySQL.Slave.Relay_Master_Log_File; } b7Relay_Master_Log_FileYa-The position upto which the I/O thread has read in the current master binary log[`1function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Read_Master_Log_Pos) return "(n/a)"; return MONyog.MySQL.Slave.Read_Master_Log_Pos; }_3Read_Master_Log_PosC^The Username used for connecting to the Replication MasterK]function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Master_User) return "(n/a)"; return MONyog.MySQL.Slave.Master_User; } t# Qi function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Relay_Log_File) return "(n/a)"; return MONyog.MySQL.Slave.Relay_Log_File; }k' Relay_Log_PosdjC The name of the Relay Log File from which the SQL thread is currently reading and executingh) Relay_Log_FileogYThe position in the master binary log file containing the most recent event executed by the SQL thread[f1function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Exec_Master_Log_Pos) return "(n/a)"; return MONyog.MySQL.Slave.Exec_Master_Log_Pos; }e3Exec_Master_Log_PoskdQThe name of the master binary log file containing the most recent event executed by the SQL thread ,zO/ tfunction() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Questions; } s7 NumCounterWithSeconds(rM MONyog.MySQL.GlobalStatus.Uptimeq Questions:pq The total combined size of all existing relay logsbo? function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Relay_Log_Space) return "(n/a)"; return NumCounter(MONyog.MySQL.Slave.Relay_Log_Space, 1); }n+ Relay_Log_Space^m7 The position upto which the SQL thread has read and executed in the current relay logPl function() { if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Relay_Log_Pos) return "(n/a)"; return MONyog.MySQL.Slave.Relay_Log_Pos; } a _J!k=( a Cfunction () { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return (ToInt(MONyog.MySQL.GlobalStatus.Com_insert)+ToInt(MONyog.MySQL.GlobalStatus.Com_replace)); }~! Statements+}SMONyog.MySQL.GlobalStatus.Questions(|M MONyog.MySQL.GlobalStatus.Uptime.{Y(Com_insert + Com_replace) / QuestionsWz)The total number of SELECT statements that all clients have sent to the server%yEfunction() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; if(!MONyog.MySQL.GlobalStatus.Qcache_hits) MONyog.MySQL.GlobalStatus.Qcache_hits = 0; return (ToInt(MONyog.MySQL.GlobalStatus.Com_select)+ToInt(MONyog.MySQL.GlobalStatus.Qcache_hits)); }x! Statements(wM MONyog.MySQL.GlobalStatus.Uptime v=Com_select + Qcache_hitsPuThe total number of statements that all clients have sent to the server i Z,}# i: 'function () { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return (ToInt(MONyog.MySQL.GlobalStatus.Com_delete)); }! NumCounter ; max_prepared_stmt_countW )The total number of DELETE statements that all clients have sent to the server ! Statements+ SMONyog.MySQL.GlobalStatus.Questions(M MONyog.MySQL.GlobalStatus.Uptime 9Com_delete / QuestionsW)The total number of UPDATE statements that all clients have sent to the server'function () { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return (ToInt(MONyog.MySQL.GlobalStatus.Com_update)); }! Statements+SMONyog.MySQL.GlobalStatus.Questions(M MONyog.MySQL.GlobalStatus.Uptime 9Com_update / QuestionsW )The total number of INSERT statements that all clients have sent to the server 2 S^it$/:EP[fq|H=2 ' 4 3 2 1 0 x y z | { } ~ b4 function x() { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.max_prepared_stmt_count) == "undefined") return "(n/a)"; var count = MONyog.MySQL.GlobalVariables.max_prepared_stmt_count; return count; }! Statements+SMONyog.MySQL.GlobalStatus.Questions(M MONyog.MySQL.GlobalStatus.Uptime~w Ensure that an appropriate value for max_prepared_stmt_count is set, depending on your application requirement{q This variable limits the total number of prepared statements in the server. Although using prepared statements in applications that execute the same query several times increases their performance, prepared statements consume memory in the MySQL server until they are closed.If a restore of your backup is required, Binary logs enable you to roll-forward changes made after the full backup
90obinlog_do_db != \"\" || binlog_ignore_db != \"\" N N b2?function() { if(this.Value == "Yes") return "Warning"; return "None"; };1qfunction x() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; var value = MONyog.MySQL.GlobalVariables.log_bin; if((value != "OFF") && (MONyog.MySQL.Master.Binlog_Do_DB != "" || MONyog.MySQL.Master.Binlog_Ignore_DB != "")) return "Yes"; else return "No"; }-3UAll changes (DDL, DML and security) to a database are recorded by MySQL in the binary log. This enables point-in-time recovery, thus preventing catastrophic data loss due to outages.The slow query log can be used to identify queries that take long time to complete. Enabling the slow query log is highly recommended as it allows you to record unoptimized queries ; _+long_query_time `9function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return TimeUnits(MONyog.MySQL.GlobalVariables.long_query_time); }^a7If a query takes longer time than thisi^MEnable the slow query log. Refer to http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html for more information on the slow query log._+long_query_time `9function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return TimeUnits(MONyog.MySQL.GlobalVariables.long_query_time); }^a7If a query takes longer time than this value, it is recorded into the slow query log. Nzz qh]function() { if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.log_queries_not_using_indexes) == "undefined") return "(n/a)"; var val = MONyog.MySQL.GlobalVariables.log_queries_not_using_indexes; if(val == "ON") return "Yes"; else if(val == "OFF") return "No"; else return val; }8imThis option logs queries that don't use indexes.sjaYou should enable this option whenever you want to indentify queries that are not using indexes.
If you keep this option always enabled, the slow query log can get potentially flooded with queries that read small static tables (for example country_master). You should enable this option for a particular task and disable it again when the task gets completed.
Q Q_%A an=
function()
{
if(this.Value == "Yes")
return "Warning";
return "None";
} t7 NumCounterWithSeconds(sM MONyog.MySQL.GlobalStatus.UptimeZr/The total number of successful and failed connection attempts to the MySQL server
qfun k
logBl
function x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(MONyog.MySQL.GlobalVariables.log == "ON")
return "Yes";
else
return "No";
}sma
The general log records all requests sent by multiple clients to MySQL. This includes all SQL statements executed on the server along with clients' connection and disconnection information.
It is not recommended that the general log be enabled on a production server, as it adds an overhead to the server, and grows rapidly consuming large amount disk space.an=
function()
{
if(this.Value == "Yes")
return "Warning";
return "None";
}
|a G (}M MONyog.MySQL.GlobalStatus.Uptime|yThe total number of failed connection attempts.
Excess aborted connection attempts indicates that
- Client doesn't have privileges to connect to a database
- A client uses an incorrect password
- It takes more than connect_timeout seconds to get a connect packet
If the number is high, it might indicate that someone is trying to hack your server!{!function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Aborted_connects;
}z-Aborted_connects y7 NumCounterWithSeconds(xM MONyog.MySQL.GlobalStatus.UptimeOwThe total number of successful connection attempts to the MySQL server:vofunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Connections - MONyog.MySQL.GlobalStatus.Aborted_connects;
}&uIConnections - Aborted_connects
y y
30
25'High number of Connection attempts failed.
If this number is high then it seems that your clients don't have privileges to connect or they are using an incorrect password to connect. A high number might also indicate that someone is trying to hack your server!X+function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return (SafeDiv0(MONyog.MySQL.GlobalStatus.Aborted_connects,MONyog.MySQL.GlobalStatus.Connections)*100).toFixed(2)+ "%";
}& IAborted_connects / Connections*O1. Try to isolate users/applications that are using an incorrect password or trying to connect from unauthorized hosts.
2. A client will be disallowed to connect if it takes more than connect_timeout seconds to connect.
3. Switch on the General Query Log to log connection attempts. ~7 NumCounterWithSeconds
~pE function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Aborted_clients;
} +Aborted_clientsx
k1. Try to isolate users/applications that are using an incorrect password or trying to connect from unauthorized hosts.
2. A client will be disallowed to connect if it takes more than connect_timeout seconds to connect.
3. Set the value of log_warnings system variable to 2. This will force the MySQL server to log further information about the error.( M MONyog.MySQL.GlobalStatus.Uptime
Yes Yes YescAfunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
W ~ p W )Bytes_received
1) Make sure that your applications close the MySQL connections gracefully. For example: mysql_real_connect() without a corresponding mysql_close() may result in aborted connections
2) Raise the value of wait_timeout and interactive_timeout 7 NumCounterWithSeconds(M MONyog.MySQL.GlobalStatus.Uptime3 aConnections that were established successfully but got aborted abruptly. This might happen if the client does not close the connection gracefully or the client had been sleeping more than wait_timeout seconds.
It would also be useful to check the values of wait_timeout and interactive_timeout and see if a timeout is really the problem. If there are processes in SHOW PROCESSLIST that have been idle (Sleep) for a time approaching one of these, you might advise either 1) close connections when you are done or 2) raise the value.
N "B\ function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Bytes_received;
}X+The values indicates the amount of incoming network traffic to the MySQL server(M MONyog.MySQL.GlobalStatus.Uptime 7 NumCounterWithSeconds! Bytes_sent
function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Bytes_sent;
}Z/ The values indicates the amount of outgoing network traffic from the MySQL server(M MONyog.MySQL.GlobalStatus.Uptime 7 NumCounterWithSeconds+max_connections ;function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.max_connections);
}
M *5@KValw '2=HS^it $/:EP[fq|
y
x
!
"
#
$
%
&
'
(
)
*
+
,
-
.
/
0
1
2
3
4
5
6
7
8
9
:
;
<
=
>
?
@
A
B
C
D
E
F
G
H
I
J
K
L
M
s
t
u
v
w
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
[
\
]
. . + QThe total number of concurrent connections allowed by the MySQL server. If you are getting 'Too many connections' error, it means that you have reached this limit {If you are getting 'Too many connections' error frequently, you should try increasing this value. At the same time please note that if this is a very large value, it would make the system spend more time on thread scheduling and context switching. /Threads_connected !function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Threads_connected;
}&!IThe number of open connections " Yes#! NumCounter $
Avg+%SThreads_connected / max_connections`&;function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return (SafeDiv0(MONyog.MySQL.GlobalStatus.Threads_connected,MONyog.MySQL.GlobalVariables.max_connections)*100).toFixed(2)+ "%";
}
e e BP^lzQk Z'/The number of currently open connections as percentage of max allowed connections
(75
)85c*Afunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
} +Yes , Yes -
Yes .
AvgS/!1. The global variable max_connections determines the maximum number of concurrent connections to MySQL. Make sure that you have a high value for this variable.
2. Design your application such that a MySQL connection is kept open for a very short period of time.
3. Try pooling connections or switch to persistent connections. This will reduce the number of active MySQL connections. For example try using mysql_pconnect() instead of mysql_connect()0+Threads_running1 function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Threads_running;
}
( ~pbT G ( :5Max_used_connections 9
If the database is overloaded you'll get an increased number of queries running. Occasional spikes are OK for very short period of time. Too many active threads indicate that:
1. MySQL is taking too much time to process you requests.
2. You are continuously retrieving/updating large datasets.
Make sure that queries are tuned to use indexes. Execute SHOW FULL PROCESSLIST of find queries that are getting locked continuously. Try isolating long running queries by enabling the slow query log. 8
Avg 7 Yes 6Yesc5Afunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
425
320~2wThe number of threads that are active at this point of time. Too many active threads indicates there is a severe load on the server and may be a sign of lock contention or unoptimized SQL queries. Too many active threads are bad for performance.
ZWJ= G
Avg F
Yes E
Yes D
YescCA
function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
B
85
A
75s@a
A high value indicates that you may run out of connections soon and new clients will be refused connectionc?A
function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return (SafeDiv0(MONyog.MySQL.GlobalStatus.Max_used_connections,MONyog.MySQL.GlobalVariables.max_connections)*100).toFixed(2)+ "%";
}.>Y
Max_used_connections / max_connections =
Avgd<CThe maximum number of concurrent connections that have been in use since server was started";?function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Max_used_connections);
}
A Btd A N
If there is more than this number of interrupted connections from a host, MySQL blocks that host from further connections.M+
function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalVariables.max_connect_errors;
}L1
max_connect_errorsPK The server will disconnect the client after this duration of inactivity J3 function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return TimeUnits(MONyog.MySQL.GlobalVariables.wait_timeout);
}I% wait_timeout :Ho
1. Make sure that you have a reasonable high value for max_connections.
2. Design your application such that a MySQL connection is kept open for a very short period of time.
M3 xRk The number of seconds that the MySQL server waits for a connect packet before responding with an error message. Q9 function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return TimeUnits(MONyog.MySQL.GlobalVariables.connect_timeout);
}P+ connect_timeout/OY
Unfortunately there is no way to query the server about the current value of this counter for each host. When the server rejects connections, for this reason it does so without warning. The default value is 10 and that is a rather low value particularly if connections from remote hosts are allowed. You can raise the number significantly. Optionally, you can execute FLUSH HOSTS as a daily or weekly scheduled job.
~d U;function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.key_buffer_size);
}T+key_buffer_size~Sw In situations of network congestion (either at the client or server), it's possible for an initial connection to take several seconds to complete. Increasing this value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.
Ideally, you should set the value to either 15 or 20 seconds.
H XW+function()
{
// should not be less than 32MB
if(this.Value != "(n/a)" &&
MONyog.MySQL.GlobalVariables.key_buffer_size < 33554432)
return "Warning";
else
return "None";
} Y5key_cache_block_sizepX[You should set it to at least 32M for machines that use no MyISAM tables, and larger for those that do.4VcThe MyISAM storage engine maintains a cache to keep the most frequently used index blocks in memory. Note that only index blocks are kept in this cache, for data blocks MyISAM uses the native filesystem cache. This should be 30-40% of available memory if you use MyISAM tables exclusively. If you dont use MyISAM tables set it to 32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
1 qZ]function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.key_cache_block_size) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.key_cache_block_size);
}E_(key_buffer_size / key_cache_block_size) - Key_blocks_unused-^WThe number of blocks in the key cache`];function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(MONyog.MySQL.GlobalVariables.key_cache_block_size && MONyog.MySQL.GlobalVariables.key_buffer_size)
return NumCounter(SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size, MONyog.MySQL.GlobalVariables.key_cache_block_size));
return "(n/a)";
}/\[key_buffer_size / key_cache_block_size
6[iThe size of the block buffers in the key cache
WbTF8 %cEfunction()
{
var value = PercentUsed();
if(MONyog.M <`sfunction()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalVariables.key_cache_block_size) == "undefined" ||
typeof(MONyog.MySQL.GlobalStatus.Key_blocks_unused) == "undefined")
{
return "(n/a)";
}
return NumCounter(SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size, MONyog.MySQL.GlobalVariables.key_cache_block_size) -
MONyog.MySQL.GlobalStatus.Key_blocks_unused);
}2aaThe number of used blocks in the key cacheKb1 - (Key_blocks_unused / (key_buffer_size / key_cache_block_size))%cEfunction()
{
var value = PercentUsed();
if(MONyog.MySQL.Custom.Available != 1 || value == "(n/a)")
return "(n/a)";
return value + "%"
}
K *5@KValw '2=HS^it$/:EP[fq|
^
_
`
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z
{
|
}
~
)E@` (dMThe fraction of key cache in use e Yes f
Yes g
Avgi/Key_read_requestsj!function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Key_read_requests;
}6kiThe number of read requests from the Key Cache(lM MONyog.MySQL.GlobalStatus.Uptime m7 NumCounterWithSeconds n3If the number of reads from the disk is high (more than 100/sec) then the key buffer may be too small. Increase the key_buffer_size variable
Vq'The number of physical reads of a 'key block' from disk, ie, key cache misseso Key_reads pfunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Key_reads;
} B{The number of physical reads of a 'key block' from diskPrfunction()
{
// warn if more than 100/sec
if(SafeDiv0(MONyog.MySQL.GlobalStatus.Key_reads,MONyog.MySQL.GlobalStatus.Uptime) > 100)
return "Warning";
else
return "None";
}(sM MONyog.MySQL.GlobalStatus.Uptime t7 NumCounterWithSeconds
a DuTuning cache size is not an exact science. The method which proved most useful over the years is the ubiquitous 'trial-and-error'. Here's what you should do:
Tuning cache size is not an exact science. The method which proved most useful over the yeD.Tuning cache size is not an exact science. The method which proved most useful over the years is the ubiquitous 'trial-and-error'. Here's what you should do:
Unlike the MyISAMj1OUnlike the MyISAM Key Cache, the InnoDB buffer pool caches both data and index pages. The cache hit ratio represents the proportion of data and index pages that are being read from cache in memory instead of from disk. Reading from the memory is many times faster than reading from the disk and therefore a DBA should strive to keep physical I/O to a minimum. When using InnoDB, most data access should occur in RAM, and therefore the InnoDB buffer cache hit ratio should be high. 295 385 $2@Ny u4efunction() { if(this.Value == "(n/a)") return "None"; if(CacheHitRatioInnodb(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_reads, MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_read_requests, MONyog.MySQL.Custom.Available) > 0) return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false); return "None"; } 5Yes 6 Yes 7 Yes(8M MONyog.MySQL.GlobalStatus.Uptime79iTuning cache size is not an easy task. Cache hit ratio is not a metric you should use to tune cache size. Instead check Cache misses and follow the advise there. Remember you need to wait till the server is running for a reasonable warm-up time before you get a meaningful cache hit rate.I: Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests n D D 'CK innodb_additional_mem_pool_size ( D function() { if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb()) return "(n/a)"; var innodb_additional_mem_pool_size = MONyog.MySQL.GlobalVariables.innodb_additional_mem_pool_size; if(typeof(innodb_additional_mem_pool_size) == "undefined" ) return "(n/a)"; if(ToInt(MONyog.MySQL.GlobalVariables.innodb_additional_mem_pool_size)>0) result = NumCounter(MONyog.MySQL.GlobalVariables.innodb_additional_mem_pool_size); else result = "(n/a)"; return result; }XE+ This pool is used to store InnoDB data dictionary and other internal structures{Fq function() { if(this.Value == "(n/a)") return "None"; // should not be more than 20MB if(MONyog.MySQL.GlobalVariables.innodb_additional_mem_pool_size > 20971520) return "Warning"; else return "None"; } * K1 function() { if(this.Value == "(n/a)") return "None"; // should not be more than 20MB if(SafeDiv0(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_wait_free, MONyog.MySQL.GlobalStatus.Uptime) > 1) return "Warning"; else return "None"; } ]J5 This total number of waits for pages to be flushed first from the InnoDB buffer pool ( bI? function() { if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb()) return "(n/a)"; var Innodb_buffer_pool_wait_free = MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_wait_free; if(typeof(Innodb_buffer_pool_wait_free) == "undefined") return "(n/a)"; return MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_wait_free; }$HE Innodb_buffer_pool_wait_freeFG Setting this variable to more than 20MB is usually a mistake. L@3 V+ Latest_deadlock U No T 11S] As an immediate solution, you can try and restart the victim transactions. Use the information on the deadlocks provided here to fine-tune your application to prevent them from occurring again. The following are general guidelines to avoid deadlocks:
Once the value of Threads_created stabilizes, edit my.ini/my.cnf to apply the change permanently.
Remember you need to wait till the server has been up for a reasonable warm-up time before you get a meaningful cache hit rate.A- slow_launch_time B; function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return TimeUnits(MONyog.MySQL.GlobalVariables.slow_launch_time); }cCA If creating a thread takes longer than this time, the thread creation is considered 'Slow'D3 Slow_launch_threadsE% function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Slow_launch_threads; } I V+ lV I K function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; if(!MONyog.MySQL.GlobalVariables.table_cache) MONyog.MySQL.GlobalVariables.table_cache = MONyog.MySQL.GlobalVariables.table_open_cache; if(MONyog.MySQL.GlobalVariables.table_cache) return NumCounter(MONyog.MySQL.GlobalVariables.table_cache); else if(MONyog.MySQL.GlobalVariables.table_open_cache) return (MONyog.MySQL.GlobalVariables.table_open_cache); else return "Undefined"; }J# table_cacheI1 Tune your queries to use indexes. Also, avoid queries returning large result sets.
You can also try increasing the value of thread_cache H7 NumCounterWithSeconds(GM MONyog.MySQL.GlobalStatus.Uptime&FG If too many threads are taking a long time to launch, it indicates a general system overload. General system overload happens because of non-optimal queries. h c h M- function() { // should not be 0 if(this.Value != "(n/a)" && this.Value == 0) return "Warning"; else return "None"; }T function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Opened_tables; }S' Opened_tables R Avg5Qg The number of tables that are currently open.P- function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return NumCounter(MONyog.MySQL.GlobalStatus.Open_tables); }O# Open_tables6Ng This variable controls the size of the table cache, in units of tables cached. You should set it large enough that Opened_tables does not increase very quickly, if possible.L- Each time MySQL accesses a table, it places it in the table cache. If your system accesses many tables, it is faster to have these in the cache. 1 PZ/ 1T 0Y[ If the number of table cache misses is high (typically, more than 10 per second), increase the value of the table_cache variable within your my.cnf/my.ini file.(]M MONyog.MySQL.GlobalStatus.UptimeA\ The number of times table locks was acquired immediately.[) function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Table_locks_immediate; } Z7 Table_locks_immediate X7 NumCounterWithSeconds(WM MONyog.MySQL.GlobalStatus.UptimerV_ function() { // warn if more than 40% of all Qeustions and Admin commands if(SafeDiv0(MONyog.MySQL.GlobalStatus.Opened_tables,MONyog.MySQL.GlobalStatus.Uptime) > 10) return "Warning"; else return "None"; },US The number of open table requests that were not served by the table cache. If the number is high or increases rapidly, you should increase the size of table cache. \ +F \ 1e] function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; var val = SafeDiv0(MONyog.MySQL.GlobalStatus.Table_locks_waited,ToInt(MONyog.MySQL.GlobalStatus.Table_locks_waited)+ToInt(MONyog.MySQL.GlobalStatus.Table_locks_immediate)) ; return val.toFixed(2) * 100 + "%"; } h 60 g 30f) Performance can be degraded if the percentage of table operations that have to wait for a lock is high compared to the overall number of locksJd Table_locks_waited / (Table_locks_waited + Table_locks_immediate) c7 NumCounterWithSeconds(bM MONyog.MySQL.GlobalStatus.UptimeJa The number of times wait was required before getting a table lock`# function() { if(MONyog.MySQL.Custom.Available != 1) return "(n/a)"; return MONyog.MySQL.GlobalStatus.Table_locks_waited; }_1 Table_locks_waited ^7 NumCounterWithSeconds \ ~pE \*4 Tn# If table lock contention is excessive, check that no sessions are using the LOCK TABLES statement unnecessarily. Make sure that queries are tuned to use indexes. Execute SHOW FULL PROCESSLIST of find queries that are getting locked continuously. Try isolating long running queries by enabling the slow query log.
If your tables use the MyISAM storage engine, they could be candidates for migration to a row-level locking storage engines like InnoDB.rp_function x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var type = MONyog.MySQL.GlobalVariables.have_query_cache;
if(type=="ON" || type=="YES")
return "Yes";
else
return "No";
}o-have_query_cache(mM
MONyog.MySQL.GlobalStatus.Uptime l
Yes k
Yes j
YesciA
function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
) D ) s-query_cache_typeFrTo make sure MySQL uses the query cache, there are a few variables you need to set in the configuration file (my.cnf/my.ini).
First, is the query_cache_type. There are three possible settings:
0 (for off, do not use)
1 (for on, cache queries)
2 (on demand, discussed more below)
To ensure it is always on, place: query_cache_type = 1 in the configuration file.
If your application frequently sends queries that are repetitive in nature, you can set the size of the query cache as high as 25% of the available RAM for optimal performance. Use the system variable query_cache_size to set the size of the query cache.nqWIn a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over.
{tqfunction x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var type = MONyog.MySQL.GlobalVariables.query_cache_type;
if(type == 0 || type=="OFF")
return "Off";
else if(type == 1 || type=="ON")
return "Always";
else if(type == 2 || type=="DEMAND")
return "Demand";
else
return "Unknown Type";
},uSThe following can be used to determine the type of query cache Temporary tables having TEXT or BLOBS are always placed in disk, so try to see if you can change TEXT or BLOBs to some other column types. Also, check that you're not being too generous with varchar(ZM MONyog.MySQL.GlobalStatus.Uptime
3
V9u @e}["Attempts", "Refused", "Terminated Abruptly", "Cached"]4gc[MONyog.MySQL.GlobalStatus.Connections,
MONyog.MySQL.GlobalStatus.Aborted_connects,
MONyog.MySQL.GlobalStatus.Aborted_clients,
MONyog.MySQL.GlobalStatus.Threads_cached]Lj["MyISAM Key Cache", "InnoDB Cache", "Query Cache", "Thread Cache"]i%Cache Missesh%RealTimeLine(fM MONyog.MySQL.GlobalStatus.Uptime B d#Connections
cOnOffb%RealTimeLine[a1[MONyog.MySQL.Custom.Available == 1?1:"", MONyog.MySQL.Custom.Available == 1?"":1]&`I["Available", "Not Available"]_1MySQL Availability&^GSynchronizing is the safest and well as the slowest choice because in the event of a crash you loose at most one statement or transaction from the binary log
"4 |jd^XRF@:4vpL " !
& V Pk[MONyog.MySQL.GlobalStatus.Key_reads,
MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_reads,
MONyog.MySQL.GlobalStatus.Qcache_inserts,
MONyog.MySQL.GlobalStatus.Threads_created]l%RealTimeLine m
Yesn!StatementsQ 4oe["SELECTs", "INSERTs", "UPDATEs", "DELETEs"](pM MONyog.MySQL.GlobalStatus.Uptime q5[ToInt(MONyog.MySQL.GlobalStatus.Com_select) + ToInt(MONyog.MySQL.GlobalStatus.Qcache_hits),
ToInt(MONyog.MySQL.GlobalStatus.Com_insert) + ToInt(MONyog.MySQL.GlobalStatus.Com_replace),
ToInt(MONyog.MySQL.GlobalStatus.Com_update),
ToInt(MONyog.MySQL.GlobalStatus.Com_delete)]
v u[CheckForSystem() && MONyog.System.Custom.Available == 1?1:"",
CheckForSystem() && MONyog.System.Custom.Available == 1?"":1].yY["Kernel", "User", "IO Wait", "Total"]r%RealTimeLines3System Availability&tI["Available", "Not Available"] ` v%RealTimeLine
wOnOffx CPU Usage / Hz [CheckForSystem()?GetCpuKernalPercentage(MONyog.System.Cpu.sys_cpu_user, MONyog.System.Cpu.sys_cpu_nice, MONyog.System.Cpu.sys_cpu_system,
MONyog.System.Cpu.sys_cpu_idle, MON 0{%RealTimeLine |
Yes } Yes ~ 1
No
X '09BKT]fox#,5>GPYbkt}
(1:CLU^gpy !
b ! !-/0 2
3 4 5
6789:;<=>@?ABCD E . F"#,1
function()
{
if(this.Value != "(n/a)" &&
typeof(MONyog.MySQL.InnodbStatus.Deadlock_detected) != "undefined" &&
MONyog.MySQL.InnodbStatus.Deadlock_detected == "Yes")
return "Critical";
else
return "None";
}]5Increase the binary log cache by changing the binlog_cache_size value. Try to decrease the number of transactions that go into a temporary file by tuning binlog_cache_size and observing this percentage.!?
Uptime_since_flush_status+key_buffer_sizeq]function x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var buffsize = MONyog.MySQL.GlobalVariables.key_buffer_size;
if(buffsize > 4294967296)
return "Yes";
else
return "No";
}
zYY/ #mAfunction()
{
if(MONyog.MySQL.Security.Is_mysql_db_accessible != "Yes")
return "(n/a)";
return MONyog.MySQL.Security.File_account_count;
}BnDo not grant global privilegesl1File_account_count#mAfunction()
{
if(MONyog.MySQL.Security.Is_mysql_db_accessible != "Yes")
return "(n/a)";
return MONyog.MySQL.Security.File_account_count;
}BnDo not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.ofunction()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}
G G U>Z BsDo not grant global pBsDo not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.tfunction()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}iuMIf there are users with unnecessary global privileges, you can remove them using the REVOKE statement. To revoke all global privileges, use the following SQL: REVOKE ALL ON *.* FROM "user_name"@" host_name"
'Off': The query cache has been disabled.
'Always': All statements are cached except those that begin with 'SELECT SQL_NO_CACHE'.
'Demand' Caching is done only for those statements that begin with 'SELECT SQL_CACHE'v-query_cache_sizexwkfunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var size = MONyog.MySQL.GlobalVariables.query_cache_size;
if(size > 0)
return NumCounter(size);
else
return "(not configured)";
}#xCThe size of the query cache
7 7 dyCfunction()
{
var type = MONyog.MySQL.GlobalVariables.query_cache_type;
// warn if cache was turned on bu no buffer was allocated
if(this.Value != "(n/a)" && (type == 1 || type == "ON" || type == 2 || type == "DEMAND") && MONyog.MySQL.GlobalVariables.query_cache_size == 0)
return "Warning";
else
return "None";
} [z1Enable the query cache by setting the query cache variables as follows within your my.cnf/my.ini file and restarting the server:
query_cache_type = 1 (Large values are not recommended on multi-core machines. 256MB would be optimum)
query_cache_limit = 1048576
query_cache_limit should be around 1-2Mb generally to cache as many queries as possible. Experiment with different values of query_cache_size and query_cache_limit until you achieve optimum performance based on your application's use of queries and the amount of memory available on your system.
N *5@KValw '2=HS^it $/:EP[fq |
P
O
N
M
:
,
-
.
/
0
1
3
2
4
5
6
7
8
:
;
9
<
?
A
@
=
>
B
C
E
L
H
I
J
F
G
D
K
$
%
&
(
5
'
*
+
,
.
-
6% {/query_cache_limitk|Qfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.query_cache_limit) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.query_cache_limit);
}}+The maximum size of an individual query results that can be cached. This should be around 1-2Mb generally to cache as many queries as possible.~1Qcache_free_memorygIfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_free_memory) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Qcache_free_memory);
}4 eThe amount of free memory in the query cache
) nWfunction()
{
// check if more than 50% free and it free size is more than 8MB
if(MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache > 100 &&
(SafeDiv0(MONyog.MySQL.GlobalStatus.Qcache_free_memory/MONyog.MySQL.GlobalVariables.query_cache_size) > .5) &&
this.Value > 8388608)
return "Warning";
else
return "none";
}oYfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.query_prealloc_size) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.query_prealloc_size);
}3query_prealloc_size7iIf your Query Cache has more than 50% free memory and the free memory is more than 8MB, it is too large. You can reclaim some memory by decreasing the size of the Query Cache
Avg Yes
L), 3 Qcache_total_blocksF
The query cache allocates memory in blocks for storing cached data. This value indicates the size of each block. If your queries have large result sets, you can get better performance with a larger block size. However, if you have lot of queries with small results, a large block size may lead to memory fragmentationy mfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.query_cache_min_res_unit) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.query_cache_min_res_unit);
} =query_cache_min_res_unit0[This is a persistent buffer used by MySQL for query parsing and execution. If you have lots of complex queries, you might get better performance by increasing the size
= z= :q Qcache_free_blocks / ceil(Qcache_total_blocks / 2)
Avg:q
The total number of free blocks in the query cachegI
function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_free_blocks) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Qcache_free_blocks);
}1
Qcache_free_blocks
Avg5
g The total number of blocks in the query cachei M function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_total_blocks) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Qcache_total_blocks);
}
wtfXJ< function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.Qcache_free_blocks) == "undefined" ||
typeof(MONyog.MySQL.GlobalStatus.Qcache_total_blocks) == "undefined")
return "(n/a)";
return (SafeDiv0(MONyog.MySQL.GlobalStatus.Qcache_free_blocks,Math.ceil(MONyog.MySQL.GlobalStatus.Qcache_total_blocks/2))*100).toFixed(2)+ "%";
}
Avg
Yes Yes Yesy function()
{
if(MONyog.MySQL.GlobalStatus.Qcache_total_blocks >= 300 && MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache > 100)
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
else
return "none";
}
70ym The query cache allocates a block on demand. This value indicates the extent of fragmentation in the query cache
/ /{ k Q The fragmentation is high. This may occur if you have a lot of queries returning small result sets.
Please consider the following options.
1) Decrease the query_cache_min_res_unit
2) Issue (may be periodically) FLUSH QUERY CACHE to defragment the Query Cache.
3) Add SQL_NO_CACHE to queries you know will not be beneficial to cache.Y"-function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_not_cached) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Qcache_not_cached;
}!/Qcache_not_cached
AvgA
The number of queries currently stored in the Query Cacheq ]
function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache);
} ;
Qcache_queries_in_cache
D D(Ha8 s & J-The number of queries that served successfully by the query cacheM,function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_hits) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Qcache_hits;
}5#eThe number of queries that cannot be cached. Prepared statements and queries containing user variables, UDFs or queries accessing the MySQL system database cannot be cached($M MONyog.MySQL.GlobalStatus.Uptime %7 NumCounterWithSeconds&)Qcache_insertsS'!function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_inserts) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Qcache_inserts;
}d(CThe number of queries that were inserted into the query cache; translates into cache misses()M MONyog.MySQL.GlobalStatus.Uptime *7 NumCounterWithSeconds+#Qcache_hits
S S$q _39function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes) == "undefined")
return "(n/a)";
M,function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_hits) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Qcache_hits;
}J-The number of queries that served successfully by the query cache@.{function()
{
if(MONyog.MySQL.GlobalStatus.Qcache_total_blocks >= 300 &&
MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache > 100 &&
SafeDiv0(MONyog.MySQL.GlobalStatus.Qcache_hits / MONyog.MySQL.GlobalStatus.Qcache_inserts) < .5)
return "Warning";
else
return "none";
}(/M MONyog.MySQL.GlobalStatus.Uptime 07 NumCounterWithSeconds
4` u h (5M MONyog.MySQL.GlobalStatus.Uptime
:50g9IThis shows the rate of queries pruned (removed from the cache because of low memory) A1}If more than 50% of the queries are being inserted but never hit, there are some queries that ought to have SQL_NO_CACHE added to them to prevent this work of storing and invalidating. 25Qcache_lowmem_prunes
~Ss< A+function()
{
var cachehitratequery = CacheHitRatioQuery(MONyog.MySQL.GlobalStatus.Qcache_inserts,
MONyog.MySQL.GlobalStatus.Qcache_hits,
MONyog.MySQL.Custom.Available);
if(cachehitratequery)
{
if(!isNaN(cachehitratequery))
return cachehitratequery + "%";
}
return "(n/a)";
}4@eQcache_hits / (Qcache_inserts + Qcache_hits)\?3You should increase the query_cache_size system variable. Also consider using 'SELECT SQL_NO_CACHE...' for :
1) Queries that are too big for the query cache
2) Queries that are non-repetitive in nature.(>M MONyog.MySQL.GlobalStatus.Uptime =
Yes < Yesc;Afunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
?K KValw '2=HS^it$/:EP[fq|
!
"
%
&
#
$
'
*
+
)
(
0F[(Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)(EM MONyog.MySQL.GlobalStatus.Uptime D
Yes C Yes6BgThe Query Cache can speed up your applications dramatically in situations where the application has to repeatedly run the same queries on the same data set, returning the same results each time. You should try to increase the cache hit rate by increasing the query cache and checking the improvements.
9
J40
I20]H5This value indicates the percentage of rows that were accessed via full table scans.CGfunction x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var tablescans = 0;
var nonkeyread = ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd_next)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd);
var allread = ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd_next)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_first)+ ToInt(MONyog.MySQL.GlobalStatus.Handler_read_next)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_key)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_prev);
if(nonkeyread && allread)
tablescans = SafeDiv0(nonkeyread, allread)*100;
return tablescans.toFixed(1)+"%";
}
/ (OM MONyog.MySQL.GlobalStatus.Uptime N
Yes M Yes LYes MKfunction()
{
if(ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd_next) > 4000)
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
else
return "None";
}
P You should try to keep this value as low as possible. Try to isolate queries that are not using indexes. Once you have indentified the queries, create the appropriate indexes or rewrite the queries to use indexes. MySQL has a great feature - slow query log, which allows you to log all queries that took more than a specified amount of time to execute. The slow query log can be used to identify queries that takes a long time to complete. Enabling the slow query log is highly recommended as it allows you to record unoptimized queries. Enable the slow query log refer to http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html for more information on the slow query log.Q-read_buffer_size
T - ] - QU This variable should not be set larger than 1MB for most purposes, and then only with benchmarks to prove that it helps. Making it too large can actually slow down queries. Instead of setting it to a large value globally, leave the global setting small, and set the local value larger for queries you know will benefit from itNTfunction()
{
// should not be more than 1MB
if(this.Value != "(n/a)" && MONyog.MySQL.GlobalVariables.read_buffer_size > 1048576)
return "Warning";
else
return "None";
} 2S_This buffer is used for full table scans of MySQL tables. The recommended value is 1MB. This buffer is allocated per client, so be careful about not making it very largeiRMfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.read_buffer_size) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.read_buffer_size);
}
P PG
,function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Select_scan;
}0-join_buffer_size /7 NumCounterWithSeconds(.M MONyog.MySQL.GlobalStatus.Uptime -9This value indicates the number to times a full table was read for a SELECT. A high value indicates your queries are not properly tuned to use indexes+#Select_scan
O [ O "4?This variable is set to a non-default size, which is usually not justified without benchmarks to support the non-default setting.
Also, this variable should not be set larger than 4M globally, because it is allocated to its full size when needed. If you know that a certain query will benefit from a large setting, it is likely due to poorly optimized JOINs. You should rather optimize schemas and/or queries.A3}function()
{
// should not be more than the default 128K
if(MONyog.MySQL.GlobalVariables.join_buffer_size > 131072)
return "Warning";
else
return "None";
} 25This buffer is used for joins that don't use indexes. Such JOINs are bad. However you can decrease disk usage in such cases by increasing this value!1=function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.join_buffer_size);
}
1 Os8
1 @9function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.tmp_table_size);
}?)tmp_table_size >7 NumCounterWithSeconds(=M MONyog.MySQL.GlobalStatus.Uptime <9 Number of Joins that reevaluate index selection for each row in a join. If you have a high value here, you should optimize your queries to use indexes;# function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Select_range_check;
}:1 Select_range_check 97 NumCounterWithSeconds(8M MONyog.MySQL.GlobalStatus.Uptimeq7]Full table scans are bad. If you have a high value here, you should optimize your queries to use indexes6 function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Select_full_join;
}5-Select_full_join
D bL D Efunction()
{
// should not be less than 32MB
if(this.Value != "(n/a)" && ToInt(MONyog.MySQL.GlobalVariables.max_heap_table_size) < ToInt(MONyog.MySQL.GlobalVariables.tmp_table_size))
return "Warning";
else
return "None";
}kDQThis value defines the maximum size of a MEMORY (storage-engine) table that is created explicitly.$CCfunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.max_heap_table_size);
}B3max_heap_table_size|AsThis value defines the maximum size of temporary tables created in memory. In fact, MySQL also uses another variable 'max_heap_table_size' and uses the lowest of these variables. Please note that this size defines the maximum size of an internal table that can be created in memory. If an internal table is larger than this size, it is converted to a MyISAM table on disk
nQ~S3t HN The number of temporary tables created on disk. If this value is high, you should consider increasing the value of tmp_table_size and max_heap_table_size. MySQL uses the lowest of these variables to determine whether a temporary table can be created in memory. Excessive temporary tables on disk are bad for p M-function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Created_tmp_disk_tables;
} L;Created_tmp_disk_tables K7 NumCounterWithSeconds(JM MONyog.MySQL.GlobalStatus.Uptime8ImThe number of temporary tables created in memoryH#function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Created_tmp_tables;
}G1Created_tmp_tablesFThis variable should be at least as large as tmp_table_size, and may need to be much larger if you use explicit Memory tables. erformance
Temp tables are used for internal operations such as group by and distinct, some ORDER BY queries, UNION, and subqueries in the FROM clause (derived tables). They are created in-memory as Memory tables, and if they exceed min(tmp_table_size, max_heap_table_size) they are converted to an on-disk MyISAM table. The conversion is expensive.
Temporary tables always go right to MyISAM if the query involves BLOB or TEXT columns, which can't be stored in Memory tables. Also, the size of the in-memory temporary table is influenced by the worst-case. Memory tables always use fixed-length columns, so VARCHAR(255) that holds 'y' or 'n' will take 255 characters per row regardless. This is a very good reason not to use larger columns than necessary. Also, if it's utf8, it's 255*3 because the worst-case has to be prepared for. This can make the in-memory temporary tables much larger than you'd think -- many times larger than the total size of the table that's being queried!
4 ~E ^ P B 4 Y
Yes X Yes WYescVAfunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}
U75
T50[S1The percentage of temporary tables that got created on disk because of insufficient tmp_table_size or max_heap_table_size.
MySQL creates temporary tables internally for executing various tasks. If the size of a temporary table exceeds tmp_table_size or max_heap_table_size, MySQL creates the temporary files in 5Refunction x()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var disk = ToInt(MONyog.MySQL.GlobalStatus.Created_tmp_disk_tables);
var total = ToInt(MONyog.MySQL.GlobalStatus.Created_tmp_tables);
var hit = SafeDiv0(disk,total)*100;
return hit.toFixed(1) + "%";
}4QeCreated_tmp_disk_tables / Created_tmp_tables P7 NumCounterWithSeconds(OM MONyog.MySQL.GlobalStatus.Uptime disk instead of memory. Temp tables are used for internal operations such as group by and distinct, some ORDER BY queries, UNION, and subqueries in the FROM clause (derived tables). They are created in-memory as Memory tables, and if they exceed min(tmp_table_size, max_heap_table_size) they are converted to an on-disk MyISAM table. The conversion is expensive. Temporary tables always go right to MyISAM if the query involves BLOB or TEXT columns, which can't be stored in Memory tables. Also, the size of the in-memory temporary table is influenced by the worst-case. Memory tables always use fixed-length columns, so VARCHAR(255) that holds 'y' or 'n' will take 255 characters per row regardless. This is a very good reason not to use larger columns than necessary. Also, if it's utf8, it's 255*3 because the worst-case has to be prepared for. This can make the in-memory temporary tables much larger than you'd think -- many times larger than the total size of the table that's being queried!
"]?function ()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalVariables.sync_binlog) == "undefined")
return "(n/a)";
var type = MONyog.MySQL.GlobalVariables.sync_binlog;
if(type == 0)
return "No";
else
return "Yes";
}\#sync_binlogq[]If this value is high, you should consider increasing the value of tmp_table_size and max_heap_table_size. MySQL uses the lowest of these variables to determine whether a temporary table can be created in memory. Excessive temporary tables on disk are bad for performance.
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user
yX B}Do not grant global privilB}Do not grant global pr v;Shut_down_account_count(wKfunction()
{
if(MONyog.MySQL.Security.Is_mysql_db_accessible != "Yes")
return "(n/a)";
return MONyog.MySQL.Security.Shut_down_account_count;
}BxDo not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.yfunction()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}
H H
8W1 B}Do not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.~function()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}iMIf there are users with unnecessary global privileges, you can remove them using the REVOKE statement. To revoke all global privileges, use the following SQL: REVOKE ALL ON *.* FROM "user_name"@" host_name"
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user
D D
.RXSy
functioBDo not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.function()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}i MIf there are users with unnecessary global privileges, you can remove them using the REVOKE statement. To revoke all global privileges, use the following SQL: REVOKE ALL ON *.* FROM "user_name"@" host_name"
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user
8W"$ function()
{
if(this.Value != "(n/a)" && this.Value > 1)
B Do not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwisiM If there are users with unnecessary global privileges, you can remove them using the REVOKE statement. To revoke all global privileges, use the following SQL: REVOKE ALL ON *.* FROM "user_name"@" host_name"
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user 5
Delete_account_count%E
function()
{
if(MONyog.MySQL.Security.Is_mysql_db_accessible != "Yes")
return "(n/a)";
return MONyog.MySQL.Security.Delete_account_count;
}
v v$ o Yfunction()
{
if(this.Value == "No")
return "Critical";
else
return "None";
}Q This option indicates whether MONyog is able to collect OS Data.
(Note: You have to configure MONyog to enable this feature. This feature is only available on Linux machines with SSH configured.) g F MONyog is not able to collect OS Data. Check:
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user
h h:RR B
Do not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allow!? Lock_tables_account_count*O function()
{
if(MONyog.MySQL.Security.Is_mysql_db_accessible != "Yes")
return "(n/a)";
return MONyog.MySQL.Security.Lock_tables_account_count;
}B Do not grant global privileges unnecessarily. You should always restrict the number of accounts that have global server privileges. Global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous.Any global privilege is a privilege for ALL databases.
^ A iM If there are users with unnecessary global privileges, you can remove them using the REVOKE statement. To revoke all global privileges, use the following SQL: REVOKE ALL ON *.* FROM "user_name"@" host_name"
If you prefer GUI, you can use SQLyog. Go to Tools->User Manager->Edit User to remove the global privileges for a user function()
{
if(this.Value != "(n/a)" && this.Value > 1)
return "Critical";
else
return "None";
}" ) Available ;qfunction()
{
if(CheckForSystem())
{
if(MONyog.System.Custom.Available == 1)
return "Yes";
return "No";
}
return "(not configured)";
}
,
JU
function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
{
if (MONyog.System.Swp.proc_swp_vmsize != -1)
{
return (MONyog.System.Swp.proc_swp_vmsize * 1024);
}
else
{
var ret = "Could not read PID file.
";
if(MONyog.MySQL.Custom.Available == 1)
ret += "Please add the SSH user to mysql group in Linux.";
else
ret += "MySQL is not available.";
return ret;
}
}
else
{
return "(n/a)";
}
}Y!
NumCounterX
System W
YesV
Total amount of virtual memory used by MySQL. It includes all code, data and shared libraries plus pages that have been swapped out;Ts
Value of 'VmSize' in /proc/<MySQL_PID>/status
O '4 O +`Qfunction()
{
if(typeof(MONyog.MySQL.GlobalStatus.Com_drop_user) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_user;
}oaYWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.(_M MONyog.MySQL.GlobalStatus.Uptime^'Com_drop_usero]YWhile it is normal to have frequent changes to the database schema during testing and development, you should be careful of frequent changes in the production server. Always make sure that such changes are authorized and required.\ function ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_table;
}([M MONyog.MySQL.GlobalStatus.UptimeZ)Com_drop_table
~~X '\Ifunction ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Com_drop_table;
} * ^'Com_drop_user(_M MONyog.My b9myisam_recover_options Xc+function()
{
if(MONyog.MySQL.Custom.Available != 1
|| typeof(MONyog.MySQL.GlobalVariables.myisam_recover_options) == "undefined")
return "(n/a)";
var val = MONyog.MySQL.GlobalVariables.myisam_recover_options;
if(val == "OFF" || val == "DEFAULT" || val == "")
return "No";
else
return "Yes";
} d{This option enables automatic MyISAM crash recovery whenever a MyISAM table becomes corrupt for some reason. If this option is not set, then a table will be 'Marked as crashed' when it becomes corrupt and no operations can be performed against it
V ;3eaSetting BACKUP options will ensure that MySQL makes a backup of the MyISAM data file (.MYD) before attempting automatic repair in case a table has crashed. You may also use the FORCE option, but automatic repair may then cause loss of rows. For that reason don't use FORCE unless also using BACKUP.f+innodb_buffer_pool_size + key_buffer_size + max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections * 2MB g
Avg
, S^it H2='$/:EP[fq|
Yi
function()
{
if(CheckForSystem())
return MONyog.System.General.uptime/1000;
else
return "(n/a)";
}
"4GB"$CSet key_buffer_size to a value less than or equal to 4 GB, or upgrade to MySQL version 5.0.52 or later if you are not running the server on Windows.oYfunction()
{
if(this.Value == "Yes")
return "Critical";
else
return "None";
} YeskQThe MyISAM storage engine employs a key cache to keep the most frequently accessed index blocks in memory. This minimizes disk I/O. However, prior to MySQL version 5.0.52 this key cache is limited in size to 4 GB, even on 64-bit operating systems. If set to a larger value, mysqld may crash when it tries to increase the actual buffer beyond 4 GB.
1V8\ `+pCache hit ratio $3_%Cache misses (a?XCache write wait required !b1 @ #c5 #(Additional pool size *dC
'No. of waits for free pages %e9 New deadlock detected? 'f= Latest detected deadlock g+
Log buffer size !h1
Log waits required !i1
"j3
Number of log files $k7
Size of each log file -lI
pCombined size of all log files !m1
XLog flush interval *nC
@No. of waits for free pages
8X =w Time from which the data will be considered and shown;q
function x()
{
if(MONyog.MySQL.GlobalStatus.Uptime_since_flush_status > 0)
return TimeUnits(MONyog.MySQL.GlobalStatus.Uptime_since_flush_status);
else
return "(n/a)";
}Z/
Number of seconds the MySQL server has been running, after FLUSH STATUS execution 9Is_mysql_db_accessible%Efunction ()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.Security.Is_mysql_db_accessible;
}
{ Y;-function x()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var Innodb_buffer_pool_wait_free = MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_wait_free;
var Innodb_buffer_pool_write_requests = MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_write_requests;
if(typeof(Innodb_buffer_pool_wait_free) == "undefined" ||
typeof(Innodb_buffer_pool_write_requests) == "undefined")
{
return "(n/a)";
}
var val = InnoDBCachePercentOnDisk(Innodb_buffer_pool_wait_free, Innodb_buffer_pool_write_requests);
return val + "%";
}y<mTo achieve the best performance, InnoDB should not have to wait before writing pages into the InnoDB buffer pool =0.01 >0.1e?Efunction()
{
return GetWarnStatusFloat(this.Value, this.Critical, this.Warning, true);
}
@ @N @
Avg
AIf InnoDB is waiting too much, increase the size of the InnoDB buffer pool by increasing the value of innodb_buffer_pool_size B
function x()
{
var disk = ToInt(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_wait_free);
var total = ToInt(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_write_requests);
var hit = SafeDiv0(disk,total)*100;
return hit.toFixed(2);
}
R R5 ,\ _39function()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof(MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes;
}(4KThis values indicates the number of queries that were inserted into the query cache, but subsequently got pruned (removed from the cache because of low memory)(5M MONyog.MySQL.GlobalStatus.Uptime 67 NumCounterWithSeconds-7WQcache_lowmem_prunes / Qcache_inserts 8;function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes) == "undefined" ||
typeof(MONyog.MySQL.GlobalStatus.Qcache_inserts) == "undefined")
return "(n/a)";
var val = SafeDiv0(ToInt(MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes), ToInt(MONyog.MySQL.GlobalStatus.Qcache_inserts)) * 100;
return (val.toFixed(2) + "%");
}
g9IThis shows the rate of queries pruned (removed from the cache because of low memory) relative to the number of queries inserted to query cache. If this rate is high, you should increase the query_cache_size system variable
:50
} R D 6 ( Yes Yes Yes
Yes(
M MONyog.MySQL.GlobalStatus.Uptime7 iTuning cache size is not an easy task. Cache hit ratio is not a metric you should use to tune cache size. Instead check Cache misses and follow the advise there. Remember you need to wait till the server is running for a reasonable warm-up time before you get a meaningful cache hit rate.D Tuning cache size is not an exact science. The method which proved most useful over the years is the ubiquitous 'trial-and-error'. Here's what you should do:
X o5
Disable the general query log by removing the log option from your configuration file, if your server is running in a production environment.p#Connections
qfunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Connections;
}Zr/The total number of successful and failed connection attempts to the MySQL server(sM MONyog.MySQL.GlobalStatus.Uptime t7 NumCounterWithSeconds
r _ This variable controls how many file handles InnoDB keeps open internally. If you have set innodb_file_per_table and Open_tables is larger than this, you should set it large enough to hold all tables in the table_cache.
- innodb_checksums
H : |qf[PE:/$ *5@KValw '2=$/:EP[fq|
G
H
I
J
Q
P
O
U
R
S
V
T
W
X
Y
\
Z
[
]
^
a
_
b
c
d
e
f
g
h
i
j
k
F
E
D
>
=
<
;
n n ) iMemoryRequiredpNumUsersvar ret=0;
if(!MONyog.MySQL.GlobalVariables.key_buffer_size)
MONyog.MySQL.GlobalVariables.key_buffer_size = 0;
if(!MONyog.MySQL.GlobalVariables.sort_buffer_size)
MONyog.MySQL.GlobalVariables.sort_buffer_size = 0;
if(!MONyog.MySQL.GlobalVariables.read_buffer_size)
MONyog.MySQL.GlobalVariables.read_buffer_size = 0;
if(!MONyog.MySQL.GlobalVariables.binlog_cache_size)
MONyog.MySQL.GlobalVariables.binlog_cache_size = 0;
ret = ToInt(MONyog.MySQL.GlobalVariables.innodb_buffer_pool_size) +
ToInt(MONyog.MySQL.GlobalVariables.key_buffer_size) +
ToInt(pNumUsers) * (ToInt(MONyog.MySQL.GlobalVariables.sort_buffer_size) +
ToInt(MONyog.MySQL.GlobalVariables.read_buffer_size) +
ToInt(MONyog.MySQL.GlobalVariables.binlog_cache_size))+
ToInt(pNumUsers)*(2*1024*1024);
return NumCounter(ret);
1 . " ! f
=!function() {
var obj = MONyog.UserObject('FullText_Index');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Table_Name + '.' + obj[i].Column_Name;
}
if (results.length == 0)
results = '(n/a)';
return results;
} ?UDO9>oThis shows list of tables having FullText Indexes
j %MuUnitWithNextpUnit, pUnitStr, pNextUnit, pSepvar ret = "";
if(!pSep)
pSep=".";
if(pUnit)
{
ret = String(pUnit);
if(pNextUnit)
ret += pSep + String(pNextUnit);
ret += "" + pUnitStr;
}
return ret; V ){SafeDiv0pNum, pDivisorif(pDivisor == 0)
return 0;
return(pNum/pDivisor); ; _ToIntpNumreturn parseInt(Number(pNum).toFixed(5)); v 13IsReplicationSlavepIsReplicationSlave if(pIsReplicationSlave == "1")
return true;
return false; f )'IsSlaveRunningpSlaveRunningif(pSlaveRunning == "ON")
return true;
return false;
# # Z TimeUnitspCountervar seconds = ToInt(pCounter);
var ret="";
var years = days = hours = minutes = 0;
years = ToInt(seconds / 31536000);
seconds -=(years * 31536000);
days = ToInt(seconds / 86400)
seconds -=(days * 86400);
hours = ToInt(seconds / 3600);
seconds -=(hours * 3600);
minutes = ToInt(seconds / 60);
seconds -=(minutes * 60);
if(years)
{
ret += String(years);
if(years > 1)
ret += " yrs ";
else
ret += " yr ";
}
if(days)
{
ret += String(days);
if(days > 1)
ret += " days ";
else
ret += " day ";
}
if(hours)
{
ret += String(hours);
if(hours > 1)
ret += " hrs ";
else
ret += " hr ";
}
if(minutes)
{
ret += String(minutes);
if(minutes > 1)
ret += " mins ";
else
ret += " min ";
}
if(seconds)
{
ret += String(seconds);
if(seconds > 1)
ret += " secs";
else
ret += " sec";
}
return ret;
]z %/gStorageUnitspBytes, pDecimalsvar ret="", temp;
var tb = gb = mb = kb = 0;
var ONE_K = 1024;
va M !1]NumCounterpCounter, pDecimalreturn StorageUnits(pCounter, pDecimal); 7/aNumCounterWithSecondspCounter, pUptimevar ret = "";
ret += NumCounter(pCounter, 0);
if(!pUptime)
return ret;
var seconds = ToInt(pUptime);
if(seconds>0 && pCounter>0)
ret += " (" + NumCounter(ToInt(pCounter)/seconds, 3)+ "/sec)";
return ret;
IsInnodbvar value =MONyog.Server.Config.InnoDBSupport;
if(value == '')
{
value = MONyog.MySQL.GlobalVariables.have_innodb;
if(typeof(value) == "undefined")
return false;
}
if(value != "NO" && value != "DISABLED")
return true;
return false; m )/CheckForInnoDBpHaveInnodb, pValif(IsInnodb(pHaveInnodb) == true)
return pVal;
return "(n/a)";
r ONE_M = ONE_K * ONE_K;
var ONE_G = ONE_M * ONE_K;
var ONE_T = ONE_G * ONE_K;
if(!pDecimals)
pDecimals = 2;
pBytes = Number(pBytes);
tb = ToInt(pBytes /(ONE_T));
pBytes -= (tb * ONE_T);
if(tb>0)
{
temp = SafeDiv0(pBytes, (ONE_T));
ret = (tb + temp).toFixed(pDecimals) + "T";
return ret;
}
gb = ToInt(pBytes /(ONE_G));
pBytes -=(gb * ONE_G);
if(gb>0)
{
temp = SafeDiv0(pBytes, (ONE_G));
ret = (gb + temp).toFixed(pDecimals) + "G";
return ret;
}
mb = ToInt(pBytes /(ONE_M));
pBytes -=(mb * ONE_M);
if(mb>0)
{
temp = SafeDiv0(pBytes, (ONE_M));
ret = (mb + temp).toFixed(pDecimals) + "M";
return ret;
}
kb = ToInt(pBytes / ONE_K);
pBytes -= (kb * ONE_K);
if(kb>0)
{
temp = SafeDiv0(pBytes, (ONE_K));
ret = (kb + temp).toFixed(pDecimals) + "K";
return ret;
}
return pBytes.toFixed(pDecimals);
B 3]CacheHitRatioInnodbpBufferPoolReads, pBufferPoolReadRequests, pIsMySQLAvailablevar disk = ToInt(pBufferPoolReads);
var total = ToInt(pBufferPoolReadRequests);
var hit = 0;
if(pIsMySQLAvailable == 1)
{
if(total > 0)
hit = (1 - SafeDiv0(disk, total)) * 100;
return hit.toFixed(2);
}
return null; { 3imCacheHitRatioMyISAMpKeyReads, pKeyReadRequests, pIsMySQLAvailablevar disk = ToInt(pKeyReads);
var total = ToInt(pKeyReadRequests);
var hit = 0;
var percentage;
if(pIsMySQLAvailable == 1)
{
if(total > 0)
{
percentage = (1 - SafeDiv0(disk, total));
if(percentage >= 0 && percentage <= 1)
{
hit = percentage * 100;
return hit.toFixed(2);
}
else
{
return null;
}
}
return hit.toFixed(2);
}
else
return null;
o 1kUGetCpuIOPercentagepUser, pNice, pSystem, pIdle, pIowait, pHi, pSi// Get the percentage of CPU Io wait, in the mode whose value is passed as the parameter.
var totalcpu = 0;
totalcpu = Number (pUser);
totalcpu += Number (pNice);
totalcpu += Number (pSystem);
totalcpu += Number (pIdle);
totalcpu += Number (pIowait);
totalcpu += Number (pHi);
totalcpu += Number (pSi);
if(totalcpu==0)
{
return 0;
}
var cp = (pIowait / totalcpu) * 100;
cp = cp.toFixed(1);
return Number(cp); 8 1iiCacheHitRatioQuerypQCacheInserts, pQCacheHits, pIsMySQLAvailablevar cachehits = pQCacheHits;
var total = ToInt(pQCacheInserts) + ToInt(cachehits);
var hitrate = 0;
if(pIsMySQLAvailable == 1)
{
hitrate = (SafeDiv0(cachehits, total) * 100).toFixed(2);
return hitrate;
}
return null;
& & & 2 +- GetServerUptimepServerStartTimevar today = new Date();
var serverstarttime = new Date(pServerStartTime * 1000);
return ((today - serverstarttime)/1000); I
+ oCheckForSystem if(MONyog.System)
return true;
return false; Y !k9GetCpuUsedpUser, pNice, pSystem, pIdle, pIowait, pHi, pSiret = GetCpuPercentage(pUser, pNice, pSystem, pIdle, pIowait, pHi, pSi);
if(ret == -1)
{
return 0;
}
ret = (100-ret).toFixed(1);
return ret; { -kqGetCpuPercentagepUser, pNice, pSystem, pIdle, pIowait, pHi, pSi// Get the percentage of CPU used, in the mode whose vaule is passed as the parameter.
var totalcpu = 0;
totalcpu = Number (pUser);
totalcpu += Number (pNice);
totalcpu += Number (pSystem);
totalcpu += Number (pIdle);
totalcpu += Number (pIowait);
totalcpu += Number (pHi);
totalcpu += Number (pSi);
if(totalcpu==0)
{
totalcpu=1;
return -1;
}
var cp = (pIdle / totalcpu) * 100;
cp = cp.toFixed(1);
return Number(cp);
l l C*function() {
var obj = MONyog.UserObject('DiskInfo');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (obj[i]['Rows'] >= 1000000) {
if (results.length > 0)
results += "
";
results += obj[i].Database + "." + obj[i].Table;
}
}
if (results.length == 0)
results = 'None';
return results;
} ,UDO<+uThis shows tables which has more than a million rows
61function() {
var obj = MONyog.UserObject('Tables_Size');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Tables + ' ' + obj[i].Total_Size;
}
if (results.length == 0)
results = 'None';
return results;
} 8UDO'7Kshows number of tables and size 5UDO
: , F Yes EYeseDEfunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false);
} C1
B105AgThis shows ratio of tables having primary key @
function() {
var obj = MONyog.UserObject('Primary_Key_Ratio');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += parseFloat(obj[i].Ratio).toFixed(2)+'%';
}
if (results.length == 0)
results = 'None';
return results;
}
$S4\y4Z %796InnoDB Data Activities ,8G:InnoDB Row Lock Acquring Time +9E>MyISAM Key Buffer Activities *:CBhMyISAM Key Block Activities $;7FPRow Access Statistics < J8Row Reads =/N Row Modifications >+RSort Activities %?9YTable Lock Contentions @#UTable Locks 'A=]Temporary Tables Created B'aTables Opened &C;eBinlog Cache Efficiency D/ixReplication Slave #E5 m`Database Throughputs #F5 u0Table DDL Statements =GiList of tables having more than a million rows -HITop 5 Databases and their size 3IU Data distribution by storage engines index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.Indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.
* Indexes don’t come for free.What you gain for in retrieval speed you lose in insertion and deletion speed because every time you alter a table the indexes must be updated accordingly. If your table is updating frequently it’s possible that having indexes will cause overall performance of your database to suffer.
x xt # _ 9>oThis shows list of tables having FullText Indexes ?UDO > Q3 function() {
var obj = MONyog.UserObject('Data_Types');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (obj[i].Number > 100) {
if (results.length >0)
results+='
';
results += obj[i].Data_Type + ' '+ obj[i].Number;
}
}
if (results.length == 0)
results = '(n/a)';
return results;
}04]Shows Number of columns of each datatype
j9Ofunction() {
var obj = MONyog.UserObject('WorstIndex');
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enable=:wThis shows worst performing queries on MySQL instance9 function() {
var obj = MONyog.UserObject('WorstIndex');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Table + '.' + obj[i]['Field_Name'];
}
if (results.length == 0)
results = '(n/a)';
return results;
}
t t u=efunction() {
var obj = MONyog.UserObject('FullText_Index');
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Table_Name + '.' + obj[i].Column_Name;
}
if (resul=:wThis shows worst performing queries on MySQL instance <UDO2;_* Use EXPLAIN PLAN it shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed.
* Indexes take up disk space. Usually the space usage isn’t significant, but because of creating
^ ^ 80kfunction() {
var obj = MONyog.UserObject('Storage_Engine');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Engine+ ' :'+ obj[i].Tables+' ' + obj[i].Total_Size;
}
if (results.length == 0)
results = 'None';
return results;
} 2UDOU1%This shows storage engine types and number of tables belongs to each of them
z z +-Qfunction() {
var obj = MONyog.UserObject('Database_Size');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results += obj[i].Database + ' ' +obj[i].Database_Size+'M';
}
if (results.length == 0)
results = 'None';
return results;
} /UDOF.This Shows top 5 databases and their size on a MySQL instance
W *W P 9S'InnoDBLogPercentOnDiskInnodb_log_waits, Innodb_log_writesvar disk = ToInt(Innodb_log_waits);
var total = ToInt(Innodb_log_writes);
var hit = SafeDiv0(disk,total)*100
return hit.toFixed(2); S 7 uKeyCachePercentOnDiskvar disk = ToInt(MONyog.MySQL.GlobalStatus.Key_writes);
var total = ToInt(MONyog.MySQL.GlobalStatus.Key_write_requests);
var hit = SafeDiv0(disk,total);
return hit.toFixed(2);
C = aInnoDBCachePercentOnDiskInnodb_buffer_pool_wait_free, Innodb_buffer_pool_write_requestsvar disk = ToInt(Innodb_buffer_pool_wait_free);
var total = ToInt(Innodb_buffer_pool_write_requests);
var hit = SafeDiv0(disk,total)*100;
return hit.toFixed(2);
m m ! - CachePercentUsedif(!MONyog.MySQL.GlobalStatus.Key_blocks_unused)
MONyog.MySQL.GlobalStatus.Key_blocks_unused = 0;
var blocks = SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size, MONyog.MySQL.GlobalVariables.key_cache_block_size).toFixed(0);
if(MONyog.MySQL.GlobalStatus.Key_blocks_unused > 0)
return((1 - MONyog.MySQL.GlobalStatus.Key_blocks_unused/blocks)*100).toFixed(3);
else
return 0; l # ;PercentUsedif(!MONyog.MySQL.GlobalStatus.Key_blocks_unused)
MONyog.MySQL.GlobalStatus.Key_blocks_unused = 0;
if(MONyog.MySQL.GlobalVariables.key_cache_block_size && MONyog.MySQL.GlobalVariables.key_buffer_size)
{
var blocks=SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size, MONyog.MySQL.GlobalVariables.key_cache_block_size).toFixed(0);
return((1 - SafeDiv0(MONyog.MySQL.GlobalStatus.Key_blocks_unused, blocks))*100).toFixed(3);
}
else
return "(n/a)";
H P H 1[GetWarnStatusFloatpValue, pCritical, pWarning, pIsGreaterif(pIsGreater == true)
{
if(parseFloat(pValue) > pCritical)
return "Critical";
else if(parseFloat(pValue) > pWarning)
return "Warning";
}
else
{
if(parseFloat(pValue) < pCritical)
return "Critical";
else if(parseFloat(pValue) < pWarning)
return "Warning";
}
return "None"; | -[GetWarnStatusIntpValue, pCritical, pWarning, pIsGreaterif(pIsGreater == true)
{
if(parseInt(pValue) > pCritical)
return "Critical";
else if(parseInt(pValue) > pWarning)
return "Warning";
}
else
{
if(parseInt(pValue) < pCritical)
return "Critical";
else if(parseInt(pValue) < pWarning)
return "Warning";
}
return "None"; - !5RatioUnitspCounter1, pCounter2var ratio;
var ret = "";
ratio = SafeDiv0(pCounter1, pCounter2).toFixed(3);
if(ratio>0)
ret += String(ratio)+":1";
return ret;
= =X *) Each connection to the MySQL database server runs in its own thread. Thread creation is a time consuming activity, so instead of killing the thread each time a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later$+C function()
{
// should not be 0
if(MONyog.MySQL.GlobalVariables.thread_cache_size == 0)
return "Warning";
else
return "None";
}
q q
function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)"; g 7 innodb_force_recovery
q] "Innodb_force_recovery is set to a non-zero value ONLY when recovering data from a corrupt database and must be set to zero otherwise.
Set this variable to zero in MySQL.ini if you are not backing up a corrupt database."
function()
{
if(MONyog.MySQL.GlobalVariables.innodb_force_recovery > 0)
return "Warning";
else
return "None";
}% log_warningsS! "This option is used ONLY to back up databases in case of database corruption.
Setting the innodb_force_recovery to a value between 0 and 6 would force the innodb engine to start while preventing
any kind of background operations from happening - there by allowing us to backup tables from a corrupt database. "
* V < !
log_output : o function()
{
var warnings = MONyog.MySQL.GlobalVariables.log_warnings;
if(warnings == 0 || warnings == 1)
return "Warning";
else
return "None";
}
Ensure log_warnings is set to a value greater than 1 to log warnings related to‘Aborted connection information’ and replication.G This option logs the MySQL warnings. Set this value to zero to disable logging warnings, one to log warnings but not those related to "Aborted connections", two or above to log all warnings.&G function()
{
if(MONyog.MySQL.Custom.Available != 1)
{
return "(n/a)";
}
var value = MONyog.MySQL.GlobalVariables.log_warnings;
if(value == 0)
{
return "No";
}
else if(value == 1)
{
return "Enabled BUT warnings on Aborted connection wont be recorded";
}
else if(value >= 2)
{
return "Enabled and all warnings are being recorded";
}
}
-p 4&e Days after which the binary logs are purged.\%3 function()
{
if(MONyog.MySQL.Custom.Available != 1 || ( typeof MONyog.MySQL.GlobalVariables.expire_logs_days == "undefined"))
return "(n/a)";
return MONyog.MySQL.GlobalVariables.expire_logs_days;
}$- expire_logs_days "
function()
{
if(MONyog.MySQL.GlobalVariables.log_output == "TABLE")
return "Warning";
return "None";
}i!M
Logging queries to Slow/General log tables instead of files would affect performance adversely. N
This variable specifies the destination of the Slow and General logs.O
function()
{
if( MONyog.MySQL.Custom.Available != 1 || (typeof MONyog.MySQL.GlobalVariables.log_output == "undefined"))
return "(n/a)";
return MONyog.MySQL.GlobalVariables.log_output;
}
`Uo \'3 Old binary logs need to be removed from the disk if binary logging is enabled, failing which the binary logs fill the disk.
There is no ideal value for this variable as it depends on how busy your MySQL server is. To find the optimum value for this variable you will have to do trial and error such that binary logs are deleted after they are made use of.
NOTE: Removing binary log files from the disk manually may result in erroneous behaviour.q(] function()
{
var isbinlog = MONyog.MySQL.GlobalVariables.log_bin;
var days = MONyog.MySQL.GlobalVariables.expire_logs_days;
if( isbinlog && days == 0)
return "Warning";
else
return "None";
}*+
max_binlog_size
+
function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalVariables.max_binlog_size;
}
d dmm ,
This variable defines the maximum size of a binary log file. Sometimes during big transactions, while writing to the binary log in chunks, the content are not split over files resulting in such log files exceeding this value. The default value is 1 GB._-9
This value should be set to its default(maximum) value which is 1 GB for all purposes.y.m
function()
{
var isbinlog = MONyog.MySQL.GlobalVariables.log_bin;
var size = MONyog.MySQL.GlobalVariables.max_binlog_size;
if( isbinlog && size != 1073741824)
return "Warning";
else
return "None";
}0!flush_time1 function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalVariables.flush_time;
}2All the tables are closed in order to free resources and all the un-synchronized data are flushed to the disk if this variable is set.
i)F|
! O3If this option is set MySQL might spend considerable amount of time closing tables to release resources and flushing frequently there by affecting the performance.
Disable this option in my.cnf4 function()
{
if(MONyog.MySQL.GlobalVariables.flush_time > 0)
return "Warning";
else
return "None";
}5!
NumCounter 6 RAM Usage71["MySQL", "Total"]28_[(SafeDiv0(MONyog.System.Mem.proc_mem_vmrss , MONyog.System.Mem.sys_mem_total) * 100).toFixed(2),
(SafeDiv0(MONyog.System.Mem.sys_mem_total - MONyog.System.Mem.sys_mem_free - MONyog.System.Mem.sys_mem_buffers - MONyog.System.Mem.sys_mem_cached , MONyog.System.Mem.sys_mem_total) * 100).toFixed(2)]9%RealTimeLine:Current ;7Database Transactions8<m["BEGINs", "COMMITs", "ROLLBACKs", "SAVEPOINTs"]
b Bp3z :b b * &.; qHDatabase DDL Statements /$M>Key buffer size greater than 4GB +%E @Is innodb_force_recovery on? &' .Log warnings? '+
2Log destination .(K #(Days before purging binary logs +)E
'Maximum binary log file size !*1: ,+G>Interval between disk flushes , RAM Usage /-XConnections Used $-7Database Transactions 1Disk IO '0= @Percentage of Cache Hits %29 #(Query Cache Efficiency !31
'Query Cache Memory 4- *Queries in Cache !51 .InnoDB Row Details 16Q
2InnoDB Buffer Pool Page Activities
] ]<e X=+[ToInt(MONyog.MySQL.GlobalStatus.Com_begin) + ToInt(MONyog.MySQL.GlobalStatus.Com_xa_start),
ToInt(MONyog.MySQL.GlobalStatus.Com_commit) + ToInt(MONyog.MySQL.GlobalStatus.Com_xa_commit),
ToInt(MONyog.MySQL.GlobalStatus.Com_rollback) + ToInt(MONyog.MySQL.GlobalStatus.Com_xa_rollback),
ToInt(MONyog.MySQL.GlobalStatus.Com_savepoint)]>%RealTimeLine ?; Database DDL Statements J m * &@I ["CREATEs", "DROPs", "ALTERs"]A) [ToInt(MONyog.MySQL.GlobalStatus.Com_create_db),
ToInt(MONyog.MySQL.GlobalStatus.Com_drop_db),
ToInt(MONyog.MySQL.GlobalStatus.Com_alter_db)]
B eB H=Percentage of Cache Hits*EQ["Percentage of Connections Used"]B% RealTimeLineC Current
D-Connections Used ~Fw[(SafeDiv0(MONyog.MySQL.GlobalStatus.Threads_connected,MONyog.MySQL.GlobalVariables.max_connections)*100).toFixed(2)]GCurrent LI["MyISAM Key Cache", "InnoDB Cache", "Query Cache", "Thread Cache"]
X X ( 3 CacheHitRatioThreadpThreadCreated, pConnections, pThreadCacheSize, pIsMySQLAvailablevar threadcachesize = ToInt(pThreadCacheSize);
var threadcreated = ToInt(pThreadCreated);
var connection = ToInt(pConnections);
var hitrate = 0;
if(pIsMySQLAvailable == 1)
{
if(connection > 0)
{
hitrate = ((1-SafeDiv0(threadcreated,connection))*100).toFixed(2);
}
return hitrate;
}
else
return null; z 9kcGetCpuKernalPercentagepUser, pNice, pSystem, pIdle, pIowait, pHi, pSi// Get the percentage of CPU used by Kernal, in the mode whose value is passed as the parameter.
var totalcpu = 0;
totalcpu = Number (pUser);
totalcpu += Number (pNice);
totalcpu += Number (pSystem);
totalcpu += Number (pIdle);
totalcpu += Number (pIowait);
totalcpu += Number (pHi);
totalcpu += Number (pSi);
if(totalcpu==0)
{
return 0;
}
var cp = (pSystem / totalcpu) * 100;
cp = cp.toFixed(1);
return Number(cp);
@ @T N[CheckForSystem() && MONyog.System.Custom.Available == 1 && IsKernalVersionLessThan2dot6(MONyog.System.Custom.Version) ? (MONyog.System.Io.blocks_in * 1024) : 0,
CheckForSystem() && MONyog.System.Custom.Available == 1 && IsKernalVersionLessThan2dot6(MONyog.System.Custom.Version) ? (MONyog.System.Io.blocks_out * 1024) : 0,
CheckForSystem() && MONyog.System.Custom.Available == 1 && IsKernalVersionLessThan2dot6(MONyog.System.Custom.Version) ? MONyog.System.Io.blocks_in * 1024 + MONyog.System.Io.blocks_out * 1024 : 0]:Pq ["Hits", "Inserts", "Lowmem Prunes", "Not Cached"] O9 Query Cache EfficiencyM%RealTimeLine"LA["Read", "Write", "Total"]KDisk IO yog.System.Cpu.sys_cpu_iowait, MONyog.System.Cpu.sys_cpu_hi,
MONyog.System.Cpu.sys_cpu_si):0,
CheckForSystem()?GetCpuUserPercentage(MONyog.System.Cpu.sys_cpu_user, MONyog.System.Cpu.sys_cpu_nice, MONyog.System.Cpu.sys_cpu_system,
MONyog.System.Cpu.sys_cpu_idle, MONyog.System.Cpu.sys_cpu_iowait, MONyog.System.Cpu.sys_cpu_hi,
MONyog.System.Cpu.sys_cpu_si):0,
CheckForSystem()?GetCpuIOPercentage(MONyog.System.Cpu.sys_cpu_user, MONyog.System.Cpu.sys_cpu_nice, MONyog.System.Cpu.sys_cpu_system,
MONyog.System.Cpu.sys_cpu_idle, MONyog.System.Cpu.sys_cpu_iowait, MONyog.System.Cpu.sys_cpu_hi,
MONyog.System.Cpu.sys_cpu_si):0,
CheckForSystem()?GetCpuUsed(MONyog.System.Cpu.sys_cpu_user, MONyog.System.Cpu.sys_cpu_nice, MONyog.System.Cpu.sys_cpu_system,
MONyog.System.Cpu.sys_cpu_idle, MONyog.System.Cpu.sys_cpu_iowait, MONyog.System.Cpu.sys_cpu_hi,
MONyog.System.Cpu.sys_cpu_si):0
]
L L 2! E IsKernalVersionLessThan2dot6pVersionvar version = (pVersion+"").split();
if(version[0] < 2)
return false;
else if (version[1] < 6)
return false;
else
return true; | 5kkGetCpuUserPercentagepUser, pNice, pSystem, pIdle, pIowait, pHi, pSi// Get the percentage of CPU used by User Program, in the mode whose value is passed as the parameter.
var totalcpu = 0;
totalcpu = Number (pUser);
totalcpu += Number (pNice);
totalcpu += Number (pSystem);
totalcpu += Number (pIdle);
totalcpu += Number (pIowait);
totalcpu += Number (pHi);
totalcpu += Number (pSi);
if(totalcpu==0)
{
return 0;
}
var cp = (pUser / totalcpu) * 100;
cp = cp.toFixed(1);
return Number(cp);
lO *SQ
["Percentage of Query Cache Used"]_% RealTimeLine 2^a ["Read", "Inserted", "Updated", "Deleted"]]1 InnoDB Row Details;\s [MONyog.MySQL.GlobalStatus.Qcache_queries_in_cache][ CurrentZ% RealTimeLine7Qi [MONyog.MySQL.GlobalStatus.Qcache_hits,
MONyog.MySQL.GlobalStatus.Qcache_inserts,
MONyog.MySQL.GlobalStatus.Qcache_lowmem_prunes,
MONyog.MySQL.GlobalStatus.Qcache_not_cached]R1
Query Cache Memory T%
RealTimeLineU% RealTimeLine3Va
[(SafeDiv0(MONyog.MySQL.GlobalVariables.query_cache_size - MONyog.MySQL.GlobalStatus.Qcache_free_memory,
MONyog.MySQL.GlobalVariables.query_cache_size) * 100).toFixed(2)]W
Current
Y# ["Queries"]X- Queries in Cache
' g 5 *bQ
InnoDB Buffer Pool Page Activitiesh/["Read", "Write"]g#InnoDB Datad)
[MONyog.MySQL.GlobalVariables.have_innodb ?
(SafeDiv0(ToInt(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_pages_data) + ToInt(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_pages_misc),
ToInt(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_pages_total)) * 100).toFixed(2)
: 0]f
Currente%
RealTimeLine /c[
["Percentage of Buffer Pool Page Used"]a% [MONyog.MySQL.GlobalStatus.Innodb_rows_read ? MONyog.MySQL.GlobalStatus.Innodb_rows_read : 0,
MONyog.MySQL.GlobalStatus.Innodb_rows_inserted ?
MONyog.MySQL.GlobalStatus.Innodb_rows_inserted : 0,
MONyog.MySQL.GlobalStatus.Innodb_rows_updated ?
MONyog.MySQL.GlobalStatus.Innodb_rows_updated : 0,
MONyog.MySQL.GlobalStatus.Innodb_rows_deleted ?
MONyog.MySQL.GlobalStatus.Innodb_rows_deleted : 0]
N *5@KValw '2=HS^it $/:EP[fq|
! -
! ,
! +
*
)
(
'
&
%
?
@
A
B
C
6
7
8
9
:
K
L
N
M
0 f @ Ii
[MONyog.MySQL.GlobalStatus.Innodb_data_read ? MONyog.MySQL.GlobalStatus.Innodb_data_read : 0,
MONyog.MySQL.GlobalStatus.Innodb_data_written ? MONyog.MySQL.GlobalStatus.Innodb_data_written : 0]j%RealTimeLine%kGInnoDB Row Lock Acquring Timel#["Current"] #sCMyISAM Key Block Activities8mm[MONyog.MySQL.GlobalStatus.Innodb_row_lock_time]n%RealTimeLine$oEMyISAM Key Buffer Activities:pq["Read Request", "Read", "Write Request", "Write"].qW[MONyog.MySQL.GlobalStatus.Key_read_requests,
MONyog.MySQL.GlobalStatus.Key_read,
MONyog.MySQL.GlobalStatus.Key_write_requests,
MONyog.MySQL.GlobalStatus.Key_writes]r%RealTimeLine
^ r ^[I)
h ]u5["Percentage of MyISAM Key Blocks Used", "Percentage of MyISAM Key Blocks Modified"]&G[ToInt(MONyog.MySQL.GlobalStatus.Handler_read_key),
ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd_next)+ToInt(MONyog.MySQL.GlobalStatus.Handler_read_rnd)]~%RealTimeLine/}[["via Indices", "via Full Table Scans"]| Row Reads{%RealTimeLine+zS[MONyog.MySQL.GlobalStatus.Queries] y5["Queries Executed"] x7Row Access StatisticswCurrentv%RealTimeLine
t[100 - (SafeDiv0(MONyog.MySQL.GlobalStatus.Key_blocks_unused,
SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size,
MONyog.MySQL.GlobalVariables.key_cache_block_size))*100).toFixed(2),
(SafeDiv0(MONyog.MySQL.GlobalStatus.Key_blocks_not_flushed,
SafeDiv0(MONyog.MySQL.GlobalVariables.key_buffer_size,
MONyog.MySQL.GlobalVariables.key_cache_block_size))*100).toFixed(2)]
P PT"Ck 1JQNumber of columns of each datatype .KKTotal number of tables and size 9LaWorst performing indexes on MySQL instance 'M=List of FullText Indexes 6N[ Percentage of tables having primary key 6O[Percentage of free memory for Data Node 3PUPercentage of free Redo buffer space *QC Number of nodes not running /RMPercentage of free Redo logspace S+ Connected users %T9 List of Unused Indexes 5UY Number of InnoDB buffer pool instances 6V[@tData integrity check enabled by server? !W1An
: <%+ : iL , U["Percentage of Table Locks Waited"]>
w[(SafeDiv0(MONyog.MySQL.GlobalStatus.Table_locks_waited, ToInt(MONyog.MySQL.GlobalStatus.Table_locks_waited)+ToInt(MONyog.MySQL.GlobalStatus.Table_locks_immediate))*100).toFixed(2)] 9Table Lock ContentionshK[MONyog.MySQL.GlobalStatus.Table_locks_immediate,
MONyog.MySQL.GlobalStatus.Table_locks_waited]
7 ["Immediate", "Wait"] #Table Locks ! ! %RealTimeLine%RealTimeLine}[MONyog.MySQL.GlobalStatus.Sort_merge_passes,
MONyog.MySQL.GlobalStatus.Sort_range,
MONyog.MySQL.GlobalStatus.Sort_scan]9o["Merge Passes", "Using Range", "By Table Scans"]+Sort Activities[MONyog.MySQL.GlobalStatus.Handler_write,
MONyog.MySQL.GlobalStatus.Handler_update,
MONyog.MySQL.GlobalStatus.Handler_delete]%RealTimeLine)O["INSERTs", "UPDATEs", "DELETEs"] /Row Modifications
&Il&>x@ %RealTimeLine =Temporary Tables Created =["In Memory", "On Disk"] 7[MONyog.MySQL.GlobalStatus.Created_tmp_tables - MONyog.MySQL.GlobalStatus.Created_tmp_disk_tables,
MONyog.MySQL.GlobalStatus.Created_tmp_disk_tables]%RealTimeLine 'Tables Opened5g["Tables Opened", "Table Definitions Opened"] fG[MONyog.MySQL.GlobalStatus.Opened_tables,
MONyog.MySQL.GlobalStatus.Opened_table_definitions]%RealTImeLine 7Binlog Cache Effiency%G["Cache Write", "Disk Write"]fG[MONyog.MySQL.GlobalStatus.Binlog_cache_use,
MONyog.MySQL.GlobalStatus.Binlog_cache_disk_use]%RealTimeLine /Replication Slave! ?["Seconds Behind Master"]
t t 4 2 a[MONyog.MySQL.Slave.Seconds_Behind_Master] %RealTImeLine Current !5 Database Throughputs%"G ["Sent", "Received", "Total"]8#k [MONyog.MySQL.GlobalStatus.Bytes_sent,
MONyog.MySQL.GlobalStatus.Bytes_received,
ToInt(MONyog.MySQL.GlobalStatus.Bytes_sent) + ToInt(MONyog.MySQL.GlobalStatus.Bytes_received)]$% RealTimeLine %5 Table DDL Statements&&I ["CREATEs", "DROPs", "ALTERs"] '9 [ToInt(MONyog.MySQL.GlobalStatus.Com_create_table),
ToInt(MONyog.MySQL.GlobalStatus.Com_drop_table),
ToInt(MONyog.MySQL.GlobalStatus.Com_alter_table)](% RealTimeLine) Current
W W %JE[CacheHitRatioMyISAM(MONyog.MySQL.GlobalStatus.Key_reads, MONyog.MySQL.GlobalStatus.Key_read_requests, MONyog.MySQL.Custom.Available),
CheckForInnoDB(MONyog.MySQL.GlobalVariables.have_innodb, CacheHitRatioInnodb(MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_reads, MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_read_requests, MONyog.MySQL.Custom.Available)),
CacheHitRatioQuery(MONyog.MySQL.GlobalStatus.Qcache_inserts, MONyog.MySQL.GlobalStatus.Qcache_hits, MONyog.MySQL.Custom.Available),
CacheHitRatioThread(MONyog.MySQL.GlobalStatus.Threads_created,MONyog.MySQL.GlobalStatus.Connections,MONyog.MySQL.GlobalVariables.thread_cache_size, MONyog.MySQL.Custom.Available)]
ps[
function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_log_file_size = MONyog.MySQL.GlobalVariables.innodb_log_file_size;
var innodb_log_files_in_group = MONyog.MySQL.GlobalVariables.innodb_log_files_in_group;
if(typeof(innodb_log_file_size) == "undefined" || typeof(innodb_log_files_in_group) == "undefined")
return "(n/a)";
return NumCounter(ToInt(innodb_log_files_in_group) * ToInt(innodb_log_file_size));
}8rm
innodb_log_files_in_group * innodb_log_file_size .tY
The combined size of InnoDB log files.&uI
innodb_flush_log_at_trx_commit
P P
h9K [MONyog.MySQL.GlobalStatus.Table_locks_immediate,
MONyog.MySQL.GlobalStatus.Table_locks_waited]+:SMONyog.MySQL.GlobalStatus.Questions ;9 default_storage_engine?<y function()
{
if(typeof(MONyog.MySQL.GlobalVariables.default_storage_engine) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.default_storage_engine;
}3=c Shows the default storage engine for tablese>E The default storage engine for tables. As of MySQL 5.6.3. The storage engine for permanent tables only. To set the storage engine for TEMPORARY tables from MySQL 5.6.3, set the default_tmp_storage_engine system variable.?) innodb_version/@Y function()
{
if(typeof(MONyog.MySQL.GlobalVariables.innodb_version) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.innodb_version;
}(AM Shows the InnoDB version number
B1
performance_schema
%3A IUDO9Jo(Total memory - Used memory / Total memory) * 100 'function() {
var obj = MONyog.UserObject('Cluster_Data_Free');
if (!obj || !obj.isEnabled() || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var r
M40
N30eOEfunction()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false);
} PYes Q Yes R
YesPSWhen all memory is used, no more rows can be added to MySQL Cluster tables.
If more memory is available on the data nodes then increase the value of the DataMemory and IndexMemory parameters and perform a rolling restart of the data nodes. Alternatively remove any rows from Cluster tables and run OPTIMIZE TABLE
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results +=parseFloat(obj[i].Free_Redo_Buffer).toFixed(2)+'%';
}
if (results.length == 0)
results = 'None';
return results;
}
c1function() {
var obj = MONyog.UserObject('Cluster_RedoLogspace');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results +=parseFloat(obj[i].Free_Redo_Log_Space).toFixed(2)+'%';
}
if (results.length == 0)
results = 'None';
return results;
}Nb(Total Redo Logspace - Used Redo Logspace/ Total Redo Logspace) * 100 aUDO
j UDO'kK Shows number of users connectedXl+ function() {
var obj = MONyog.UserObject('Percona_Unused_Indexes7dkAdvises when the redo logspace start to fill up e Yes f
YesTg#Currently using %redo_logspace_used% out of %redo_logspace_total% of configured redo log space. If the checkpointing is slow and there are so many writes to the database that the log files are full and the log tail cannot be cut without jeopardizing recovery, all updating transactions are aborted with internal error code 410 (Out of log file space temporarily). This condition prevails until a checkpoint has completed and the log tail can be moved forward. hUDO
6 6 Wu) function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_buffer_pool_instances = MONyog.MySQL.GlobalVariables.innodb_buffer_pool_instances;
if(typeof(innodb_buffer_pool_instances) == "undefined")
return "(n/a)";
else
return innodb_buffer_pool_instances;
}
Jv The number of regions that the InnoDB buffer pool is divided into w7 If you have a total innodb_buffer_pool_size of several Gigabytes and you are using MySQL 5.5+ you can define more buffer pool instances. The total pool is divided into multiple instances each handling I/O etc. Define so many instances that each is as close to 1 GB as possible.
g !}=function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.open_files_limit);
}$CThe number of files that MySQL can have open at a time. Default settings are often too low for production servers. It is safe to raise the value up to 64K.?~{Number of file descriptors available to MySQL instance.|-open_files_limit8{kTo obtain the highest level of data integrity set SQL_mode to any of the following:TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES.
Note that the recommended values were not introduced until MySQL version 5.0.2. If you are running MySQL version 4.1.x, use ANSI SQL mode. Refer the MySQL manual for the options available in earlier versions of MySQL.After setting sql_mode to the desired value in your my.cnf/my.ini file, restart your MySQL server.
Note:Global setting does not prevent any client to define it own SESSION sql_mode
i ) 7 innodb_file_per_table
Setting skip_name_resolved disables the MySQL server's ability to perform DNS lookups. DNS servers can be unavailable and for a production server it is usually better to specify and ip and not a hostname as the 'host' specifier of a username. However users must then be created as 'user'@’ip’ and not as 'user'@'host' (except for ‘localhost’ what is accepted with this setting ON)<uResolve host names when checking client connections.8kfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof MONyog.MySQL.GlobalVariables.skip_name_resolve== "undefined")
return "(n/a)";
else
if (MONyog.MySQL.GlobalVariables.skip_name_resolve == "ON")
return "NO";
else
return "YES";
} /skip_name_resolve
( X+ function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var value = MONyog.MySQL.GlobalVariables.innodb_file_per_table;
if(!value)
return "(n/a)";
return value;
} InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace.T# Innodb_file_per table controls if a single tablespace is used by InnoDB. If set to "1" a tablespace will be created for each table. It has effect from the time it was applied - existing tables are not changed. Using multiple tablespaces is in particular useful if you often drop large objects (tables or databases) as files will be deleted and the disk space reclaimed by the OS. With the setting of "0" dropping an object will not return diskspace to the OS.
O
If set to "0" metadata statistics are not updated when accessing table information from Information_Schema. Disabling statistics on metadata may thus improve performance with queries to Information_Schema if you have a very large number of databases or tables in a database. But it is generally not needed and not recommended.3 afunction()
{
if(this.Value != "(n/a)" && MONyog.MySQL.GlobalVariables.myisam_recover_options == "FORCE")
return "Warning";
else
return "None";
}2
_You can improve 'ORDER BY' performance by setting this variable to a large value. However, since it is allocated per client, be careful with extremely high values.The buffer may also be set on a SESSION basis. This is most often a more economical way to handle memory than raising global settings.
P P ,%S If you are not using InnoDB XA transaction support, it is advisable that you disable this feature. This features adds an extra fsync which can degrade performance.
m I W mr F
function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_stats_on_metadata = MONyog.MySQL.GlobalVariables.innodb_stats_on_metadata;
if(typeof(innodb_stats_on_metadata) == "undefined")
return "(n/a)";
else
return innodb_stats_on_metadata;
}!?
InnoDB stats on metadata? %
E
Updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS.
K K 1] function() {
var obj = MONyog.UserObject('Percona_Active_Tables');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length > 0)
results += "
";
results += obj[i].TABLE_SCHEMA+"."+obj[i].TABLE_NAME+"
"+"Rows read"+' '+ obj[i].ROWS_READ+"
"+"Rows changed"+ ' '+obj[i].ROWS_CHANGED+"
";
}
if (results.length == 0)
results = 'None';
return results;
}
y y O Shows most active tables based on #rows read and changed respectively. UDO' Last_IO_ErrorG function()
{
if(IsReplicationSlave(MONyog.Server.Config.IsReplicationSlave) == false || !MONyog.MySQL.Slave.Last_IO_Error)
return "(n/a)";
if(MONyog.MySQL.Slave.Last_IO_Error =="MySQL user requires 'replication client' privilege for displaying this data")
return MONyog.MySQL.Slave.Last_IO_Error;
else
return MONyog.MySQL.Slave.Last_IO_Errno + " " + MONyog.MySQL.Slave.Last_IO_Error;
}A The last error message that caused the I/O thread to stop
~r / [Calculated from values at /proc/loadavg
No
No
Yes
1gI
function()
{
if(this.Value == "(n/a)" || this.Value == "MySQL user requires 'replication client' privilege for displaying this data" ||
( !MONyog.MySQL.Slave.Last_Error_Changed ||
(MONyog.MySQL.Slave.Last_Error_Changed && MONyog.MySQL.Slave.Last_Error_Changed == "No")))
return "None";
else
return "Critical";
} 1p[ function()
{
if(this.Value == "(n/a)" || this.Value == "MySQL user requires 'replication client' privilege for displaying this data" ||
( !MONyog.MySQL.Slave.Last_IO_Error_Changed ||
(MONyog.MySQL.Slave.Last_IO_Error_Changed && MONyog.MySQL.Slave.Last_IO_Error_Changed == "No")))
return "None";
else
return "Critical";
} Yes
: C { P : (# Connections('M MONyog.MySQL.GlobalStatus.Uptime&System5$eIf load avg value is more that the number of CPU in a system, it is considered that processes are waiting for CPU cycles. Following can be cause of high load avg.
1. CPU bound processes waiting for cpu cycles.
2. High memory consumption.
3. I/O bound processes waiting over resources. %
Avg # Yes "Yes!function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
return GetWarnStatusFloat(this.Value, this.Critical * MONyog.System.Cpu.sys_cpu_count , this.Warning * MONyog.System.Cpu.sys_cpu_count, true);
else
return 'None';
} 1 0.9# CLoad average for last 5 min9 mfunction()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
ret = MONyog.System.LoadAvg.last_5_min;
else
ret = "(n/a)";
return ret;
}
G *5@KValw '2=HS^it$/:EP[fq|
'
(
6
*
+
0
1
2
3
,
^ l5 ^z^AeA (b= Load avg for last 5 min -sI Number of nodes in the cluster r% Cluster UUID #q3$ Sync relay log info p)# Sync relay log o-" Sync master info !n1!} Relay log recovery m/ Binary log format l/
LLock wait timeout 'k= 'Innodb adaptive flushing !j1
Performance schema i) \InnoDB version %h9 Default storage engine g# Table Locks e! Statements d Bytes c# Connections a) 3,Last I/O error :^c Most Active tables (rows read,rows changed) ']=
#(InnoDB stats on metadata 4\W Single InnoDB table space for tables? )[A4Need to resolve host name? !Z15 Y)FPmax open files !X1Bh
6N8( WB l V 6 6 6 6 87["Immediate", "Wait"]7# Table Locks!6? ["Successful", "Refused"] Z 35 [ToInt(MONyog.MySQL.GlobalStatus.Com_select) + ToInt(MONyog.MySQL.GlobalStatus.Qcache_hits),
ToInt(MONyog.MySQL.GlobalStatus.Com_insert) + ToInt(MONyog.MySQL.GlobalStatus.Com_replace),
ToInt(MONyog.MySQL.GlobalStatus.Com_update),
ToInt(MONyog.MySQL.GlobalStatus.Com_delete)]42e ["SELECTs", "INSERTs", "UPDATEs", "DELETEs"]1! Statements(0M MONyog.MySQL.GlobalStatus.Uptime /# StackedAreaY.- [MONyog.MySQL.GlobalStatus.Bytes_sent,
MONyog.MySQL.GlobalStatus.Bytes_received] -5 ["Sent", "Received"]
, Bytes+# StackedArea
* [MONyog.MySQL.GlobalStatus.Connections - MONyog.MySQL.GlobalStatus.Aborted_connects,
MONyog.MySQL.GlobalStatus.Aborted_connects] #
Z ZMx 7Ci
function()
{
if(typeof(MONyog.MySQL.GlobalVariables.performance_schema) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.performance_schema;
}5Dg
Shows whether performance_schema is ON or OFF'EI
If your server supports performance_schema you should consider to enable what performance_schema functionalities you find useful for your work. Note that some performance_schema functionalities may have a significant performance impact, so you should only enable what you intend to use" F= innodb_adaptive_flushing ( 9Gm function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_adaptive_flushing = MONyog.MySQL.GlobalVariables.innodb_adaptive_flushing;
if(typeof(innodb_adaptive_flushing) == "undefined")
return "(n/a)"
return innodb_adaptive_flushing;
}
'S S^it$/:EP[fq|
"
!
p
q
r
s
t
u
v
w
x
y
z
{
|
}
~
zZ mHU This is ON as default and should remain so in most cases as it will prevent bursts of I/O activity.
I/
lock_wait_timeout@J{
function()
{
if(typeof(MONyog.MySQL.GlobalVariables.lock_wait_timeout) == "undefined")
return "(n/a)"
return TimeUnits(MONyog.MySQL.GlobalVariables.lock_wait_timeout);
}K-
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year).L
The setting defines for how long a query will wait for a LOCK to be released. If the value specified is exceeded the query will be aborted. Default setting is rather high. You may choose to lower this setting if you prefer an error to a very long wait.
'S S^it$/:EP[fq|
!
"
#
$
%
&
'
(
)
*
+
,
-
.
/
0
1
@
A
B
C
D
E
2
3
4
5
6
M' binlog_format-NU function()
{
if(typeof(MONyog.MySQL.GlobalVariables.binlog_format) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.binlog_format;
}pO[ Specify whether to use row-based, statement-based, or mixed replication. Statement-based is the default h &,28>DJPV\bhntz
"(.4:@FLRX^djpv|
w o 2` 3h 5s 6 8 9 :) , / 2 4 8 9 <
? F K L >V ?W ] ` @c Ah k t Bw bz C D E H
F
I J K L( O8 =B PG RL TO VR WW X\ Yb Zj \q ]w `} ^ _ a i j k" l% m+ r0 s4 t@ uJ vT w^ xh zr {| | } ~ ' / 3 6 : ? D J P Z d o u | $ 1 7 B J P , Y
7Ri!function()
{
if(typeof(MONyog.MySQL.GlobalVariables.relay_log_recovery) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.relay_log_recovery;
}Q1!relay_log_recovery/PY The value of this may be STATEMENT (from very early MySQL versions), ROW (from MySQL 5.1) or MIXED (from MySQL 5.5). If you are using MySQL 5.1 or higher you should consider what binlog format to use. It is not possible to provide a general advice as what is optimal will depend on applications and how they write to to the master. But using ROW or MIXED setting may - as compared to STATEMENT - decrease the risk of deadlocks on slaves since BULK INSERTS (including LOAD DATA statements) will not be executed as one large transaction on the slave.
! nS! xWk"If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.3Va"function()
{
if(typeof(MONyog.MySQL.GlobalVariables.sync_master_info) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.sync_master_info;
}U-"sync_master_info&TG!At least replication slaves that are restarted automatically after a crash etc. should have this enabled, as it reduces the risk of corruption of slave data.dSC!If sets to “ON” , Enables automatic relay log recovery immediately following server startup, which means that the replication slave discards all unprocessed relay logs and retrieves them from the replication master.
$ ] $ 5\e#A value of 1 is the generally the best choice. The default value of sync_relay_log is 0, which does not force any synchronization to disk by the MySQL server—in this case, the server relies on the operating system to flush the relay-log.info file's contents from time to time as for any other file.m[U#If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk/ZY#function()
{
if(typeof(MONyog.MySQL.GlobalVariables.sync_relay_log) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.sync_relay_log;
}Y)#sync_relay_logcXA"If value is > 0 then slave synchronizes its master.info file to disk using fdatasync()) after every sync_master_info events. In this case, the server relies on the operating system to flush the master.info file's contents from time to time as for any other file. 0 is safest, but may have a small performance overhead depending on the OS details.
Q Q o,
]3$sync_relay_log_info9^m$function()
{
if(typeof(MONyog.MySQL.GlobalVariables.sync_relay_log_info) == "undefined")
return "(n/a)"
return MONyog.MySQL.GlobalVariables.sync_relay_log_info;
}z_o$If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk:`o$A value of 1 is the generally the best choice. The default value of sync_relay_log_info is 0, which does not force any synchronization to disk by the MySQL server—in this case, the server relies on the operating system to flush the relay-log.info file's contents from time to time as for any other file. a= wsrep_cluster_state_uuidrb_ function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_cluster_state_uuid) == "undefined" )
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_cluster_state_uuid);
}
e e. (cK This value shows Cluster's uuid and we need to check whether the node belongs to the right cluster and this value should be the same across the whole cluster.d1 wsrep_cluster_sizeeeE function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_cluster_size) == "undefined" )
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_cluster_size);
}f Make sure that this value is equal to the expected number of nodes, enough to check this variable value on any of the nodes. g5 wsrep_received_byteshhK function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_received_bytes) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_received_bytes);
}7ik Total number of bytes written from other nodes.j- wsrep_replicated
: {>Je+ l : /K "Serialization of Leaf To Memory +C "Deserialized Leaf To Memory ,E
"De-Compressed Leaf To Memory )? "Compressed Leaf To Memory 3S"Buffers Fetched - Target Query Time 7["@Basement Nodes - Fetched For Write Time !!@Row Format +!XRead Block Size !!pBlock Size !/!Checkpoint Period -~I 6InnoDB buffer pool load status 2}S
2Load InnoDB buffer pool at startup? 3|U .Dump InnoDB buffer pool on shutdown? ({?
'wsrep_local_state_comment z) #(Cluster status y# @wsrep_ready &x; XFind network bottleneck *wC pSlowest node in the cluster :vc Number of parrallel threads for replication >uk Total number of bytes replicated to other nodes Atq Total number of writes from other nodes (in bytes)
r rW ak= function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_replicated) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_replicated);
}>ly Shows total number of bytes replicated to other nodes.m3 wsrep_slave_threadsvng function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalVariables.wsrep_slave_threads) == "undefined")
return "(n/a)";
return NumCounter(MONyog.MySQL.GlobalVariables.wsrep_slave_threads);
}9oo Shows number of parrallel threads for replication p; wsrep_flow_control_sentnqW function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_flow_control_sent) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_flow_control_sent);
}
I \eG1 I dwC function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_ready) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_ready;
}v# wsrep_readyu/ This variable is useful to find out network bottlenecks. High (on the order of the number of client connections) values may indicate a bottleneck in the network. If this is the case, the cause can be at any layer, from physical to OS configuration and must be investigated.sta function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_send_queue_avg) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_local_send_queue_avg);
}"sA wsrep_local_send_queue_avg|rs The slowest cluster node will have the highest value. Highest value for a node indicates slowest node in a cluster.
1 K'1 r _
function()
{
if(MONyog.MySQL.Custom.Available != 1 ||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_state_comment) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_local_state_comment);
}!?
wsrep_local_state_comment1~] PRIMARY means it can accept writes and reads, also a good indicator if the node is in a good state
NON-PRIMARY can't accept reads and writes, not a part of the cluster.
+ X- V + (M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime$C
In a primary component it normally can be one of: Joining, Waiting for SST, Joined, Synced and Donor and in a non-primary component node state comment should be Initialized. Any other states are transient and momentary.
Synced - In sync with other nodes in the cluster, data is consistent with all other nodes
Donor - read-only, currently helping get another nodes synced into the cluster
Joining - communicating with the cluster to join
Joined - Just joined the cluster
Waiting for SST - in process of receiving state transfer
Note: Generally you won't see "Joining" or "Joined" because it happens so fast with anything under a 6 node cluster (even over the WAN)
9 h SEh g}I function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_cluster_status) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_cluster_status);
};xs If this value is "ON", the node can accept SQL load;ys Make sure that value of this variable is ON always.gzI function()
{
if(this.Value == "OFF" )
return "Critical";
else
return "None";
}d |5 wsrep_cluster_status { Yes
T)}R' +S innodb_buffer_pool_dump_at_shutdown(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(M MONyog.MySQL.GlobalStatus.Uptime(
M MONyog.MySQL.GlobalStatus.Uptime( M MONyog.MySQL.GlobalStatus.Uptime( M MONyog.MySQL.GlobalStatus.Uptime(
M MONyog.MySQL.GlobalStatus.Uptime( M MONyog.MySQL.GlobalStatus.Uptime
x sql_modeJyfunction()
{
if(MONyog.MySQL.Custom.Available != 1 || typeof MONyog.MySQL.GlobalVariables.sql_mode== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.sql_mode;
}rz_SQL Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. If no SQL modes are enabled this means there is no kind of server-enforced data integrity, which means incoming data that is invalid will not be rejected by the server, but instead will be changed to conform to the target column's default datatype.
3 L7
NumCounterWithSecondsvMg
This value should not increase by more than 1/sec. If it does, it means the InnoDB buffer pool is too small.N/ Deadlock_detected4Oc function()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
if(typeof MONyog.MySQL.InnodbStatus.Deadlock_detected == "undefined" || MONyog.MySQL.InnodbStatus.Deadlock_detected == 0)
return "(not configured)"
return MONyog.MySQL.InnodbStatus.Deadlock_detected;
}
F F 6Pg The InnoDB Engine employs row-level locking mechanisms to handle multiple simultaneous transactions. For instance, if a row were to be updated at the same time that a SELECT statement was retrieving it, the output of the SELECT statement would be inconsistent. InnoDB's locking mechanism prevents such inconsistencies from creeping in but is susceptible to deadlocks. Deadlocks occur when two or more transactions wait on the other to complete before executing, thereby entering an infinitely long cycle. InnoDB can instantly identify and break deadlocks, at the cost of rolling back one or more transactions (the victims). If deadlocks occur frequently, it should be cause for worry.
spa
function()
{
if(this.Value == "(n/a)" || typeof (MONyog.MySQL.GlobalStatus.Innodb_pages_read) == "undefined")
return "None";
// should be more than the default 5MB
if((MONyog.MySQL.GlobalStatus.Innodb_pages_read > 0) && MONyog.MySQL.GlobalVariables.innodb_log_file_size == 5242880)
return "Warning";
else
return "None";
}8qk
This variable controls the size of the InnoDB transaction logs. The variable is set to its default value, 5M, which is too small. You should watch the Innodb_os_log_written variable over a period of several minutes, and estimate how many bytes per second are written to your InnoDB transaction logs. You can multiply this value by 3600 (an hour) and divide by innodb_log_files_in_group to choose a good log file size.,oU
Size of each log file in a log group
r !GStatementspCounter, pUptime, pQuestionsvar total = ToInt(pQuestions);
var percent =(100*(pCounter/total)).toFixed(3);
ret = NumCounterWithSeconds(pCounter,ToInt(pUptime),2) + " " + (percent>0 ? String(percent)+"%" :"")
return ret;
var value = MONyog.MySQL.GlobalVariables.innodb_force_recovery;
if(typeof(value) == "undefined")
return "(n/a)";
if(value == 0)
{
return "No";
}
else if(value == 1)
{
return "Yes. Set to 1 - Makes SELECT * FROM tbl_name jump over corrupt index records and pages, which help in dumping tables";
}
else if(value == 2)
{
return "Yes. Set to 2 - Prevents the main thread from running";
}
else if(value == 3)
{
return "Yes. Set to 3 - Does not run transaction rollbacks after recovery";
}
else if(value == 4)
{
return "Yes. Set to 4 - Prevents insert buffer merge operations";
}
else if(value == 5)
{
return "Yes. Set to 5 - Does not look at undo logs when starting the database";
}
else
{
return "Yes. Set to 6 - Does not do the log roll-forward in connection with recovery";
}
}
I *5@KValw '2=HS^it$/: EP[f q|
$
#
"
.
/
7
9
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
z
{
y
|
}
~
!
? 'I Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Make sure that you have set this value to ON (1), so that whenever MySQL server goes down InnoDB buffer pool gets saved automatically.
*Q
innodb_buffer_pool_load_at_startup
j & I Innodb_buffer_pool_load_status
function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_buffer_pool_load_at_startup = MONyog.MySQL.GlobalVariables.innodb_buffer_pool_load_at_startup;
if(typeof(innodb_buffer_pool_load_at_startup) != "undefined")
{
return innodb_buffer_pool_load_at_startup;
}
else
{
return "(n/a)";
}
}
a =
Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination with innodb_buffer_pool_dump_at_shutdown.
Make sure that you have set this value to ON (1), so that whenver MySQL server starts up InnoDB buffer pool gets reloaded automatically.
Y [~Y ""A innodb_max_dirty_pages_pctY!- This can be used to monitor the progress of the load of a innodb buffer pool. If it takes too long, or causes other issues, you can abort the buffer pool load by setting innodb_buffer_pool_load_abort to 'ON'.* Q Indicates buffer pool load status
t c function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var Innodb_buffer_pool_load_status= MONyog.MySQL.GlobalStatus.Innodb_buffer_pool_load_status;
if(typeof(Innodb_buffer_pool_load_status) != "undefined")
{
return Innodb_buffer_pool_load_status;
}
else
{
return "(n/a)";
}
}
, D, %# This value should always be either 2-4 times than log file size or 10-20% of the buffer pool size, not more than that.
Example:
Buffer pool size is 40GB, redo logs are 2x1000MB. 2-4 times of 2GB =
4/8GB. 4/8GB = 10/20% of the buffer pool, this is recommended value.L$ How much of the buffer pool InnoDB permits to be dirty at any time
i#M function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_max_dirty_pages_pct= MONyog.MySQL.GlobalVariables.innodb_max_dirty_pages_pct;
if(typeof(innodb_max_dirty_pages_pct) != "undefined")
{
return innodb_max_dirty_pages_pct;
}
else
{
return "(n/a)";
}
}
T o[Z h T + ! Customize5*g!We recommend leaving this variable unchanged.6)g!This variable specifies the time in seconds between the beginning of one checkpoint and the beginning of the next. The default time between TokuDB checkpoints is 60 seconds.}(u!function()
{
if (MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
var result =
MONyog.MySQL.GlobalVariables.tokudb_checkpointing_period;
if (typeof(result) == "undefined")
return "(n/a)";
return TimeUnits(result);
}' ! Customize
& function()
{
if(MONyog.MySQL.Custom.Available != 1 || !IsInnodb())
return "(n/a)";
var innodb_buffer_pool_dump_at_shutdown= MONyog.MySQL.GlobalVariables.innodb_buffer_pool_dump_at_shutdown;
if(typeof(innodb_buffer_pool_dump_at_shutdown) != "undefined")
{
return innodb_buffer_pool_dump_at_shutdown;
}
else
{
return "(n/a)";
}
}
$ $; b%Slow_queriescfunction()
{
if(MONyog.MySQL.Custom.Available != 1)
return "(n/a)";
return MONyog.MySQL.GlobalStatus.Slow_queries;
}
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results +=obj[i].Nodes_Not_Running;
Z
Yes Y Yes6XiAdvises when the redo buffers start to fill up']K(Total nodes) - (Running nodes) \UDO [{Currently using %redo_buffer_used% out of %redo_buffer_total% of configured redo buffer space. If redo buffers fill up, then errors may be observed. If more buffer space is required, increase the RedoBuffer parameter and perform a rolling restart.
gYK 7ii function() {
var obj = MONyog.UserObject('Users_Connected');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results += '
';
results += obj[i].No_Of_Connections + ' ' + obj[i].Users + '@' + obj[i].Host_Name;
}
if (results.length == 0)
results = 'None';
return results;
} j UDO'kK Shows number of users connected
& &Ov %lE function() {
var obj = MONyog.UserObject('Percona_Unused_Indexes');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length > 0)
results += "
";
results += obj[i].Table_Name + "." + obj[i].Index_Name;
}
if (results.length == 0)
results = 'None';
return results;
}$mE Shows list of unused indexes n UDOoSystempSystemqSystemrSystems System$tE innodb_buffer_pool_instances
( T ( (KKfunction() {
var obj = MONyog.UserObject('Cluster_Data_Free');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results +=parseFloat(obj[i].Percentage_Of_Free_Data_Memory).toFixed(2)+'%';
}
if (results.length ==0)
results = 'None';
return results;
}9Jo(Total memory - Used memory / Total memory) * 100 IUDOQH Make sure that you have Primary key on every table for faster accessing. G
Yes
K K 1L]Advises when the amount of Memory(Data and Index) configured for the data nodes starts to run low. Database inserts will start to fail is all of the memory is consumed.
z^ofunction() {
var obj = MONyog.UserObject('Cluster_Nodes');
if(!obj) {
return "Custom SQL Object doesn't exist. Click here to add.";
}
if(!obj.isEnabled()) {
return 'Custom SQL Object for this monitor is not enabled.
Click here to enable it.';
}
if (!obj || !MONyog.MySQL.Custom.Available)
return '(n/a)';
obj = obj.select();
var results = '';
for (i in obj) {
if (results.length >0)
results+='
';
results +=obj[i].Nodes_Not_Running;
}
if (results.length == 0)
results = 'None';
return results;
},`SOnly %Ndb_number_of_ready_data_nodes% out of %Ndb_number_of_data_nodes% data nodes are running. View which data nodes are unavailable by running "ndb_mgm -e show".5_gIndicates how many data nodes are not running
G eTC2!cUGGG d d d * ! Yes Yes This value indicates the percentage of free space left on the volume, if this value is less consider increasing size of the volume. This value indicates the percentage of free space left on the volume, if this value is less consider increasing size of the volume. O System System SystemSystemK Amount of free space left on the volume where InnoDB data resides.JAmount of free space left on the volume where MySQL data resides.
$ Y > 1 $ $ $
1 30
0 30e/E function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false);
}e.E function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false);
} +
Yes *
Yes$)C function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
return MONyog.System.Disk.sys_disk_free_innodb;
return "(n/a)";
}#(Afunction()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
return MONyog.System.Disk.sys_disk_free_mysql;
return "(n/a)";
}
$7C function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
return MONyog.System.Disk.sys_disk_free_tmpdir;
return "(n/a)";
}<6u Free disk space, received by executing 'df' command. 5 function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
{
if( MONyog.System.Disk.sys_disk_freepercent_innodb != "(n/a)")
return ( MONyog.System.Disk.sys_disk_freepercent_innodb + "%");
}
return "(n/a)";
} 4
function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
{
if( MONyog.System.Disk.sys_disk_freepercent_mysql != "(n/a)")
return ( MONyog.System.Disk.sys_disk_freepercent_mysql + "%");
}
return "(n/a)";
}
3 20
2 20
'S |qf[PE:/$ti^S
8
~
}
|
{
z
y
x
w
v
t
u
s
r
q
p
o
n
m
; zm` h Z L ; ; B!System A!
Yes @!Yese?E!function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, false);
} >!This value indicates the percentage of free space left on the volume, if this value is less consider increasing size of the volume.
=!20
<!30 ;!function()
{
if(CheckForSystem() && MONyog.System.Custom.Available == 1)
{
if( MONyog.System.Disk.sys_disk_freepercent_tmpdir != "(n/a)")
return ( MONyog.System.Disk.sys_disk_freepercent_tmpdir + "%");
}
return "(n/a)";
} O 9 System8! Amount of free space left on the volume where the directory specified in 'tmpdir' system variable resides. (NOTE: If you have specified more than one directories in 'tmpdir' system variable then, disk usage of only that volume where first directory resides is shown)
K gB. J= innodb_autoinc_lock_modeI This value should be equal to 'ROW' as it prevents clients from setting a per session binary log format that is not 'ROW'.
H It is a binary log format that overrides any session binlog_format settings.Possible values
can be 'STATEMENT','ROW','MIXED' or 'NONE'.
"FA wsrep_forced_binlog_format0E]!(free disk space/Total disk space)*100 .0D] (free disk space/Total disk space)*100 .0C] (free disk space/Total disk space)*100 .
uCb*V 7I[! Percentage of Time Cluster is Throttled 2ES ixLatency of the replication protocol CDu eNumber of transaction that failed certification test 5CY aNumber of transaction aborted by slave (B? ] Length of the send queue +AE Y Length of the receive queue .6K .InnoDB auto-increment lock mode 5?Y RList of removed nodes from the cluster $>7 N List of delayed nodes (=? J8Cluster connection status G% yQueue Length F% 28000Queue Length :@c UPercentage of time the cluster is throttled /9M :Myisam tables replication status 38U 6Address for cluster nodes to connect &7;
2Enforced storage engine !H1 } Galera Transaction
aP=
Storage engines are components that handle the SQL operation for different table types.
N9
enforce_storage_enginexMk This value must be 2 as other modes may cause INSERT statements on tables with
AUTO_INCREMENT columns to fail.
yLm It is used for generating auto-increment values.Traditional lock mode is 0,consecutive is 1 and interleaved is 2
_ VQ'
This value should be “InnoDB”, if it is available for the particular MySQL version.Adding ‘enforce_storage_engine=InnoDB’ in my.cnf file will start the server with InnoDB as enforced storage engine R7 wsrep_cluster_addressKT This gives cluster nodes addresses to connect to when starting up.iUM It should not be set to the string “gcomm://” as this will result into restarting of a node. V9 wsrep_replicate_myisam(XK Possible values for this are 'ON' or 'OFF'.'ON' indicates that MyISAM tables will be
replicated and 'OFF' indicates that MyISAM tables will not be replicated.
eYE This value must be OFF, as it is still in the experimental phase and cannot be relied upon.
T Tnz f+ wsrep_connectedph[ It indicates whether the cluster is connected to the wsrep provider.Possible values are 'ON' or 'OFF'.
i' Must be 'ON'.j/ wsrep_evs_delayedal= This gives the list of all the nodes a particular node has registered on its delayed
list,indicating the node in the list may be removed permanently from the cluster.
The listing format is “uuid:address:count”.This represents the UUID and IP address of the delayed
node,with a count of the number of the entries it has on the delayed list.
dmC function()
{
if(this.Value !=" " )
return "Critical";
else
return "None";
} n5 wsrep_evs_evict_list
) < ) function()
{
if(this.Value != 2 && this.Value !="(n/a)")
return "Critical";
else
return "None";
} function()
{
if(this.Value !="ROW" && this.Value !="(n/a)")
return "Critical";
else
return "None";
} function()
{
if(this.Value !="Primary" && this.Value !="(n/a)")
return "Critical";
else
return "None";
}
Yes Yes
20 5ym function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_evs_repl_latency)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_evs_repl_latency;
}y function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_cert_failures)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_local_cert_failures;
}
{ function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalVariables.wsrep_replicate_myisam) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.wsrep_replicate_myisam;
}kQ function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_connected)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_connected;
} ~w function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalVariables.wsrep_cluster_address) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.wsrep_cluster_address;
}
=a alw '2=HS^it$/:EP[fq|
!
!
!
!
!
!
!
!
!
!
!
!
!
!
!
! B
! C
! D
! E
! F
! G
! H
! I
! J
! K
! L
! M
! N
! O
! 9
! :
! ;
!
5
!
6
!
7
!
8
! ,
! -
! .
! /
! 0
! 1
!
2
!
3
!
4
! <
! =
! >
! ?
! @
! A
N i N ) function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_flow_control_paused) == "undefined")
return "(n/a)";
return (MONyog.MySQL.GlobalStatus.wsrep_flow_control_paused*100).toFixed(2) + "%";
}L function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_evs_evict_list)== "undefined")
return "(n/a)";
else if (MONyog.MySQL.GlobalStatus.wsrep_evs_evict_list)
return(MONyog.MySQL.GlobalStatus.wsrep_evs_evict_list);
else return "(empty)";}C function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_evs_delayed)== "undefined")
return "(n/a)";
else if (MONyog.MySQL.GlobalStatus.wsrep_evs_delayed)
return(MONyog.MySQL.GlobalStatus.wsrep_evs_delayed);
else return "(empty)";}
=-w![MONyog.MySQL.GlobalStatus.wsrep_flow_control_paused]
c?k w9 wsrep_local_recv_queue% 7 % 7 v YescuA function()
{
return GetWarnStatusInt(this.Value, this.Critical, this.Warning, true);
}6tg It indicates the percentage of time the cluster is in throttled state waiting for slower
nodes to catch up,in other words,how much the slave lag is slowing down the cluster. !r? wsrep_flow_control_paused!q? This list must be empty.
p- This lists the UUID's of all nodes removed from the cluster.The removed nodes can
rejoin the cluster only when their mysqld process is restarted
% E % % % 7 wsrep_local_bf_abortsm~U Values should be closer to 0, as considerably larger values than 0 indicate replication
throttling.
;}s It is the instantaneous length of the send queue.
{9 wsrep_local_send_queuez Value should be closer to 0,as values considerably larger than 0 means that node can not apply write sets as fast as they are received.oyY It is the current length of the receive queue,which is the number of write-sets waiting to be applied.
Y- It gives the total number of transactions that have failed certification tests.
!? wsrep_local_cert_failures$C Increase in this value is a good indication of some unexpected deadlock problem arising because of some local transaction getting aborted and rolled back.
kQ It gives the total number of local transactions aborted by slave transactions while in
execution.
D Y8
# This value periodically resets.The reset value can be controlled using the
evs.stats_report_period parameter.The default value is 1 minute.% E This setting provides information on the latency of the replication protocol.The
variable format is Minimum/Average/Maximum/Standard Deviation/Sample Size.
9 wsrep_evs_repl_latency#A Increase in this value is a good indication of some unexpected deadlock problem arising because of some local transaction getting aborted and rolled back.
{
function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalVariables.enforce_storage_engine) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.enforce_storage_engine;
} function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalVariables.innodb_autoinc_lock_mode) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.innodb_autoinc_lock_mode;
} function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalVariables.wsrep_forced_binlog_format) == "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalVariables.wsrep_forced_binlog_format;
}
ym function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_recv_queue)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_local_recv_queue;
}ym function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_send_queue)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_local_send_queue;
}wi function()
{
if(MONyog.MySQL.Custom.Available != 1||
typeof(MONyog.MySQL.GlobalStatus.wsrep_local_bf_aborts)== "undefined")
return "(n/a)";
return MONyog.MySQL.GlobalStatus.wsrep_local_bf_aborts;
}
" ( (n+Hb= "
function()
{
if(this.Value !="InnoDB" && this.Value != "(n/a)")
return "Critical";
else
return "None";
}h!K function(){if(this.Value=="OFF" && this.Value!="(n/a)") return "Critical"; else return "None";} ",A!["Cluster Throttled Time"]/+[!Percentage of Time Cluster is Throttledm#U function(){if(this.Value=="gcomm://" && this.Value!="(n/a)") return "Critical"; else return "None";}g$I function(){if(this.Value=="ON" && this.Value!="(n/a)") return "Critical"; else return "None";}%% Queue Length4&e ["Receive Queue Length","Send Queue Length"]l'S [MONyog.MySQL.GlobalStatus.wsrep_local_recv_queue,MONyog.MySQL.GlobalStatus.wsrep_local_send_queue](1 Galera TransactionD) ["Transaction Aborted","Transaction Certification Failure"]n*W [MONyog.MySQL.GlobalStatus.wsrep_local_bf_aborts,MONyog.MySQL.GlobalStatus.wsrep_local_cert_failures]