Follow Slashdot stories on Twitter

 



Forgot your password?
typodupeerror
Check out the new SourceForge HTML5 internet speed test! No Flash necessary and runs on all devices. ×
Linux Software

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?"
This discussion has been archived. No new comments can be posted.

What Database is the best for a Web Site/Small Business?

Comments Filter:
  • 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!

  • 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.

  • 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...
  • But I won't fault MySQL, either. Since both are gratis for testing, I would suggest testing both.

  • 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.
    --
  • 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.

    --
  • 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.
  • I've written an article on choosing a web database (and a server platform) over at ahref.com [ahref.com] .

    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

  • Delta:
    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

  • 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...
  • 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.

  • 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,

    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 :) 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?

    Thanks in advance :)

  • 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)

  • 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!
  • by Van ( 16850 )
    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.

The price one pays for pursuing any profession, or calling, is an intimate knowledge of its ugly side. -- James Baldwin

Working...