I don't have a specific application in mind. I've developed several applications using MySQL, but always with a small enough user base that performance never was an issue.
Last year, I was working on a more demanding application. I wanted to use MySQL, but was given specific instructions to use MSSQL. The project manager did not feel confident in MySQL's performance and was not willing to discuss it, even though there were practical advantages to us if we used MySQL. Since then I have been very curious if there was any truth to his performance concerns.
This is why the above story of a company switching from MySQL to MSSQL was so interesting to me. I'd be curious to know if they tried MySQL Cluster or any of your other suggestions before jumping ship.
Do you have an answer to the core limitation described by the original poster? What can be done when a single server is simply not fast enough to handle all the needed write operations? A read-heavy application can easily have its load distributed using replication. But replication only has limited performance benefits for a write-heavy application. So how can MySQL be scaled in such a case beyond the limitations of a single server?
I tried to do some research and get a discussion going in the above comments, but I didn't find a definitive answer. This is an essential question, because it would be foolish to start any project with a database that couldn't scale to match any future needs.
Yeah, I missed that. I was reading this all pretty quickly.:)
I'm no expert, I was just trying to get at one core question: is MySQL really not scalable beyond the limits of a single machine? I don't have a solid conclusion about that yet.
What would we get if we calculated out the averages for every sequence, created an artifical DNA strand with that sequence, and grew it using cloning technology? Some kind of perfect man, or a genetic freak?
Sorry to follow up my own post, but I've been doing some interesting reading on MySQL tuning. Here is one reason that replication could help improve performance and avoid the locking issues:
When a client performs a large SELECT on a MyISAM table, it obtains a read lock on the data. Until the SELECT completes, the server won't release the lock and service any pending write requests for the table.
That's from this article. They also say that the most likely cause of performance issues is inefficient queries. I wonder if a review of all those hand-written queries the original author describes might find many places for simple performance increases? Rewriting queries or switching to stored procedures could be the easiest sort of change to make.
The next chapter gets more into the details of replication. There is a big downside: all the slave machines must perform all the same writes as the master machine, so they could only offload so much of the read load.
The best answer to scaling a write-heavy MySQL application seems to be data partitioning. This is brand new with the MySQL 5.1 beta, so it's yet to really be tested, but it's good to know there is at least a promising option underway.
I think that clustering could still help, because if the server doing the writes has its reads offloaded, it should be able to devote more resources to writes. But yeah, they still might run up against the limits of how fast a single server can handle all their writes.
I've also heard that MySQL is more optimized for reads at the expense of writes. Still, I find it hard to believe that there's no solution to this person's problem short of switching databases. I'd be curious to hear from any MySQL gurus reading this.
MySQL supports clustering. Why are you limiting your database to run on a single server? It sounds like that's the main problem here. Instead of trying to push MySQL past its limits, offload some of the work and spread it out across multiple machines. Surely that's more cost effective than converting your entire application to a new database?
I want a moonbase too, but I don't care as much where we go on our next mission. To establish a meaningful, lasting presence in space, we must reinvent the launch vehicle. Current space missions are so expensive because of launch costs. Because launching is so expensive, payloads are much more expensive and limited as well, because they must be built with every ounce of weight carefully planned.
Rather than spending the next 20 years and billions of dollars on repeating the Apollo missions (or doing just slightly better), I'd rather we spent the effort on developing better launch technology. Once we can take larger loads to space for much cheaper, all sorts of space exploration will naturally follow, including a moonbase.
Ideas for how to build such a vehicle exist. My favorite is described here. Unfortunately, it is harder to gain support for such a plan, but if it's the plan with the best long term potential, we should be doing it.
1. Repair Democracy - Utilize a better voting system such as range voting or ranked pairs which does not lead to a two-party system. Change voting laws to reduce the influence of corporations and big money.
2. Return Culture to the People - Reduce copyright terms to short, reasonable time periods, only as necessary to encourage new works. Launch a major federal operation to archive as many movies, songs, books, and other forms of expression as is feasible. Make these freely downloadable once their copyrights expire.
3. Optimize the Legal System - Make it more difficult to sue, and make those who sue irresponsibly pay for the time they wasted. Get people smarter than me together to figure out how to work more common sense into the legal system. Make it harder to prove malpractice, to help control medical costs. Remove the complex jury selection process, using the simpler, more expedient, and fairer British system.
4. Use the Internet to Involve People in Government - Try developing a system in which people can make suggestions, discuss current legislation, etc, and have their leaders listening and involved in the discussion.
5. Protect Basic Rights - Privacy, etc.
These are what I would bring to the job personally. For the other big issues, I would have to lean on the wisdom of others.
What are you suggesting? A communist utopia? The willful renouncing of material goods by all the well-to-do individuals in the world? Who do you have in mind with the power to "structure our world" as you phrased it?
I don't think we're realistically going to get away from a world where some have more than others, and some others are jealous. Perhaps we can work to make things more fair, but even if that happens there will always be troublemakers out there. There's nothing bad about building a reliable, fraud resistant banking system.
I have what I think is an awesome idea for Amazon. Give everyone who buys mp3s a free S3 storage account. For every mp3 purchased, create a virtual link to the file in their S3 account.
Now you have a backup of all your purchased music at no charge, which you can download at any time at standard S3 download rates. And, of course, you can feel free to use that S3 account for other purposes if you like. But there's no monthly fee for storing the mp3s since Amazon only needs to keep a single copy of each song for all users.
No, it means we should thank him for demonstrating this flaw in the current system, and work on a solution to prevent future abuses.
(Not hard, either. Nearly every bank is online now. If a transfer request comes from an unrecognized source, the account owner should have to approve it online.)
Ranked Pairs is far and away the best option. It satisfies the monotonocity criterion. It is very simple to understand, especially from the perspective of a voter. You simply rank who you want to vote for. (Any voter who doesn't like it can simply rank their candidate #1 and leave the rest blank, just as we do now.)
The big win here is that it has the potential to break down the two-party deadlock. How often do people say "I'd like to vote for third party candidate X, but I don't want to throw away my vote?" Ranked Pairs allows you to vote for X, while also expressing your preference for A over B. If X is eliminated, your vote for A over B will count just as much as if it were your only vote.
There are two main problems with range voting. One, it makes voting overly complicated (not to the Slashdot crowd, but for the average voter.) Two, it is subject to gaming. Voters will be likely to rate secondary candidates lower than how they really feel in order to increase their top candidate's odds. With Ranked Pairs, there is some possibility of gaming (no election system is perfect) but it is minimized, and most often the best strategy is to vote honestly.
The answer is to hold the project managers and cost estimators personally responsible. If they can't deliver on their promises, they should know that their jobs may be in jeopardy. Likewise, those who can manage a budget well should be promoted. In this way, you can have accountability without dooming a $550m project to failure just to prove an expensive point.
At the same time, a careful analysis needs to be made of just how and why a project gets to be over budget. Was it poor planning, poor management, or was it simply an unforeseeable expense? There has to be some room for the budget to grow when you're doing theoretical, cutting edge science. Not everything can be predicted.
Slashdot Mirror
Thank you for supplying your expert opinion. At last we can lay the Perl v. PHP debate to rest.
I don't have a specific application in mind. I've developed several applications using MySQL, but always with a small enough user base that performance never was an issue.
Last year, I was working on a more demanding application. I wanted to use MySQL, but was given specific instructions to use MSSQL. The project manager did not feel confident in MySQL's performance and was not willing to discuss it, even though there were practical advantages to us if we used MySQL. Since then I have been very curious if there was any truth to his performance concerns.
This is why the above story of a company switching from MySQL to MSSQL was so interesting to me. I'd be curious to know if they tried MySQL Cluster or any of your other suggestions before jumping ship.
Do you have an answer to the core limitation described by the original poster? What can be done when a single server is simply not fast enough to handle all the needed write operations? A read-heavy application can easily have its load distributed using replication. But replication only has limited performance benefits for a write-heavy application. So how can MySQL be scaled in such a case beyond the limitations of a single server?
I tried to do some research and get a discussion going in the above comments, but I didn't find a definitive answer. This is an essential question, because it would be foolish to start any project with a database that couldn't scale to match any future needs.
Yeah, I missed that. I was reading this all pretty quickly. :)
I'm no expert, I was just trying to get at one core question: is MySQL really not scalable beyond the limits of a single machine? I don't have a solid conclusion about that yet.
What would we get if we calculated out the averages for every sequence, created an artifical DNA strand with that sequence, and grew it using cloning technology? Some kind of perfect man, or a genetic freak?
I think I'd make a good mad scientist...
The next chapter gets more into the details of replication. There is a big downside: all the slave machines must perform all the same writes as the master machine, so they could only offload so much of the read load.
The best answer to scaling a write-heavy MySQL application seems to be data partitioning. This is brand new with the MySQL 5.1 beta, so it's yet to really be tested, but it's good to know there is at least a promising option underway.
I think that clustering could still help, because if the server doing the writes has its reads offloaded, it should be able to devote more resources to writes. But yeah, they still might run up against the limits of how fast a single server can handle all their writes.
I've also heard that MySQL is more optimized for reads at the expense of writes. Still, I find it hard to believe that there's no solution to this person's problem short of switching databases. I'd be curious to hear from any MySQL gurus reading this.
MySQL supports clustering. Why are you limiting your database to run on a single server? It sounds like that's the main problem here. Instead of trying to push MySQL past its limits, offload some of the work and spread it out across multiple machines. Surely that's more cost effective than converting your entire application to a new database?
I want a moonbase too, but I don't care as much where we go on our next mission. To establish a meaningful, lasting presence in space, we must reinvent the launch vehicle. Current space missions are so expensive because of launch costs. Because launching is so expensive, payloads are much more expensive and limited as well, because they must be built with every ounce of weight carefully planned.
Rather than spending the next 20 years and billions of dollars on repeating the Apollo missions (or doing just slightly better), I'd rather we spent the effort on developing better launch technology. Once we can take larger loads to space for much cheaper, all sorts of space exploration will naturally follow, including a moonbase.
Ideas for how to build such a vehicle exist. My favorite is described here. Unfortunately, it is harder to gain support for such a plan, but if it's the plan with the best long term potential, we should be doing it.
Things a robot taking over your brain would say?
But how do you determine if the car is unconscious? Ask if it knows what model it is?
Calling Coupling a raunchier version of Friends is like calling a porterhouse steak a thicker version of the McDonald's burger patty.
1. Repair Democracy - Utilize a better voting system such as range voting or ranked pairs which does not lead to a two-party system. Change voting laws to reduce the influence of corporations and big money.
2. Return Culture to the People - Reduce copyright terms to short, reasonable time periods, only as necessary to encourage new works. Launch a major federal operation to archive as many movies, songs, books, and other forms of expression as is feasible. Make these freely downloadable once their copyrights expire.
3. Optimize the Legal System - Make it more difficult to sue, and make those who sue irresponsibly pay for the time they wasted. Get people smarter than me together to figure out how to work more common sense into the legal system. Make it harder to prove malpractice, to help control medical costs. Remove the complex jury selection process, using the simpler, more expedient, and fairer British system.
4. Use the Internet to Involve People in Government - Try developing a system in which people can make suggestions, discuss current legislation, etc, and have their leaders listening and involved in the discussion.
5. Protect Basic Rights - Privacy, etc.
These are what I would bring to the job personally. For the other big issues, I would have to lean on the wisdom of others.
We are robots, you insensitive clod!
What are you suggesting? A communist utopia? The willful renouncing of material goods by all the well-to-do individuals in the world? Who do you have in mind with the power to "structure our world" as you phrased it?
I don't think we're realistically going to get away from a world where some have more than others, and some others are jealous. Perhaps we can work to make things more fair, but even if that happens there will always be troublemakers out there. There's nothing bad about building a reliable, fraud resistant banking system.
I have what I think is an awesome idea for Amazon. Give everyone who buys mp3s a free S3 storage account. For every mp3 purchased, create a virtual link to the file in their S3 account.
Now you have a backup of all your purchased music at no charge, which you can download at any time at standard S3 download rates. And, of course, you can feel free to use that S3 account for other purposes if you like. But there's no monthly fee for storing the mp3s since Amazon only needs to keep a single copy of each song for all users.
But they'd still be raiding the musical booty of other countries, so they still can hoist the old Jolly Roger.
T<span style='display: {can_prove(P = NP)};'>h</span>e
Acid3 is hard.
No, it means we should thank him for demonstrating this flaw in the current system, and work on a solution to prevent future abuses.
(Not hard, either. Nearly every bank is online now. If a transfer request comes from an unrecognized source, the account owner should have to approve it online.)
They haven't solved the integer problem yet, but if you need to store strings you should take a look at the latest data center they are "rolling out."
So they're going after grep?
Ranked Pairs is far and away the best option. It satisfies the monotonocity criterion. It is very simple to understand, especially from the perspective of a voter. You simply rank who you want to vote for. (Any voter who doesn't like it can simply rank their candidate #1 and leave the rest blank, just as we do now.)
The big win here is that it has the potential to break down the two-party deadlock. How often do people say "I'd like to vote for third party candidate X, but I don't want to throw away my vote?" Ranked Pairs allows you to vote for X, while also expressing your preference for A over B. If X is eliminated, your vote for A over B will count just as much as if it were your only vote.
There are two main problems with range voting. One, it makes voting overly complicated (not to the Slashdot crowd, but for the average voter.) Two, it is subject to gaming. Voters will be likely to rate secondary candidates lower than how they really feel in order to increase their top candidate's odds. With Ranked Pairs, there is some possibility of gaming (no election system is perfect) but it is minimized, and most often the best strategy is to vote honestly.
The answer is to hold the project managers and cost estimators personally responsible. If they can't deliver on their promises, they should know that their jobs may be in jeopardy. Likewise, those who can manage a budget well should be promoted. In this way, you can have accountability without dooming a $550m project to failure just to prove an expensive point.
At the same time, a careful analysis needs to be made of just how and why a project gets to be over budget. Was it poor planning, poor management, or was it simply an unforeseeable expense? There has to be some room for the budget to grow when you're doing theoretical, cutting edge science. Not everything can be predicted.
Thanks, that's good to know! Of course, I'll believe it when I receive it.