database architecture and query performance

[David Usherwood]

Hi All,

I am currently in the process of designing a new system. We are using Drupal to gather customer registration details and moodle for course content and activity. Both these technolgies save data to a database and in my case that will obviously be mysql.

In brief I just wanted to canvas opinion as whether it would be more efficient in terms of the physical machine/mysql db server/sql queries performance to have two separate databases running on one mysql database server instance or just one database containing drupal/moodle tables and other database objects e.g views etc running on one mysql database server instance/one physical machine.

I suppose if all the drupal/moodle tables are located in one database then this would be far more efficient than having to map to tables on another database even if on the same db server. But I was thinking for backup purposes (e.g. mysqldump) it may be better to have the separate db objects for drupal/moodle in separate databases ??

One final question - when I perform a mysqdump (for backup purposes) ideally should all users be logged off or can this activity be performed on a live server with users still logged in and submitting queries without any issues or possible db damage/failure.

Many thanks for your help