MySQL memory usage planning

[ugob]

Hi,

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?

Regards,

[Peter]

To identify if increasing memory will help you need to know your “working set”, meaning the part of the data which is accessed by these queries, or most frequently accessed.

If queries are full table scan queries it is easy you can just can take a look at .MYD file to estimate how much memory you need to fit them.

Full table scans are however fast compared to the joins - joins require a lot of random IO which is very slow. So it is very important for joined tables to fit well in memory for optimal performance.

Also you may have problems with warmup - time taken to populate the caches by random IO - having some full table scan query on joined tables to bring them up in OS cache may help.

[ugob]

Hi, thanks a lot for your quick reply, really appreciated. I’ll look into that.

[al.james]

On a related note…

How can I see if JOIN’ed tables are being cached in memory? I have a quite complex query that I am trying to optimise. Which mysql memory variable should I increase if not?

Thanks alot

Alastair James

[Peter]

Nope you can’t really see if table is cached in memory. You can normally see it though by running query and checking if there is significant disk IO.

Also pre-caching tables ie by running full table/index scans on them may speed up join speed a lot in cold joins.