| MySQL memory usage planning [message #250] |
Wed, 11 October 2006 09:47  |
 ugob
Messages: 2
Registered: October 2006 |
Junior Member |
|
|
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,
|
|
|
| Re: MySQL memory usage planning [message #251 is a reply to message #250 ] |
Wed, 11 October 2006 10:02  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
|
| Re: MySQL memory usage planning [message #306 is a reply to message #301 ] |
Mon, 30 October 2006 09:22 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
