I have lots of free memory. How do I use it to increase performance?

I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM.

Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how do i make the vps use more RAM?

my.cnf:

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer                                      = 512M
max_allowed_packet                              = 8M
table_cache                                     = 4096
sort_buffer_size                                = 16M
read_buffer_size                                = 8M
join_buffer_size                                = 2M
read_rnd_buffer_size                    = 32M
myisam_sort_buffer_size                 = 128M
myisam_repair_threads                   = 1
myisam_recover
max_heap_table_size                             = 8M
tmp_table_size                                  = 8M
thread_cache_size                               = 8
thread_concurrency                              = 8
query-cache-type                                = 1
query_cache_limit                               = 4M
query-cache-size                                = 16M

log-slow-queries=/var/log/mysqlslowqueries.log
local-infile = 0
wait_timeout = 10
interactive_timeout = 15
max_connections = 35

character-set-server            = utf8
default-character-set           = utf8

skip-networking
skip-federated
skip-symbolic-links

skip-innodb
[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqld_safe]
open-files-limit = 8192

I actually do need to increase MySQL performance because it's the most cpu-consumming process and there are

Memory can help you by caching and thus reducing I/O.

However, that won't reduce CPU usage which is your problem. This is an unusual bottleneck, as CPUs are insanely fast for most database work and I/O tends to be the bottleneck.

In your case, it is even more inusual, because you have 16-cores and VPSs tend not to have great I/O performance. First of all, make sure that you are CPU bound. vmstat should help here.If you have high numbers on the wa column, you are probably I/O bound; high numbers on the us column could indicate that you are really CPU bound.

If you are CPU bound, analyze what queries are you executing and why they take long to complete. You are either executing a lot of queries/s or they include complex calculations (i.e. aggregates, functions, etc.). The solution for the former is usually caching on the frontend, which means executing less queries. The latter is solved by simplifying your queries (if possible- you might have queries which are needlessly complex) and calculating stuff once and reusing it (say you have lots of aggregate queries; create a table with the aggregation results and query that instead of running aggregates continuously). The most efficient way to research about this is by logging which queries you are running and analyzing the log- tools exist which do this neatly.

If you are I/O bound, then you can tune memory usage, although the OS cache is often working correctly. Take a look at free:

$ free
             total       used       free     shared    buffers     cached
Mem:       6122892    5903564     219328          0     257020    3119240
-/+ buffers/cache:    2527304    3595588
Swap:     11956220      65980   11890240

The first line of numbers accounts for memory usage including OS caches, the second doesn't; by comparing both you can see how OS caching is working. Also, vmstat will already tell you how much I/O you are performing (bi, bo columns). Often, the key to solving I/O problems is query tuning and indexing; indexing prevents full table scans (i.e. reading the entire table to get a limited set of data, which causes excessive and unnecessary I/O). Again, logging queries is most effective here- running EXPLAIN on the queries will tell you which operations the database is performing to execute the query, which often leads you to understanding inefficiencies in the query (and altering the query to solve them) or finding out about needed indexes.

StackExchange Database Administrators Q#11814, answer score: 5