PostgreSQL vs. SAP? 97
Johann asks: "As my friend and I embark on building a large web site using open source development tools, I planned on using PostgreSQL. I was reminded that another 'enterprize' database is now released under the GPL - SAP DB.
Since there have been countless Pg vs. MySQL comparisons on Slashdot, I wanted to ask: how does SAP DB compare technically to Pg?"
A great move forward! (Score:1)
This company has realized that the benefits of releasing the source out to everyone outweigh the benefits of keeping it inside.
Congratulations, SAP! I wish the best for you!
Re:A great move forward! (Score:2)
SapDB in a real world Environment (Score:1)
The Slashdot Example (Score:5, Interesting)
Also Slashdot now numbers each and every post uniquely. Contrary to popular myth, this was not done to reduce FPs. When they needed two fields (the story number and the post-within-story number) for their primary key, they took a nasty performance hit.
So if we use Slashdot as an example (and I have to admit it's the only big MySQL application I know much about), then we have to conclude that MySQL's much-touted performance lead goes away if your database has more than the most trivial structure, or if your database gets very large.
Re: The Slashdot Example (Score:1)
I was the lead developer on a e-commerce site. The guy that started the company in his basement picked MySQL because the benchmarks beat the shit out all the other DBs. Now we're stuck with it, due to legacy.
MySQL works great, as long as you're prepared to make a bunch of bastard hacks to get around the limitations. We started over from scratch, and designed around MySQL. The Objects we have work great, as long as we use MySQL. If we ever move to something that is ACID compliant, we'll probably have to redesign from scratch to get it working. It would definately be easier.
But our site is freaking fast! We're stuck with MySQL because the whole App had to be designed around MySQL to make it run reasonably.
I don't get it (Score:1)
That was more likely because at the time, MySQL was the only proper open source database around and that had good interfaces in web languages. Postgresql was still unstable, slow and full of dumb limitation (32 KB row sizes anyone ?). Interbase was closed source.
Nowaday we got plenty of excellent open source databases to choose from (including the modern Postgresql) and you can make different choices than MySQL, but this is a fairly new situation.
MySQL works great, as long as you're prepared to make a bunch of bastard hacks to get around the limitations.
Most of MySQL limitations are not limitation on what you can do but on how easy you can do it. Stored proc, nested selects and views are all nice to use but you can write clean code without using them (it's just more lines of codes). There's no "hack" to do here.
Re:I don't get it (Score:1)
That would explain the 600 Meg Perl Hash I have to load up, because MySQL can't do sub-selects.
Clean code is what we ended up with. But it would be much eaiser to maintain 1 SQL statement that UPDATEs a table bases of the results of a SELECT statement (or for that matter, lets me update more than 1 table in a single UPDATE statement). Instead we have to mainted approx 20 lines of Perl code and 2 SQL statements per. Plus all that data is now clogging my network, instead of residing in RAM on the server.
Re:I don't get it (Score:1)
Re:I don't get it (Score:1)
That's an arguement that I'll listen too. The only arguement proposed was "It's faster."
Lies, damn lies, and benchmarks (Score:2)
When you read benchmarks, you should never stop with the numbers. You should look at exactly what the benchmark does, and ask how it relates to what you want to do. Is the benchmark task at all similar to your task? Was the system tested at the same scale you need it to work at?
Re:The Slashdot Example (Score:1)
That other site [kur5hin.org] does run MySQL as well (with InnoDB I think)
Marketing 101 (Score:5, Insightful)
Introduction of a third option. Makes the audience feel powerful and privileged.
MySQL is considered (even by its detractors...and there are a lot of them!) to be much faster than the competition.
In this world of benchmarks, is there any need to talk about one database being "considered" faster than any others? Sneaky.
While other RDBMS makers go on about "tradeoffs,"
Well, the "tradeoffs" that they were going on about were little unimportant things like transactions which, up until recently, MySQL didn't support.
the MySQL team has put their money where their mouths are and delivered a database that makes speed the top priority.
Use of language to suggest the MySQL boys are a bunch of good stand-up guys all around.
This is vital in the enterprise.
According to...? Sneaky.
Furthermore, the latest MySQL releases have full support for transactions and complete ACID compliance.
Once again, only a very recent phenomenon.
MySQL also supports a greater and more useful subset of the SQL99 standard than either Postrgres or SAP.
"More useful"? According to...? Sneaky.
I am by no means a MySQL zealot (though there are plenty who are, and you won't have to look far to find them)
Ah yes. This person is an everyman. A reasonable person. You can tell because he talks about the marginalized whackos and does not identify with them.
Just ignore the fact that he's promoting the same software the marginalized whackos would.
but I do think you should take all the options into account.
And Slashdot uses MySQL. Could you even ask for a more shining recommendation?
Appeal to Authority. Sneaky...
troll alert (Score:1)
hahahahahahahahahahahahahahaha...
hmmm.... (Score:3, Informative)
You can't do everything in one line of SQL. Not in a timely fashion anyways. Aside from the speed of the database, you should also consider the speed of development.
On stored proc (Score:2, Informative)
a) They are not portable at all. Moving to another database means rewriting all of them.
b) They tend mix business logic and data which is a big no-no as far as three-tier architecture goes
For these two reason I think everyone should restrict stored procedure to trivial repetitive things and keep the complex logic in a separate layer (outside the database that is)
Re:On stored proc (Score:2)
I thought the idea was to do your BL in Stored Proceedures so that you don't need to implement it in client, and thusly allow any interface; web page, windows app, unix app, and so on, to access.
If you're going three-tier, then you do it in objects (et all) that sit between database and clients, anyway.
Re:On stored proc (Score:1)
That's exactly my point. And that means that you don't write it with stored procedure (especially since PL/SQL is an horrible language to use for this task)
Re:On stored proc (Score:1)
In fact, putting those in any layer other than the database layer is probably the worst mistake made in database driven apps today.
Re:On stored proc (Score:1)
That's convenient but a) it's probably not very efficient because the script interpreter has to be loaded up everytime and b) if the interpreter crashes, your datas are left in a very inconsistent state.
And while business rules may belong to the middle tier, data integrity (not null, primary keys, unique, references, constraint check in, etc... all belong to the database.
Agreed. And that's all supported in mysql btw
Re:On stored proc (Score:2)
For small apps in a two-tier setup, this is a plausible way to go, especially if you are writing the rest of your code in a procedural language. But it ties you to a single vendor and a single database server. Migrating or scaling is a sharp-taloned bitch.
Personally, after a decade of OO design and development, I hate most uses of stored procedures. I also am deeply suspicious of complicated queries. It's my general belief that a database is not a processsing engine; it's where good objects go to rest. Keeping your business logic on a separate server increases flexibility, eases development, and reduces DBA pain. The initial development cost can be a little higher, but I think the long-term costs are a lot lower.
Re:On stored proc (Score:1)
b) i guess this is a personal preference. i like calling a stored procedure (Search), passing in a single string (a+list+of+words), and having it return a single recordset. but i guess that's just me.
also, they are (usually) compiled (as opposed to interpreted) and can potentially take a considerable processing load - especially if there's complex logic - off of the web server.
Re:On stored proc (Score:1)
As in they are not portable across databases. Try loading a stored procedure written for Sybase into Oracle. It doesn't work. The syntax is completely different.
I agree that stored procs are great and can have performance advantages, but portable is something that they are not.
Re:On stored proc (Score:1)
Well as pointed out by another poster, every vendor use a different language for stored proc.
also, they are (usually) compiled (as opposed to interpreted) and can potentially take a considerable processing load - especially if there's complex logic - off of the web server.
That's true but only assuming you want to move load from the web server to the database. It's way preferable to have the Web server do the hard work than the database. A cluster of Apache server and a load balancer is cheap compared to an Oracle or DB2 cluster (or some big iron with 32 CPU)
Re:On stored proc (Score:1)
It's just the way of db access with least overhead.
Re:On stored proc (Score:1)
Re:have you considered MySQL? (Score:4, Insightful)
Yes, you can now have transactions and foreign keys, but to get them you'll be using InnoDB tables, which don't offer up any significant speed advantage. They run at slightly slower than postgres speeds, in my testing.
MySQL sounds really good, especially if you haven't worked with real databases before, so you don't know what they're supposed to be able to do, but in the end it's not. Unfortunately, telling mysql advocates this is like convincing a Best Buy employee that overall PC performance cannot be compared by looking at the CPU clock rate.
Re:have you considered MySQL? (Score:1)
You got part of it right. Row level locking (and not page level locking like most other databases) are in MySQL as well as foreign key constraint (and their associated "on cascade" triggers). And as far as moderately complex databases go I don't know what you mean by that but I've seen systems with several dozens of tables combined with gigabyte sized tables work flowlessly and quickly. It sure isn't a DB2 or Oracle cluster, but it's already overkill for 99% of peoples who need a database. As for the open-source competition, Postgresql & interbase also have their own quirks and limitations.
Re:have you considered MySQL? (Score:2)
That's assuming you use the innodb tables which are not the default normally. Also the foreign key stuff only allows you to set things to null or delete the row. You can't have mysql stop the sql statement with an error or change the value to something besides null.
Re:have you considered MySQL? (Score:1)
Judging by how things goes, Innodb seems to become the default recommanded table handlers by MySQL and will probably replace myisam in the long run.
Re:have you considered MySQL? (Score:1)
Re:have you considered MySQL? (Score:1)
Re:have you considered MySQL? (Score:2)
Row level locking may be in mysql now, i gladly admit that i don't follow the development of it, but that's standard in pretty much every database made since 1996 or so, it's not special. if anybody tells you that most databases don't do it, they're wrong.
I never said foreign key constraints aren't possible, I said to use them you have to use InnoDB tables, which aren't fast. I said check constraints aren't implemented, because they aren't. In MySQL CHECK is a no-op which always returns true, whether the condition is true or false.
"on cascade" triggers aren't what i meant by triggers. A real trigger lets me do something like create a rule saying 'on an update to table foo, write some other shit in table bar', or 'if the update to table foo attempts to change the userid, kill the transaction, and write something to a log'.
And dozens of tables doesn't imply a complex database structure, it merely implies that there are dozens of tables. a few gigs of data also does not imply a complex database structure, or something that should be particularly challenging for the database to handle quickly either.
MySQL has more limitations than that too, it can't even have the default value for a field be variable. You can't do something like: expires TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 days' because that's not a constant.
as far as quirks and limitations of interbase, i can't say anything because i've never used it heavily.
As for postgres, yes, there are some things it can't do, but far, far, far fewer than mysql. PL/pgSQL is no match for PL/SQL but it beats the shit out of mysql's procedural language (you know, the non-existant one). It has some annoying limitations, like the fact that you can't use the return value of a function as a CHECK constraint, but to find the limitations of Postgres (and believe me, I'm good at finding the limitations of Postgres) you need to be attempting to do fairly advanced and complicated things. To find the limitations of MySQL, you just need to take somebody who learned SQL on a real database and ask them to solve a simple problem. Odds are good they'll do something that MySQL does not support.
function's return value in a check constraint (Score:1)
Re:have you considered MySQL? (Score:3, Insightful)
But it is not faster than Postgre or others in all circumstances, and is incredibly slower in many that bear importance to enterprise useage. MySQL provides responses with extremely low latency when there are few concurrent connections. This however, does not scale. It also tends to suffer under certain mixes of statements, or when using particular features.
If you need what I would tend call a "more safe flat file" MySQL is perfect. MySQL also is perfect for some other situations due to it's popularity.
If you need a relational database, potentially large, and that sustains a high number of concurrent connections, PostgreSQL is probibly your best option. While I've not tested the latest flavors of MySQL and Postgre, at the time I did do a comparision, even in 90% simple read queries MySQL's throughput fell below Postgre's after around 10 concurrent connections were reached.
Look, I'm not a Postgre zealot either, but I do feel that many MySQL supports are not familiar with the rest of the database world, that MySQL is all they know, and they try and pull it out as a silver bullet answer to ever problem.
And as others have pointed out, Slashdot is a poor example, they've written a great deal if middleware to deal with MySQL. If writing middleware is something you wanna do, go for it.
The biggest criticism I could level at PostgreSQL is that replication isn't fully implimented yet. This really holds it back from scaling out, again, something important for the enterprise today.
interface support (Score:5, Informative)
like you, i've been pondering the switch to SAP-DB - from looking through the source, their jdbc implementation seems to be very complete. the only problem i've run into with SAP is the lack of readable documentation... the manual seems to have lots of information, but it isn't exactly developer-friendly.
if this is an "enterprise" level application, i see little choice but to dive into SAP and figure it all out - otherwise i think you'll run into bigger problems down the road.
Re:interface support (Score:1, Interesting)
Re:interface support (Score:1)
Re: interface support (Score:2, Interesting)
I guess I had similar problems:
First of all the binary downloads of PostgreSQL don't seem to include the enterprise jdbc drivers (implementations of the interfaces in javax.sql.*) which I wanted to use to implement connection pooling in an application using jdk1.4.
Downloading the latest PostgreSQL from cvs (a few months ago) did appear to include the source for this, however they wouldn't build with jdk1.4 due to many new methods having been added to the interfaces since the implementation was last updated (and even many of those that were there will just throw an org.postgresql.Driver.notImplemented exception).
By modifying the source to add implementations of the missing methods to also throw notImplemented exceptions I have successfully built some drivers which appear to work correctly with my connection pool manager (and having since then got a proper internet connection have gathered from the mailing lists and news groups that other people have done the same thing).
The problem with this is that you no longer have a program that just works out of the box. (and building from source also entails either creating or suitably modifying some init.d scripts)
OTOH it was an educational experience, and made me seriously consider learning more about PostgreSQL and maybe trying to join the development effort (though I haven't actually done so yet, too many other things have come up)
I have also used the C++ interface, which seems to work O.K, but it is much less powerfull than the jdbc implementation could be if it were to implement some of the cool features such as writable result sets and prepared statements that the java interfaces declare (but which PostgreSQL doesn't yet implement)
Re: interface support (Score:3, Informative)
Updateable result sets for JDBC have been implemented in CVS, and will be in 7.3. I implemented prepared statements and that should be in CVS soon, but I'm not sure what the status of JDBC support for that is. Hmmm... this will definately need to be fixed before the 7.3 release (if the 1.4 methods aren't implemented, they should at least be declared and throw exceptions so that JDBC will compile out of the box with JDK 1.4).
Re:interface support (Score:2)
you can't return multiple rowsets from a stored procedure.
Re:interface support (Score:2, Interesting)
Re:interface support (Score:2, Interesting)
Re:interface support (Score:1)
Re:interface support (Score:1)
it's also true that the jdbc driver that ships with postgresql is poorly executed.
this driver [sourceforge.net] offers more potential to be the jdbc driver of choice for postgresql. i'm already using it in a number of applications despite its "alpha" status (the lead developer is keeping it alpha until fully compliant with the jdbc spec, but this of course could not happen until the backend changes).
One thing to consider... (Score:3, Interesting)
In all seriousness, if you use SAP DB, you could probably raise your rates just due to the SAP name alone. The name 'SAP' is also good for covering your ass when things get difficult.
SAP and Oracle have expensive reputations and you can charge accordingly. We do it all the time, for some of our nastier customers, when anything with the word 'Cisco' on it need care.
It depends on the relationship you have with your custome, wether you go that route, but it's worth considering.
anyone else notice the history of SAP (Score:3, Interesting)
http://www.sapdb.org/history.htm
Re:anyone else notice the history of SAP (Score:3, Informative)
The black bars are weird. If you use the Wayback Machine you can see what they obsure: http://web.archive.org/web/20011130142504/http://w ww.sapdb.org/history.htm [archive.org]
The removed text is
I think a marketing drone just doesn't want to show any sign of weakness in the product's history.
Re:anyone else notice the history of SAP (Score:1)
Re:anyone else notice the history of SAP (Score:2)
Maybe the blacked-out parts are the names of an advanced alien race that the government stole the technology from using Jeff Goldblum and a laptop.
Other alternative (Score:1)
From using MySQL/PostgreSQL and researching SAP... (Score:5, Informative)
My info about SAPDB is research-based, as I did a lot of that. Please feel free to correct me, if I'm wrong, in any way. MySQL has steadily improved, so a couple of items, below may be out of date.
I see that SAPDB is really an Oracle killer. It is a true enterprise-ready DB. If you want all the features of a big DB, such as replication, partitioning, etc., use SAPDB. You can get true commercial support, etc. Read their PDF's and be impressed.
However, you might want to know why I chose PostgreSQL over MySQL and SAPDB:
1.) PostgreSQL is really ACID compliant, as is SAPDB. MySQL, on the other hand hasn't yet proved itself in this area. Give MySQL a few more months, though, and we'll see, with version 4.1.
2.) Hosting companies support MySQL really well, and PostgreSQL only partially well. I have yet to see SAPDB as a hosting offering. Oracle is rare, too, but expensive.
3.) All three are free, in both senses of the word, while DB/2, Sybase, Oracle, etc., are not. (No preference in this area.)
4.) There is an enormous userbase for MySQL, and a sizable one for PostgreSQL, so I can get help from peers 24 by 7. SAPDB, unfortunately, does not have much in this area.
5.) PostgreSQL 7.1.x+ is supposed to scale better in many ways compared to MySQL 3.xx+. Many benchmarks seem to bear out the fact that PGSQL annihilates MySQL 3.xx, after about 5-10 or so web users. PGSQL seems to beat Oracle up through and beyond 100 simultaneous web users. I cannot find any benchmark on SAP, anywhere.
6.) Installing PostgreSQL and MySQL are easy, easy, easy. It's not so easy with SAPDB. While I'm no neophyte, when you consider remote hosting issues, I hope to have a system I can quickly rebuild if hardware dies.
7.) PostgreSQL has enough enterprise-ready features for my web site. MySQL did not (and probably still does not). SAPDB is almost overkill. Views, triggers, foreign keys, constraints of various kinds, stored procedures, versioning, hot backups, etc., are all available in PostgreSQL and SAPDB. One responder indicated that programming time is more important than benchmarks -- amen, preach it, brother. Your time programming and tweaking are far more expensive than hardware and most software.
8.) After working with PostgreSQL and MySQL, I saw that care and feeding (DBA work, in particular) were very simplified, straight forward, and quick. SAPDB smacks of Oracle, in terms of tweaking, complexity, etc. (I was an Oracle DBA for two years; a RedBrick DBA; worked for Informix; and have administered MySQL, PostgreSQL, and even lowly Access.) I haven't actually tried SAPDB, because it looks like a major investment of time.
9.) Books, web sites, and other literature are readily available for MySQL and PostgreSQL. SAPDB's included documentation is most excellent, however, followed by MySQL's included docs. PostgreSQL suffers in this area -- buy the books.
10.) As I scale up, I probably will have to consider something other than PostgreSQL, like SAPDB, DB/2, or Oracle. I refuse to look at Sybase or especially it ancestor, SQL Server. On the other hand, PostgreSQL is semi-tunable and the development team plans to be adding replication, etc., in the coming months. I'll have to wait-and-see. If SAP ERP can be hosted on SAPDB, then, well, it'll scale, no question. I'd contact the author of BinaryCloud for more objective info, here. http://www.binarycloud.com/
11.) Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above. I'm sure the same is true about SAPDB, and more so, since it's got a real business and an ERP behind it.
12.) PostgreSQL, like MySQL, has a user-friendly SQL command line, with readline support, etc. I don't know about SAPDB, but I expect it to be less so, like Oracle's SQL*Plus. Can somebody help me out, here?
In conclusion, my first choice, for now, is PostgreSQL, my second would be SAPDB, my third is actually MySQL, followed by the commercial products. Again, I've never used SAPDB, but I hope to, in the near future; it seems "too big" for my needs, now, and information, outside of sapdb.org is scarce. I hope the community really gets around it, soon, so we can have a more objective look at the product. We need support groups and books available at our local book stores.
SAPDB looks absolutely excellent, while PostgreSQL looks good. MySQL has potential to be a business/enterprise-ready product, in a couple of years. I like the fact that SAPDB uses ODBC/UDBC for its native calls, like SQL Server, Access, and DB/2. MySQL, PostgreSQL, Oracle, and the like, require drivers to translate calls back and forth, slowing things down and adding complexity, for ODBC, UDBC, and JDBC.
For PostgreSQL, I gotta say, the thing that really impressed my was versioning, which makes transaction support and hot backups easy, while keeping performance very, very high.
Try out both databases. Use PGSQL today and SAP tomorrow, as you grow into it.
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Re:From using MySQL/PostgreSQL and researching SAP (Score:2, Informative)
Almost all of the PostgreSQL/SAPDB advantages are available to Firebird/Interbase.
My list:
PostgreSQL (for now)
SAPDB or Firebird (future growth)
MySQL 4.1+ (maybe, maybe not)
I have done less research about Firebird than SAPDB. All 4 give a great deal of choice. Firebird is based off of Interbase and has a userr community probably larger than SAPDB. SAPDB still kills Firebird/Interbase and PostgreSQL in terms of enterprise features.
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Re:From using MySQL/PostgreSQL and researching SAP (Score:1, Informative)
I think you meant it the other way around. MSFT bought a copy of Sybase and it became SQL Server.
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Odd (Score:2, Interesting)
Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above
I'm following pretty closely both releases and frankly I've the exact opposite feeling. While MySQL adds features one by one, Postgresql seems to absorb lots of stuff at each releases. While this is great feature-wise, it can lead to pretty nasty bugs and instabilities.
Just look a the latest release for example (from postgresql history) :
"it fixes a critical bug in v7.2: sequence counters will go backwards after a crash "
That bug is really critical (can you imagine the state of your datas when counters go backward ?), but it took 2 months to be discovered and fixed. On the other hand the MySQL history list only very minor bug fixes, most of them that you are highly unlikely to meet and that are of little effects. Actually the MySQL developpers make the effort of documenting every petty bug fix, which is a very professional thing.
Re:Odd (Score:1)
Re:Odd (Score:2, Insightful)
But let me ask the parent of the parent I'm responding to, how does MySQL recover from crashes? Does it have a write ahead log feature? does it use some other method to guarantee data integrity in case of database crash? I'm not being facetious, I don't use MySQL so I don't know.
Re:Odd (Score:1)
The time to repair is causing us a few issues. The 60GB of data we have takes approx 1.5 hours to repair after a crash (single process repair.) We're currently experimenting with how many processes the hardware can support for optimum repair time. 32 processes is looking good, but that's a Quad UltaSparc2 w/ Fiber Channel.
I've had to deal with quite a few crashes recently. It looks like our hardware is a lot bigger than MySQL can test on in house. The Sparc boxes (mentioned above) have had about 10 crashes in the past year. The Quad Xeon box has had 0 crashes in the 2.5 years that I've maintained it. YMMV.
Re:Odd (Score:1)
So does MySQL not flush disk writes to disk before a transaction commits? Your reply seems to suggest that MySQL waits for the kernel to flush disk buffers to disk -- if that's the case, it definately sounds like there is a potential for data loss. Have you considered using PostgreSQL?
Re:Odd (Score:1)
Transactions? You must be thinking of another database. MySQL only does transactions if you use the not-supported-out-of-the-box InnoDB table type. And that defeats the whole point of MySQL, because InnoDB is about the same speed as PostgreSQL.
The "Data" I've lost was usually still in the middle of being written. Some of the writes takes more than a few seconds to complete, so I guess the few seconds overlaps with "not done yet". That's what I get for posting drunk ;-)
MySQL does flush the data out of disk, but its less agressive flushing indexes out to disk. In general though, most of my repairs after a crash are just to be safe. MySQL has a "clean" bit in the tables, so we check tables that are labelled unclean. Most of the time the unclean tables are fine, and the bit is cleared. It still takes a while to verify though. Sorta like checking your ext3 filesystem after a crash. MySQL isn't journaled, but our filesystem is. That might help a bit.
Have you considered using PostgreSQL?
We're too far gone. See my other comments [slashdot.org]
Re:Odd (Score:1)
Re:Odd (Score:1)
One of the interesting things to try with postgresql is to run a couple dozen transactions and kill -9 the postmaster, or even more severe, hit the big red switch, and then reboot. With journaling file systesm and postgresql's write ahead logs, the whole system if back up in literally minutes. And the transactions that were pending are either all individually committed or none at all.
I've run it on smaller sparc boxen running linux (Sparc 20, ultra 1, 2 etc...) and it seems to be quite stable and fast. Solaris, not so fast.
Re:Odd (Score:1)
If you use Innodb it has a double-write system that takes care of graceful recovery in case of crashes. MyISAM tables don't have any specific protection but they seem to never ever get corrupted, safe for index which might need to be repaired in case of crash. Either way Mysql is pretty solid and has never lost datas on me in years, and seldom crashes (I have uptime for the database between 1 to 3 months, which is not too bad IMHO)
Re:From using MySQL/PostgreSQL and researching SAP (Score:2)
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Most of what you mentioned above compares well with my findings. I take issue with only one. You mention that PostgreSQL is clean compared to MySQL. I suppose that could mean many things. After studying the database syntax, I didn't feel PostgreSQL was nearly as "clean" as MySQL.
It has been a while since I've read the PostgreSQL manual, so I don't have the best examples. But I found many more inconsistencies and "why the heck did they do it that way" features in PostgreSQL. One token example is escaping data. Depending on the situation, you may need to escape a character with one, two, or even four backslashes. That makes putting binary data into a PostgreSQL database fairly difficult. With MySQL, you only have to backslash the apostrophe and the backslash -- very simple and clean. With most other databases, there is a prepared statement feature that directly transfers data as a separate blob. With PostgreSQL, good luck getting your binary data in.
But when all is said and done, other than syntax complaints, PostgreSQL is an amazing database system.
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
I agree about the conformance -- PostgreSQL is fairly good in that respect. Just as a matter of taste, I prefer MySQL's way of doing certain things over the ANSI standard. YMMV.
(Don't get me wrong. I'm trying to make the switch from MySQL to PostgreSQL because I want the power PostgreSQL has. I'm just finding it hard, because I keep running into these roadblocks where I think "MySQL does this so much simpler!")
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Here are my corrections to a couple big misunderstandings:
1. SAPDB uses a fixed cache buffer per database. For example, you can give 128MB of RAM to a database for DATACACHE. The problem is that there seems to be no way to SHARE the datacache between multiple database instances. So this makes it poorly suited for web hosting applications... there is a lot of RAM overhead for each instance of SAPDB. Yes, you could create one instance and use permissions to split your hosting customers, but hope that you customers never try to create the same tablename
2. SAPDB has no replication! The "replication" is nothing more than an import/export manager - and it still seems pretty immature. Bugs are being found in it all the time when people actually use it.
3. SAPDB error messages and documentation are not very easy for newcomers. This is for people who have worked with MS-DOS 3.0 and Linux 2.0
4. At least for Windows users, the ODBC drivers seem like they are built on a old codebase and updated. They don't have an OLEDB driver, no interest in tuned dotNET drivers, etc. Another example of things to watch for: UNICODE is supported in the database, but not by the ODBC drivers! They are supposed to be working on it, but it was a shock to me to find that UNICODE wasn't working.
I think people are wrong to compare Oracle to SAPDB. Just because it has some compatibility, it doesn't even seem to compare in maturity. I'm not saying that SAPDB is unreliable, but some basic problems have existed that you would assume would have shown up if more people were actually using it!
Example of problem: If you ran a single program that did a SELECT that returned no results, a memory leak took place that would hit you after 2000 or so such SELECTs.
I think it was previously used mostly for R/3 and just doesn't have a lot of usage outside of R/3 - so a lot of bugs are yet undiscovered. As people switch from other apps to SAPDB, all new SQL is being thrown at it, and the bugs are getting found and fixed.
Having a dedicated (SAP) development staff is great for this type of maturity... as the bugs are found, they get fixed. Yet it seems obvious to me that we are turning the titanic here (really old codebase).
I'm sticking with it, but I think sometimes people assume it is going to be really "high-end" in terms of features and maturity. Don't assume...
Re:From using MySQL/PostgreSQL and researching SAP (Score:1)
Different personalities (Score:3, Informative)
- Platform independence: SAP DB supports Windows better [skippingdot.net] than PostgreSQL does. They both have good support for unices of various flavors.
- Concurrency: Both databases support ACID transactions. PostgreSQL uses multi-version concurrency control so reads and writes won't interfere with each other, while SAP DB uses row-level locking. In this respect, PostgreSQL v. SAP DB is similar to Oracle v. DB2.
- Curriculum vitae: PostgreSQL originated at Berkeley, and has very cool features like MVCC and functional indexes [postgresql.org]. SAP DB descends [accpro.com.sg] from Adabas D, which was marketed as the PC-sized little brother of Adabas for mainframes, and SAP's focus is on providing robust support for business applications such as SAP's customer-relations and supply-chain products.
- Developers: PostgreSQL is developed by a global community of volunteers. SAP DB is developed by a team of employees (FAQ [sapdb.org] says 100) in a major software company. Development of both products is active.
You can do good work with either product.Performance : SAPDB rules (Score:1)
here my (absolutely unscientific) ranking:
- SAPDB
- Firebird
- PostgreSQL
remarks:- All three databases performed well
- (RANT: It is a nuissance that there is no standard for called procedures!)
- SAPDB was almost twice as fast on inserts as PostgreSQL (deletes where only a little faster)
- Firebird: somewhat slower than SAPDB. I didn't know then that there are also some tuning possibilities.
- MySQL: I didn't even test it because it isn't ACID (I know, there are recent additions, but
...)
we have been using sapdb now for half a year and it rocks!Of course it is heavier than the other databases; and it is a little hard to get started. (The beginners documentation could be better, although there are web sites providing valuable hints)
Would I recommend it for medium or big sized installations?
Yes, without hesitation.
Big differences I know of (Score:1)
Postgresql uses MVCC (multi-version concurrency control.) That allows for multiple transactions to run with each viewing the database as an instance in time when the transaction began. Other than Oracle and Postgresql, I don't think any other database uses MVCC.
Row level locking is a win for a data warehouse, with little writes and many reads, but in a heavily updated transactional environment hits a brick wall pretty quick.
The other big difference which not one person has mentioned yet is the license.
Postgresql is BSD style (do what you will) while SAP was released under the GPL.
For some companies this may be an important difference, like if you're building "black box" apps that you sell (think network appliance type stuff)
Re:Big differences I know of (Score:1)
This licensing doesn't restrict my rights to my application software at all. As for letting customers know the identity of the server software, that's no problem because SAP DB is easier to sell than PizzaFace DB.
Re:Big differences I know of (Score:1)
Re: (Score:1)
Re:Short and sweet evaluation (Score:1)
I guess you had a REALLY short eval of PostgreSQL, or were using an old copy, since both observations concerning PostgreSQL are false, again, unless you're using something older than 7.1.2 or haven't tuned it according to Bruce Momjian's guide.
As of 7.1.x, backups are hot: http://www.postgresql.org/idocs/index.php?backup.
Hot backups have been made possible, partly due to PG's versioning system.
As for load, once again, read some of the tuning guides, such as http://www.postgresql.org/idocs/index.php?perform
Many different kinds of benchmarks (believe them if you want to) actually show that PG outperforms MySQL (which really bogs down under load) and Oracle 8i (up to 100 simultaneous web connections). However, tuning Oracle and PG and changing platforms, etc., is going to affect performance drastically.
(Why is Slashdot chopping up my URL's? Good thing I previewed, first....)
Reliability (Score:1)