{"id":95,"date":"2010-08-31T00:38:36","date_gmt":"2010-08-31T00:38:36","guid":{"rendered":"http:\/\/10sa.com\/sql_stories\/?p=95"},"modified":"2010-08-31T07:47:50","modified_gmt":"2010-08-31T07:47:50","slug":"too-many-connections-on-mysql","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=95","title":{"rendered":"Too many connections on MySQL"},"content":{"rendered":"<p>New problem on MySQL, called &#8220;tmc&#8221; &#8211; &#8220;Too Many Connections&#8221;. In case of environment with many db users, different applications\/services and load balancing the investigation is not simple. <\/p>\n<p>First of all I have to clarify some question: there are two types of such event.<\/p>\n<p>Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR) &#8211; this concerns global connections.<br \/>\nError: 1203 SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS) &#8211; this concerns only one user connections limit.<br \/>\nA very useful information is that your MySQL server limits number of connections to max_connections+1 (the extra connection is reserved for use by accounts that have the SUPER privilege).<\/p>\n<p>After some analysis I realized that problem is incoming from one of www servers so I had to trace it. Cause it was from Apache I was able to profit from module <a href=\"http:\/\/httpd.apache.org\/docs\/2.2\/mod\/mod_status.html\">&#8220;status_module&#8221;<\/a> (plse take care about ExtendedStatus On).<\/p>\n<p>Below I present ready-to-use script which catches list of process of database instance and apaches &#8211; it helped me a lot to identify the problem. As a result you additionaly obtain list of system processes of http user and further history of database process list (next 10 s).<\/p>\n<p>How to use it. Named it tmc.sh and bring it to cron with parameter keep-alive, e.g.:<br \/>\n*\/2 * * * * \/root\/scripts\/cron\/tmc.sh keep-alive > \/dev\/null 2>&#038;1<\/p>\n<pre lang=\"bash\">\r\n#!\/bin\/bash\r\n\r\n# @desc: Script is intended to find and log events when error 1040 (too many connections) occures.\r\n\r\nMYSQL_HOMEDIR=\"\/usr\/local\/services\/mysql\/bin\/\"\r\nMYSQL_CMD=\"mysql\"\r\n\r\n# User w\/o Super_priv\r\nMY_PASSWD=\"YourPassword\";\r\nMY_USER=\"YourUser\";\r\n\r\n# User with Super_priv\r\nROOT_PSSWD=\"RootPassword\";\r\nROOT_USER=\"root\";\r\n\r\nLOCKFILE=\"\/tmp\/tmc.lock\";\r\n\r\n# To be set\r\nHOMEDIR=\"\";\r\nLOGDIR=\"tmc\/\";\r\nfileExec=\"\";\r\n\r\n# Block functions\r\n\r\n# This function gives you more info about unix processes\r\n# until you put there to index.php file lines\r\n# <?php if ( isset ($_GET['ps']) ): system (\"ps aux\"); endif;\r\nfunction log_processes\r\n{\r\n    WGET_URL=http:\/\/$2\/Tester\/?ps\r\n    OUTPUT_FILE=$1\"\/\"$2\"_ps\";\r\n    \/usr\/bin\/wget -q $WGET_URL --output-document=$OUTPUT_FILE &#038;\r\n}\r\n\r\n\r\nfunction log_events \r\n{\r\n    WGET_URL=\"http:\/\/$2\/server-status\";\r\n    OUTPUT_FILE=$1\"\/\"$2;\r\n    \/usr\/bin\/wget -q $WGET_URL --output-document=$OUTPUT_FILE &#038;\r\n}\r\n\r\ncheck_pid(){\r\n    \r\n    PARENT_PID=$(($$ + 0 ));\r\n    DAEMON_PID=$(\/bin\/ps aux  | \/bin\/grep $fileExec | grep \"\/bin\" | awk -F \" \" '{print $2}');\r\n    ARR=($DAEMON_PID);\r\n    ARR_QTY=${#ARR[@]};\r\n    \r\n    ARR_PID=();\r\n    if [ \"$ARR_QTY\" -gt \"1\" ]; then\r\n\t\r\n\tINSTANCE_ITERATOR_PID=0;\r\n\tfor ((INSTANCE_ITERATOR=0;INSTANCE_ITERATOR<$ARR_QTY;INSTANCE_ITERATOR++)); do\r\n\t    xpid=${ARR[${INSTANCE_ITERATOR}]};\r\n\t    xpid=$(($xpid + 0));\r\n\t    if [ \"$xpid\" -gt \"$PARENT_PID\" ]; then\r\n\t\t# ommit pid from ps usage\r\n\t\tcontinue;\r\n\t    else\r\n\t\tINSTANCE_ITERATOR_PID=$(($INSTANCE_ITERATOR_PID+1));\r\n\t\tARR_PID[$INSTANCE_ITERATOR_PID]=$xpid\r\n\t    fi \r\n\tdone;\r\n    else\r\n\tDAEMON_PID=$(($DAEMON_PID+0));\r\n    fi\r\n\r\n    ARR_PID_QTY=${#ARR_PID[@]};\r\n    if [ \"$ARR_PID_QTY\" -eq 1 ]; then\r\n\treturn 0;\r\n    elif [ \"$ARR_PID_QTY\" -eq \"2\" ]; then\r\n\tDAEMON_PID=${ARR_PID[0]};\r\n\techo \"Working: worker &#038; checker thread.\";\r\n\treturn 1; \r\n    else\r\n\tDAEMON_PID=${ARR_PID[0]};\r\n\tLIMIT=$(($ARR_PID_QTY - 1));\r\n\tfor ((INSTANCE_ITERATOR=1;INSTANCE_ITERATOR<$LIMIT;INSTANCE_ITERATOR++)); do\r\n\t    MY_PID=${ARR_PID[${INSTANCE_ITERATOR}]};\r\n\t    if [ $MY_PID -ne \"$$\" ]; then\r\n\t\t# i am sure\r\n\t\t\/bin\/kill -9 $MY_PID;\r\n\t    fi\r\n\tdone;\r\n\treturn 1;\r\n    fi\r\n\r\n}\r\n        \r\n\r\nfunction set_homedir {\r\n    dirExec=`pwd`;\r\n    fileExec=`basename $1`;\r\n    fullPath=\"$dirExec\/$fileExec\";\r\n    if [ -f $fullPath ]; then\r\n        sh_tmp=$dirExec;\r\n    else\r\n        sh_tmp=`dirname $0`;\r\n    fi\r\n    echo $sh_tmp;\r\n}\r\n\r\nfunction check_all {\r\n    if [ ! -d \"$LOGDIR\" ]; then\r\n\tmkdir $LOGDIR;\r\n\tretval=$?;\r\n\tif [ \"$retval\" = \"1\" ]; then\r\n\t    return 0;\r\n\tfi\r\n    fi\r\n    return 1;\r\n}\r\n\r\nfunction start_process {\r\n\r\n    touch $LOCKFILE;\r\n    HOMEDIR=$(set_homedir $0);\r\n    LOGDIR=\"$HOMEDIR\/$LOGDIR\";\r\n    LOG_PS_CONTINOUS=0;\r\n    check_all;\r\n    retval=$?;\r\n    if [ \"$retval\" = \"0\" ]; then\r\n\techo \"Error has occured. End of script.\";\r\n\texit;\r\n    fi\r\n\r\n    while [ \"1\" = \"1\" ]; do\r\n    \r\n    CONNECTION_INFO=`$MYSQL_HOMEDIR$MYSQL_CMD -N -u$MY_USER --password=$MY_PASSWD  -e \"select 1 FROM dual;\" 2>&1`;\r\n    TOO_MANY_CONNECTION=`echo $CONNECTION_INFO | grep '1040' | sed 's\/\\s\/\/g'`;\r\n    if [ ! -z \"$TOO_MANY_CONNECTION\" ]; then\r\n\tsubdir=$(date +\"%Y%m%d_%H%M%S\");\r\n\tsubdir=$LOGDIR$subdir;\r\n\tmkdir $subdir;\r\n\t$MYSQL_HOMEDIR$MYSQL_CMD --protocol=tcp -N -u$ROOT_USER --password=$ROOT_PSSWD  -e \"SELECT * FROM information_schema.PROCESSLIST;\" > $subdir\/process_list 2>&1;\r\n\tlog_events $subdir \"192.168.1.20\";\r\n\tlog_events $subdir \"192.168.1.21\";\r\n\tlog_events $subdir \"192.168.1.22\";\r\n\tlog_events $subdir \"192.168.1.23\";\r\n\t\r\n\t\r\n\tlog_processes $subdir \"192.168.1.20\";\r\n\tlog_processes $subdir \"192.168.1.21\";\r\n\tlog_processes $subdir \"192.168.1.22\";\r\n\tlog_processes $subdir \"192.168.1.23\";\r\n\tLOG_PS_CONTINOUS=10;\r\n    fi\r\n\r\n    if [ \"$LOG_PS_CONTINOUS\" -gt \"0\" ]; then\r\n\tpostfix=$(date +\"%M%S\");\r\n\t$MYSQL_HOMEDIR$MYSQL_CMD --protocol=tcp -N -u$ROOT_USER --password=$ROOT_PSSWD  -e \"SELECT * FROM information_schema.PROCESSLIST;\" > $subdir\/process_list_$postfix 2>&1;\r\n\t((LOG_PS_CONTINOUS--));\r\n    fi;\r\n\r\n    if [ ! -f $LOCKFILE ]; then\r\n\tbreak;\r\n    fi\r\n\r\n    sleep 1;\r\n    done;\r\n}\r\n\r\nfunction kill_process {\r\n    info=\"\";\r\n    rm -v -f $LOCKFILE;\r\n    sleep 2;\r\n    check_pid;\r\n    retval=$?;\r\n    if [ \"$retval\" -gt \"0\" ]; then\r\n\tkill -9 $DAEMON_PID;\r\n\tinfo=\"Proces was killed\";\r\n    else \r\n\tinfo=\"Process was stopped\";\r\n    fi;    \r\n    echo $info;    \r\n}\r\n\r\ncase \"$1\" in\r\n    status)\r\n        check_pid;\r\n        echo $?;\r\n        ;;\r\n    'kill')\r\n\tkill_process;\r\n\t;;\r\n    keep-alive)\r\n\tcheck_pid;\r\n\tretval=$?;\r\n\tif [ \"$retval\" = \"0\" ]; then\r\n\t    start_process;\r\n\tfi\r\n\t;;\r\n    *)\r\n    echo \"Usage: $0 (status|kill|keep-alive)\"\r\n    ;;\r\nesac\r\n\r\nexit 0;\r\n<\/pre>\n<p>You can kill this process with option &#8220;kill&#8221;.<br \/>\nEnd.<\/p>\n<p>ps. in my case the cause was in php <a href=\"http:\/\/php.net\/manual\/en\/function.mysql-connect.php\">mysql_connect<\/a> function with option new_link set on true.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>New problem on MySQL, called &#8220;tmc&#8221; &#8211; &#8220;Too Many Connections&#8221;. In case of environment with many db users, different applications\/services and load balancing the investigation is not simple. First of all I have to clarify some question: there are two types of such event. Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR) &#8211; this concerns global connections. Error: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[20],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/95"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=95"}],"version-history":[{"count":17,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/95\/revisions"}],"predecessor-version":[{"id":113,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/95\/revisions\/113"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=95"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=95"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=95"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}