Why Mari­aDB Scores Over MySQL

This ar­ti­cle gives read­ers an in­sight into Mari­aDB as well as MySQL. Both are an­a­lysed and the dif­fer­ences be­tween them are dis­cussed. There is also a sec­tion on why MySQL users should switch to Mari­aDB.

OpenSource For You - - Contents -

To­day, or­gan­i­sa­tions have a range of data­base op­tions to choose from—whether com­mer­cial or open source. There was a time when most data­base man­age­ment sys­tems (DBMSs) were com­mer­cial. But now, with a num­ber of open source data­bases avail­able, in­dus­try pro­fes­sion­als pre­fer to use them. In fact, open source data­bases have gained a sig­nif­i­cant share of the mar­ket to­day, be­cause users have the free­dom to im­ple­ment these sys­tems as per re­quire­ments and get the best out of them.

Open source data­base sys­tems are avail­able from var­i­ous ven­dors like Or­a­cle, Mi­crosoft, Google, Ama­zon and Rackspace. The his­tory of open source DBMSs is not very old. The first op­er­a­tional open source DBMS was MySQL, which was launched in 1995 and till date, lots of sig­nif­i­cant im­prove­ments have been in­cor­po­rated in it, which has made it a strong com­peti­tor even to com­mer­cial data­base soft­ware.

Open source DBMSs are gain­ing pop­u­lar­ity not only be­cause they are fea­ture-rich and low cost, but also due to their op­er­a­tional ef­fi­ciency. The free­dom users en­joy in cus­tomis­ing the source code as per in­di­vid­ual needs, and with lit­tle pro­gram­ming knowl­edge, is a plus point. Gartner re­ports that open source DBMSs are grow­ing by 42 per cent each year, and this in­dus­try will be worth US$ 8 bil­lion by 2020. About 80 per cent of the users polled for the re­port use some sort of open source soft­ware, in­clud­ing DBMSs. Var­i­ous com­mer­cial ven­dors like Mi­crosoft and IBM are also of­fer­ing ‘Ex­press Edi­tions’ of their DBMS soft­ware.

The most widely adopted open source DBMSs are

MySQL and Mari­aDB. This ar­ti­cle dis­cusses their sim­i­lar­i­ties, dif­fer­ences and rel­a­tive mer­its, be­fore go­ing on to sug­gest why users ought to mi­grate to Mari­aDB.

Data­bases

Data­bases are spe­cific struc­tures that hold data in the form of ta­bles. The soft­ware that en­ables the flow of data through a ta­ble-based struc­ture is called a DBMS. The most widely used DBMS is a re­la­tional data­base man­age­ment sys­tem (RDBMS). A DBMS is a com­puter soft­ware ap­pli­ca­tion that in­ter­acts with end users, other ap­pli­ca­tions and the data­base it­self to cap­ture data. A gen­eral-pur­pose DBMS al­lows def­i­ni­tion, cre­ation, query­ing, up­da­tion and the ad­min­is­tra­tion of data­bases.

The func­tions of a data­base and its data are:

ƒ Data def­i­ni­tion: Cre­ation, mod­i­fi­ca­tion and re­moval of def­i­ni­tions that lay the foun­da­tion for data or­gan­i­sa­tion ƒ Data up­da­tion: In­ser­tion, mod­i­fi­ca­tion and dele­tion of data

ƒ Re­trieval: Re­trieval of data from the en­tire data­base by mak­ing spe­cific queries

ƒ Ad­min­is­tra­tion: User cre­ation and mod­i­fi­ca­tion, data

se­cu­rity, per­for­mance mon­i­tor­ing, data in­tegrity, con­cur­rency con­trol and data re­cov­ery

So, a data­base sys­tem is a com­bi­na­tion of a data­base model, a DBMS and a data­base.

MySQL

MySQL is an open source, multi-threaded, re­la­tional data­base man­age­ment sys­tem (RDBMS) writ­ten in C and C++. MySQL was cre­ated by a Swedish com­pany, MySQL AB, founded by David Ax­mark, Al­lan Lars­son and Micheal ‘Monty’ Wi­de­nius, and de­vel­op­ment started in 1994. The first ver­sion was launched on May 23, 1995. MySQL is named after co-founder Monty Wi­de­nius’s daugh­ter, My. The name of the MySQL dol­phin logo, Sak­ila, was cho­sen from a huge list of names sug­gested by users in a ‘Name the Dol­phin’ con­test.

MySQL server is suit­able for mis­sion-crit­i­cal, heavy­load pro­duc­tion sys­tems as well as for em­bed­ding into mass de­ployed soft­ware. It is re­garded as a cen­tral com­po­nent of the LAMP stack (Linux, Apache, MySQL and PHP).

The MySQL data­base is avail­able for var­i­ous op­er­at­ing sys­tem plat­forms like Linux, Win­dows, MAC, BSD

UNIX, etc. Pop­u­lar web­sites like Wikipedia, Face­book and YouTube use MySQL.

MySQL is now a trade­mark owned by Or­a­cle

Cor­po­ra­tion and/or its af­fil­i­ates, and is dual li­censed. Users can choose to use the soft­ware as open source un­der the

GNU li­cence and even pur­chase a com­mer­cial li­cence from Or­a­cle.

MySQL ar­chi­tec­ture

The MySQL server is based on a tiered ar­chi­tec­ture and con­sists of both pri­mary sub-sys­tems and sup­port com­po­nents that in­ter­act with each other to read, parse and ex­e­cute queries to re­turn the re­sults.

The five types of pri­mary sub-sys­tems of MySQL are: ƒ Query en­gine

ƒ Stor­age man­ager

ƒ Buf­fer man­ager

ƒ Trans­ac­tion man­ager

ƒ Re­cov­ery man­ager

Query en­gine: This con­tains three main in­ter­re­lated com­po­nents—the syn­tax parser, the query op­ti­miser and the ex­e­cu­tion com­po­nent. The syn­tax parser de­com­poses the SQL com­mands it re­ceives from call­ing pro­grams in a form that is un­der­stood by the MySQL en­gine. The ‘query op­ti­miser’ stream­lines the syn­tax for use by the ex­e­cu­tion com­po­nent and then pre­pares the most ef­fi­cient plan for query ex­e­cu­tion. The ex­e­cu­tion com­po­nent in­ter­prets the ex­e­cu­tion plan, based on the in­for­ma­tion it re­ceives and makes re­quests to other com­po­nents to re­trieve the records.

The stor­age man­ager: This in­ter­faces with the op­er­at­ing sys­tem to write data to the disk in the form of user ta­bles, in­dices and logs as well as in­ter­nal sys­tem data.

The query cache: The MySQL en­gine uses an ex­tremely ef­fi­cient re­sult set caching mech­a­nism called query cache, which dra­mat­i­cally en­hances the re­sponse time for queries that are called upon to re­trieve the same data as the pre­vi­ous query.

The buf­fer man­ager: This han­dles all mem­ory man­age­ment is­sues be­tween re­quests for data by the query en­gine and the stor­age man­ager. MySQL makes use of mem­ory to cache re­sult sets that can be re­turned, and the cache is main­tained in the buf­fer man­ager.

The trans­ac­tion man­ager: This sub-sys­tem pro­vides a lock­ing fa­cil­ity to en­sure that mul­ti­ple users ac­cess the data in a con­sis­tent man­ner with­out cor­rupt­ing or dam­ag­ing the data.

The re­cov­ery man­ager: This keeps copies of data for the pur­pose of re­trieval in case of any sort of data loss.

The two sup­port com­po­nents of MySQL are:

ƒ Process man­ager

ƒ Func­tion li­braries

Process man­ager: This per­forms two main func­tions —man­ag­ing user con­nec­tions via mod­ules for net­work con­nec­tion man­age­ment, and syn­chro­ni­sa­tion of tasks and pro­cesses via mod­ules for multi-thread­ing, thread lock­ing and per­form­ing thread-safe op­er­a­tions.

Func­tion li­braries: This con­tains gen­eral-pur­pose rou­tines like string ma­nip­u­la­tion, sort­ing op­er­a­tions and per­form­ing OS-spe­cific func­tions like mem­ory man­age­ment and file I/O.

Fea­tures of MySQL

ƒ Re­la­tional data­base man­age­ment sys­tem: MySQL sup­ports all fea­tures, which makes it a com­plete

RDBMS sys­tem. It sup­ports full SQL as a stan­dard­ised lan­guage for query­ing and up­dat­ing data, and to ad­min­is­ter the data­base.

ƒ Easy and se­cure: MySQL is very sim­ple and in­ter­ac­tive to use, com­pared to other DBMS soft­ware, and is also highly se­cure with a solid data se­cu­rity layer pro­vid­ing ef­fi­cient en­cryp­tion to data.

ƒ Client/server ar­chi­tec­ture: Its sim­ple client/server ar­chi­tec­ture helps end users to cre­ate a server with con­nec­tiv­ity to many clients, in or­der to com­mu­ni­cate with the server for in­sert­ing, up­dat­ing and ad­min­is­trat­ing the data­base.

ƒ Scal­able: MySQL can han­dle high vol­umes of data with­out any hic­cups—as much as 50 million rows. It can han­dle up to 8TB of data with­out any is­sues. ƒ Cross-plat­form: It is com­pat­i­ble with al­most ev­ery op­er­at­ing sys­tem, like UNIX, Win­dows, Linux, MAC OS X, etc.

ƒ High-per­for­mance, flex­i­ble and good pro­duc­tiv­ity: MySQL pro­vides faster, highly re­li­able, cheap stor­age so­lu­tions and sup­ports a large num­ber of em­bed­ded ap­pli­ca­tions. It makes use of trig­gers, pro­ce­dures and views to de­liver bet­ter pro­duc­tiv­ity.

Mari­aDB

Mari­aDB is a forked ver­sion of MySQL. It was pri­mar­ily de­vel­oped due to con­cerns that arose when MySQL was ac­quired by Or­a­cle Inc. Mari­aDB is a gen­eral-pur­pose DBMS en­gi­neered with ex­ten­si­ble ar­chi­tec­ture to sup­port a broad set of use cases via plug­gable stor­age en­gines. It uses dif­fer­ent stor­age en­gines to sup­port dif­fer­ent use cases.

Mari­aDB is an open source, multi-threaded, re­la­tional data­base man­age­ment sys­tem, re­leased un­der the GNU Pub­lic Li­cense (GPL). Its lead de­vel­oper is Michael Monty Wi­de­nius, who is also one of the founders of MySQL AB. Many fea­tures con­tribute to Mari­aDB’s pop­u­lar­ity as a data­base sys­tem. Its speed is one of its most prom­i­nent fea­tures. Mari­aDB is also re­mark­ably scal­able, and is able to han­dle tens of thou­sands of ta­bles and bil­lions of rows of data. It can also man­age small amounts of data quickly and smoothly, mak­ing it con­ve­nient for small busi­nesses or per­sonal projects. An­other fea­ture that sets it apart from its pre­de­ces­sors is a fo­cus on se­cu­rity. Mari­aDB’s built-in func­tions in­clude those for ma­nip­u­lat­ing and for­mat­ting text, busi­ness and sta­tis­ti­cal cal­cu­la­tions, record­ing chrono­log­i­cal in­for­ma­tion, as well as spe­cial­ity fea­tures such as those re­lated to GPS map­ping.

Mari­aDB Server is one of the most pop­u­lar open source data­bases in the world. It is avail­able in De­bian and Ubuntu, and is now the de­fault data­base on Arch Linux, Man­jaro, openSUSE, Red Hat En­ter­prise Linux, Cen­tOS, Fe­dora and SUSE Linux En­ter­prise. And as one of the most broadly adopted and widely de­ployed in the world, Mari­aDB

Server re­ceives con­tri­bu­tions from companies like Alibaba, Face­book and Google. Re­cently, Mi­crosoft has also joined hands to sup­port the Mari­aDB com­mu­nity.

Fea­tures of Mari­aDB

ƒ Mari­aDB is avail­able for use un­der GPL, LGPL and BSD. ƒ It in­cludes a wide se­lec­tion of stor­age en­gines, in­clud­ing high-per­for­mance stor­age en­gines, for work­ing with other RDBMS data sources.

ƒ It uses a stan­dard and pop­u­lar query­ing lan­guage. ƒ Mari­aDB runs on a num­ber of op­er­at­ing sys­tems and sup­ports a wide va­ri­ety of pro­gram­ming lan­guages. ƒ It of­fers sup­port for PHP, one of the most pop­u­lar Web de­vel­op­ment lan­guages.

ƒ It of­fers Galera clus­ter tech­nol­ogy.

ƒ Mari­aDB also of­fers many op­er­a­tions and com­mands un­avail­able in MySQL, and elim­i­nates/re­places fea­tures that im­pact per­for­mance neg­a­tively.

Other fea­tures in­clude multi-source repli­ca­tion, fu­sion IO op­ti­mi­sa­tions, ta­ble dis­cov­ery and on­line al­ter ta­ble.

A com­par­a­tive anal­y­sis of Mari­aDB and MySQL

Mari­aDB has a sig­nif­i­cantly high num­ber of new fea­tures, which makes it bet­ter in terms of per­for­mance and useror­i­en­ta­tion. Let’s eval­u­ate both Mari­aDB and MySQL to de­ter­mine which one is the bet­ter. The choice will ul­ti­mately de­pend on the IT man­agers and their fa­mil­iar­ity with open source data­bases.

Some sig­nif­i­cant dif­fer­ences be­tween MySQL and Mari­aDB

1. Us­age of the data­base: Since 1995, MySQL has been re­garded as the most im­ple­mented and widely used open source data­base till date. Many IT gi­ants like Twit­ter, YouTube, Net­flix and PayPal, as well as NASA, the US de­fence forces and Wal­mart, make use of this data­base. Mari­aDB, be­ing a more re­cent ar­rival, is also tak­ing strong root as back-end soft­ware in var­i­ous IT gi­ant or­gan­i­sa­tions, such as Google, Red Hat, Cen­tOS and Fe­dora.

2. Struc­ture of the data­base and the in­dex: MySQL is a pure re­la­tional data­base in­te­grated with an ANSI­s­tan­dard in­for­ma­tion schema, and con­sists of ta­bles, columns, views, pro­ce­dures, trig­gers, cur­sors, etc. The SQL of MySQL is a sub­set of ANSI SQL 99.

Mari­aDB, on the other hand, is a fork of MySQL and, hence, has the same data­base struc­ture and in­dex. This fa­cil­ity makes Mari­aDB a strong choice for users who want to switch or up­grade their back-end di­rectly with­out any is­sues of up­grad­ing the data­base and data struc­tures. Ev­ery­thing—from the data, ta­ble def­i­ni­tions, struc­tures and APIs—re­mains iden­ti­cal when up­grad­ing from MySQL to Mari­aDB.

3. Bi­nar­ies and im­ple­men­ta­tion: MySQL was de­vel­oped us­ing C and C++ and is fully com­pat­i­ble to run with al­most ev­ery op­er­at­ing sys­tem like Mi­crosoft Win­dows, MAC OS X, Linux, FreeBSD, UNIX, NetBSD, Novell Net­ware and many oth­ers.

Mari­aDB was de­vel­oped us­ing C, C++, Bash and Perl. It is com­pat­i­ble with var­i­ous op­er­at­ing sys­tems like Mi­crosoft Win­dows, Linux, MAC OS X, FreeBSD, So­laris, etc.

4. Repli­ca­tion and clus­ter­ing: MySQL pro­vides strong repli­ca­tion and clus­ter­ing through master-master and master-slave repli­ca­tion, and makes use of the Galera Clus­ter for multi-master clus­ter­ing.

Mari­aDB pro­vides al­most the same repli­ca­tion and clus­ter­ing fa­cil­i­ties to end users as MySQL in terms of master-master and master-slave repli­ca­tion. It also uses Galera Clus­ter from ver­sion 10.1 on­wards.

5. Sup­port for data­bases: MySQL tech­ni­cal sup­port ser­vices are pro­vided round-the-clock via Or­a­cle, and the sup­port team con­sists of pro­fes­sional de­vel­op­ers and en­gi­neers who pro­vide var­i­ous fa­cil­i­ties like bug fixes, patches and ver­sion re­leases. Or­a­cle of­fers MySQL pre­mier sup­port, ex­tended sup­port and sus­tain­ing sup­port, de­pend­ing on what users need.

Mari­aDB pro­vides strong sup­port to users through

the open source com­mu­nity, on­line fo­rums and even via ex­perts. Round-the-clock sup­port is avail­able for Mari­aDB via en­ter­prise sub­scrip­tion es­pe­cially for mis­sion-crit­i­cal pro­duc­tion sys­tems.

6. Se­cu­rity: In terms of se­cu­rity, MySQL pro­vides a strong en­cryp­tion mech­a­nism for ta­blespace data. It pro­vides strong se­cu­rity pa­ram­e­ters in terms of choos­ing good pass­words, not grant­ing un­nec­es­sary priv­i­leges to users, and en­sures ap­pli­ca­tion se­cu­rity by pre­vent­ing SQL in­jec­tions and data cor­rup­tion.

Mari­aDB has had a sig­nif­i­cant boost in terms of se­cu­rity fea­tures like in­ter­nal se­cu­rity and pass­word check, PAM and LDAP au­then­ti­ca­tion, Ker­beros, user roles, and strong en­cryp­tion over ta­blespaces, ta­bles and logs. 7. Ex­ten­si­bil­ity: A data­base sup­port­ing an ex­ten­si­ble sys­tem can be ex­tended by the user in many dif­fer­ent ways like adding new data types, func­tions, op­er­a­tors, ag­gre­gate func­tions, in­dex meth­ods and pro­ce­dural lan­guages. MySQL has no sup­port for ex­ten­si­bil­ity. Mari­aDB is built on a mod­ern ar­chi­tec­ture that is ex­ten­si­ble at ev­ery layer—client, clus­ter, ker­nel and stor­age. This ex­ten­si­bil­ity pro­vides two ma­jor ad­van­tages. It al­lows for con­tin­ual com­mu­nity in­no­va­tion via plug­ins, which means that a va­ri­ety of stor­age en­gines, like Mari­aDB Colum­nS­tore or Face­book’s MyRocks, can be in­te­grated through Mari­aDB’s ex­ten­si­ble ar­chi­tec­ture. Ad­di­tion­ally, it makes it easy for cus­tomers to con­fig­ure Mari­aDB to sup­port a wide va­ri­ety of use cases, rang­ing from OLTP to OLAP.

8. JSON sup­port: MySQL sup­ports a na­tive JSON data type that en­ables ef­fi­cient ac­cess to data in JSON (JavaScript Ob­ject No­ta­tion) doc­u­ments. The JSON data type pro­vides the fol­low­ing ad­van­tages over stor­ing JSON-for­mat strings in a string col­umn:

• Au­to­matic val­i­da­tion of JSON doc­u­ments stored in JSON columns. In­valid doc­u­ments pro­duce an er­ror. • Op­ti­mised stor­age for­mat: JSON doc­u­ments stored in JSON columns are con­verted to an in­ter­nal for­mat that per­mits quick read ac­cess to doc­u­ment el­e­ments. When the server later has to read a JSON value stored in this bi­nary for­mat, the value need not be parsed from a text rep­re­sen­ta­tion. The bi­nary for­mat is struc­tured to en­able the server to look up sub-ob­jects or nested val­ues di­rectly by key or ar­ray in­dex with­out read­ing all val­ues be­fore or after them in the doc­u­ment.

On the other hand, Mari­aDB Server 10.2 in­tro­duces a com­pre­hen­sive set of 24 func­tions for read­ing and writ­ing of JSON doc­u­ments. In ad­di­tion, the JSON_ VALID func­tion can be used with a check con­straint for val­i­da­tion, while func­tions like JSON_VALUE can be used with dy­namic columns to in­dex spe­cific fields. 9. Li­cens­ing: MySQL of­fers its code as open source un­der the GPL, and pro­vides the op­tion of nonGPL com­mer­cial dis­tri­bu­tion in the form of MySQL En­ter­prise.

Mari­aDB can only use the GPL, be­cause its work is de­rived from the MySQL source code un­der the terms of that li­cence.

10. Per­for­mance: Mari­aDB achieves best-in-class per­for­mance with nu­mer­ous in­no­va­tions that are ab­sent for MySQL. These in­clude thread pool man­age­ment to max­imise pro­cess­ing ef­fi­ciency and ex­ten­sive op­ti­mi­sa­tion fea­tures such as de­frag­men­ta­tion within the In­noDB data store. So, when rows are deleted from an In­noDB ta­ble, the free space im­me­di­ately be­comes avail­able to the op­er­at­ing sys­tem. There’s no need to copy data from old ta­bles to new ones, and there are no gaps in the ta­blespace. Mari­aDB also of­fers en­ginein­de­pen­dent ta­ble statis­tics to im­prove the op­ti­miser’s per­for­mance, speed­ing up query pro­cess­ing and data anal­y­sis on the size and struc­ture of the ta­bles.

With­out these en­hance­ments, MySQL falls short in terms of per­for­mance. Thread util­i­sa­tion in MySQL is sub-op­ti­mal and In­noDB ta­bles be­come frag­mented over time, com­pro­mis­ing per­for­mance.

Mari­aDB vs MySQL

The fol­low­ing points high­light the pros and cons of Mari­aDB.

Pros

ƒ Mari­aDB has been op­ti­mised for per­for­mance and is much more pow­er­ful than MySQL for large data sets. El­e­gant mi­gra­tion from other data­base sys­tems to Mari­aDB is yet an­other ben­e­fit.

ƒ Switch­ing from MySQL to Mari­aDB is rel­a­tively easy and is a piece of cake for sys­tems ad­min­is­tra­tors.

ƒ Mari­aDB pro­vides bet­ter mon­i­tor­ing through the in­tro­duc­tion of mi­cro-sec­ond pre­ci­sion and ex­tended user statis­tics.

ƒ Mari­aDB en­hances the KILL com­mand to al­low you to kill all queries from a user (KILL USER user­name) or to kill a query ID (KILL QUERY ID query_id). Mari­aDB has also switched to Perl-com­pat­i­ble reg­u­lar ex­pres­sions (PCRE), which of­fer more pow­er­ful and pre­cise queries than stan­dard MySQL regex sup­port.

ƒ Mari­aDB has ap­plied a num­ber of query op­ti­mi­sa­tions for queries con­nected with disk ac­cess, join op­er­a­tions, sub­queries, de­rived ta­bles and views, ex­e­cu­tion con­trol, and even ex­plain state­ments.

ƒ Mari­aDB is purely open source, in­stead of the dual­li­cens­ing model that MySQL uses. Some plug­ins that are avail­able only for MySQL En­ter­prise cus­tomers have equiv­a­lent open source im­ple­men­ta­tions in Mari­aDB. ƒ Mari­aDB sup­ports sig­nif­i­cantly more en­gines (SphinxSE, Aria, Fed­er­at­edX, TokuDB, Spi­der, ScaleDB, etc) com­pared to MySQL.

Mari­aDB of­fers a clus­ter data­base for com­mer­cial use, which also en­ables multi-master repli­ca­tion. Any­one can use it freely and re­liance on a MySQL En­ter­prise sys­tem is not re­quired.

Cons

Mi­gra­tion of Mari­aDB back to MySQL has not been pos­si­ble since re­lease 5.5.36.

For new re­leases of Mari­aDB, the ap­pro­pri­ate li­braries (for De­bian) will not be de­ployed in time, which will re­sult in a re­quired up­grade to a newer ver­sion due to de­pen­den­cies.

Clus­ter ver­sion of Mari­aDB is not very sta­ble.

Top rea­sons to mi­grate to Mari­aDB

First and fore­most, Mari­aDB of­fers more and bet­ter stor­age en­gines. NoSQL sup­port, pro­vided by Cas­san­dra, al­lows you to run SQL and NoSQL in a sin­gle data­base sys­tem. Mari­aDB also sup­ports TokuDB, which can han­dle Big Data for large or­gan­i­sa­tions and cor­po­rate users.

MySQL’s usual (and slow) data­base en­gines MyISAM and In­noDB have been re­placed in Mari­aDB by Aria and XtraDB re­spec­tively. Aria of­fers bet­ter caching, which makes a dif­fer­ence when it comes to disk-in­ten­sive op­er­a­tions. Mari­aDB pro­vides bet­ter mon­i­tor­ing through the in­tro­duc­tion of mi­cro-sec­ond pre­ci­sion and ex­tended user statis­tics.

The cut­ting-edge fea­tures of Mari­aDB like GIS, dy­namic col­umn sup­port, etc, make it a bet­ter choice.

Mari­aDB fol­lows good in­dus­try stan­dards by re­leas­ing se­cu­rity an­nounce­ments and up­grades at the same time, and han­dling the pre-re­lease se­crecy and post-re­lease trans­parency in a proper way.

Newspapers in English

Newspapers from India

© PressReader. All rights reserved.