It's so funny how scared and upset the MySQL guys become when you offer valid advice to people that accurately reflects the state of MySQL.
Every time I offer this, it gets modded up by neutral and pro-whatever database and then gets modded back down out of ignorance and fear from the MySQL camp.
I think that's pretty friggen funny. Are MySQL users, as a whole, really so scared to find out that better solutions exist? Or worse, find learn why, when and how it makes sense to even use a MySQL database?
Well, without digging deeper, we're faced to look at it rather standoffish terms and compare based on common standards.
Now then, add a couple of tables with million of rows and maybe some with 10s of millions, with joins, and I think you'll find that you're in serious territory of what people commonly consider to be large databases.
Based on that, your database, no matter how proud you are of it, is still generally considered to be a small database (medium at best).
Dang it. I guess someone did what I asked. My fault. At any rate, I was actually hoping the original parent (this post's grandparent), would be modded up. While I thank you for the karma, it was not my intention to have the parent post modded up.
I don't believe I can take you task on any of your points.
MySQL, like Access, has it's place. Sadly, both tend to be used far outside their comfort zones.
Bluntly, I am pro-PostgreSQL. That's not hard to see. I really am not anti-MySQL. As I said, it has its place. Just the same, as you too have identified, just about any project where you want to use MySQL, there is always a better solution available. In your case, SQLite appears to be your favorite hammer. With others, it's Firebird...etc... Personally, I don't care which you choose, I just like to remind people that there are almost always much better options available than MySQL.
Re:Problems with postgres in production
on
Why MySQL Grew So Fast
·
· Score: 2, Informative
You might want to consider upgrading. Most (all?) of these issues have long been resolved. In fact, the latest versions now support auto-vacuuming.
As long as your application is not holding transactions open and never completing them, vacumming should not be a problem at all.
If you are having serious performance issues, I sincerely hope that you've contacted someone on the mailing list to determine if they can be resolved. It's certainly possible that PostgreSQL is not right for the job. More often than not, the situations that you described almost always turn out to be improper performance tuning or unrealistic performance expectations for the available hardware; lumping the blame onto PostgreSQL's shoulders.
It does sound you have some problems there. What did the developers say when you asked for performance tuning assistance?
While I agree with everything you said, I did not mean to make it sound like they were exactly the same technologies. Hopefully, most everyone will understand my angle on it.
As many (most?) people that would be looking to use MySQL probably have some Access experience, it becomes a good matrix for comparison. Your average MySQL or Access user is not going to know what SQLite is. Thusly, if people can determine that Access may or may not be a good match for their project then I argue that they now have an answer to the question, "will MySQL be a good match for their project."
After all that is said and done, Access still is far more feature rich than is MySQL even though it is much slower. Just the same, they both scale rather poorly. Again, this allows those that don't know much about databases to have some basis for comparison. The result of that comparison should indicate if MySQL is a good choice for their project.
You should look at it like this. In one hand, you have an ounce of your favorite food. It has MSG on it a little bit too much salt. In the other hand, you have a pound of your favorite food. It is salted just right and has no MSG. You can only pick one. Your buddy and the talk around town says to pick the one ounce item as it can easily fit into your mouth. Which would you pick?
Obviously, all things being equal, people should be picking the one pound hand, to be eaten at your leisure. Yet, far too many people are listening to their buddy and the talk around town. Sadly, there's a lot of people running around with too much salt and MSG in their system and a hunger they can't satisfy.
I don't think it's all that hard to figure out the parallels here.;):)
If there was no advantage to threads, why would have they bothered with 2.x?
Because Apache on Windows was getting stomped by IIS on Windows in performance benchmarks. At that time, IIRC, Linux also has some process fork performance issues. The only way to address the issue on the Windows platform was to add support for threads. Since threads vs processes differs from unix platform to unix platform, the process model was kept as well. As a result, a hybrid process/thread model was created. The really cool thing is, it works well on all platforms and even allows for targeted tuning on various unix platforms to better suit the ideals for each. This allowed better tuning on Linux to account for kernel issues, which no longer exist.
In other words, it was a win-win for everyone, save only IIS.
Postgres is the only database in wide use which is not multithreaded.
So? And your point is?
Don't make up lame excuses as to why it's better then multi threaded databases.
Ah. Thankfully, I didn't have to make anything up. It's a simple fact. That means it's true to the layman reading this.;) In threaded applications, simple stack or data segment corruption is all that is needed to corrupt anything from a single field, all the way up to a page, and perhaps more. This doesn't mean that threading shouldn't be used. It does, however, mean that simply stating that PostgreSQL uses a process model and try to imply that it's bad, is completely false. Especially in light of the fact that electing to use a threaded model carries significantly higher risks. Worse, should a single thread become corrupted, it corrupts the whole process. That means, even if the moment the corruption occurs, no damage is done, the potential for damage is still lurking as it may still be lurking within the process it self. This also means that should the process fail, the entire database is down, having crashed more than likely. This also means that the likely hood for repeated corruption is also increased.
A process model, on the other hand, means that, at worst, a single connection (a single backend) will fail, allowing all other backends to continue. This, in theory, means a larger window for larger uptimes. Best of all, in the event that process corruption occurs, and the connection is transient, the potential for damage ends when the client disconnects. So, this leaves us with pretty much just shared memory being open to corruption. While it's possible this could go unnoticed, the odds are significantly lower that the corruption will go undetected before it has a chance to be written to disk, because of the data layout in shared memory and the implementation of checksums of hashes.
In other words, the simple fact is, aside from the process fork versus thread spawn overhead, a process model is easily argued to be a superior model. So really, what you hoped to be a plus is litterally a negative in the eyes of all that understand how these things are put together.
I fail to see your point. It's considered to be a small database by any real DBA with database experience. It's not domain specific (football or whatever)...it's a comparison across all RDBMs and their ability to effectively manage datasets. In this case, while 200,000 rows may seem like a lot to you, in database terms, it's a faily small dataset.
Ah! I knew something fishy was going on here (see my reply to grandparent). That explains it. I guess if the other platforms are using prepared queries properly optimized queries as needed, I'd be willing to accept those results even with caching enabled. Just the same, we both know, once that DB gets much contention, it's going to seriously crawl, especially with that cache enabled.
It's worth noting that to really be a fair comparison with the cache enabled, some additional code needs to be written for the other RDBMS to emulate the cached behavior. At any rate, assuming such an optimization would be allowed (no reason not to allow it), MySQL would quickly start falling behind even with its cache enabled and it's select only environment.
I completely agree with you here. I believe that once the native Win32 port of PostgreSQL finally becomes available, it will start to make some serious inroads.
The cygwin port is good for playing and development purposes, but it is not ready for production purposes. If someone is using it in production, good luck and more power to them. While I consider my self to be very pro-PostgreSQL, I wouldn't use the Win32 cygwin port in a production environment for anything.
For what it's worth, the native Win32 port is being actively worked on. Hopefully it will be available in the next major release. It may slip to the second release out. Nonetheless, it is actively being tested, developed, and worked on.
Trust me when I say that MySQL guys won't want to hear this. They've been assured by MySQL, that you don't want to do this. In fact, they've been told that it's down right a bad idea to do this.
In what way? Or is that something you read from a pro-MySQL site? Being multithreaded does not always translate into scalability or even better performance.
The flip side of that is, a single bug will not corrupt or take down your entire database. Being multithreaded means your are prone to data corruption and total RDBMS failure.
Now then, back to the performance issue. How does PostgreSQL's implementation limit its performance? The only argument I can come up with is the overhead associated with each new connection, as a new backend process is spawned.
Hate to burst your bubble, but that test highlights MySQL's greatest strength. Which is, what pretty much everyone openly admits. That is, readonly (select) environment. Serving web pages, what is generally very simple selects, has always been MySQL's best trick. Period.
Obviously, 4.x has further helped its best trick scale even better. The complaint about MySQL is under load, especially in a more complex environment, (concurrent selects/updates/inserts), MySQL historically turns into a major dog. Generally speaking, it normally falls to the back of the pack in every test where scalability and a non-read-only environment exists.
Now then, having said that, the chart only shows part of the picture. Were they allowed to turn the benchmarks to target each database or did they all run the exact same code? If the answer is, they all ran the exact same code, then the test is immediatley invalidated as it ignores many, many significant features that are pretty much standard on all modern RDBMS, save only MySQL. Did they use the default tables or did they use Inno tables? They need to use Inno tables to a fair comparison, especially so if the SQL wasn't implemented and optimized for the other platforms. Were the database connections persistent or transient? That too can be a major factor.
Based on the results, I *suspect* some of the concerns I have are true, but clearly, that's nothing more than FUD at this point. Care to provide a link which provides more details?
Thanks. I didn't know that. I'm honestly not surprised as it is simply, just the right thing to do.
IIRC, Postgresql also has checksums (or whatever), elsewhere within various pages and data structures, but the page level is the only I mostly clearly recall.
The problem is, most people would disagree. Why? It's not because many hate the DB....and many do. It's because MySQL teachs many non-standard and down right bad habits. If you want a system to learn SQL or relational theory, MySQL isn't right for the task. Why? Because very little of the learned knowledge will apply to any other system. If, after all, the intent is to learn a generalized body of knowledge, then by definition, you can't use MySQL.
If, on the other hand, your objective is to learn MySQL, then by all means, learn by using MySQL. MySQL is well known for teaching people enough to be very dangerous but never enough to produce clean, fast, effecient SQL code for other RDMBS. In other words, learning on MySQL will probably teach you how to make the least effective queries for other platforms.
MySQL users constantly forget that they are doing things wrong compared to most other systems. As a result, when they compare to other systems, they are almost always comparing best MySQL performance with absoluete worst case performance on other systems.
The fact that you're having to reply to such a statement highlights why he's using MySQL.
Nuff said.
Re:Views? Subqueries? Easy to move databases?
on
Why MySQL Grew So Fast
·
· Score: 2, Insightful
Well, I think you missed the point of MySQL. MySQL targets the "select x from y where a='123'" group. There's nothing complex about it. Serious database work is not done with MySQL, thusly, serious features required to do serious work are absent. This may change as MySQL matures, but the fact remains, its simplicity generally meets its user's skill level. Many (most?) MySQL users are there because they are clueless about RDBMS systems and MySQL was the first free product that ran on Windows that they heard associated with SQL. So, for them, the vast majority have no idea they are even missing powerful concepts such as subselects, ACID, triggers, RI, stored procedures, etc.
But that's the point isn't it? That is, if people would stop buying into the MySQL myth and picked a better solution from the start, you wouldn't need to think about a migration now, or in the future. It's features and scalability would grow with your project. In the odd event that you do need to migrate off of PostgreSQL, you'll have a far, far easier time migrating from PostgreSQL to Oracle or other RDBMS than you will migrating from MySQL to ANY platform. Their non-standard, non ANSI-SQL implementation assures that!
That's the point. Pick the right tool, the first time!
Re:MySQL got there first and was "good enough"
on
Why MySQL Grew So Fast
·
· Score: 2, Interesting
Under what conditions. Notice that the part you quote even made that stipulation.
Generally speaking, MySQL is wicked fast for a single user that only does selects. Once you leave that safety zone, it becomes a huge question mark for MySQL.
Want transactions and ACID? Performance just dropped. Want concurrent select/insert/updates? Performance just dropped a lot! It's fairly well understood that MySQL scales poorly.
I remember back in the day when Crays where king. Someone with a workstation came out and said, "cool! I just matched a Cray box's performance with my machine". He was talking about some off the wall benchmark. The follow up reply said, "now, run 10 instances on each system and let us know now the results." He came back saying that performance was less than 1/10 what the Cray was seeing. I mention this example because it sooo reminds me of MySQL versus just about any other real RDBMS.
There will always be situations where MySQL is fast. It's a simple database for simple tasks. Once things start to get complex or the load ramps up, MySQL suddenly starts turn pale.
Don't believe me? Feel free to finish reading here. It shouldn't be too hard to find pro-MySQL guys stating the same thing, though perhaps worded slightly differently.
You know, I've read this soo many times from MySQL users, talking about PostgreSQL. I'm constantly amazed. There is a readme file while walks you throught it. There is online documentation which walks you through it. There is an online administrators guide and WIKI documentation available which not only walks you though it, but adds insights and additional comments.
Can someone please tell me why so hard about reading 5 pages, or so, of instructions?!?!?
Slashdot Mirror
LOL! Thanks for the kind words.
;)
As for the ID, it's a good reminder that life's too short to take everything so seriously.
Cheers!
It's so funny how scared and upset the MySQL guys become when you offer valid advice to people that accurately reflects the state of MySQL.
Every time I offer this, it gets modded up by neutral and pro-whatever database and then gets modded back down out of ignorance and fear from the MySQL camp.
I think that's pretty friggen funny. Are MySQL users, as a whole, really so scared to find out that better solutions exist? Or worse, find learn why, when and how it makes sense to even use a MySQL database?
Well, without digging deeper, we're faced to look at it rather standoffish terms and compare based on common standards.
Now then, add a couple of tables with million of rows and maybe some with 10s of millions, with joins, and I think you'll find that you're in serious territory of what people commonly consider to be large databases.
Based on that, your database, no matter how proud you are of it, is still generally considered to be a small database (medium at best).
Dang it. I guess someone did what I asked. My fault. At any rate, I was actually hoping the original parent (this post's grandparent), would be modded up. While I thank you for the karma, it was not my intention to have the parent post modded up.
LOL.
I don't believe I can take you task on any of your points.
MySQL, like Access, has it's place. Sadly, both tend to be used far outside their comfort zones.
Bluntly, I am pro-PostgreSQL. That's not hard to see. I really am not anti-MySQL. As I said, it has its place. Just the same, as you too have identified, just about any project where you want to use MySQL, there is always a better solution available. In your case, SQLite appears to be your favorite hammer. With others, it's Firebird...etc... Personally, I don't care which you choose, I just like to remind people that there are almost always much better options available than MySQL.
You might want to consider upgrading. Most (all?) of these issues have long been resolved. In fact, the latest versions now support auto-vacuuming.
As long as your application is not holding transactions open and never completing them, vacumming should not be a problem at all.
If you are having serious performance issues, I sincerely hope that you've contacted someone on the mailing list to determine if they can be resolved. It's certainly possible that PostgreSQL is not right for the job. More often than not, the situations that you described almost always turn out to be improper performance tuning or unrealistic performance expectations for the available hardware; lumping the blame onto PostgreSQL's shoulders.
It does sound you have some problems there. What did the developers say when you asked for performance tuning assistance?
While I agree with everything you said, I did not mean to make it sound like they were exactly the same technologies. Hopefully, most everyone will understand my angle on it.
As many (most?) people that would be looking to use MySQL probably have some Access experience, it becomes a good matrix for comparison. Your average MySQL or Access user is not going to know what SQLite is. Thusly, if people can determine that Access may or may not be a good match for their project then I argue that they now have an answer to the question, "will MySQL be a good match for their project."
After all that is said and done, Access still is far more feature rich than is MySQL even though it is much slower. Just the same, they both scale rather poorly. Again, this allows those that don't know much about databases to have some basis for comparison. The result of that comparison should indicate if MySQL is a good choice for their project.
Amen!
You should look at it like this. In one hand, you have an ounce of your favorite food. It has MSG on it a little bit too much salt. In the other hand, you have a pound of your favorite food. It is salted just right and has no MSG. You can only pick one. Your buddy and the talk around town says to pick the one ounce item as it can easily fit into your mouth. Which would you pick?
;) :)
Obviously, all things being equal, people should be picking the one pound hand, to be eaten at your leisure. Yet, far too many people are listening to their buddy and the talk around town. Sadly, there's a lot of people running around with too much salt and MSG in their system and a hunger they can't satisfy.
I don't think it's all that hard to figure out the parallels here.
If there was no advantage to threads, why would have they bothered with 2.x?
Because Apache on Windows was getting stomped by IIS on Windows in performance benchmarks. At that time, IIRC, Linux also has some process fork performance issues. The only way to address the issue on the Windows platform was to add support for threads. Since threads vs processes differs from unix platform to unix platform, the process model was kept as well. As a result, a hybrid process/thread model was created. The really cool thing is, it works well on all platforms and even allows for targeted tuning on various unix platforms to better suit the ideals for each. This allowed better tuning on Linux to account for kernel issues, which no longer exist.
In other words, it was a win-win for everyone, save only IIS.
Please.
;) In threaded applications, simple stack or data segment corruption is all that is needed to corrupt anything from a single field, all the way up to a page, and perhaps more. This doesn't mean that threading shouldn't be used. It does, however, mean that simply stating that PostgreSQL uses a process model and try to imply that it's bad, is completely false. Especially in light of the fact that electing to use a threaded model carries significantly higher risks. Worse, should a single thread become corrupted, it corrupts the whole process. That means, even if the moment the corruption occurs, no damage is done, the potential for damage is still lurking as it may still be lurking within the process it self. This also means that should the process fail, the entire database is down, having crashed more than likely. This also means that the likely hood for repeated corruption is also increased.
Okay, since you insist.
Postgres is the only database in wide use which is not multithreaded.
So? And your point is?
Don't make up lame excuses as to why it's better then multi threaded databases.
Ah. Thankfully, I didn't have to make anything up. It's a simple fact. That means it's true to the layman reading this.
A process model, on the other hand, means that, at worst, a single connection (a single backend) will fail, allowing all other backends to continue. This, in theory, means a larger window for larger uptimes. Best of all, in the event that process corruption occurs, and the connection is transient, the potential for damage ends when the client disconnects. So, this leaves us with pretty much just shared memory being open to corruption. While it's possible this could go unnoticed, the odds are significantly lower that the corruption will go undetected before it has a chance to be written to disk, because of the data layout in shared memory and the implementation of checksums of hashes.
In other words, the simple fact is, aside from the process fork versus thread spawn overhead, a process model is easily argued to be a superior model. So really, what you hoped to be a plus is litterally a negative in the eyes of all that understand how these things are put together.
I fail to see your point. It's considered to be a small database by any real DBA with database experience. It's not domain specific (football or whatever)...it's a comparison across all RDBMs and their ability to effectively manage datasets. In this case, while 200,000 rows may seem like a lot to you, in database terms, it's a faily small dataset.
Ah! I knew something fishy was going on here (see my reply to grandparent). That explains it. I guess if the other platforms are using prepared queries properly optimized queries as needed, I'd be willing to accept those results even with caching enabled. Just the same, we both know, once that DB gets much contention, it's going to seriously crawl, especially with that cache enabled.
It's worth noting that to really be a fair comparison with the cache enabled, some additional code needs to be written for the other RDBMS to emulate the cached behavior. At any rate, assuming such an optimization would be allowed (no reason not to allow it), MySQL would quickly start falling behind even with its cache enabled and it's select only environment.
I completely agree with you here. I believe that once the native Win32 port of PostgreSQL finally becomes available, it will start to make some serious inroads.
The cygwin port is good for playing and development purposes, but it is not ready for production purposes. If someone is using it in production, good luck and more power to them. While I consider my self to be very pro-PostgreSQL, I wouldn't use the Win32 cygwin port in a production environment for anything.
For what it's worth, the native Win32 port is being actively worked on. Hopefully it will be available in the next major release. It may slip to the second release out. Nonetheless, it is actively being tested, developed, and worked on.
Trust me when I say that MySQL guys won't want to hear this. They've been assured by MySQL, that you don't want to do this. In fact, they've been told that it's down right a bad idea to do this.
So, who do you think they are going to believe?
In what way? Or is that something you read from a pro-MySQL site? Being multithreaded does not always translate into scalability or even better performance.
The flip side of that is, a single bug will not corrupt or take down your entire database. Being multithreaded means your are prone to data corruption and total RDBMS failure.
Now then, back to the performance issue. How does PostgreSQL's implementation limit its performance? The only argument I can come up with is the overhead associated with each new connection, as a new backend process is spawned.
Hate to burst your bubble, but that test highlights MySQL's greatest strength. Which is, what pretty much everyone openly admits. That is, readonly (select) environment. Serving web pages, what is generally very simple selects, has always been MySQL's best trick. Period.
Obviously, 4.x has further helped its best trick scale even better. The complaint about MySQL is under load, especially in a more complex environment, (concurrent selects/updates/inserts), MySQL historically turns into a major dog. Generally speaking, it normally falls to the back of the pack in every test where scalability and a non-read-only environment exists.
Now then, having said that, the chart only shows part of the picture. Were they allowed to turn the benchmarks to target each database or did they all run the exact same code? If the answer is, they all ran the exact same code, then the test is immediatley invalidated as it ignores many, many significant features that are pretty much standard on all modern RDBMS, save only MySQL. Did they use the default tables or did they use Inno tables? They need to use Inno tables to a fair comparison, especially so if the SQL wasn't implemented and optimized for the other platforms. Were the database connections persistent or transient? That too can be a major factor.
Based on the results, I *suspect* some of the concerns I have are true, but clearly, that's nothing more than FUD at this point. Care to provide a link which provides more details?
Thanks. I didn't know that. I'm honestly not surprised as it is simply, just the right thing to do.
IIRC, Postgresql also has checksums (or whatever), elsewhere within various pages and data structures, but the page level is the only I mostly clearly recall.
The problem is, most people would disagree. Why? It's not because many hate the DB....and many do. It's because MySQL teachs many non-standard and down right bad habits. If you want a system to learn SQL or relational theory, MySQL isn't right for the task. Why? Because very little of the learned knowledge will apply to any other system. If, after all, the intent is to learn a generalized body of knowledge, then by definition, you can't use MySQL.
If, on the other hand, your objective is to learn MySQL, then by all means, learn by using MySQL. MySQL is well known for teaching people enough to be very dangerous but never enough to produce clean, fast, effecient SQL code for other RDMBS. In other words, learning on MySQL will probably teach you how to make the least effective queries for other platforms.
I wish someone would mod this up.
MySQL users constantly forget that they are doing things wrong compared to most other systems. As a result, when they compare to other systems, they are almost always comparing best MySQL performance with absoluete worst case performance on other systems.
The fact that you're having to reply to such a statement highlights why he's using MySQL.
Nuff said.
Well, I think you missed the point of MySQL. MySQL targets the "select x from y where a='123'" group. There's nothing complex about it. Serious database work is not done with MySQL, thusly, serious features required to do serious work are absent. This may change as MySQL matures, but the fact remains, its simplicity generally meets its user's skill level. Many (most?) MySQL users are there because they are clueless about RDBMS systems and MySQL was the first free product that ran on Windows that they heard associated with SQL. So, for them, the vast majority have no idea they are even missing powerful concepts such as subselects, ACID, triggers, RI, stored procedures, etc.
Remember, happiness is completley subjective.
But that's the point isn't it? That is, if people would stop buying into the MySQL myth and picked a better solution from the start, you wouldn't need to think about a migration now, or in the future. It's features and scalability would grow with your project. In the odd event that you do need to migrate off of PostgreSQL, you'll have a far, far easier time migrating from PostgreSQL to Oracle or other RDBMS than you will migrating from MySQL to ANY platform. Their non-standard, non ANSI-SQL implementation assures that!
That's the point. Pick the right tool, the first time!
Under what conditions. Notice that the part you quote even made that stipulation.
Generally speaking, MySQL is wicked fast for a single user that only does selects. Once you leave that safety zone, it becomes a huge question mark for MySQL.
Want transactions and ACID? Performance just dropped. Want concurrent select/insert/updates? Performance just dropped a lot! It's fairly well understood that MySQL scales poorly.
I remember back in the day when Crays where king. Someone with a workstation came out and said, "cool! I just matched a Cray box's performance with my machine". He was talking about some off the wall benchmark. The follow up reply said, "now, run 10 instances on each system and let us know now the results." He came back saying that performance was less than 1/10 what the Cray was seeing. I mention this example because it sooo reminds me of MySQL versus just about any other real RDBMS.
There will always be situations where MySQL is fast. It's a simple database for simple tasks. Once things start to get complex or the load ramps up, MySQL suddenly starts turn pale.
Don't believe me? Feel free to finish reading here. It shouldn't be too hard to find pro-MySQL guys stating the same thing, though perhaps worded slightly differently.
You know, I've read this soo many times from MySQL users, talking about PostgreSQL. I'm constantly amazed. There is a readme file while walks you throught it. There is online documentation which walks you through it. There is an online administrators guide and WIKI documentation available which not only walks you though it, but adds insights and additional comments.
Can someone please tell me why so hard about reading 5 pages, or so, of instructions?!?!?