Too many databases

Hi,

I am using drupal for my clients website, and was thinking is it possible to host all ( about 500) of them on the same server(maybe VPS or dedicated).
Here is the situation..... Each clients website has a database with about 50 tables each, all the databases are small in size about 2-5 MB .... and the websites are low traffic websites with say.. 50 hits/day on avg.... that means about 2000 queries/db/day ..... (avg 40 queries per hit)....

Wanted to know if it is possible to have so many databases about 500 on the same server?

what are the things that i should look into if i should make this happen?

Comments

[Todd Hoff]

The only lame answer I can come up with is to create a test and see how it works. I know I've read about hosting services running that many (can't find the reference), but on a VPS you won't have the same size machine to power your app, so it's unlikely.

[atif.ghaffar]

> Wanted to know if it is possible to have so many databases about 500 on the same server?
Yes.

> what are the things that i should look into if i should make this happen?
Use single instance of the code. Do not install the whole cms per website.

Make a metadb that works with domain name/url as key and tell you which database/server to connect to even if its the same server. This logic will have to be embedded in your cms so it knows which database to connect to and so you can scale in future.

Use innodb instead of MyISAM. (see next point)
Use a single innodb file instead of splitting it per table.
500 db x 50 tables with MyISAM is something like 500X50X2 file descriptors.

Let us know if this works.
best regards

[Anonymous]

@atif

thank you for answering.

Thats exactly the approach i have taken... hacked the settings.php file in drupal and use a metadb to get the dbname, dbuser and dbpassword for each of the website...

Was wondering what the bottlenecks would be,and was thinking on the following lines... please correct me if i am wrong....

1) is there a limit, as in max number of db that can be open at a point of time?
2) is it too much resource consuming to access different(read random) db each time? because it needs to switch form one db to another?
3) was thinking ... as mysql has no database level cacheing as such(but only query level cacheing)... if mysql is intellegent to distinguish bw the same queries for diff databases.... there shouldnt be any overhead as such when accessing random databases...
4) was wondering if there is any use using persistent connections for db access?

[atif.ghaffar]

Hi,

I cant answer all the questions, I will let someone else respond to 1.

2. No I have not seen much problems switching dbs.
Please use innodb and a single file if possible for all databases and tables or even a raw partition for innodb data.
Make sure you can spare a few Gigabytes of RAM for the database. 2GB should be good.

3. The query cache is table specific.
select * from users will be cached only until there is a change to that table.
so if you are doing something like
select * from users where uid=x
and then
update users set lastviewed=now() where uid=x

then you have probably no advantage of query caching.

Please someone correct me if I am horribly wrong.

4) No do not use persistent connections.
I have exeprienced that persistent connection via php has some problems.
Instead set the wait_timeout to very small number 5 seconds in mysql.
Set max connections to something like 300.
With the kind of traffic you are talking about this should do the trick.

best regards.
Atif