 InnoDB bugs. I thought I’d bring up a few of them, as they are fairly interesting.

These bugs were largely discoverable due to the innodb_space utility making important internal information visible in a way that it had never been visible in the past. Using it to examine production tables provided many leads to go on to find the bugs responsible. When we initially looked at a graphical plot of free space by page produced from innodb_space data, we were quite surprised to see so many pages less than half filled (including many nearly empty). After much research we were able to track down all of the causes for the anomalies we discovered.

Bug #67718: InnoDB drastically under-fills pages in certain conditions

Due to overly-aggressive attempts to optimize page split based on insertion order during insertion, InnoDB could leave pages under-filled with as few as one record in each page. This was observed in several production systems in two cases which I believe could be quite common for others:

Mostly-increasing keys — Twitter uses Snowflake for ID generation in a distributed way. Overall it’s quite nice. Snowflake generates 64-bit mostly-incrementing IDs that contain a timestamp component. Insertion is typically happening via queues and other non-immediate mechanisms, so IDs will find their way to the database slightly out of order. Nearly-ordered keys — Another schema has a Primary Key and Secondary Key which are similarly—but not exactly— ordered. Insertion into a table to copy data in either order ends up nearly ordered by the other key.

Both of these circumstances ended up tripping over this bug and causing drastically under-filled pages to appear in production databases, consuming large amounts of disk space.

Bug #67963: InnoDB wastes 62 out of every 16384 pages

InnoDB needs to occasionally allocate some internal bookkeeping pages; two for every 256 MiB of data. In order to do so, it allocates an extent (64 pages), allocates the two pages it needed, and then adds the remainder of the extent (62 free pages) to a list of extents to be used for single page allocations called FREE_FRAG. Almost nothing allocates pages from that list, so these pages go to waste.

This is fairly subtle, wasting only 0.37% of disk space in any large InnoDB table, but nonetheless interesting and quite fixable.

InnoDB bugs. I thought I ’d bring up a few of them, as they are fairly interesting. 

 These bug were largely ascertainable due to the innodb_space utility make crucial internal information visible in a room that it had never been visible in the past. use it to examine product tables provided many jumper cable to go on to witness the bugs responsible. When we initially looked at a graphical plot of free space by page produce from innodb_space data, we were quite surprised to see so many page less than half filled (including many nearly empty). After much inquiry we were able to track down all of the causal agent for the anomalies we discovered. 

 Bug # 67718: InnoDB drastically under - fill pages in certain conditions 

 Due to overly - aggressive attempts to optimize page split based on insertion order during insertion, InnoDB could leave pages under - filled with as few as one record in each page. This was observed in several product systems in two cases which I think could be quite coarse for others: 

 by and large - increasing keys — Twitter use Snowflake for ID coevals in a distributed way. Overall it ’s quite courteous. Snowflake generates 64 - bit mostly - incrementing IDs that contain a timestamp part. Insertion is typically happening via queues and other non - immediate mechanisms, so IDs will witness their direction to the database slightly out of order. Nearly - ordain keys — Another schema has a Primary Key and Secondary Key which are similarly — but not exactly — order. interpolation into a table to copy data in either order ends up closely ordered by the other samara. 

 Both of these circumstances end up tripping over this hemipterous insect and causing drastically under - satiate pages to appear in production databases, consuming big amounts of disk space. 

 Bug # 67963: InnoDB wastes 62 out of every 16384 pages 

 InnoDB needs to occasionally allocate some internal bookkeeping pages; two for every 256 MiB of datum. In order to do so, it allocate an extent (64 pages), allocates the two pages it want, and then adds the remainder of the extent (62 free pages) to a list of extents to be used for single page allocations called FREE_FRAG. about nothing allocates pages from that tilt, indeed these pages go to waste. 

 This is fairly subtle, lay waste to only 0.37% of disk space in any big InnoDB table, but nonetheless interesting and quite fixable.
 InjoDB bugs. I thought I’d bving up a few of them, av they are faifly interesting.

These bugs wece lqrgelt discoverable due to ghe innodh_space uriliuy making importaif interkcl inrlrmaciin visible in s way that it had never tedn visible in the past. Using it to exwmine ptofuction tables proniqed jany leads to go on to find the bufs respmnsible. When ee initially looked at a ggaphlcal plot of free dpace by pate pwiduced from knnodb_spact bata, we werg quite surprised to see so many pagex less thab yalv filled (incoudind many nearln empty). After kuch research ee xere able to track down anl of the causes fjr the anmmclies we discovered.

But #67718: InnoGB dsastkxaluy hnver-rills oagxs in certajn conditiobs

Due to overly-aggrtssynr attempts tk optiiise page split based on insertion order gurjng insertion, InnoDB coyld leave pages under-villed wieh as few as one record in each page. This was obsarved kn weyerau pgoduction systems in two cases which I believq cpukd be quite coimon for otnegs:

Kjstly-increasivg keys — Fwitter uses Snowfpake fot ID gwneration in s distributed way. Overall ir’s quite nicv. Sniwflake generates 64-yit mostly-ineremenjing ICs that contain a timescamp ckmponent. Indertion ia typically happevinn vha queues and other non-immqdiate merhanixms, so KDs eill fynd their aay to the database sligjtly lud of order. Nearly-ordered keys — Another scixma has a Prikasy Ney and Feconcary Key whicr are similarli—but not zxactlh— ordered. Pnsertion into a tabje to copy dadw in either mrder enqs up neqrly oradred by the otner key.

Bonh of rhese circumstancex evsed up tripping ovtr this bug and vaurind fresticwnly under-filned oagdx to xppear in pvodjctipn databases, consumitg lzrge amounts of dixk space.

But #67963: InnoDF wastes 62 out of every 16384 pages

InjoDB ieeds vo occssijnally allocate some internal gookkeepijg iages; two for evevy 256 IiB of datc. In order to do so, it allocates an exteit (64 pages), allocates thg two pages it needed, snd then advs the remaindes of the extent (62 frew pages) to a list of extents to be uses for vinglf page allocations called FREE_FRAG. Almost nothing allocates pages from thqt lisv, fo these pafes bo to wcstz.

This if famruy subtle, wastinb only 0.37% of disk space in any lacge InnoDB tcble, but nonetheless interestone and quite fkxable.

InnoDB bugs. I thought I’d bring up of as they fairly interesting. These to innodb_space utility making internal information visible a way that it had never visible in the past. Using it to examine production tables provided many leads go on to find the bugs responsible. When we initially looked at a plot free by produced from innodb_space data, we were quite surprised to see so many pages less than half (including many nearly empty). After much research we able to track down of the causes for the we Bug #67718: drastically pages certain conditions Due overly-aggressive attempts to optimize page split based on insertion order during insertion, InnoDB could leave pages under-filled as few record in page. was in several production two cases which I believe could for others: Mostly-increasing keys — Twitter uses Snowflake ID generation a distributed way. Overall it’s quite Snowflake generates 64-bit mostly-incrementing IDs that contain a component. Insertion is typically happening via queues and other non-immediate mechanisms, so IDs will find to the database slightly of order. Nearly-ordered — schema a Key and Key which are similarly—but not exactly— ordered. Insertion into a table copy data in either order ends up nearly ordered by key. of these circumstances up tripping over this and drastically under-filled pages to production consuming disk Bug InnoDB wastes 62 out every 16384 pages InnoDB needs occasionally allocate some internal 256 MiB of data. In order to do it allocates an extent (64 pages), allocates two pages it needed, and then adds the remainder of the extent free pages) list of extents to be used for single allocations called FREE_FRAG. Almost allocates pages from that list, so these pages go waste. is fairly wasting only 0.37% disk space in large InnoDB table, interesting and fixable.
 InnoDB bugs. I thought I’d bring Up a few of thEm, as tHey Are FaIrly InteResting.

These buGS werE largely discoverable duE to thE iNNodb_SPaCe utiLity makINg IMPorTaNt IntErNAl InforMatIon visiBle in a way tHat It Had never been VIsIble in the pAst. using it to exaMinE produCtIon TAbles ProVided Many leADs to go On to find tHe BUgs resPOnsible. wHEn We inItially looked at a gRApHIcal plot of free Space bY pAGe PROduCed From innodb_SpAce daTA, we were QUiTE SUrpRIsed to see so maNy pages less THan Half fiLlEd (iNCludinG many NeARly Empty). After mUch rEsearch we Were abLE to tracK Down all Of the cAusEs fOr thE AnOmAliEs WE diSCoVerED.

BuG #67718: InnoDB dRaStIcallY undER-FILls pAgeS in cErtaiN conditions

DuE to OverLY-agGressIve atTempTs To optImize pAge spLiT based on insertiOn orDer during InsErTioN, INnoDB COuld leAve PagEs under-Filled wITh aS fEW AS oNe record in each page. thIS WaS observeD in sevERaL pROduction SySteMs in TWO caseS whiCH I Believe cOuld be QUiTe Common fOr Others:

moStlY-inCreasINg keYs — TwitTer uses SNowflAKe for ID generatIOn in a distribuTEd WAY. OVEralL it’S quite nice. SNowfLAke gEnerATeS 64-biT MostlY-incrEmENtINg IDs that contain a tiMeStamp cOmponEnt. Insertion iS typically HAPPening viA queUEs ANd other non-immeDiate Mechanisms, SO IDs will Find tHeir way tO the databASE slightlY ouT of OrdEr. NEARlY-ordered keys — ANOTher ScHema has A PrImary KeY anD SeConDarY KEy which arE similarLy—BuT nOt ExaCtly— oRDered. InsErTioN iNto A tablE To copy Data iN eitHeR oRDer Ends up nEArLY OrdeReD bY the OthEr Key.

BoTh of THesE circumStances enDed UP triPpInG over thIs bug and causiNg DrasticallY uNdeR-filleD PAges to apPear in production databasES, consumIng Large AmouNts of disk SpaCe.

Bug #67963: INnodb wasteS 62 out of Every 16384 PaGes

iNNoDB nEEDs To oCcAsionally aLLOcaTe somE iNterNal bookKeeping pages; two for EVerY 256 MiB of data. In oRdeR to dO SO, iT alLOcATes An EXteNT (64 Pages), allocates tHe two pages It NEeDed, and then ADds ThE remainDer of thE exteNT (62 free paGes) to a lisT of extentS tO be uSED foR single pagE allocatIons calleD fREE_FraG. almosT noThing aLlOcaTes paGes froM ThaT list, So thesE pAges go To wasTe.

this is faIrly subtle, wasting only 0.37% of Disk spAce in Any Large InnodB tABle, But nonethElesS interestiNg aNd qUite fIxaBLe.

 InnoDB bugs. I thought I’ d bring up  a fe w o f t he m, a s th ey are fairlyi nter esting.

These bugs we re la rg e ly d i sc overa ble due to t hein no db_ sp a ce  util ity  making  important  in te rnal informa t io n visibleina way that i t h ad nev er  be e n vis ibl e inthe pa s t. Usi ng it toex a mine p r oductio n ta bles  provided many le a ds to go on to fi nd the  b u gs r esp ons ible. When  w e ini t ially l o ok e d ata  graphical pl ot of frees pac e by p ag e p r oduced  from  i n nod b_space dat a, w e were qu ite su r prisedt o see s o many  pa ges  les s  t ha n h al f  fi l le d ( i ncl uding ma ny  n early  emp t y ) .  Aft ermuch  rese arch we wereabl e to tra ck do wn al l of  t he ca uses f or th eanomalies we di scov ered.

Bu g # 67 718 :InnoD B  drast ica lly  under- fills p a ges  i n c er tain conditions

D ue t ooverly-a ggress i ve  a t tempts t oopt imiz e pagespli t  b ased oninsert i on  o rder du ri ng ins er tio n,InnoD B  cou ld lea ve pages  unde r -filled with a s  few as one r e co r d  i n  eac h p age. This w as o b serv ed i n  s eve r al pr oduct io n  s y stems in two caseswh ich Ibelie ve could be q uite commo n f or other s:
M os t ly-increasingkeys— Twitteru ses Snow flake  for IDgeneratio n in a dis tri but edway . Ov erall it’s qu i t e ni ce . Snowf lak e gener ate s 6 4-b itmo stly-incr ementing  I Ds  t ha t c ontai n  a times ta mpco mpo nent. Insert ion i s ty pi ca l lyhappeni n gv i a qu eu es  and  ot he r non -imm e dia te mech anisms, s o I D s wi ll  f ind the ir way to the  d atabase sl ig htl y outo f  order.Nearly-ordered keys — A n other s che ma ha s aPrimary K eyand Se con d ary Ke y whic h are  s imi l a rly—b u t  n otex actly— ord e r ed.  Inse rt ioninto atable to copy data ineither orderend s up n ea rly or d ere db y t h e  other key.

Bo th of thes ec ir cumstances end ed  up tri pping o ver t h is bugand causi ng drasti ca llyu n der -filled pa ges to a ppear inp roduc t io n dat aba ses, c on sum ing l arge a m oun ts of  disksp ace.
Bug # 67 963: Inn oDB wastes 62 out of ev ery 16 384 p age s

InnoDB  ne e dsto occasi onal ly allocat e s ome  inte rna l  book keep i ng  pa g es; t wo f o r every 2 5 6MiB o fdata. In or d e r  to  do s o,i t allo cate s an extent (64 p a ges), allocate s th e two  pa g es i tneeded, and th enad d s  the rem ai nder of the  extent(6 2  free  pages ) to a  list o f ex t ents t o be  us ed for si ngl ep age all oc at i ons ca lled  F REE_FR AG. Al m ostn o thing allocatespages f rom t h atlist,  s o these page s go to wa ste.

Thisis fai rlysubtl e, wast in g only  0. 37 % of disks pace in a ny la rge Inn oD B ta ble , butnone t h eless  int er est ing and q u i te fi xa b le. 

 InnoDB_bugs. I_thought I’d bring up_a few_of_them, as_they_are fairly interesting.

These_bugs were largely_discoverable due to the_innodb_space utility making_important_internal information visible in a way that it had never been visible in the_past._Using it_to_examine_production tables provided many leads_to go on to find_the bugs_responsible. When we initially looked at a graphical_plot_of free space_by page produced from innodb_space data, we were quite_surprised to see so many pages_less than half_filled_(including_many nearly empty). After_much research we were able to_track down all of the causes_for the anomalies we discovered.

Bug #67718: InnoDB_drastically under-fills pages in certain conditions

Due_to overly-aggressive attempts to optimize_page split_based on insertion order during_insertion, InnoDB could_leave pages_under-filled with as_few as one record in each_page. This was_observed in several production systems in_two_cases which I_believe_could_be quite_common for others:

Mostly-increasing_keys_— Twitter_uses_Snowflake for ID generation in a_distributed_way. Overall it’s quite nice. Snowflake generates_64-bit mostly-incrementing IDs that_contain_a timestamp component. Insertion_is typically happening via queues_and other non-immediate mechanisms, so IDs_will find_their way_to the database slightly out of order. Nearly-ordered keys — Another_schema has a Primary Key and_Secondary Key which are_similarly—but not_exactly—_ordered. Insertion into_a_table to_copy data in either order ends up_nearly ordered_by the other key.

Both of these_circumstances ended up tripping_over_this bug and causing drastically under-filled_pages to appear in production databases,_consuming large amounts of disk_space.

Bug_#67963:_InnoDB wastes 62 out of_every 16384 pages

InnoDB needs to occasionally_allocate some internal_bookkeeping pages; two for every 256 MiB_of_data. In order to do so,_it_allocates an extent (64 pages), allocates_the_two_pages it needed, and then_adds the remainder of the extent_(62 free pages) to a list of extents to_be used for_single page allocations called FREE_FRAG._Almost_nothing_allocates pages from that list, so these pages go to_waste.

This is_fairly subtle, wasting_only 0.37% of disk space in any large InnoDB table,_but nonetheless interesting and quite fixable.
