明月登楼

VPS 服务器升级更新到 MariaDB 10.2.12 初体验
  在我的博客【记录一次失败的 Upgrade MySQL to MariaDB 经历】一文里,明月详细的说明了上...
扫描右侧二维码阅读全文
05
2018/02

VPS 服务器升级更新到 MariaDB 10.2.12 初体验

  在我的博客【记录一次失败的 Upgrade MySQL to MariaDB 经历】一文里,明月详细的说明了上周 Upgrade MySQL to MariaDB 失败的经历,可以说周末很不甘心。今天周一上班就想着在公司的服务器上试试了,因为公司目前使用的是 MySQL 5.7的,可以说硬件配置上是没有任何问题的。于是,马上进入控制台执行 Upgrade MySQL to MariaDB 脚本进行升级更新,这次选择的是 MariaDB 10.2.12版的,经过半个多小时(配置高了编译就是快了很多呀!)编译,成功升级 MySQL 到 MariaDB ,并且升级完成后网站直接自动恢复访问,不需要任何的附加操作,哈哈!

20180205110036.png

  其实到目前为止,明月对 MariaDB 的了解也仅限于其创始人也是 MySQL 的创始人,至于为啥要重复开发一个 MariaDB 来,主要是因为 MySQL 被甲骨文(Oracle)收购后就存在会收费和涉及版权的隐患了。单从 CentOS 7 开始放弃 MySQL 转投 MariaDB 就可以知道这个“隐患”带来的影响有多大,当然 CentOS 7 有“洁癖”也是事实。长久来看数据库的开源分享纯粹性对于网站来说还是很重要的,因为指不定哪天就面临着“收费授权”的尴尬就不好了,正所谓“长痛不如短痛”嘛!能尽早转投 MariaDB 还尽量的早好的,公司的 MySQL 转 MariaDB 更是迫切需要的,毕竟涉及企业的很容易被“收费”嘛。

那么 MariaDB 和 MySQL 的区别主要在哪些方面呢?

MariaDB和mySQL到底区别在哪,实验说明问题!

先看图,插入数据和时间的对数图,实验条件一直且关闭了mysql默认事务保证不是单条事务而是批量事务

1039835-20161029133004953-2003069300.png

另外确保了 MySQL and MariaDB 都是在支持事务存储引擎下测试的。

MySQL 之父 Widenius 先生离开了 Sun 之后,觉得依靠 Sun/Oracle 来发展 MySQL ,实在很不靠谱,于是决定从新开发代码全部开源免费关系型数据库,这就是 MariaDB 。在 navicat 中操作 MariaDB 的界面和提示符还是 MySQL 端口也是 3306 外人看起来除了数据库名字改了其他和 MySQL 完全一致,但是看一下性能比较,最近在美国 MariaDB 很火。我觉得 Oracle 是不是故意把 MySQL 做的很差?

MariaDB 在各方面都是 MySQL 创新和提高版本,而不是简单的替代品。

MariaDB 的前世今生

2009年

Michael Widenius创建新项目Michael以规避关系型数据库开源的可能风险.直到5.5的版本,一直按照MySQL的版本进行发行。使用者基本上不会感受到和MySQL不同的地方。

2012年

MariaDB开始按照自己的节奏和版本发行方式进行发行,初始版本为:10.0.0,此版本以MySQL5.5为基础,同时合并了MySQL5.6的相关功能。

MariaDB 与 MySQL ——特性比较(官方版)

更多的存储引擎

  除了提供标准的MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE和MERGE引擎外,在MariaDB源码与二进制包中还提供下列存储引擎:

  • Aria
  • XtraDB (与InnoDB对等替换)
  • PBXT(在MariaDB 5.1、MariaDB 5.2与 MariaDB 5.3提供,MariaDB 5.5己不提供了)
  • FederatedX (与Federated对等替换)
  • OQGRAPH (在MariaDB 5.1、MariaDB 5.2与 MariaDB 5.3提供,MariaDB 5.5己不提供了)
    —5.2版添加
  • SphinxSE —5.2版添加
  • IBMDB2I. Oracle公司从MySQL 5.1.55移除该引擎,MariaDB将在代码中保留其至版本5.5。
  • TokuDBMariaDB 5.5与MariaDB 10.0
  • Cassandra MariaDB 10.0

    • 我们正忙于给MariaDB添加NoSQL存储引擎。
  • CONNECT MariaDB 10.0
  • SEQUENCE   MariaDB 10.0
  • Spider MariaDB 10.0

速度提升

  • 在MariaDB 5.3中进行了大量的优化提升,子查询真正可用了!完整的列表及与的比较请点击此处,性能指标请在此处查看。
  • Faster and safer replication: Group commit for the binary log. This makes many setups that uses replication and lot's of updates more than 2x times faster.
  • Improvements for Innodb asynchronous IO subsystem on Windows.
  • Indexes for the MEMORY(HEAP) engine are faster. According to a simple test, 24% faster on INSERT for integer index and 60% faster for index on a CHAR(20) column.
  • CHECKSUM TABLE is faster.
  • We improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big results sets with all characters between 0x00-0x7f.
  • Pool of Threads in MariaDB 5.1 and even better in MariaDB 5.5. This allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
  • There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
  • Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should
    give a speedup when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn't have to write the temporary rows to disk.
  • The test suite has been extended and now runs much faster than before, even though it tests more things.

扩展&新特性

我们为MariaDB添加了很多新特性,假如一个补丁或特性是涉及可用性、安全与稳定性——我们会积极地将其添加到MariaDB中。特别值得一提如下:

  • Microseconds in MariaDB — new in 5.3
  • Microsecond Precision in Processlist
  • Table Elimination
  • Virtual Columns — new in 5.2
  • Extended User Statistics — new in 5.2
  • Segmented Key Cache for MyISAM — new in 5.2
  • KILL all queries for a user — new in 5.3
  • Pluggable Authentication — new in 5.2
  • Storage-engine-specific CREATE TABLE — new in 5.2
  • Enhancements to INFORMATION SCHEMA.PLUGINS table — new in 5.2
  • Group commit for the binary log. This makes replication notably faster! — new in 5.3
  • Added --rewrite-db mysqlbinlog option to change the used database — new in 5.2
  • Progress reporting for ALTER TABLE and LOAD DATA INFILE. — new in 5.3
  • Faster joins and subqueries. — new in 5.3
  • HandlerSocket and faster HANDLER calls. — new in 5.3
  • Dynamic Columns support. — new in 5.3
  • GIS Functionality — new in 5.3
  • Multi-source replication. — new in 10.0
  • SHOW EXPLAIN gives the EXPLAIN plan for a query running in another thread. — new in 10.0

更充分的测试

  • More tests in the test suite.
  • Bugs in tests fixed.
  • Test builds with different configure options to get better feature testing.
  • Remove invalid tests. (e.g. don't test feature ''X'' if that feature is not in the tested build)

更少的警告与缺陷

  • Bugs are bad. Fix as many bugs as possible and try to not introduce new ones.
  • Compiler warnings are also bad. Eliminate as many compiler warnings as possible.

真正开源

  • All code in MariaDB is released under GPL, LPGL or BSD. MariaDB does not have closed source modules like the ones that can be found in MySQL Enterprise Edition. In fact, all the closed source features in MySQL 5.5 Enterprise Edition are found in the MariaDB open source version.
  • MariaDB includes test cases for all fixed bugs. Oracle doesn't provide test cases for new bugs fixed in MySQL 5.5.
  • All bugs and development plans are public.
  • MariaDB is developed by the community in true open source spirit.

  作为一名服务器运维人员,任何“隐患”都要做好防范和修补的, CentOS 的“洁癖”其实就是运维人员们一个很好的标杆,虽然现在 MySQL 还可以依旧免费使用,但是就甲骨文(Oracle)的“尿性”, MySQL 未来版本性能下降的可能性是大幅提升了,至于说“收费”问题也是一个必然会发生的事儿,所以 Upgrade MySQL to MariaDB 对于服务器运维来说还是很有必要的一个工作,如果服务器硬件配置合适,明月还是建议大家 Upgrade MySQL to MariaDB 的。

最后修改:2018 年 02 月 05 日 11 : 46 AM
如果觉得我的文章对你有用,请随意赞赏

发表评论

2 条评论

  1. 面料

    咦这里还有一个明月登楼啊,博主真是能折腾

    1. 明月登楼
      @面料

      呵呵,多谢支持!