Have I made a mistake with my database setup?
Hi,
I've got a few previous questions about expanding our web infrastructure. Finally decided to move our database(s - there are several even tho we call it "our database") from our local server to a hosted server, set up that stuff as a master, and then set up a new VPS I rented as a "slave" in mysql as well as our local server with our webpage as an additional "slave".
So the idea was that the slaves are used for simple searches and lookups, while the master is used for any inserts or updates. these then propagate down to the slaves at their leasure.
I was hoping this would spread the load and speed up the simple stuff that the web users do as well as form a expandable structure that we will be able to grow with for the next few years.
So I made dump of all our databases and copied them to the new hosted server and set it up with the key databases that need to be replicated mentioned in the my.cnf file. there were lots of steps, but basically, I dumped those databases from the hosted server, copied them to my test vps, imported them, set up a replication user, did some "start slave" - made sure the master was ID = 1 and slave ID = 2.
But when I view the webpages on the slave, (connecting to localhost) it's still allowing me to perform "write" functions to the database (like register new accounts). I was under the impression that in this kind of structure changes only propagate down from the master so the slaves are read-only and don't send changes back up to the master. so I expected not to be able to write to the database.
How can I know if what I've done is correct in setting up the relationship (I can't really have the webpage running on the master server to see if users I've created on the slave really exist)
And How can I see if it's really working without having to throw my real live local production server into the mix?
--At the moment all the PHP code still reads off "localhost" I got and tested a script to change the code to read and write off the master when the time is right. we'll then manually change everything we know is safe to read off localhost as we come across it in the weeks following the changeover.--
Is my plan sounding plausible, am I going down the right road?
Thanks
Steven
I've got a few previous questions about expanding our web infrastructure. Finally decided to move our database(s - there are several even tho we call it "our database") from our local server to a hosted server, set up that stuff as a master, and then set up a new VPS I rented as a "slave" in mysql as well as our local server with our webpage as an additional "slave".
So the idea was that the slaves are used for simple searches and lookups, while the master is used for any inserts or updates. these then propagate down to the slaves at their leasure.
I was hoping this would spread the load and speed up the simple stuff that the web users do as well as form a expandable structure that we will be able to grow with for the next few years.
So I made dump of all our databases and copied them to the new hosted server and set it up with the key databases that need to be replicated mentioned in the my.cnf file. there were lots of steps, but basically, I dumped those databases from the hosted server, copied them to my test vps, imported them, set up a replication user, did some "start slave" - made sure the master was ID = 1 and slave ID = 2.
But when I view the webpages on the slave, (connecting to localhost) it's still allowing me to perform "write" functions to the database (like register new accounts). I was under the impression that in this kind of structure changes only propagate down from the master so the slaves are read-only and don't send changes back up to the master. so I expected not to be able to write to the database.
How can I know if what I've done is correct in setting up the relationship (I can't really have the webpage running on the master server to see if users I've created on the slave really exist)
And How can I see if it's really working without having to throw my real live local production server into the mix?
--At the moment all the PHP code still reads off "localhost" I got and tested a script to change the code to read and write off the master when the time is right. we'll then manually change everything we know is safe to read off localhost as we come across it in the weeks following the changeover.--
Is my plan sounding plausible, am I going down the right road?
Thanks
Steven
greetings, , StevenHook - - I am posting because no one else has, I am NOT really a MySQL data server setup expert, But I have "Heard" some that were talk about this sort of thing. So consider my info things to consider, not experienced tried stuff. But Data Server configuration capacity, and speed are complex issues, and more so if you want to include future "Expansion" in the mix, you may want to get some pay for professional advice and consulting for this.
I heard that fragmenting or copy of same Data sets on different locations, can cause problems of cordination, and non conformity of data.
So. . . . Maybe by increasing the Data base server performance level (more processesors, hard drives) but keeping all data in one location is a more better for your needs?
I have heard of separation of data sets on different data servers for speed, as separate sets, not copies, as in user names begining with A-H on one, users beginning with G-N on another, and O-Z on a third, routing all queries by the first letter of the user name, dividing the "Load" into thirds, But I do not think this is a very good approach, except for speciallized setups.
I do know that there are hardware (software) database setups that can have tremendous performance and redundancy (backup) of data sets, with "Shared"-"Divided" processors, but this is a speciallized knowledge sort of thing that changes as new hardware comes out.
Sorry I could not be of more help, but your method of doing this in your own way is likely unproductive, as this has been done thousands of times before for data increases that all web sites have, Hopefully someone else here on EE can shed more light on this.
I heard that fragmenting or copy of same Data sets on different locations, can cause problems of cordination, and non conformity of data.
So. . . . Maybe by increasing the Data base server performance level (more processesors, hard drives) but keeping all data in one location is a more better for your needs?
I have heard of separation of data sets on different data servers for speed, as separate sets, not copies, as in user names begining with A-H on one, users beginning with G-N on another, and O-Z on a third, routing all queries by the first letter of the user name, dividing the "Load" into thirds, But I do not think this is a very good approach, except for speciallized setups.
I do know that there are hardware (software) database setups that can have tremendous performance and redundancy (backup) of data sets, with "Shared"-"Divided" processors, but this is a speciallized knowledge sort of thing that changes as new hardware comes out.
Sorry I could not be of more help, but your method of doing this in your own way is likely unproductive, as this has been done thousands of times before for data increases that all web sites have, Hopefully someone else here on EE can shed more light on this.
I also meant to say - we found that by changing info storage off of the main data base, and into SQLite or File storage, especially for data that was Only accessed by a single User call, like Blogs, shout boxes, user posts, greatly increased the speed and capasity of the main data base, but back-up difficulty increased.
Not to be flip, but you really need to hire a data base administrator. You may be able to find one in the EE community or you may be able to find one in a local PHP user's group.
There are many, many variables you need to consider - table structure, transaction arrival rate, relationships, table locking, etc. We don't have any of that information from your question so it's hard to give specifics. If you want us to make some back-of-the-envelope calculations for you, please tell us more about the app. Here is what we would need to know first...
Please post the CREATE TABLE statements for all of the tables, along with a diagram showing relationships.
Tell us the number of transactions per day. A graph of hourly arrival rates would be useful.
Tell us the number of rows in each table.
Tell us about any "slow response" conditions that you might be experiencing.
With that information we may be able to drill down into the issues and help you find answers.
If performance is the issue that prompted you to make this move, please check the following...
1. Add indexes on every column used in WHERE, JOIN, ORDER, GROUP
2. Use EXPLAIN SELECT on every query that draws from more than one table
3. NEVER use SELECT * -- instead SELECT by column name
4. Add the LIMIT clause to every query that does not require a table scan
5. Avoid TEXT and BLOB data types wherever possible; use the server file system instead
HTH, ~Ray
There are many, many variables you need to consider - table structure, transaction arrival rate, relationships, table locking, etc. We don't have any of that information from your question so it's hard to give specifics. If you want us to make some back-of-the-envelope calculations for you, please tell us more about the app. Here is what we would need to know first...
Please post the CREATE TABLE statements for all of the tables, along with a diagram showing relationships.
Tell us the number of transactions per day. A graph of hourly arrival rates would be useful.
Tell us the number of rows in each table.
Tell us about any "slow response" conditions that you might be experiencing.
With that information we may be able to drill down into the issues and help you find answers.
If performance is the issue that prompted you to make this move, please check the following...
1. Add indexes on every column used in WHERE, JOIN, ORDER, GROUP
2. Use EXPLAIN SELECT on every query that draws from more than one table
3. NEVER use SELECT * -- instead SELECT by column name
4. Add the LIMIT clause to every query that does not require a table scan
5. Avoid TEXT and BLOB data types wherever possible; use the server file system instead
HTH, ~Ray
Hi,
What I'm most worried about is geo-locale of the stuff, up till now we've hosted our websites at our offices over bonded ADSL lines. website and MYSQL database all on one server. The reasons I decided to host locally:
1. Clients upload large files, and we don't want to spend additional time downloading them before we can start production.
2. all our job tracking runs off the same database, clients sign in, book in jobs, upload files, the jobs show on the "production" screens in each department as the previous department checks it off. Clients and support staff can check the status of the jobs through search screens.
Our internet in this side of the world is quite slow and not too reliable, so with opening up a second office in Mauritius, I don't want their customers to all be relying on our dodgey infrastructure on top of their own, it would additionally, be extremely high latency for them and their would also be the problem of the increased load of customers uploading and Mauritian office downloading from our DSL lines.
So I want to get them their own hosted VPS. But doing this means their database would likely be separate from ours which is far from ideal, any development changes need to be done twice, and it opens up issues for users who might use both branches having duplicate accounts, not having transparent and overall tracking etc.
so I really want both branches running off the same database.
At the same time, I don't want to be pushing myself into a corner where I'm too heavily invested in something that's both a single point of failure, and perhaps not able to grow further with us in the future.
There are problems with my developer's code, there are certain reports and certain tracking screens which I know place an un-due load on the database, I know he should really fix them now, but he's got his plate full with new development work, and I was hoping that by splitting things and having the simple lookups run off a local (local to the server running the webpage) copy of the database, while all writes are made to a master that is in a central hosted location which all the webservers can get their copies from in order to do their reads from it would spread the load a little and give us some more breathing room to finish our current projects, get the new office online, and then start working further on fixing the problems that I know are there, but will require a lot of time and energy to refine and optimise.
The new office will be starting off slow and take a long time to grow where we are now.
Anyway.
I didn't think it's good for a office in Mauritius to be running off a server in South Africa hosted on DSL lines, and because the new office in Mauritius is only going to be running with 1 of our brands, I can't justify me moving the ENTIRE webserver off site, I'm far from being ready to take that step, we host about 10 different domains with totally different, but integrated sites, they will be using 2.
Our "database" consists of about 8 databases, mostly with 5 - 10 tables each, but the core database has about 50 tables, and the tables range in length between 5 and 20 thoustand records.
Transactions, I don't know how many a day, I would estimate about 1000 writes and about 8000 reads.
It's not busy sites, we don't really use them for branding or marketing, it's functional services, people visit when they want to load and track jobs.
Tx for the advice.
Steve
What I'm most worried about is geo-locale of the stuff, up till now we've hosted our websites at our offices over bonded ADSL lines. website and MYSQL database all on one server. The reasons I decided to host locally:
1. Clients upload large files, and we don't want to spend additional time downloading them before we can start production.
2. all our job tracking runs off the same database, clients sign in, book in jobs, upload files, the jobs show on the "production" screens in each department as the previous department checks it off. Clients and support staff can check the status of the jobs through search screens.
Our internet in this side of the world is quite slow and not too reliable, so with opening up a second office in Mauritius, I don't want their customers to all be relying on our dodgey infrastructure on top of their own, it would additionally, be extremely high latency for them and their would also be the problem of the increased load of customers uploading and Mauritian office downloading from our DSL lines.
So I want to get them their own hosted VPS. But doing this means their database would likely be separate from ours which is far from ideal, any development changes need to be done twice, and it opens up issues for users who might use both branches having duplicate accounts, not having transparent and overall tracking etc.
so I really want both branches running off the same database.
At the same time, I don't want to be pushing myself into a corner where I'm too heavily invested in something that's both a single point of failure, and perhaps not able to grow further with us in the future.
There are problems with my developer's code, there are certain reports and certain tracking screens which I know place an un-due load on the database, I know he should really fix them now, but he's got his plate full with new development work, and I was hoping that by splitting things and having the simple lookups run off a local (local to the server running the webpage) copy of the database, while all writes are made to a master that is in a central hosted location which all the webservers can get their copies from in order to do their reads from it would spread the load a little and give us some more breathing room to finish our current projects, get the new office online, and then start working further on fixing the problems that I know are there, but will require a lot of time and energy to refine and optimise.
The new office will be starting off slow and take a long time to grow where we are now.
Anyway.
I didn't think it's good for a office in Mauritius to be running off a server in South Africa hosted on DSL lines, and because the new office in Mauritius is only going to be running with 1 of our brands, I can't justify me moving the ENTIRE webserver off site, I'm far from being ready to take that step, we host about 10 different domains with totally different, but integrated sites, they will be using 2.
Our "database" consists of about 8 databases, mostly with 5 - 10 tables each, but the core database has about 50 tables, and the tables range in length between 5 and 20 thoustand records.
Transactions, I don't know how many a day, I would estimate about 1000 writes and about 8000 reads.
It's not busy sites, we don't really use them for branding or marketing, it's functional services, people visit when they want to load and track jobs.
Tx for the advice.
Steve
Mostly I'm worried about latency.
Looking for a way to service both servers with a single database without clogging the whole thing with the trouble of our and Mauritius' high latency internet connections.
Steve
Looking for a way to service both servers with a single database without clogging the whole thing with the trouble of our and Mauritius' high latency internet connections.
Steve
Please post the CREATE TABLE statements for all of the tables, along with a diagram showing relationships.
Tell us the number of transactions per day. A graph of hourly arrival rates would be useful.
Tell us the number of rows in each table.
Tell us about any "slow response" conditions that you might be experiencing.
With that information we may be able to drill down into the issues and help you find answers.
Tell us the number of transactions per day. A graph of hourly arrival rates would be useful.
Tell us the number of rows in each table.
Tell us about any "slow response" conditions that you might be experiencing.
With that information we may be able to drill down into the issues and help you find answers.
@ StevenHook , , OK, thanks for the additional info, I was hoping an Expert with more data server knowledge would get here, but no. - -
Certainly improved database queries (indexs and such) would help, but you should have done that already regardless of any data slow-downs or not.
Centralizing your database is the way to go (my opinion), for your expansion, however, to me, it's a bad idea to try and do the data server on your own with just PHP code, I know that the MySQL setups (server settings, MySQL settings) have much in options for having large and fast database operations, but I have not done that setup, so no advice.
FRom your info about daily volume and data sizes, I feel like you may not need a master-slave setup, it does not strike me as overly large, but there can be BIG differences in data sets and performance depending on the data sizes and query interactions.
If you want (need) to do a partitioned master-slave data server, there are already many, many places and firms that have that type of thing (MySQL settings, Linux-apatche software), and know how to set it up and use it, , , they are time tested and functional.
There are many web hosts that also offer Data Server hosting, with management of that data-base server operations, you can look at this page for Rackspace
http://www.rackspace.com/managed_hosting/services/database/
** I do NOT recommend or promote Rackspace, I have heard someone talk of it, but it's just an example, there are many others.
AS I have tried express your problem is not rare, and many have already done this, you may look at these page about master slave MySQL setup -
http://www.howtoforge.com/mysql_master_master_replication
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
but these are a simple examples, and I know that there are hundreds other data set factors to make changes in your setup, not covered in those.
You may have better luck at getting advice for this on a MySQL server Forum site, maybe -
http://forums.mysql.com/
sorry I do not know any others.
there are some "Open Source" data cluster setups, all I can remember is "Big5" or something like that. I likely will not post again here.
Certainly improved database queries (indexs and such) would help, but you should have done that already regardless of any data slow-downs or not.
Centralizing your database is the way to go (my opinion), for your expansion, however, to me, it's a bad idea to try and do the data server on your own with just PHP code, I know that the MySQL setups (server settings, MySQL settings) have much in options for having large and fast database operations, but I have not done that setup, so no advice.
FRom your info about daily volume and data sizes, I feel like you may not need a master-slave setup, it does not strike me as overly large, but there can be BIG differences in data sets and performance depending on the data sizes and query interactions.
If you want (need) to do a partitioned master-slave data server, there are already many, many places and firms that have that type of thing (MySQL settings, Linux-apatche software), and know how to set it up and use it, , , they are time tested and functional.
There are many web hosts that also offer Data Server hosting, with management of that data-base server operations, you can look at this page for Rackspace
http://www.rackspace.com/managed_hosting/services/database/
** I do NOT recommend or promote Rackspace, I have heard someone talk of it, but it's just an example, there are many others.
AS I have tried express your problem is not rare, and many have already done this, you may look at these page about master slave MySQL setup -
http://www.howtoforge.com/mysql_master_master_replication
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
but these are a simple examples, and I know that there are hundreds other data set factors to make changes in your setup, not covered in those.
You may have better luck at getting advice for this on a MySQL server Forum site, maybe -
http://forums.mysql.com/
sorry I do not know any others.
there are some "Open Source" data cluster setups, all I can remember is "Big5" or something like that. I likely will not post again here.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial