admin list shows newest users first

cron daemon to check slow queries

git-svn-id: svn://presbrey.mit.edu/sql/mitsql@72 a142d4bd-2cfb-0310-9673-cb33a7e74f58

diff --git a/admin/main.php b/admin/main.php
index bdd80236f47a4586bc5c35760b8b54b6dc9f1258..0852432155ab093d2d6e5191a4967e792a3603c7 100755 (executable)
--- a/admin/main.php
+++ b/admin/main.php
@@ -17,7 +17,8 @@ $sql = sprintf("SELECT User.UserId,Username,Name,dSignup,dLastCheck,nBytes,nByte
                                NATURAL JOIN UserStat
                                NATURAL JOIN UserQuota
                                WHERE bEnabled=1
-                               ORDER BY dSignup");
+                               ORDER BY dSignup DESC");
+//                             ORDER BY nBytes DESC");
 $users = fetchRows(DBSelect($sql),'UserId');
 
 include 'tpl/head.php';

diff --git a/cron/checkProcesses.php b/cron/checkProcesses.php
new file mode 100755 (executable)
index 0000000..b0ce051
--- /dev/null
+++ b/cron/checkProcesses.php
@@ -0,0 +1,61 @@
+<?php
+
+chdir('../');
+require_once('mitsql.cfg.php');
+require_once('mitsql.lib.php');
+define('MAX_PROC_TIME', 10);
+
+echo '<pre>';
+
+$baddbs = $badusers = $bad = array();
+$result = mysql_query('SHOW FULL PROCESSLIST');
+while ($row = mysql_fetch_assoc($result)){
+       if ($row['Time']>MAX_PROC_TIME && $row['Command']!='Sleep') {
+               $bad[] = $row;
+               $baddbs[] = $row['db'];
+               $badusers[] = $row['User'];
+       }
+}
+mysql_free_result($result);
+
+foreach($bad as $badproc) {
+       $mailtos = $mailnames = array();
+       $baddb = mysql_escape_string($badproc['db']);
+       $badtime = $badproc['Time'];
+       $badquery = $badproc['Info'];
+       mysql_query('KILL '.$badproc['Id']);
+       if (empty($baddb)) continue;
+       $sql = "SELECT User.UserId,User.Name,User.Email
+                       FROM `User`
+                       NATURAL JOIN DBOwner
+                       NATURAL JOIN DB
+                       WHERE DB.Name = '$baddb'";
+       $r = fetchRows(DBSelect($sql),'UserId');
+       foreach($r as $addy) {
+               $mailtos[] = $addy['Email'];
+               $mailnames[] = $addy['Name'];
+       }
+       $mailto = implode(', ',$mailtos);
+       $mailname = implode(', ',$mailnames);
+       $mailsubj = "[sql] Slow Query on $baddb";
+       $mailbody = "Dear $mailname:
+
+A slow query was found on your database: $baddb
+It took $badtime seconds and has been aborted.
+
+We do not allow inefficient SQL queries to run this long to conserve server
+resources. Your query has been appended to this message for your records.
+Please optimize your queries to avoid having your query killed in the
+future. If you have any questions, please contact sql@mit.edu.
+
+The SQL Service is available at http://sql.mit.edu/
+
+---------------------------------------------------------------------------
+
+$badquery";
+
+       $mailto = 'jwp@mit.edu';
+       mail($mailto,$mailsubj,$mailbody,"From: SQL Service <sql@mit.edu>\r\nBcc: sql@mit.edu\r\n");
+}
+
+?>

diff --git a/global.act.php b/global.act.php
index 94e662d5af48dad487532fd0b4077d516142332f..467282be760f9ebf83b7803a5aea32db2fb090ed 100644 (file)
--- a/global.act.php
+++ b/global.act.php
@@ -48,4 +48,5 @@ if (isPost() || isset($i_refresh)) {
        checkQuotas($UserId);
        isset($i_refresh) && redirect('main');
 }
+
 ?>