Shinguz's blog

Today we had a very interesting phenomena at a customer. He complained that MySQL always get some errors of the following type:

When analysing MySQL configuration and status information at customers it is always interesting to see how the applications behave. This can partially be seen by the output of the SHOW GLOBAL STATUS command. See also Reading MySQL fingerprints.

This week I was preparing the exercises for our MySQL/MariaDB for Beginners training. One of the exercises of the training is about MySQL spatial (GIS) features. I always tell customers: “With these features you can answer questions like: Give me all points of interest around me!”

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

• max_user_connections Can be changed online in MySQL. Cannot be changed in MariaDB if value was set to 0.
• PERFORMANCE_SCHEMA is enabled in MySQL 5.6 and 5.7 by default. In MariaDB disabled by default.
• Replication incompatibilities from MariaDB (Master) to MySQL (Slave)
• MariaDB 10.2.0 still contains XtraDB 5.6. So MySQL 5.7 features cannot be used.
• MariaDB 10.2 Window Functions are missing in MySQL 5.7
• SHOW PROCESSLIST has additional column Progress in MariaDB.
• Progress indication in: mysqldump --progress-reports (default on) for MariaDB.
• MariaDB 10.0 on CentOS 7 uses still SysV init script. MySQL 5.6 on CentOS 7 uses SystemD init script.
• binlog_row_image is implemented in MySQL 5.6 and does not exist in MariaDB 10.0.
• MariaDB 10.1 and older (containing XtraDB 5.6) does not provide flexible/general tablespaces yet which where introduced by MySQL 5.7.
• EXPLAIN FOR CONNCECTION does exist in MySQL 5.7 and does not exist in MariaDB 10.1 or 10.2.

Other sources

• Sidegrade (MySQL - MariaDB - Percona Server)
• MariaDB versus MySQL - Features
• Differences Between the MySQL and MariaDB Query Optimizer
• MariaDB versus MySQL - Compatibility

MariaDB 10.2 has introduced some Window Functions for analytical queries.

To synchronize the data between the Galera Cluster and a new or re-entering Galera node Galera Cluster uses 2 different mechanisms:

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

Hi oli

Since MySQL 5.6.2 there is a new MySQL variable called binlog_rows_query_log_events. If you enable this variable MySQL will write the original SQL statements into the binary logs. More information about this variable you can find here:

How many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:

InnoDB plugin is enabled.

The InnoDB Log File (innodb_log_file_size) size is possibly too small.
Choosing a too small InnoDB Log File size can have significant write performance impacts.

MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.

InnoDB Force Recovery (innodb_force_recovery) is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data.

InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of committed transactions in case of a power failure or an unclean shutdown of your database.

You got an InnoDB Deadlock. Please talk to your development department to fix this problem. Deadlocks are an application problem!

If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons.
Some rules to size InnoDB Buffer Pool instances are:

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

The number of table definitions (SHOW CREATE TABLE<br>G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables (
> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency.
The command SELECT COUNT(*) FROM information_schema.tables; shows you how many tables and thus table definitions you have. The global status Open_table_definitions is the current amount of open table definitions.