Results 1 to 7 of 7
Thread: Split MySQL in 2
-
11-21-2006, 06:30 PM #1Junior Guru
- 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?
-
11-23-2006, 03:15 AM #2Web Hosting Master
- 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.
AlexLast edited by Saeven; 11-23-2006 at 03:21 AM.
-
11-23-2006, 05:21 AM #3Junior Guru
- 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.
-
12-06-2006, 12:46 PM #4Junior Guru
- Join Date
- Nov 2006
- Posts
- 180
Originally Posted by localhost127
-
12-06-2006, 01:48 PM #5Web Hosting Master
- Join Date
- Aug 2002
- Location
- Canada
- Posts
- 667
how do you optimize MySQL queries? I Have MySQL 4.1. Can you please help?
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
-
12-06-2006, 09:00 PM #6Junior Guru
- Join Date
- Nov 2006
- Posts
- 180
Originally Posted by Saeven
-
12-07-2006, 02:28 AM #7Web Hosting Master
- 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.