Database design: how to effectively manage about 4000 databases with mysql?

4000 databases for one system?! Wowzer, did you invent Google?

4000 databases for one system?! Wowzer, did you invent Google? I'd definitely say that you need to redesign that setup - unless your 'system' is actually database hosting and each user has paid for a private db, of course.

Nothing wrong with having multiple discrete databases, but 2-per-user is the wrong approach. The 'right' approach will depend entirely on what your system is meant to do. You mention everyone has a dedicated server too - this should prevent contention issues for other users.

Are you sure it's not shared hosting?

I have a dedicated server 2GB RAM. And on this server I have these databases. – karto Sep 29 at 13:13 @karto: ok, there's the problem - 4,000 databases on a server with 2GB RAM will be slow, regardless of design.My laptop had 2GB in it and that struggled to run a dev instance of SQL Server.

– Widor Sep 29 at 13:15 Thanks widor: Do I increase the RAM then to about 16GB? Whiles I try to redesign? – karto Sep 29 at 13:27 RAM is cheap and it would certainly help the connections/contention issue but the real issue here is the design of the database(s) - you may still need many, or perhaps you can refactor the setup into one or two.

Throwing more RAM at it is only a temporary solution. – Widor Sep 29 at 13:29 :I can redesign the database to about 15. Then it means, for my 4000 users, they will be connecting/reconnecting to 15 databases.

So that makes 4000*15 connections, thats if they are all logged in. Whiles if I maintain 4000 dbs it will be 4000*1 connections. Any sense in what i'm saying?

– karto Sep 29 at 14:12.

Nine times out of ten, when someone structures an application database this way (segmenting identical data into different databases, or even into different tables) it's a mistake based on an unnecessary attempt to pre-optimize the system. But without more information we cannot tell whether: This is one one of the nine times it's a mistake, or the tenth time, when it's an appropriate design. Whether the number of connections is what's causing the performance problems you see (which would be solved by switching to a single database) or something else.

Its about collecting distinc data for users and storing them. The data collected is intensive in volume. – karto Sep 29 at 13:09 I can redesign the database to about 15.

Then it means, for my 4000 users, they will be connecting/reconnecting to 15 databases. So that makes 4000*15 connections, thats if they are all logged in. Whiles if I maintain 4000 dbs it will be 4000*1 connections.

Any sense in what i'm saying? – karto Sep 29 at 14:14.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.