Without any reference to the hardware, it's hard to say if 25M/s is fast or slow. On my laptop running an external USB hard drive that's quite peppy. On my production file server with 16 fast SATA drives it's godawful slow. But without some form of reference, it's just another number.
And you're so sure of your experiences that you post as AC. Sure sure, you're not trolling. I've had / currently admin linux servers (file, web, app, db) with years of uptime and no crashes and average load factors of 4 to 40 in daily use. NO CRASHES
Last place I worked we had two monitors for every developer, and we had about 40 developers. Place I work now has 4 developers, and 3 have 2 monitors and 1 has 3 monitors. The one thing we found in both places is that older Nvidia cards work best. 7800 series, stuff like that. Get the latest cards and you'll pull your hair out trying to get them to work.
Note that there are plenty of solid reliable replication engines available RIGHT NOW. No, none are in the main branch because none are good enough yet, and there's more than one way to skin the replication cat.
Bucardo Slony Londiste Mammoth Replicator and a half dozen or so more I can't remember right now. Each one, in its own right, is better than MySQL's built in replication. If someone submitted that pile of dog turds to the pg project it would never have been accepted.
Howver, if PostgreSQL were to come out with a MySQL compatibility front end
I can tell you here and now that will never happen. Given MySQL's insane disregard for standards, the needed gutting of pgsql to allow it to behave in the same manner as MySQL is not going to happen, ever.
Silently ignore column level FK references? Silently truncate ints that are too big to fit? Silently allow int inserts into an enum to insert the int key directly without error?
There are way too many "oh dear god!" levels of gotchas in mysql to ever try implementing them in pgsql as a compatibility layer.
Now, there's a company out there that provides an Oracle compatibility level that works pretty well. That's something you can sell reasonably well.
7.0.0 was released on 200-05-08, or about 8 months later, and here's the list of changes / updates to that: http://www.postgresql.org/docs/8.4/static/release-7-0.html (I count 4 major architectural changes and about 275 or so minor changes)
Yeah, no development on pgsql 10 years ago. You might try fact checking your shit next time.
Sorry, but often putting 10 people on the same job one genius could do in a week results in a year long project that never reaches its goals. It's "The Mythical Man Month" at work.
I've been using the 1680 series for a couple of years now and they've been rock solid, for the most part. I had one that was delivered bad, replaced it and the replacement is running smoothly a year later. Have you got any kind of outside numbers that show them having a higher failure rate / data corruption rate? The brand I've had problems with in the past has mostly been adaptec.
I've built materialized views following the online tutorial for pgsql. Took me about 1 hour to set them up and have them working. And it's not simulating a materialized view, it IS a materialized view. With the materialized view, pg also uses the indexes on it. And you don't have to change the app, for christ's sake you just put the materialized view in place of the old view. You ever actually use postgres for much?
MySQL was once much faster at lots of small queries. Now that's not so true. It's still faster, but only by a few percent most the time.
I know a lot of developers who had the same attitude you're putting forth, that they don't need complex queries. Then I rewrote their reporting apps which ran for 20 or 30 minutes by pulling in all the data one table at a time and munging it together. The same app, using pg to return the same data but do the work in the db dropped the time down into the sub minute range. As a bonus, the load on the db server went down too.
The type of thing MySQL is good at is content management, where the queries are usually simple and small. for that kind of stuff I can totally understand sticking with it. Like Slashdot!
I find slony works fine and scales well enough, but you often have to setup a star system. replicate to one slave, that replicates to all the other slaves and does nothing else. Also, slony runs slow on slower hardware, but on bigger 4 to 16 core servers with plenty of IO bandwidth, it runs quite quickly.
And when it stops replicating, there's a reason, usually my mistake, that it did.
But it really depends on what part of the work you're talking about as to whether or not the db is the better choice to do it. It's also not uncommon to see things done on the app server cost as much, if not more, in terms of the db server overhead, because they have to move more data out and back into the db to do what the db can do locally. Imagine running full text searches by retrieving every row and examining it in the app layer. No way is that gonna be better than letting the db do it.
I've yet to see an app provide data integrity cheaper than the db can do it, and there's also the issue of consistency and race conditions if the app is trying to do it.
OTOH, a lot of business logic is much better suited to being in the app layer. Generally speaking, IO intensive things belong in the DB, CPU intensive things belong in the app layer.
MySQL definitely supports foreign keys. Just make sure you're not accidentally using a myisam table, and don't use column level reference defs, cause both will result in a silent ignoring of what you asked it to do.
Wish there was a way to tell it to only use certain table types and throw errors when things go wrong though.
What's the current GA? 5.1. Can you do cross db transactions in 5.1? No. Sorry, but you should really learn more about the db you're a fanboi of if you're gonna tell me I'm talking out my ass. Sorry, but no one's making you look stupid but you.
Really? I've found there to be plenty of rabid fanboi types on both side of the fence. Pgsql users often still have a chip on their shoulder from the totally slanderous statements MySQL AB used to make about PostgreSQL, and they made a lot of them, and for a long time, it stuck, and I heard them repeated over and over from mysql fanbois.
I tend to ignore fanbois on both sides of the fence and consider the two positions based on reason, evidence, experience, testing, and support. I find that pgsql beats mysql on most all those accounts. But I've used both a fair bit before, so I have a basis for comparison.
Healthy suspicion is a good thing, it keeps us away from things like porcupines. OTOH, relying on just suspicion won't get you very far. Try it out, see how it really works. That applies to everything, not just databases.
I think it might be worth examining the differences between the two dbs. I've found some truly compelling things about pgsql. Here's a short list of the things I really like about pgsql:
Transactional DDL statements. Sounds kinda trivial, but it lets you do things like alter a table, update a field, and then, if things go horribly wrong, you can roll it back. For production updates, this can be a life saver. In pgsql everything except create / drop database and create drop tablespace are transactable.
The CTRL-C works like it should in psql, it cancels the current command. The fact that it exits the session in mysql's command line tool is idiotic, and should have been fixed years ago.
Fast and reliable bug fixes. I'd say the average time a nasty bug (crash, wrong results, data corruption) is fixed is within one to two days after it's found. There aren't a lot, but when the pg crew finds out about one, they stomp it fast.
I would agree that none of the new features would make switching from MySQL to PostgreSQL compelling. OTOH, the features PostgreSQL has had for quite some time ARE compelling to switch. Like storing the db meta data in transaction safe tables (pgsql) versus storing them in non-crash / non-transaction safe tables (MySQL). Or making sure that what you put in either goes in or throws an error without mangling the data (i.e. MySQL inserts 'stan' into a timestamp field as 0000-00-00 00:00:00 a non-existent and nonsense value, or inserting 2^31-1 for a value overflowing a 4 byte int, etc) Or the fact that MySQL still has issues with multibyte encodings, or that you can't have full text indexing on transactional tables like innodb. Or the fact that MySQL has serious and known bugs that haven't been addressed for years. Or the fact that MySQL has added known bugs to production releases because their QA is crap, for example, the order by DESC bug with regards to innodb tables, which showed up, got fixed, then showed up again all in the 5.0 GA release.
In fact, I'm really hoping that the community just takes MySQL away from Oracle / Sun / MySQL AB and fixes it and maintains it as a pure GPL only product. It deserves better treatment than it's gotten in the past by MySQL AB and their shoddy release practices. So far, the signs are good that that may in fact happen.
Re:I really wish they improved clustering
on
PostgreSQL 8.4 Out
·
· Score: 1
There are some first runs at such a thing, but they're all alpha level code or have serious restrictions. Look for Postgres-R and Bucardo to see if they can do what you need. Neither are RAC, but then RAC isn't necessarily the best answer all the time either for multi-master, as sometimes you need real shared_nothing clustering.
Re:[Sigh]... Still waiting for bulk loading...
on
PostgreSQL 8.4 Out
·
· Score: 1
You should look up both pg_loader (which acts a lot like oracles bulk loader) or pg_bulkloader which does the dirty under the covers trick to just stick data into tables while the db is down and when it comes back up viola, there it is. Both are quite impressive, but have different use cases.
Slashdot Mirror
Without any reference to the hardware, it's hard to say if 25M/s is fast or slow. On my laptop running an external USB hard drive that's quite peppy. On my production file server with 16 fast SATA drives it's godawful slow. But without some form of reference, it's just another number.
Sounds like you spend too much time reading marketing materials and not enough time testing things.
And you're so sure of your experiences that you post as AC. Sure sure, you're not trolling. I've had / currently admin linux servers (file, web, app, db) with years of uptime and no crashes and average load factors of 4 to 40 in daily use. NO CRASHES
Sorry you linux-fu and troll-fu suck so much ass.
Last place I worked we had two monitors for every developer, and we had about 40 developers. Place I work now has 4 developers, and 3 have 2 monitors and 1 has 3 monitors. The one thing we found in both places is that older Nvidia cards work best. 7800 series, stuff like that. Get the latest cards and you'll pull your hair out trying to get them to work.
Yep, but ask on slashdot and you'll find out you and I and other pgsql users are the "fanbois". urg.
Note that there are plenty of solid reliable replication engines available RIGHT NOW. No, none are in the main branch because none are good enough yet, and there's more than one way to skin the replication cat.
Bucardo
Slony
Londiste
Mammoth Replicator
and a half dozen or so more I can't remember right now. Each one, in its own right, is better than MySQL's built in replication. If someone submitted that pile of dog turds to the pg project it would never have been accepted.
Howver, if PostgreSQL were to come out with a MySQL compatibility front end
I can tell you here and now that will never happen. Given MySQL's insane disregard for standards, the needed gutting of pgsql to allow it to behave in the same manner as MySQL is not going to happen, ever.
Silently ignore column level FK references?
Silently truncate ints that are too big to fit?
Silently allow int inserts into an enum to insert the int key directly without error?
There are way too many "oh dear god!" levels of gotchas in mysql to ever try implementing them in pgsql as a compatibility layer.
Now, there's a company out there that provides an Oracle compatibility level that works pretty well. That's something you can sell reasonably well.
Wow, not just misinformed, but proud of it too!
I started using pg about 10 years ago on version 6.5.2, released 1999-09-15. Here's a list of fixes that 6.5.2 provided:
http://www.postgresql.org/docs/8.4/static/release-6-5-2.html (I count 23). note the previous version, 6.5.1 was released 1999-07-15. So that's 23 changes in two months. 10 years ago.
7.0.0 was released on 200-05-08, or about 8 months later, and here's the list of changes / updates to that:
http://www.postgresql.org/docs/8.4/static/release-7-0.html (I count 4 major architectural changes and about 275 or so minor changes)
Yeah, no development on pgsql 10 years ago. You might try fact checking your shit next time.
SCO is the only way to GO!
Sorry, but often putting 10 people on the same job one genius could do in a week results in a year long project that never reaches its goals. It's "The Mythical Man Month" at work.
I've been using the 1680 series for a couple of years now and they've been rock solid, for the most part. I had one that was delivered bad, replaced it and the replacement is running smoothly a year later. Have you got any kind of outside numbers that show them having a higher failure rate / data corruption rate? The brand I've had problems with in the past has mostly been adaptec.
I've built materialized views following the online tutorial for pgsql. Took me about 1 hour to set them up and have them working. And it's not simulating a materialized view, it IS a materialized view. With the materialized view, pg also uses the indexes on it. And you don't have to change the app, for christ's sake you just put the materialized view in place of the old view. You ever actually use postgres for much?
Next item on your list?
You can also cobble together some pretty cool stuff using plproxy and views.
This has been explained previously in this thread.
In Postgresql, schemas are equivalent to databases in MySQL. MySQL has no equivalency to PostgreSQL's database really.
Of course, I don't think you're actually looking for an answer, you're just being a mysql fanboi
MySQL was once much faster at lots of small queries. Now that's not so true. It's still faster, but only by a few percent most the time.
I know a lot of developers who had the same attitude you're putting forth, that they don't need complex queries. Then I rewrote their reporting apps which ran for 20 or 30 minutes by pulling in all the data one table at a time and munging it together. The same app, using pg to return the same data but do the work in the db dropped the time down into the sub minute range. As a bonus, the load on the db server went down too.
The type of thing MySQL is good at is content management, where the queries are usually simple and small. for that kind of stuff I can totally understand sticking with it. Like Slashdot!
I find slony works fine and scales well enough, but you often have to setup a star system. replicate to one slave, that replicates to all the other slaves and does nothing else. Also, slony runs slow on slower hardware, but on bigger 4 to 16 core servers with plenty of IO bandwidth, it runs quite quickly.
And when it stops replicating, there's a reason, usually my mistake, that it did.
But it really depends on what part of the work you're talking about as to whether or not the db is the better choice to do it. It's also not uncommon to see things done on the app server cost as much, if not more, in terms of the db server overhead, because they have to move more data out and back into the db to do what the db can do locally. Imagine running full text searches by retrieving every row and examining it in the app layer. No way is that gonna be better than letting the db do it.
I've yet to see an app provide data integrity cheaper than the db can do it, and there's also the issue of consistency and race conditions if the app is trying to do it.
OTOH, a lot of business logic is much better suited to being in the app layer. Generally speaking, IO intensive things belong in the DB, CPU intensive things belong in the app layer.
MySQL definitely supports foreign keys. Just make sure you're not accidentally using a myisam table, and don't use column level reference defs, cause both will result in a silent ignoring of what you asked it to do.
Wish there was a way to tell it to only use certain table types and throw errors when things go wrong though.
Holy shit! I just tried it and amazing, it does work. Wow. I remembered using an old version where that wasn't the case. Well, I guess I was wrong.
What's the current GA? 5.1. Can you do cross db transactions in 5.1? No. Sorry, but you should really learn more about the db you're a fanboi of if you're gonna tell me I'm talking out my ass. Sorry, but no one's making you look stupid but you.
Really? I've found there to be plenty of rabid fanboi types on both side of the fence. Pgsql users often still have a chip on their shoulder from the totally slanderous statements MySQL AB used to make about PostgreSQL, and they made a lot of them, and for a long time, it stuck, and I heard them repeated over and over from mysql fanbois.
I tend to ignore fanbois on both sides of the fence and consider the two positions based on reason, evidence, experience, testing, and support. I find that pgsql beats mysql on most all those accounts. But I've used both a fair bit before, so I have a basis for comparison.
Healthy suspicion is a good thing, it keeps us away from things like porcupines. OTOH, relying on just suspicion won't get you very far. Try it out, see how it really works. That applies to everything, not just databases.
I think it might be worth examining the differences between the two dbs. I've found some truly compelling things about pgsql. Here's a short list of the things I really like about pgsql:
Transactional DDL statements. Sounds kinda trivial, but it lets you do things like alter a table, update a field, and then, if things go horribly wrong, you can roll it back. For production updates, this can be a life saver. In pgsql everything except create / drop database and create drop tablespace are transactable.
The CTRL-C works like it should in psql, it cancels the current command. The fact that it exits the session in mysql's command line tool is idiotic, and should have been fixed years ago.
Fast and reliable bug fixes. I'd say the average time a nasty bug (crash, wrong results, data corruption) is fixed is within one to two days after it's found. There aren't a lot, but when the pg crew finds out about one, they stomp it fast.
I would agree that none of the new features would make switching from MySQL to PostgreSQL compelling. OTOH, the features PostgreSQL has had for quite some time ARE compelling to switch. Like storing the db meta data in transaction safe tables (pgsql) versus storing them in non-crash / non-transaction safe tables (MySQL). Or making sure that what you put in either goes in or throws an error without mangling the data (i.e. MySQL inserts 'stan' into a timestamp field as 0000-00-00 00:00:00 a non-existent and nonsense value, or inserting 2^31-1 for a value overflowing a 4 byte int, etc) Or the fact that MySQL still has issues with multibyte encodings, or that you can't have full text indexing on transactional tables like innodb. Or the fact that MySQL has serious and known bugs that haven't been addressed for years. Or the fact that MySQL has added known bugs to production releases because their QA is crap, for example, the order by DESC bug with regards to innodb tables, which showed up, got fixed, then showed up again all in the 5.0 GA release.
In fact, I'm really hoping that the community just takes MySQL away from Oracle / Sun / MySQL AB and fixes it and maintains it as a pure GPL only product. It deserves better treatment than it's gotten in the past by MySQL AB and their shoddy release practices. So far, the signs are good that that may in fact happen.
There are some first runs at such a thing, but they're all alpha level code or have serious restrictions. Look for Postgres-R and Bucardo to see if they can do what you need. Neither are RAC, but then RAC isn't necessarily the best answer all the time either for multi-master, as sometimes you need real shared_nothing clustering.
You should look up both pg_loader (which acts a lot like oracles bulk loader) or pg_bulkloader which does the dirty under the covers trick to just stick data into tables while the db is down and when it comes back up viola, there it is. Both are quite impressive, but have different use cases.