Hello,
In a multi-site, where all the sites share the same installation, is it also a good idea to have them all share the same database but using a different prefix? Or is there a reason why this would be a bad idea? I would prefer to have fewer databases since my webhost likes to charge a fee for each database we have, but not if it's going to cause a lot of problems later.
Thanks, Daniel.
On Thu, 18 Oct 2007 14:38:34 +0200 Daniel Carrera daniel.carrera@zmsl.com wrote:
Hello,
In a multi-site, where all the sites share the same installation, is it also a good idea to have them all share the same database but using a different prefix? Or is there a reason why this would be a bad idea? I would prefer to have fewer databases since my webhost
The first thing that comes to my mind is separate backup. The second thing is separate access/write permissions.
two more things: if you have a need to migrate one of the sites to it's own install at some point (because of high traffic for example) it would be a bigger job to migrate the database.
It's been a while since I did significant database management (beyond normal setting up of databases, tables users etc) but if I remember correctly you can set things such as concurrent connections per databsae not just per user - if your host is charging per database it's likely they have that set up so doing this could have performance limitations (eg. high traffic on one site resulting in decreased performance on the other sites or even make them inaccessible)
.s
Ivan Sergio Borgonovo wrote:
On Thu, 18 Oct 2007 14:38:34 +0200 Daniel Carrera daniel.carrera@zmsl.com wrote:
Hello,
In a multi-site, where all the sites share the same installation, is it also a good idea to have them all share the same database but using a different prefix? Or is there a reason why this would be a bad idea? I would prefer to have fewer databases since my webhost
The first thing that comes to my mind is separate backup. The second thing is separate access/write permissions.
On Thu, 18 Oct 2007 11:34:00 -0400 sander-martijn lists@severeddreams.com wrote:
two more things: if you have a need to migrate one of the sites to it's own install at some point (because of high traffic for example) it would be a bigger job to migrate the database.
Get the schema, pass it through sed to get the tables/indexes/sequences that have to be deleted, backup, restore, delete.
That's part of the "no separate backup" pain.
Ok, I guess I'll go for separate databases then. Thanks for all the help.
Cheers, Daniel.
Ivan Sergio Borgonovo wrote:
On Thu, 18 Oct 2007 11:34:00 -0400 sander-martijn lists@severeddreams.com wrote:
two more things: if you have a need to migrate one of the sites to it's own install at some point (because of high traffic for example) it would be a bigger job to migrate the database.
Get the schema, pass it through sed to get the tables/indexes/sequences that have to be deleted, backup, restore, delete.
That's part of the "no separate backup" pain.
Quoting sander-martijn lists@severeddreams.com:
two more things: if you have a need to migrate one of the sites to it's own install at some point (because of high traffic for example) it would be a bigger job to migrate the database.
Why? You would just export the tables you need (E.G. foo_%), create the new DB and then import the tables. The settings.php file for the site would remain the same except for pointing to the new DB.
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
Because as Ivan said (this is assuming you want to migrate one or some of the sites not all) you would need to use a script like sed or manually separate the tables with the appropriate prefix from the rest of the databse. You might be able to export foo_% but I didn't think using the % variable worked on tables - I could be mistaken.
either way, that's just one of several reasons given why a single database can be more complicated and less desirable than separate databases - however no one said it can't be done and if the benefit outweighs the drawbacks mentioned you certainly can do it, just that there are reasons why it is not the best way to go.
Earnie Boyd wrote:
Quoting sander-martijn lists@severeddreams.com:
two more things: if you have a need to migrate one of the sites to it's own install at some point (because of high traffic for example) it would be a bigger job to migrate the database.
Why? You would just export the tables you need (E.G. foo_%), create the new DB and then import the tables. The settings.php file for the site would remain the same except for pointing to the new DB.
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
Quoting sander-martijn lists@severeddreams.com:
Because as Ivan said (this is assuming you want to migrate one or some of the sites not all) you would need to use a script like sed or manually separate the tables with the appropriate prefix from the rest of the databse. You might be able to export foo_% but I didn't think using the % variable worked on tables - I could be mistaken.
I think maybe the dba module [1] could use some love to add an export function. The tables for the site would then be Drupal controlled which gives the benefit of not dealing with tables from other sites. It will already do a backup for you but from cron.php.
either way, that's just one of several reasons given why a single database can be more complicated and less desirable than separate databases - however no one said it can't be done and if the benefit outweighs the drawbacks mentioned you certainly can do it, just that there are reasons why it is not the best way to go.
Of course, but the more databases the more database processes you also have so you also need to concern to the CPU power and memory limits of the host.
[1] http://drupal.org/project/dba
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On Fri, 19 Oct 2007 08:43:06 -0400 Earnie Boyd earnie@users.sourceforge.net wrote:
I think maybe the dba module [1] could use some love to add an export function. The tables for the site would then be Drupal
It is not so easy... You should provide export functions for pgsql and mysql + indexes.
Quoting Ivan Sergio Borgonovo mail@webthatworks.it:
On Fri, 19 Oct 2007 08:43:06 -0400 Earnie Boyd earnie@users.sourceforge.net wrote:
I think maybe the dba module [1] could use some love to add an export function. The tables for the site would then be Drupal
It is not so easy... You should provide export functions for pgsql and mysql + indexes.
Thanks Ivan, I've put this on my list to take a look at.
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
-
Daniel Carrera -
Earnie Boyd -
Ivan Sergio Borgonovo -
sander-martijn