MySQL processes slowing down host server

Site is overloading the server

MySQL InnoDB database with approx 2gb data. Quite a few sleeping MySQL processes that could be the cause.

Before drilling down in code I would like to make few tweaks to mysql db configuration.Reduce the MySQL wait_timeout value to 3 seconds and increasing the number of allowed connections.Apparently, persistent connections do not currently play nice with transactions with InnoDB.

Mysql configuretion file my.cnf needs following parameters to changewait_timeout = 30 #30 secondsIncrease the number of allowed connection.

Here is the script to check if there is a MySQL sleeping process from long kill them and write log

#!/usr/bin/php -q


$link = mysql_connect (“localhost”, “root”, “password”);

$query = “show processlist”;

$result = mysql_query($query) or die (“failed $query”);

while ($row = mysql_fetch_array($result))
    if (($row['Time'] >1000 && $row['Command'] == “Sleep”) && ($row['User'] != “Root”))
        $killed = 0;
        mysql_query(“kill “.$row['Id']) or ($killed = 1);
        if ($killed = 1)
            “Killed process number “.$row['Id'].”, with a User of “.$row['User']
            .” in database “.$row['db'].”, which had a time of “.$row['Time']
            .”, and a command of “.$row['Command'].”\r\n\r\n”;



MySQL on Mac OS X

MySQL on Mac OS X 10.7.3

Installation & configuration

Download the mac based tar or dmg from MySQL website

Add group mysql

Add user mysql who’s group is mysql

shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db –user=mysql
shell> chown -R root .
shell> chown -R mysql data


bin/mysqld_safe –user=mysql &

Do not forget to change the root password

bin/mysqladmin -u root password ‘secure-phrase’

MYSQL replication in the same box

MYSQL replication in the same box


MYSQL 5.0.17 standard (SLAVE) –

MYSQL 4.1.1 standard (MASTER) – MASTER running on port 3306 SLAVE running on port 3308 MASTER (my.cnf)
server-id = 1
log-bin SLAVE (my.cnf)
server-id = 2
master-host = localhost
master-user = root
master-password = mysql
master-port = 3306

replicate-do-db = dbrep

to check the MASTER status on mysql cosole execute this querymysql > SHOW MASTER STATUS;

mysql > grant replication slave, replication client,file,super,reload,select on *.* to root@’%hostname%’ identified by ‘mysql’; to check the SLAVE status on mysql cosole execute this querymysql > SHOW SLAVE STATUS;

mysql > grant all on *.* to root@%hostname% identified by ‘mysql’; mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’master_user_name’,

-> MASTER_PASSWORD=’master_pass’,

-> MASTER_LOG_FILE=’recorded_log_file_name’,

-> MASTER_LOG_POS=recorded_log_position;
Above mentioned query can be used to chnage the slave info at runtime. restart both MYSQL SLAVE & MASTER On slave execute the command
mysql > start slave; Now create the database at MASTER and start creating tables and inserting values into it. Check the slave it started replicating all those tables. Isn’t it simple. )