MySQL Tech-Feed (en)

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.

Today we wanted to know where the high Com_create_table and the twice as high Com_drop_table is coming from. One suspect was TEMPORARY TABLES. But are real temporary tables counted as Com_create_table and Com_drop_table at all? This is what we want to find out today. …

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!”

Now I wanted to try out how it really works and if it is that easy at all…

To get myself an idea of what I want to do I did a little sketch first:

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:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB; …

• 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 …

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions, Window Functions, Window function and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;
+-----+-------------+
| num | …

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

• For full synchronization of data: Snapshot State Transfer (SST).
• For delta synchronization of data: Incremental State Transfer (IST).

The Incremental State Transfer (IST) is relevant when a node is already known to the Galera Cluster and just left the cluster short time ago. This typically happens in a maintenance window during a rolling cluster restart.

The Galera Cache is a …

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

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA’s and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the …

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:

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_rows_query_log_events

To see the original queries you can either run SHOW BINLOG EVENTS ... in the MySQL client or you can run mysqlbinlog -vv binary-log.000001

I hope, this …

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

SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 505   |
+-----------------+-------+

If this limit was ever reached in the past can be checked with:

SHOW GLOBAL STATUS LIKE 'max_use%';
+----------------------+-------+
| Variable_name        | Value | …

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.

For further details please consult MariaDB or MySQL documentation.

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

Foreign Key Constraint Errors are always either a bug in your application (which should be fixed) or inconsistencies in your data (which should be fixed) or both (first fix the bug in the application, then clean-up your data).

Foreign Key Constraint Errors can be found with the following command:

SHOW ENGINE INNODB STATUS<br>G

SHOW ENGINE Syntax …

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

Please consult MariaDB and MySQL documentation for further details or InnoDB Recovery Modes.

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:

• One InnoDB Buffer Pool Instance should be at least 1 Gibyte in size (innodb_buffer_pool_size / innodb_buffer_pool_instances
  >= 1 Gib).
• InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine.

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.

For further information consult MariaDB or MySQL documentation.

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 …

The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables).
Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires.
You can check whether you need to increase the Table Open Cache by checking Open_tables and Opened_tables. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which …