What Database is the best for a Web Site/Small Business? 47
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?"
What about Access or Visual Fox Pro? (Score:1)
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.
What about Access or Visual Fox Pro? (Score:1)
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.
NOT ORACLE unless you're Citibank... (Score:1)
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!
Addenda re: Oracle JDBC drivers (Score:1)
Which SQL server supports ODBC? (Score:1)
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...
Personally, I like PostGreSQL. (Score:1)
But I won't fault MySQL, either. Since both are gratis for testing, I would suggest testing both.
Interbase is a possible option (Score:1)
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).
Web site needs vs small business needs (Score:1)
only if you're incompetent. OWAS is extremely fast.
PostgreSQL vs. MySQL vs. Oracle vs... (Score:1)
--
Web site needs vs small business needs (Score:1)
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.
Depends on your needs (Score:1)
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
Database... (Score:1)
Almost-a-newbie's definition of transaction (Score:1)
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.
--
Phillip Greenspun's useful database book & site (Score:1)
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.
Article on choosing a web database (Score:1)
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
Persistent connections to PostgreSQL (Score:1)
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. [php.net]
ep
MySQL here too (Score:1)
I got Linux Oracle to work with perl:DBI (Score:1)
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...
FileMaker. Ha. (Score:1)
it ain't oracle (Score:1)
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).
Solid (newbie) question (Score:1)
I have been using this setup for over a year without the slightest problem.
aryeh
------
Aryeh Goldsmith
Director of Interactive Programming
Iron Armadillo Inc.
Sybase definitely. (Score:1)
Relational DB required? (Score:1)
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!
Which SQL server supports ODBC? (Score:1)
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
Which SQL server supports ODBC? (Score:1)
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)
MacHack on the Attack! (Score:1)
MySQL (Score:1)
Addenda re: Oracle JDBC drivers (Score:1)
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.
Other databases to consider (Score:1)
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.
NOT ORACLE unless you're Citibank... (Score:1)
Similarly, if you don't use rollback in your multi-user systems, your database will be worse than useless. Period.