Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
This is a pretty difficult to answer question. How heavy do we expect the
usage to be? What kind of system will it be running on?
In general, I've found that MySQL has the best GUI applications. The MySQL
Query Browser makes writing scripts and executing arbitrary SQL statements
on the database a breeze. I've heard arguments that postgresql is a better
database platform; the claim is that postgres is more functional and runs
faster (I don't know how this changed with MySQL 5).
If your site is going to be real light traffic, then you can use SQLite,
which is file-based. Writes to the DB require file locking but reads are
concurrent.
Push comes to shove, a well designed program will not have a lot of
dependency on the database. All of your SQL statements should be aggregated
in a single place, and if you use DBI (and fairly vanilla syntax) then you
can completely change the platform just by changing the DSN.
Josh
--
Josh Danziger
Josh@allDucky.com
-----Original Message-----
From: John Armstrong [mailto:john_armstrong@dot.ca.gov]
Sent: Tuesday, November 29, 2005 8:02 PM
To: dbi-users@perl.org
Cc: cgiapp@lists.erlbaum.net
Subject: [cgiapp] What's the best "free" DB for a web-based app?
Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
------------------------------------------------------------ ---------
Web Archive: http://www.mail-archive.com/cgiapp@lists.erlbaum.net/
http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
To unsubscribe, e-mail: cgiapp-unsubscribe@lists.erlbaum.net
For additional commands, e-mail: cgiapp-help@lists.erlbaum.net
You're likely to get as many opinions as there are respondents to your =
question. MySQL seems to be the most popular for smaller Web apps. =
It's free, Free, easy to use, well documented, widely understood, and =
capable enough for most tasks. PostgreSQL is also popular for similar =
reasons, but my impression is far fewer people are familiar with it than =
MySQL. I've heard that PostgreSQL might be somewhat more capable than =
MySQL, but I haven't used PostgreSQL myself. Since this sounds like =
your first open-source DB experience, I think you'd be better off with =
MySQL, since it's easiest to find help should you need it.
Ted Behling
-----Original Message-----
From: John Armstrong [mailto:john_armstrong@dot.ca.gov]
Sent: Tuesday, November 29, 2005 8:02 PM
To: dbi-users@perl.org
Cc: cgiapp@lists.erlbaum.net
Subject: What's the best "free" DB for a web-based app?
Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no =
Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
At 5:02 PM -0800 11/29/05, John Armstrong wrote:
>Hi - I'm soon to be doing a Perl app on the Internet, that'll need
>database. We want the db to be as "free" as possible, but still fully
>multi-user (web-based). Would the best route be MySql on Linux? Random
>access files? Something else? We want no licensing obligations (no Oracle,
>Sequal Server, etc.). We want to go with Perl because it's the best
>programming language invented by humans.
If you want something that's trivially easy to use, try SQLite.
The database manager is embedded in the DBD::SQLite module itself,
and you have no separate server processes to maintain, and you can
specify a database's storage location like an ordinary file.
Its also faster than anything else for some types of usage, and
unlike many typical MySQL setups, it is fully transactional, ACID
compliant, and safe.
Certain kinds of usage with many writing processes may be slow,
though, since only one active writer can access a SQLite database at
once.
SQLite is also public domain, which is about as liberty as you can get.
-- Darren Duncan
postgreSQL!
On Tue, 29 Nov 2005, John Armstrong wrote:
>
> Hi - I'm soon to be doing a Perl app on the Internet, that'll need
> database. We want the db to be as "free" as possible, but still fully
> multi-user (web-based). Would the best route be MySql on Linux? Random
> access files? Something else? We want no licensing obligations (no Oracle,
> Sequal Server, etc.). We want to go with Perl because it's the best
> programming language invented by humans.
>
--
/********** Louis Gonzales ***********\
/****** http://www.linuxlouis.net ****\
/**** louis.gonzales@linuxlouis.net **\
/**** BSCS Eastern Michigan Univ. ****\
/****** HP Certified Professional ****\
--s2ZSL+KKDSLx8OML
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On 2005-11-29 18:13:04 -0800, Darren Duncan wrote:
> At 5:02 PM -0800 11/29/05, John Armstrong wrote:
> >Hi - I'm soon to be doing a Perl app on the Internet, that'll need
> >database.
>=20
> If you want something that's trivially easy to use, try SQLite.
[...]
> Its also faster than anything else for some types of usage, and unlike ma=
ny=20
> typical MySQL setups, it is fully transactional, ACID compliant, and safe.
>=20
> Certain kinds of usage with many writing processes may be slow, though, s=
ince=20
> only one active writer can access a SQLite database at once.
Even worse, when a table is locked by one process, attempts to access it
=66rom another result in an error. So it is entirely possible that an
application works fine during testing but bombs out spectacularly with a
higher workload. There are ways around that, but that default behaviour is
surprising for anyone who's used other RDBMSs before.
hp
--=20
_ | Peter J. Holzer | In our modern say,learn,know in a day
|_|_) | Sysadmin WSR | world, perhaps being an expert is an
| | | hjp@wsr.ac.at | outdated concept.
__/ | http://www.hjp.at/ | -- Catharine Drozdowski on dbi-users.
--s2ZSL+KKDSLx8OML
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
iQDQAwUBQ41vbVLjemazOuKpAQF5hQXRAaiO0b6sJ1NBdqEFN04WuGcapENq FijB
85bQKuUDYVbTOdj0zuKahFirRfFC1gUou/6/VzFZrIQ1gGLSu2naOp0UYl1D szHn
vkQfyIZLeG51prmzn3LLnQbrFOplqab/lvnrCOx8ZZqD8CxC4WEyHvcsdi4Y D1qH
geC6Zwy0dZ2MoAsX5hKmHy8c19TzgEqMNPinQ4Hqgkgsi4okHeQOYCdb6SY4 NIYw
XIij5k7FkHwp2ovZ+fljO93Xjw==
=gloy
-----END PGP SIGNATURE-----
--s2ZSL+KKDSLx8OML--
I'd just like to ask anyone thinking of replying to this thread to *please*
only do so if you can add something useful that's not already been said,
and do so politely, with consideration for the validity of other peoples views.
Tim.
On Tue, Nov 29, 2005 at 05:02:23PM -0800, John Armstrong wrote:
>
> Hi - I'm soon to be doing a Perl app on the Internet, that'll need
> database. We want the db to be as "free" as possible, but still fully
> multi-user (web-based). Would the best route be MySql on Linux? Random
> access files? Something else? We want no licensing obligations (no Oracle,
> Sequal Server, etc.). We want to go with Perl because it's the best
> programming language invented by humans.
>
Thanks much for your cautions, Tim, but no harm done at all. Looks like
most people endorsed MySql. SQL Lite is totaling a little weak, and
Postgres is as good as MySql, just has more of an emphasis on db internals
rather than (MySql's) GUI. MySql's freeness might be getting shakey,
except when under Linspire (Linux). For this app, the design version is
in Access, prod on Linux. Circa 6 concurrent users, 24 tables, 3 lookup
tables of which are updated/supplied by federal and CA state agencies,
linked to, not internal to the app.
Tim Bunce
com> cc: dbi-users@perl.org, cgiapp@lists.erlbaum.net
Subject: Re: What's the best "free" DB for a web-based app?
11/30/2005 03:27
AM
I'd just like to ask anyone thinking of replying to this thread to *please*
only do so if you can add something useful that's not already been said,
and do so politely, with consideration for the validity of other peoples
views.
Tim.
On Tue, Nov 29, 2005 at 05:02:23PM -0800, John Armstrong wrote:
>
> Hi - I'm soon to be doing a Perl app on the Internet, that'll need
> database. We want the db to be as "free" as possible, but still fully
> multi-user (web-based). Would the best route be MySql on Linux? Random
> access files? Something else? We want no licensing obligations (no
Oracle,
> Sequal Server, etc.). We want to go with Perl because it's the best
> programming language invented by humans.
>
Wow, that's a tough one. It really depends on what you want/need from
your database. What's your recoverability liability? How scalable do
you need your app? Platform/OS requirements?
Being from primarily an Oracle background (as far as DBs go), I'd say
overall "Oracle". Free? Yes! There's now a free Express Edition
("XE") currently for Linux and Winders (beta) which can be downloaded
from http://www.oracle.com/technology/products/database/xe/index. html
Like everything there are caveats, and I believe a big one to be that
DBD::Oracle doesn't yet play will this version of Oracle (see previous
threads on this list although I haven't been paying that close attention
to it myself since we use the full-blown Enterprise Edition). It's also
limited in DB size and what features are supported. See the above link
for more info.
I've also used MySQL 4.x at home. Being an Oracle-type, I found it to
be lacking in backup/recovery as well as scalability, but my tests
admittedly weren't very scientific. For home use, I've switched my
MySQL DBs to PostgreSQL 8.0 (haven't taken the 8.1 leap yet).
PostgreSQL? Quickly, it's as close feature-wise to "enterprise" as
full-blown pay-up-the-gazoo Oracle as I've seen. Plus, you can write
your DB procs in Perl if you want. :)
DBI-wise, I have no real preference for any of the three. SQL-wise, I
much prefer Oracle, with PostgreSQL coming in a close second, with MySQL
off in a distant 3rd. Probably because the syntax of PostgreSQL is very
close to Oracle. :) =20
I know this e-mail is way too generic to be of any real use, but thought
I'd chime in on some areas that might be important for your decision
like recoverability, scalability and flexibility.
HTH! GL!
Rich
-----Original Message-----
From: John Armstrong [mailto:john_armstrong@dot.ca.gov]=20
Sent: Tuesday, November 29, 2005 7:02 PM
To: dbi-users@perl.org
Cc: cgiapp@lists.erlbaum.net
Subject: What's the best "free" DB for a web-based app?
Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no
Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
You'd be remiss not to look at Firebird.
Mysql5, Firebird 1.5+ and Postgres are what you should be
comparing and testing in my opinion.
Not to add any more fuel to this flame but 10XE works fine with DBD::Oralce
at least for windows.
"Rich Jesse"
news:FB5D3CCFCECC2948B5DCF4CABDBE6697545C1E@QTEX1.qg.com...
Wow, that's a tough one. It really depends on what you want/need from
your database. What's your recoverability liability? How scalable do
you need your app? Platform/OS requirements?
Being from primarily an Oracle background (as far as DBs go), I'd say
overall "Oracle". Free? Yes! There's now a free Express Edition
("XE") currently for Linux and Winders (beta) which can be downloaded
from http://www.oracle.com/technology/products/database/xe/index. html
Like everything there are caveats, and I believe a big one to be that
DBD::Oracle doesn't yet play will this version of Oracle (see previous
threads on this list although I haven't been paying that close attention
to it myself since we use the full-blown Enterprise Edition). It's also
limited in DB size and what features are supported. See the above link
for more info.
I've also used MySQL 4.x at home. Being an Oracle-type, I found it to
be lacking in backup/recovery as well as scalability, but my tests
admittedly weren't very scientific. For home use, I've switched my
MySQL DBs to PostgreSQL 8.0 (haven't taken the 8.1 leap yet).
PostgreSQL? Quickly, it's as close feature-wise to "enterprise" as
full-blown pay-up-the-gazoo Oracle as I've seen. Plus, you can write
your DB procs in Perl if you want. :)
DBI-wise, I have no real preference for any of the three. SQL-wise, I
much prefer Oracle, with PostgreSQL coming in a close second, with MySQL
off in a distant 3rd. Probably because the syntax of PostgreSQL is very
close to Oracle. :)
I know this e-mail is way too generic to be of any real use, but thought
I'd chime in on some areas that might be important for your decision
like recoverability, scalability and flexibility.
HTH! GL!
Rich
-----Original Message-----
From: John Armstrong [mailto:john_armstrong@dot.ca.gov]
Sent: Tuesday, November 29, 2005 7:02 PM
To: dbi-users@perl.org
Cc: cgiapp@lists.erlbaum.net
Subject: What's the best "free" DB for a web-based app?
Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no
Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
On Wed, 2005-11-30 at 10:34 -0600, Jesse, Rich wrote:
> Being from primarily an Oracle background (as far as DBs go), I'd say
> overall "Oracle". Free? Yes! There's now a free Express Edition
> ("XE") currently for Linux and Winders (beta) which can be downloaded
> from http://www.oracle.com/technology/products/database/xe/index. html
> Like everything there are caveats, and I believe a big one to be that
> DBD::Oracle doesn't yet play will this version of Oracle (see previous
> threads on this list although I haven't been paying that close attention
> to it myself since we use the full-blown Enterprise Edition). It's also
> limited in DB size and what features are supported. See the above link
> for more info.
There are some clearly defined areas where this certainly is the right
answer. Oracle still has some killer OLAP features in its Enterprise
product compared to Postgres; off the top of my head:
- bitmap indexes (though the latest version of Bizgres, a Postgres
extension, supports these)
- materialized views with query rewriting (lets you explicitly
precompute results for slow queries, or partial precomputation to
reduce the time required for slow queries)
- table partitioning (though some limited support, to the level of
Oracle 8.x or so, exists in Bizgres 0.7 and has been integrated into
Postgres 8.1)
- compressed tablespaces, which for highly compressible data reduces
IO load
If you are considering MySQL, the InnoDB back-end is much better for
OLTP. The MyISAM backend may be a toy database, but there are a lot of
use cases where its "atomic operations" model is a lot faster - even
though it's basically retarded.
SQLite is useful, but does not scale - it is not possible to have
concurrent update transactions. However for small web applications
which you need to distribute to unknown environments it is an excellent
choice.
So, really it depends if your web application is primarily OLAP or OLTP
(analytical or transactional). Personally I find Oracle a bit clunky
for OLTP, and have had it return blatantly incorrect results for me a
few too many times to trust it.
In a few years' time, it is quite possible that those great guys at
Greenplum will have enough of those OLAP features in Bizgres that the
remaining difference between it and Oracle will matter to virtually
no-one.
Which makes a compelling argument for a general purpose recommendation
for Postgres.
Sam.
Sam Vilain wrote:
> There are some clearly defined areas where this certainly is the right
> answer. Oracle still has some killer OLAP features in its Enterprise
> product compared to Postgres; off the top of my head:
>
> - bitmap indexes (though the latest version of Bizgres, a Postgres
> extension, supports these)
[...]
As an Oracle person, having little experience with other databases,
I reading these informative comments, and this one in particular
is close to my concerns.
So followup question: are any of these other systems good
with large'ish databases? I have multi-TB Oracle databases that
I'd like to explore on a "free" database. Is that realistic?
My current system relies heavily on partitions, bitmap indexes,
table compression, though I can live without query rewrite.
For example, I have a ~10 billion row, ~1TB table, time partitioned,
with about 10-20 million rows per day. I need "interactive"
performance (2-10 seconds) response on queries that are confined
to 2-3 days data. Obviously, I can't scan a TB table each time.
In Oracle, the I/O pruning is simple to setup, and then automatic,
with partitioning.
Are there similar facilities in MySQL, or the others?
Thanks!
Mark
On 12/1/05 10:00 PM, Mark wrote:
> For example, I have a ~10 billion row, ~1TB table, time partitioned,
> with about 10-20 million rows per day. I need "interactive"
> performance (2-10 seconds) response on queries that are confined
> to 2-3 days data. Obviously, I can't scan a TB table each time.
> In Oracle, the I/O pruning is simple to setup, and then automatic,
> with partitioning.
>
> Are there similar facilities in MySQL, or the others?
Postgres 8.1 supports basic partitioning:
http://www.postgresql.org/docs/8.1/interactive/ddl-partition ing.html
If anyone has a 10 billion row MySQL table, partitioned or otherwise, that
that they can actually do useful work with, I'd like to hear about it... :)
-John
On Thu, 2005-12-01 at 19:00 -0800, Mark wrote:
> So followup question: are any of these other systems good
> with large'ish databases? I have multi-TB Oracle databases that
> I'd like to explore on a "free" database. Is that realistic?
> My current system relies heavily on partitions, bitmap indexes,
> table compression, though I can live without query rewrite.
>
> For example, I have a ~10 billion row, ~1TB table, time partitioned,
> with about 10-20 million rows per day. I need "interactive"
> performance (2-10 seconds) response on queries that are confined
> to 2-3 days data. Obviously, I can't scan a TB table each time.
> In Oracle, the I/O pruning is simple to setup, and then automatic,
> with partitioning.
>
> Are there similar facilities in MySQL, or the others?
That's a slightly larger data set than anything I've worked with, but if
you don't need the materialised views with query rewrite then I'd say
it's worth investigating the latest Bizgres to see how far it's got,
then relaying your experience back via the Bizgres mailing list. It's
got a few of those handy OLAP support facilities like a loader GUI, etc.
http://www.bizgres.org/
http://pgfoundry.org/projects/bizgres/
Currently Bizgres has no table compression, but on the scale of things
that is an "easy" feature to add, so I wouldn't be surprised if it comes
soon. Perhaps it would cost less than a year of an Oracle license to
implement ;).
Sam.