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 elements to the processlist table: Id, User, Host, db, Command, Time, State, Info, where:
Id is the connection identifier
User is the mysql user who issued the statement
Host 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.
db is the database being used for the particular mysql statement or query.
Command 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.
Time is the delay between the original time of execution of the statement and the time the processlist is viewed
State is an action, event, or state of the specific mysql command and can be one of hundreds of different values.
Info will show the actual statement being run in that instance
another useful command is:
mysql> show full processlist;
which is equivalent to:
mysqladmin -u [adminuser] -p processlist;
this shows my specific query as:
| 4342233 | adminusername | localhost | NULL | Query | 0 | NULL | show full processlist |
or you can display each field in a row format (vertical format), like so, simply by appending \G to the end of the query:
mysql> show full processlist\G
this 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.
******** 55. row ********
Id: 4342233
User: adminusername
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show full processlist
you can also check how many mysql queries a user has open by running the following command:
mysqladmin -u [adminuser] -p pr | awk -F\| {‘print $3’} | sort -nk1 | awk -F_ {‘print $1’} |uniq -c |sort
to see which database has the most active queries, run the following:
mysqladmin -u [adminuser] -p pr | awk -F\| {‘print $3’} | sort -nk1 |uniq -c |sort
oh, and since it’s useful… here’s a recommend /etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
safe-show-database
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
max_user_connections=100
max_connections=350
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=64M
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
thread_concurrency=4
myisam_sort_buffer_size=64M
server-id=1
long_query_time=2
slave_net_timeout = 50
delayed_insert_timeout = 50
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
another fine tuning would include the following and is good for machines with plesk:
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 64M
thread_concurrency = 8
the above will help you optimize your mysql database as well, but the configuration isn’t for everyone.