Thursday, August 9, 2012

What is MariaDB? Quick Comparison with MYSQL.

    Monty Widenius, creator of mysql left after acquiring it by Oracle and SUN in 2009. And he started the new project, MariaDB to be a replacement for mysql server.

   MariaDB and Mysql are very near to each other as brother and sister. in other words, if you have mysql 5.1, you can switch to use MariaDb 5.1 and preserve all of your stuff and configuration. On top of that all MySQL connectors , api, etc. are the same (your PHP script that works on mysql will work normally on MariaDB)

Advantages of MariaDB over Mysql:
  • More storage engines like Aria
  • It uses the XtraDB (InnoDB improved) storage engine from Percona as default.
  • Improvements on speed, specially that the optimizer had been greatly improved to handle big data with Subqueries, derived tables and views, Index Merge, and Join queries.
  • Replication is a magnitude faster in MariaDB if you have lots of concurrent updates to InnoDB
  • Group commit for the binary log: MariaDB 5.3 implements group commit which works when using XtraDB with the binary log enabled. (In previous MariaDB releases, and all MySQL releases at the time of writing, group commit works in InnoDB/XtraDB when the binary log is disabled, but stops working when the binary log is enabled).
  • Row-based replication for tables with no primary key: This feature can improve the performance of row-based replication on tables that do not have a primary key (or other unique key), but which do have another index that can help locate rows to update or delete. With this feature, index cardinality information from ANALYZE TABLE is considered when selecting the index to use (before this feature is implemented, the first index was selected unconditionally).
  • The focus for MariaDB 5.3 is to radically improve performance for subqueries, as well as for joins and single-table queries over large data sets
  • Disk Access Optimization (Index Condition Pushdown and Multi-Range-Read optimization (MRR))
  • START TRANSACTION WITH CONSISTENT SNAPSHOT: in MariaDB 5.3, START TRANSACTION WITH CONSISTENT SNAPSHOT now also works with the binary log. This means it is possible to obtain the binlog position corresponding to a transactional snapshot of the database without blocking any other queries. This is used by the command "mysqldump --single-transaction --master-data" to do a fully non-blocking backup which can be used to provision a new slave. "START TRANSACTION WITH CONSISTENT SNAPSHOT" now also works consistently between transactions involving more than one storage engine (currently XTraDB and PBXT support this).
  • Microsecond support for NOW() and timestamp, time, and datetime columns.
New variables:

        1.Rows_tmp_read, Handler_tmp_write, and Handler_tmp_update status variables which count what happens with internal temporary      tables.
        2.Rows_read, Handler_write and Handler_update are no longer count operations on internal temporary tables.
        3.Handler_read_rnd_deleted status variable, which is number of deleted rows found and skipped while scanning a table
        4.in_transaction variable, that is 1 if you are in a transaction, 0 otherwise
        5.max_user_connections (both the global variable and the GRANT option) can be set to -1 to stop users from connecting to the server.    The global max_user_connections variable does not affect users with the SUPER privilege.

The issues that need some attention regarding MariaDB:
  •     Compatibility tests
  •     Stability tests

1 comment:

  1. It is very good to have detailed information of MariaDB