You are here

FromDual TechFeed (en)

innodb_deadlock_detect - Rather Hands off!

Shinguz - Mon, 2020-03-23 11:24

Recently we had a new customer who has had from time to time massive database problems which he did not understand. When we reviewed the MySQL configuration file (my.cnf) we found, that this customer had disabled the InnoDB Deadlock detection (innodb_deadlock_detect).

Because we have advised against doing this so far, but I never stumbled upon this problem in practice, I have investigated a bit more about the MySQL variable innodb_deadlock_detect.

The MySQL documentation tells us the following [1]:

Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

And about the parameter innodb_deadlock_detect itself [2] itself:

This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

The problem is, that every time, when MySQL is doing a (Row) Lock or a Table Lock, it checks, if the Lock causes a Deadlock. This check is quite expensive. By the way: The feature disabling InnoDB Deadlock detection was developed by Facebook for WebScaleSQL [3].

The relevant functions can be found in [4]:

class DeadlockChecker, method check_and_resolve (DeadlockChecker::check_and_resolve) Every InnoDB (row) Lock (for mode LOCK_S or LOCK_X) and type ORed with LOCK_GAP or LOCK_REC_NOT_GAP, ORed with LOCK_INSERT_INTENTION Enqueue a waiting request for a lock which cannot be granted immediately. lock_rec_enqueue_waiting()

and

Every (InnoDB) Table Lock Enqueues a waiting request for a table lock which cannot be granted immediately. Checks for deadlocks. lock_table_enqueue_waiting()

This means if the variable innodb_deadlock_detect is enabled (= default) for every Lock (Row or Table) it is checked, if it causes a Deadlock. If the variable is disabled, the check is NOT done (which is faster) and the transaction hangs in (Dead-)Lock until the Lock is freed or the time innodb_lock_wait_timeout (default 50 seconds) is exceeded. Then the InnoDB Lock Wait Timeout (detector?) strikes and kills the transaction.

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+

This means, deactivating InnoDB Deadlock detection is interesting, if you have many (like Facebook!?!) short and small transactions where you expect little to now conflicts.
Further it is recommended, to set the MySQL variable innodb_lock_wait_timeout to a very small value (some seconds).

Because most of our customers do not have the size of Facebook and because they have rather not so many concurrent short and small transactions but few but long transactions (with probably many Locks and therefore a high Deadlock probability), I can imagine, disabling this parameter was responsible for the hickup (Locks are piling up) of the customer system. Which leads to exceeding max_connections and finally the whole system sticks.

Therefore I strongly recommend, to let InnoDB Deadlock detection enabled. Except you know exactly what your are doing (after about 2 weeks of extensive testing and measuring).

Literature Taxonomy upgrade extras: innodbdeadlocklockperformancelockingblock

innodb_deadlock_detect - Rather Hands off!

Shinguz - Mon, 2020-03-23 11:24

Recently we had a new customer who has had from time to time massive database problems which he did not understand. When we reviewed the MySQL configuration file (my.cnf) we found, that this customer had disabled the InnoDB Deadlock detection (innodb_deadlock_detect).

Because we have advised against doing this so far, but I never stumbled upon this problem in practice, I have investigated a bit more about the MySQL variable innodb_deadlock_detect.

The MySQL documentation tells us the following [1]:

Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

And about the parameter innodb_deadlock_detect itself [2] itself:

This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

The problem is, that every time, when MySQL is doing a (Row) Lock or a Table Lock, it checks, if the Lock causes a Deadlock. This check is quite expensive. By the way: The feature disabling InnoDB Deadlock detection was developed by Facebook for WebScaleSQL [3].

The relevant functions can be found in [4]:

class DeadlockChecker, method check_and_resolve (DeadlockChecker::check_and_resolve) Every InnoDB (row) Lock (for mode LOCK_S or LOCK_X) and type ORed with LOCK_GAP or LOCK_REC_NOT_GAP, ORed with LOCK_INSERT_INTENTION Enqueue a waiting request for a lock which cannot be granted immediately. lock_rec_enqueue_waiting()

and

Every (InnoDB) Table Lock Enqueues a waiting request for a table lock which cannot be granted immediately. Checks for deadlocks. lock_table_enqueue_waiting()

This means if the variable innodb_deadlock_detect is enabled (= default) for every Lock (Row or Table) it is checked, if it causes a Deadlock. If the variable is disabled, the check is NOT done (which is faster) and the transaction hangs in (Dead-)Lock until the Lock is freed or the time innodb_lock_wait_timeout (default 50 seconds) is exceeded. Then the InnoDB Lock Wait Timeout (detector?) strikes and kills the transaction.

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+

This means, deactivating InnoDB Deadlock detection is interesting, if you have many (like Facebook!?!) short and small transactions where you expect little to now conflicts.
Further it is recommended, to set the MySQL variable innodb_lock_wait_timeout to a very small value (some seconds).

Because most of our customers do not have the size of Facebook and because they have rather not so many concurrent short and small transactions but few but long transactions (with probably many Locks and therefore a high Deadlock probability), I can imagine, disabling this parameter was responsible for the hickup (Locks are piling up) of the customer system. Which leads to exceeding max_connections and finally the whole system sticks.

Therefore I strongly recommend, to let InnoDB Deadlock detection enabled. Except you know exactly what your are doing (after about 2 weeks of extensive testing and measuring).

Literature Taxonomy upgrade extras: innodbdeadlocklockperformancelockingblock

FromDual is 10 years old

Shinguz - Mon, 2020-03-02 10:03

On 1 March 2020 FromDual became 10 years old! Sincere thanks are given to all our customers, partners and interested person for their support and good cooperation in the last 10 years. And we would be pleased to advise and support you again competently in the coming 10 years.

Your FromDual Team

Picture by kalhh on Pixabay

Taxonomy upgrade extras: fromdual

FromDual is 10 years old

Shinguz - Mon, 2020-03-02 10:03

On 1 March 2020 FromDual became 10 years old! Sincere thanks are given to all our customers, partners and interested person for their support and good cooperation in the last 10 years. And we would be pleased to advise and support you again competently in the coming 10 years.

Your FromDual Team

Picture by kalhh on Pixabay

Taxonomy upgrade extras: fromdual

FromDual is 10 years old

Shinguz - Mon, 2020-03-02 10:03

On 1 March 2020 FromDual became 10 years old! Sincere thanks are given to all our customers, partners and interested person for their support and good cooperation in the last 10 years. And we would be pleased to advise and support you again competently in the coming 10 years.

Your FromDual Team

Picture by kalhh on Pixabay

Taxonomy upgrade extras: fromdual

InnoDB Page Cleaner intended loop takes too long

Shinguz - Tue, 2020-02-18 17:50

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

You can find various advices in the Internet on to get rid of this Note:

innodb_lru_scan_depth = 1024, 256 innodb_buffer_pool_instances = 1, 8 innodb_io_capcity = 100, 200 or 1000 innodb_page_cleaners = 1, 4 or 8

But non of these changes made the Note go away in our case. I only found one voice claiming it could be an external reason which makes this message appear. Because we are actually running on a Cloud-Machine the appearance of this message could really be an effect of the Cloud and not caused by the Database or the Application.

We further know that our MariaDB Database has a more or less uniform workload over the day. Further it is a Master/Master (active/passive) set-up. So both nodes should see more or less the same write load at the same time.

But as our investigation clearly shows that the Note does not appear at the same time on both nodes. So I strongly assume it is a noisy-neighbour problem.

First we tried to find any trend or correlation between these 2 Master/Master Databases maas1 and maas2:

What we can see here is, that the message appeared on different days on maas1 and maas2. The database maas1 had a problem in the beginning of December and end of January. Database maas1 had much less problems in general but end of December there was a problem.

During night both instances seem to have less problems than during the day. And maas2 has more problems in the afternoon and evening.

If we look at the distribution per minute we can see that maas2 has some problems around xx:45 to xx:50 and maas1 more at xx:15.

Then we had a closer look at 28 January at about 12:00 to 15:00 on maas2:

We cannot see any anomalies which would explain a huge increase of dirty pages and and a page_cleaner stuck.

The only thing we could see at the specified time is that I/O latency significantly increased on server side. Because we did not cause more load and over-saturated the system it must be triggered externally:

This correlates quite well to the Notes we see in the MariaDB Error Log on maas2:

2020-01-28 12:45:27 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:46:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6908ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:46:32 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5339ms. The settings might not be optimal. (flushed=17 and evicted=0, during the time.) 2020-01-28 12:47:36 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4379ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:48:08 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5053ms. The settings might not be optimal. (flushed=7 and evicted=0, during the time.) 2020-01-28 12:48:42 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.) 2020-01-28 12:49:38 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4202ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 12:57:28 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4615ms. The settings might not be optimal. (flushed=18 and evicted=0, during the time.) 2020-01-28 12:58:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5593ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:58:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5442ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:59:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4327ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:00:05 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5154ms. The settings might not be optimal. (flushed=82 and evicted=0, during the time.) 2020-01-28 13:08:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4321ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:10:46 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 21384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 13:14:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4180ms. The settings might not be optimal. (flushed=20 and evicted=0, during the time.) 2020-01-28 13:14:49 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4935ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:15:20 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4472ms. The settings might not be optimal. (flushed=25 and evicted=0, during the time.) 2020-01-28 13:15:47 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4358ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:48:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6212ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:55:44 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4280ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:59:43 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5817ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:00:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 14:00:52 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9460ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:01:25 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7727ms. The settings might not be optimal. (flushed=103 and evicted=0, during the time.) 2020-01-28 14:01:57 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7154ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:02:29 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7501ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:03:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4322ms. The settings might not be optimal. (flushed=78 and evicted=0, during the time.) 2020-01-28 14:32:02 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4927ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:32:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4506ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.)
Taxonomy upgrade extras: innodbpage cleanerdirty pagesmigrationflushingnoisy neighbours

InnoDB Page Cleaner intended loop takes too long

Shinguz - Tue, 2020-02-18 17:50

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

You can find various advices in the Internet on to get rid of this Note:

innodb_lru_scan_depth = 1024, 256 innodb_buffer_pool_instances = 1, 8 innodb_io_capcity = 100, 200 or 1000 innodb_page_cleaners = 1, 4 or 8

But non of these changes made the Note go away in our case. I only found one voice claiming it could be an external reason which makes this message appear. Because we are actually running on a Cloud-Machine the appearance of this message could really be an effect of the Cloud and not caused by the Database or the Application.

We further know that our MariaDB Database has a more or less uniform workload over the day. Further it is a Master/Master (active/passive) set-up. So both nodes should see more or less the same write load at the same time.

But as our investigation clearly shows that the Note does not appear at the same time on both nodes. So I strongly assume it is a noisy-neighbour problem.

First we tried to find any trend or correlation between these 2 Master/Master Databases maas1 and maas2:

What we can see here is, that the message appeared on different days on maas1 and maas2. The database maas1 had a problem in the beginning of December and end of January. Database maas1 had much less problems in general but end of December there was a problem.

During night both instances seem to have less problems than during the day. And maas2 has more problems in the afternoon and evening.

If we look at the distribution per minute we can see that maas2 has some problems around xx:45 to xx:50 and maas1 more at xx:15.

Then we had a closer look at 28 January at about 12:00 to 15:00 on maas2:

We cannot see any anomalies which would explain a huge increase of dirty pages and and a page_cleaner stuck.

The only thing we could see at the specified time is that I/O latency significantly increased on server side. Because we did not cause more load and over-saturated the system it must be triggered externally:

This correlates quite well to the Notes we see in the MariaDB Error Log on maas2:

2020-01-28 12:45:27 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:46:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6908ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:46:32 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5339ms. The settings might not be optimal. (flushed=17 and evicted=0, during the time.) 2020-01-28 12:47:36 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4379ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:48:08 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5053ms. The settings might not be optimal. (flushed=7 and evicted=0, during the time.) 2020-01-28 12:48:42 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.) 2020-01-28 12:49:38 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4202ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 12:57:28 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4615ms. The settings might not be optimal. (flushed=18 and evicted=0, during the time.) 2020-01-28 12:58:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5593ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:58:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5442ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:59:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4327ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:00:05 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5154ms. The settings might not be optimal. (flushed=82 and evicted=0, during the time.) 2020-01-28 13:08:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4321ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:10:46 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 21384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 13:14:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4180ms. The settings might not be optimal. (flushed=20 and evicted=0, during the time.) 2020-01-28 13:14:49 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4935ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:15:20 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4472ms. The settings might not be optimal. (flushed=25 and evicted=0, during the time.) 2020-01-28 13:15:47 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4358ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:48:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6212ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:55:44 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4280ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:59:43 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5817ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:00:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 14:00:52 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9460ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:01:25 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7727ms. The settings might not be optimal. (flushed=103 and evicted=0, during the time.) 2020-01-28 14:01:57 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7154ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:02:29 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7501ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:03:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4322ms. The settings might not be optimal. (flushed=78 and evicted=0, during the time.) 2020-01-28 14:32:02 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4927ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:32:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4506ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.)
Taxonomy upgrade extras: innodbpage cleanerdirty pagesmigrationflushingnoisy neighbours

InnoDB Page Cleaner intended loop takes too long

Shinguz - Tue, 2020-02-18 17:50

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

You can find various advices in the Internet on to get rid of this Note:

innodb_lru_scan_depth = 1024, 256 innodb_buffer_pool_instances = 1, 8 innodb_io_capcity = 100, 200 or 1000 innodb_page_cleaners = 1, 4 or 8

But non of these changes made the Note go away in our case. I only found one voice claiming it could be an external reason which makes this message appear. Because we are actually running on a Cloud-Machine the appearance of this message could really be an effect of the Cloud and not caused by the Database or the Application.

We further know that our MariaDB Database has a more or less uniform workload over the day. Further it is a Master/Master (active/passive) set-up. So both nodes should see more or less the same write load at the same time.

But as our investigation clearly shows that the Note does not appear at the same time on both nodes. So I strongly assume it is a noisy-neighbour problem.

First we tried to find any trend or correlation between these 2 Master/Master Databases maas1 and maas2:

What we can see here is, that the message appeared on different days on maas1 and maas2. The database maas1 had a problem in the beginning of December and end of January. Database maas1 had much less problems in general but end of December there was a problem.

During night both instances seem to have less problems than during the day. And maas2 has more problems in the afternoon and evening.

If we look at the distribution per minute we can see that maas2 has some problems around xx:45 to xx:50 and maas1 more at xx:15.

Then we had a closer look at 28 January at about 12:00 to 15:00 on maas2:

We cannot see any anomalies which would explain a huge increase of dirty pages and and a page_cleaner stuck.

The only thing we could see at the specified time is that I/O latency significantly increased on server side. Because we did not cause more load and over-saturated the system it must be triggered externally:

This correlates quite well to the Notes we see in the MariaDB Error Log on maas2:

2020-01-28 12:45:27 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:46:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6908ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:46:32 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5339ms. The settings might not be optimal. (flushed=17 and evicted=0, during the time.) 2020-01-28 12:47:36 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4379ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:48:08 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5053ms. The settings might not be optimal. (flushed=7 and evicted=0, during the time.) 2020-01-28 12:48:42 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5760ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.) 2020-01-28 12:49:38 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4202ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 12:57:28 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4615ms. The settings might not be optimal. (flushed=18 and evicted=0, during the time.) 2020-01-28 12:58:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5593ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 12:58:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5442ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 12:59:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4327ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:00:05 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5154ms. The settings might not be optimal. (flushed=82 and evicted=0, during the time.) 2020-01-28 13:08:01 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4321ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:10:46 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 21384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 13:14:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4180ms. The settings might not be optimal. (flushed=20 and evicted=0, during the time.) 2020-01-28 13:14:49 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4935ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 13:15:20 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4472ms. The settings might not be optimal. (flushed=25 and evicted=0, during the time.) 2020-01-28 13:15:47 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4358ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:48:31 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6212ms. The settings might not be optimal. (flushed=9 and evicted=0, during the time.) 2020-01-28 13:55:44 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4280ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.) 2020-01-28 13:59:43 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5817ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:00:16 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5384ms. The settings might not be optimal. (flushed=100 and evicted=0, during the time.) 2020-01-28 14:00:52 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9460ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:01:25 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7727ms. The settings might not be optimal. (flushed=103 and evicted=0, during the time.) 2020-01-28 14:01:57 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7154ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:02:29 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7501ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.) 2020-01-28 14:03:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4322ms. The settings might not be optimal. (flushed=78 and evicted=0, during the time.) 2020-01-28 14:32:02 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4927ms. The settings might not be optimal. (flushed=4 and evicted=0, during the time.) 2020-01-28 14:32:34 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4506ms. The settings might not be optimal. (flushed=101 and evicted=0, during the time.)
Taxonomy upgrade extras: innodbpage cleanerdirty pagesmigrationflushingnoisy neighbours

FromDual Ops Center for MariaDB and MySQL 0.9.3 has been released

Shinguz - Mon, 2020-02-17 16:37

FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB and MySQL.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

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 for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.3

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

Upgrade from 0.9.x to 0.9.3

Upgrade from 0.9.x to 0.9.3 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.3 Machine
  • Machine without a usergroup is not allowed, fixed.
  • Machine name number is incremented by one if server already exists.
  • Delete machine fixed by setting server_id on instance to null.
  • Refresh repo automatization added.
  • Monitoring link added to menu.
  • Machine performance graphs added to menu.

Instance
  • Node renamed to Instance.
  • Create instance for CentOS and Ubuntu integrated.
  • Instance version comment was too long for PXC. Fixed.
  • Data Dictionary version added.
  • Special case for error log to syslog added (MariaDB packages on CentOS).
  • performance_schema last seen queries added.
  • Instance show overview made nicer.
  • Bug in instance check fixed.
  • Generate password improved thus bad special characters are not suggested any more.
  • Instance edit, eye added and field length shortened.
  • Instance name checking improved for creating and add.
  • Various minor bugs fixed.
  • Monitoring link added to menu.

Cluster
  • Cluster is clickable now in instance overview.
  • Minor Cluster bugs fixed.
  • Galera cluster added.
  • Master/Slave replication made smoother.

Load-Balancer
  • HAproxy and glb load-balancer added.

Tools
  • Jobs: Error logging improved to get more info about aborted jobs.
  • Crontab: Run job now icon added.
  • Schema compare: Schema drop-down sorted ascending and related code cleaned and refactored.

Configuration
  • Crontab: start_jobs.php was removed from crontab and is now started by run_crontab.php.
Database-as-a-Service (DBaaS)
  • Pricing plan added.
  • Database pricing added.
  • Machine cost added.
  • Resource cost included into job structure.

Building and Packaging
  • Installer: Repository installation hint added to installer.
  • Upgrade: Table fixed for MySQL 5.7.
  • Packaging: Session folder included into packaging.
  • Packaging: DEB and RPM improved for Upgrade.

Themes / UI
  • Default theme made a bit nicer.
  • Link more visible in default theme.

General
  • Disable license warning.
  • Link to fromdual license information added.
  • Jquery upgraded from 1.12.0 to 1.12.1.
  • http authentication brought to Apache version 2.4.
  • Session store changed because we loose very often ours sessions.
  • Session path also for all frag adapted.
  • Function implode syntax made compatible with newer PHP versions.
  • Minor typos fixed.
  • Minor errors fixed.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerglbHAProxyfocmm

FromDual Ops Center for MariaDB and MySQL 0.9.3 has been released

Shinguz - Mon, 2020-02-17 16:37

FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB and MySQL.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

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 for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.3

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

Upgrade from 0.9.x to 0.9.3

Upgrade from 0.9.x to 0.9.3 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.3 Machine
  • Machine without a usergroup is not allowed, fixed.
  • Machine name number is incremented by one if server already exists.
  • Delete machine fixed by setting server_id on instance to null.
  • Refresh repo automatization added.
  • Monitoring link added to menu.
  • Machine performance graphs added to menu.

Instance
  • Node renamed to Instance.
  • Create instance for CentOS and Ubuntu integrated.
  • Instance version comment was too long for PXC. Fixed.
  • Data Dictionary version added.
  • Special case for error log to syslog added (MariaDB packages on CentOS).
  • performance_schema last seen queries added.
  • Instance show overview made nicer.
  • Bug in instance check fixed.
  • Generate password improved thus bad special characters are not suggested any more.
  • Instance edit, eye added and field length shortened.
  • Instance name checking improved for creating and add.
  • Various minor bugs fixed.
  • Monitoring link added to menu.

Cluster
  • Cluster is clickable now in instance overview.
  • Minor Cluster bugs fixed.
  • Galera cluster added.
  • Master/Slave replication made smoother.

Load-Balancer
  • HAproxy and glb load-balancer added.

Tools
  • Jobs: Error logging improved to get more info about aborted jobs.
  • Crontab: Run job now icon added.
  • Schema compare: Schema drop-down sorted ascending and related code cleaned and refactored.

Configuration
  • Crontab: start_jobs.php was removed from crontab and is now started by run_crontab.php.
Database-as-a-Service (DBaaS)
  • Pricing plan added.
  • Database pricing added.
  • Machine cost added.
  • Resource cost included into job structure.

Building and Packaging
  • Installer: Repository installation hint added to installer.
  • Upgrade: Table fixed for MySQL 5.7.
  • Packaging: Session folder included into packaging.
  • Packaging: DEB and RPM improved for Upgrade.

Themes / UI
  • Default theme made a bit nicer.
  • Link more visible in default theme.

General
  • Disable license warning.
  • Link to fromdual license information added.
  • Jquery upgraded from 1.12.0 to 1.12.1.
  • http authentication brought to Apache version 2.4.
  • Session store changed because we loose very often ours sessions.
  • Session path also for all frag adapted.
  • Function implode syntax made compatible with newer PHP versions.
  • Minor typos fixed.
  • Minor errors fixed.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerglbHAProxy

FromDual Ops Center for MariaDB and MySQL 0.9.3 has been released

Shinguz - Mon, 2020-02-17 16:37

FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

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 for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.3

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

Upgrade from 0.9.x to 0.9.3

Upgrade from 0.9.x to 0.9.3 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.3 Machine
  • Machine without a usergroup is not allowed, fixed.
  • Machine name number is incremented by one if server already exists.
  • Delete machine fixed by setting server_id on instance to null.
  • Refresh repo automatization added.
  • Monitoring link added to menu.
  • Machine performance graphs added to menu.

Instance
  • Node renamed to Instance.
  • Create instance for CentOS and Ubuntu integrated.
  • Instance version comment was too long for PXC. Fixed.
  • Data Dictionary version added.
  • Special case for error log to syslog added (MariaDB packages on CentOS).
  • performance_schema last seen queries added.
  • Instance show overview made nicer.
  • Bug in instance check fixed.
  • Generate password improved thus bad special characters are not suggested any more.
  • Instance edit, eye added and field length shortened.
  • Instance name checking improved for creating and add.
  • Various minor bugs fixed.
  • Monitoring link added to menu.

Cluster
  • Cluster is clickable now in instance overview.
  • Minor Cluster bugs fixed.
  • Galera cluster added.
  • Master/Slave replication made smoother.

Load-Balancer
  • HAproxy and glb load-balancer added.

Tools
  • Jobs: Error logging improved to get more info about aborted jobs.
  • Crontab: Run job now icon added.
  • Schema compare: Schema drop-down sorted ascending and related code cleaned and refactored.

Configuration
  • Crontab: start_jobs.php was removed from crontab and is now started by run_crontab.php.
Database-as-a-Service (DBaaS)
  • Pricing plan added.
  • Database pricing added.
  • Machine cost added.
  • Resource cost included into job structure.

Building and Packaging
  • Installer: Repository installation hint added to installer.
  • Upgrade: Table fixed for MySQL 5.7.
  • Packaging: Session folder included into packaging.
  • Packaging: DEB and RPM improved for Upgrade.

Themes / UI
  • Default theme made a bit nicer.
  • Link more visible in default theme.

General
  • Disable license warning.
  • Link to fromdual license information added.
  • Jquery upgraded from 1.12.0 to 1.12.1.
  • http authentication brought to Apache version 2.4.
  • Session store changed because we loose very often ours sessions.
  • Session path also for all frag adapted.
  • Function implode syntax made compatible with newer PHP versions.
  • Minor typos fixed.
  • Minor errors fixed.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerglbHAProxy

FromDual Performance Monitor for MariaDB and MySQL 1.1.0 has been released

Shinguz - Tue, 2019-12-24 12:34

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular Database Performance Monitor for MariaDB, MySQL and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB and MySQL (fpmmm) enables DBAs and System Administrators to monitor what is going on inside their MariaDB and MySQL databases and on their machines where the databases reside.

More detailed information your can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In case you find a bug in the FromDual Performance Monitor for MariaDB and MySQL please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB and MySQL Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 1.1.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade from 1.0.x to 1.1.0 shell> cd /opt shell> tar xf /download/fpmmm-1.1.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.1.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB and MySQL 1.1.0

This release contains various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB and MySQL version with the following command:

shell> fpmmm --version
General
  • fpmmm is now available for Cent OS with RPM packages and for Ubuntu with DEB packages.
  • MariaDB 10.4 seems to work and thus is officially declared as supported.
  • TimeZone made configurable.
  • Error printed to STDOUT changed to STDERR.
  • Return codes made unique.
  • De-support PHP versions older than 7.0.
  • All old PHP 5.5 stuff removed, we need now at least PHP 7.0.
  • Cosmetic fixes and error handling improved.

fpmmm agent
  • Error message typo fixed.
  • All mpm remainings removed.
  • Upload: Error exit handling improved.

fpmmm Templates
  • InnoDB Template: Links to mysql-forum replaced by links to fromdual.com.
  • Templates: Zabbix 4.0 templates added and tpl directory restructured.

fpmmm Modules
  • Backup: Backup hook added to templates as example.
  • InnoDB: InnoDB buffer pool flushing data and graph added.
  • InnoDB: innodb_metrics replacing mostly SHOW ENGINE INNODB STATUS.
  • InnoDB: Started replacing SHOW ENGINE INNODB STATUS by I_S.innodb_metrics with Adaptive Hash Index (AHI).
  • InnoDB: innodb_file_format removed.
  • InnoDB: InnoDB files items and graph added.
  • InnoDB: Negative values of innodb_buffer_pool_pages_misc_b fixed.
  • InnoDB: Bug report of Wang Chao about InnoDB Adaptive Hash Index (AHI) size fixed.
  • Memcached: Memcached module fixed.
  • MySQL: MariaDB thread pool items and graph added.
  • MySQL: Slow Queries item fixed and graph added.
  • Server: Smartmon monitor added to monitor HDD/SSD.
  • Server: Server module made more robust and numactl replaced by cpuinfo.
  • Server: Server free function adapted according to Linux free command.
  • Server: Function getFsStatsLinux added for global file descriptor limits.
  • Aria: Aria cleaned-up, old mariadb_* variables removed, Aria transaction log graph added.
  • Aria: Aria pagecache blocks converted to bytes.

fpmmm agent installer
  • No changes.

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasrelease

FromDual Performance Monitor for MariaDB and MySQL 1.1.0 has been released

Shinguz - Tue, 2019-12-24 12:34

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular Database Performance Monitor for MariaDB, MySQL and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB and MySQL (fpmmm) enables DBAs and System Administrators to monitor what is going on inside their MariaDB and MySQL databases and on their machines where the databases reside.

More detailed information your can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In case you find a bug in the FromDual Performance Monitor for MariaDB and MySQL please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB and MySQL Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 1.1.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade from 1.0.x to 1.1.0 shell> cd /opt shell> tar xf /download/fpmmm-1.1.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.1.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB and MySQL 1.1.0

This release contains various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB and MySQL version with the following command:

shell> fpmmm --version
General
  • fpmmm is now available for Cent OS with RPM packages and for Ubuntu with DEB packages.
  • MariaDB 10.4 seems to work and thus is officially declared as supported.
  • TimeZone made configurable.
  • Error printed to STDOUT changed to STDERR.
  • Return codes made unique.
  • De-support PHP versions older than 7.0.
  • All old PHP 5.5 stuff removed, we need now at least PHP 7.0.
  • Cosmetic fixes and error handling improved.

fpmmm agent
  • Error message typo fixed.
  • All mpm remainings removed.
  • Upload: Error exit handling improved.

fpmmm Templates
  • InnoDB Template: Links to mysql-forum replaced by links to fromdual.com.
  • Templates: Zabbix 4.0 templates added and tpl directory restructured.

fpmmm Modules
  • Backup: Backup hook added to templates as example.
  • InnoDB: InnoDB buffer pool flushing data and graph added.
  • InnoDB: innodb_metrics replacing mostly SHOW ENGINE INNODB STATUS.
  • InnoDB: Started replacing SHOW ENGINE INNODB STATUS by I_S.innodb_metrics with Adaptive Hash Index (AHI).
  • InnoDB: innodb_file_format removed.
  • InnoDB: InnoDB files items and graph added.
  • InnoDB: Negative values of innodb_buffer_pool_pages_misc_b fixed.
  • InnoDB: Bug report of Wang Chao about InnoDB Adaptive Hash Index (AHI) size fixed.
  • Memcached: Memcached module fixed.
  • MySQL: MariaDB thread pool items and graph added.
  • MySQL: Slow Queries item fixed and graph added.
  • Server: Smartmon monitor added to monitor HDD/SSD.
  • Server: Server module made more robust and numactl replaced by cpuinfo.
  • Server: Server free function adapted according to Linux free command.
  • Server: Function getFsStatsLinux added for global file descriptor limits.
  • Aria: Aria cleaned-up, old mariadb_* variables removed, Aria transaction log graph added.
  • Aria: Aria pagecache blocks converted to bytes.

fpmmm agent installer
  • No changes.

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasrelease

FromDual Performance Monitor for MariaDB and MySQL 1.1.0 has been released

Shinguz - Tue, 2019-12-24 12:34

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular Database Performance Monitor for MariaDB, MySQL and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB and MySQL (fpmmm) enables DBAs and System Administrators to monitor what is going on inside their MariaDB and MySQL databases and on their machines where the databases reside.

More detailed information your can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In case you find a bug in the FromDual Performance Monitor for MariaDB and MySQL please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB and MySQL Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 1.1.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade from 1.0.x to 1.1.0 shell> cd /opt shell> tar xf /download/fpmmm-1.1.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.1.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB and MySQL 1.1.0

This release contains various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB and MySQL version with the following command:

shell> fpmmm --version
General
  • fpmmm is now available for Cent OS with RPM packages and for Ubuntu with DEB packages.
  • MariaDB 10.4 seems to work and thus is officially declared as supported.
  • TimeZone made configurable.
  • Error printed to STDOUT changed to STDERR.
  • Return codes made unique.
  • De-support PHP versions older than 7.0.
  • All old PHP 5.5 stuff removed, we need now at least PHP 7.0.
  • Cosmetic fixes and error handling improved.

fpmmm agent
  • Error message typo fixed.
  • All mpm remainings removed.
  • Upload: Error exit handling improved.

fpmmm Templates
  • InnoDB Template: Links to mysql-forum replaced by links to fromdual.com.
  • Templates: Zabbix 4.0 templates added and tpl directory restructured.

fpmmm Modules
  • Backup: Backup hook added to templates as example.
  • InnoDB: InnoDB buffer pool flushing data and graph added.
  • InnoDB: innodb_metrics replacing mostly SHOW ENGINE INNODB STATUS.
  • InnoDB: Started replacing SHOW ENGINE INNODB STATUS by I_S.innodb_metrics with Adaptive Hash Index (AHI).
  • InnoDB: innodb_file_format removed.
  • InnoDB: InnoDB files items and graph added.
  • InnoDB: Negative values of innodb_buffer_pool_pages_misc_b fixed.
  • InnoDB: Bug report of Wang Chao about InnoDB Adaptive Hash Index (AHI) size fixed.
  • Memcached: Memcached module fixed.
  • MySQL: MariaDB thread pool items and graph added.
  • MySQL: Slow Queries item fixed and graph added.
  • Server: Smartmon monitor added to monitor HDD/SSD.
  • Server: Server module made more robust and numactl replaced by cpuinfo.
  • Server: Server free function adapted according to Linux free command.
  • Server: Function getFsStatsLinux added for global file descriptor limits.
  • Aria: Aria cleaned-up, old mariadb_* variables removed, Aria transaction log graph added.
  • Aria: Aria pagecache blocks converted to bytes.

fpmmm agent installer
  • No changes.

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasrelease

Migration from MySQL 5.7 to MariaDB 10.4

Shinguz - Sat, 2019-11-30 14:17

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading
  • Make a backup first before you start!
  • Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
    The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
  • It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature
Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

Migration from MySQL 5.7 to MariaDB 10.4

Shinguz - Sat, 2019-11-30 14:17

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading
  • Make a backup first before you start!
  • Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
    The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
  • It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature
Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

Migration from MySQL 5.7 to MariaDB 10.4

Shinguz - Sat, 2019-11-30 14:17

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.columns_priv OK ... mysql.user OK Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist status : Operation failed sys.host_summary_by_file_io Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error error : Corrupt ... sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt ... sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases staging staging.sales OK staging.sugarcrm_contact_export Warning : Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. status : OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK

If you run the mysql_upgrade utility a 2nd time all issues are gone...

# mysql_upgrade --user=root --force
Some hints for upgrading
  • Make a backup first before you start!
  • Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
    The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
  • It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.

Literature
Taxonomy upgrade extras: upgradesidegrademigrationmariadbmysql5.710.4

FromDual Recovery Manager (rman) with progress indicator

Shinguz - Tue, 2019-08-20 21:44

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

FromDual Recovery Manager (rman) with progress indicator

Shinguz - Tue, 2019-08-20 21:44

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

FromDual Recovery Manager (rman) with progress indicator

Shinguz - Tue, 2019-08-20 21:44

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

Pages

Subscribe to FromDual aggregator - FromDual TechFeed (en)