Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    180

    Split MySQL in 2

    My PHP script uses MySQL, but it takes a lot of resources so I wanted to split my MySQL database in 2 parts and run both of them at the same time.

    On my shared host ing server, my host has his MySQL databses on different servers. Like if I create one database it will be on server1.host.com and if I create another then it will be on server2.host.com

    So I wanted to split them into 2 so half of my applications database is being run on server 1 and the other half is running on server 2. Is this possible? If so, how?

  2. #2
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    667
    Yes this is possible, it's called federation (I'm assuming you want to spread your tables about as though they were one database). Unless you are Amazon or Google however, it's very rare that a federated storage engine is required by load alone. Most often, you can squeeze 10000x more performance out of query optimization than you can from server upgrades or division of tasks.

    http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

    With some careful tuning, I bet your script would behave quite nicely! Start with logging indexless queries and then slow queries, and fix them all. Indexless queries are your biggest nightmare.

    Then, spend time with your queries and EXPLAIN to see if you are performing many full index scans or temp table creation. Chapter 7 of the MySQL manual treats well on the topic:

    http://dev.mysql.com/doc/refman/5.0/...imization.html

    Past this, the work extends into redundant queries. Stuff like queries inside of loops that can be replaced with appropriate single-execution queries. Using prepared statements with PDO or some other abstraction like ADODB can be a big help as well. Lastly, upgrading to MySQL 5.0 can be a big help, with the advent of multi-index usage (which involves devising adequate index sets for your tables).

    This writ, best of luck! Tuning applications to work well on servers is always a very rewarding experience!

    Cheers.
    Alex
    Last edited by Saeven; 11-23-2006 at 03:21 AM.
    circlical - hosting software development
    forums * blog

  3. #3
    Join Date
    Sep 2005
    Location
    Southern California
    Posts
    179
    I agree with Saeven that the best course of action here is to just optimize your queries. Splitting between multiple servers (especially ones that you do not own) is probably a larger challenge than you are capable of handling.

    I have had many clients with queries that take 40 - 60 seconds to execute that were fixed by adding a single index to their database. After a few indexes and/or other optimizations, their queries ran within a half of a second.

  4. #4
    Join Date
    Nov 2006
    Posts
    180
    Quote Originally Posted by localhost127
    I agree with Saeven that the best course of action here is to just optimize your queries. Splitting between multiple servers (especially ones that you do not own) is probably a larger challenge than you are capable of handling.

    I have had many clients with queries that take 40 - 60 seconds to execute that were fixed by adding a single index to their database. After a few indexes and/or other optimizations, their queries ran within a half of a second.
    how do you optimize MySQL queries? I Have MySQL 4.1. Can you please help?

  5. #5
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    667
    how do you optimize MySQL queries? I Have MySQL 4.1. Can you please help?
    Oh boy, that's like asking a doctor how they do open heart surgery It takes study, practice and experience. To get you started though:

    1. Make some tea
    2. Start reading http://dev.mysql.com/doc/refman/4.1/...imization.html
    3. Sign up for the MySQL mailing lists, and start askin questions

    Good luck! It's a rare expertise these days!

    Cheers.
    Alex
    circlical - hosting software development
    forums * blog

  6. #6
    Join Date
    Nov 2006
    Posts
    180
    Quote Originally Posted by Saeven
    Oh boy, that's like asking a doctor how they do open heart surgery It takes study, practice and experience. To get you started though:

    1. Make some tea
    2. Start reading http://dev.mysql.com/doc/refman/4.1/...imization.html
    3. Sign up for the MySQL mailing lists, and start askin questions

    Good luck! It's a rare expertise these days!

    Cheers.
    Alex
    noo.. not reading. ah... well... hopefully that guide will help me achieve a more effiecient mySQL database. thanks for the link.

  7. #7
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    #mysql on the freenode irc network is also a good place to ask such questions (your chances of success are varied though).

    Consider picking up a book on MySQL, most will cover this topic extensively (avoid the '21 days' and 'For Dummies' or other beginner series books).

    Optimizing queries is a bit of a dark art as it differs for each database and application (what kind of queries are more often used, etc).

    Having a proper index on each table, however is a very easy first step.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •