Heavy mysql usage CPU or Memory

Question

I have an Amazon s3 instance and the project we have on the server does a lot of INSERTs and UPDATEs and a few complex SELECTs

We are finding that MySQL will quite often take up a lot of the CPU.

I am trying to establish whether a higher memory or higher cpu is better of the above setup.

Below is an output of cat /proc/meminfo

MemTotal:      7347752 kB
MemFree:         94408 kB
Buffers:         71932 kB
Cached:        2202544 kB
SwapCached:          0 kB
Active:        6483248 kB
Inactive:       415888 kB
SwapTotal:           0 kB
SwapFree:            0 kB
Dirty:          168264 kB
Writeback:           0 kB
AnonPages:     4617848 kB
Mapped:          21212 kB
Slab:           129444 kB
SReclaimable:    86076 kB
SUnreclaim:      43368 kB
PageTables:      54104 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   3673876 kB
Committed_AS:  5384852 kB
VmallocTotal: 34359738367 kB
VmallocUsed:       180 kB
VmallocChunk: 34359738187 kB

Current Setup:

High-CPU Extra Large Instance

7 GB of memory 20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units each) 1690 GB of instance storage 64-bit platform I/O Performance: High API name: c1.xlarge

Possible Setup:

High-Memory Double Extra Large Instance

34.2 GB of memory 13 EC2 Compute Units (4 virtual cores with 3.25 EC2 Compute Units each) 850 GB of instance storage 64-bit platform I/O Performance: High API name: m2.2xlarge

Answer 1

I would go for 32GB memory and maybe more harddisks in RAID. CPU won't help that much - you have eough cpu power. You also need to configure mysql correctly.

• Leave 1-2 GB for OS cache and for temp tables.
• Increase tmp_table_size
• remove swap
• optimize query_cache_size (don't make it too big - see mysql documentation about it)
• periodically run FLUSH QUERY CACHE. if your query cache is <512 MB - run it every 5 minutes. This doesn't clean the cache, it optimizes it (defragment). This is from mysql docs:

Defragment the query cache to better utilize its memory. FLUSH QUERY CACHE does not remove any queries from the cache, unlike FLUSH TABLES or RESET QUERY CACHE.

However I noticed that the other solution has the half disk space: 850GB, which might be reduced number of hard disks. That's generally a bad idea. The biggest problem in databases is hard disks. If you use RAID5 - make sure you don't use less hard disks. If you don't use raid at all - I would suggest raid 0.

Answer 2

Use vmstat and iostat to find out whether CPU or I/O is the bottleneck (if I/O - add more RAM and load data into memory). Run from shell and check results:

vmstat 5
iostat -dx 5

• if CPU is the problem vmstat will show high values in us column, and iostat will show low disk use (util)
• if I/O is the problem then vmstat will show low values in us column and iostat will show high disk utilization (util); by high I mean >50%