Slashdot Mirror
What Database is the best for a Web Site/Small Business?
pepper20
asks: "Okay, now that the big boys
in the database game have ported their
software to Linux, what is a smart choice
for a web site backend or small business?
With all the choices out there (Sybase,
Informix, Oracle, mSQL, mySql, etc... ),
what would you all recommend, and why?"
We're using Solid. It's a fine dbms with all the
basic features (transactions, backups) , easy to install, easy to maintain, not too expensive, all platforms you need (you can migrate by just copying the data files) and it's not that expensive (Ora, IBM want big $$ for every user)
Support is fast and friendly.
This is the unification of Cambridge University Engineering Dept's databases. At present these are held on flat text files, Windows based databases, and an X.500 system. We'd like them in a Unix database, and use ODBC to allow windows clients to connect. We're talking about a department in the the region of 2500 people.
I've tested a number of databases:
- PostgreSQL 6.4
- MySQL
- mSQL 2
- Oracle 8.0.5
- Ingres 2
- Informix
My final results are far too verbose to show here, but here's a quick summary:- Commercial databases: For our purposes, these are totally unnecessary. Sure, all the management tools that come with Oracle are nice, but if you can write Perl, you can achieve the same, albeit with some more work.
- mSQL: MySQL is derived from this, and it offers no advantages over MySQL.
- MySQL: There's one reason and one reason only to choose this: Performance, especially reading performance. However, the cost of this is very high - none of the features that make databases usable with complex data structures, etc. In particular, the lack of transactions verges on criminal negligence.
- PostgreSQL: This is the one we're going for. It supports everything you'd expect from a modern database. In particular, inherited tables allow you to do many things that the commercial databases have, such as per column access control. Performance is adequate, but not fantastic. In our application, this isn't terribly important - but I wouldn't want to run Slashdot on PostgreSQL.
In the end it all boils down to horses for courses. I suggest you install PostgreSQL and MySQL, and write some C/Perl/Whatever scripts to benchmark their performances with whatever data structures you're going to use. If PostgreSQL gives adequate performance, use it, as you will sure as fate find it's features useful in future. If not, MySQL.Of course, you should take what I've just written with a pinch of salt if you have unusual data requirements, which may dictate one of the commercial databases.
If you'd like specific details of what each database can do, I've done quite a lot of comparisons. Feel free to email me with questions.
Alistair Cunningham, ac212@cam.ac.uk
There are two purposes for transactions.
... some delay ...
... some delay ...
The first is to allow you you to abort on error, as above.
The other is even more important - to ensure mutual exclusion during concurrent access. If you have more than one client connected, you MUST MUST MUST not allow scheduling problems between clients to corrupt your data.
Consider two or more clients running the following rather contrived SQL on a single row table:
SELECT field AS temp FROM table;
UPDATE table SET field = temp + 1;
This is of course a crazy way of incrementing a field, but real, more complex, systems actually do this sort of thing. If client 1 does the SELECT, then client 2 does the SELECT, then client 1 does the UPDATE, then finally client 2 does the UPDATE, what do we end up with? Answer: The wrong value - the UPDATE by client 1 has been discarded. If, however, the following were used:
BEGIN;
SELECT field AS temp FROM table;
UPDATE table SET field = temp + 1;
COMMIT;
then, while client 1 was working, it would lock the data that used. Client 2 would be blocked - prevented from accessing the tables until client 1 had finished, and had called COMMIT. Then client 2 could go. Thus we have mutual exclusion at the price of client 2 having to wait.
Alistair Cunningham, ac212@cam.ac.uk
If you don't need SQL then I would recommend FIleMaker from FileMaker Inc. (formerly Claris). It's an Apple offshoot but is the number two desktop database after Access on PC's. With the server ($995) you can support up to 100 clients and 100 tables of data, with limitless layouts (reports) and scripts (macros). Plus it's heavily AppleScriptable if you have Mac's on your network.
I'm currently developing a system for a network of about 25 clients doing all of their advertising tracking and creative storage. It has about 200 layouts (reports), 25 tables, and over 100 scripts. Total fields is about 300 and total records across the system is 10,000-20,000. The speed has been great and the interface is wonderful. My only complaint is lack of SQL or SQL like queries. The find feature is a bit lacking for complex queries.
Just my $.02
I vote for Solid Server.
Although Solid Server doesn't have much of a reputation in the US, its a hot item in Europe. There is a reason for all this excitement. It performs beautifully AND you don't need to know a thing about database management. This combination is CRUCIAL to a small business.
There is a free version on their web site www.solidtech.com for linux, it is limited to three or five user connections. This should be enough to get you started and do some modest performance testing.
One of the reasons Solid is so wonderful is that it "does away" with locking. Solid creates a time dependant view of the data for each user read and update. As long as no other transaction effects this time dependant view, the transaction completes sucessfully. They might call this "Optimisitic locking" in their documentation/marketing.
Technical support is very good, but remember that Solid's headquarters is five to eight hours ahead of the US. While this hasn't been a problem for me, it is a consideration.
Finally, I did get an oppurtunity to meet the president of the company at a conference a little over a year ago. He was a man with vision. I think this is a company to watch.
In summary, I have worked with Oracle, Sybase, Microsoft's SQL server, DB2 and Solid. All of these in the context of Mission-Critical, Enterprise scale applications. For a small business, I recommend Solid, hands down.
By the way, the Florida Department of Motor Vehicles is the second large user of Solid server in North America. Every driver's license system in the state has a solid server running. If you want a real live demo, go get a driver's license in Flordia.
Informix especially is alive and growing.
$99 for Linux, That's $99 more than Postgress
or MySql, but then you get more.
Don't know how much, if anything, DB2 for Linux is.
>> mSQL: MySQL is derived from this, and it offers no advantages over MySQL.
I am pretty sure they are independant products, and they don't share any code.
Go to php.net (and find a mirror). There are links to a couple of example code repositories, and they have a bunch of examples of mySQL usage.
I want to put a database of about 100 company names and phone numbers on a page, with a search function. I have no experience in databases whatsoever, and performance isnt that big an issue but cost is. From what Ive been reading, it seems like mySql and perl are the way to go. The database was made in Intra Builder, so maybe I should just use the linux version of that server?
I often find people who claim that PostgreSQL is a bit too slow for their needs. That is right for the default setup of PostgreSQL, but if you read doc/README.fsync you find out what causes this...
MySQL has it's silly table locking. It seems to work for some people...
Beside that you write its name wrong (it's "PostgreSQL" or simply "Postgres"), your claim ``but it's slow'' is wrong.
Compared to MySQL 3.21.33 the speed is about the same. You just have to read the docs to get a decent speed from it.
Your claim that you've ``managed to get dynamic pages back to the user as fast as static pages'' sounds plain silly. A static page will be always cached better and will always need fewer resources (especially I/O) than a dynamic one.
You seem to be on the Sun's and TCX' paylist. Ever heard of PHP3? It's faster, more stable and more portable than your servlets will ever be.
I spent a couple months screwing around with
/triggers/ etc I could want but was ridiculously complex to figure out, but I did, and wrote my database with a mix of constraints and triggers and sequences. It's really not all that difficult after a week or so messing with it. THe lack of auto-increment numbers is a fucking bitch. You must use sequences and triggers if you want this. If anyone needs help with this crap, I'll be glad to send you my .sql files, just make a reply to this post.
MySql, Postgres, and finally Oracle...
I found mysql horribly lacking in referential integrity.-- looking back, this may not be an issue for my database since records will be inserted in a very very specific order, but it bothered me at the time. To some extent it still bothers me because I would not want to have some bizarre freakish occurence corrupt the data out of a lack of referential checking.
Postgres HAD the same problem, I'm not sure it does any more. Plus, it is slow. But i'm starting to like it more. I may port my application to it, since speed is actually not a concern for me.
I ended up using oracle after getting the cd from the oracle booth at the Atlanta LS
Before i mention oracle:
For querying, i think mysql would be the absolute best. There is no concern whatsoever about integrity in querying, so perhaps the best would be a mixture of a strict server for entering data, and then dump the data to the lightweight mysql for queries...This assumes no data is being collected via the web, which is a huge assumption of course!
As for Oracle:
Oracle had all the referential
I have used MySQL, MSSQL, Access, Paradox :) and a few other database on the web.
Since the original question was what was best for small businesses my answer has to be MYSQL! For general selects, table updates, inserts and publishing large lists of information to the web it is awesome, there is no comparison. It is great for a person who wants to get things done easily, quickly and cost effectively.
Small business typically just want a centralized esily managable way to publish large LISTS of information to the Internet with the ability to select subsets of that list. A band might want tour dates published, a company may want product names and prices. They may just want to store information for a mailing list.
None of that requires Oracle, MSSQL, or anything other than MySQL. Most importantly none of that requires the expenditure of large amounts of time and money.
So if you are a small business, and when you start looking at your requirements you realize you need a spreadsheet on steroids. GO WITH MYSQL.
Have a good one.
Stephen Johnston
http://www.gainsay.com/
Indeed let's put the bullshit to rest. This nonsense of yours that table locks are an appropriate way to go is a worse than useless suggestion.
Firstly, it ensures that other people are denied access to the table while you're performing your DML (unless you've implemented a Share/Exclusive/Dirty scheme) and the problem only escalates as you lock more tables. Multi-statement updates, by their nature, involve more than one table, and so what you're suggesting is a wholesale lockout of large parts of the database.
I have used Interbase on Windows, and it is pretty good - small, fast(ish) and simple.
Interbase 4.0 is avaiable free and IB 5.0 is availiable for the same price as on NT.
Check out http://www.borland.com for more details.
Overall:
None offer full SQL implementations. None offer clean programming constructs within SQL. None offer the ability to drive system+IO functionality from within a non3GL-API environment (their forms tools partially excepted).
#BFD. *NO* one offers a complete SQL-92 (or
#-89...) implementation... SQL isn't *SUPPOSED* #to support "clean programming constructs". SQL #is supposed to be purely set-based. Sure, some #things are done easier or faster (simple #example: running sums) in a procedural fashion #(which is what report writers, Perl, etc. are #for), but most queries are not. The SQL pedants #would smite you (I used to work for one...).
Specifically:
* Informix is a barely visible wrapper round I/VSAM.
* Sybase is functionally equivalent to Oracle but has BADLY buggy SQL. If you are restricted to Sybase and want usable SQL, I advise driving it via SAS without passthru.
#No, Sybase is functionally equivalent to #Microsoft SQL Server... But I like Transact-SQL, #at least for writing queries. Its query #optimizer kind of wanks.
#I find too many differences in Oracle vs. Sybase.
#Oracle... NULL == "". Strange design choice.
* Oracle does not have the jawdropping bugs of Sybase but has equivalent design/structure: it is NOT relational despite its advertising. It offers some relational sounding words but does not support correct consistency, integrity, etc.
?
For example, to support transactions (OFF by default)(no, the ability to Commit data is not the same as Transactions)
Hmm... by the SQL standard, a transaction is such:
#BEGIN TRANSACTION
#...
#either COMMIT TRANSACTION or ROLLBACK #TRANSACTION.
#How exactly it's implemented shouldn't matter #too much?
, the DBMSs locking granularity is one table... Unbelievable.
#Well, in Sybase it's page-level. Better, to a #point. How to fake out? Waste a lot of space #having a big varchar or blob field to ensure #that each table record is one page in size.
#but what would really be nice would be true row-#level locking.
> ie you can not practically establish a maximum >level of data corruption risk in a multiuser >environment.
#Hmm...
However, since most selfprofessed RDBMS coders are simply manipulating data via APIs, they are really just buying ISAM plus a DDL and a couple of DML macros. In which case get whichever DBMS runs quicker, use your 3GL of choice, and just accept the high code/maintenance costs.
Other:
* DB2 was relational and used to get the thumbsup all round but have no idea where it is nowadays.
* A friend has said good things about PostGreSQL, principally comparing it to MSAccess, but it seems to offer full SQL.
* Re the speed comments on other posts here: realistically, minor processing time differences will seem slight for a nonmajor site, compared to the time spent downloading graphics etc.: I'd strongly suggest you go with ease of use rather than try to squeeze out an extra 0.1 second. Optimising TPS is usually a splendid way to waste spectacular amounts of time and money.
Posted by Scottqn:
New to Slashdot today. New to databases in general. I am working with ASP and Access for a very small project. I understand Access to be too limited for most business needs, but what about Visual Fox Pro? I saw no mention of that one in any of the posts. I'm just curious, since I really don't understand what makes these DBMSs so different from one another.
Posted by Scottqn:
O.k., pardon that totally lame question there - I didn't spend enough time here to figure out just how anti-MS most everyone here is. Once I get my hands on a copy of Linux, I may be another convert after all I've read this evening.
Let's put some bullshit to rest here, shall we?
1) MySQL can simulate the important part of transactions, namely, the atomicity of an update. Requesting a table lock for the duration of a multi-statement update, and releasing it at the end, is what you have to do. Who the hell uses rollback, anyways?
2) PostGreSQL is a pleasant database to use, but it's slow, and I can't for the life of me wrap my mind around the code. There are lots of spatial and object-oriented constructs in there which you don't need and which, in conjuction with totally- portable transactions, make it relatively slow.
3) Oracle is a bear to install, tune, and make behave. However, with enough memory on the server and enough competence in the DBA, it will scale from here to eternity. I have to administer an Oracle database as the backend of my company's corporate-infrastructure web application and it's not a pleasant job. Maybe if I could get mod_jserv to work on my server, I'd be singing a different tune -- the JDBC driver for Oracle is pretty nice. Unfortunately, it uses the TCP/IP listener, which does not scale as well as using the OCI interface (via Perl/DBI/Apache::DBI) does.
With the tweaking I do to my scripts and server, I've managed to get dynamic pages back to the user as fast as static pages (with MySQL; slightly slower for Oracle). For the amount of grief it has caused me, and the crappy tool SQL*plus is (I have lots of DBI scripts to dump tables in a readable manner), I disdain Oracle.
Sybase was much more pleasant to work with, however the driver support for Sybase on Linux is pathetic. Every molecular biology concern I know of seems to use Sybase on Solaris, where their JDBC driver is fully supported, and the replication features of Sybase allow the DBAs to sleep quite soundly. On Linux, though, it sucks.
Do yourself a favor and buy a MySQL license, or help Monty hack subselects and atomic operations (pseudo-transactions, really) into the MySQL code. The world will be a better place when there isn't a reason to use Oracle anymore.
And if you use JDBC + servlets, use connection pooling and caching -- mail me if you care. Servlets and Java Server Pages can obsolete ASP altogether if we work with Sun and they work with us... otherwise, delenda est Sun Microsystems!
Remember that what's inside of you doesn't matter because nobody can see it.
I just remembered that you can, in fact, use the OCI interface with JDBC. Instead of loading the "Thin" driver, you load the OCI driver. My boss was against this ("no no no, we want to use the Thin driver") but I'm not -- a web server driven mostly by a database may as well reside on the same server (unless you're smoking crack and use applets or CGI scripts for everything, as opposed to mod_jserv + DBConnectionBroker and/or mod_perl + ApacheDBI). So sue me or whatever.
Remember that what's inside of you doesn't matter because nobody can see it.
MySQL does have an ODBC driver for windows machines, but it's a PITA to setup. I can rarely get it setup properly. (It's nice to query a MySQL db that has website hits logged to it from Excel, attach it to an e-mail and send it to a client...)
Sybase, on the other hand, has ODBC drivers from lots of different people, and if those don't work you can probably use a MS SQL Server driver.
I'm in the process of figuring out if/how to move certain applications from MySQL over to Sybase primarily because of:
-Transactions
-Stored procedures
-Triggers
-potential for replication
I'm going to keep the website hits logging to MySQL, though, because it's so darned fast...
Chris
M-x auto-bs-mode
But I won't fault MySQL, either. Since both are gratis for testing, I would suggest testing both.
--
Ben Kosse
Remember Ed Curry!
Interbase gets very little attention. But in fact it can be a good choice for Linux. Version 4.2 is free. Version 5 has a very good JDBC driver (we have found it rock solid).
For us there were the following reasons for choosing interbase
1. Good JDBC support
2. Support for unicode in all char/varchar/blob fields.
3. Good pricing for VARS
4. Support for NT/Linux and Netware (v4 only at present newer versions on the way).
5. The speed is good when you have a mixture of OLTP and OLAP due to the versioning engine (does not require locks but uses multiple versions of rows). This means writers do not block readers and also you get a reliable read eg if you start a long stock report by warehouse and someone does a transaction in the middle to move gold bars from warehouse A to Z then in Interbase you only count them once, in many dbms you count them twice.
BUT the bad things are
1 Marketing is terrible
2. There are very few functions (you can extend them using C but then you need to support on all platforms and not possible at all on netware).
3. I think there are special license prices for internet applications.
Regards
Dave
PS the free dbms tend to have poor support for large numeric column types, no domains, poor triggers, no unicode (or no unicode on indexed fields).
PPS we were using Postgresql on Linux but have found Interbase a lot better (for our needs which possibly are not very typical).
"It takes forever to get a connection to Oracle."
only if you're incompetent. OWAS is extremely fast.
Interactive SQL query tools don't do anything until the command is sent (with a ; on Oracle, a "go" on many systems). So no locking occurs until the command is sent. You can't type "BEGIN TRANSACTION", send that command and continue - a SQL engine wouldn't allow that. It has to be atomic.
--
Matt. Want XML + Apache + Stylesheets? Get AxKit.
The original Q was "what's best for a website or small business?". I don't think these share the same needs.
When you use a DB to back a website, speed is critical. The time to establish a connection to the DB becomes very important too (or the ability to maintain a persistent one and recycle it). On these grounds, MySQL/mod_perl all the way.
It takes forever to get a connection to Oracle.
(incidentally, has anyone with an advance copy of Oracle/Linux got Oraperl or DBI::DBD working?)
However, the lack of subselects and union in MySQL is a pain in the arse. Yes, you can code around it, but this is a nuisance, and makes for messy code, since some of your query is in the SQL, and some in whatever procedural language you're wrapping it in.
In a business setting you're more likely to miss these features.
Another thing to consider is whether you'll be writing back a lot (or at all). Often for a website the DB is essentially read-only, in which case lack of transaction, commit or rollback is forgivable. MySQL wins again. OTOH, business apps are not like this.
One possible solution might even be to use MySQL to back a website while using a more sophisticated DB in the business, and dumping into MySQL overnight.
Moral: it all depends. Create your own checklist of needs and then do your own comparison.
I've looked at a couple databases for web based genetics applications at UT-Houston - your choice depends quite a bit on what your needs are.
For raw speed, nothing beats MySQL on the benchmarks. This comes at a price, though, as any functionality that might negatively impact best-case speed (e.g. triggers, transactions) as well as other useful capabilities (sub-selects, views) are missing. Administration is easy, and DBD, JDBC, etc. drivers are quite solid.
On the other hand, if "number of cool capabilities" is what you're looking for, Oracle is quite good. We're using an Oracle backend as the master database (probably for other tasks as well), with a MySQL database on the webserver itself. Oracle seems to have an enormous learning curve, is a PITA for a few admin tasks, and would almost certainly be overkill for small databases.
PostgreSQL has some nice GUI admin apps, is totally free, but is neither as fast as MySQL nor as full-featured as Oracle.
Most of the commercial DBs with Linux ports came out shortly after we had the MySQL/Oracle setup running - DB2 looks promising.
If you can code around it's shortcomings, use MySQL (but read the license - you can't distribute it even with your app or use the Windows port for free)... and if you run into a stumbling block in the future, you shouldn't have difficulty upgrading to another DB.
Make sure you stick with a database-independent API (and as portable SQL as feasable) so you're not locked into one vendor - we're using Perl/DBI (probably mod_perl with persistent connections later) but JDBC or ODBC both have drivers for everything as well
well... here at work, we are currently on a path to support ~60,000 clients concurrently on browser based applications. The server, linux, the database MySQL. It does NOT have nice things like transaction/rollbacks and sub-selects, but I have not found ANYTHING that I couldn't code around. For MASSIVE raw speed, data mining, data warehousing, you can't beat MySQL.
You BEGIN a transaction, then INSERT, UPDATE, DELETE, etc to your heart's content. When all done, you END the transaction. Nothing is actually written to the database until the END.
The point is that anywhere befroe that END, you can ABORT the transaction, and nothing will have changed. So if you keeping data consistent requires 17 operations, and the 15th one fails (dup record which you don't want), you can abort, and all you've lost is time. You don't have to go back and undo the whole schmeer manually.
--
Infuriate left and right
Phillip Greenspun wrote what I consider to be the most sensible book on database-backed web sites available .. the book is called, uh, "Database Backed Web Sites". It's out of print, but he's coming out with a new edition called "Phillip and Alex's Guide to Web Publishing". (Alex is his dog. You'll find pictures of him all over Phillip's site. In addition to writing about the web, databases, and collaborative technologies, Phillip is a pretty good photographer.)
That book isn't out yet, but the good news is that the *entire text* of that book is already on-line at http://www.photo.net/wtr.
That site also has an excellent user forum on web-backed databases.
Phillip favors Oracle, but he also pushes Solid. He's down on mysql for the same reason others have mentioned - no transactions. If you want to know why, go check out his book.
Among other things, it mentions something I don't think anyone's hit on yet: as nice and fast as MySQL is, it doesn't support stored procedures, which are a very nice, language-independent way of storing a series of actions you want to take with your database. With stored procedures, recoding the programs accessing your db gets a lot easier. That being said, I do use MySQL for most sites I work on. This may change soon, though...
ep
--------- http://www.ahref.com: a community for web developers http://www.piou.org: yet another blog ---------
PHP3 offers persistent connections to PostgreSQL. That is, it lets you open a connection to PostgreSQL, and all subsequent PHP hits on the database will try to go through that same connection. I've used PHP's persistent connections to MySQL, but not to PostgreSQL yet.
For more info, see the PHP home page.
ep
--------- http://www.ahref.com: a community for web developers http://www.piou.org: yet another blog ---------
I've been doing all my WebDBase programming in PHP and MySQL. It is missing some fairly standard SQL database features (views among them), but it's extremely fast and relatively light on system resources (try running Oracle sometime and you'll see what I mean). I am not doing very sophisticated queries so more involved database programming may get stuck on what MySQL lacks, but it is overall an exceptional engine and it's free for non-commercial use. With Apache and PHP3 in toe, it's been exceptionally easy to manipulate databases online. And, after you've been doing queries in MS SQL Server 6.5 long enough, the speed difference is a revelation.
I was evaluating Oracle for use on our web server. I did get the DBD for Oracle to work without much problem.
My main gripe with Oracle is that it's too flexible (and therefore too complicated). It also consumes more disk space than a 3 day news spool for alt.binaries. When I get a little more hair on my chest and have an oversized application, I'll reconsider Oracle, but until then I'm sticking with PostgreSQL. PostgreSQL has problems, but I've already worked around them.
Now to play with Sybase...
> All software is broken.
Anon, I think you're missing the point of a database like FileMaker Pro. Well designed, stable and easy-to-use software like FileMaker Pro was meant to do away with the likes of you, me and those misnomered corporate entities, Information Technology departments. Some people would rather use the data they have than program a database to provide a simple set of ascending and descending sorts, or budget someone from IT to program the sorts for them.
I'm an Oracle DBA by profession, and I'd have to say that almost nobody really needs Oracle on *any* platform. It's EXTREMELY fast when properly tuned and has just about all the functionality that anybody really needs. However, it's very difficult to tune right since it has literally hundreds of adjustable parameters, some of which are undocumented. It's not a database that you can set up and forget about...you have to fiddle with it pretty frequently, or right dozens of scripts to do the fiddling for you.
99% of the DB programs out there either use auto-commit or a simulation thereof. Because of that, transactional stuff is wasted on most people.
I think MySQL lacks other important functionality, so I favor PostgreSQL. However, etiher one is a pretty good database for web use (most of the time).
All you need is the perl modules for DBI and DBD::Solid. It supports TCP/IP connections as well as Unix Pipes.
I have been using this setup for over a year without the slightest problem.
aryeh
------
Aryeh Goldsmith
Director of Interactive Programming
Iron Armadillo Inc.
aryeh
To email me, remove my pants!
If you're looking at commercial grade database solutions, your best bet is sybase. It's full featured, and the single process, simulated thread architecture is a lot faster than oracle or informix on low end servers.
My experience only covers the big RDBMSs: DB2, Oracle, Sybase, Informix, etc.
Overall:
None offer full SQL implementations. None offer clean programming constructs within SQL. None offer the ability to drive system+IO functionality from within a non3GL-API environment (their forms tools partially excepted).
Specifically:
* Informix is a barely visible wrapper round I/VSAM.
* Sybase is functionally equivalent to Oracle but has BADLY buggy SQL. If you are restricted to Sybase and want usable SQL, I advise driving it via SAS without passthru.
* Oracle does not have the jawdropping bugs of Sybase but has equivalent design/structure: it is NOT relational despite its advertising. It offers some relational sounding words but does not support correct consistency, integrity, etc. For example, to support transactions (OFF by default)(no, the ability to Commit data is not the same as Transactions), the DBMSs locking granularity is one table... Unbelievable. ie you can not practically establish a maximum level of data corruption risk in a multiuser environment.
However, since most selfprofessed RDBMS coders are simply manipulating data via APIs, they are really just buying ISAM plus a DDL and a couple of DML macros. In which case get whichever DBMS runs quicker, use your 3GL of choice, and just accept the high code/maintenance costs.
Other:
* DB2 was relational and used to get the thumbsup all round but have no idea where it is nowadays.
* A friend has said good things about PostGreSQL, principally comparing it to MSAccess, but it seems to offer full SQL.
* Re the speed comments on other posts here: realistically, minor processing time differences will seem slight for a nonmajor site, compared to the time spent downloading graphics etc.: I'd strongly suggest you go with ease of use rather than try to squeeze out an extra 0.1 second. Optimising TPS is usually a splendid way to waste spectacular amounts of time and money.
Good luck!
Hi,
:)
:) so I could use a ODBC driver. From what I know these are either commercial or lacking. Anyone worked with either MySQL or PostgreSQL and a ODBC driver?
:)
I've always wondered about this, and now seems like a good time to ask about it
The thing is that I'm still new to databases, flipping between PostgreSQL and MySQL. I've found MySQL to be faster, and PostgreSQL to be quite slow, but allows one to grow in the term of the language itself. It also offers some nice API's.
However, the problem with PostgreSQL can mostly be tracked down to the way it handles connection. From what I've learned it's using a postmaster to accept the connection, then the postmaster forks up a database backend and connects the two. This is a process far to slow for web development, but could probably be overcome by running a server which connects to the postmaster and keeps the connection, then that server could handle the requests from the CGI's.
Any better ideas?
Finally, I'm looking into perhaps writing a windows client for some of my databases (after I've done CGI, ncurses, and GTK versions that is
Thanks in advance
Terje Elde
Okey, so I can use persistant connections, or I can at least make my own frontend server to simulate them (depending on the language).
But how does the speed compare to MySQL? So far the speed is the one thing I'm kinda scared of with PostgreSQL. I mean, I like it, I've used it, and I've coded for it, but still I don't know...
Can it be used to run a web site with minimal delay? So low delay that it's a no-wait site even compared to LAN sites?
Also, this ODBC driver... You've connected PostgreSQL to windows clients and so you know it works? If so, which windows clients? And was it hard to set up??
Thanks for the help! (not only you, but everyone taking the time to reply)
Terje Elde
I sent mail query to Rob, but it was returned undeiverable? Two weeks into this online stuff and I am totally hooked. I am a Mac sys. support tech at VAR, and need fast tracks to leads like cracks & numbers. Gotta get ahead of the curve. People are asking me more and more about Linux. I'm still trying to master 8.5! Thanks for the insight on OS10, MOSR!
Running on a p233MMX, I'm mining through my past 8 months of sys logs to do some performance tracking. Original logs file was 220 Meg. I'm sorting 2.5 Million records in a minute. Sounds, kinda slow, but beats grep-ping through 2.5 million lines wish a bash script. MySQL Rocks. Slackware 3.5 kernel 2.2.0
Your boss is right, although maybe he doesn't
know why.
At my day job, we build a pure-Java product
that is supposed to work with any database
and JDBC driver. JDBC drivers suck. The
ingenuity of driver vendors in finding new
ways to suck is never-ending. There are
major bugs in metadata support. Translations
of database types to Java types are often
buggy, especially for types such as date,
time and timestamp. We've found that many
native code drivers (types 1-3) have
problems with memory corruption, memory leaks,
and multithreading.
In the specific case of the Oracle drivers, foreign key metadata access is unbelievably slow. We had to write our own monster SQL statement to fix the problem. The OCI driver had problems with
multithreading, (last time we checked -- over a year ago). I spoke to a vendor of a 3rd party JDBC driver for Oracle, which used OCI, and they described the problems they had with multithreading and OCI.
We found the thin driver to survive our stress tests much better than the OCI driver.
I've found SQL Anywhere very nice to use on
all Windows platforms. It's fast and easy to
run. There's supposedly a standalone UNIX
version, but I have no experience with it.
Object Design's products should also be
considered. They sell a variety of object
database systems and related tools. These
can be thought of more as providing
persistence for programming language data structures (C++ or Java), than as traditional relational database systems.
database.
In a read-only Web application transactions are unnecessary and table-level locks are sufficient. But a multi-user read/write application would need row-level locking granularity or clearly it'd be unreliable.
Similarly, if you don't use rollback in your multi-user systems, your database will be worse than useless. Period.