{"id":26,"date":"2006-09-18T14:15:13","date_gmt":"2006-09-18T14:15:13","guid":{"rendered":"http:\/\/ramblingsofasysadmin.com\/blog\/?p=26"},"modified":"2006-09-18T14:15:13","modified_gmt":"2006-09-18T14:15:13","slug":"showing-and-understanding-mysql-processes-in-detail","status":"publish","type":"post","link":"https:\/\/ramblingsofasysadmin.com\/?p=26","title":{"rendered":"showing and understanding mysql processes in detail."},"content":{"rendered":"

i’ve learned a little trick on how to determine how your mysql server is running and where to pinpoint problems in the event of a heavy load. this is useful in determining how you might want to proceed in terms of mysql optimization.
\n# mysql -u [adminuser] -p
\nmysql> show processlist;<\/font>
\ngranted, on a server with heavy volume, you might see hundreds of rows and it will scroll off the screen. here are the key elements to the processlist table: Id, User, Host, db, Command, Time, State, Info, where:
\nId<\/b> is the connection identifier
\nUser<\/b> is the mysql user who issued the statement
\nHost<\/b> is the hostname of the client issuing the statement. this will be localhost in almost all cases unless you are executing commands on a remote server.
\ndb<\/b> is the database being used for the particular mysql statement or query.
\nCommand<\/b> can be one of many different commands issued in the particular query. the most common occurrence on a webserver is “Sleep,” which means that the particular database connection is waiting for new directions or a new statement.
\nTime<\/b> is the delay between the original time of execution of the statement and the time the processlist is viewed
\nState<\/b> is an action, event, or state of the specific mysql command and can be one of hundreds of different values.
\nInfo<\/b> will show the actual statement being run in that instance
\nanother useful command is:
\nmysql> show full processlist;<\/font>
\nwhich is equivalent to:
\nmysqladmin -u [adminuser] -p processlist;<\/font>
\nthis shows my specific query as:
\n| 4342233 | adminusername | localhost | NULL | Query | 0 | NULL | show full processlist |<\/font>
\n<\/font>
\nor you can display each field in a row format (vertical format), like so, simply by appending \\G<\/font> to the end of the query:
\nmysql> show full processlist\\G<\/font>
\nthis list is very likely preferable in the event that your data scrolls off the screen and you want to find out the specific field name of a value in your database.
\n******** 55. row ********
\nId: 4342233
\nUser: adminusername
\nHost: localhost
\ndb: NULL
\nCommand: Query
\nTime: 0
\nState: NULL
\nInfo: show full processlist
\n<\/font>
\nyou can also check how many mysql queries a user has open by running the following command:
\nmysqladmin -u [adminuser] -p pr | awk -F\\| {‘print $3’} | sort -nk1 | awk -F_ {‘print $1’} |uniq -c |sort<\/font>
\nto see which database has the most active queries, run the following:
\nmysqladmin -u [adminuser] -p pr | awk -F\\| {‘print $3’} | sort -nk1 |uniq -c |sort<\/font>
\noh, and since it’s useful… here’s a recommend \/etc\/my.cnf<\/font>:
\n
\n[mysqld]
\ndatadir=\/var\/lib\/mysql
\nskip-locking
\nskip-innodb
\nsafe-show-database
\nquery_cache_limit=1M
\nquery_cache_size=64M
\nquery_cache_type=1
\nmax_user_connections=100
\nmax_connections=350
\ninteractive_timeout=10
\nwait_timeout=20
\nconnect_timeout=20
\nthread_cache_size=128
\nkey_buffer=64M
\njoin_buffer=1M
\nmax_connect_errors=20
\nmax_allowed_packet=16M
\ntable_cache=1024
\nrecord_buffer=1M
\nsort_buffer_size=2M
\nread_buffer_size=2M
\nread_rnd_buffer_size=2M
\nthread_concurrency=4
\nmyisam_sort_buffer_size=64M
\nserver-id=1
\nlong_query_time=2
\nslave_net_timeout = 50
\ndelayed_insert_timeout = 50
\n[mysql.server]
\nuser=mysql
\nbasedir=\/var\/lib
\n[safe_mysqld]
\nerr-log=\/var\/log\/mysqld.log
\npid-file=\/var\/lib\/mysql\/mysql.pid
\nopen_files_limit=8192
\n[mysqldump]
\nquick
\nmax_allowed_packet=16M
\n[mysql]
\nno-auto-rehash
\n[isamchk]
\nkey_buffer=64M
\nsort_buffer=64M
\nread_buffer=16M
\nwrite_buffer=16M
\n[myisamchk]
\nkey_buffer=64M
\nsort_buffer=64M
\nread_buffer=16M
\nwrite_buffer=16M
\n[mysqlhotcopy]
\ninteractive-timeout
\n<\/font>
\nanother fine tuning would include the following and is good for machines with plesk:
\n
\nskip-locking
\nkey_buffer = 128M
\nmax_allowed_packet = 1M
\ntable_cache = 512
\nsort_buffer_size = 2M
\nread_buffer_size = 2M
\nread_rnd_buffer_size = 8M
\nmyisam_sort_buffer_size = 64M
\nthread_cache_size = 8
\nquery_cache_size = 64M
\nthread_concurrency = 8
\n<\/font>
\nthe above will help you optimize your mysql database as well, but the configuration isn’t for everyone.<\/p>\n","protected":false},"excerpt":{"rendered":"

i’ve learned a little trick on how to determine how your mysql server is running and where to pinpoint problems in the event of a heavy load. this is useful in determining how you might want to proceed in terms of mysql optimization. # mysql -u [adminuser] -p mysql> show processlist; granted, on a server with heavy volume, you might see hundreds of rows and it will scroll off the screen. here are the key …<\/span> Read More →<\/span><\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-26","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=\/wp\/v2\/posts\/26","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=26"}],"version-history":[{"count":0,"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=\/wp\/v2\/posts\/26\/revisions"}],"wp:attachment":[{"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ramblingsofasysadmin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}