Something like mtop, but showing whole sql sentences in action?

[luuuciano]

Hi!

I am trying to catch a mod slowness under certain circumstances... tried using mtop, and it is cool... but just shows active sql sentences in 1 line, so if the sentences is longer it just not show it full...

Any idea how can I get those full sql sentences?

[luuuciano]

I have found this
http://forge.mysql.com/tools/tool.php?id=38

[butch2k]

You can always activate MySQL General Log to catch all queries going on if you have full control of your mysql server.

[luuuciano]

You can always activate MySQL General Log to catch all queries going on if you have full control of your mysql server.

Well, I think I did it, but nothing is being logged... maybe something is wrong...
I have added
log-bin=/var/log
log=/var/log
log-error=/var/log
log-slow-queries=/var/log
to the my.cnf file...
But maybe I need to add something on the starter script at /etc/rc.d/mysqld too?

Anyway, I have no idea what happens... mysqld just eats 100% cpu 95% of the pages loaded.... (I am trying pmxsubforums mod on a clean smf installation, but using the db information of another website running)

[butch2k]

 
Definitely wrong indeed !!!!
The log-bin is not a log per se but used for replication purposes, you should put the options back to standard value asap !

The two values on interest for you are those related to general-query-log:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

Keep in mind that in an active server it might consume a bit of ressources, so best to use it only for debugging purposes

[LiroyvH]

Code Select Expand


mysql show processlist\G


[butch2k]

Show processlist is not of much use since you can only catch long running queries, those lasting a fraction of second won't be catched unless you are very lucky.

[luuuciano]

Definitely wrong indeed !!!!
The log-bin is not a log per se but used for replication purposes, you should put the options back to standard value asap !

The two values on interest for you are those related to general-query-log:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

Thanks, done

btw, this is the mysqld start script (on an archlinux distribution)

Code Select Expand

#!/bin/bash

# general config
. /etc/rc.conf
. /etc/rc.d/functions

getPID() {
   echo $(pgrep -u mysql mysqld 2>/dev/null);
}

case "$1" in
  start)
    stat_busy "Starting MySQL Server"
    [ ! -d /var/run/mysqld  ] && install -d -g mysql -o mysql /var/run/mysqld &>/dev/null
    if [ -z "$(getPID)" ]; then
       /usr/bin/mysqld_safe --user=mysql -l &>/dev/null &
      if [ $? -gt 0 ]; then
        stat_fail
        exit 1
      else
        timeo=30
        while [ $timeo -gt 0 ]; do
          response=`/usr/bin/mysqladmin -uUNKNOWN_USER ping 2>&1` && break
          echo "$response" | grep -q "mysqld is alive" && break
          sleep 1
          let timeo=${timeo}-1
        done
        if [ $timeo -eq 0 ]; then
          stat_fail
          exit 1
        else
          echo $(getPID) > /var/run/mysqld/mysqld.pid
          add_daemon mysqld
          stat_done
        fi
      fi
    else
      stat_fail
      exit 1
    fi
    ;;

  stop)
    stat_busy "Stopping MySQL Server"
    if [ ! -z "$(getPID)" ]; then
      timeo=30
      kill $(getPID) &> /dev/null
      if [ $? -gt 0 ]; then
        stat_fail
        exit 1
      fi
      while [ ! -z "$(getPID)" -a $timeo -gt 0 ]; do
        sleep 1
        let timeo=${timeo}-1
      done
      if [ -z "$(getPID)" ]; then
        rm -f /var/run/mysqld/mysqld.pid &>/dev/null
        rm_daemon mysqld
        stat_done
      else
        stat_fail
        exit 1
      fi
    else
      stat_fail
      exit 1
    fi
    ;;

  restart)
    $0 stop
    $0 start
    ;;
  *)
    echo "usage: $0 {start|stop|restart}"
esac
exit 0

Where should I add --general-log? (temporally)


EDIT: here http://www.simplemachines.org/community/index.php?topic=451804.msg3287653#msg3287653 feline found the guilty sql query

[lemmy28]

help me webhost says my    /usr/bin/php /home/lemmy28/public_html/index.php is eating too many resources that they might suspend me what could be the cause of this

[luuuciano]

Wich is your hosting? too cheap? your forum is visited/used a lot?

Search the thread about doing things to decrease the cpu usage on a smf forum