Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Mysql: Multiple Tables Vs. Multiple Databases

  • 16-10-2009 11:25am
    #1
    Registered Users, Registered Users 2 Posts: 461 ✭✭


    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....
    Tagged:


Comments

  • Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭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?


  • Registered Users, Registered Users 2 Posts: 2,800 ✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 461 ✭✭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


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭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?


  • Advertisement
Advertisement