Master/slave databases?

bengiles

Would it be possible to set up a master database which contains all of the records available and 100 or so individual slave databases which only contain certain records, and setup so that any changes made to records in the master database would be reflected in the individual records wherever they might reside in the slave databases? All changes would be initiated in the master database only, and the slave databases would be used for read only queries?
If feasible, this setup should make individual queries much faster since the number of records to be searched would be reduced by a factor of 100 (or however many slave databases were used.
Is there some obvious drawback to this setup I'm missing?

rachel2004

I'm not sure what the reason is for using seperate databases, but what you're asking for could be done if you use tables where you are thinking of databases.

A series of JOINs could be used where a change in tableA could be be used to affect changes to tableB, tableC and so on IF they have some sort of JOIN between them.

Do a search of the MySQL manual for use of JOIN types.

bengiles

The purpose of the multiple databases is to make the searches much faster. One feature I am using is a zipcode + distance calculator. It is an intregal part of my website, and it is also the slowest process. I'm still trying to get a grip on database theory, but I'm going under the assumption that the use of individual databases for the hundred or so categories I have would improve my speed to 1/100 th of what it would be if only a single database were used. The problem with the multiple databases is that in some cases I need to access all the records with a single query. Thus, the master/slave setup.
Speed is what I'm trying to improve. Are you saying that searching a table with several joins (in a large database with hundreds of tables and millions of records) is just as fast as searching a table of the same size in a much smaller database?

rachel2004

Originally posted by bengiles
Are you saying that searching a table with several joins (in a large database with hundreds of tables and millions of records) is just as fast as searching a table of the same size in a much smaller database?

If it were me, I'd benchmark what I'm doing to determine it for myself. The whole idea of Relational Databases (such as MySQL or Oracle) is to build around the idea of tables as your containers and relate to each of those tables. If you have read somewhere that no, using a bunch of databases (instead of tables) is faster in someway, please give me the ISBN of the publication or the URI of the link and I'll look at it myself.

What I've explained is very basic. Consideration also has to be given to how you've designed your database and what you are storing in the tables. Data types of BLOB or text, for example, are not as fast to search through when you run a query. Then the quesion is how you are indexing your tables. To my knowledge, I am not aware of how to index an entire database, let alone the myriad of databases in your master/slave set-up.

By the way, I see you are using a zip code distance calculator. Here is a good PHP class for that if these are zip codes based in the United States:

http://www.phpclasses.org/browse/package/1640.html

mourisj

If you have a DBMS using triggers, you can replicate your data with triggers (on insert, on update, ...)
With Oracle or some other DBMS, you can also do triggers between different databases.

However, the triggers are part of your transaction; if the trigger fails (e.g. database not available, ...), the insert into your master table also will fail !!

I don't thik this works with a MySql database.

JJ Mouris

bengiles

Thanks for the replies, they have been helping me decide on a strategy.
I know I'm taking something simple and making it damn near impossible, but their is a method to my madness. I'm trying to design a website system that, if successful, could grow very large with heavy traffic. The problem is that income will lag way behind the capabilities of a single dedicated server if the growth actually takes place. In addition, the old addage "Speed Kills" works in reverse for a website. Slow page returns will kill a website faster than anything. It seems even the best financed websites are irritatingly slow if they have large databases to search.
I've put a few months of spare time into getting to understand the costs and inner workings of multi-server systems. That whole end of this industry is a nightmare. Unless you have the big bucks it takes to start using load balencers and truely managed hosting solutions, the only workable setup I can see is to set your website up right from the very start to be broken up into smaller portable sections that each can be handled by a seperate server. That way, when you reach your server's capaticy, it's just a simple matter of moving one or more of those sections to another server and changing a few addresses in your code.
My website could be broken down into about a hundred such sections, each with it's own database. Solving the scaleability problem this way has the added benefit that it reduces the size o any given database, making queries much faster.
I will need to use triggers or stored procedures. A seperate database will be used for the users/session records.
Anyway, I'm going to deal with these problems now so that I don't have to later when the pressure is on. Next step is picking the right database, probably postgreSQL or Firebird. Suggestions are always welcome.

nemonoman

If you intend on putting your 'slave' databases on the same server as your master, your response times will degrade significantly. The only value to the separate database approach is if you could offload processing to multiple servers as well.

The problem, from the sound of it, is that you are trying to process data item by item using PHP, rather than using the inherently extremely fast search and calculation capability of the database.

Try to use SQL to select the smallest subset of records requiring PHP processing.

ultraslacker

The purpose of the multiple databases is to make the searches much faster. One feature I am using is a zipcode + distance calculator. It is an intregal part of my website, and it is also the slowest process. I'm still trying to get a grip on database theory, but I'm going under the assumption that the use of individual databases for the hundred or so categories I have would improve my speed to 1/100 th of what it would be if only a single database were used. The problem with the multiple databases is that in some cases I need to access all the records with a single query. Thus, the master/slave setup.
Speed is what I'm trying to improve. Are you saying that searching a table with several joins (in a large database with hundreds of tables and millions of records) is just as fast as searching a table of the same size in a much smaller database?

You are searching hundreds of tables for GIS data? Do you have a solid design?

Databases should scale, so if yours doesn't take a look at the design and dbms.

nemonoman

Speed is what I'm trying to improve. Are you saying that searching a table with several joins (in a large database with hundreds of tables and millions of records) is just as fast as searching a table of the same size in a much smaller database?

With a reasonably constructed query on a reasonably efficient database the speed differences should negligible, and should certainly trump the difficulties inherent in hundreds of slave databases.