On Wed, 2004-07-07 at 05:28, Dave Raven wrote:
> Hi all,
> I'm going to be building a rather large mysql box soon and I was
> hoping I could just run a few specs and requirements past you to double
> check it won't choke.. Here are some pasted parts of the document, any help
> / suggestions would really be appreciated..
> The data is to be held in an SQL database as follows -
> 60 Days detailed data.
> 90 Days daily summaries
> Monthly summaries for 6 months.
> 30,000 users would double these estimates to 20 million records per day or
> 5,3 gigabytes of data giving a 60 day total of 1,152 million records or 322
> gigabytes of data. Including summary records and allowing for actuals it
> would seem sensible to budget for double these estimates.
At such large data set you will still have things very IO bound. So
good hard drives are very important.
Also you might do better avoiding to have all users in the same table.
> 3U rack mount server case
> Dual 3,2Ghz Xeon processors (effective 4 processors [+/- 11.5Ghz])
> 1MB level 2 processor cache.
> 4 GB DDR Memory
> High speed SCSI controller with large cache.
Cache is good but do not expect magic from it on such large volume.
> 10 x 72GB SCSI drives in a RAID 5 cluster
I assume you're going to have a lot of writes, in this case RAID5 is not
what you want. RAID10 could be much better, even if it will push you
back to SATA drives due to the price.
> Dual redundant power supplies
> USB Backup Device (Zip)
> 32 bit mutli-user, mulitasking FreeBSD Operating System.
> MySQL SQL server software
> The hardware must be able to cope with the data volumes expected. 15 to 30
> Million SQL inserts per day, 600 gigabytes of data storage, and reasonable
> response times when reporting and summarizing on over one thousand million
It could be also better idea to have number of lower end boxes instead.
Speaking about response time - it depends a lot from how do you design
the system. Do not try to scan millions of rows in real time and expect
some magic :)
Peter Zaitsev, Senior Support Engineer
MySQL AB, www.mysql.com