Feed Aggregator

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. The tested MySQL version is 5.7.11.

Caution: Different MySQL or MariaDB versions might behave differently!

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

Master:

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

Slave:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So far no problem, I was able to start the …

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:

To do this I needed a table and some data:

CREATE TABLE poi (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(40)
, type VARCHAR(20)
, sub_type VARCHAR(20)
, pt POINT NOT NULL
, PRIMARY KEY (id)
, SPATIAL INDEX(pt)
) ENGINE=InnoDB;

INSERT INTO poi (name, type, sub_type, pt) VALUES
  ('Shop 1', 'Shop', 'Cloth', Point(2,2))
, ('Cafe 1', 'Cafe', '',  Point(11,2))
, ('Shop 2', 'Shop', 'Cloth',  Point(5,4))
, ('Restaurant 1', 'Restaurant', 'Portugies',  Point(8,7))
, ('Cafe 2', 'Cafe', '', …

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;

EXPLAIN SELECT * FROM test WHERE data = 42<br>G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: data
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 522500
     filtered: 10.00
        Extra: Using where

EXPLAIN SELECT * FROM test WHERE data = '42'<br>G
*************************** 1. row ***************************
           id: 1 …

I recently managed to get my certification as MySQL 5.6 DBA, and was asked to write a blog about it, because I had trouble getting the informations I needed.

You may have figured out too, that Oracle does not really supply you with information about the certification. At least, there is the MySQL documentation. It contains all the information you need.

Further, I recommend to use a virtual linux machine in combination with our tool MyEnv. This way you can simulate multiple scenarios, including replication set-ups, and if one or two servers die during your exercises, nobody gets mad at you.

When learning, make sure to have a look at the following topics:

• Query tuning
• Parameters tuning
• MySQL client tools (mysqldump, mysqladmin, …)
• MySQL Audit Plugin
• How to secure MySQL (Especially, the correct assignment of privileges.)
• How to use the Performance and Information Schema
• Partitions
• Replication
• Backup and Recovery (Both, physical and logical variant.)

The certification takes 150 minutes and contains 100 …

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

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 | category_id |
+-----+-------------+
|   1 | ACTUAL      |
|   2 | ADJUSTMENT  |
|   3 | BUDGET      |
+-----+-------------+

ROW_NUMBER() per PARTITION

SELECT ROW_NUMBER() OVER (PARTITION BY store_type ORDER BY SUM(sf.store_sales) DESC) AS Nbr
     , s.store_type AS "Store Type", s.store_city AS City, SUM(sf.store_sales) AS Sales
  FROM store AS s
  JOIN sales_fact AS sf ON sf.store_id = s.store_id
 GROUP BY s.store_type, s.store_city
 ORDER BY s.store_type, Rank
; …

One of our customers recently ran into a problem, where he wanted to have a preferred donor for SST, whenever a node came up. The problem was, that the node did not come up, when the preferred donor was not running.

In the documentation, you can find the parameter wsrep_sst_donor, which prefers the specified node as SST donor. This is great, as long as the donor is actually running.

The problem can be fixed by adding a comma to the end of the value of wsrep_sst_donor, what would look like this:

wsrep_sst_donor="galera2,"

Note the comma at the end of the value. This trailing comma basically tells this node, that galera2 is the preferred donor, if galera2 is not available, any other available node will be used as donor.

You could also specify a secondary node, which is needed to be available for the node to come up:

wsrep_sst_donor="galera2,galera1"

In this case, galera1 wil be used as secondary donor if galera2 is not available. If both are not available, the node will refuse to come up.

Time flies, and my blogging frequency is quite low. More frequent would be better, but knowing myself I’ll rather not promise anything ;-)

Still, it is appropriate to write some notes about CeBIT, the “Chemnitzer Linuxtage 2016”, and future events.

CeBIT

CeBIT was running from March 14 to 18 (Monday till Friday) in Hannover, Germany, and I will leave the general assessment to the various marketing departments as well as to the regular visitors (to which I do not belong).

In order to meet our current customers as well as potential future ones, FromDual had a booth in the “Open Source Forum”. We displayed a Galera Cluster, running on three tiny headless single-board Linux machines, and showed how it reacts to node failures and then recovers all by itself, without any administrator intervention. Many of our visitors were fascinated, because a HA solution would be a good fit in their solution architecture. We had got several stuffed dolphins “Sakila”, the traditional …

Yesterday we did a first real life test of the formulas and the predictions of all 3 methods where quite congruent… So I personally trust them!

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 round-robin file based cache that keeps all the write-sets (= transactions + metadata) for a certain amount of time. This time, which should be bigger than your planned maintenance window, depends on the size of the Galera Cache (default 128 Mbyte) and the traffic which will happen during your maintenance window.

If your traffic is bigger than the Galera Cache can keep Galera Cluster will fall-back from IST to SST which is a very expensive operation for big databases.

The size of the Galera Cache can be …

MySQL Performance Monitor agent gets a TERM signal from time to time:

39004:2015-02-23 09:59:36.365 - INFO: FromDual Performance Monitor for MySQL (0.9.3) run started.
39004:2015-02-23 09:59:37.770 - INFO: FromDual Performance Monitor for MySQL run finshed (rc=0).
39089:2015-02-23 09:59:46.458 - INFO: FromDual Performance Monitor for MySQL (0.9.3) run started.
39089:2015-02-23 09:59:47.865 - INFO: FromDual Performance Monitor for MySQL run finshed (rc=0).
39181:2015-02-23 09:59:56.510 - INFO: FromDual Performance Monitor for MySQL (0.9.3) run started.
39181:2015-02-23 09:59:57.855 - INFO: FromDual Performance Monitor for MySQL run finshed (rc=0).
39290:2015-02-23 10:00:06.311 - INFO: FromDual Performance Monitor for MySQL (0.9.3) run started.
39290:2015-02-23 10:00:09.094 - ERR :   got TERM signal. Cleaning up stuff an exit (rc=1).
39290:2015-02-23 10:00:09.094 - INFO: FromDualMySQLagent::removeAgentLock
39290:2015-02-23 10:00:09.095 - INFO: FromDual Performance Monitor for MySQL run finshed (rc=1).

Can you …

FromDual has the pleasure to announce the release of the new version 1.0.1 of the FromDual Nagios and Icinga plugins for MySQL, Galera Cluster and MariaDB.

Download

The new FromDual Nagios plugins can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Nagios plugins please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for Nagios plugins).

Changes in Nagios plugins 1.0.1

• Help adapted to new 5.7 conventions for creating user in check_repl_mysql_heartbeat.pl
• Output can be formatted for Centreon, Icinga and Nagios now in perf_mysql.pl
• Support for Galera Cluster implemented in perf_mysql.pl

or

xfs Users, Take Care!

Recently, we had a customer ask: Why do many files holding my data take up vastly more space than their size is? That question may sound weird to you, but it is for real, and the customer’s observation was correct. For a start, let’s make sure we are using the same terms.

• The size of a file is the number of bytes it will deliver if it is read sequentially from start to end.
• The space it takes up is the sum of all disk pages which are used to hold the file’s data, or to locate those data pages (“indirect” blocks in Unix/Linux terminology).

Every Unix/Linux admin knows (or at least should know) that a file may take up less disk space than its size is. This happens when not all bytes of the file were really written, but the write pointer was advanced via “seek()”, leaving a gap. Disk pages which are completely contained in such a gap will not be written, and reading these positions will produce bytes containing zero. This is called a …

Hello DBStarter

First of all a few things:

• About MariaDB documentation: I would say the MariaDB documentation is not too bad compared to other open source project documentation or even enterprise software documentation. But its far from being brilliant.

• Then: How much did you or your company pay for MariaDB subscription? A part of the subscription fee flows into documentation to make it better. If you have a subscription with a support provider you also can ask such questions there.

• MariaDB claims to be an open source project. How much did you or your company contribute back to the project documentation (or event the server)?

To your question:

The most important maintenance and operations task for MySQL/MariaDB are:

• Proper Backup and Restore testing
• Monitoring
• Clean-out old data

MariaDB does otherwise not need much maintenance work.

If you have tables with may UPDATE/DELETE statements sometimes an OPTIMIZE TABLE can help. And if you experience flipping query execution plans an ANALYZE TABLE can …

The FOSDEM 2016 in Brussels (Belgium) January 29/30 is over and was very interesting and IMHO a big success.

For all those who could not participate at FOSDEM 2016 our presentation slides about PERFORMANCE_SCHEMA and sys schema are available here:

PERFORMANCE_SCHEMA and sys schema - What can we do with it? (PDF, 406 kbyte)

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 inconceivable case that you find a bug in the FromDual Ops Center please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for FromDual Ops Center).

Installation of Ops Center

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.2 to 0.3

An upgrade path from Ops-Center v0.2 to v0.3 is currently not supported. Please also …

FromDual has the pleasure to announce the release of the new version 1.3.0 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x or higher to 1.3.0

# cd ${HOME}/product
# tar xf /download/myenv-1.3.0.tar.gz
# rm -f myenv
# ln -s myenv-1.3.0 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv
ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/

Changes in MyEnv 1.3.0

MyEnv

• NDB Cluster stuff was removed and cleaned-up.
• my.cnf template was slightly adapted to new requirements (query_cache_size, innodb_buffer_pool_instances, binlog_stmt_cache_size, wsrep_sst_method and …

Most of you know, that it is possible to synchronize MySQL and MariaDB servers using replication. But with the latest releases, it is also possible to use more than just two servers as a multi-master setup.

Most of you know that both MySQL and MariaDB support replication in a hierarchical master-slave-setup, to propagate changes across all connected servers.

But with the latest releases, a slave can have more than one master.

The keyword: Multi-Source replication

It is supported from MySQL 5.7 and MariaDB 10.0 on, and this article describes how to set it up.

What does Multi-Source mean?

Multi-Source means, that you can take two or more masters and replicate them to one slave, where their changes will be merged. This works just like the regular MySQL/MariaDB replication.

Well, we are going to exploit that a little. It is still possible to configure a Master-Master set up, what basically allows the following configuration.

Multiple servers are conjoined in a cluster, where every server is a master, and …

Hello together, sorry for the placement of this thread in this MySQL generalls topic. Was not sure where to post :/ I try to understand how to maintenance a database in the MariaDB World. But to be honest i have never seen before a bad technical documentation like from MariaDB. I located tasks like using ANALYZE TABLES or OPTIMIZE TABLE. Are there any other maintenance task that a DBA should operate? Some keywords explicit to MariaDB would be enough, please no general answers like reindexing or defragmenting. Thanks. Kind Regards from Germany.