Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The following usage for mysqld doesn't seem right, and that time stamp! Anyone got any ideas why it is going crazy like that?

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25662 mysql     16   0  139m  30m 5256 S 88.9  1.7 312:17.23 mysqld
20200 apache    16   0  102m  14m 7548 S  5.0  0.8   0:01.42 httpd

I am a total newbie when it comes to mysql, Apache and Lighttpd configurations sure, but mysql totally clueless. Below is my my.conf. I am running on a machine with 2GB of memory and using InnoDB, connecting via PDO.

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Totally default to start from scratch and avoid errors, help! :)

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

You should definitely look at your Processlist and see which query is so slow.

But from your "top" screenshout I would recommend the following changes in your my.cnf file

[mysqld]
thread_cache_size = 64
table_cache = 64
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8

You might need to do a bit of tweaking here and there next time you run into this problem, but these settings are quite good for the immediate problem.

share|improve this answer
add comment

First, use the mysql or mysqladmin command to access your server, and use it to run 'show processlist' to check which queries are currently running.

For example: mysqladmin -u root -p processlist. This will prompt you for the password of your mysql root user (not the same as your regular root account), and show you the currently running queries.

Also, a little tuning of your settings might be in order. I've found the mysqltuner.pl script handy to give me pointers on where to start. Remember, it's just a start though. Spending a few hours to learn the basics of mysql performance tuning is a wise investment on such a heavy loaded server.

share|improve this answer
    
The gui tools can also give you a look at whats going on with the server. dev.mysql.com/downloads/gui-tools/5.0.html –  XTZ Jul 1 '09 at 13:18
add comment

I'd run a "show processlist" and see what kind of queries you've got running. It's obviously been sitting there chewing processor for some time.

share|improve this answer
    
You might find mytop of interest to see which queries are running the longest. –  David Pashley Jun 29 '09 at 17:13
    
How do I do this? Simply "show processlist" in SSH doesn't work for me. –  James Jun 29 '09 at 17:14
    
@James: Have a look at Martijn's comment - He's giving you the syntax for the command, assuming you have the mysql root password (not necessarily the same as the box's root password). –  Evan Anderson Jun 29 '09 at 17:30
add comment

Turn on the general query log during QA, and do some explains on the queries you capture. One of the most common things that I find web developers do to my databases is sort without an index. Another is to put a bunch of pointless indexes on a table that gets frequent insert/update/deletes. Adding indexes that never get used just hurts your write performance. There is a lot of info here, but it is worth applying.

I would also suggest installing and monitoring sar/sysstat.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.