Slashdot Mirror

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

MySQL can use different "storage engines" for different tables. One of their older (and arguably, more widely deployed) engines in MyISAM, which does table-level locking (their newer engine, InnoDB, does row-level locking). From the MySQL Manual (sec 7.3.1):

Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

and from Section 7.3.2:

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

This is one of the many problems with MySQL. I agree with you that Oracle (and PostgreSQL) are much better designed.

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

MySQL can use different "storage engines" for different tables. One of their older (and arguably, more widely deployed) engines in MyISAM, which does table-level locking (their newer engine, InnoDB, does row-level locking). From the MySQL Manual (sec 7.3.1):

Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

and from Section 7.3.2:

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

This is one of the many problems with MySQL. I agree with you that Oracle (and PostgreSQL) are much better designed.

If you obtain something under the GPL, you retain the right to redistribute it under the GPL.

You are allowed to charge a reasonable fee for your time spend in copying it and for the medium upon which is is distributed.

If I want to sell people some commercial software, or to give them some closed-source software, and provide them with MySQL as the accompanying database engine under the GPL, source and all (for MySQL) why the hell should I have to pay a single penny at all to MySQL?

Service contracts is a major way. You sited a web host I'm sure icdsoft.com has a service contract with someone to fix problems(unless they are a reseller of hosting). Mid-Large Businesses and governement agencies all need service contracts for help or they won't use a software package for the most part. Plus if you look at mysql.com you'll notice they also over consulting and training.

If you're asking "what legal reason is there?", then the answer is because they decided in their infinite wisdom to GPL the client libraries, which is a more restrictive policy than any of the commercial DBs impose as far as I know.

If you meant "why on Earth would they do that?", then I have no answer. They had to invent a stupid "FOSS License Exception" (see the above link for details) to allow popular non-GPL projects like PHP to offer MySQL support, and have basically removed any chance of commercial software support.

In a nutshell, if you want to use a database in your non-GPL project (whether Free or proprietary) then MySQL is a poor choice. They've already added huge client library restrictions by moving from the LGPL to the GPL, and I don't see any reason to believe that they won't drop the "FOSS License Exception" kludge in the future. Note that I like the GPL - it's a good license and I support its goals - but this seems like a wholly inappropriate place to use it.

Open source & MySQL will rise, legal foes will fall...

I thought MySQL IS open source!

*shudders*

I belive that the general concensus is that MySQL is one of the least efficent databases today.

And as I discovered this morning, Google uses it. I clicked on a link in an email message sent to me, and...

ERROR OCCURED
Description: Error connecting to MySQL server: #1045 - Access denied for user: 'root@localhost' (Using password: YES)
Module: /mailer/link.php
Time occured: Sat, 15 Jan 2005 07:42:27 -0800

So they use it for Gmail, but I don't know offhand if MySQL is what they're using for the index. To their credit, they are using PHP rather than Perl.

This is not all that uncommon. I suspect they are trying to do something similar to mysql's commerical license. Entities can buy the commerial license which allows them create apps that interact with mysql without having to release their app's source.

That being said, I don't think they should list open source as a feature until the source is available.

BSPlayer for movies, its fast and fairly lightweight. theres free, and non-free versions on the BSPlayer site

SmartFTP - n00b friendly FTP client - free for home/non-commercial use

pretty obvious what the following 3 are:
Apache
MySQL
PHP

PostCast SMTP server

CrapCleaner! cleans crap from your PC! temp files, MRUs, deletes invalid registry stuff. Whenever I use it, it usually free's up ~1GB on my hard drive! quite amazing how much crap there is on XP

Firefox and Thunderbird

--Mike-- wz

--Mike-- qo

MySQL Control Center [mysql.com] is a step in that direction (client side) if they implement some more features on server side M$ centric customers need, it could get Microsoft into trouble in the future (some years) kg

How about Oracle asking for MySQL to remove their stats from the benchmark table [mysql.com]

"Note that Oracle is not included because they asked to be removed. All Oracle benchmarks have to be passed by Oracle! We believe that makes Oracle benchmarks very biased because the above benchmarks are supposed to show what a standard installation can do for a single client." qp

http://dev.mysql.com/doc/mysql/en/CREATE_TRIGGER.h tml

WTF are you talking about?

Yahoo uses mysql for an application for which they loose thousands of dollars a minute if the DB goes down?

That has to be one of the worst cases of sentence mangling I've seen in a long time. I was arguing that companies, with a 50 BILLION dollar market cap run mysql, they are doing just fine. If you think they are stupid for it, fine, but I don't see you doing any better.

Anyway, no I don't work for yahoo, but I can read:

http://www.mysql.com/news-and-events/success-stori es/yahoo_finance.html

One guru could support a small company on a database. One guy would not be able to provide enterprise level support for one database.
Perhaps your definition of enterprise is "a 8 person law firm". Mine, OTOH, is something like a $500M+ public company that could lose thousands of dollars a minute on downtime.
If that's my thousands of dollars a minute, I certainly wouldn't be stupid enough to only hire one person, much less some wanna be support company.

Instead, I would hire a company with:

a plan ( http://www.mysql.com/company/ ) or
a proven track record ( http://www.oracle.com/customers/partners/index.htm l )
or, at the very least, one that my friends recommend.

who keep telling me, that they want their commercial applications re-written with an open source database backend like mysql. I never have the heart to tell them that unless they are actually are releasing the source code for their apps, that they need to purchase a commercial licence

While I think it's quite harsh to dub mySQL a "so-called 'database'", sprocs are an integral part to a 3-tiered webapp.
MySQL is an extrememly robust platform, and while the version 4 feature-set isn't up to par with other offerings, the 5.0 release is coming along very well and is making due on some very nice promises, namely sprocs and updatable views.
MySQL is about the fastest dbms out, which isn't always a good thing if it trades atomity for speed, which hasn't been an issue since 4.0 was released.
5.0 roadmap

The stable version does NOT have subqueries. Version 4.1 does, but that is still beta quality software.

That not up-to-date. 4.1 isn't considered beta since 2004-06-24 and had it's first production release over a month ago, on 2004-10-23. See here

The transaction support in 4.0 is horrible and slow. Rollbacks are O(n) operations (for InnoDB, that is) etc...

Slow rollbacks are not really a problem, if you don't abuse them. That is, don't write your transactions with failure as an expectation (like inserting a row and waiting if it fails due to a unique key), but do your own checks (most often you don't need additional queries, because you already have the data or can expand an existing query). Not only does that make your queries faster with most databases (even those with good rollback support), but also makes your application more robust (in case of db changes)

Edd Dumbill has a list of (mostly) free issue tracking systems. It's a very good starting point.

One of the most interesting systems in that list is (IMHO) Eventum, by the company that produces the commercial version of MySQL. It only needs PHP and MySQL, and should therefore run well on Windows. Be advised that I haven't actually tried it though.

OPTIMIZE TABLE `tablename`

or you can look here

http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.h tml

Our software is 100% GPL (General Public License)

Here is what I found: MySQL Connector/ODBC 2.50, which is according to this page is LGPL.

As I said though, I am not a MySQL guru, so I am not sure if this is the only LGPL MySQL connectivity library or there are others. That said though, they can't GPL the protocol (or can they?), so technically, it should be possible for third-party developers to write their own libraries and license them however they want...which, of course, may potentially be as disruptive as migrating to another DBMS altogether.

Now, if the GPL were altered so that you have to provide source to people who access a GPLed web application, then it would seem to me that LAMP is dead.

Of course, it's might not be a bad idea to migrate the app from MySQL to PostgreSQL anyway :-)

No argument there. :) Still, this would seem to be hugely disruptive.

It is fully transactional when using InnoDB storage.

You can choose the storage method (InnoDB, BDB) per table, so you can choose the ultimate performance (BDB) in cases where you don't need transactions (like for lookup-only tables).

Backups require very little effort. Simply read lock the table and copy the table file, or you can use a live backup command.

Most reports of lacking features or non-enterprise-readiness are simply a lack of education on the part of the speaker. A quick trip to the MySQL.com site will resolve those questions.

Now I personally like PostgreSQL, but MySQL is indeed good, and enterprise-ready. Don't take my word for it... http://www.mysql.com/it-resources/case-studies/

The Computer Associates Trusted Open Source License (CATOSL) apparently qualifies as an "open source" license, but it would probably not qualify as a "free software" license. The focus on user's software freedom found in the free software movement is important in interpreting what powers the license grants and what the license claims to regulate.

Section 10.1 tries to control use of the program--if one's rights under the license terminates, the license claims that that user's rights to use the program terminate as well. But the FSF tells us that US copyright law doesn't permit setting conditions on merely running a computer program (outside of a license or encryption manager) and that if this were to become accepted, would extend copyright law in a dangerous way. This was part of the rationale for saying the first and second revisions of the Apple Public Source License were not free software licenses.

Section 11.4 of the CATOSL claims that no licensee will bring a legal action under the license more than once a year. When one does bring a legal action, one is supposed to waive a jury trial and hold the trial in the state of New York. Licensees in other districts may enjoy rights which the state of New York does not recognize or grant, including the right to bring suit more than once a year; rights licensees would want to retain should they need to go to court.

I'm sure a more thorough examination of the CATOSL would reveal more problems for users. I don't recommend getting involved with programs licensed under the CATOSL. This shouldn't pose a practical problem for anyone because there are excellent database programs under more amenable licenses, including PostgreSQL (licensed under the new BSD license) and MySQL (licensed under the GNU GPL). I also don't recommend licensing one's own programs under the CATOSL.

Amen Brother. MySql is blazingly fast when used correctly as are most RDBMS'. I am sure these people disagree though: MySql Customers

You're right.

• Schemas
• Views
• Rules
• Check constraints
• Domains
• Triggers
• Custom datatypes
• Stored procedures in no less than five different programming languages
• Geometric/spatial datatypes and indexes
• Real type safety
• Errors when the alternative is data corruption
• No silent failures
• Enforcing a difference between NOT NULL and DEFAULT directives
• Consistent foreign key enforcement (Target table must also be InnoDB, ON UPDATE does not allow recursive updates on the same table, out of range violations, brain dead implementation of NO ACTION)
• Better transition to Oracle (closer feature parity)
• Substandard transaction support (The time required to roll back a transaction increases in proportion with the number of operations performed during that transaction; Any non-InnoDB tables referenced in the transaction will not rollback)

And how about bugs like this (http://bugs.mysql.com/bug.php?id=5670) where creating an index destroys the table. Nice.

ROLLUP in MySQL

Although I understand your post was meant to be funny, I feel an urge to point out that mysql can very well be used in enterprise environments. Me, I administer a mysql database of over 2TB, with one table holding 3.5 billion records, growing some 20 million every day. The thing is still blazingly fast.

And look at the mysql home page. There are some really large companies out there using mysql.

Support is excellent too, and available in all sorts. From the do-it-yourself lookups via google to payed support that will have experts log in to your machine and fix any problem you have.

I say mysql is great. A friend of mine always says "know your database". What he means is: look at the problem you have and select a database for it. For us, that definitly is mysql.

Close, but not quite.

Well, HSQLDB is embedded, but only inside a java application

True, but that's why I said I'd need to know more about his application.

It persists data in a flat file that it loads on start.

Not quite. HSQLDB uses an SQL script of the transactions to recreate the database. However, this script is significantly reduced in size if you use CACHED tables.

If you've got more than 1000 records

Again, depends on the data and the application. 1000 records is usually not a whole lot to cache in memory. And with CACHED table types, you could easily go up to millions of records without stressing the user's machine.

you might want to go with something more robust, like MS Access.

That's what he was trying to get away from.

Berkeley DB isn't a "database" in the sense most people think.

It's not an "SQL" database, but it is a database. (Technically, even a filesystem is a type of database, but we won't go there.) Again, it depends on his application. He gave no requirement that the database support SQL, so I went with the most natural assumption: It doesn't need it.

It's not relational, and there's no SQL (there may be a SQL driver, but it'd be totally inefficient.

That's an amusing statement, because MySQL does exactly that. :-)

There is, of course, no tool that meets the needs of every possible scenario. Instead, there are tools that meet the needs of common scenarios. Most of what MySQL is being (improperly) used for could be easily replaced with PostgreSQL. Postgres offer similar performance, but takes a minor hit for data integrity. As a bonus, you gain sub-selects, stored procedures, SQL-92 compliance, and other features of a "real" SQL database.

There is, of course, no tool that meets the needs of every possible scenario. Instead, there are tools that meet the needs of common scenarios. Most of what MySQL is being (improperly) used for could be easily replaced with PostgreSQL. Postgres offer similar performance, but takes a minor hit for data integrity. As a bonus, you gain sub-selects, stored procedures, SQL-92 compliance, and other features of a "real" SQL database.

The non-standard SQL isn't too bad

MySQL's SQL is quite standard, and while there are differences, they are either the lack of a small number of features (like sub-selects, which you go into below) or very baroque trade-offs, which most users will not encounter.

I suspect you're instead running into assumptions about what is and is not "standard" based on what you've used in the past. MySQL follows the ANSI SQL standard as closely as all of the other databases I've used, having its own small quirks and LOTS of extensions, but basically doing as good a job of standards compliance as, say, gcc does with the ANSI C standard.

I understand your frustration, though. I'm an old Sybase user, and I miss the Sybase extensions in many places.

No subselects [...] That is huge

It is?! Why? All a sub-select does is instantiate a temporary table on the fly. You can, of course, instantiate a temporary table on the fly, so what is a sub-select buying you?

The only possible advantage would be the possibility of lazily evaluating a very large subselect, but I've come across that need in perhaps 2 or 3 situations in the course of 12 years of working with databases in production environments... how is this "huge"?

In addition, the version that I used didn't support views.

You're showing your lack of depth here. By definition a system that lacks sub-selects lacks views (as views are just named sub-selects).

All that said, sub-selects are being added to MySQL, but people who pay for work on the core server don't ask for these features as much as certain others, and that's how the folks at MySQL decide what to put effort into.

The non-standard SQL isn't too bad

MySQL's SQL is quite standard, and while there are differences, they are either the lack of a small number of features (like sub-selects, which you go into below) or very baroque trade-offs, which most users will not encounter.

I suspect you're instead running into assumptions about what is and is not "standard" based on what you've used in the past. MySQL follows the ANSI SQL standard as closely as all of the other databases I've used, having its own small quirks and LOTS of extensions, but basically doing as good a job of standards compliance as, say, gcc does with the ANSI C standard.

I understand your frustration, though. I'm an old Sybase user, and I miss the Sybase extensions in many places.

No subselects [...] That is huge

It is?! Why? All a sub-select does is instantiate a temporary table on the fly. You can, of course, instantiate a temporary table on the fly, so what is a sub-select buying you?

The only possible advantage would be the possibility of lazily evaluating a very large subselect, but I've come across that need in perhaps 2 or 3 situations in the course of 12 years of working with databases in production environments... how is this "huge"?

In addition, the version that I used didn't support views.

You're showing your lack of depth here. By definition a system that lacks sub-selects lacks views (as views are just named sub-selects).

All that said, sub-selects are being added to MySQL, but people who pay for work on the core server don't ask for these features as much as certain others, and that's how the folks at MySQL decide what to put effort into.

1. How to setup replication with different architectures including master/slave, master/slave/slave (another level), replication rings, dual master replication with slaves and more.

Simple, just read a few pages on the MySQL documentation.

2. RAID (software vs. hardware), RAID types, IDE vs. SCSI. I know. I'm sure you've thought of these things already but most people will find something new here because there is a lot on it. It is quite dense.

What on earth does this have to do with MySQL specifically? And furthermore, if you dont know about _RAID_, and need to be told the benefits of SCSI/Raid, do you really think you need MySQL clustering ? Any PC/Server with enough ram will push queries like a maniac. Also, check #4...

3. Selection of an OS. Seriously. It actually compares threading models on OSes compared to performance with MySQL. This is great stuff.

Great, now that i've bought this book, i'm supposed to _CHANGE_ OS in my server farm, just to gain a few more queries/sec ?

4. Selection of a filesystem on an OS. Yes, nitty gritty. Includes talk about journaling vs non-journaling benefits and warnings.

Filesystems? Oboy, anyone who knows about SQL performance knows that if you access _DISK_ during execution, you are screwed anyway.

5. Load balancing mySQL. How to do this through a load balancer. Why and how it's different than load balancing web servers.

This seems like a decent chapter.

6. How to backup live using replication.

DOH! We are talking backup here, right? Backup means archiving a copy of the database. MySQL allows you to use the magic command "cp" (or scp, or rsync, or..) Best # so far ...

7. I love this: Detailed information on how the replication system actually works. I love understanding the inner workings because then I know how/why things went wrong.

The updated node sends commands to the other machines to update as well. If something goes wrong, you could rollback the transaction.

8. The benefits of myISAM/InnoDB table types including how they store/retrieve data, the features available, etc.

This one could be interesting as well.

I dont mean to troll here, but most of those #s are either offtopic or irrelevant. To create a high-performance *SQL database, just have enough ram, ie, _NO_ disc access except for sporadic logging etc. Furthermore, it's title is "High Performance MySQL", so what we are interested in are what bottlenecks we might run into with MySQL specifically, not stuff like disc/raid. Tell me about some of the quirks with indexing, tell me how to tune threads, how to determine good from bad queries, how to monitor performance.

If you need clustering for some reason, just create a low latency backbone for master/slave connections. It's easy to configure, just read the docs.

For a moment there, I thought you meant that Yahoo was processing company financial data with MySQL. Then I realized that they use it just for displaying market data and the like.

Give me a heart attack, why don't you?!

Why not? Yahoo finance has been using MySQL for the last four years.

"Yahoo! uses the MySQL database to power many of the services on Yahoo! Finance (finance.yahoo.com), a popular web site that provides the full spectrum of financial services and information. MySQL is behind Yahoo! Finance's high-traffic area of news headlines, which is aggregated from over 100 sources worldwide, as well as its stock charts, insider trading, SEC filings, conference calls and earnings reports, among others. "

"Prior to its switch to MySQL in 2000, Yahoo! Finance managed its database needs through homegrown flat files and Berkeley DB databases. But those solutions proved to be inflexible and not scalable enough for their needs, with the volume of data nearly doubling every few months. "Our needs vary considerably, and that is one reason that MySQL works well with us. It handles the high-volume, 260 million record tables just as well as the small, low-volume ones. One of our servers handled over a quarter of a billion queries in a month-and-a-half, and it still has capacity to spare.""

"Since Yahoo! Finance started using MySQL, the database's popularity has grown within the company, and many of Yahoo's other world-wide properties are now using MySQL too. "We have used MySQL far more than anyone expected," stated Zawodny. "We went from experimental to mission-critical in a couple of months. Once others saw it, they jumped on board.""
---

I wonder why many people here hate MySQL even when it has been proven to be a very competitive database. Is it because MySQL is a foreign product?

http://dev.mysql.com/tech-resources/articles/4.1/s erver-type.png/ http://dev.mysql.com/tech-resources/articles/4.1/d atabase-usage.png/ http://dev.mysql.com/tech-resources/articles/4.1/s ecurity-settings.png/

Why can't we linux folk have a nice, sexy installer like that? Or do we already? Don't get me wrong, but sometimes, I'm just too lazy to read instructions and at times like that a GUI installer could be the ideal solution...

http://dev.mysql.com/tech-resources/articles/4.1/s erver-type.png/ http://dev.mysql.com/tech-resources/articles/4.1/d atabase-usage.png/ http://dev.mysql.com/tech-resources/articles/4.1/s ecurity-settings.png/

Why can't we linux folk have a nice, sexy installer like that? Or do we already? Don't get me wrong, but sometimes, I'm just too lazy to read instructions and at times like that a GUI installer could be the ideal solution...

http://dev.mysql.com/tech-resources/articles/4.1/s erver-type.png/ http://dev.mysql.com/tech-resources/articles/4.1/d atabase-usage.png/ http://dev.mysql.com/tech-resources/articles/4.1/s ecurity-settings.png/

Why can't we linux folk have a nice, sexy installer like that? Or do we already? Don't get me wrong, but sometimes, I'm just too lazy to read instructions and at times like that a GUI installer could be the ideal solution...

If you distribute a proprietary application in any way, and you are not licensing and distributing your source code under GPL, you need to purchase a commercial license of MySQL

http://www.mysql.com/company/legal/licensing/

Forgive me if I have trouble trusting a company that changes licenses for the simple reason to raise revenue.

Slashdotters, this is a very important discussion (the one on software patents), but let's start with accurate facts.

The 283 thing is old news and was just repeated by Mr Florian Müller (who is NOT a MySQL developer). See here:

http://news.com.com/Group:+Linux+potentially+infri nges+283+patents/2100-7344_3-5291403.html

I quote from that article:

- - -

Linux potentially infringes 283 patents, including 27 held by Microsoft but none that have been validated by court judgments, according to a group that sells insurance to protect those using or selling Linux against intellectual-property litigation.

Dan Ravicher, founder and executive director of the Public Patent Foundation, conducted the analysis for Open Source Risk Management. OSRM is like an insurance company, selling legal protection against Linux copyright-infringement claims. It plans to expand the program to patent protections.

- - -

So it seems that an important discussion has got onto the wrong track due to incorrect input information.

But let us discuss software patents! MySQL's official position can be found here:
http://www.mysql.com/company/legal/patents.html

Marten Mickos, CEO, MySQL AB

microsoft are funding MySQL Server now?

those guys over at http://www.mysql.com/ will be so pleased!

bad news for the http://www.postgres.org/ guys though.

;-)

Sybase ASE: same original codebase as MSSQL Server
Oracle: most well-known database engine in the world
MySql: Most popular Open-Source database in the world
PostGres: Not my cup of tea, but its an option
DB2: IBM's DB2... SuSE and RedHat only

I always shudder at the thought of running a database engine on Windows...

from the MySQL documentation... http://dev.mysql.com/doc/mysql/en/Subqueries.html "Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific."

I suppose that depends on your tests. How much attention should we pay to the databse test if the review says:
We expect to see a performance increase with HyperThreading - SQL servers must thread well.

Yet the sql-bench manual says:
Note that this benchmark is single-threaded .

Take a decent benchmark, run it with 10 instances simultaneously and then we'll see if it really helps for heavy multithreaded loads. (That doesn't really belong in a desktop test, but does compiling?)

I have been using MySQL's eventum http://dev.mysql.com/downloads/other/eventum/featu res.html/ for the past few months and have found it very good. It's PHP MySQL based. It supports multiple projects, email integration, supports public/private fields, custom categories, custom fields, project management (time tracking), issue listing, sorting, searching, reporting and graphical stats. It also supports SOAP (remote posting) and RSS for viewing