We are testing and working to build a platform which could support many databases.
Why many databases?
Well, we have no other option. We are working on to offer SaaS application and that application is not actually built for SaaS so we will be forced to create a separate database for each user. Each database will contain roughly 400 tables. We estimate 5000 databases on each server so that gives us 2 million tables.
Why one server? Why not distribute it into many small servers?
Managing few number of servers would be more economical. Also most of these database won’t be much demanding.
Problem?
Because there are so many tables in database we are not able to optimize this database. Total combined DB size of all DBs can be huge. These are many-many low demanding database. By low demanding I mean they don’t require to process many queries/sec but combined queries/sec is high. query cache seems to be useless in this case.
What do you suggest should be hardware configuration for this hardware and software configuration values.
- FusionIO / SSD will not be economical because of huge size.
- We can put 256GB of RAM in system but are not sure how to use it in this case.
- Hardware we have can support up to 16 drives and we can afford SAS 15k with hardware RAID with BBU.