Mysql: Multiple Tables Vs. Multiple Databases

Howitzer

Hi,

I've a couple of pretty busy sites all on the one server. The database is on that server too. There is no scope for another server at the moment.
All sites are completely separate from each other.

Each site has their own set of tables within the one database on that server. Each is prefixed site1_ , site_2 etc... for identification.

This is a pretty lazy setup in my opinion and I'm starting to see sites slow down due to slow queries.

Would it definitely be better to have separate databases for each site?
I feel it would - but can't find any info to back up the idea.
If anyone had a link that would be great.

Perhaps there is no gain to be made though? I mean the same mysql server would be managing these databases. If one database crashed mysql - wouldn't the others all go down too? Or would Mysql server compartmentalise the crash? (leaving the other sites alive and well)

If anyone has any insights into this it would be welcome.
Lastly, I'm using site caching to relieve as much strain as possible.
My mysql slow_quey log is 3.8mb today already (much the same query in there each time too)!

Looking forward to your views....

oeb

Howitzer wrote: »

Hi,

I've a couple of pretty busy sites all on the one server. The database is on that server too. There is no scope for another server at the moment.
All sites are completely separate from each other.

Each site has their own set of tables within the one database on that server. Each is prefixed site1_ , site_2 etc... for identification.

This is a pretty lazy setup in my opinion and I'm starting to see sites slow down due to slow queries.

Would it definitely be better to have separate databases for each site?
I feel it would - but can't find any info to back up the idea.
If anyone had a link that would be great.

Perhaps there is no gain to be made though? I mean the same mysql server would be managing these databases. If one database crashed mysql - wouldn't the others all go down too? Or would Mysql server compartmentalise the crash? (leaving the other sites alive and well)

If anyone has any insights into this it would be welcome.
Lastly, I'm using site caching to relieve as much strain as possible.
My mysql slow_quey log is 3.8mb today already (much the same query in there each time too)!

Looking forward to your views....

One of the main reasons I would separate them is for security reasons.

If one of your accounts gets compromised now, you could lose everything.

amen

see sites slow down due to slow queries

I think that is your problem.

Have you done any analysis on the slow queries?
Do you have the correct indexes , keys etc?
are the queries slow searching/updateing/inserting?

Is it on table or many tables?
certain times of the day?

voxpop

Ive never heard of someone using different tables per site rather than different databases.

There would be horrendous duplication of entities, site specific SQL that needs to be rewritten for each site, problems with bug fixing, upgrades, moving sites to different servers.

There is also the security concerns as already posted.


Different database wont help if the database server crashes - you need some sort of clustering there.

Howitzer

Thanks for the pointers all.

Turns out the major slowness is the servers cpu.

Still no harm blaming the database and seeing where it can be improved.

the slow queries are all the same query - I'll have to work on optimising that one!

Cheers

amen

Turns out the major slowness is the servers cpu.

yes but is it slow because the database is hammering it or is it just a slow cpu?