Ubuntu – TIME column in TOP command for thesql

MySQLtopUbuntu

When I run top on my database server I get that mysqld has been running for 4:00.51 and it continues to go up.

I assume this means that one process with mysql has been running this long from other posts on here. Its not set to cumulative mode as best I can tell as the heading looks like it would change to CTIME if that be the case.

What I'm wondering is if this is normal for a site that makes a lot of individual connections using PHP. I shouldn't have any long running processes that would hold on to a mysql connection for this long, only seconds at most.

Am I incorrect to assume that this time relates to one connection/process running? I think usually I see it flash up and away on the TOP, not just stay there with this number increasing.

Best Answer

When you look at top and see mysqld, there should only be one mysqld per instance. Older versions of server-compiled mysqld spawn many threads. I'll assume you are not using such.

To see individual connections you could do one of two things

In mysql client, run SHOW PROCESSLIST; (Source of incoming connection in the third column Host)

In the OS, run

  • netstat | grep -i mysql
  • netstat | grep 3306

For emphasis, you should only see one mysqld process per instance.

If you want to double check the time value as seen in top, ask mysqld

If you have MySQL 5.1+, run this:

SELECT 
    TRIM(REPLACE(CONCAT(
        IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),
        IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs  '))),
        IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),
        IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),'  ',' '))
    TimeDisplay
FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc
FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa
FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa
FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
FROM
    (
        SELECT variable_value sec_a
        FROM information_schema.global_status
        WHERE variable_name = 'Uptime'
    )
A) AA) AAA) AAAA) B;

If you have MySQL 5.0, run this

MYSQL_CONN="-uroot -ppassword"
UPTIME_SEC=`mysql ${MYSQL_CONN} -A -N -e"SHOW GLOBAL STATUS LIKE 'Uptime'" | awk '{print $2}'`
SQLTIME="SELECT TRIM(REPLACE(CONCAT("
SQLTIME="${SQLTIME} IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),"
SQLTIME="${SQLTIME} IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs  '))),"
SQLTIME="${SQLTIME} IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),"
SQLTIME="${SQLTIME} IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),'  ',' ')) TimeDisplay"
SQLTIME="${SQLTIME} FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc"
SQLTIME="${SQLTIME} FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa"
SQLTIME="${SQLTIME} FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa"
SQLTIME="${SQLTIME} FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa"
SQLTIME="${SQLTIME} FROM (SELECT ${UPTIME_SEC} sec_a ) A) AA) AAA) AAAA) B"
MYSQL_START_TIME=`mysql ${MYSQL_CONN} -A -N -e"${SQLTIME}"`
echo ${MYSQL_START_TIME}

This will give you the number of seconds mysqld has been running.

If you want to see the date and time mysqld started

MySQL 5.1+

select (now() - interval variable_value second) MySQLStartTime from
information_schema.global_status where variable_name='uptime';

MySQL 5.0

MYSQL_CONN="-uroot -ppassword"
UPTIME_SEC=`mysql ${MYSQL_CONN} -A -N -e"SHOW GLOBAL STATUS LIKE 'Uptime'" | awk '{print $2}'`
MYSQL_START_TIME=`mysql ${MYSQL_CONN} -A -N -e"SELECT (NOW() - INTERVAL ${UPTIME_SEC} SECOND) MySQLStartTime"`
echo ${MYSQL_START_TIME}

Give it a Try !!!

Related Topic