More tables or more databases?
I am setting up a system to host WordPress blogs for the users of the site I am running. Now everything works very well within the same database and different blogs working with their own added tables ( user1_posts
, user_posts
etc.).
Even though this works so far, it seems a little messy. If this database should have 4000 tables, would that be a problem? Wouldn't it be better to split this across 400 databases? (Or am I missing a smarter way to do this?)
Thank!
source to share
What you are doing is essentially giving them a separate database since the data for each user will exist in isolation. I would suggest they split them into their own databases.
Where it gets tricky is if you have a table where all users share their data. This is when you need to think about whether you want to keep your data in one place or separately
source to share
It depends on which engine you are using. If you are using MyISAM, performance will be very high if you have more tables than the table cache size set in my.cnf.
Setting the table cache to> the total number of tables is vital, otherwise it will have to keep opening and closing tables that are blowing the index cache into the key buffer, which means heaps of unnecessary I / O.
It doesn't matter if you are using multiple databases or the same, table cache for MyISAM has a safe effect.
The same can happen for other engines as well, but I think the effect is much less pronounced (for example, InnoDB does not lose cache when the table is closed, but it is still not free)
source to share
Depending on the size of the table and the database software, you may want to consider a section.
If the table is small and ultimate security is optional, will have a customer_id column.
The 4000 tables in the schema are quite large, they may or may not cause performance issues - depending on the database.
maintaining 400 databases is likely to be a big headache. Think about ongoing maintenance - backups, updates, etc. And how you will do them when you have 400 databases. I have personally avoided this route.
Maybe a middle road? database for every 50 clients?
source to share
4000 tables are a bit big.
If you plan on splitting your tables across different databases ... you can see how Meta Database will work. One meta-database residing in all different databases containing connection information for different databases ... and general information. this way you can scale easily .. and with an existing operating system .. you only need a wrapper over your meta-DB to support your running application ...
I'm not sure how much this will help !!!!
source to share