mysqld problems

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! :)

From serverfault James

• 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.

  David Pashley : You might find mytop of interest to see which queries are running the longest.

  James : How do I do this? Simply "show processlist" in SSH doesn't work for me.

  Evan Anderson : @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).
  From Evan Anderson

• 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.

  XTZ : The gui tools can also give you a look at whats going on with the server. http://dev.mysql.com/downloads/gui-tools/5.0.html
  From Martijn Heemels

• 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.

  From Richard Bronosky

• 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.

  From Jonathan