Low Cpu Usage in MySQL

Question

I have one dedicated server on Amazon EC2, running mysql5 in a custom ami based on alestic debian lenny, in a small instance.

Small Instance (default)* 1.7 GB memory 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit) 160 GB instance storage (150 GB plus 10 GB root partition) 32-bit platform I/O Performance: Moderate

The problem is that CPU usage by MySQL never is more than 45%, with query that should use all aviable CPU, and makes queries slows.

One common query is a select where like "%word% for over 2kk rows.

The other process running on this machine is hyperic-hq-agent. Any idea to increment

my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking


key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 128M
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
transaction-isolation = READ-COMMITTED

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M


[isamchk]
key_buffer              = 16M

Answer 1

The query is done in localhost, with command line mysql client. The database size is 300 Mb, so i think all database is buffered because mysql memory usage is about 780 mb.

I found the problem.

The cpu, is just 1 Virtual Unit that correspond to almos 40% of current cpu.

The sar stats shows this.

01:36:15 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:36:16 PM     all     43.14      0.00      0.00      0.00     56.86      0.00
01:36:17 PM     all     42.16      0.00      0.00      0.00     57.84      0.00
01:36:18 PM     all     42.45      0.00      0.00      0.00     57.55      0.00
01:36:19 PM     all     25.00      0.00      0.00      0.00     29.00     46.00
01:36:20 PM     all      0.00      0.00      0.00      0.00      0.00    100.00
01:36:21 PM     all      0.00      0.00      0.00      0.00      0.00    100.00
01:36:22 PM     all      0.00      0.00      0.00      0.00      0.00    100.00

The %steal is reserved by hypervisor to server or serving other virtual CPU's.

So, the maximum is showed by 40%.

Answer 2

Sometimes there are things preventing a process from being able to reach full CPU. In your example, as it's a fairly simple query with potentially a lot of data, the bottleneck might be bandwidth in sending the results back to the requesting server - MySQL may never be reaching 100% CPU because it's already sending data out as fast as it can.