Can you help me with my capacity planning?
I have written an application that uses MySQL & Sphinx for searching and storing information.
the application contains a database that weights 300GB (mostly text, mediumtext and varchars). my sphinx index weights 30GB.
my problem stated when I was planning to have a 100GB~ DB and I now have a 300GB one, which can't be loaded into memory(?).
I'm looking for the best architecture that will give me the highest performance.
my application first gets a query, then runs it against the sphinx index, gets a bulk of IDs from sphinx, queries the DB for several text field for those IDs and sends the results to a .NET ranker (special algorithm).
In order to get the best performance I used a Xeon i7 W3520 server with 2 SSD disks (24GB ram) for MySQL, ain't cheap and I'm out of space and can't add more disks so I need another server.
Do you think there will be a major perfomance impact if I'll use a server with regular disks for mysql? I know that I need fast disks for the sphinx searching (this is my full text search engine), I'm using an indexed field (ID) to select rows from the DB without any complicated queries, I may need to host the DB on a cheaper server with enough storage.
can you spot my bottleneck? the .NET application needs mostly RAM and CPU, the DB needs a lot of space and or RAM and the sphinx needs fast disks and RAM.
- is it possible to load the MySQL index to the RAM?
- what can I do to mitigate the fact the DB's size if 300GB?
- will a server with slower disks impact the performance by much? (I can't benchmark it without spending hundreds of dollars on hardware).
- does it matter if I use a linux or windows machine for the MySQL?