0

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

4 Answers 4

1

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.

2

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.

1
0

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.

3
  • You might find mytop of interest to see which queries are running the longest. Jun 29, 2009 at 17:13
  • How do I do this? Simply "show processlist" in SSH doesn't work for me.
    – James
    Jun 29, 2009 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). Jun 29, 2009 at 17:30
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .