From 51455efdbe6892758a03253ef035d7a47640f603 Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@gmail.com>
Date: Mon, 8 Aug 2016 16:21:21 +0200
Subject: [PATCH] status, css

---
 lib/php/db/types/mysql.php | 45 ++++++++++++++++++++++++--------------
 1 file changed, 28 insertions(+), 17 deletions(-)

diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php
index 20594abf..d440adad 100644
--- a/lib/php/db/types/mysql.php
+++ b/lib/php/db/types/mysql.php
@@ -22,7 +22,7 @@ $DB_TYPES['mysql'] = array (
 'extra_where' => 'having',
 
 'localFile' => array (getenv('HOME').'/.my.cnf','^(?:user(?:name)?=(?P<user>\\S+)|password=(?P<password>\\S+))'),
-'databases' => 'SELECT SCHEMA_NAME as `name`,DEFAULT_CHARACTER_SET_NAME as `encoding`,DEFAULT_COLLATION_NAME as `collate` FROM INFORMATION_SCHEMA.SCHEMATA',
+'databases' => "SELECT SCHEMA_NAME as `name`,DEFAULT_CHARACTER_SET_NAME as `encoding`,DEFAULT_COLLATION_NAME as `collate` FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('performance_schema','information_schema')",
 
 'table.sql' => 'SHOW CREATE TABLE `<NAME>`',
 'table.sql.index' => "SELECT ".(Db::p('db.type') ? "CONCAT(s.TABLE_NAME,'_',s.INDEX_NAME,'_idx')" : 's.INDEX_NAME')." as name,(CASE NON_UNIQUE WHEN 1 THEN 0 ELSE 1 END) as uniqe,GROUP_CONCAT(COLUMN_NAME) as field FROM INFORMATION_SCHEMA.STATISTICS s LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON t.TABLE_SCHEMA=s.TABLE_SCHEMA AND t.TABLE_NAME=s.TABLE_NAME  AND s.INDEX_NAME=t.CONSTRAINT_NAME  WHERE 0=0 AND t.CONSTRAINT_NAME IS NULL AND s.TABLE_SCHEMA = '<DB>' AND s.TABLE_NAME='<NAME>' GROUP BY name ORDER BY SEQ_IN_INDEX",
@@ -60,30 +60,41 @@ $DB_TYPES['mysql'] = array (
     ) as $k) { if (isset($r[$k])) $row["SLAVE.$k"] = $r[$k]; } return $row;
   },
   'SHOW STATUS' => create_function('&$r',join('',array(
-    'if (preg_match("/(Threads.*|Conn.*|Max_used_connections|Table_.*|Open.*_tables)/",$r["Variable_name"]))'
-      .'return array("STATUS.".$r[0]=>$r[1]);'
-    #'if (preg_match("/(Threads_connected|Max_used_connections|Table_locks_immediate|Table_locks_waited|Open.*_tables)/",$r[0])) return $r;',
+    'return array("STATUS.".$r[0]=>$r[1]);'
   ))),
   'SHOW GLOBAL STATUS' => create_function('&$r',join('',array(
-    'if (preg_match("/(Threads.*|Conn.*|Max_used_connections|Table_.*|Open.*_tables)/",$r["Variable_name"]))'
-      .'return array("GLOBAL.".$r[0]=>$r[1]);'
-    #'if (preg_match("/(Threads_connected|Max_used_connections|Table_locks_immediate|Table_locks_waited|Open.*_tables)/",$r[0])) return $r;',
+    'return array("GLOBAL.".$r[0]=>$r[1]);'
   ))),
   'SHOW VARIABLES' => create_function('&$r',join('',array(
-    'if (preg_match("/^(Table|.*[Cc]onn|Open|Com|hostname).*/",$r["Variable_name"]))'
-      .'return array("VARIABLES.".$r[0]=>$r[1]);'
+    'return array("VARIABLES.".$r[0]=>$r[1]);'
   ))),
 ),
 
 'status_callback' => function(&$status) {
-  #debug($status['GLOBAL.Open_tables']);
-  #debug($status['VAR.max_connections']);
-  $status['connections_pcent'] = ( ($status['STATUS.Max_used_connections']*100) / $status['VARIABLES.max_connections'] ).'%';
-  return;
-  foreach($status as $k=>$v) {
-    if (!preg_match('/^[A-Z]{3,}/',$k)) continue;
-    unset($status[$k]);
-    $status[$k] = $v;
+/*
+*/
+  // Conns
+  $status['max_connections'] = $status['VARIABLES.max_connections'];
+  $status['Max_used_connections'] = $status['STATUS.Max_used_connections'];
+  $status['connections_pcent'] = ( ($status['STATUS.Max_used_connections']*100) / $status['VARIABLES.max_connections'] );
+
+  // Innodb
+  $status['innodb_log_size_pcent'] = ( ($status['VARIABLES.innodb_log_file_size']*100) / $status['VARIABLES.innodb_buffer_pool_size'] );
+
+  // Cache
+  $status['query_cache_efficiency'] = sprintf("%.1f",($status['STATUS.Qcache_hits'] / ($status['STATUS.Com_select'] + $status['STATUS.Qcache_hits'])) * 100);
+  if ($status['VARIABLES.query_cache_size']) {
+    $status['query_cache_pcent'] = sprintf("%.1f",100 - ($status['STATUS.Qcache_free_memory'] / $status['VARIABLES.query_cache_size']) * 100);
+  }
+  if ($status['STATUS.Qcache_lowmem_prunes'] == 0) {
+    $status['query_cache_prunes_per_day'] = 0;
+  } else {
+    $status['query_cache_prunes_per_day'] = (int)($status['STATUS.Qcache_lowmem_prunes'] / ($status['STATUS.Uptime']/86400));
+  }
+
+  // Delete olters
+  foreach ($status as $k=>$v) {
+    if (preg_match('/^[A-Z]+\./',$k)) unset ($status[$k]);
   }
 },
 
-- 
2.47.3