aaronbbrown

#!/bin/bash

U=user
P=pass
MYSQLCMD="mysql -u $U -p$P -BN -e"

while : ; do
  LAG=$($MYSQLCMD "select now()-ts as behind_by from maatkit.heartbeat limit 1;" | awk -F. '{print $1}')
  echo "$(date) $LAG"
  if [[ $LAG -gt 120 ]]; then

aaronbbrown

#!/bin/bash
U=user
P=pass
MYSQLCMD="mysql -u $U -p$P -e"
SECS=60

OLDLOGPOS=1
while : ; do
  LOGPOS=$($MYSQLCMD "SHOW SLAVE STATUS\G" | grep Exec_Master_Log_Pos | awk '{print $2}')
  echo "$(date) pos: $LOGPOS old pos: $OLDLOGPOS"

aaronbbrown

                                 My traceroute  [v0.80]anders.borg.lan (0.0.0.0)                                                                                                             Fri Feb  3 10:51:27 2012
Keys:  Help   Display mode   Restart statistics   Order of fields   quit
                                                                                                                      Packets               Pings
 Host                                                                                                               Loss%   Snt   Last   Avg  Best  Wrst StDev
 1. router.borg.lan                                                                                                  0.0%     7    1.1   1.1   0.7   1.4   0.2
 2. 10.117.128.1                                                                                                    83.3%     7    9.1   9.1   9.1   9.1   0.0
 3. vlan109.que7600-pv-1.twalb.com                                                                                

aaronbbrown

                                            My traceroute  [v0.80]gaeta-wifi.borg.lan (0.0.0.0)                                                         Fri Feb  3 19:08:50 2012
Resolver: Received error response 2. (server failure)er of fields   quit
                                                                      Packets               Pings
 Host                                                               Loss%   Snt   Last   Avg  Best  Wrst StDev
 1. router.borg.lan                                                  0.0%    95    1.3   1.6   1.0  10.1   1.2
 2. 10.117.128.1                                                    44.2%    95    7.5  12.9   6.6  49.0   7.2
 3. vlan109.que7600-pv-1.twalb.com                                  38.9%    95    9.9  17.1   7.0  94.2  15.0

aaronbbrown

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO <> 'NULL' ORDER BY TIME;
*************************** 1. row ***************************
           ID: 19210373
         USER: me
         HOST: localhost
           DB: production
      COMMAND: Query
         TIME: 0
        STATE: executing
         INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO <> 'NULL' ORDER BY TIME

aaronbbrown

83109   production.table Locked - write        High priority write lock
83109   production.table Locked - read         Low priority read lock

aaronbbrown

session1> SELECT RUN_LONG_TIME FROM table;
session2> ANALYZE TABLE table;
session3> SELECT * FROM table WHERE id=123; 

aaronbbrown

session1> SELECT RUN_LONG_TIME FROM table;
session2> SELECT * FROM table WHERE id = 123

aaronbbrown

------- TRX HAS BEEN WAITING 42 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `db`.`table` trx id 4617 lock mode AUTO-INC waiting
------------------
TABLE LOCK table `db`.`table` trx id 4617 lock mode AUTO-INC waiting

aaronbbrown

$ (for x in {1..100}; do echo 'select * from departments d where (select count(*) from dept_emp de where d.dept_no = de.dept_no) = 0;'; echo; done ) | ./mk-query-profiler  -u root --database=employees 

+----------------------------------------------------------+
|                     100 (0.1104 sec)                     |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                       11.596
   Questions                               100
     COMMIT                                  0