Slashdot Mirror

Also, while the source code may be available, the licesnse that MySQL is under does not come anywhere close to meeting the open source definition - with the exception of older versions as you mention.

MySQL is, AFAIK open source, just not Free Software. Feel free too look up both definitions. The limitation to MySQL is available
Don't forget that GPL'd versions of MySQL (older releases) are always made available as well.

PostgreSQL certainly has its good points. I would guess one reason they're sticking with MySQL would be speed. MySQL is significantly faster than PostgreSQL, and everything else. Although recently MS SQL Server 7 has caught up in some kinds of operations.

MySQL is extremely fast if you are doing mostly SELECTs (versus a lot of INSERTs and UPDATEs), and that's what Slashdot tends to do. The site spends much more time retrieving messages (and displaying them) than it does storing new messages. Even after the MySQL team spent lots of time tweaking benchmarks to make *PostgreSQL* look better (!), MySQL continues to be much faster, especially in SELECTs.

But as the friendly MySQL team will tell you (on the excellent, excellent mailing list), there are many scenarios where PostgreSQL is the best choice. And hey, there are situations where industrial-size Oracle is the best choice (although it will cost you 5-7 figures).

PostgreSQL has some more advanced features like user-defined types, triggers, rules and some transaction support. However, PostgreSQL lacks many of the standard types and functions from ANSI SQL and ODBC. See the database comparison with crash-me for a complete list of limits and which types and functions are supported or unsupported.

Normally, PostgreSQL is a magnitude slower than MySQL. This is due largely to their transactions system which is not as sofisticated as Berkely DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe. If you really the rich type system PostgreSQL offers and you can afford the speed penalty, you should take a look at PostgreSQL.

I can imagine with the loads on slashdot on order of magnitude speed difference makes a world of difference. Second there's already an complete MySQL code base for slash, so you get stuck with "industrial inertia".

PostgreSQL has some more advanced features like user-defined types, triggers, rules and some transaction support. However, PostgreSQL lacks many of the standard types and functions from ANSI SQL and ODBC. See the database comparison with crash-me for a complete list of limits and which types and functions are supported or unsupported.

Normally, PostgreSQL is a magnitude slower than MySQL. This is due largely to their transactions system which is not as sofisticated as Berkely DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe. If you really the rich type system PostgreSQL offers and you can afford the speed penalty, you should take a look at PostgreSQL.

I can imagine with the loads on slashdot on order of magnitude speed difference makes a world of difference. Second there's already an complete MySQL code base for slash, so you get stuck with "industrial inertia".

The fact that Oracle licensing fees are more blatantly large makes it rather clearer that Oracle is out to Take Care Of Your Money (by putting it in their bank account!), which shows off the clear need to periodically use other vendors' DBMSes.

The other fortunate thing is that Oracle primarily is connected to selling Big Databases, which is something that only people with big chequebooks tend to get involved with.

There is not any reasonable likelihood of any of the "libre" options ( e.g. - PostgreSQL, InterBase, MySQL, ... becoming reasonable alternatives at the Big Iron / Enterprise end of things any time soon, although they may become quite reasonable choices for "small, departmental" applications.

All you need to do is to look at the licensing of Designer 2000 and see how while the fees may rise exponentially, this results in a die-off of deployment amongst anyone that doesn't have deep pockets...

Nothing like good old-fashioned IRC support: "My database is down, my company is going down the drain, the users can't work, what do I do!?!?!?" Typical IRC reply: "Go fsck yourself".

If you want to be sure that you'll get an answer to your support request, you pay for it, just like with a proprietary app.

For mySQL, from the source:
https://www.mysql.com/license.htmy

Or, from a consultant:
http://mysql.com/consultants_search.htmy

And, similarly, for PostgreSQL:

http://www.pgsql.com/support.html

Of course, expecting good results on any business-critical system without a backup & recovery plan or competent staff is a bit naive, anyway.

Nothing like good old-fashioned IRC support: "My database is down, my company is going down the drain, the users can't work, what do I do!?!?!?" Typical IRC reply: "Go fsck yourself".

If you want to be sure that you'll get an answer to your support request, you pay for it, just like with a proprietary app.

For mySQL, from the source:
https://www.mysql.com/license.htmy

Or, from a consultant:
http://mysql.com/consultants_search.htmy

And, similarly, for PostgreSQL:

http://www.pgsql.com/support.html

Of course, expecting good results on any business-critical system without a backup & recovery plan or competent staff is a bit naive, anyway.

Most of the other open source products...???? Um, MySQL is not Open Source. Cheap: yes, Open Source: no

Strange you should say that, I seem to recall that you can download the source here

The latest version is not GPL, but the source is open for you to read and submit patches if you wish.

Hi!

Just for your information:

MySQL doesn't support SELECT INTO ... as this is not ANSI SQL. MySQL support however the ANSI SQL way to do the same thing: INSERT INTO ... SELECT.

If the character column is defined as binary all LIKE operation on it will be case sensitive. In SQL, the speed overhead of doing a case sensitive compare is neglectable compared to fetching the data from the database. You can easily verify this by doing a LIKE search with and without BINARY.

To force -- to be followed by a white space was done to remove a lot of possible problems. As many SQL databases doesn't even support -- this isn't normally a big problem :)

As you may have noted on the MySQL crash-me page, most SQL databases are incompatible with eachother. If you are worried about incompatibles, you should never use Oracle (Oracle is big enough to have it own SQL standard).

MySQL is not free for commercial use.

What hasn't yet been widely discussed is something that I would have expected the Slashdot community to care deeply about: the license. It's not open source. It's not even close. It's neither free as in beer, nor as in speech. Why does mySQL get a pass on this?

It seems like Dude's biggest gripe is that MySQL is claiming to be an RDBMS when it's not, but I've never found the documentation on the MySQL web site to be particularly misleading and always thought that they are quite open about the areas that MySQL is lacking.

I think other people have found the MySQL side of the story less than completely frank. A big reason for this is their infamous benchmark collection, where they compare apples and oranges. (To drag this metaphor out a bit, they also seem to suggest that since their apples are sweeter than most oranges that it's okay that they are a poor source of vitamin C since most foods are a poor source of vitamin C...)

There is a very good reason to use mysql: Great Tech Support

One day I had a problem with an ODBC issue and Lotus Approach. I zipped the logs and sent it to the tech support. The next day I had a DLL that solved the problem.

I agree that the lack of transaction support is a great weakness with MySQL, but there are several other things that are important for programming convenience.

If you want to create and maintain databases, you'll certainly appreciate a few things that I think are annoying with MySQL (for a more complete list, I suggest having a look at their own compatibility documentation):

• Can't use SELECT INTO (Oracle SQL extension IIRC, also supported by Sybase) for faster table creation
• Can't use SELECT within SELECT, or sub-selects (as the article mentions)
• Case insensitive string matching without the possibility of choosing case sensitive
• '--' is only a comment if followed by whitespace
• Lack of stored procedures and triggers

The first one is the one I've found most annoying. It's a very nice thing to have when putting stuff in temporary tables. I recall a different version of their document, where the gist of their argument against SELECT INTO basically was that it wasn't standard SQL. Okay, fine.

The second is also a bugger, and that

in many cases you can rewrite the query without a sub select

doesn't really help in those cases you can't.

Case sensitive string matching is faster than case insensitive string matching, so I'm actually surprised that they didn't make that the constraint instead of the opposite. I'd also like to be able to do this without storing stuff as "BINARY".

As an argument against '--' style comments without requiring a following space, the MySQL team claims that

Because 1--1 is legal in SQL, we think it is terrible that `--' means start comment.

The reason that is a problem is of course that one can say something like !payment! and have the value inserted instead of that (meaning: 1-!payment! will become 1--1 if !payment! evaluates to -1). This could be solved if comments were interpreted before other things, like they probably should.

As for stored procedures and triggers, I just find them practical.

I'm satisfied with one thing, at least, and that's the tone of the documentation, which has been improved a bit since the last time I looked. It's slightly less arrogant.

Yet I won't even consider recommending the use of MySQL to anyone who wants a database for any serious work, for the reasons mentioned in the article, the reasons mentioned above, and the incompatibilities not mentioned here, but in their own documentation.

Sure, I'm perfectly aware that you shouldnt trust a benchmark you havent rigged yourself, but it does prove that there is at least one test in which PostgreSQL loses.

1. Have you used any of the Open Source databases like MySQL or Postgres enough to recommend one of them for a light-usage site?

   Or perhaps none of the Open Source databases are yet ready for production use?

   Phillip:

   I talk about this a bit in http://photo.net/wtr/aolserver/introduction-2.html .

   The bottom line is that for people who care about data integrity, concurrency, and 24x7 redundant operation, there really is not an adequate substitute for commercial RDBMes (even the commercial object database companies haven't been able to make any headway against the heavy-duty RDBMS systems).

The implication here is ominous to me, in that he seems to be implying that someone interested in building a community oriented site shouldn't even bother trying to use an Open Source RDBMS.

I know that what he says is true about MySQL (for lack of transaction support, and the fact that it isn't truly Free) and PostgreSQL isn't yet 100% SQL2-compliant, but is it possible that

1. a. One of these could be adapted to be more fitting for these types of applications? or,

2. b. Borland/Inprise Interbase 6.0 could be appropriate?

It is fully GPL'ed, fully SQL2-compliant, and very fully-featured compared to the alternatives. It is somewhat slower than PostgreSQL (and much slower than MySQL, of course) with more simultaneous connections, but if we could garner enough support from the Open Source community to build a decent threading mechanism, I think it could easily beat PostgreSQL in the long run. It woulde be incredibly useful to have an Open Source alternative to Oracle.

There are other features that would probably take some time to hack together to make Interbase 6.0 competitive with Oracle, but if everybody just takes Phillip's advice (no offense, Phillip, I agree with 95% of what you say) instead of helping to develop alternative tools, we will never have an option at all.

Just my two cents.

Get exportsql.txt from the MySQL contributed downloads page. Paste it as a module in the Access database and run it. It will make a SQL "script" which you can run in mySQL to recreate the data exactly. I did something similar for a client some time ago and it worked quite well.

As a plug for a favorite product, I'd suggest pointing the designers toward Visio2000 for database design, rather than doing it directly in Access. Visio2000 lets you visually draw out the entities and relationships. Then, with a few clicks, it will actually create the structure you've drawn on any ODBC-compliant database (Access97, SQL Server, MySQL, whatever). It's VERY usefull for complex database design.

check out what the MySQL folx say about this book here

There are multiple problems with the above statement. MySQL is neither free as in speech nor as in beer. Read their license for yourself. I have never understood why in a community where the nuances of a license are so critically important, mySQL seems to get a free pass.

The other problem is that mySQL does not have transaction support, and so calling it a database at all is a stretch at best. mySQL is a filesystem that uses SQL as a query language. Certainly this is useful, but comparing it to relational databases is dishonest. Of course mySQL is fast: It makes no attempt to ensure transaction semantics.

One could understand the community's attitude towards mySQL if no truly open source alternative existed, but PostgreSQL is both open source, and a "real" database.

I gave up dB work a year or two ago. Maybe it was starting with MS Access and enjoying its compact, RAD, toy quality. Unscaleable and inapproriate for Enterprise stuff of course (and making no pretence to such garlands), but fast, small (well, the .mds were small if you compacted them) and fun.

The problem was the inevitable upgrade lead me to MS SQL Server 6.5 (I was stuck on NT at the time). SQL Server had a lot of stuff I was missing in Access - triggers, stored procedures, scaleability - but it also brought a lot of frustrations. The domain aggregate functions were poorer than those offered by Access, which was a pain as I was trying to roll my own OLAP before it all got proprietarized into a Babel of different buyouts and skill-subsets. Its big-iron feel didn't stop it having a ludicrous 255 byte limit on varchar fields. For bigger you had to futz about wastefully amalgamating BLOBs of text with READTEXT/WRITETEXT. Plus it was grotesquely high-maintenance. I didn't want to become a DBA. I just wanted to hack SQL. And wasteful. The 'devices' ('Honey, I bloated the database') were huge and couldn't be shrunk, no matter how svelte the actual data.

By the time I escaped the NT shop I was naively looking to Oracle to save me from these frustrations. Unfortunately, I'll never know the joys or horrors of that particular 'platform', because at 600 Mb for the Linux installation I just bailed out and cried 'Enough of this grotesquely bloated crap!' and pursued XML or BerkeleyDB solutions to anything remoteley persistence-flavoured thereafter. I knew that fast and small were synonyms, but the vendors were growing fat on the antonym line and there was nothing I could do about it. Even MySQL and PostgreSQL were part of the problem. They're all emacs. None of them are vi.

Recently I ran my periodic, wishful, wistful Google-grep for 'fast', 'small' and 'rdbms' and found myself, after rejecting Brian Jepson's TinySQL as ridiculously small and cute but strictly pedagogical, finally discovering The One.

Hypersonic SQL, a tiny Open Source Java database weighing in at less than 100K, supports correlated subqueries, transactions, referential integrity, indexes, stored procedures and JDBC - everything basically, but GROUP BY, cursors and triggers. I never used cursors myself. I'd rather iterate in Perl. The other two, admittedly are fondly missed, but not life-threatening. It doesn't support failover. But with such a small, developer-friendly codebase anything's possible.

Did I say 'Perl'? 'But it's a Java database', I hear you cry. How can this beast talk Perl? Well, it can't, which is why I'm working on a Perl DBI interface to it talking to a native driver over TCP/IP. If anyone wants to contribute (I wouldn't need to hack it if some brave soul wants to polish up the languishing JNI module in JPL to support embedding Java in Perl on Linux (currently it only works for Win32)), please get in touch. I'm almost certainly way out of my depth and entering a world of pain.

Oh, did I mention? It's 7 times faster than SQL Server.

And how you may ask -- oh illustrious poster -- do you suppose it was done?

I (the programmer) created a middle layer of code that forced each part of what we would all call an SQL "transaction" into atomic data units, with pass/fails on each step. Alot of extra code yes. A pain to code, yes. But the bottom line is that the code which didn't require this level of commit/rollback ability also didn't have the speed penalty associated with the extra code.

We all agree that if I want transactions, I will have to pay the speed penalty somewhere, either on the database, or in the middleware, and that it is going to be more efficient (when needed) if it's just in the database. However, if I don't need it, then what? By way of example, I wouldn't expect a web site based on PHP/MySQL and transaction logic to be any faster than a PHP/SQL Server web site where the transaction logic is in the database, in fact I would expect it to be slower. But for every non-transactional SQL operation, I would expect PHP/MySQL to have a benchmarkable and reproducable speed advantage, and according to other RDBMS reviewers (one link here), this in fact has proved to be true, against even Oracle on the same machine.

Take a look at the to do list... Transactions are the number two item, after replication.

-Andy

What about setting up something similar to mysql? You could then enter all of this info into a database, and access/update it using AvantGo for the Palm or CE device.

-mark

For case 1, let's assume complete a complete linux front to back solution, with as much free (or mostly free) software as possible:

Needed Software Components:

1. Favourite Distro of Linux
2. MySQL or Postgres Database (personal pref is for MySQL... not going to get into the pros and cons here...)
3. Dynaminc Web-Scripting Language (PHP, Perl, whatever... personal pref for this kind of thing is PHP... again, I'm not debating at the moment...)
4. Linux Vitrual Server Project - very solid load-balancing from my experience. Don't know how it compares with the appliances on the market... but it's still solid.
5. HA/Redundancy software (Linux HA project isn't quite there... but they're getting close... there are some commercial packages available - one that's free for non-profit use - http://www.high-availability.com

Hardware:

NB: For maximum up-time I recommend systems with redundant hardware (backup power supplies, dual NICs, and RAID arrays)

1. Firewall/Load-balancer - preferably using HA/Redundancy software on two machines... Mirrored (RAID 1, right?) boot/system hot-plug drives are a good idea.
2. Web-farm - up to X systems (where X+1 breaks your budget... ;) ) load balanced with Virtual Server Project. For a reasonably heavy duty method of doing this relatively cheaply, see Cubix and their "density" series... up to 8 servers in a single box... with hot plug everything. RAID isn't as necessary here... as the systems themselves are effective your RAID...
3. Database system - again preferably an HA/Redundancy cluster for maximum availability. I recommend a mirrored boot/system disk again, with a RAID 5 array (or RAID 5+5 - mirrored RAID arrays) for speed and maximum availability... highest RPM drives you can afford can help here a lot for speed, too.
4. 100 BaseT Switch for maximum through-put. Personal preference is for Cisco but your budget dollars may vary.
5. I've mentioned RAID a couple of times... you can get SCSI and IDE raid these days (SCSI being more common)... the cheapest/fastest one I've see is from Raidzone - very nice, check them out (up to 15 - 40 GIG hot-plug IDE drives in one array, with a very high through-put). You can also do software RAID, taking a performance hit, but saving coin...

Case 2 assumes that you don't mind using some commercial stuff... and have a bigger budget:

1. Replace Virtual Server with an appliance. (Alteon, F5 and Cisco all make good products... presently my preference is with F5's BigIP.
2. Replace in born Linux firewall with Checkpoint's firewall-1 running under linux - or an appliance firewall, a Cisco PIX is very nice, and has very high though-put. The Nokia appliance running Checkpoint and a BSD bastardisation is quite nice.

Before I forget.

• PHP link
• MySQL link

Another important feature is that these techniques are server side so are independent of the browser used. You can also use PHP with Oracle 8i but most of the really cool stuff is centered around MySQL. Plus, why spend money on software if you don't have to. Spend the money save paying for support or donating to the OpenSource projects (PHP and MySQL). Yet another advantage is that the application can be developed and tested on Windows (read Configuring Windows98 For Local Dev) then uploaded to the Apache Server. A good free program with PHP syntax highlighting and tons of other goodies for developing is HTML-Kit.

Warning: MySQL Connection Failed: Too many connections in php_header.php3 on line 10 Warning: MySQL Connection Failed: Too many connections in /web/sites/www.thebench.org/htdocs/index.php3 on line 81
Warning: MySQL: A link to the server could not be established in /web/sites/www.thebench.org/htdocs/index.php3 on line 81
Warning: MySQL Connection Failed: Too many connections in /web/sites/www.thebench.org/htdocs/index.php3 on line 88
Warning: MySQL: A link to the server could not be established in /web/sites/www.thebench.org/htdocs/index.php3 on line 88
Warning: MySQL Connection Failed: Too many connections in /web/sites/www.thebench.org/htdocs/index.php3 on line 93
Warning: MySQL: A link to the server could not be established in /web/sites/www.thebench.org/htdocs/index.php3 on line 93

With persistent connections enabled these limits don't seem too difficult to reach. Ugh.

javier

Ugh, another degrading experience, answering an AC article.

According to the company history:

Prolog Development Center (PDC) was founded in 1984 with the development of a Prolog compiler - later to be known as Turbo Prolog, PDC Prolog and now Visual Prolog as its main activity. Since then PDC has established itself as a world leader in the development of Prolog and related products.

Today, PDC consists of an R&D and a consultancy division. The R&D Division is concerned with the development of the Visual Prolog compiler together with new methodologies and development tools.

Borland might be an evidence against the common contention that "Microsoft is the company that never produces anything, but merely buys out products from other companies that are creative," as many of Borland's products were not natively produced, but rather resold on behalf of other componies.

By the way, that was Ashton Tate that used to own the dBase trademark...

As for integration with DBM variants, I see little importance to that. InterBase is a relational database (or at least, as relational as they come), as opposed to merely being a data store. The value would be in sharing code between InterBase and PostgreSQL or MySQL, or maybe using InterBase as a "data store" for persistent data in KDE or GNOME.

Russia used this militarily to destroy both French and German armies; they performed strategic retreats when "outgunned," destroying crops and other infrastructure so that when the Russian winter set in, opponents were overextended, and despite "winning the battle," wound up losing the war.

This obviously came at the cost of considerable Russian destruction, and with Inprise, the cost is that of not getting revenues from license sales, whilst the immediate benefit is that this may injure sales of competing DB vendors.

The open question is of how this affects already-free DBs like MySQL and PostgreSQL.

Effects on them are severalfold, and some are dependent on what license Inprise comes up with:

• Regardless of the license, it should be useful to have source code access as this can allow folks to see an implementation of transaction locking, stored procedures, SQL-CLI, ODBC, as well as the data storage mechanis, which may be quite useful when they try to add such functionality to other DBMS systems even if there is no reuse of code.
• If the license is sufficiently compatible, it may prove possible to integrate code one way or another either into "OpenInterBase" or into one of the other DBMSes.

• Altera
• SOLID SQL Server
• OpenIngres
• Mimer
• Faircom
• Raima
• Yard
• Empress

I have built a few E-Commerce sites using Linux with the following

Minivend shopping cart and SSL Webserver I use Mysql with DBI you can use an ODBC connector from windoze to the Mysql Server. You will find a converter filter for acces at www.mysql.com that will move your mdb file into the SQL server. Using it this way still allows Windoze apps to access the data and run reports as if it was still an mdb file.

Mysql.org and Mysql.com are the USA mirror of Mysql.net the german site. So if .com or .org is down, try .net. I cannot remember their other domain off the top of my head.
Also, as their site says: Our connection to the world has been down between Saturday 19991030 GMT 22.32 to Monday 19991101 GMT 17.10
---

I don't think there is anything out there to plug into Access from Linux, but you can certainly export from Access into MySQL.
Either use MyODBC as someone pointed out or you can use a script available here to dump an Access DB into nicely formatted MySQL compatible SQL.
There are a couple of these utilities available on the MySQL web site.

MySQL may be okay for really simple applications (like a guestbook) but it is lacking features that a robust dB application needs. There is no support for Foreign Keys nor does it look like there will be any in the future. Foreign keys are needed to ensure data integrity and they just aren't there. You basically get a lot of the same problems as in weakly types languages with really no gain (unlike the gain you get from weakly typed languages). There are no stor ed procedures or triggers. While I'll agree that SP code can be done in the dB client (the ASP), you have to make sure you call trigger code before every operation and check the result and it really isn't the same as having actual triggers.

However, these two things are kind of forgivable, but mySql doesn't have support for transactions! So if an operation fails in the middle, you can't roll it back. They claim they will have something like transactions without rollback soon. While that helps with some concurrancy issues, the roll backs are really needed when entering data into a normalized dB.

Maybe, you can use MySQL for the back end to EJBs for object persistance. The EJBs ould give you the data integrity and the event model that you would use for triggers (you'd trigger code in the EJB anyway since it is suppsed to encasuplate business logic), but I'm not sure if you can do EJBs on a dB without tranactions.

So, if you are going to have a novel web technology, I would suggest against using MySQL, although if you Web application is novel because of content or something and you want to save some money, it might be okay to forget about the robustness at first.

MySQL may be okay for really simple applications (like a guestbook) but it is lacking features that a robust dB application needs. There is no support for Foreign Keys nor does it look like there will be any in the future. Foreign keys are needed to ensure data integrity and they just aren't there. You basically get a lot of the same problems as in weakly types languages with really no gain (unlike the gain you get from weakly typed languages). There are no stor ed procedures or triggers. While I'll agree that SP code can be done in the dB client (the ASP), you have to make sure you call trigger code before every operation and check the result and it really isn't the same as having actual triggers.

However, these two things are kind of forgivable, but mySql doesn't have support for transactions! So if an operation fails in the middle, you can't roll it back. They claim they will have something like transactions without rollback soon. While that helps with some concurrancy issues, the roll backs are really needed when entering data into a normalized dB.

Maybe, you can use MySQL for the back end to EJBs for object persistance. The EJBs ould give you the data integrity and the event model that you would use for triggers (you'd trigger code in the EJB anyway since it is suppsed to encasuplate business logic), but I'm not sure if you can do EJBs on a dB without tranactions.

So, if you are going to have a novel web technology, I would suggest against using MySQL, although if you Web application is novel because of content or something and you want to save some money, it might be okay to forget about the robustness at first.

MySQL may be okay for really simple applications (like a guestbook) but it is lacking features that a robust dB application needs. There is no support for Foreign Keys nor does it look like there will be any in the future. Foreign keys are needed to ensure data integrity and they just aren't there. You basically get a lot of the same problems as in weakly types languages with really no gain (unlike the gain you get from weakly typed languages). There are no stor ed procedures or triggers. While I'll agree that SP code can be done in the dB client (the ASP), you have to make sure you call trigger code before every operation and check the result and it really isn't the same as having actual triggers.

However, these two things are kind of forgivable, but mySql doesn't have support for transactions! So if an operation fails in the middle, you can't roll it back. They claim they will have something like transactions without rollback soon. While that helps with some concurrancy issues, the roll backs are really needed when entering data into a normalized dB.

Maybe, you can use MySQL for the back end to EJBs for object persistance. The EJBs ould give you the data integrity and the event model that you would use for triggers (you'd trigger code in the EJB anyway since it is suppsed to encasuplate business logic), but I'm not sure if you can do EJBs on a dB without tranactions.

So, if you are going to have a novel web technology, I would suggest against using MySQL, although if you Web application is novel because of content or something and you want to save some money, it might be okay to forget about the robustness at first.

Best of luck to ya'.

//Johan

Well, MySQL never said they would "never" do transaction support. In section F3 of the current documentation, "Some things we don't have any plans to do":

Transactions with rollback (we mainly do SELECTs, and because we don't do transactions, we can be much quicker on everything else). We will support some kind of atomic operations on multiple tables, though. Currently atomic operations can be done with LOCK TABLES/UNLOCK TABLES but we will make this more automatic in the future.

On the other hand, at the O'Reilly open source conference David Axmark told me they eventually plan to support the complete ANSI92 SQL spec. So in this case 'not now' may not be 'never.'

Beating on a drum I have worn out doing so, not everyone understands what database people mean when we say transactions. PHBs and product specifiers, particularly. It really just means support of the SQL standard COMMIT/ROLLBACK feature. Operationally, this means a lot of heavy hauling for the database; just ask an Oracle engineer about how they do it (as I did once).

If you don't really really need "transactions," it's worth thinking carefully about how to turn off the feature in the DBMS you're using (some do allow that), or use one like MySQL that doesn't.

MySQL's developers made a deliberate choice here; it optimizes the program for some applications and rules it out for others.

--------

We are currently using Solaris as a server platform, with Apache or phttpd as a webserver and we are using Oracle and MySQL as databases. We are running PHP as scriping engine as CGI version and as Apache module in some instances.

You seem to come from a mostly Microsoft background, which is not where PHP is at home. PHP3 does not run as an IIS module, as far as I know, but only as a CGI version. This will make it perform much worse that for example CF or ASP on IIS, due to the abysmal performance of the NT platform and IIS as a CGI host - NT just doesn't fork. PHP4 will be running as an IIS module, but is in beta now and I would not build any production code on it - yet. As soon as PHP4 proves to be stable under load, it should outperform PHP3 by a factor of 5-10, though, plus the speed gain coming from being able to use it as a module on IIS.

On a Unix system (Linux, Solaris, doesn't matter) with Apache, PHP3 performs excellently as a module and can take any reasonable amount of load, provided you have enough RAM. We already know this from the Mindcraft benchmark - Apache must not swap and you must tune your MaxClients to match your RAM size to avoid performance degradation under high load. Many sites are parsing all pages, including their regular HTML, through PHP3 for convenience and the performance overhead is neglegible - if Apache can take it, Apache and mod_php can usually take it as well.

PHP excels in portability, support and in connectivity when benchmarked against CF. PHP will run on any old server platform and will talk to almost anything, and natively, where CF will most probably talk through an ODBC adapter. PHP includes some 10+ native database interfaces, including all major database vendors, and does LDAP, SNMP, SMTP, NNTP, IMAP4, POP3, some OODB and fulltext database protocols, can generate pictures on the fly, can generate PDF on the fly and so on. Writing extensions for PHP is trivial, if you can do reasonable PHP programming.

One point must not be left out of the equation when talking about PHP, and that is the online support. There are many large PHP mailing lists, including THE PHP3 mailing list, which are extremely friendly and efficient and usually generate correct and useful answers within 15 minutes. Also, the annotated online manual is a unique ressource for help, because it is learning and growing, incorporating user annotations. I have nowhere experienced anything that comes close to this kind in support, commerical or not.

My recommendation: PHP on IIS on Windows works, but will most likely not perform as exspected. It is nice for testing, but I won't go productive in this configuration. PHP on Apache on Windows works better, but will still not use PHP to it's fullest advantage. Also, you will make installation and maintentance unnecessarily difficult for you. PHP on Apache on any Unix will perform extremely satisfactorily, generate only minimal TCO, and is supported excellently. If you have at least minimal Unix knowhow inhouse, I suggest that you go for the full plunge in a test installation instead of an incremental migratory approach, because this way you will maximize the advantages of PHP and your server platform.

Re the migration from version 3 to version 4: PHP4 and PHP3 are drop-in compatible. There is no need to "port" from 3 to 4, because both languages are virtually identical. The differences are extremely minimal and well documented, also the development team is working on closing these final gaps between versions. Changes between version 3 and 4 are completly internal, switching from a fully interpreted system to a byte-code compiler/interpreter hybrid for speed reasons. Also, some language features have been added in an upward compatible and transparent way. We have tested the beta and found it to be living up to its promises in speed _and_ compatibility. Waiting for PHP4 won't pay: You can use PHP3 to learn just now and all this knowledge as well as your code will be valid and valueable on PHP4.

If you'll be using the CGI version of PHP3, please be sure that you

• set up a chroot() running environment for your CGI (phttpd does this by default, Apache does this with a modified suexec - ask me if you need it).
• compile a version of PHP with --enable-force-cgi-redirect or you'll be opening a great security hole.

If you have any further questions, please subscribe to the php3@lists.php.net mailing list or have a look at the PHP Knowledge base. These are great ressources.

• Web Server: FreeBSD 3.2-STABLE with Apache 1.3.9 / PHP 3.0.9 on a PII-400 w/128 Meg RAM, IBM 4.55G U2W Drive. Due to FreeBSD's proven track record for Web/Network performance, stability, and security (e.g. Yahoo, wcarchive, and others), it's a natural.
• SQL Server: Linux 2.2.x with MySQL 3.22.25 on a PII-400 w/256 Meg RAM, IBM 4.55G U2W System Drive and a Mylex AcceleRAID 250 w/4 IBM 4.55G U2W Drives in a RAID-5 configuration. Linux was the obvious choice when considering MySQL performance and driver availability wrt RAID controllers.

• Apache: Ensure you have adequate spare servers to handle the connections (StartServers, MaxSpareServers, MaxClients, and MaxRequestsPerClient in the config); nothing sucks more than clients not being able to connect. Also, if you are using embedded script of some sort (PHP, Perl, etc.), use modules compiled into Apache (mod_perl, etc.); this should significantly increase speed and decrease the overhead of reloading the module for each access.
• MySQL: Tweak the applicable setting as appropriate. We increased (usually doubled in most cases) the following: Join Buffer, Key Buffer, Max Connections, Max Join Size, Max Sort Length, Sort Buffer, and Sort Buffer). If possible, depending on the amount of data, get as much memory in the system as possible. If the OS can maintain frequently used data cached, disk access won't be required which significantly increases the speed of queries, etc. In addition, get rid of that pre-compiled MySQL and compile it yourself. If possible, optimize using egcs/pgcc for your platform. Also, compile mysqld statically; this will increase it's memory overhead a bit but can increase it's speed by 5 - 10% by not using shared libraries.
• Storage: For optimum speed, use SCSI (of course). For our data, we require RAID 5 for redundancy. If that is not required, RAID 0 (striping) can be used for increased speed. The optimal way is to use hardware RAID (external RAID or RAID controller). Luckily, Linux has drivers for quite a few different RAID controllers that are available for a reasonable price.
• Linux: Beware of Redhat's security problems, disable all unnecessary services, et. al. Seek out security-oriented and Linux performance-tuning sites for more suggestions.
• General: Don't skimp on hardware. A cheap component, be it a drive, network card, motherboard, or whatever, if it fails, will cause unrecoverable downtime. We decided on Intel NL440BX boards (serial console/BIOS support is nice), PII-400's, and IBM SCSI drives in both boxes. If one box were to have a catastrophic failure, the other is able to perform both webserver and SQL server functions if necessary. We can also simply replace a failed component with one pulled from a similarly-configured non-production (test) box, or just swap boxes altogether.

However, what are the real comparison numbers? Are we talking 3-4% on 1 million rows?

If PostgreSQL is only slightly slower that MySQL, I would consider switching.

A.

--
Adam Sherman

I have done as much research as Google could point me to and as far as i can see MySQL screams but it is not feature rich. Here is the summary of an afternoon (last week) of research (Disclaimer: I still have to do benchmarks myself but this is a start.)
MySQL is fast but it lacks some important stuff that we decided we couldn't live with out.

• Doesn't support inner selects - I tried to convince my self that I could live without these but today while doing some data cleaning i was using them all over the place.
• no support for transactions, they suggest table locking but when you are updating several tables at once locking all of them maynot be the best thing to do. They are going to implement some sort of transaction process but rollbacks won't be incorporated.

Postgres on the other hand was developed in a very academic enviroment and is extremly feature rich. The major draw back to postgres I could see was that it doesn't scale very well and i am not sure if 6.5 addressed any of these issues. That being said if the db won't scale then it really doesn't matter how many features you have.

They guys who developed MySQL have a really cool and usfull tool on there web site that runs a script and lets you know exactly (It is VERY detailed) what features each db has.

I strongly suggest anyone insterested in benchmarking features of dbs check this out. db compare

We decided that we would use postgres until we do our own benchmarks. It may scale enough for what we need, the load shouldn't be too high for our use.

more info would be great, also the timeline for MySQL to incorporate inner selects would also be handy cause i know they havbe it at the tops of the features they are going to implement.

OK, here's the scoop. I *DO* servlet programming for a living. We've pulled together webmacro, IBM WebSphere AppServer and MySQL on Linux. But if I had a choice, I wouldn't be using IBM WebSphere. We got lucky, and didn't have to pay for it. It's mostly fast, and does everything we need it to - for now. But it's got it's flaws, and it's a resource pig - 30Meg of Memory to run two servlets? And It's the only app I've seen to Max out CPU Utilization on Linux. Hell, it's the only thing I've seen *KILL* apache deader than a doornail. We're still looking at the OpenSource Servers because they look smaller, faster, and just as feature rich. Please Tell me which one you choose, and why. I want to take it to my boss as an alternative.

I think you can figure it out, just remove the +nospam+ :). I'll get you in touch with him if neccisary.

isamchk

Read all about it...

(Sorry if this is a duplicate)