You are here

Feed aggregator

FromDual Schulungen trotz Corona - einfach online und remote

Oli Sennhauser - Mon, 2020-03-23 15:43

Aufgrund der aktuellen Corona-Pandemie sind sämtliche FromDual vor Ort Schulungen, Schulungen bei unseren Schulungspartnern sowie FromDual Beratungseinsätze bis auf weiteres sistiert.

Damit Sie diesen Frühling aber nicht ganz ohne Weiterbildung auskommen müssen, sind wir bereits seit letzter Woche daran, alternative Möglichkeiten mittels online Schulungen zu testen. Erste Testschulungen wurden bereits Ende letzter Woche und anfangs dieser Woche durchgeführt.

Möglicherweise bietet sich Ihnen in den nächsten Wochen die Möglichkeit, aus dem Homeoffice die eine oder andere unserer online-Schulungen zu besuchen. Voraussichtlich betroffen sind die folgenden Schulungstermine:

  • 2. bis 4. April: Galera Cluster für MariaDB/MySQL im Linuxhotel in Essen
  • 23. bis 24. April: Galera Cluster für MariaDB/MySQL in der Heinlein Academy in Berlin
  • 4. bis 8. Mai: MariaDB/MySQL für Fortgeschrittene bei der GfU Cyrus in Köln
  • 11. bis 15. Mai: MariaDB/MySQL für Fortgeschrittene in der Heinlein Academy in Berlin

Ob es auch noch spätere Schulungstermine betrifft werden wir sehen...

Remote geht es besser - FromDual remote-DBA Dienstleistungen

Die Corona-Pandemie verursacht möglicherweise auch ein anderes oder neues Lastmuster auf Ihrer Datenbank.

  • Buchungen werden storniert (Reiseportale),
  • Rabatt-Aktionen gestartet (Outdoor-Aktivitäten),
  • es wird vermehrt online eingekauft (Webshops),
  • es wird vermehrt kommuniziert (VoIP, Video-Conferencing, Screen Sharing) oder auch
  • die Plattformen des Gesundheitswesen werden heftiger als sonst in Anspruch genommen.
  • etc.

Falls diese Situation bei Ihnen zu betrieblichen Problemen oder Performance-Engpässen führt, helfen wir Ihnen auch gerne mit einem remote-DBA Einsatz anstelle eines vor Ort Beratungseinsatzes weiter. Wir haben diese Technologien bereits seit Jahren im Einsatz und zeigen Ihnen auch gerne remote, wie Sie Ihre Probleme in den Griff kriegen.

Taxonomy upgrade extras: schulungremote-dbaremoteberatungconsultingtraining

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

Eher Finger weg: innodb_deadlock_detect

Oli Sennhauser - Fri, 2020-03-06 15:27

Kürzlich haben wir bei einem unserer Kunden, der gelegentlich massive Datenbankprobleme hat, bei der Durchsicht der MySQL Konfigurationsdatei (my.cnf) festgestellt, dass er die InnoDB Deadlock-Erkennung (innodb_deadlock_detect) deaktiviert hatte.

Da wir davon bisher immer abgeraten haben, ich aber noch nie konkret über dieses Problem gestolpert bin, bin ich der Sache noch etwas nachgegangen und habe zur Variable innodb_deadlock_detect recherchiert.

Die MySQL Dokumentation sagt dazu folgendes [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.

Und zum Parameter innodb_deadlock_detect selbst [2]:

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.

Das Problem ist, dass jedes mal, wenn MySQL einen (Row) Lock oder Table Lock macht, überprüft wird, ob dieser Lock einen Deadlock verursacht. Was entsprechend teuer ist. Diese Feature wurde übrigens von Facebook entwickelt [3].

Die entsprechenden Funktionen sind in [4] zu finden:

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()

und

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

Das heisst jetzt, wenn die Variable innodb_deadlock_detect eingeschaltet ist (= default) wird bei jedem Lock (Row oder Table) überprüft, ob dadurch ein Deadlock entsteht. Wenn die Variable ausgeschaltet ist, wird diese Überprüfung NICHT ausgeführt (was schneller ist) und die Transaktion bleibt im (Dead-)Lock hängen bis der Lock frei gegeben wird oder die Zeit innodb_lock_wait_timeout (default 50 Sekunden) überschritten ist. Dann schlägt der InnoDB Lock Wait Timeout (Detektor?) zu.

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

Das bedeutet, das Deaktivieren der InnoDB Deadlock Detektion ist interessant, wenn Sie sehr viele (wie Facebook!?!) kurze/kleine Transaktionen haben, bei welchen wenig bis keine Konflikte auftreten.
Im Weiteren wird empfohlen, gleichzeitig die innodb_lock_wait_timeout auf einen sehr geringen Wert (wenige Sekunden) einzustellen.

Da die meisten unserer Kunden aber nicht in die Kragenweite Facebook passen und tendenziell eher nicht viele gleichzeitige kurze/kleine Transaktionen haben sondern wenig lange Transaktionen (mit wahrscheinlich vielen Locks und daher einer grossen Deadlock-Wahrscheinlichkeit), kann ich mir durchaus vorstellen, dass das deaktivieren diese Parameters für das Verschlucken (Locks stauen auf) des Kundensystems verantwortlich ist, was anschliessen dazu führt, dass max_connections erreicht wird und schliesslich gar nichts mehr geht.

Daher würde ich dringend empfehlen, die InnoDB Deadlock Detektierung aktiviert zu lassen. Ausser man weiss genau, was man tut (nach ca. 2 Wochen testen und messen).

Literatur 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 ist 10 Jahre alt

Oli Sennhauser - Mon, 2020-03-02 10:01

Am 1. März 2020 wurde die FromDual GmbH 10 Jahre alt! Wir möchten allen Kunden, Partnern und Interessenten herzlich für die Unterstützung und gute Zusammenarbeit in den vergangenen 10 Jahren danken. Es würde uns freuen, Euch auch in den kommenden 10 Jahren kompetent beraten und betreuen zu dürfen.

Euer FromDual Team

Bild von kalhh auf 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

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 mit Galera Cluster und SQL Query Tuning an den Chemnitzer Linux-Tagen 2020

Oli Sennhauser - Tue, 2020-02-11 09:55

Zum 22. mal finden am 14. und 15. März 2020 die Chemnitzer Linux-Tage an der Technische Universität Chemnitz statt.

FromDual ist am Samstag, 14. März das erste mal mit einem Workshop "MariaDB Galera Cluster ganz einfach" und am Sonntag, 15. März wieder einmal mit einem Vortrag, diesmal zum Thema MariaDB Performance Tuning: "MariaDB SQL Query Tuning für Applikations-Entwickler", präsent.

Falls Sie also einmal unter Anleitung einen MariaDB Galera Cluster aufsetzen oder sich mit SQL Query Tuning unter MariaDB vertraut machen wollen, besuchen Sie doch einfach die Chemnitzer Linux-Tage 2020 und sitzen Sie in unseren Vortrag rein oder melden Sie sich für unseren Workshop an.

Bild von Génesis Gabriella auf Pixabay

FromDual wird offizieller MariaDB Partner

Oli Sennhauser - Tue, 2020-02-11 08:51

Nach Jahren der losen Zusammenarbeit wird FromDual jetzt, im Frühjahr 2020, offizieller MariaDB Partner.

Schon seit Beginn der MariaDB Erfolgsgeschichte befasst sich FromDual mit der Open Source Datenbank MariaDB und hat ihre Kunden mit Rat und Tat beim Betrieb der Datenbank unterstützt.

Da die Nachfrage nach MariaDB in den letzten Jahren laufend zu nahm, bietet FromDual schon seit längerem Schulungen, vor Ort Beratungen (Consulting), und remote-DBA Dienstleistungen für die MariaDB Datenbank an.

Der nächste Schritt, die offizielle Zusammenarbeit, ist somit schon längst fällig: Daher ist FromDual nun offizieller MariaDB Partner geworden.

Dieser Schritt ermöglicht es FromDual ihren Kunden auch beim Erwerb, der Nutzung und der Implementierung der MariaDB Enterprise Edition Subskription tatkräftig zu unterstützen um den grössten Nutzen aus dieser Datenbank zu ziehen.

Zudem wurden sämtliche FromDual Tools und die Monitoring-as-a-Service Dienstleistung für die MariaDB Datenbank funktionsfähig gemacht und werden schon seit einiger Zeit vollständig unterstützt.

Taxonomy upgrade extras: mariadbPartnerschaftpartnerenterprisesupportconsultingberatung

FromDual Performance Monitor 1.1.0 für MariaDB und MySQL freigegeben

Oli Sennhauser - Thu, 2020-02-06 14:19

FromDual hat die Version 1.1.0 des Performance Monitors für MariaDB, MySQL und Galera Cluster (fpmmm) freigegeben.

Dieser Performance Monitor ermöglicht es DBAs und Systemadministratoren einen tiefen Einblick in das Innenleben der Datenbank und des Servers, auf dem sich die Datenbank befindet, zu erhalten.

Die neue Version steht hier zum Download bereit.

Falls Sie sich die Mühe sparen wollen, eine eigene Monitoring-Infrastruktur aufzubauen, nutzen Sie doch einfach unsere kostengünstige Monitoring-as-a-Service Lösung. Gerne schicken wir Ihnen ein Angebot.

Wichtige Neuerungen

  • fpmmm steht jetzt paketiert für CentOS mit RPM und Ubuntu mit DEB Paketen zur Verfügung.
  • MariaDB 10.4 wird ab sofort offiziell durch fpmmm unterstützt.
  • fpmmm unterstützt ab sofort ausschliesslich PHP Version 7+.
  • Sämtliche Zabbix Templates wurden auf die Version 4.0 upgegraded.
  • Ein Backup-Hook wurde eingeführt. Sie können somit Ihr eigenes Backup-System oder den FromDual Backup Manager in fpmmm einbinden.
  • Graphen für InnoDB Buffer Pool flushing und InnoDB Files wurden hinzugefügt.
  • MariaDB Thread Pool Monitoring, für Anwendungen welche extrem viele Verbindungen zu Datenbank aufbauen, wurde hinzugefügt.
  • Die Graphen für den Aria Pagecache wurde verbessert.

Eine vollständige Liste der Verbesserungen finden Sie hier.

Eine detaillierte Anleitung zum fpmmm finden Sie im fpmmm Installation Guide.

Viel Spass beim Überwachen Ihrer MariaDB/MySQL Datenbank,
Ihr FromDual Team

Taxonomy upgrade extras: fpmmmreleasemonitoringmonitor

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

MariaDB und MySQL Schulungsprogramm 2020

Oli Sennhauser - Fri, 2019-12-13 16:02

Hiermit präsentieren wir Ihnen das FromDual Schulungsprogramm 2020 für MariaDB und MySQL. Auch nächstes Jahr führen wir wieder zahlreiche MariaDB und MySQL Schulungen für Anfänger und Fortgeschrittene bei unseren 3 Partnern in Essen, Köln und Berlin durch.

Die Liste mit Schulungsterminen 2020 finden Sie hier.

Welches für Sie das richtige Schulungsmodul ist, erläutern wir unter der Seite Schulungsmodule.

Gleich richtig los geht es anfangs Januar mit MariaDB und MySQL für Einsteiger. Dieser Kurs ist schon fast ausgebucht und findet daher sicher statt. Sichern Sie sich also noch die letzten Plätze!

FromDual Technik-Blog

Im FromDual Blog berichten wir regelmässig darüber, mit welchen Themen wir unser Wissen erweitert haben. Diese Artikel sind frei verfügbar und somit auch Ihnen zugänglich:


Werkzeuge für den täglichen Datenbankbetrieb

FromDual bietet Ihnen auch eine breite Palette von nützlichen Werkzeuge für den täglichen Betrieb und die Pflege Ihrer MariaDB und MySQL Datenbanken an:


Monitoring as a Service

Falls Sie sich keine eigene Monitoring Lösung antun wollen, bieten wir Ihnen auch gerne unsere preiswerte Monitoring-as-a-Service (MaaS) Lösung an. Nehmen Sie hierzu einfach mit uns Kontakt auf. Wir unterbreiten Ihnen gerne ein Angebot.

Ich denke, hiermit haben Sie genügend zu tun, damit Ihnen über die Feiertag nicht langweilig wird... :-)

Schöne Weihnachten und einen gute Rutsch ins neue Jahr wünscht Ihnen
Ihr FromDual Schulungsteam

Taxonomy upgrade extras: 2020schulungtrainingmysql-trainingmariadb traininggalera cluster trainingmariadb schulunggalera cluster schulung

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

Schulung Galera Cluster für MariaDB und MySQL im September 2019 in Berlin

Shinguz - Sun, 2019-08-18 21:38

Die Sommerferien sind vorbei. Mit neuem Elan in den Herbst! Zeit für eine Weiterbildung?

Vom 19. bis 20. September führt FromDual wieder die Galera Cluster Schulung Galera Cluster für MySQL und MariaDB in Berlin durch. Siehe auch unsere weiteren Schulungstermine.

Es hat noch Plätze frei! Anmelden können Sie sich direkt bei unserem Schulungs-Partner, der Heinlein Akademie.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken mit einem Galera Cluster zu betreuen haben und gerne besser verstehen wollen, wie Sie den Galera Cluster sicher und stabil betreiben.

In dieser Schulung behandeln wir, wie Sie einen Galera Cluster richtig designen und aufsetzten, wie Sie ihn installieren, konfigurieren und betreiben. Zudem betrachten wir mögliche Load Balancing Mechanismen und besprechen Performance Fragen zu Galera.

Das Ganze ist mit zahlreichen Übungen versehen, damit Sie das gelernte auch gleich praktisch anwenden können!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser zweitägigen Galera Cluster Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulunggaleraclustertraining

MariaDB and MySQL Character Set Conversion

Shinguz - Sat, 2019-08-17 21:50
Contents Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well [ Lit. ]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision:

  • 2 smaller changes are easier to control then one big shot.
  • We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs.

For Upgrading see also MariaDB and MySQL Upgrade Problems

Remark: It makes possibly also sens to think about Collations before starting with the conversion!

Character Sets

Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere.

But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well.

The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0:

SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+

The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step:

SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec

So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization).

For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4!

Steps to convert Character Set to utf8mb4 Analyzing the Server

First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set:

# locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL=

On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands:

SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+

These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem.

Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set:

SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+

The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ]

Analyzing the Application and the Clients

Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions:

  • Support of utf8 of Application/Client O/S (Windows)?
  • Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...)
  • Version of programming language (Java, PHP (5.4 and newer?), ...)
  • Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...)
  • Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc.

See also: Configuring Application Character Set and Collation

If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on:

190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit

or with some queries on the PERFORMANCE_SCHEMA:

-- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+
Preparation of the Server Settings and the Application

To have a better control of the impact of some changes we decided to do some changes on the Application first:

  • Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future.
  • Apache and PHP are configured to support UTF-8.
  • After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ]
Convert Tables to utf8mb4

First we checked and converted the default Character Set of the Schemata/Databases:

SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+

Converting the Schemata is done as follows:

SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4;

which is a fast operation.

To convert the tables there are many different possibilities we considered:

  • The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;
  • The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/'
  • The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump).
  • The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ]
  • The most elegant but not supported one: Master/Slave Replication.

Already with converting the first table we run into a problem:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+

So we have to find out first, which tables are still in old Antelope File Format:

SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+

Then we could covert the table correctly forcing the Barracuda File format:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic;
Testing of new Character Set

The last but most important step is to test the changes. Here we recommend to do as many as possible different tests:

  • MySQL CLI: mysql
  • phpMyAdmin
  • MySQL Workbench
  • Other GUIs
  • Your Application

Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment.

Some aid you also can get from here:


MySQL Pump

mysqlpump — A Database Backup Program

This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub).

Interesting features are:

  • Parallel dumping of databases.
  • Secondary Index restore separated from Table Restore.

Missing features are:

  • Does not support MariaDB and MySQL 5.6 and earlier.
  • No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication.

First test:

# mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`);

Test against MySQL 5.6:

Server version is not compatible. Server version should be 5.7.8 or above.
MySQL Master/Slave Replication for Character Set conversion

Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported!

Some tests:

MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+

See also: Why is varchar(255) not varchar(255)? And:

MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+
MySQL Shell, mysqlsh Upgrade Checker Utility

Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ]

# ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@127.0.0.1:3309') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@127.0.0.1:3309', {'targetVersion':'5.7.27'}) The MySQL server at 127.0.0.1:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError)

Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables.

Taxonomy upgrade extras: character setconversionupgradelatin1utf8utf8mb4replicationmysqlshmysqlpump

FromDual Ops Center for MariaDB and MySQL 0.9.2 has been released

Shinguz - Tue, 2019-08-13 16:50

FromDual has the pleasure to announce the release of the new version 0.9.2 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 manage 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.2

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.2

Upgrade from 0.9.x to 0.9.2 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.2 Instance
  • Replication: Mariadb 10.4 problem with mysql.user table fixed.
  • Restore: Clean-up work done.
  • Restore: Point-in-Time-Recovery is implemented.
  • Operations: Binary Log state added.
Security
  • Potential XSS hole fixed.
  • Bootstrap CSS updated to 4.3.1
General
  • Newest MyEnv Library added.
  • Function array_pop replaced by array_shift.
  • Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.
Build and Packaging
  • Dependency bug in Debian control file fixed.
Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestoreFromDual Ops Centerops center

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.1 has been released

Shinguz - Tue, 2019-08-06 09:12

FromDual has the pleasure to announce the release of the new version 2.2.1 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager 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 feedback@fromdual.com.

Upgrade from 2.x to 2.2.1 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.1.tar.gz shell> rm -f brman shell> ln -s brman-2.2.1 brman
Changes in FromDual Backup and Recovery Manager 2.2.1

This release is a new minor release. It contains only bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General
  • No changes.

FromDual Backup Manager
  • Workaround for MariaDB binlog purge problem added do binlog backup.

FromDual Recovery Manager
  • PiTR did not find binary logs in case they were from a foreign instance.

FromDual brman Catalog
  • No changes.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.0 has been released

Shinguz - Mon, 2019-07-29 16:51

FromDual has the pleasure to announce the release of the new version 2.2.0 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager 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 feedback@fromdual.com.

Upgrade from 2.x to 2.2.0 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.0.tar.gz shell> rm -f brman shell> ln -s brman-2.2.0 brman
Changes in FromDual Backup and Recovery Manager 2.2.0

This release is a new major release series. It contains some new features. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General
  • Automated testing improved.
  • Packaging optimized. Especially unnecessary dependencies removed.
  • Code redundancy removed, made code more readable and partially code reorganized.

FromDual Backup Manager
  • Requirement checks improved. Warnings do not spoil rc any more.
  • Bug with --pass-through and --per-schema fixed (MGB).
  • Backup size is now correctly reported to fpmmm. And message is written to the log.
  • Timestamp format was wrong in start backup tag.
  • Globals removed from function doPrivilegeBackup.
  • Columns of table mysql.user quoted for MariaDB 10.4 compatibility.
  • Test on fpmmm-cache-file fixed from dir to file.
  • Some bugs in function binlogBackup simulation fixed.
  • Too verbose debugging disabled.
  • Sftp archiving did not fail or complain if directory does not exist.
  • Better input validation for option --archivedestination.
  • Function checkArguments renamed to checkBmanOptions.
  • Wrong error message for servers without binary log removed.
  • Skip gathering of binlog file and pos in case of binary log was not enabled.

FromDual Recovery Manager
  • Missing --stop-instance option added to usage.
  • Omitted option --policy PHP error fixed for logical and physical restore, this allows us to restore from conventional backup infrastructure as well.
  • Tables slave_master_info and slave_relay_log_info in mysql schema are omitted in restore progress output.
  • Error message for missing --backup-name option improved.
  • Error handling and messages improved as preparation for PiTR.
  • Binlog restore type removed and check made accordingly that it is not possible any more.
  • PiTR pre Recover test implemented.
  • Various tests for test automation improved.
  • Binlog position discovery possible also for compressed files.
  • Execution time information added.
  • Option --debug added to rman.
  • Point-in-Time-Recovery for logical and physical backup implemented.
  • Instance starting for physical backup was implemented.
  • Option --simulate was implemented everywhere.
  • Option --disable-log-bin added and error handling improved.
  • Option --config activated in rman.
  • Fast PiTR is implemented.
  • Only full physical restore is supported atm. This was not caught correctly.
  • Schema PiTR also possible.

FromDual brman Catalog
  • No changes.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrman

Schulung MariaDB/MySQL für Fortgeschrittene im August 2019 in Köln

Shinguz - Mon, 2019-07-29 14:29

Sommerferien-Zeit – für all die Daheimgebliebenen dürfte es jetzt hoffentlich etwas ruhiger zu und her gehen. Zeit für eine Weiterbildung? Es bleibt nicht mehr viel Zeit, das Jahres-Schulungs-Budget aufzubrauchen!

Vom 19. bis 23. August führt FromDual wieder einmal die Schulung MariaDB und MySQL für Fortgeschrittene in Köln durch. Siehe auch unsere Schulungstermine.

Es hat noch Plätze frei! Anmelden kann man sich direkt bei unserem Schulungs-Partner, der GFU Cyrus AG.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken zu betreuen habe und gerne besser verstehen wollen, wie man das noch besser macht.

In dieser Schulung behandeln wir Backup, Restore und Point-in-Time-Recovery sowohl einer kleinen wie auch einer grossen Datenbank. Aufsetzen von hochverfügbaren MariaDB und MySQL Datenbanken mittels der Master/Slave Replikation sowie dem Galera Cluster inklusive Switch-Over-Techniken. Schliesslich und endlich beschäftigen wir uns auch noch zwei Tage mit Datenbank Performance Tuning (Hardware, O/S, DB Konfiguration, Schema Tuning und SQL Query Tuning, etc.).

Das Ganze ist mir zahlreichen Übungen versehen, damit man das gelernte auch gleich praktisch anwenden kann!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser fünftägigen MySQL/MariaDB Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulungtraininggaleraBackupRestorepoint-in-time-recoveryreplikationPerformance Tuning

Pages

Subscribe to FromDual aggregator