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.
Best Answer
It's normal. Memory usage is determined by various configuration parameters, some of which are shared (e.g. innodb_buffer_pool_size), while others are per thread.