dcsimg

MySQL: Alive and Kicking

Despite some of the concerns floating around the MySQL Conference this week, there's some good news coming out of the event. The MySQL developers are returned to a "release early, release often" schedule and the pending 5.4 release has a number of features worth keeping an eye on.

The Seventh Annual MySQL Users Conference is being held this week in Santa Clara, CA. While much of the conference banter has focused on the prospects of the popular, open source database—the state of MySQL 6.0 and Oracle’s proposed acquisition of Sun Microsystems, the stewards of MySQL—two other announcements hold great promise for the immediate future.

The MySQL development team stated it will return to a “release early, release often” schedule; and the team announced that MySQL 5.4, a maintenance release of the shipping production software, will include a variety of optimizations to boost the performance of the database software on everything from vanilla gear to high-end, 64-processor machines.

Previously, iterations of the MySQL server software were offered on a timetable, with each scheduled release including only those features and repairs considered production-ready at the time. More recently, updates have been less predictable, because the team postponed each revision until a requisite set of features were implemented in entirety. While “release early, release often” is a considerable onus on the developers, it is nonetheless the prevalent model for open source software. Moreover, frequent maintenance releases deliver valuable, often essential, new code quickly and constantly. Hence, MySQL DBAs should be pleased with the new mandate to field features more promptly.

MySQL 5.4, now available for preview on Linux and Solaris systems and soon to be obtainable for all platforms, is a case-in-point: MySQL 5.4 includes some much-needed performance bumps and comes just a scant five months after the release of MySQL 5.1. (MySQL 5.1 is the current production-quality software. MySQL 6.0, the next major release, is in alpha, or very early testing.) As the release notes state, “The principal emphasis [for MySQL 5.4] is to improve scalability on multi-core CPUs. MySQL 5.4 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores.”

There are three notable tune-ups in MySQL 5.4. Two are patches, courtesy of Google, to increase throughput on multi-processor machines. The third, implemented by MySQL staff, hastens subqueries and joins.

Google Goodness

Google is a longtime user of MySQL, and its developers made a number of local changes to the database to speed it up on multi-core machines. (MySQL supports many different storage engines. InnoDB is one of the most mature of the engines and offers transactions and referential integrity.) Specifically, the patches (SMP Performance, InnoDB Async I/O, and InnoDB I/O Tuning) affect InnoDB, especially locking, memory management, and thread concurrency.

The InnoDB storage engine can now utilize more than four cores. 16-core x86 systems are supported, as are 64- or more concurrent multi-threaded (CMT) servers. Better yet, no SQL code changes are required.

According to Sun, the EAStress2004 benchmark, which models a typical web application and involves a significant number of create, read, update, and delete (CRUD) operations, showed MySQL 5.4 running 59% faster than MySQL 5.1. On Sun CMT configurations, benchmarks show MySQL 5.4 throughput increasing 71 percent, from 32 to 64 vCPUs.

Some of the acceleration must also be attributed to changes to the InnoDB I/O subsystem to make more effective use of available I/O capacity. And beyond enhancements, the InnoDB I/O subsystem is now configurable. You can change the number of input and output threads and regulate the I/O rate. For example, if your system uses solid-state disks, you can increase the I/O rate to capitalize on fast transfers.

Google also contributed two patches to improve the status display of the InnoDB engine. This output shows the condition of the I/O threads.

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
  reads 0 writes 0 requests 0 io secs 0.000000 io msecs/request 0.000000
  max_io_wait 0.000000
I/O thread 1 state: waiting for i/o request (log thread)
  reads 0 writes 3 requests 3 io secs 0.006906 io msecs/request 2.302000
  max_io_wait 6.861000
I/O thread 2 state: waiting for i/o request (read thread)
  reads 33 writes 0 requests 2 io secs 0.005925 io msecs/request 2.962500
  max_io_wait 5.895000
...
Summary of background IO slot status: 0 issued, 0 done, 0 claimed, sleep set 0

Robin Schumacher, MySQL’s Director of Product Management, claims, “MySQL 5.4 improves server utilization, handles more concurrent users, and provides 60 percent better price/performance.”

Join Me, Won’t You?

Subqueries are notoriously slow in MySQL 5.1 and older versions, so the changes in MySQL 5.4 are particularly welcome.

Schumacher provides this example.

SELECT COUNT(l_orderkey) FROM lineitem
  WHERE l_linenumber=1 AND
        l_orderkey IN
          (SELECT o_orderkey FROM orders
             WHERE o_totalprice > 1000 AND
                   o_custkey IN
                   (SELECT c_custkey FROM customer
                      WHERE c_address LIKE 'Le%'));

Here, the result of each parenthetical query, or subquery, forms a kind of ephemeral table for another query. This query asks, “Show me all orders of more than $1,000, with one item, placed by customers whose address begins ‘Le’.”

According to Schumacher, MySQL 5.1 performs this query in a glacial 720 seconds, while MySQL 5.4 runs the same query in 1.8 seconds with just 2 percent of the reads required previously. According to Schumacher, certain subquery operations can accelerate 99 percent.

Simple join operations have also been optimized. Here’s another example, one that would seemingly be quite common in an application.

SELECT COUNT(*) FROM part, lineitem
  WHERE l_partkey=p_partkey AND p_retailprice>2050
    AND l_discount>0.04;

Run on the same data set, MySQL 5.1 required 255 seconds; MySQL 5.4 took just 44 seconds. Again, this performance gain is seamless. Just install the new version of the database software and relaunch your application.

Mikael Ronstrom, the architect of MySQL 5.4, also reports general speed gains of 30-40 pecent, and a 200 percent increase on a certain benchmark.

Keep On Queryin’

MySQL 5.4 has a number of other improvements. It’s easier to build an efficient server executable from source code; some of the performance enhancements made to MySQL 6.0 have been backported to the release; and the team tweaked the code to leverage the capabilities of individual operating systems. For instance, you can now use DTrace with MySQL 5.4 on Solaris. The team also revised the default configuration parameters, so MySQL 5.4 should work better “out of the box.”

Refer to the release notes for the complete list and to learn how to tune the multi-processor settings for your hardware.

And buckle up. MySQL 5.4 aims to burn rubber. Or is it silicon?

Comments on "MySQL: Alive and Kicking"

I’m extremely impressed with your writing abilities as smartly as with the format for your weblog. Is that this a paid subject or did you customize it your self? Either way stay up the nice quality writing, it is uncommon to peer a great blog like this one these days..

Reply

I carry on listening to the news broadcast talk about receiving boundless online grant applications so I have been looking around for the top site to get one. Could you tell me please, where could i find some?

Reply

Hi! I’m at work browsing your blog from my new iphone 3gs! Just wanted to say I love reading through your blog and look forward to all your posts! Keep up the outstanding work!

Reply

MySQL: Alive and Kicking | Linux Magazine
[url=http://www.gf5hu7h1w0888js67164e6iy9a7sv7ahs.org/]uljlqefdj[/url]
aljlqefdj
ljlqefdj http://www.gf5hu7h1w0888js67164e6iy9a7sv7ahs.org/

Reply

Nice post. I used to be checking continuously this blog and I am inspired! Very helpful info particularly the ultimate part :) I deal with such info much. I used to be seeking this particular information for a long time. Thank you and best of luck. |

Reply

Because оf this, these people tribute for the company’s fabric and style of major business. Moncler jackets rapid sales their brands in the wintertime. There are people like different age groups, еsрeciаllу women. This сan bе the fashion industry admire. It looks like Moncler folly, onе of the biggest significance is focused on big men began to obtain that wear coats are juѕt like longer, but also people could make you lоok accepted. If yоu want to define yоur style and you havе the fashion sense tо know whіch names automatically acknowledged symbols of fashion, you’ll turn to moncler italia Hoody for men jackets.

Reply

Find find Coach handbag today and turn into іn style fоr a long time! You’ll ѕee whаt excellent bargains you can get on Coach handbags at An enthralling Gift.

Reply

Moncler Coat аre the most universal item of clothing ever created and are simple to be matched wіth a simple tank top. And this let many fashion men deeply provided to Moncler men jackets. Simple dressing though thе simple style! moncler outlet men jackets wіth normal jeans give men thе unnormal personality looking!

Reply

As, Goddess Durga is perhaps the most widely worshiped deity of Shakti, this puja marks the existence of Shakti as a symbol of tolerance and forbearance. Therefore, moving beyond human suffering requires moving beyond the ego.. It was developed by Pythagoras the ideal mathematician in which even now his theorems are nevertheless utilized inside contemporary mathematics..
Cheap NFL Jerseys

Reply

Fantastic items from you, man. I have take into account your stuff previous to and you’re just extremely excellent. I really like what you’ve acquired here, really like what you’re stating and the way wherein you are saying it. You are making it entertaining and you continue to take care of to keep it wise. I can’t wait to read much more from you. This is really a great site.

Reply

Flurry of Air Jordan 13 Doernbecher Children’s Hospital charity bring color official image, as a new member of the Air Jordan 13 shoes are hot, overwhelmed join DB series, with exquisite details bring rich people never get tired of boutique feel .
cheap retro jordans

Reply

Is that true? Ill spread this information. Anyway, good posting.
??

Reply

When some one searches for his necessary thing, so he/she wishes to be available that in detail, thus that thing is maintained over here.

Reply

?????????????????2017????????????????????001~100?%?20Hz~20kHz????????15?????????31?????????????????????????????????????????????

Reply

Except moncler outlet ,Moncler is alѕo оne brand offers mаny fashion style for men,mens Moncler jacket come i waѕ done differеnt style and fashions wіth vаrіouѕ features to suit іts customers. Coming fashionable for dіffеrent associated with occasion suсh aѕ casual, semi formal and formal, Moncler givеѕ уou assorted to choose with. Elegant and tasteful pattern Moncler аre іndеed remarkable. Sequence womens moncler coats hаѕ obtained іts identify aѕ onе pretty popular manufacturers, these kinds of aѕ winter jerkin and Moncler covers.

Reply

. By bringing the positive, focus on more of what-is-working approh of Appreciative Inquiry Team Building ideasOnce youe satisfied your shopping needs,Men’s Minnesota Vikings #4 Brett Favre Purple NFL Jersey, you can continue your fun with the nightlife No matter if you plan to buy and live in the property or to just make a fast profit from it,Men’s St.If a consensus arises about what needs to be done to improve so area….The ball sets used on Engli pool tables prise 7 red, 7 yellow plus 1 blk balls. This can be done by filling your life with love and joy.
Cheap NFL Jerseys

Reply

I feel that is one of the most important info for me. And i am happy studying your article. However should remark on some normal issues, The web site style is ideal, the articles is actually nice : D. Excellent job, cheers

Reply

Moncler jacket іѕ a product wіll get into style moncler outlet Coats considered most common clothes, cаn easily, and also simple jacket. This makes muсh fashionable man deeply like Moncler down applications. The simple style of dress, rather than easy! Moncler male with normal jeans jacket morbid personality to determine! A woman can make yоur fashion and Moncler jacket іѕ a variety of clothes, nеvеr outdated. After you get it, yоu will see that it is worth stumping up. Come аnd join us. You can still find knowing.

Reply

This jacket iѕ built-in Recco avalanche rescue thе reflector mоrе opportunities for aid aftеr аn influx. Additional benefits include dismantle powder skirt, hooks – аnd – loop cuffs, adjustable, adjustable hem pant – а – locks system along with a partner elevator clip. Get accustomed to сhаngeѕ and north face winter jacket, reversible lively frоm khaki north encounter. This jacket is a technically piumini moncler advanced reversible jacket made уоu associated with warm or cooler with rеgards to hоw you wear it again. When the оld silver side out, the reversible vagaries оf jacket reflects almоst 40 to half of sunlight absorption thаn black side.

Reply

It’s easy to gо any large regarding items. How many handbags hаѕ Louis Vuitton designed over the years? The figure must be close tо a hundred, by incorporating оf them lіke traditional sour cream party Speedy or Neverfull still іn bloodstream circulation. This means could possibly find it tough tо compare items promote a choice, partіcularlу if уou are unsure as to whісh bag to get.

Reply

Hey there! This is kind of off topic but I need some guidance from an established blog. Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about creating my own but I’m not sure where to begin. Do you have any tips or suggestions? Many thanks

Reply

Durga Puja is widely commemorated in the Indian states of West Bengal, Assam, Jharkhand, Orissa and Tripura. As Joseph Campbell said, “Follow your bliss.Oh yes, a very nice evening atmosphere, very inviting!April 5, 2014 at 5:24 AM cieldequimper said.April 5, 2014 at 8:47 PM Bibi said. Your ladies handbag collection provides you with long lasting design and sturdiness.
Wholesale Jerseys

Reply

Shop for NBA jerseys at the official NBA Store! We carry the widest variety of , and Replica NBA basketball jerseys online. Browse for your favorite team or player, for Swingman NBA Jerseys, and youth sizes. Keep checking back for the arrivals of the NBA Nike Jersey!
Official NBA Team Jerseys

Reply

hi
it’s good posting.
???

Reply

Air Jordan 5 “Toro” Raging Bull in red overall experience, coupled with the classic 3M reflective silver tongue, became the classic Air Jordan 5 shoes type.
jordans for cheap

Reply

The problem isn’t that Whip is known for a character for the show, noticeably Whip and even his family unit lost ones own main income source due with the BP gasoline spill.
onitsuka tiger ultimate 81 white red blue green

Reply

”????????????????????????????????????????????????????????????????????????????????????????????2.

Reply

Air Jordan 11 Low shoes most amazing color, black and white dress Concord bring a familiar image, but the patent leather material part to the full texture carbon fiber texture rendering.
cheap air jordans

Reply

Unquestionably imagine that that you stated. Your favourite reason appeared to be at the net the easiest factor to take note of. I say to you, I certainly get irked at the same time as other folks think about concerns that they just do not understand about. You controlled to hit the nail upon the highest as neatly as defined out the whole thing with no need side-effects , people can take a signal. Will probably be again to gt more. Thanks

Reply

This can be done by filling your life with love and joy. Opera glasses would make it a little easier for those sitting in the cheap seats to see what was happening on stage and see what the actors actually looked like. Sothing as easy as this may give we pee of mind, recognizing which the kids are protected from sothing potentially harmful.. REAL leaderip is required to guide, empower and inspire teams to greater performance in a sustainable way.
Cheap Jerseys From China

Reply

clipress. Every of the 78 Tarot cards has o anings, one when the card appears right-side up, plus another whenever the card appears upside-down, or “reversed. Still, there will be thousands of brand-new videos we have not explored. v . I wi :)p.
Cheap MLB Jerseys China

Reply

Wow, this piece of writing is good, my sister is analyzing these kinds of things, thus I am going to let know her.

Reply

Shop for NBA jerseys at the official NBA Store! We carry the widest variety of Cheap Jerseys Basketball., and Replica NBA basketball jerseys online. Browse for your favorite team or player, for Basketball Jerseys, and youth sizes. Keep checking back for the arrivals of the NBA Nike Jersey!
Basketball Jerseys

Reply

I do consider all the concepts you have introduced in your post. They’re really convincing and can definitely work. Nonetheless, the posts are very quick for novices. May just you please prolong them a bit from subsequent time? Thank you for the post.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>