I’d like to determine if adding memory to our server may help performance. Our system is basically using two databases. On the first one, there are 2 big tables. The database is 4.4GB big (3.1 data, 1.3 indexes). This database serves mainly for maillog entries. Inserts are ok, but we are looking for a way to increase the speed of report generation (which uses SELECT queries). Everytime we try to generate a report, the cpu falls almost 100% iowait. I guess that means that the CPU is waiting for disk I/O.
There is also one database (about 1.3 Gb 600MB data/700MB index) wich is our Bayes database for spamassassin.
The server has 4 GB of RAM and is running the MySQL server and is also scanning e-mails (wich uses about 800MB of RAM). Server is dual Xeon 3.0Ghz, 2 MB cache, SCSI disks, RAID 1 Megaraid.
We were wondering if increasing the physical memory in this server would increase the performance in report generation. Since the bottleneck is the hard drives, if I put more memory in the server, it may be possible for mysql to put all the table in memory and work on it from there. Am I right?