Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI SQL extensions ?

The story here seems to be the following - REPLACE INTO existed forever, at least since MySQL 3.22 and was a way to do replace faster and what is also important atomically, remember at that time MySQL only had ISAM tables with table locks and no transactions support. Of course you could use LOCK TABLES but it is not efficient.

The reason REPLACE could be efficient for ISAM and MyISAM, especially for fixed length rows is - it could perform row replacement without reading old data first, and of course because you could set it to replace multiple values at the same time just as you have multiple value INSERT.

As a side note: the …

The objective of this exercise is compiling MySQL from a source tree (BitKeeper) on Mac OS X.

I bought a new laptop, a MacBook running Mac OS X Tiger. I was captivated by the user interface, and I was willing to try this new experience, after a long and successful relationship with Linux (I gave up Windows many years ago). Everything went well, until the moment I tried to compile MySQL from source.
Using the default Xcode tools, I was able to compile the source packages provided with the GA release (5.0.x). But with 5.1 it was a different story. I was not even able to complete the compile part. something was breaking quite soon during the process.

After unsuccessfully trying all the tools made by the MySQL Build Department, I finally decided to take things into my own hands, and to create a reliable compiling environment.
I - Isolating the environmentI decided that the sanest course of action was to get the …

There have been a lot of good talks at linux.conf.au so far that I’ve attended (and the one’s that I’ve missed due to scheduling difficulty, I’ve already started watching some videos of). There are lots of good reports about it on Planet Linux Australia even, but one of the most useful talks in my opinion was Jono Bacon’s talk on Herding Cats and Influencing People (watch the video when its uploaded), his thoughts on running a Community.

He talked about McDonalds, and how they package things, that are consumable by all (his point was that projects generally might even need bite size tasks, not get new contributors chucked into the deep end). Which got me thinking, a little more. McDonalds are in the real estate business, and they’re one of the major players in the fast food industry. I’ve always believed that there are too many Linux distributors in the market, …

REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong.

Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.

Yes, I realize both are MySQL additions, so please don’t bother telling me they’re not ANSI standard.

References:

• INSERT… ON DUPLICATE KEY UPDATE
• REPLACE INTO

Although I have not updated the website, documentation, or how to for mysqlsla, I am tossing mysqlsla v1.4 “out there” to view or wget at:

http://hackmysql.com/scripts/mysqlsla-1.4

I don’t like to release new versions without having everything else updated too, but for various reasons I’m making an exception this time.

mysqlsla v1.4 is much better than v1.3 and a lot has changed. So much has changed that the entire script was essentially re-written. Without an updated doc or how to, I suggest starting with the –help command line op, then contact me if you have specific questions or issues. I will, eventually, update the website, doc, how to, and officially package this new version.

With the start of the new year there are around 20 new job openings at MySQL.  There are worldwide positions available for web developers, software developers, support engineers, consultants, trainers, QA engineers and more.  Take a look at www.mysql.com/jobs for more information.

• MySQL: Jobs
• NewsForge: Finding an Open Source Programming Job

One of the new features in upcoming MySQL 5.1 Server will be the Event Scheduler. This feature was implemented by Andrey Hristov within the scope of a master's thesis for the University of Applied Sciences in Stuttgart. We incorporated his contribution into our code base - you can read more about this feature in this article from Peter Gulutzan. Andrey is now employed by MySQL AB as a Software Developer. Lenz Grimmer asked him if he would be interested in sharing his story and provide some more background about himself.

By Allison Randal

If you have the time to spare, it's definitely worth picking up a copy of Optaros' Open Source Catalogue 2007 (it's free). Most valuable is their perspective on current trends in the top-ranked projects: which are on the rise, steady, or on the decline. A few tasty tidbits:

The virtualization software XEN has positioned itself as a low cost and innovative alternative to commercial products such as VMWare. With every release stability and applicability are increasing.

And:

The databases MySQL and PostgreSQL (respectively EnterpriseDB as the professional derivate of it) have almost caught up with the benchmark of Oracle in terms of functionality and performance. In many situations they might even fit much better than the industry leader. …

What is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries which were taking milliseconds to stall for few seconds, especially in certain OS configurations, and on low profile servers (ie having only one disk drive) even if you just have one such query running concurrently.

Lets talk a bit how it is happening and how to prevent it.

Cache Wiping This is first reason for this to happen - query which crunches large amount of data set wipes data from your normal working set from OS cache. Operation Systems and MySQL Itself employs various strategies to attempt to minimize such effect but the truth is it still happens.

Disk Starvation As Cache efficiency drops more requests have to hit the disk, which may be 100% busy running your batch job query. This is especially bad when you …

I don't get it. I understand that Red Hat is a threat to operating systems companies everywhere, but I continue to find it highly ironic that its competitors proclaim cost savings for their customers...by shaving pennies from the least expensive part of the stack (the operating system).

Oracle did it with its "Unbreakable Linux." (Just picked up this shirt today, btw. Zack had it up on his blog and I thought it was too cool not to buy.) Oracle conveniently overlooked the fact that its database costs orders of magnitude more than the Linux the database runs on. If …