Should CPU usage be so low while memory usage is high on heavy mysql server?

Question

I'm using ubuntu server 12.04 x64. The server receives a lot of requests to mysql through apache web server (it's light interface). From mysql stats I see there is approx 250 queries / second from 8 AM - 9PM. At night server is practically not used. RAM is mostly used by mysql (according to mysqloptimizer maximum MySQL ram usage is 25GB). Top confirms it - mysql uses about 77% of RAM.

The database size is about 20GB. One table that is under heaviest fire has about 1-2 mln records (mainly id fields from few tables plus some smallints counters).

I attached image ilustrating parts that are interesting for me(the second memory images shows memory behavior after server restart).

While watching munin I came up with few questions:

1. Why in memory useage apps memory practically never falls? Shouldn't it decrease e.g. at night when there's no traffic on the server?

2. Wouldn't it be better to somehow tweak it make more memory space for cache?

3. Why CPU usage is so low while RAM is full?

4. Level of inactive memory is also rising and it worries me a little.

Those questions came up because occasionally the traffic rises significantly for short time and in those moments server is not answering to a lot of requests. But at those moments RAM/CPU or even disk latency is not rising to much, maybe a little. Those traffic rises are unavoidable but I'm not sure whether replacing the server with stringer one will do it or maybe only put more RAM (as CPU usage is minimal)?

If this question is unanswerable - sorry.

EDIT: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
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
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1

#
# * Fine Tuning
#
key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 64
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 400
table_cache            = 6000
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
slow_query_log = 0
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 20
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size = 512M
innodb_buffer_pool_size = 24G
bulk_insert_buffer_size = 256M
innodb_open_files = 6000
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_file_io_threads = 4
thread_cache_size = 64
thread_concurrency = 12
query_cache_size = 64M
query_cache_limit = 2M

max_sp_recursion_depth = 50
thread_stack = 384K

tmp_table_size = 64M
max_heap_table_size = 64M
key_buffer_size = 32M

event_scheduler = ON

lower_case_table_names = 1

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Answer 1

Databases (well, 99% of them) cache everything they can. Cache is good. It makes stuff go faster. You want your database server to eat up all the memory it can, and never release it unless it have to.

It won't cache stuff unless you ask it to (like in a query). Maybe there are tools to make MySQL pre-cache data, but I have no idea. Newer database products is beginning to get lots of in-memory features, where you can select certain data to always be present in memory.

CPU usage does not correlate to RAM usage. Your queries could be extremely lightweight, like only fine-grained SELECTS which hits a index every single time. In other words, the database will serve you cached data which resides in RAM, so there would be very little CPU work to be done (like scheduling I/O to retrieve and process data from disk).

To me, this looks like a very healthy and fast performing database. There really is no problems at all. If you have free memory then it just means that the database doesn't need to cache more data, as it's never being requested.

EDIT: You've limited the InnoDB pool to 24GB, which is excellent since your database is 20GB. If the database increases then take care to increase the buffer pool size. You also mention that you are worried by the decrease of available memory - this should only be a concern if other processes than the database is consuming it. The database server will not grow beyond what you've limited it to.