Google Releases MySQL Enhancements

An anonymous reader noted that "Google has released its internally developed enhancements to MySQL to the open source community this week. Changes include improvements in replication, high availability configuration, and performance." It'll be interesting to see if the changes they made are of interest to other places using MySQL.

Comments

so...

[shinygerbil]

All Hail Google?

Re:so...

[bigman2003]

No, all hail MySQL.

I'm a recent convert from SQL Server, and I love MySQL now.

The fact that I can download a copy and get it running in about 20 minutes is the best part. The fact that the admin is pretty damn good, and easy, is the part that keeps me using it.

Maybe there IS something to this whole open-source thing.

Re:so...

[Matje]

I wouldn't be so sure: the mysql administrator is not always able to restore a backup correctly. you are well advised to restore backups through the command line. it is also impossible (at least last time I checked) to automatically include all databases in a scheduled backup. You can only select the databases that are present at the time you schedule the backup.

furthermore, the mysql query browser screws up when you paste a query containing tabs. this particular bug was reported to mysql over a year ago. it was somewhat fixed, but not really.

I really like mysql but claiming that the admin is better than sql server is well, pushing it.

Re:so...

[bill_mcgonigle]

All Hail Google?

No. Remember - Google uses open source but never gives anything back.

Re:so...

[bigman2003]

I don't want to get all niggly...

But I didn't claim that MySQL admin was better than SQL Server admin- I just said it was damn good.

I have had much better success with backup and restore on MySQL than I have had on SQL Server. I find the interface and functionality much easier to understand and much more obvious. The damn DTS in SQL Server is a black hole to me...admittedly I had a database replicate in the wrong direction about 4 years ago and I am still gun-shy when using it. Yes, it was my fault, but MySQL lays things out a little more clearly.

Re:so...

[cdwiegand]

I call shill. I used Sql Server 2K and 2K5 at my last job, and I would LOVE to have SQL Server Studio for MySql - the admin tools SUCK. The ones that use the native dll crash, and the ones that use .Net bindinds (i.e. Toad from Quest) have bugs of their own, and crash every once in awhile too. Also, none of them hold a candle to the usability of SQL Server Studio - executing part of a script, and wanting to cut and paste the result to show what I got (standard practice here) is difficult in MySql's tools (even third party ones), but SQL Server makes it easy.

Re:so...

[Architect_sasyr]

Whilst the link escapes me there is a set of admin tools for MySQL that are almost identical to the 2k5 ones for MSSQL. Truth be told it doesn't look as flash, but they are still really nice to use. The one installed on my laptop (hence not being able to remember the name) allows for highlight execution of part of script a-la the SQL Studio and cut and pastes fine, I'm sure there is more than one tool out there that can do this!

I use both MS SQL and MySQL in corporate environments for various purposes and have to say that I like both for different reasons. MySQL is "damned good" in its own right, but there are some ANSI features available in MSSQL I really have trouble living without (for example: DELETE FROM tblData WHERE fldValue = '3' OUTPUT INTO tblBackup). My thought would be to see what Google have to offer, if it helps me, then great, if it doesn't, it's great for the community, and in a few years time when MySQL has all the features I require I can finally throw out the MS SQL servers.

My $0.02 AU

Re:so...

[Anonymous Coward]

It's always easier to lay things out when you have less there.

Re:so...

[Anonymous Coward]

OOG loves open source.
You did not see reason open source good.
MySQL made you suspect that open source maybe good.
OOG loves MySQL.
OOG loves Google.

Re:so...

[Mr. Underbridge]

I wouldn't be so sure: the mysql administrator is not always able to restore a backup correctly. you are well advised to restore backups through the command line. it is also impossible (at least last time I checked) to automatically include all databases in a scheduled backup. You can only select the databases that are present at the time you schedule the backup.

I'd echo the "command line" solution - any admin who couldn't whip up a script in 10 minutes to backup all databases that exist when the script is *run* isn't worth much.

Re:so...

[gfxguy]

Gee, thanks. What about us jack-of-all-trade schmoes who only use the basics and aren't running company wide databases? The amount of data I use the database for is actually quite small, relatively speaking. I run (among all my other jobs) our internal webserver, which means I need to write all the code, including the presentation part, and administer the database.

Sorry, we can't all be gurus, but I still need to back up my tables every so often.

Re:so...

[misleb]

furthermore, the mysql query browser screws up when you paste a query containing tabs. this particular bug was reported to mysql over a year ago. it was somewhat fixed, but not really.

I've also had some serious problems with the ODBC client for Windows. There was one bug when accessing views on a MySQL > 5.0.10 server through ODBC that, as far as I know, has yet to be fixed. It has been open for over a year. And it is pretty serious. Basically you can't query a view from MS Access (didn't try from other programs). I had to get a user supplied patch, figure out how to recreate their ancient VC6 build environment, and recompile the ODBC client.

But I guess I should be grateful that I could recompile with a user supplied patch. There's commercial software that I use right now that leaves me helpless until the company gets around to releasing a binary patch. At least with open source there is some chance that I can get in there and fix a problem myself (i've done it).

-matthew

Re:so...

[J0nne]

it is also impossible (at least last time I checked) to automatically include all databases in a scheduled backup. You can only select the databases that are present at the time you schedule the backup.

http://sourceforge.net/projects/automysqlbackup/
Sure, it's a shell script, but it does what you want (in the list of databases, just type 'all').

Re:so...

[Krazy Nemesis]

Yeah, but why use a cannon to kill a mouse?

Re:so...

[hey!]

Well, that's saying something, because from my point of view SQL Serer's admin tools suck.

I mean honestly, a database that's supposed to be for the enterprise, but it lacks even rudimentary log analysis tools?

Also, way too much of what go's on in Enterprise Manager is undocumented. Oh, the way to do it in the GUI is documented, but under the covers it is impledmented with undocumented Transact SQL. The reason this matters is that making changes on production databases through a GUI gives me the willies. I'd rather write a script I can test, then know exactly what is done, not be at the mercy of a slip of the mouse. If your mouse does slip, you may never know why something happened, due to the above mentioned lack of log tools.

I'm not anti-GUI. GUI has its place, for example in prototyping. But when getting it right matters (or when finding out what went wrong matters), you want to be able to do everything in a script. It's nice to have a GUI, but its essential to have well documented scriptable operations for everything you might want to do.

I think it is interesting to compare MySQL to SQL Server, because they have analogous natural niches. SQL Server is a good choice as a simple backing store to an application created in Visual Studio. MySQL plays a similar role in the LAMP stack.

SQL Server is not a good choice for situations where many different applications use the data, especially if the way they use the data requires sophisticated relational functionality. It isn't that SQL Server is quirky, although it is. It isn't that SQL Server's treatment of advanced queries is flaky, although it is. It isn't even that SQL Server only gives you the crudest of physical DBA options, althogh it does.

The killer management deficit of SQL SErver is the inability to detect problems created by rogue programs, diagnose, and correct them. With a more capable databases, it is often quite easy to undo the damage of a rogue program without resorting to backups. This includes undoing arbitrary deletions and modificatoins of important data.

Everything in a relational database should be undoable in strict choronlogical order to any point in the past you specify. Any sequence of operations should be undoable even if other transactions are interleaved with them, provided no inconsistency results. I have personally extracted all the transactions done by certain people, found out various ways they were screwing up the data, and with a touch of my magic wand made it as if they had always done the right thing.

This is career saving/making stuff we are talking about here. SQL Server flunks. I don't know about MySQL.

There are aftermarket log analyzers for SQL Server. They are a must-have if you run SQL Server. But even so, they are not easy to use. The log file structure in SQL server is not structured to support the kinds of operations DBAs need to do. This may be in an effort to eke out a bit more transaction speed: updates to databases aren't just in the database files; the full scope of the change must be reflected by changes to the log file before and after the data file per se is changed. Making the log lest robust could create a significant improvement in transaction throughput.

Re:so...

[Matje]

[blatant selfpromotion] if you're looking for something similar (but with much less functionality) written as a dos batch script, try this http://www.jijenik.com/projects/mysqlbackup/. It will even email you an compressed and encrypted copy of the backup file. [/blatant selfpromotion]

Re:so...

[chrispycreeme]

Because it's a hell of a lot more fun than using a mouse trap and the mouse is just as dead.

Re:so...

[tempestdata]

Well said, not everyone can be the guru of everything. This macho geek attitude of 'If you cant do it in this super efficient, optimal way, you have no business doing it' is very detrimental to the OSS community. I am a software engineer, but I do basic admin stuff too, there are admins in my company who are obviously better than me.. but that doesn't mean that I cant take care of some of the duties too. I dont have to be supremely competent.. just competent enough.

Re:so...

[pvera]

I use both SQL Server 2000/2005 and MySQL. There is no way in hell that you can compare the out-of-the-box admin tools that ship with MySQL with the Enterprise Manager+Query Analyzer in SQL Server 2000 and the SQL Server Management Studio in 2005. This is why most people use phpmyadmin, Aqua Data Studio or an equivalent third party tool instead of the CLI interface.

Also, installing SQL Server 2000 and/or 2005 is pretty effortless. Just run the installer and the basic defaults will do for most people. When you say "The fact that I can download a copy and get it running in about 20 minutes is the best part." you are implying that you can't install SQL Server in 20 minutes. Sure, you can't download it because it isn't free, but installing it takes just a few minutes.

That said, I am a fan of MySQL. It is a great product and the price is right. Even paying for commercial support is affordable. SQL Server is awesome, but I don't agree with the pricing for the developer and standard editions. SQL Server 2005 developer should be free, and SQL Server Standard should cost no more than $300.

There's another thing I really like about MySQL is that all of the database objects for one database are not clumped together into one file like SQL Server does (SQL Server allows you to spread out one database across many files, but MySQL saves each object within the database as one or more files). This has not caused me to lose data (yet) in the past 7 years, but it did create some hairy situations.

Re:so...

[bigman2003]

Sorry, I was not referring to the CLI interface at all. The only thing I have used that for is to import some databases from a PHPMyAdmin dump.

I'm using the GUI tools from the MySQL site:

http://mysql.org/downloads/gui-tools/5.0.html

I just assumed that most people- especially those familiar with SQL Server would be using the GUI.

Re:so...

[Krazy Nemesis]

Yeah, but the setup time and learning curve is atrocious, and you run the risk of shooting your own foot off.

Re:so...

[kz77]

Your admin is easy you say?? Lucky you!

Re:so...

[operagost]

I like to kill mice with LUDICROUS GIBS mode on.

Re:so...

[jazzkat]

BigMan, If you're coming from SQL Server, you'd be better off downloading and using Postgres 8.2.4 for windows, from here. PG is BSD licensed, which means you can bundle it with your commercial .NET-based apps for free.

The management interface for PG is on-par with SQL Server Studio; I use both on a daily basis. It's also "20 minutes to set up and start populating data". As an added plus, Postgres has all of the "standard" syntax and referential data integrity turned on out of the box.

You use MySQL if: a) you're developing a LAMP app for an inexpensive webhost that only allows MySQL databases, or b) all of your developers cut their teeth on MySQL and therefore productivity will drop if you ask them to use standard compliant syntax, or c) You're using an app (like SugarCRM or WordPress), the developers of which insisted on using funky MySQL-only features (instead of standard portable syntax) and therefore it's too much work to port to a standard syntax.

In all other cases, you use Postgres or some other commercial database. Postgres scales much better than InnoDB on any combination of a) larger numbers of read-write transactions, b) larger numbers of connections, c) more processors, d) larger datasets (including and beyond 400-500GB).

Cheers, -J

Re:so...

[Anonymous Coward]

It's pretty easy to backup mysql databases. Here's a simple shell script (yes, I know it's a bad idea to store the password like that):


#!/bin/sh

for DB in db1 db2 db3 db4
do
        mysqldump -h localhost -u backups -pThePassword $DB | bzip2 -9 >/mnt/backups/db/$DB-$(date +%Y-%m-%d).sql.bz2
done


Someone who's doing admin work should be able to pull off simple stuff like that. If not, they need to do some reading.

Re:so...

[Mr. Underbridge]

Gee, thanks. What about us jack-of-all-trade schmoes who only use the basics and aren't running company wide databases? The amount of data I use the database for is actually quite small, relatively speaking. I run (among all my other jobs) our internal webserver, which means I need to write all the code, including the presentation part, and administer the database. Sorry, we can't all be gurus, but I still need to back up my tables every so often.

Then you don't need to schedule the backup to dynamically determine what databases exist and when to back them up, and you can run a one-line backup script from a mysql cheat sheet. Couldn't possibly get less "guru" than that.

I'd assume the point was implicit, but I'll make it explicit - if your application needs to do powerful things for which the admin tools are insufficient, then your databases should have a trained IT person in charge of them, and that IT person should know what he's doing. The inability to write a script to determine what databases exist and then back them up would indicate that said IT person doesn't know what he's doing.

Re:so...

[Anonymous Coward]

time apt-get -y install mysql-server

20 minutes?

Re:so...

[xappax]

I dont have to be supremely competent.. just competent enough.

I agree in general, because I'm in the same situation, being "Administrator of Computer Stuff". But come on, folks. Being an expert in mysql is one thing, but understanding how to write a shell script or use a simple command-line utility like mysqldump is pretty basic stuff that even "master of none" types like me are comfortable with.

There is a lot of reflexive elitism and egotism among techies, but if someone doesn't have a foundation of basic "linux literacy", telling them not to meddle with important systems is often warranted. I think people who consider themselves computer-savvy can sometimes get defensive when their knowledge about a particular topic is shown to be lacking because they feel it's a reflection on their overall "geek cred". So they point out all the other technologies they're competent in as though that somehow mitigates their lack of knowledge in this one. This, too, is a kind of "macho geek attitude", in that people will refuse to admit that they're n00bs and need to spend a lot of time learning before they can use a new technology.

When I come up against something I don't know how to do well, I spend my energy trying to learn how to do it better, not vehemently asserting why I don't need to.

Re:so...

[Anonymous Coward]

Check again. mysqldump -A dumps all databases.

Re:so...

[jaseuk]

hehe. That appears to work but you've forgotten the -Q switch or whatever it is. Good luck restoring your backups, some will work, some will not at all and will depend on the content of your database.

Jason.

Re:so...

[adamchou]

a) google is an inexpensive webhost b) google most only have MySQL developers c) who woulda thought the google search engine was developed from WordPress? you're right! no wonder google uses mysql

Re:so...

[Anonymous Coward]

-Q is enabled by default smartypants.

Re:so...

[RobNich]

echo("mysqldump can already do that");

mysqldump --all-databases

Not sure how long we've had it, but it's been a while. I'm on 5.0.27.

Re:so...

[Gnight]

A serious question: What about replication and clustering? Does PostgreSQL have support for replication and clustering that is on par or better than MySQL?

Disclaimer: This is not meant to be trollish in any way.

Re:so...

[killjoe]

When something goes wrong with mysql or postgres you get a real error message which tells you exactly what went wrong. You type the error message into google and you get the answer.

When something goes wrong in SQL server you get a cryptic error message which doesn't say anything such as "multi step process caused an error". You type that into google and you get a thousand people who posted the same question and maybe twenty possible answers "which worked for me". You then spend the rest of the month trying each and every one of those solutions till you either fix the problem or you work around it.

SQL server is one of the hardest databases to work with no matter what the admin interface is like.

Oh and 2005 admin interface is a step back in usability and functionality.

Re:so...

[killjoe]

Why can't you copy the data before you delete it?

Re:so...

[jazzkat]

Yes, there are a few options.

Slony-I is a BSD licensed Master-Slave system; its successor, Slony-II, is still in development.

Various outfits like EnterpriseDB and Greenplum offer commercial Postgres derivatives with multi-master replication and clustering.

There are also open-source, less-popular options like PGPool and PGCluster.

Cheers,
-J

Re:so...

[Architect_sasyr]

You can, but the problem occurs when you copy all the data then only delete half of it due to a power outage/system failure or some realignment of the moons of Jupiter. In this case the clause is there to do two things: Clean up your SQL code (one transaction instead of two) and to ensure integrity of your data deletion. There are other standards I look for, but this was a good example.

Re:so...

[Gnight]

Sweet. Thanks for the quick reply buddy. I'll check those out to make a decision to how they stack up against MySQL's offerings.

Re:so...

[cheater512]

It must have been there in MySQL 3.

Re:so...

[Anonymous Coward]

If it helps, I just found out that one major cell carrier uses Postgres as the back-end to their subscriber authentication process. 20 million customer records, used for authentication when people power up their phone.

The actual database is from a company called Times Ten, who was just purchased by Oracle. They add some components on, but the SQL engine is Postgres.

  [censored]

Re:so...

[prog-guru]

You should try sqlite, for me these days it's all postgres if I need real database features, or sqlite if it's something simple. sqlite is even bundled with current version of python, php, and it's easy to set up in rails.

Re:so...

[Erik Noren]

What part of fun don't you understand?

Re:so...

[killjoe]

That's what transactions are for. What if the power goes out in the middle of your one statement? It's not like the server is doing everything in one step you know.

Re:so...

[Architect_sasyr]

At the risk of whipping a dead horse (namely coz we both have our own opinions ;) and not having any statistics on me at the current time, transactions are (I believe) more overhead than the OUTPUT INTO statement. And there is more than just that keeping me on SQL Server (row_number() for example). MySQL is great, and its good to see supporters of it (I'm one of them!) but its not about the (Open Source) tool for me or my company, it's about using the right tool for the job, and SQL Server is the right tool for some of our jobs, as MySQL is for others.

I recall the name of the SQL management tools I mentioned in a previous post. SQL Server Management Studio for MySQL, it's put out by a company called EMS.

Re:so...

[fferreres]

... Or, you use it if you are Google, Yahoo or the US Census Bureau.

Re:so...

[cdwiegand]

And I wrote scripts for SQL Server, and executed them, either in whole or in part, and was able to cut and paste the results (in text format) back into the script so that I could show what was returned for each part. I have yet to find a tool for MySql that makes that happen easily. Also, only Toad seems to like having Editor (or script) files - it's actually the client I usually use, but it too has stability problems (and suckage problems in the 2.0 series, the 3.0 beta is actually more stable, and more useful, than 2.0 production). Also, the fact that MySql can't seem to run EXPLAIN PLAN on a sql statement that includes create table, even if it's from a select, is annoying. SQL Server 2K is able to do that for me...

Re:so...

[cdwiegand]

I would love a tool better than either the admin tools that come from MySql, or Toad, or EMS SQL Manager for MySql. None of those holds a candle to Sql Server studio, sadly. I've almost broken down and started writing my own, twice, because of my withdrawl from Usable DB Tools.

Re:so...

[Vulcann]

If you want to move off SQL Server, might as well go to Sybase ASE. They both share the same history (MS-SQL Server was in fact Sybase SQL Server) and also share more or less the same Transact-SQL syntax. When porting apps becomes a huge pain in the ass, this can be a significant advantage.

Re:so...

[LizardKing]

Sybase ASE may have similarities to SQL Server that make porting a little easier, but the two do differ quite a bit now that Micorsoft don't have to maintain compatability (a period of compatability at the TSQL level was specified in the deal MS struck with Sybase to get the source code). Worse still, Sybase ASE is looking increasingly like a dead end. Version 15 is so unreliable that I don't know anyone who has successfully migrated from 12.5 and Sybase seem to be more interested in "compact" databases for small devices. Overall, Sybase ASE doesn't seem to have evolved much since the early 1990s, and is looking increasingly archaic.

Re:so...

[killjoe]

SQL server has implicit transactions. SQL server does not magically move the records for you after a delete. SQL server starts a transaction, moves your records and then deletes them after it's done.

Really it's not that much different then doing it yourself.

Anyway if you want to avoid transactions for speed use mysql.

I use sql server every day. I have used it for the last five years at my job every day. I hate the living shit out of it. I wish I could personally choke all the developers on the SQL server team. I would rather use anything else but sql server even mysql. I keep a instance of postgres running so I can do the things that I can't in sql server.

Honestly I can't understand why anybody would choose SQL server over oracle or postgres or db/2 (well other then "we are a windows shop and we only use MS products")

Great!

[glwtta]

Did they fix that thing where it always sacrifices data integrity for speed?

(I'm not even trolling, I do want to know if they fixed that)

Re:Great!

[Bigby]

You can go with a slower speed and higher data integrity by switching from MyISAM to InnoDB tables. The choice is there, so I would use the term "always sacrifice".

Re:Great!

[Anonymous Coward]

so I would use the term "always sacrifice".

you would, would you.

Re:Great!

[Anonymous Coward]

They did that in 5.0 with strict mode.

http://dev.mysql.com/doc/refman/5.0/en/server-sql- mode.html

Re:Great!

[Anonymous Coward]

you would, would you.

He probably could care less. ;)

Re:Great!

[xelah]

He doesn't mean 'foreign keys actually work', or 'inserting nonsense dates gives an error' or anything of that kind. He means things like:
    - The database doesn't corrupt tables. Ever.
    - If the power fails or the kernel goes away at an arbitrary instant, then when the database starts up again all of the data will be there, with committed transactions entirely present and uncommitted ones entirely gone.

Secondly, it's not justified to just assume that MySQL will be faster even with it's limits on data integrity. It depends on your workload. Consider differences in locking strategy and query plans, for example. There's a benchmark showing scaling behaviour in one particular set of circumstances here: http://tweakers.net/reviews/674/6 ; this shows a fairly striking difference in scaling with load on a specific machine.

Re:Great!

[Matt Perry]

You can go with a slower speed and higher data integrity by switching from MyISAM to InnoDB tables. The choice is there, so I would use the term "always sacrifice".

According to the docs MyISAM is the default table format. So how about "sacrifice by default." Sure, you can change the default to InnoDB but why isn't it set to InnoDB already? I find it strange that you have to enable data integrity features in a RDBMS.

Re:Great!

[rnicey]

Because then they'd have to admit that they're a fancy SQL parser, and most of the real meat is contributed by a company called Oracle (ahem Innobase). That said, InnoDB is a real nice data engine.

Re:Great!

[Simap]

Did they fix that thing where it always sacrifices data integrity for speed?

You can use InnoDB tables with a write-through battery-backed cache and the performance is very similar, yet 100% ACID safe. Dell 2950s come with this standard (not a plug, just something i noticed)

Re:Great!

[operagost]

You can use InnoDB tables with a write-through battery-backed cache

White-through cache doesn't need a battery. Write-back does.

Re:Great!

[ajs]

Sure, you can change the default to InnoDB but why isn't it set to InnoDB already?

Why sacrifice performance by default for transactional integrity when most database applications require only statement atomicity?

My spam token DB, photo album manager and Wiki back-end simply don't have the ACID requirements of a bank, and I DO NOT want to pay the performance penalty for some theoretical need that I might have in the future. I'll happily lock when I need to, and track completion of complex actions. If I need true transactional integrity, I'll turn it on in a new table that I only use for that purpose, and take the performance hit there.

Re:Great!

[jbellis]

"MyISAM is faster than InnoDB" is certainly the conventional wisdom. It's also wrong.

Re:Great!

[encoderer]

..You can mod me troll, but can you actually dispute what I'm saying? ..I didn't think so.

Re:Great!

[Simap]

Yep, thanks for correcting me there. I was half asleep and without coffee :-) It is write-back cache that enables a massive speed boost when using InnoDB and the fully ACID settings. (parent probably knows this, but im putting the rest of this message here for anyone else reading the thread)
Write back-cache is possible without a battery, however having a working battery makes the write-back cache safe; when power is lost in the middle of a write, the cache keeps this data until the computer regains power, then it is written to disk. It is possible but ill-advised to have a write-back cache and no battery :-) If you do that you shouldn't bother with InnoDB's ACID safe settings. According to the manual, some drives and controllers have write-back cache (and no battery) which is not configurable, so be careful putting your fancy ACID applications on cheap SATA controllers/drives.

My test setup is a Dell 2950 2G ram RAID 1 SAS drives. The controller has a battery and 256m of cache. I had to turn write-back on, as it is configured to write-through by default. I did several unplug tests while I had 50 threads updating different records in the table on one machine while unplugging the DB server. After each unplug test the number of successfully committed transactions matched the data when the machine came back up. Switching between write-back (fast) and write-through (slow) had a very significant impact on the number of write transactions per second.

Disabling the syncs altogether boosted the performance several fold, MySQL was maxing all 4 CPUs and was not bottlenecked on I/O at all. If you can afford to lose 1 second of transactions with these fly-by-the-seat-of-your-pants settings then go for it. Stuff like web forums, content sites, etc could probably survive. Places where the worst case is that someone has to repost is not going to kill you. Online stores, etc should not go that route, as losing a second of transactions could leave you with a lot of billed and not shipped customers! Some people will tell you that with these settings you will have inconsistent state, but they are full of it. You will have state that is transactionally consistent, you just might have a slightly older version of it as the last second of transactions may have been lost. Uncommited transactions are rolled back. InnoDB crash recovery still works fine.

I have not found any significant read performance difference between innodb and myisam on identical tables.

Re:Great!

[LizardKing]

The tests referred to in your link focused on reads, whereas writes with MyISAM are considerably faster than InnoDB. The most sensible applications for MySQL that use the MyISAM engine are those where there are a high number of writes as well as reads, and the reads involve few if any joins. MySQL has a lousy query optimiser which means that joins have a pretty high performance cost, but for simple queries the short time spent optimising is a benefit.

Fit for duty?

[Anonymous Coward]

Those Googlians are stupid, don't they read slashdot? If they did they'd know MySQL isn't fit for ANYTHING! Sheesh.

Re:Fit for duty?

[alienmole]

Yes, but imagine the world's biggest Beowulf cluster of MySQL servers.

Now imagine them in Google's data centers.

Which, in fact, is where they are. Now do you see?

Re:Fit for duty?

[Frosty Piss]

But how can this be? According to the Slashdot Zingiest, MySQL isn't a "real" DB server. Only rubes use MySQL, real DB admins insist on PostgresSQL... So according to the Slashdoters with the low ID numbers, than, Google is just a bunch of rubes!

Re:Fit for duty?

[alienmole]

What are they teaching in troll school these days? If Google were running PostgreSQL, they wouldn't need such huge clusters, and then they wouldn't be as cool!

Re:Fit for duty?

[Anonymous Coward]

> Yes, but imagine the world's biggest Beowulf cluster of MySQL servers.
> Now imagine them in Google's data centers.
> Which, in fact, is where they are. Now do you see?

done imagining? great, next embrace reality - google isn't using mysql for your searches - they're using it for smaller, mostly internal business functionality.

Re:Fit for duty?

[alienmole]

Imagine a Beowulf cluster of pedants... Now imagine them posting on Slashdot...

Re:Fit for duty?

[Anonymous Coward]

> Imagine a Beowulf cluster of pedants... Now imagine them posting on Slashdot...

Nah, if I was being pendantic I probably have said that a google-sized beowulf cluster of databases that don't support hash partitioning would be the largest waste of fossil fuels in IT history.

Then I might also state vast clusters (generally MPPs) of thousands of smp nodes of hash-key partioned data are already old-hat with various mature database solutions (db2, maybe teradata as well).

Finally, I'd probably have to define what hash-keys are, what mpps are, what smps are, and what the pros & cons of hash, range and list partitioning are. Then remind you that mysql is only planning to support range and perhaps list and even that (with its 200 limitations) is not GA yet.

However, I didn't. I merely provided a simple corrections to your gross misunderstanding of google's architecture and use of mysql. Even a kid could have done that.

Re:Fit for duty?

[alienmole]

However, I didn't. I merely provided a simple corrections to your gross misunderstanding of google's architecture and use of mysql. Even a kid could have done that.

Heh. You apparently missed the fact that my original comment was not intended seriously. Googling for "Imagine a beowulf cluster" might help you understand the context, since it is clear that you must be new here.

Re:Fit for duty?

[Kirth Gersen]

The really great thing about Google is they realized that web searches are an application for computers where *accuracy is not essential*. If you get four useful links out of fifty -- instead of seven -- do you care? Do you notice? If eight of the links are completely cockamamey, do you get bent outashape? I think not.

At last, MySQL has found its niche.

Nice to see the feel-good side of open source

[Anonymous Coward]

It feels all warm and fuzzy, makes me feel that way too.
Oh, no, wait I'm just growing mold.

It's nice to see this though. It's a good change from the internal politics/fighting, or the complaints of other one OSS group using anothers OSS code - it emphasizes what the while thing is supposed to be about.

Captcha: 'sneaking' - so appropriate for an AC like me.

Google-y goodness

[loafing_oaf]

Ah, now this is how it's supposed to work. No bull like, "We're releasing improvements as MSN-SQL," or any other nonsense. Yay Google.

Wont be included in MYSQL...

[emj]

MySQL is dual licensed so if they add this code they can't sell their product under another license..

Re:Wont be included in MYSQL...

[Tokimasa]

Doesn't it depend on the license that Google released their improvements under? If the license that Google uses allows for commercial distribution, then it could be sold.

Re:Wont be included in MYSQL...

[Albanach]

MySQL could license the code for distribution. Equally Google could submit the code to MySQL using their existing mecanism for code contributions and transfer copyright to MySQL.

To suggest that the code can't be included because someone else owns copyright is just wrong - MySQL didn't develop the Innodb stuff, they license it. They could do the same here.

Re:Wont be included in MYSQL...

[BinaryPower]

Google doesn't sell a lot of software products. Some, but not a lot. It's intended purpose was to speed up their own internal system, and so they decided to share their work because it probably works well.

Re:Wont be included in MYSQL...

[MightyYar]

That's true, but only part of this release is a patch - the other two items are tools:

• mypgrep.py - a tool, similar to pgrep, for managing mysql connections
• compact_innodb.py - compacts innodb datafiles by dumping and reloading all tables
• patches - patches to add features to MySQL 4.0.26

It will be interesting to see what happens with the patches.

Re:Wont be included in MYSQL...

[vivaoporto]

Yes it does. And it was GPL, as it's said in TFA. So, no dual license if it is included.

Re:Wont be included in MYSQL...

[diegocgteleline.es]

Also, because it's just cheaper for them if they get it merged in the mainstream mysql tree. Basically, it's stupid not to release it.

Re:Wont be included in MYSQL...

[Bacon Bits]

No, these won't be included because the patches are for MySQL 4.0. To give you an idea, MySQL 4.0 is what shipped with Debian 3.1 (Sarge). It's that old. Mysql.com doesn't even host binaries for MySQL 4.0 anymore!

4.0 is not actively developed at all, and lacks basic requirements such as subqueries, accurate math for DECIMAL or NUMERIC types (they're identical to FLOAT in 4.x so decimal errors for aggregate functions are common), and VIEWs. That's in addition to the normal flaws for data integrity in MySQL (MyISAM database engine, fsync disabled by default) that make it one of the least ACID compliant databases around that can still understand SQL.

This is like Google releasing patches for Apache 1.2.

Re:Wont be included in MYSQL...

[MightyYar]

If you look at the subversion repository, they also have patches for MySQL 5. It seems as though they are not going to "release" those patches until they are feature-equivalent to the MySQL 4 patches:

These will have the same functionality as the MySQL 4 patches. Unfortunately, only some of the features have been ported. This will be updated when the patches have equivalent functionality.

Re:Wont be included in MYSQL...

[DragonWriter]

MySQL is dual licensed so if they add this code they can't sell their product under another license.

Presuming that Google owns all the copyrights (which I would assume they do) to their changes, nothing prevents MySQL AB from negotiating a deal with Google to include the enhancements in the commercial distribution.

If there's enough value to MySQL that they can offer enough to Google to make it worthwhile, there is no reason for it not to happen.

The patch...

[dfdashh]

Wiki for the patch here: http://code.google.com/p/google-mysql-tools/wiki/M ysql4Patches

Re:The patch...

[loconet]

Thank you. Why is it so hard to link to the damn link of interest in article summaries? Why must one always dig it from comments?

Wireless Developers?

[BigDumbAnimal]

The situation is similar among wireless developers, almost 30% of who use Microsoft SQL Server compared to the 20% who use MySQL. What is a wireless developer? Cell phone companies? Programmers who use laptops?

Re:Wireless Developers?

[fm6]

(Snicker.) In the building where I work, there are special supply cabinets for nomadic employees who use the flex offices. These have signs on them saying "These supplies are for flexible employees only!" Every time I see one, I want to add an addendum: "Inflexible employees fuck off!"

Re:Wireless Developers?

[Greedo]

Most of the developers I know are wired ... on Red Bull, that is.

Re:Wireless Developers?

[TheLink]

You mispelled weird. ;)

Re:Wireless Developers?

[greenzrx]

Supplies for flexible employees? I suppose you have to bend over backwards to get them. Bah-dum dum.

This illustrates a problem with commercial OSS

[MikeRT]

At least with the GPL, anyway. You have a company like MySQL which actually makes a product, rather than bundling and providing services as the majority of the business. However because they can't exclusively control the app, they can't make a large amount of money except on other services and such. Then a company like Google comes in, with tons of resources, and makes great modifications to your code. The only problem is that you can't merge them into the commercial distribution without their permission. What's ironic about this is that being dual-licensed, the code would remain free for everyone to use under the GPL.

MySQL chose this approach, but I could see how this might make others less inclined to dual-license under the GPL.

Re:This illustrates a problem with commercial OSS

[dfdashh]

Clarification: MySQL provides tons of services (http://mysql.com/support/). It is just that they are only for their "Enterprise" products. Of course, those are under the commercial license...

Re:This illustrates a problem with commercial OSS

[99BottlesOfBeerInMyF]

This illustrates a problem with commercial OSS At least with the GPL, anyway.

I think this illustrates a problem with trying to sell OSS as if it were closed source software, instead of relying upon contract work for improvements, customizations, services, and other closed source add ons or using that OSS as a tool yourself for some other market.

Re:This illustrates a problem with commercial OSS

[Anonymous Coward]

I think this illustrates a problem with trying to sell OSS as if it were closed source software, instead of relying upon contract work for improvements, customizations, services, and other closed source add ons or using that OSS as a tool yourself for some other market.

But that has it's own share of problems... Namely make sure the software sucks so that there is a market for improvements, customizations, services and support.

Re:This illustrates a problem with commercial OSS

[99BottlesOfBeerInMyF]

But that has it's own share of problems... Namely make sure the software sucks so that there is a market for improvements, customizations, services and support.

No, that is the problem with relying upon support as your main revenue stream, not with any of the business methods I listed.

You can make money developing an OSS database by:

• offering to code improvements, bug fixes, and interfaces for anyone willing to pay for them
• selling any product or service that relies upon a database and using the OSS database as a tool for that purpose (while not paying to license a commercial one and getting free improvements form others)
• building custom solutions for a given company's workflow based around the tool, e.g. a database for tracking the shipments of shoes through customs agencies

None of those provide any motivation for trying to hold back the functionality of the product, nor to do shoddy work in the hopes that you can get paid again for fixing your own problems, because it is a competitive market and someone else can always do better work and drive you out of business.

I think the main thing about OSS licensing that people coming from a closed source sales model mistake is that OSS is not a way to get more money from software you develop. It is a feature of the product that benefits end users and allows you to undercut commercial and closed source offerings with flexibility and price. The people who came up with OSS licensing were both users and developers and the license was designed to benefit them as users. It is not a good business move to open source your code instead of selling it as closed source, unless the cash you get from selling it is not worth the trouble or using the software is more important to you than selling it or if you need a way to make your product better than a competitor and are willing to change to a less profitable model to do it.

Re:This illustrates a problem with commercial OSS

[noidentity]

In summary, OSS creates more opportunities to sell services, but not products. When the product can be produced at near zero cost, only systems with artificial restrictions allow it to be sold for more.

Re:This illustrates a problem with commercial OSS

[Raenex]

I think the main thing about OSS licensing that people coming from a closed source sales model mistake is that OSS is not a way to get more money from software you develop. And later this day a story comes out that puts MySQL revenue at $50 million. I'm sure some of that money comes from companies who buy the product under a commercial license because they can't use GPL.

It is not a good business move to open source your code instead of selling it as closed source, unless the cash you get from selling it is not worth the trouble or using the software is more important to you than selling it or if you need a way to make your product better than a competitor and are willing to change to a less profitable model to do it. Trolltech seems to be doing ok with the dual-license model, too. What you are missing is that lots of companies will pay to get a non-GPL version of the code, while at the same time the developer gets open-source marketing and support for free. The only limitation is the threat of fork and that they can only take code submissions where the copyright is assigned to them.

Then there's Red Hat, who manages to sell GPL'd code they don't own, and Mozilla/Firefox, who makes $50 million a year from Google, but I digress.

Re:This illustrates a problem with commercial OSS

[99BottlesOfBeerInMyF]

And later this day a story comes out that puts MySQL revenue at $50 million. I'm sure some of that money comes from companies who buy the product under a commercial license because they can't use GPL.

This doesn't have a lot to do with my statement. If they could have sold copies of their database to even half the people who use it for free they'd be making a whole lot more than that. Providing it as open source, however, allows them to undercut Oracle and MS and other OSS projects, which is where those people would probably be going if MySQL did not offer it as GPL. I think you may misconstrued my statement. MySQL makes more money by licensing it as GPL as well, but not because the license benefits them directly, but because it benefits customers who would otherwise not have paid them at all. Think of it like a feature. Adding a new feature to MySQL costs money and effort and does not directly benefit the company, but it does benefit their customers, thus motivating more sales, which benefits the company.

What you are missing is that lots of companies will pay to get a non-GPL version of the code, while at the same time the developer gets open-source marketing and support for free. The only limitation is the threat of fork and that they can only take code submissions where the copyright is assigned to them.

That threat is exactly why it is not long-term sustainable business model in a mature market. It works for emerging markets, but eventually it will fail.

Then there's Red Hat, who manages to sell GPL'd code they don't own

I think RedHat is going to be in serious trouble if Linux ever takes off because support is not really a sustainable business model either because it does make development opposed to the interests of users, which will eventually result in them moving elsewhere.

Mozilla/Firefox, who makes $50 million a year from Google, but I digress.

Mozilla has a sustainable business plan. They do work on contract and make funds from ancillary sources like advertising via Google. That is exactly the kind of low-margin business method that cannot only succeed, but also undercut any commercial competitor using traditional methods.

Re:This illustrates a problem with commercial OSS

[Raenex]

MySQL makes more money by licensing it as GPL as well, but not because the license benefits them directly, but because it benefits customers who would otherwise not have paid them at all. Err, I really don't understand what you are getting at. By choosing the GPL, MySQL gained a lot of free press and mindshare, allowing them to sell to companies who otherwise would probably never have heard of them in an already crowded market. Choosing the GPL was a strategy to generate more sales. Period. Otherwise they would not have done it. How can you argue with $50 million in revenue for a GPL'd product? Do you really think they could have sold that much without the free GPL buzz?

[The threat of fork] is exactly why it is not long-term sustainable business model in a mature market. It works for emerging markets, but eventually it will fail. Software doesn't stand still. Even in something as age-old as databases, new features are constantly being added. There is always room for innovation. Red Hat, MySQL, Trolltech, etc. have been around for an awfully long time, selling GPD'd software. Sure, a fork could succeed, it's a risk, but there's no guarantee it will in the long run.

I think RedHat is going to be in serious trouble if Linux ever takes off It has taken off already, on the server-side backend. That's why Red Hat sells "Enterprise" versions of Linux. Supposedly you are buying support, but they use trademark poisoning to take away your GPL freedoms, relicensing the OS so that you cannot install the OS on as many boxes as you want, or copy the software freely. That's why distributions like CentOS have to spend a month disentangling the Red Hat trademark from the software. So Red Hat has moved beyond the vanilla support model, because they weren't selling enough.

Mozilla has a sustainable business plan. They do work on contract and make funds from ancillary sources like advertising via Google. They can be forked just like anybody else. What is Google really paying them for? Once the Google toolbar is part of Firefox, Mozilla doesn't have a monopoly. How is Mozilla's situation different than any other GPL-based company? They own what all the other GPL companies own -- a brand name, and (possibly) copyright ownership if they want to sell a non-GPL version. I say possibly because I'm not sure if Mozilla has maintained tight copyright ownership. I did a search but couldn't see if they required you to sign over the code when submitting patches.

Re:This illustrates a problem with commercial OSS

[99BottlesOfBeerInMyF]

Err, I really don't understand what you are getting at. By choosing the GPL, MySQL gained a lot of free press and mindshare...

Okay. Let me explain it another way. Every move a company makes is either a feature or an anti-feature. Adding a person to your phone support team is a feature because customers get through faster. This costs the company money, which they make up by having happier customers who renew contracts. Removing a support person is an anti-feature for the same reason. It cuts costs though so may allow users to buy at a lower cost. Both measures are designed to make more money for the company in the long run, as is everything a company does.

A lot of people look at GPL licensing and don't understand that it is a feature instead of an anti-feature. It costs the company money in licensing in the short run, but allows for free input from the community and spurs adoption in the long run. Since licenses are generally anti-features, designed to restrict customers more a lot of people have trouble changing this mindset.

GPL licensing is about making users happy which will in turn make the company money. It is not about forcing those users to fork over more cash.

Software doesn't stand still. Even in something as age-old as databases, new features are constantly being added. There is always room for innovation. Red Hat, MySQL, Trolltech, etc. have been around for an awfully long time, selling GPD'd software. Sure, a fork could succeed, it's a risk, but there's no guarantee it will in the long run.

I think you're confusing the terms "making a profit from OSS" and "selling OSS." At some point in the maturity of a software field, selling OSS becomes a losing proposition because the cost of a fork is less than the cost of the license that is worthwhile to sell. You can make money from OSS in the long term (I should know, we're making millions developing/using it right now), but it is unlikely you will be able to sell it in the long term.

It has taken off already, on the server-side backend.

Redhat has already given up on selling to the desktop, for the most part and they make their money off of support as their main revenue stream. The problem with this is that it brings development goals that conflict with users needs. Users want software that requires minimal support and as the server industry matures this will become more and more the case. Redhat had momentum, but anyone can see that they are losing a lot of it in recent years. As you mention, they have tried to artificially "close" Linux by making maintaining forks inconvenient, but it has not really worked well from what I've seen.

They can be forked just like anybody else. What is Google really paying them for? Once the Google toolbar is part of Firefox, Mozilla doesn't have a monopoly.

You're missing my point. Because the Mozilla team is not selling Firefox directly, that motivation for forking is gone. Sure people still can and will fork the project for various reasons, but not in ways that threaten Mozilla's revenue stream directly. Mozilla doesn't make money by selling Firefox. They make money by developing software and providing advertising ties in that software. Google pays the Mozilla team because Firefox sends Web searchers to Google first and because it benefits Google strategically to not be dependent upon their competitor (MS).

How is Mozilla's situation different than any other GPL-based company?

Mozilla is in the same situation as many OSS-based companies. They are different from the MySQL team, in that they do not directly sell the product they make, but profit from a more sustainable business model of revenue from development directly (paid changes for people with money) and through marketing ties.

Google's Not Evil Today

[neoform]

*chalks it up on the board*

"GoogleIsGood"

Silly Question

[bill_mcgonigle]

Did they fix that thing where it always sacrifices data integrity for speed?

It's a silly question, really. MySQL is about performance. If you can trade off integrity for speed, and you need speed, use MySQL with myASM tables. If you need that integrity and would benefit from MVCC use PostgreSQL. If you need MySQL for some reason (web hoster, etc) and want the integrity, use InnoDB tables (for version 5 - something else for version 6), though there's a performance hit and you still don't get MVCC.

Re:Silly Question

[afidel]

That's funny, your own link to Wiki contradicts your assertion that you don't get MVCC with MySQL.
MySQL when used with InnoDB [7] or Falcon [8] storage engines.

Re:Silly Question

[bill_mcgonigle]

That's funny, your own link to Wiki contradicts your assertion that you don't get MVCC with MySQL.

Thanks for making me go look at this.

From the MySQL site:

InnoDB implements MVCC with a combination of multiple versions of record and write locks on key ranges and records. In Repeatable Read transaction isolation mode, InnoDB has update anomalies that require the use of the non-standard SELECT ... FOR UPDATE syntax. The result of a simple SELECT and a SELECT ... FOR UPDATE may differ within the same repeatable read transaction.

Falcon uses versions of records and transaction identifiers to provide repeatable reads and to recognize and prevent inconsistent updates by concurrent transactions. Repeatable read is handled by providing each transaction with the version of each record that was committed when the transaction started.

So it would appear that InnoDB can do MVCC, if you use special syntax. I'm not likely to give full credit for solving the problem set that way.

The Falcon backend appears to do MVCC correctly - it appears to now be available in MySQL 5.2, which is currently in alpha release. I don't follow MySQL too closely, but it seems that this is no long going to have to wait for 6.0 with Firebird, which was the story I heard several months ago. That's good, it'll help people transition more gracefully.

I didn't search enough to find benchmarks of myASM vs. Falcon tables - that would be good to see. Jim Starkey is doing the design work. He's already proven his mettle with Interbase/Firebird. I'd be so tickled if MySQL became a first-class database with Falcon, since it already has the popularity/mindshare.

WTF?

[mekane8]

MySQL is the second most popular database behind Microsoft SQL Server, according to a winter 2006 report from Evans Data. Oracle comes in second if you combine its database offerings. The situation is similar among wireless developers, almost 30% of who use Microsoft SQL Server compared to the 20% who use MySQL.

What exactly does Oracle offer besides database offerings? How do you not include its "database offerings" when you're ranking the most popular databases? Is information week retarded? The vague reference to "wireless developers" is also weird. Wouldn't the Google page be more worth reading and more coherent?

Re:WTF?

[0racle]

What exactly does Oracle offer besides database offerings?

Application Server, ERP, some other stuff.

What they are talking about is counting Oracle Standard, Oracle Enterprise and other Oracle database editions as separate offerings to make MySQL look more important.

Re:WTF?

[Smidge204]

The word is "combine" not "include" - Oracle offers a variety of Database "flavors" (editions) as well as services and utility suites. If you "combine" these offerings, Oracle comes in second.

=Smidge=

Re:WTF?

[Em Ellel]

What exactly does Oracle offer besides database offerings? Erm, check out their web site - look at the other two columns of products besides database - they offer quite a few products including little things like Oracle ERP, Peoplesoft and Siebel.

-Em

Re:WTF?

[allenw]

I parsed that as intending to mean that the Evans Data report splits the various Oracle DB offerings (Enterprise Edition, Standard Edition, etc) into separate products for purposes of calculating market share. Of course, that begs the question of how they are calculating the MySQL installations. Does usage under the commercial/enterprise license count differently than the open source version?

Re:WTF?

[Simon (S2)]

What exactly does Oracle offer besides database offerings?

Middleware and applications like Oracle Fusion, Oracle E-Business Suite, Oracle On Demand, PeopleSoft Enterprise, Siebel, JD Edwards EnterpriseOne, JD Edwards World and so on.

Re:WTF?

[TopSpin]

What exactly does Oracle offer besides database offerings? Three different widespread ERPs (Oracle E-Business, PeopleSoft and JDE,) Siebel, a Java IDE (at one time quite popular,) a database design platform, OLAP stuff (their own and now Hyperion) and application containers. They also have some not so widely used products including a collaboration system (email, etc.) and some sort of Oracle backed file system. They have a cluster file system for Linux that's gotten some play. Oh, and they're an OS vendor now with their 'Unbreakable Linux' thing.

Oracle is a monster.

"wireless developers" Yeah, I don't get that either. All I can say for sure is that I have no wires sticking out of me anywhere.

Re:WTF?

[Anonymous Coward]

What exactly does Oracle offer besides database offerings? How do you not include its "database offerings" when you're ranking the most popular databases? Is information week retarded? No, but you might be. The keyword you skipped over is combine. Oracle does not have a single database product more popular than MySQL, but all of its database products combined etc. etc.

Re:WTF?

[PinkPanther]

It also begs the question as to what they define "wireless" to mean, as SQL Anywhere was not mentioned at all.

Re:WTF?

[MrMunkey]

This is a tad off topic, but MySQL deserves its spot up there.

We've been using MySQL for several years, and recently we decided to purchase the MySQL Enterprise license. Their monitoring tools are AWESOME! I get a text message if a server goes down, replication stops, etc. There are also historical reports and graphs of very detailed information that, I think, you can't get with MRTG and other SNMP monitoring tools. It also gives me suggestions for improving MySQL's performance on that particular server. Even better, it's dirt cheap compared to the other enterprise offerings, and you get support with it.

All in all, I love MySQL, and Google releasing code that makes it better can only be good IMO.

Re:WTF?

[DragonWriter]

I parsed that as intending to mean that the Evans Data report splits the various Oracle DB offerings (Enterprise Edition, Standard Edition, etc) into separate products for purposes of calculating market share.

In edition to a series of "editions" of the main Oracle Database engine product, Oracle has Oracle Berkeley DB and the TimesTen In-Memory Database, which are (certainly in the first case, and I believe also in the second) completely different systems, not just different versions of the core engine.

Frankly...

[Anonymous Coward]

I'd argue speed at all. But InnoDB wins hands god-damned fucking down over the drek that is MyISAM. MyISAM is a carcass; whom can deny the Angel of Death? What Shai-hulud has decreed shall be.

InnoDB: When your clients want to run crap that doesn't support Postgres, and you don't want to run myisamchk every other day.

Re:Frankly...

[Anonymous Coward]

Bobby, Bobby... I told you not to run your databases on floppy disks.

myisamchk every other day? Do your clients run their servers inside volcanoes or underwater or something?

InnoDB...?

[suv4x4]

Apparently something has changed in Google's usage of MySQL. They have this to say for their patch:

"In a perfect world, each feature would be provided as a separate patch and all code would be as portable as MySQL. We are not there yet. These have been implemented and deployed on Linux. Also, some of these features only work with InnoDB, because we use InnoDB."

InnoDB is the slower, but safer/transaction-able way to use MySQL. In an earlier blog entry "let's get a real database", Google revealed they run AdWords/AdSense on MySQL, and they rolled up their own transactions as they went for speed with MySQL. Now we see they changed their mind.

So I suppose that goes to show, never mind how limited your application, you better stay away from MyISAM: it'll bite you sooner or later.

Re:InnoDB...?

[DeionXxX]

Umm, InnoDB is an engine for MySQL, they are still using MySQL. They just said they use the InnoDB engine exclusively rather than the other engines such as MyISAM. They probably never used MyISAM since it doesn't have foreign keys or transactions. No respected DBA would ever use MyISAM.

Re:InnoDB...?

[fruey]

I've seen a lot of people saying InnoDB is slower, but last time I converted a forum from MyISAM to InnoDB it was *faster* on reads, it was the writes that were slower...

And, of course, it didn't crash all the time.

Re:InnoDB...?

[suv4x4]

Umm, InnoDB is an engine for MySQL, they are still using MySQL. They just said they use the InnoDB engine exclusively rather than the other engines such as MyISAM. They probably never used MyISAM since it doesn't have foreign keys or transactions. No respected DBA would ever use MyISAM.

Where did I say InnoDB wasn't MySQL ..? And no, they used MyISAM initially, you can check the comments after their original post.

In fact the irony in your post is big, as the original post addresses just people like you who are very quick to decide what a "respected DBA" would do, or not (versus reality).

I'll quote one of the Google devs answering questions about it:

Q: holy crap, you ran adwords on a transactionless database?

A: Yep.

Q: you have balls. and you're lucky as hell nothing (else) went wrong.

A: Nope. Luck had nothing to do with it. If you don't have transactions you just roll your own. It's actually not hard at all.

Bottom line is, you use transactions as it easier to retain data integrity and makes for simpler code. You don't use transactions to be "respected DBA". Whatever reasons they had to use MyISAM was probably sound, but after certain level of sophistication, they switched to InnoDB to make things easier to manage.

Re:InnoDB...?

[merreborn]

One of the things that makes innodb faster than MyISAM in many cases, is MyISAM uses table-level locks while innodb uses row-level locking. If your read/write ratio is anywhere between 30-70%, innodb will be faster. In fact, in a few places, the mysql guys say: MyISAM is only faster if your table receives 90% writes, or 90% reads.

Re:InnoDB...?

[DeionXxX]

I really wasn't talking about transactions, though very important, I think enforced foreign-keys is what most DBA's would say is more important. Without foreign-keys, the developers could enter in whatever information they want into the database. If there was a bug in some code, you could have orphan records, and lose data integrity, which will be vital for reporting.

I could definitely see a reason for running MyISAM in situations where you don't care about orphan records, or data integrity, I just didn't see them doing it on such a huge important database as the adwords database. I think the developers originally made the case for speed over features (MyISAM over InnoDB), but there are reasons every major database has all of those features. Once your database gets over a certain size or certain complexity, those features are vital. As soon as you go from 5 developers to 30 or 100, having foreign keys etc will be vital.

Also, I will give you the benefit of the doubt that you meant to say "MyISAM" in all of the places you said "MySQL", but if you read your original post, you mention them using MySQL in places where you probably meant MyISAM, then at the end you switch to MyISAM. If this is what you meant, then I obviously misunderstood you, but from the responses and moderation I got, I'm assuming a lot of people misunderstood you too.

Your Post
"InnoDB is the slower, but safer/transaction-able way to use MySQL. In an earlier blog entry "let's get a real database", Google revealed they run AdWords/AdSense on MySQL, and they rolled up their own transactions as they went for speed with MySQL. Now we see they changed their mind."

Re:InnoDB...?

[Anonymous Coward]

Luck had nothing to do with it. If you don't have transactions you just roll your own. It's actually not hard at all.

That quote proves nothing, except maybe that the person at Google who wrote it is completely incompetent.

I remember reading that exchange. It was sad and surreal. It's like a respected doctor writing "It's okay if you don't wash your hands before surgery, just be careful what you touch." Or one of the world's supposedly best engineers saying "We don't need this bridge to support anything larger than a Ford Festiva. There's a sign that keeps bigger cars out." No thanks. Google doesn't get a pass when one of their groups thinks "data modeling" means "saving some Python objects and getting them back".

I'd still like to know 1) how they "roll their own" multi-statement transactions (without doing crazy plumbing-level shit like creating journaling tables), 2) how they know it never fails.

Here's a definition of a "respected DBA": someone who has a fucking clue about data management and doesn't pretend like the last 30 years of progress in data management never happened. You're not going to find many on Slashdot, that's for sure, and apparently not at Google either.

Re:InnoDB...?

[raxx7]

Depends. A transactional engine provides four features (as you likely know): Atomicity, Consistency, Isolation, Durability
Some are fairly easy to work arround in MyISAM, some I have no idea how.
Consistency is "easy", just make sure your application doesn't put inconsistent data in the database.
Atomicity is doable. In MyISAM, single row updates are atomic. You need to design your database in a way that any multi-row updates are marked valid by a single row update. The database design will be more complex and might hurt performance a lot.
Durability might be doable too, by combining the former technique with a liberal use of FLUSH TABLES but should be murder on performance.
Isolation.. I have no bloody idea on how to simulate.

Re:InnoDB...?

[Anonymous Coward]

You must be under the impression that you're smarter than everyone else and your way is the only way. How you could come to these conclusions, is beyond a rational human mind. Simulating transactions is nothing special. Simple queing comes to mind. Your analogies are offbase and your comments are clearly trollbait.

Re:InnoDB...?

[suv4x4]

Isolation.. I have no bloody idea on how to simulate.

People achieve (a form of) this by using table locks (which MyISAM supports). Of course, you can't really roll it back automatically.

Re:InnoDB...?

[suv4x4]

Your Post
"InnoDB is the slower, but safer/transaction-able way to use MySQL. In an earlier blog entry "let's get a real database", Google revealed they run AdWords/AdSense on MySQL, and they rolled up their own transactions as they went for speed with MySQL. Now we see they changed their mind."

You're right, the last one "MySQL" should've been "MyISAM".

Hep Me Understand...

[Anonymous Coward]

What good is speed without data integrity? If I can't trust my data, It doesn't matter how fast I can retrieve it.

Please help me understand this perspective. (?)

Re:Hep Me Understand...

[bill_mcgonigle]

What good is speed without data integrity? If I can't trust my data, It doesn't matter how fast I can retrieve it.

If you have a read-only situation there's no need for full ACID compliance. I've seen some contrivances where MySQL reads happen from myASM databases, and the writes go into an InnoDB database, and something on the backend happens to replicates the changes into the 'read-only' databases reliably. I've just never had, myself, an application so speed critical that it was worth doing that instead of doing PostgreSQL for everything. But my use cases aren't everybody's use cases.

Re:Hep Me Understand...

[_|()|\|]

What good is speed without data integrity?

In this context, data integrity refers to mechanisms that prevent you from doing something bad. If you never do anything bad, these mechanisms aren't strictly necessary. It's analogous to the difference between dynamically and statically typed program languages.

What do I mean by "something bad"? Without referential integrity, you could have the database equivalent of dangling pointers. Without concurrency controls (either through locking or the previously mentioned MVCC), one user could accidentally blow away another's changes. (Look up isolation levels for some specific examples.) Without transactions, a failed update could leave the database in an inconsistent state (e.g., the money left your savings account, but never made it to your checking account).

Re:Hep Me Understand...

[Safety Cap]

If you never do anything bad, these mechanisms aren't strictly necessary.

Show me one programmer who hasn't done something 'bad' (i.e., made a mistake) and I'll say, "Just let him write his second line of code," just after I pry the delete, backspace and ctrl keys off his keyboard.

Re:Hep Me Understand...

[Anonymous Coward]

You forgot shift...

Re:Hep Me Understand...

[caluml]

But my use cases aren't everybody's use cases.

I wish more people would think like you.

Re:Hep Me Understand...

[bill_mcgonigle]

I wish more people would think like you.

Hey, I'm working on it! ;)

Nice location mashup, BTW.

So...what do the changes actually DO?

[xxxJonBoyxxx]

So...what do the changes actually DO? This page is awfully thin on content:
http://code.google.com/p/google-mysql-tools/

These two things look like simple add-on utilities, not something for the core DB (the "compact..." one looks especially unfriendly for live DBs):

mypgrep.py - a tool, similar to pgrep, for managing mysql connections
compact_innodb.py - compacts innodb datafiles by dumping and reloading all tables

I'd like to play with this stuff:

The new features include support for "for semi-synchronous replication, mirroring the binlog from a master to a slave, quickly promoting a slave to a master during failover, and keeping InnoDB and replication state on a slave consistent during crash recovery."

...but where's the actual doc?

And why is Google releasing these for an "unsupported" version of MySQL (4.0)?

Re:So...what do the changes actually DO?

[mhall119]

And why is Google releasing these for an "unsupported" version of MySQL (4.0)?

Probably because that's the version of MySQL they wrote it for?

Re:So...what do the changes actually DO?

[Anonymous Coward]

Here's a blog entry explaining in detail what they released.

http://google-code-updates.blogspot.com/2007/04/go ogle-releases-patches-that-enhance.html

Nevermind - found the Wiki with more info...

[xxxJonBoyxxx]

Nevermind - found the Wiki with more info...
http://code.google.com/p/google-mysql-tools/w/list

Just ask

[wiredlogic]

It'll be interesting to see if the changes they made are of interest to other places using MySQL.

Why don't you ask Jeeves then?

Re:Just ask

[Anonymous Coward]

A flat head screwdriver should be used for flat-head screws and a phillips should be used for phillip screws. They both have their place just as MySQL is a flat-head and SQL Server is a phillips. Companies that prefer warranties and guarantees in terms of confidence levels toward the companies (or open source) that produce these products decide whats best for their organization. Mid range companies prefer to go with MySQL and Php because its open-source and best of all free. Where most Enterprise companies prefer non-open source products because of strong backing from the companies that produce them, such as Microsoft where a level of risk is envolved. After all, Microsoft cant risk their name if Company A is having an issue therefore they must comply with trying to satisfy all customers for the sake of one. Whereas if something goes wrong with MySQL, who you gonna call...with no one to blame. Stockholders dont want to hear that. Freelance developers like MySQL because they can get more business in the free market with no cost to themselves or the client.

Open your eyes vato and stop whinning!

Oracle Rdb

[Guanix]

There's also Oracle Rdb, a product for OpenVMS that, as far as I understand, is quite different from the regular Oracle database. More details at Wikipedia.

Mod parent up!

[Anonymous Coward]

Hep me, too - what good is ANY data storage mechanism that doesn't offer data integrity as feature zero?

Can someone describe what loss of integrity we're talking about?

Re:Mod parent up!

[Anonymous Coward]

Slashdot is a perfect example of a loosey-goosey "good enough" MySQL application, where speed is important. The data accuracy isn't that important. All that matters is that it works most of the time - a comment lost here or there, or slightly overlapping multiple pages in a story, hardly matters in the big picture. If your comment doesn't "take", just try again, no big deal. OTOH you wouldn't want this behavior for your corporate accounting ledgers, so there you'd be safer using a "real" database designed from the ground up based on the correct principles of database theory, such as PostgreSQL.

Re:Mod parent up!

[JavaRob]

Slashdot is a perfect example... Yeah -- I'm confused as to why Taco didn't mention that. Taco -- are YOU interested in the changes Google made?

I think /. has put a lot of time into tweaking their install as well... not sure if they go so far as making code changes to MySql, though.

Re:Mod parent up!

[Anonymous Coward]

The "slightly" (ha) overlapping pages in a story are a major factor in my assessment of Slashdot as a pathetic application, if it can't even handle pagination correctly. Is this the database's fault?

Re:Mod parent up!

[Safety Cap]

Examples include:

• ignoring "not null" constraints,
• accepting and silently truncating data (stuff a 30-char string into a varchar20 field and you'll loose that last 10 chars without ever knowing it),
• accepting impossible dates, etc., etc.

Re:Mod parent up!

[kv9]

Slashdot is a perfect example of a loosey-goosey "good enough" MySQL application, where speed is important. The data accuracy isn't that important.

+1, Informative my foot. what about adwords? oh wait, you were talking out of your ass like all MySQL bashers.

OTOH you wouldn't want this behavior for your corporate accounting ledgers, so there you'd be safer using a "real" database

yadda-yadda-real see link.

Re:Mod parent up!

[Anonymous Coward]

Your link just reconfirms what I said. Adwords is another example of where serious speed is important, but absolute accuracy isn't - among other things there isn't any way to verify an occasional missed ad or whatever, and it's all based on approximate algorithms anyway. Google's seach engine as a whole is kind of rough this way - their servers aren't always synchronized, and two identical queries will often yield different results (which I find frustrating) - but it's "good enough". I don't know how much of that is MySQL vs. how they distribute their data, but the point is that they don't need iron-clad accuracy for much that they do. But I seriously doubt they run their corporate finances on MySQL.

Wrong link

[jlebrech]

Was expecting a link to a google hosted webpage, in the hope of seeing a slashdoted google page in vain.

Obligatory sane link

[acidrain69]

For those that couldn't get past all the doubleclink.net stuff,
http://www.informationweek.com/news/showArticle.jh tml?articleID=199201237

Typical for MySQL ...

[Anonymous Coward]

I was curious what the actual changes were, so I went a looked...

Why am I not surprised?

        * mypgrep.py - a tool, similar to pgrep, for managing mysql connections
        * compact_innodb.py - compacts innodb datafiles by dumping and reloading all tables
        * patches - patches to add features to MySQL 4.0.26


Dumping and reloading all tables?!? That must be one of the "enterprise" features MySQL boasts about?

Re:Typical for MySQL ...

[Splab]

You are the second one I see make fun of this, and I fail to see why this is funny. If your database is used primarily for looking up stuff and seldom has inserts/updates, it makes perfect sense to dump your tables and reloading them once in a while in order to speed up sequential reads.

Re:Typical for MySQL ...

[kpharmer]

> You are the second one I see make fun of this, and I fail to see why this is funny. If your database is used primarily
> for looking up stuff and seldom has inserts/updates, it makes perfect sense to dump your tables and reloading them once in
> a while in order to speed up sequential reads.

No it doesn't - that's what online reorgs are for. Why should I take a database offline merely to resolve fragmentation? Does mysql need vacuum?

And sure, I could double my number of servers and use replication to get a failover. But why should I have to buy another large server, possibly license mysql once again, deal with a more complex environment when every other database product out there has embraced a far simpler solution?

Re:Typical for MySQL ...

[TheLink]

If you have all your innodb tables in one big multiGB innodb file (as required by old MySQL4 versions) then that dumping and reloading takes quite a long time.

Too long.

Re:Typical for MySQL ...

[Jack9]

>why should I deal with a more complex environment when every other database product out there has embraced a far simpler solution?

Because it's a niche problem that the majority of developers don't run into or work around. By the time you have an issue with table fragmentation affecting your reads, you're running multiple servers.

> Does mysql need vacuum?

That seems rhetorical. You're strongly implying it does. I'm weakly implying it does.

Everyone makes tradeoffs. MySQL has traditionally adapted common practices (or equivalents) of other engines over time. MySQL development never focuses very heavily on support tools. No money in it. I'm not worried and it's never been a significant problem for me.

Better replication is a start

[thsths]

Because the replication support in MySQL is very basic. A single master for the whole database, isn't that a bit limited? I had to run two database processes just because I had two data sources to replicate from.

Unfortunately, the patches from Google don't really fix the basic problem. I think MySQL needs a complete redesign of the replication function. Oracle seems to handle this much better: the replication is controlled on the master, not the client, and it works in just about any combination you can imagine. That is not to say that the MySQL replication is not useful in certain situations (it fans better), but it is very much limited to one kind of application.

Way to go.

Re:Better replication is a start

[Doug Neal]

I'd love to see proper multi-master replication on MySQL, I can't see any other way of doing an HA setup on an insert-heavy database (something I'm currently looking into). As it is, replication is a bit sketchy, it completely falls over on a few things that it shouldn't - delayed inserts and temporary tables, to name two. I think they've addressed some of the problems in 5.1 with a hybrid of SQL-based and row-based replication but haven't tried it out yet (mainly due to Debian only being on 5.0)... anybody have any experience of this?

Re:Better replication is a start

[cilix]

You should look at mysql cluster. I'm yet to use it, but for a write heavy and high-availability SQL DB it's a pretty good option.

http://dev.mysql.com/doc/refman/5.0/en/mysql-clust er-overview.html

Cheers,
Tim

Re:Better replication is a start

[Darby]

You should look at mysql cluster. I'm yet to use it, but for a write heavy and high-availability SQL DB it's a pretty good option.

The major downside of Clustering in 5.0 is that you need to be able to hold your entire database in RAM.
This is supposed to have gone away with 5.1. We're about to go ahead with testing of that.

Re:Better replication is a start

[thsths]

> You should look at mysql cluster.

I did. It is impressive, but with the separation into front end and back end it is everything that MySQL usually isn't:

* difficult to set up
* high latency
* limited by memory

No, I want powerful replication with a database as simple as MySQL. Otherwise I could just as well use Oracle. Yes, that is technically challenging, but it should be possible.

license incompatibility?

[nanosquid]

The Google add-ons are under Apache 2, while the open source version of MySQL is under GPLv2. Doesn't this present a problem?

Re:license incompatibility?

[linhux]

Actually, the announcement blog post says:

[...] thus we have released the changes with a GPL license for the MySQL community to use and review.

It seems to me that while their Python tools are Apache-licensed, the actual MySQL patches are GPL.

Re:license incompatibility?

[Pollardito]

that's really odd, i had remembered the article mentioning that they were released under the GPL and sure enough the first paragraph is:

Google on Tuesday released new programming code under the GPL license to enhance MySQL databases.

but then the actual tools code page lists :

License: Apache License 2.0

as for the original question, the Apache FAQ for their license says they think that they're compatible but FSF does not:

Is the Apache license compatible with the GPL (GNU Public License)?

It is the unofficial position of The Apache Software Foundation that the Apache license is compatible with the GPL. However, the Free Software Foundation holds a different position, although we have not been able to get them to give us categorical answers to our queries asking for details on just what aspects they consider incompatible.

Whether to mix software covered under these two different licenses must be a determination made by those attempting such a synthesis.

Re:license incompatibility?

[nanosquid]

The Google Code page says it's Apache 2, and I think that is what actually counts (rather than the announcement).

The question I want to know is...

[Refefer]

How long until Google's contributions to the open source community become regarded as financially motivated or opressive?

Rather, the question I want to know is, when will it become cool to hate Google?

Re:The question I want to know is...

[boxless]

i already do. Am I cool?

I know they are doing very well financially, but they are a one trick pony until proven otherwise.

Now, that trick (search with ads) happens to be a cash cow, but it's still one trick.

I am unconvinced that google's going to take over the world.

Re:The question I want to know is...

[rainmayun]

The same could have been said of Microsoft a couple of decades back. Make enough cash doing your one trick, and you can buy anybody else doing innovative things.

They differentiate between 9i and 10g

[brokeninside]

The numbers from the Evans Data report:

1. MS SQL - 61%
2. mSQL - 40%
3. MS Access - 38%
4. Oracle 10g - 22%
5. Oracle 9i - 20%

The survey ``asked developers at 517 companies in its 2006 winter survey what database they developed with'' with developers allowed to give multiple answers. It isn't clear to me why Oracle was split between two versions while none of the other databases were.

Re:They differentiate between 9i and 10g

[ahmusch]

I guess we're lucky that they didn't differentiate between 9i Release 1 and 2, and 10g Release 1 and 2.

Re:They differentiate between 9i and 10g

[kchrist]

mSQL? What year is this?

(yes, I know it was a typo)

Finding what's "under the covers"

[VampireByte]

Also, way too much of what go's on in Enterprise Manager is undocumented. Oh, the way to do it in the GUI is documented, but under the covers it is impledmented with undocumented Transact SQL.

You can use the SQL Server Profiler tool to capture the SQL that's run when you do something in Enterprise Manager. I've done this when I wanted to do something in my own admin tools that I know can be done in EM.

But they aren't really combining separate products

[brokeninside]

They're combining different versions (10g and 9i) of the same product. I'd have more sympathy for the distinction for the split if they (a) differentiated along those lines for all vendors or (b) they were differentiating distinct products such as the flagship Oracle RDBMS vs. the products the various database vendors purchased by Oracle that they still offer for sale while the features are being folded into future versions of Oracle proper.

Dual licensing hurting other projects too

[gr8_phk]

IMHO the dual license issue is holding back Firefox and OpenOffice.org as well. There are GPLed things that would make sense to include in each of those, but will not be included because of the dual license issue. Evince and (in the near future) gnash would make nice additions to firefox, not to mention the fact that they couldn't leverage existing SVG code going into 1.5 but had to write their own. Sure there are plugins, but for common stuff it should just be in there. OpenOffice is apparently planning to rewrite the charting portion of the spreadsheet. Wouldn't it be nice to be able to use existing GPLed code for that? Perhaps taken from gnumeric. Never mind the merits or lack of, the point is that they don't even have the option because of the dual license.

Too bad the writer doesn't understand the numbers

[brokeninside]

I was thinking about this and the commentator made a really big mistake unless we assume that no developers who intend to work with Oracle 10g are also working with Oracle 9i. Since the survey allowed for multiple answers, you can't just add the numbers of the two Oracle products together.

On the other hand, at least the commentator didn't add the numbers for MS SQL to the numbers for Access and conclude that MS has a combined 99% market share.

but you can't combine the numbers like that

[brokeninside]

They survey is a multiple selection survey of what databases a certain number of developers intend to use for development platforms in a given span of time. You can only combine the two Oracle products together if there is no overlap between respondents who answered Oracle 9i and respondents who answered Oracle 10g. The survey had MS SQL at 61% and MS Access at 38%. If we used the same arithmetic, we'd have 99% of developers intending to use a Microsoft database platform. But, the truth of the matter is that there is a significant amount of overlap between those who use SQL Server and those who use Access as one of the more popular uses for Access is as a front end to SQL Server.

From the analysis I've seen of the Evans Data numbers, you can't pull out enough information to find out how many developers are using either Oracle 9i or Oracle 10g. Rather you can only know that of the developers polled, 20% use one and 22% use the other. How much overlap exists between those two groups is unknown. For that matter, how much overlap between MySQL developers and MS SQL developers is unknown. The actual report might break things down enough to get good information but I've only seen the treatment by various trade rags rather than actual report.

What do they use for search?

[yabos]

TFA says they don't use MySQL for their search engine database. Does anyone know what they DO use for search?

Re:What do they use for search?

[Anonymous Coward]

Does anyone know what they DO use for search?

Yes, someone does know.

Re:What do they use for search?

[Anonymous Coward]

They use Big Table, a database they developed in house.

Re:What do they use for search?

[Anonymous Coward]

I suspect you haven't read the Bigtable paper then..

Re:Too bad the writer doesn't understand the numbe

[ahmusch]

Not having seen the survey, if the survey was "pick one", then there's potentially no middle to be excluded.

Regardless, it's not defensible to subdivide Oracle by version and not subdivide any other RDBMS system.

I haven't seen the actual survey either

[brokeninside]

But the results add up to more than 100% so if it was ``pick one'' then the results are bogus anyway. But I do agree that is highly irregular to subdivide version's of one vendor's products but not the others.

Huh?

[encoderer]

I'm so sick of hearing uninformed people complain about MySQL issues that have been non-issues for years now.

All three of the issues you mentioned are fixed. And not in MySQL 5 which came out a year ago. No, they're fixed in MySQL 4.

All these complaints about MySQL data integrity as if it's a common occurance to lose or corrupt data in MyISAM tables are just ridiculous.

Go try all three of the items you've mentioned against a MySQL 4.x table with the default ISAM storage engine. The only one that MIGHT happen is silent truncation, but that's only if you configure it to report only errors and not warnings.

You just might be a consultant.

[5n3ak3rp1mp]

Are you a consultant? ;) (I was... sounds like Deloitte...)

Re:You just might be a consultant.

[fm6]

Why do I sound like a consultant? My cynicism is pure lifer!

MyISAM is good for write-only databases

[Nicolas MONNET]

Kidding aside, the best use of MySQL and MyISAM is for data you modify very often compared to how often you read them; either short lived-data such as session data (cookies) or logs.

It's so much better to have your logs in MySQL than on a flat file, you can do all kind of analysis real time etc.

Google should reincarnate mysqlcc

[a1mint]

Perhaps Google can help bring back mysqlcc, and put those other stupid lame newer tools that mysql coughed up out of its misery.

I am surprissed

[Billly Gates]

I wonder if the cost of hiring programmers to implement these features in mysql outweighed the cost of using a more powerful DBMS like Postgresql or even a proprietary one like Oracle?

Mysql is known for a fast file system and is improving fast but many features google added already exist in current databases.

You were modded troll because you are stupid...

[Generic Player]

And its hard for mods to tell the difference between a troll and a genuinely stupid poster sometimes. Claiming that transactions are seldom needed is probably enough for people to think "nobody can really be that stupid" and thus decide you're a troll.

http://sql-info.de/mysql/gotchas.html

Most of that list is data integrity problems. Its not FUD, its fact.

Re:You were modded troll because you are stupid...

[encoderer]

Transactions _ARE_ seldom needed. I've developed against MySQL for years and have only once suggested a client upgrade their RDBMS due to the need for transactions and, in this particular case, triggers.

Unless you're doing multi-step commits transactions are USELESS. Absolutely USELESS. And you can call me "stupid" but your calling your "gotchas" list "data integrity problems" makes me think that term applies more aptly to YOU.

Did you even look at that list? So mysql handles NULL values different than ANSI standards? so what? that's an issue you have to understand to properly program against MySQL but it's not a "data integrity" issue. It doesn't make you "sacrifice" data and it doesn't put your data at risk.

Furthermore, the few issues that ARE data integrity issues on that page -- such as ignoring constraints -- were fixed by 4.1. In fact, 75% of the items on that page were fixed by 4.1. That version was releases nearly THREE YEARS AGO.

Like I said, it's FUD based on problems from YEARS AND YEARS AGO.

Now, I'm certain that you won't actually have the audacity to reply to this, so just consider this your bitch-slapping for the morning, alright? kthanks.

Re:You were modded troll because you are stupid...

[Generic Player]

If you find transactions are seldom needed, then you aren't doing anything of any significance. If you don't need an RDBMS at all, then of course you don't need transactions. And silently accepting data and then changing it without informing you in any way is most certainly a data integrity issue. If you think posting further proof that you are a moron is a bitchslapping, then you should really attend remedial bitchslapping classes, cause you suck at it.

Re:You were modded troll because you are stupid...

[encoderer]

No, if transactions are never needed it just means I'm not creating yet another boring fucking accounting system. For the software I'm hired to build, database transactions are usually limited to single table updates or inserts at a time. I'm not writing carbon-copy business software like the millions of wannabe Visual Basic programmers out there are. And in this particular world, the software is usually not database centric. Standard CRUD apps that you get paid $25 an hour to write can take advantage of the things atomic transactions have to offer. But for most of the software written (like, for example, this web browser, every single Office application, photoshop, and just about every other commercial application people are lining-up to spend $400 for) doesn't require rock-solid db transactions.

So please, the kiddy table is to the left. Stay there from now on. You're boring me.

Re:You were modded troll because you are stupid...

[Generic Player]

"But for most of the software written (like, for example, this web browser, every single Office application, photoshop, and just about every other commercial application people are lining-up to spend $400 for) doesn't require rock-solid db transactions."

Like I said, they don't require an RDBMS at all. Grow a brain fucktard.

Re:You were modded troll because you are stupid...

[encoderer]

Actually, among many other examples, if Firefox, which is implementing a DB engine for central storage of settings and configurations, as well as extensible storage. This sorta thing isn't rare. Like, for example, prototyping software that I wrote last year. Think domain-specific Catia that uses a database to store both source data -- assemblies and such, basically collections of polygons -- and compilation data -- again, collections of polygons that are stored in coordinate form so they can be exportred and manipulated in many ways.

This is not something that needs transactions in any possible way.

So really, get over it. I'm pretty positive for about a dozen reasons that you have no idea what you're talking about. Have you ever, you know, been paid to develop software? And if so, is it script-kiddy IT-department CRUD development? .... In fact, dont' answer that. I'm positive you've never written any REAL software.

Re:You were modded troll because you are stupid...

[Generic Player]

"Have you ever, you know, been paid to develop software? And if so, is it script-kiddy IT-department CRUD development? .... In fact, dont' answer that. I'm positive you've never written any REAL software."

Says the guy who can't tell the difference between "an RDBMS" and "any database of any kind". See, this is why everyone assumes you are a troll, its genuinely hard to believe anyone can be as stupid as you appear to be. Firefox doesn't use an RDBMS because it doesn't need one. So claiming that because firefox doesn't need transactions, RDBMSs don't need them means you are a fucking moron.

My subject

[Anonymous Coward]

Comment 1

Wow, you really are clueless...

[encoderer]

Dude, Firefox is implementing a relational database in V3. ........So, you were saying?

Like I said, bro, move on. You're just a bystander. You have no clue what you're talking about. You're out of your depth here and it shows. I only look stupid to you because you don't understand how to use the tools of this particular trade. Assuming that atomic transactions are an essential part of a relational database is just silly. Assuming that every application that uses a relation DB needs transactions is equally as silly.

Seriously, you're clearly either a student, an IT grunt of some sort, or, MAYBE, an IT department CRUD developer whose never written an application that wasn't database centric. \\

Once again, I'm forced to bitch slap you, and I'm starting to feel bad for you. Everyone of your arguments is based on your own flawed and imcomplete knowledge. This is really getting comical but I feel like I'm shooting baskets over the guy in the wheelchair. I'm kicking your ass, but there's no pride in it.

So please, save it.

Re:Wow, you really are clueless...

[Generic Player]

"Firefox is implementing a relational database in V3"

Which they don't need first of all, you don't need a relational database to search your history. Second, sqlite has transactions.

Making moronic claims about how much you think you are bitch slapping me, or trying to insult my work isn't going to make you any less stupid, or make mods any less likely to mod you a troll. I answered your questions, told you why you were modded troll, and gave you a list of mysql data integrity problems. Cry and act like a baby if you want, but even in the giant festering pile of idiocy that is slashdot, you still stick out as being so stupid its troll-mod worthy. Certainly an accomplishment, perhaps you should add it to your CV.

Re:Wow, you really are clueless...

[encoderer]

1. There is no slashdotter alive that hasn't been Mod'd troll before. Including you.
2. I have better karma than you. This doesn't matter much, but really, it makes it all pretty comical when you try to cast aspersions at me for being a "troll"
3. Congrats for doing some research about FF3.0 and now acting like you know wtf you're talking about. You don't. The DB Implementation is not for search history. It's designed to provide client-side data storage to XUL apps. Seriously, you really should stfu until you actually KNOW what you're talking about. It just makes you look dumb
4. Who cares if SQLite has transactions? Why does that matter? Oracle has transactions, too. That doesn't mean I'm _FORCED_ to use them...
5. I'm not insulting your work. I'm pointing out that you're not qualified to see the other side of the coin.

Hey, I get it that you've been trained that transactions are some end-all be-all. But your training is comming up short here. Like I said, you're out of your depth. It's just this simple:

Single Row, Single Table commits have no use for transactions. If it fails, all of it fails. If it succeeds, all of it succeeds. I made this point about 20 posts ago, but you were unable to parse it thru your mindless blathering. THINK, bro, THINK. Think first, then type. It works much better.

You might be a good software developer some day. But to do that, you need to get past the dogma that you've been taught and start to think on your own. When doing a single-row, single-table commit, transactions provide you with absolutely ZERO advantage. In fact, in such a case, they're just overhead.

So you can continue to argue that every use of a relation DB must use transactions or else the developer is stupid. I don't mind. Nobody else is watching this thread, anyway. It's just you, over and over again, thrusting yourself into my oncoming path so I can roll right over you. It's actually a little funny.

Now, go back to your CRUD apps and leave the real development to the big boys, OK?

Re:Wow, you really are clueless...

[Generic Player]

"Hey, I get it that you've been trained that transactions are some end-all be-all. But your training is comming up short here. Like I said, you're out of your depth."

No, I never said transactions are end all be all. Go read your bullshit again. You claimed that transactions are seldom needed. I said they are frequently needed when dealing with relational data. If your ego depends on your creative memory that's fine, but it also means there's no point in trying to reason with you since you just pretend we said something totally different from what was actually said.

"Single Row, Single Table commits have no use for transactions."

Right. And single row single table commits aren't relational data. Duh?

"It's just you, over and over again, thrusting yourself into my oncoming path so I can roll right over you."

No, its just you clinging desperately to your delusions that data integrity doesn't matter and anyone with common sense must be brainwashed morons, I'm done.

Re:Wow, you really are clueless...

[encoderer]

"Right. And single row single table commits aren't relational data. Duh?"

Dude? Are you joking?

You can't POSSIBLY be this stupid.

I can have a table for, say, an assembly which includes data like when that assembly was created, and various other meta data like the customer, job number, etc. I can then have a table full of jobs and a table full of polygons in the assembly. The polygons table holds the Assembly fKey and the Assembly table holds the job fKey. This is certainly relational data. And this is a real example from the project I mentioned a few posts ago.

In this application there are about 1000 inserts into the polygon table for every 1 insert into the assembly table, and there are about 20 inserts into the assembly table for every one insert into the jobs table.

90% of all DB I/O in this application is the polygon table.

I've written a number of apps that have very similar characteristics to this project.

Relation Data. Single Row, Single Table Commits. No transactions needed. No value from using transactions.

"No, its just you clinging desperately to your delusions that data integrity doesn't matter and anyone with common sense must be brainwashed morons, I'm done."

No, it's only in your small mind where you need atomic transactions to ensure data integrity.

Get over it.