How much hardware is 'enough' ?

[dstrickler]

I realize the debat rages on about this subject, but I have a real-world question.

We're about to replace our Sugar server witha brand new one, and I have the ablity to right-size it. And while bigger is often better in hardware, have more than Sugar can use is just silly.

So, I have approx 80MB of raw CSV data imported into Sugar, and I can see that doubling within the year. I am running on SuSE 10.0 linux and plan on keeping that platform for a while. I run MySQL v4, but we can go to v5 when we are ready. I run PHP 4.x, but I can see the need for v5 in the future as Sugar calls for it.

So, what hardware should I get. Of course the CPU will be fast (my guess over 3Ghz, and the drives wil be fast, but what about RAM.

How much can MySQL effectively use? Does it have limits? And how do I know how much to have so I can feel comefortable that all the data is cached in RAM on MySQL? Is 4GB enough? Is 6 ?

Comments?

[kpit]

The real question you should be asking yourself about the performance has a few questions.

• How many Simultaneous user will there be?
• What is the expected growth rate in number of users over the next 5 years?
• What are your current response times now?
• Are they under 2 sec for the database queries?
• Are the page load times in less than 5 Seconds?

So many factors come into play with configuration of a "Right Sized System". Ideally to enhance performance of the system you would have 2 servers. One database server and one web server by moving the database to its own server you would free up memory and CPU cycles for use by PHP and give enhanced performance to the database for the same reasons.

The database server should have enough memory configured to cache all Indexes and have a decent sized query cache. For all intents and purposes the Indexes are more important to have in memory than the whole database. The indexes are more important because they are used much more than most of the database. The second most important is the query cache this holds recent queries in memory and will purge stale info automatically to make room for a more recent query. Usually only about 10% - 40% of the total database is actively being used. This in effect reducing disk access because there are many queries that are run frequently. Memory usage for the database depends on the size of the database. For example you have a 1 GB database you would need approximately 250 MB - 600 MB of ram. The larger the database size to ratio does not increase linear as the data get bigger the ration of database size to memory grows smaller for memory. And of course proper indexing is critical to increasing performance of the database.

The web server with PHP for Sugar will depend on the number of simultaneous users. SugarCRM so far can live in 50MB per page load. So if your web server gets 100 page loads at the same time your memory Requirement just for SugarCRM would peak at approximately 5 GB total. The 5 GB does not include system overhead and Apache server. Typically limiting this to 100 is not a bad idea because after this you will hit I/O limits within the hardware and will bind the CPU unless you have 2 or more CPU's. I would not recommend letting more than 200 simultaneous load at a time with dual (or dual core) CPU's. Due to bus bandwidth for the memory. (Typically 800MHz some up to 1000MHz but the fastest memory is DDR2 677MHz at the moment) Not to mention Network bandwidth limitations. Another factor to improve performance is to use Zend's free optimizer. This will cache the PHP scripts in Precompiled state in memory for faster execution of PHP.

In summary for a single machine solution on Linux that will allow 100 simultaneous page loads (Support about 1000 active users at the same time) with a database of 1 GB. You would need about 8 GB -12 GB of Ram, Dual CPU's 3GHz or better (or one Dual Core CPU) Hardware Raid 1 with 2, 10K 72GB hard drives. This is a very large system.

For a closer to reality system for Small and Medium Size business on Linux, with 100 users and a database of 500 MB or less you would look at 20 simultaneous page requests. You would need Single CPU 3.2 GHz or better with 4 GB RAM and Hardware Raid 1 with 2, 80 GB hard drives.