1

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

migrated from stackoverflow.com Oct 20 '09 at 19:06

This question came from our site for professional and enthusiast programmers.

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

  • Seems like that could also be showing that they're not allowing you to use more? – LapTop006 Oct 27 '09 at 8:10
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy