RAM requirements- new server
Posted by:
A B
Date: April 02, 2009 12:15AM
Hello,
We are rebuilding a database that has disk performance issues. As soon as we access the largest tables (see below) for reporting the disk slows down and other reports take a LONG time to run. Inserts and updates are fine.
The current server
- MySQL 5.0
- Windows 2003 Server (32-bit)
- running on VMWare ESX server
- VMWare monitoring tools indicate that memory is not fully utilised, CPU is not fully utilised, disks are not fully utilised until the reports are run against the large tables (see below)
- RAM = 4 GB assigned
- 6 disks in RAID 5
- innodb database
- innodb_buffer_pool_size = 2 GB
- our database has about 100 tables
- database size = 180GB (data length = 140 GB, index length = 40 GB)
- all in one ibdata file
- the tables range from 100 kB to 40 GB in size, with the 40 GB table being accessed for reports frequently
- Index length from 100 kB up to 15 GB
The new server is:
- MySQL 5.1
- Windows 2008 Server, 64-bit
- on VMware ESX
- 6 disks in RAID 5 (i would have liked RAID 10 but not possible at the moment)
- same database as above BUT... one file per table
- we will be transferring the data between the old and new databases in about a week
- we have improved the speed by modifying some queries, batch processing information overnight and querying that data rather than doing things on-the-fly, and other changes. I thinkw e have hti the limit there, however.
- ultimately, i would like the server to respond quickly even when getting data from the large tables.
The questions I have are:
- how much RAM to give the server, we were thing 18 GB for the server
- what size to make the innodb_buffer_pool_size, we thought 13 GB (18GB on server)
- what settings for any other memory options in my.ini
- how does the RAM get used in MySQL, and the impact it would have on disk performance, especially given the size of some of the tables and indexes.
any assistance or comments on the server are welcome.
kind regards,
A