Wednesday, August 12, 2009

Great performance effect of fixing broken group commit

Yesterday InnoDB Plugin 1.0.4 was released by Innobase. This version contains one of the most important performance fixes - "Fix for broken group commit". After MySQL5.0, InnoDB breaks group commit when using with binary log (or with other transactional storage engines), even though setting innodb_support_xa=0. This was really serious because fsync() (called at transaction commit when setting innodb_flush_log_at_trx_commit=1) is very expensive. The initial bug report about this was submitted by Peter four years ago. Last year David Lutz submitted a bug report with his prototype patch. It is great news that this bug fix has been finally implemented in the official InnoDB release.
I did a simple benchmarking by mysqlslap. mysqlslap has functionality to run concurrent inserts from multiple connections. The result is as follows.
mysqlslap --concurrency=1,5,10,20,30,50 --iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-load-type=write \
--number-of-queries=100000


H/W is Sun Fire X4450, Intel Xeon X5560 Nehalem 2.80GHz * 16cores, 12GB RAM, SAS HDD with write cache. my.cnf configuration is as follows. log-bin is enabled.

[mysqld]
basedir=/usr/mysql5137
datadir=/data/mysql5137/data
ignore_builtin_innodb
plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;
innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;
innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_innodb.so;
innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so
innodb_log_files_in_group=2
innodb_buffer_pool_size=2G
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_data_file_path=ibdata1:500M:autoextend
innodb_file_per_table
log-bin
table_cache=8192

Apparently InnoDB Plugin 1.0.4 outperforms normal InnoDB (6.1 times faster on 30 connections, innodb_support_xa=1). Normal InnoDB doesn't scale with connections but InnoDB Plugin 1.0.4 does. What is the difference? Normal InnoDB does the following at transaction commit.
pthread_mutex_lock(&prepare_commit_mutex)
writing into InnoDB logfile for prepare, then fsync
(skipped if innodb_support_xa=0)
writing into binlog
writing into InnoDB logfile for commit, then fsync
pthread_mutex_unlock(&prepare_commit_mutex)

Under the critical section protected by prepare_commit_mutex, only one thread can do operation. So when 100 threads do commit at the same time, fsync() is called 100 times for prepare, 100 times for commit (200 in total). Group commit is totally broken. As you see the above graph, innodb_support_xa=0 is effective (though it still breaks group commit), but in general innodb_support_xa=0 is not recommended because it will break consistency between binlog and InnoDB in case of a crash.
In InnoDB Plugin 1.0.4, the behavior has changed as follows.
writing into InnoDB logfile for prepare, then fsync
(skipped if innodb_support_xa=0)
pthread_mutex_lock(&prepare_commit_mutex)
writing into binlog
writing into InnoDB logfile for commit
pthread_mutex_unlock(&prepare_commit_mutex)
fsync to the InnoDB logfile

fsync, the most expensive operation, is called outside the critical section, so group commit is possible and concurrency is much more improved. The following graph shows how much Innodb_data_fsyncs was increased after executing mysqlslap(committing 100,000 transactions).


In 5.1.37+Builtin(support_xa=1), 2 fsyncs happens per transaction, regardless of # of concurrent connections. In 5.1.37+Builtin(support_xa=0), 1 fsync happens per transaction, regardless of # of concurrent connections. These mean group commit is broken. In both cases about 10,000 fsyncs were executed per second, which seems upper limit for regular HDD with BBU. On the other hand, InnoDB Plugin 1.0.4 greatly reduces the number of fsyncs(i.e. 200251 to 26092 on 30 connections(innodb_support_xa=1): 87% decreased). This shows group commit works well.

Write ordering between binlog and InnoDB logfile is still guaranteed. Write ordering for InnoDB prepare is not same as the ordering of binlog, but this is fine. Prepared entries are used only for recovery and not visible to applications. When doing crash recovery, mysqld reads binlog at first(picking up xids), then checking prepared but not committed entries(xids) in InnoDB logfile, then applying these entries in the order of binlog xids. So in the end write ordering is guaranteed.
Note that if you set sync_binlog=1, it is still very slow because writing into binlog is protected by mutex (prepare_commit_mutex and LOCK_log).
This is my understanding. InnoDB Plugin 1.0.4 also has other awesome features (i.e. multiple i/o threads) so it's worth trying.

70 comments:

Stewart Smith said...

Awesome news. We're really hoping to merge the new plugin into Drizzle very shortly too.

Sildenafil Citrate said...

when I tried InnoDB Plugin 1.0.4 I did notice the great improvement because I was using the other old versions and in this one the major bugs that were a headache int the previous releases were fixed and I was very happy about it!

Handwriting analysis said...

This is a very interesting perspective, however I think judging someone's ability to do a job solely based upon their handwriting is a little unfair. I know my handwriting changes depending on what I am writing. I also think the fact that pretty much nothing is written by hand today; from a college to an email, that this can affect people's handwriting for the worse.

Handwriting analysis said...

This is a very interesting perspective, however I think judging someone's ability to do a job solely based upon their handwriting is a little unfair. I know my handwriting changes depending on what I am writing. I also think the fact that pretty much nothing is written by hand today; from a college to an email, that this can affect people's handwriting for the worse.

Sunburn Treatment said...

Thanks for clearing that up!

Estrada said...

interesting! although I have no expert, but I want have to know more and more, on your blog just interesting and useful information. Keep it up!

get rid of genital warts said...

Hrmm that was weird, my remark obtained eaten. Anyway I wished to say that it’s nice to know that another person also mentioned this as I had bother finding the identical information elsewhere. This was the first place that instructed me the answer. Thanks.

Sagging skin said...

You had post some pretty interesting topic here.Thanks for the information.Hoping more posts from you.Natural laxative foods Stomach gas Hard stool treatment Health benefits of almonds White spots on skin
Cure ear infection

Allen Smith said...

Thank you for another implausible blog. The place else could anyone
depression treatment get that sort of info written in such an ideal way? I've a presentation that I am simply now engaged on, and I have been on the look out for such information.

android tablet said...

Good post. Very impressive. Thanks for sharing.

website directories said...

I hope you have a nice day! Very good article, well written and very thought out. I am looking forward to reading more of your posts in the future.

WOW Gold said...

A simple example of how networks can be this easy example of a woman who used to love writing articles.
Eden Eternal
Eden Eternal Gold
Eden Eternal Review

Hcg weight loss said...

The post is very nicely written and it contains many useful facts. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement. Thanks for sharing with us.

hermes birkin said...

Thank you for another essential article. Where else could anyone get that kind of information in such a complete way of writing? I have a presentation incoming week, and I am on the lookout for such information.
Hermes replica
hermes birkin replica

Corporate Entertainment said...

Hi, nice post. I have been wondering about this topic,so thanks for sharing. I will certainly be subscribing to your blog.

RS Gold said...

Thanks for another fundamental article. Wherever more may possibly anyone have that types of information and facts in their normal complete way with words? I own a business presentation arriving weeks time, and i'm looking for similarly info.

http://fashoionstyles.com/
http://warcraftever.com/

wow gold said...

Böylelikle Türkiye ,Avrupa ve Orta Doğu'da toplam 1000'e yakın noktada tüketicileri ile buluşma imkanı sağladı.

Thomas said...

A very nice informational blog.Keep on making such important blog post.Your work is really being appreciated by some one.

cinema arts said...

I will bookmark this site and visit again. It is very informative. Thanks for sharing.

lace wedding dress said...

A holy church, rings, a bouquet, a 3-floor wedding cake, lace wedding dress champagne, and the moving promise “ I do”, make up the happiest moment in one’s life. Then it is the wedding.
lace wedding gowns
A gorgeous wedding dress has been inside a girl’s dream since she was five years old. It is widely said that the bride is the most beautiful woman all over the world.
Bridal gowns vary in different parts of the world. For example, cheap wedding dresses plus sizein traditional Chinese culture, the color red is regarded for centuries as the symbol of good luck; while in the Occident, wedding dresses 2012 a full-length white wedding dress is always associated with romance and sanctity. But, with the globalization, wedding dresses tend to be alike in the whole world.

get girlfriend give blowjob said...

Excellent post. I want to thank you for this informative read. I will bookmark this site and visit again...

Runescape Gold said...

Thank you for sharing. Glad to see you.It is really a good post.http://tryplaywow.com/

instantinsurance said...

These kind of post are always inspiring and I prefer to read quality content so I happy to find many good point here in the post, writing is simply great, thank you for the post Reed

learn arabic alphabet said...

Excellent post. I want to thank you for this informative read. I will bookmark this site and visit again...
ljubavni stihovi

loperamide said...

My friend uses this plugin, it seems he's quite satisfied with it.

discountcoupon said...

I like this post !
It pprovides not only the details about things but also posting the pictures which can make the data easy to understand!

I am a Promo Codes girl !
I like every Online Coupon Code in lifetime shopping

i need a blowjob said...

I want to thank you for this informative read. Loved the whole article! Thanks for sharing.
jacks blowjob persuasion
how to get a girl to suck your dick
get girlfriend to deepthroat
convince girlfriend facial
how to get more blow jobs
i need a blowjob
wife wont suck my cock
make swallow cum
jacks blowjob persuasion review

Alex Edward said...

The most significant challenges of which anybody encounter can be justifying what one particular might like to do and the reason it will break up the standing quo -- whether it is to give thoughts and opinions or commencing some sort of converstaion. Promoted aids whenever reading the product was adament on the subject you have highlighted. Very good tunning the idea people talked about will certainly deliver brand new chances in this area regarding fascination in addition to brings will probably result one thing constructive. When i enjoy your time and energy regarding acquiring see connected with earlier mentioned talked about subject. 17 Again Jacket

Learn Arabic Language said...

it certainly want set of earphones which might be cozy in addition healthy head phones You

<a href="http://jailbreakstation.com/>http://jailbreakstation.com</a>

Learn Arabic Language said...

certainly want set of earphones which might be cozy in addition healthy head phones You

<a href="http://www.scissorsart.com/category/celebrity-hairstyles>http://www.scissorsart.com/category/celebrity-hairstyles</a>

steve7876 said...

is certainly want set of earphones which might be cozy in addition healthy head phones You

<a href="www.islam-australia.com/convert-to-islam/>http://www.islam-australia.com/</a>

steve7876 said...

very certainly want set of earphones which might be cozy in addition healthy head phones You <a href="http://www.festivalstagehireuk.co.uk/>http://www.festivalstagehireuk.co.uk/</a>

steve7876 said...

ery certainly want set of earphones which might be cozy in addition healthy head phones You <a href="http://www.writersdrive.com/SpellChecker.html>writersdrive.com/SpellChecker.html</a>

Daniel Anthony said...

The great performance effect for fixing is described here, useful post

Corporate Events

Vinod Yadav said...

I like this “Dairy of a Gossip" very much. I always get something new from your updated blogs. Please explain in detail about "Watermill" that you are telling. Thanks for this.
unblocked games

manoj sing said...

Thanks God for StumbleUpon; otherwise I would never have found this amazing work.unblocked games at school

jism singh said...

this is NUTS! I love this, a HUGE big up to the artist! play unblocked games at school

Manoj Yadav said...

I would
love to write and say what a great job you did on this, as you have put a lot
of work into it.Unfortunately, I'm not sure how to leave a comment....phoenix carpet cleaning water softeners
merchant Cash advance
local seo
garage door repair phoenix

Robert Johnson said...

Glad to see your post. It is very helpful for me and my friends to workout. Thanks for posting this kind of information.
leon kennedy jacket

jism singh said...

Very nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts.
san diego human resources
cheap apartments in mesa az
cheap apartments in mesa az

Julia Carney said...

It's gracefull to see what i was searching for. That's the material which I was searching about. The matter you've highlighted above is surely valueable for anyone to workout. the concept of your article is very true and It will result in a positive way. Thanks you very much for sharing this with us.
swarovski crystal bag

Dentist Seo said...

These kind of post are always inspiring and I prefer to read quality content so I happy to find many good point here in the post.
Local SEO

Rosalinda Ursery said...

Glad to read your post. The topic that you discussed above is truely very useful. It is really help for anyone to workout. I like it and i will recommend it to others. Thanks for sharing this type of information. Good luck for the future posts.
click here

BERITA said...

Great stuff from you, man. Ive read your stuff before and youre just too awesome. I love what youve got here, love what youre saying and the way you say it. produk kecantikan

Clover Rayan said...

Entirely an awesome content. People do the wonderful job. Thanks a lot when planning on taking this desire linked to mentioned before assessed design. We have been satisfied that people identified people content as well as the details that you discuss from your content within just will be great. These types of records they may be handy for those who. Maintain the excellent perform going.
wolverine jacket

niki 3254 said...

The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept.school of business and manangement I really loved reading your blog. It was very well authored and easy to understand. Unlike additional I have read which are really not tht good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it Airport Parking as well!Thanks so much for this! I have not been this thrilled by a blog post for quite some time! You’ve got it, whatever that means in blogging. Anyway, You’re definitely someone that has something to say that people should hear. Keep up the wonderful job. Keep on inspiring the people.meet and greet

Promo Disc said...
This comment has been removed by the author.
Maria Arredondo said...

The past is gone and static. Nothing we can do will change it.scarlet blade gold, the future is before us and dynamic. Everything we do will affect it rs gold, You laugh at mescarlet blade gold for being different , but I laugh at you for being the same.

Maria Arredondo said...

Empty what's full. Scratch where it itches. Don't go around saying the world owes you a living rs gold, The world owes you nothing. It was here first. Being happy doesn't mean that everything is perfect Runescape Gold, It means that you've decided to look beyond the imperfections. Do not pray for tasks equal to your powersrs gold.

Robin said...

Hi there! I just want to give a huge thumbs up for the very good data you can have right here on this post. I will likely be coming again to your weblog for much more soon.
Credit Bureau

buyGuildwars2gold said...

If you lose your way, your forever friend guides you and cheers you on Diablo iii Gold, if you turn and walk away, your forever friend follows Buy Diablo iii Gold, your forever friend gets you through the hard times, the sad times, and the confused times Cheap Diablo iii Gold.

buyGuildwars2gold said...

At twenty years of age, the will reigns; at thirty, the wit; and at forty, the judgment c9 gold, Fear not that the life shall come to an end, but rather fear that it shall never have a beginning c9 online gold, do not pray for tasks equal to your powers.Pray for powers equal to your tasks.Then the doing of work shall be no miracle, but you shall be the miracle buy c9 gold.

de wo said...

If you do well in it, it will significantly decrease your tanking cheap wow gold and DPS abilities, particularly at higher levels. While you can convert, your power in these shapes will be obviously reduced without the rs gold talents in the feral tree.

steve7876 said...

twenty years of age, the will reigns; at thirty, the wit; and at forty, the judgment c9 gold, Fear not that the life shall come to an end, but rather fear that it shall never Colorado Springs Auto Service

adeoe said...

Under the critical section protected by prepare_commit_mutex, only one thread can do operation. So when 100 threads do commit at the same time, fsync() is called 100 times for prepare, 100 times for commit guild wars 2 gold (200 in total). Group commit is totally broken. As you see the above graph, innodb_support_xa=0 is effective (though it still breaks group commit), gw2 gold but in general innodb_support_xa=0 is not recommended because it will break consistency between binlog and InnoDB in case of a crash.

Yacht Transport said...

the will reigns; at thirty, the wit; and at forty, the judgment c9 gold, Fear not that the life shall come to an end, but rather fear
Dymo labels

Yacht Transport said...

we are the best herbal incense online wholesaler and we have been dealing in Herbal incense online sale from last 10 years. we can provide you excellent quality of Herbal incense and spice incense. Herbal Incense is legal in all states and you will surely enjoy it.
underground Herbs

Cahaya Mandiri said...

Great Post. I have not been visiting the site recently. Took a visit again and there were some great comments on the site. Excellent post. Keep up the good work.
tips cara agar cepat hamil l CARA BELAJAR BAHASA INGGRIS l the best acne treatment l how to lose weight fast easy
margahayuland l BISNIS ONLINE l tips cepat hamil l how to get rid of acne home remedies l
home remedies for acne l how to cure acne fast l
baju batik modern l toko sepatu online l grosir jam tangan online l
jual jam tangan l toko jam tangan murah peluang usaha online l is acne no more for you l how to get rid of acne naturally
how to clear acne l cure acne naturally
best natural remedies for acne l acne no more l tempat belajar bisnis online
peluang usaha rumahan l cara mendapatkan uang dari internet
makanan sehat agar cepat hamil l penyebab tidak bisa hamil lcara agar cepat hamil


steve7876 said...

It's such kind of article is really amazing, I read your blogs every day and give my announcement for that here this article is too great and so entertaining. More information

shahbaz said...

Looking forward to ones more details posted within this, deliver that you present, probably eventually a person or your buddies incorporate the use of.Home page

steve7876 said...

Excellent understand personally, This is just what We needed. Cheers regarding expressing this excellent write-up! That's very intriguing Smile I like reading in addition to We are always trying to find beneficial information similar to this! to visit on Dymo labels online

wow9gamer said...

I did a simple benchmarking by mysqlslap. mysqlslap has functionality to run concurrent inserts from multiple connections. The result is as follows.LOL Elo Boost
Buy League of Legends Boost

Tim Sentraloker said...

I appreciate you for giving this data. The knowledge was very assistance and also saved many my own time. This tends to definitely about to help me to during my responsibility .
http://sentraloker.net/lowongan-cpns-2013-kemenkeu.html

Elizabeth J. Neal said...

I don't know what it is about this blog that turns me off so much, but you just don't seem to get me excited. I don't know if it’s the lack of content or just the way you wrote it. But you really don't seem to understand that your readers may not agree with you. You're really just too out there for me. icons

Elizabeth J. Neal said...

This is my first an opening to analyze out your website and I found very helpful minutia you finished a spectacular job, hold more posting like this. http://www.thephotongod.com

Aura Valensia said...

you did nice work your post is awesome its increase my knowledge.the post is best i can never read before this type of post nice sharing. keep it up serum korea | Kaminomoto | pembesar payudara kitchen set murah | obat gemuk | magic plus white cream | cream walet | penghilang bekas luka | sabun susu beras | pemutih wajah
perawatan rambut | minyak bulus | produk kecantikan

Blog said...

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.

sadia sulaman said...

Excellent read, Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style.www.claimsid.co.uk |

www.positiveimagebank.co.uk |

www.bestweddingphotography.co.uk |

www.pauljamesconsulting.co.uk |

www.careers-at-benefit.co.uk |

www.tibrainvestmentmanagement.co.uk |

www.lucy-kent.co.uk |

www.cbd-research.co.uk |

www.savesolihullhospital.co.uk |

www.envirotrack.co.uk |

doglas markel said...

The particular Payday/Installment Loan from the bank will enable you to in conjunction with men and women unforeseen fees or maybe this type of unforeseen out of the blue imperative situation. should you've got getting some type of bills that may can’t wait till your current following day nstallment loan joliet enhance arrives, are purchased in to be able to America day cash advances and in addition utilize variety of our Payday/Installment Loan from the bank therefore on be traceable merely what proportion therefore on be wise.

Calvin Ewers said...

I like this “Dairy of a Gossip" very much. I always get something new from your updated blogs. Please explain in detail about "Watermill" that you are telling. Thanks for this. cheap disneyland vacation packages 2014

Post a Comment