RE: Inserting into Oracle's timestamp type
On Fri, 2 Dec 2005 17:50:08 +1100, Steve Baldwin wrote: Hi Steve Thanx. > This may not answer your question, but you can incorporate the time > format mask in the call to TO_DATE. For example ... > > insert > into history (history_timestamp) > values (to_date(:ts_val, '-MM-DD HH24:MI:SS')) Yes, I tried that first actually, but when I displayed the result via a select in sqlplus all I saw was the date part. However it now occurs to me that that display may have been truncated by sqlplus since I did not use column history_timestamp format a30 or some such :-(. And it's 6:00 pm Friday here now, and I don't feel like going back to work to test it, for some reason... -- Cheers Ron Savage, [EMAIL PROTECTED] on 2/12/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
RE: Inserting into Oracle's timestamp type
This may not answer your question, but you can incorporate the time format
mask in the call to TO_DATE. For example ...
insert
into history (history_timestamp)
values (to_date(:ts_val, '-MM-DD HH24:MI:SS'))
Steve
-Original Message-
From: Ron Savage [mailto:[EMAIL PROTECTED]
Sent: Friday, 2 December 2005 4:25 PM
To: List - DBI users
Subject: Inserting into Oracle's timestamp type
Hi Folks
I see that if I have
create table history (history_timestamp timestamp not null)
I can insert into this with
insert into history (history_timestamp) values
(to_date('2005-12-02', '-MM-DD') || ' 03:39:25PM')
(there's a space before the 03)
But what if my original date-time is a single string with a 24 hour clock,
as in '2005-12-02 15:39:25'?
Is there an Oracle function which will accept the latter string, or do I
have to
construct the former monstrosity manually?
TIA.
--
Ron Savage
[EMAIL PROTECTED]
http://savage.net.au/index.html
Inserting into Oracle's timestamp type
Hi Folks
I see that if I have
create table history (history_timestamp timestamp not null)
I can insert into this with
insert into history (history_timestamp) values
(to_date('2005-12-02', '-MM-DD') || ' 03:39:25PM')
(there's a space before the 03)
But what if my original date-time is a single string with a 24 hour clock,
as in '2005-12-02 15:39:25'?
Is there an Oracle function which will accept the latter string, or do I have to
construct the former monstrosity manually?
TIA.
--
Ron Savage
[EMAIL PROTECTED]
http://savage.net.au/index.html
Re: What's the best "free" DB for a web-based app?
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-partitioning.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
Re: What's the best "free" DB for a web-based app?
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
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
Aaron Dancygier <[EMAIL PROTECTED]> wrote:
Just look at the list of companies that use mysql. Would google and yahoo
use it if it werent up to par?
Corporate entities (i.e. corporate managers) make decisions that are not
always generated from the same motivations as the rest of us. Google and Yahoo
using mysql does make a great endorsement, yes. But that's all it is. As a
fan of philosophy and deductive reasoning, I have been getting great joy over
the past year or so in noticing and pointing out 'deductive fallacies' whenever
they are made in public. Politics is ripe with them as are internet SIGs.
For those unfamiliar, this leap in reasoning is known as Argumentum ad
Verecundiam fallacy ("Argument from respect/modesty", Latin - a.k.a. Appeal to
Authority) or perhaps it might even fall under Ipse Dixit ("He, himself, said
it", Latin) fallacy. Similarly, to point out MySQL as being a better choice
simply because more people use it (dare I remind you of the popularity of
windows xp or internet explorer?) is an Ad Populum fallacy. How's about
because it has been around longer? (I don't know if it has or not, but that
would be Ad Antiquatum fallacy)
In summation, a fallacy is something that inspires an unjustified leap in
drawing a conclusion based on something that is not logically deduced based on
the premises. In short, I prefer to stick to the facts - not popularity
contests or the like.
SW
-
Yahoo! Personals
Single? There's someone we'd like you to meet.
Lots of someones, actually. Yahoo! Personals
Re: is there a bind_hash()?
On 12/1/05 7:17 PM, Todd Hepler wrote:
> Basically I'm looking for something like this snippet:
>
> @[EMAIL PROTECTED] = ();
> $sth->bind_columns(map { \$results{$_} } @fields);
>
> wrapped in a nice little interface.
Just an unrelated tip: the above can be done a bit more idiomatically (and
probably a teeny bit faster) like this:
$sth->bind_columns([EMAIL PROTECTED]@fields});
-John
is there a bind_hash()?
Hi all,
Is there a CPAN module out there that implements something like
bind_hash() from this article:
http://www.perl.com/pub/a/2001/03/dbiokay.html
My searches aren't coming up with anything.
Basically I'm looking for something like this snippet:
@[EMAIL PROTECTED] = ();
$sth->bind_columns(map { \$results{$_} } @fields);
wrapped in a nice little interface.
Thanks,
-Todd
RE: [cgiapp] "Best free DB for a web-based Perl app" response results...
At 2:05 PM -0500 12/1/05, Jesse Erlbaum wrote: I really don't think your "SQL Lite" analogy is a valid one. Oracle, PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no proven track record. Quick Google hits check: 2,250,000 for "Oracle" +rdbms 756,000 for "MySQL" +rdbms 384,000 for "PostgreSQL" +rdbms 207 for "SQL Lite" +rdbms A bit of a straw man, wouldn't you say? I just *had* to reply to this one. You made one of the biggest argument mistakes possible, which is basing judgement on blatently incorrect data. Namely, you spelled "SQLite" wrong, so of course Google wouldn't find it. Given the correct spelling, you would get *101,000* hits for SQLite + rdbms. While lesser than the others, its still the same order of magnitude as MySQL or PostgreSQL. 2,260,000 for: Oracle rdbms 740,000 for: MySQL rdbms 431,000 for: Postgres rdbms (which also returns 'PostgreSQL' numbers 101,000 for: SQLite rdbms SQLite is also far from a skunk works project and has a strong proven track record. It has been around for a long time and being used in a huge number of applications and devices. (Most recently, its even built into Mac OS X 10.4 for its Core Data component.) Many uses aren't even advertised, since it is public domain and users don't have to say they're using it. It also has dozens of active developers and a very busy mailing list. Its inventer, D Richard Hipp, was also honored with a top award at OSCON this year due to SQLite being one of the strongest movers and benefits to the open source community. So some criticism of SQLite is warranted, such a scalability with lots of writers, but not what you said. -- Darren Duncan
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
Maybe you should try its correct spelling, SQLite :) On 12/1/05, Jesse Erlbaum <[EMAIL PROTECTED]> wrote: > Hey Jeff -- > > > Regarding teh rest of your email, I have got to agree with you, most > > web apps use way more resources than they could possibly need, but you > > know what ? As a counter to your argument if you needed Oracle you'd > > just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use > > SQL Lite. > > I really don't think your "SQL Lite" analogy is a valid one. Oracle, > PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no > proven track record. > > > Quick Google hits check: > > 2,250,000 for "Oracle" +rdbms > 756,000 for "MySQL" +rdbms > 384,000 for "PostgreSQL" +rdbms > 207 for "SQL Lite" +rdbms > > > A bit of a straw man, wouldn't you say? > > -Jesse- > > > -- > > Jesse Erlbaum > The Erlbaum Group > [EMAIL PROTECTED] > Phone: 212-684-6161 > Fax: 212-684-6226 > >
RE: [cgiapp] "Best free DB for a web-based Perl app" response results...
Hey Jeff -- > Regarding teh rest of your email, I have got to agree with you, most > web apps use way more resources than they could possibly need, but you > know what ? As a counter to your argument if you needed Oracle you'd > just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use > SQL Lite. I really don't think your "SQL Lite" analogy is a valid one. Oracle, PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no proven track record. Quick Google hits check: 2,250,000 for "Oracle" +rdbms 756,000 for "MySQL" +rdbms 384,000 for "PostgreSQL" +rdbms 207 for "SQL Lite" +rdbms A bit of a straw man, wouldn't you say? -Jesse- -- Jesse Erlbaum The Erlbaum Group [EMAIL PROTECTED] Phone: 212-684-6161 Fax: 212-684-6226
Re: [GENERAL] undefined behaviour for sub-transactions?
Where is Postgres at with psql using savepoints implicitly to wrap every client command btw? My single biggest pet peeve with Postgres is that setting autocommit off in psql is basically unusable because any typo forces you to start your transaction all over again. Going to have to disagree with you here. I use it all day long ;). If I need a savepoint, I define one. Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
> > I would just like to note that speed and reliability are largely > dependent on the transaction profile of your application. If your > application is read heavy, MySQL is a sound choice. However if your > application consists mostly of database writes, PostgreSQL's MVCC [1] > architecture and row-level locking capabilities will generally provide > superior performance and reliability to MySQL. The InnoDB tabletype, which is the only tabletype that should be used for storing real data when doing enterprise-level work, provides row-level locking and complete ACID transactions. InnoDB also provides MVCC architecture. As for strength ... Oracle just bought the company that owns the InnoDB table type. If it wasn't good, Oracle wouldn't have bought it. A quick primer for those who don't know MySQL from a DBA's perspective: There are four primary tabletypes - MyISAM, InnoDB, Heap, and NDB. (There are others, but you won't use them.) * The default is MyISAM, which is non-transactional, but extremely fast. It requires table-level locks for concurrency, but you're not going to use it if you need concurrency. This is the tabletype that provides MySQL with its reputation for blazing-fast reads. * Heap tables are temporary in-memory. These guys are even faster than MyISAM tables, but with no permanency. They also do not provide any sort of transaction capabilities. * InnoDB tables are the enterprise tabletype. They provide complete ACID, foreign keys, and use MVCC. Because of this, they are slower than MyISAM or Heap tables. Whenever I design an app that uses MySQL tables, about 90% of them are InnoDB. The rest are MyISAM (for things like sessions and logs - items that never have concurrency issues). * NDB tables are MySQL's clustering table type. They provide complete ACID and MVCC architecture. Because of these options, I can tune my tables according to my need. Not everything requires ACID or MVCC. Those are features that have a performance cost. But, the nice thing is that I can do a query against tables that have different tabletypes. So, I can query a MyISAM joined to a InnoDB, if I wanted. Hopefully this will reduce the FUD. Rob
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
> in the next release. However, PgSQL is still slow, hard to use, and of > questionable reliability. Slow, i'm not going to argue cause it's "fast enough for me" and I don't have numbers. Hard to use ? What do you find hard ? I find it aboslutly devine to use, and mysql to be cludgy and awkward. The reason for that is cause of my experience and familiarity, I don't blame MySQL. Questionable Reliability : Where did this come from ? Over the past 6 years I've been writing webapps, postgres has never screwed up on me... I think your claim is a highly personal opinion. Regarding teh rest of your email, I have got to agree with you, most web apps use way more resources than they could possibly need, but you know what ? As a counter to your argument if you needed Oracle you'd just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use SQL Lite. It's a holy war folks, w.a.r. = we are right. that's how they get started :) Jeff.
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
Jesse Erlbaum wrote: Hi John -- The clear choice from these responses is Postgres because of its internal strength over MySql I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS SQL Server, and Informix. I've also been developing web apps for quite a long time, so I feel my opinions carry *some* weight. That being said, my preference is still MySQL. ... And, BTW: Nearly all those advanced, "academically correct" features which people point to when pimping PgSQL (row-level locking, stored procs, transactions, triggers, ref. integrity checking, clustering, etc.) are available for MySQL right now, or are slated to be available in the next release. However, PgSQL is still slow, hard to use, and of questionable reliability. I would just like to note that speed and reliability are largely dependent on the transaction profile of your application. If your application is read heavy, MySQL is a sound choice. However if your application consists mostly of database writes, PostgreSQL's MVCC [1] architecture and row-level locking capabilities will generally provide superior performance and reliability to MySQL. Kind Regards, - Fred [1] - MVCC means that you don't have to worry about writers blocking readers or readers blocking writers.
Re: [GENERAL] undefined behaviour for sub-transactions?
On Thu, Dec 01, 2005 at 01:04:52PM -0500, Greg Stark wrote: > Where is Postgres at with psql using savepoints implicitly to wrap every > client command btw? My single biggest pet peeve with Postgres is that setting > autocommit off in psql is basically unusable because any typo forces you to > start your transaction all over again. Are you looking for 8.1's ON_ERROR_ROLLBACK? test=> \set ON_ERROR_ROLLBACK interactive test=> begin; BEGIN test=> create table foo (x integer); CREATE TABLE test=> roeiuqrepuqw; ERROR: syntax error at or near "roeiuqrepuqw" at character 1 LINE 1: roeiuqrepuqw; ^ test=> insert into foo values (123); INSERT 0 1 test=> commit; COMMIT test=> select * from foo; x - 123 (1 row) -- Michael Fuhr
Re: [cgiapp] Re: "Best free DB for a web-based Perl app" response results...
On 12/1/05, Robert Hicks <[EMAIL PROTECTED]> wrote: > "... if i only needed MySQL, i'd use SQL Lite." > > Really? I think that would be a poor choice and I like SQLite. ; ) > > Robert I'll elaborate. PostgreSQL is already installed. It's what I "grew up with" I've very comfortable with it and I find it easy to use. So if I'm starting an application of any reasonable size I grab Pgsql cause it's "right there". Alot of our customers are very small mom and pop shops, selling crafts, advertising their business mostly read only. As well these users can only be really expected to see a few hundred vists a month, and probably less than 100 unique visitors. We deal with the truly "small guys" quite often. For those folks SQL lite is more then enough. An example is we have one guy that takes bookings for Opening/Closing swimming pools when the seasons change. So for a year the most bookings he'd have is 365 x 2, 2 appointments per day. SQL lite is perfect for this stuff. Myself and a few of the guys were going into this topic in more depth in the IRC channel [irc.perl.org #cgiapp]. We we talking more about the "right answer" to the original posting, not so much which is best RDBMS. IMHO, the right answer is none of them are best. Choose one. Start using it. See if it does what you want, and in a timly fashion. Use it for a year then evaluate if you need more of if it works. By then you'll have had more experience to develop your own opinions. If you are just starting in this type of application etc, chances are that most of your choices or at least the 2 big free ones will be adequate. You can haggle over details later. Why did I start with PostgreSQL ? I was good friends with Marc Fournier at the time, and he pushed it on me :) Had I have been in another part of the country, maybe I might have chosen MySQL. I'm comfortable with it, it suits my needs and puts food on the table. Jeff.
RE: [cgiapp] "Best free DB for a web-based Perl app" response results...
Hey Fred -- > I would just like to note that speed and reliability are largely > dependent on the transaction profile of your application. If your > application is read heavy, MySQL is a sound choice. However if your > application consists mostly of database writes, PostgreSQL's MVCC [1] > architecture and row-level locking capabilities will > generally provide > superior performance and reliability to MySQL. I question your assertion that PgSQL's row-level locking is faster than MySQL's row-level locking. I'd like to see actual benchmarks before saying that one particular approch is faster than another. -Jesse-
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
Jesse Erlbaum wrote: Hey Fred -- I would just like to note that speed and reliability are largely dependent on the transaction profile of your application. If your application is read heavy, MySQL is a sound choice. However if your application consists mostly of database writes, PostgreSQL's MVCC [1] architecture and row-level locking capabilities will generally provide superior performance and reliability to MySQL. I question your assertion that PgSQL's row-level locking is faster than MySQL's row-level locking. I'd like to see actual benchmarks before saying that one particular approch is faster than another. Fair enough. I will put together a benchmark using DBI and make the source and results open for review. I don't believe there currently any credible benchmarks in existence which can answer this question accurately.
Re: [cgiapp] Re: "Best free DB for a web-based Perl app" response results...
Robert Hicks wrote: > "... if i only needed MySQL, i'd use SQL Lite." > > Really? I think that would be a poor choice and I like SQLite. ; ) SQLite is an embedded database or at least that is how it is primarily used. Both Apple and RealBasic are using SQLite as a part of there latest persistence engines and I'm sure there are more companies solving client side data issue that have done the same. At a recent previous job, we created a web based system that installed both the web server and database on a desktop/laptop. The system requirements for the system needed to be low enough to run on a windows 95 box with 64 MB of RAM. The system was a derivative of another complete system that ran on top of MS SQL and I was able to port it to SQLite with little effort to greatly reduce installation and licensing issues along with system requirements. If you are planning on creating a system that will have low concurrent usage you will be well served by SQLite but if you plan on making something that will be hit hard then you will likely need to move to a bigger database package. -- David Steinbrunner
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
On Thu, Dec 01, 2005 at 12:24:12PM -0500, Jesse Erlbaum wrote: > Hi John -- > > > The clear choice from these responses is Postgres because of > > its internal > > strength over MySql > > I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS > SQL Server, and Informix. I've also been developing web apps for quite > a long time, so I feel my opinions carry *some* weight. Just look at the list of companies that use mysql. Would google and yahoo use it if it werent up to par? > > That, in a nutshell, is MySQL. Features such as "offset/limit" (which > were practically invented by MySQL, which are not standard SQL, which > don't exist in Oracle, and only exist in PgSQL because they were so > damned useful) are a classic example of why MySQL is the most popular > database in the whole world for web applications. It is the right tool > for the job. Same with the "auto increment" columns. A feature which > didn't exist in Oracle-like databases, but was a practical solution > which made life that much more easy. Dont forget that time(less typing) saving replace command. > > There are dozens of other examples like these (such as the MySQL > interactive shell, which beats the pants off of sqlplus, or mysqldump > which annihilates pg_dump). The theme here is that MySQL was created to > be three things: > > 1. Simple > 2. Reliable > 3. Fast > > Let's not forget that the "P" in Perl stands for "Practical". PgSQL was > created as an academic exercise: "Can we write our own Oracle?" If I > wanted to be "academically correct", I'd be programming in Java. I > don't, and I'm not. > > > And, BTW: Nearly all those advanced, "academically correct" features > which people point to when pimping PgSQL (row-level locking, stored > procs, transactions, triggers, ref. integrity checking, clustering, > etc.) are available for MySQL right now, or are slated to be available > in the next release. However, PgSQL is still slow, hard to use, and of > questionable reliability. MySql has really caught up stored procedures, views, and triggers are new features in 5.0 while replication (since 3.xx), clustering (since 4.1), transactional table types (innodb since 3.xx), have been around for a while. Even with all these new features mysql is still blazing fast. Aaron Dancygier
Re: [GENERAL] undefined behaviour for sub-transactions?
Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > Where is Postgres at with psql using savepoints implicitly to wrap every > > client command btw? > > I think that 8.1 psql can be told to do that. Right: \set ON_ERROR_ROLLBACK interactive -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073
Re: [GENERAL] undefined behaviour for sub-transactions?
Greg Stark <[EMAIL PROTECTED]> writes: > Where is Postgres at with psql using savepoints implicitly to wrap every > client command btw? I think that 8.1 psql can be told to do that. regards, tom lane
RE: [cgiapp] "Best free DB for a web-based Perl app" response results...
Hi John -- > The clear choice from these responses is Postgres because of > its internal > strength over MySql I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS SQL Server, and Informix. I've also been developing web apps for quite a long time, so I feel my opinions carry *some* weight. That being said, my preference is still MySQL. First off, if I want/need Oracle functionality, I'll just use Oracle. When you compare PgSQL to Oracle on "internal strength", it's a no-brainer. Nobody is going to argue that PgSQL is better than Oracle. In fact, PgSQL isn't better than any of the other so-called "ACID" databases I mention above. And some of those databases are free (or very nearly so) for small sites. The biggest criticism that I've heard leveled at MySQL is that "...it's not ACID. It's more like a file system with a SQL interface." After 10 years developing web/database apps, I've discovered that 80% of the time, that is exactly what you need. Web applications are software, but they are very notably different from client-server software. One of the most significant differences is that the web is stateless. This means that some advanced features, such as transactions and cursors are not generally applicable. If I was writing a client-server application I would have a stateful connection, through which I could use a cursor to step through results, or hold a transaction open. That is not the case on the web. On the web, when a page is rendered, the application stack is effectively terminated. That's not to say you *couldn't* devise some scheme to simulate statefulness. You simply would not *want* to. On the web, every request has to contain all the information needed to fully articulate a transaction. That means, you need a FAST database with the tools necessary to get the job done. That, in a nutshell, is MySQL. Features such as "offset/limit" (which were practically invented by MySQL, which are not standard SQL, which don't exist in Oracle, and only exist in PgSQL because they were so damned useful) are a classic example of why MySQL is the most popular database in the whole world for web applications. It is the right tool for the job. Same with the "auto increment" columns. A feature which didn't exist in Oracle-like databases, but was a practical solution which made life that much more easy. There are dozens of other examples like these (such as the MySQL interactive shell, which beats the pants off of sqlplus, or mysqldump which annihilates pg_dump). The theme here is that MySQL was created to be three things: 1. Simple 2. Reliable 3. Fast Let's not forget that the "P" in Perl stands for "Practical". PgSQL was created as an academic exercise: "Can we write our own Oracle?" If I wanted to be "academically correct", I'd be programming in Java. I don't, and I'm not. And, BTW: Nearly all those advanced, "academically correct" features which people point to when pimping PgSQL (row-level locking, stored procs, transactions, triggers, ref. integrity checking, clustering, etc.) are available for MySQL right now, or are slated to be available in the next release. However, PgSQL is still slow, hard to use, and of questionable reliability. > Also, Postgres is much more Oracle-like in syntax than > MySql, so if we should ever graduate to big-daddy-Oracle, we > can do that > easier. I'd like to shoot this idea down pretty quick: The idea that you might "graduate" to Oracle. I'd like to point to an article in InfoWorld just two weeks ago: http://weblog.infoworld.com/article/05/11/17/47FEmainmigrate_1.html The article is about migrating from a mainframe computer. I direct your attention to page two: "Sabre Pushes the Limits. Sabre Holdings -- parent of the Travelocity online consumer booking service and the Sabre travel reservations and ticketing system, which handles about 40 percent of worldwide travel reservations -- is in the midst of one of the largest mainframe migrations. Todd Richmond, the company's vice president of enterprise architecture, says Sabre has the world's third-largest implementation of IBM TPF (Transaction Processing Facility) mainframes. In an effort that began almost six years ago, however, Sabre has migrated most of its domestic booking services to four-way, Intel (Profile, Products, Articles) Itanium-based HP (Profile, Products, Articles) NonStop servers and a cluster of HP Integrity Itanium-based servers running 64-bit Red Hat (Profile, Products, Articles) Linux and the MySQL database." The article goes on to describe what they're doing with MySQL: "...Sabre will continue to use NonStop servers for database transactions because they are able to process the 14,000 transactions per second more reliably across large data sets typical of Sabre's environment" So, there you have it: 14,000 transactions per SECOND, handling 40% of the worldwide travel reservations... And all running on MySQL. So, it is possible to
RE: What's the best "free" DB for a web-based app?
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.
RE: [cgiapp] "Best free DB for a web-based Perl app" response results...
Jesse Erlbaum - thanks much for your very insightful comments - we stand corrected - we're back in the MySql camp!!! - John Armstrong - Sacramento
Re: [GENERAL] undefined behaviour for sub-transactions?
Jaime Casanova <[EMAIL PROTECTED]> writes: > that is a mis-conception... a transaction *must* be atomic (all or nothing)... > the reason some databases act that bad is because they don't support > savepoints, and because postgres does it doesn't need that > awfulness... Well it's not as bad as all that. It's still "atomic" in that an interruption cannot leave half of the transaction committed and half undone. In other words "all" is just "all of the actions that didn't produce an error". It's up to the client whether to commit the transaction after an error has occurred. It's great that Postgres follows the standard here, but don't go overboard on the criticism of other databases either. Where is Postgres at with psql using savepoints implicitly to wrap every client command btw? My single biggest pet peeve with Postgres is that setting autocommit off in psql is basically unusable because any typo forces you to start your transaction all over again. -- greg
Re: [GENERAL] undefined behaviour for sub-transactions?
On Wed, Nov 30, 2005 at 02:58:15PM -0700, Michael Fuhr wrote: > > Shouldn't that be 8.0 and later? That's when savepoints were > introduced. Or are you referring to something else? Doh. Indeed. I was _thinking_ os something else, but not referring to something else. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton
Re: What's the best "free" DB for a web-based app?
Not to add any more fuel to this flame but 10XE works fine with DBD::Oralce
at least for windows.
"Rich Jesse" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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.
Re: [cgiapp] "Best free DB for a web-based Perl app" response results...
From: John Armstrong <[EMAIL PROTECTED]> >There was cross-agreement that Postgres is slower than MySql, Maybe this is irrelevant, but... don't forget to consider which data is primarily read-only and might be faster served from a caching system (optimized for reads instead of writes/transactions) like SleepyCat or the much simpler Perl module Cache::Cache (search CPAN, or Perl-cache on Sourceforge. I've used 1.04. There's a newer version but the author said it's a fork and doesn't recommend it.). You can do things to refresh the cache hourly or daily (as a batch job) and alleviate some load on your database. I only mentioned it because you mentioned performance a couple times. Mark
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
Steffen Goeldner wrote: > I'd like to remark that SQL/CLI has a more general function > > GetSessionInfo( ConnectionHandle, InfoType, ... ) > > Data TypeCode Information Type > --- - > --- > USER and CURRENT_USER CHARACTER(L)47 CURRENT USER > CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT > TRANSFORM GROUP > CURRENT_PATHCHARACTER(L) 20005 CURRENT PATH > CURRENT_ROLECHARACTER(L) 20006 CURRENT ROLE > SESSION_USERCHARACTER(L) 20007 SESSION USER > SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER CURRENT_CATALOG CHARACTER(L) 20009 CURRENT CATALOG CURRENT_SCHEMA CHARACTER(L) 20010 CURRENT SCHEMA (The last 2 InfoType were missing in the previous post, sorry!) Steffen
