You are here

FromDual TechFeed (en)

MariaDB SQL Error Log Plugin

Shinguz - Thu, 2020-07-30 12:26

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something "new" I did not know yet...

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were sent from the MariaDB daemon to applications/clients and writes it to a log file.

This Client error log file can be analysed later to find and fix bugs in the application if the applications did not catch the errors themselves.

Installation

The Plugin can be easily installed with:

SQL> INSTALL PLUGIN sql_error_log SONAME 'sql_errlog';

and uninstalled again with:

SQL> UNINSTALL PLUGIN sql_error_log; Query OK, 0 rows affected, 1 warning (0.002 sec)

The installation and uninstallation informations are persistent and survive a database instance restart.

If you uninstall the plugin you get a warning:

SQL> SHOW WARNINGS; +---------+------+----------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------+ | Warning | 1620 | Plugin is busy and will be uninstalled on shutdown | +---------+------+----------------------------------------------------+

telling you that the plugin is still running until the next database instance restart.

If the plugin currently is installed you can find with the following command:

SQL> SELECT * FROM information_schema.plugins WHERE PLUGIN_TYPE = 'AUDIT'\G *************************** 1. row *************************** PLUGIN_NAME: SQL_ERROR_LOG PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 3.2 PLUGIN_LIBRARY: sql_errlog.so PLUGIN_LIBRARY_VERSION: 1.14 PLUGIN_AUTHOR: Alexey Botchkov PLUGIN_DESCRIPTION: Log SQL level errors to a file with rotation PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0
Variables

With the command:

SQL> SHOW GLOBAL VARIABLES LIKE 'sql_error_log%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | sql_error_log_filename | sql_errors.log | | sql_error_log_rate | 1 | | sql_error_log_rotate | OFF | | sql_error_log_rotations | 9 | | sql_error_log_size_limit | 1000000 | +--------------------------+----------------+

you can see the SQL Error Log configuration variables. These variables are NOT shown if the the plugin is not installed yet or uninstalled again.

The SQL Error Log is written by default to $datadir/sql_error.log and cannot be change during runtime:/

SQL> SET GLOBAL sql_error_log_filename = '/home/mysql/database_slow/mariadb-105/log/sql_errors.log'; ERROR 1238 (HY000): Variable 'sql_error_log_filename' is a read only variable
Log Rotation

The other four variables are related to log rotation. It is cool, that this is done by the database and has not to be done by O/S means: See also: MariaDB Log Rotation.

On the first look it did not work like expected. But when reading the documentation again: This is the 'write-only' variable. Assigning TRUE to this variable forces the log rotation. it becomes understandable. If you enter:

SQL> SET GLOBAL sql_error_log_rotate=1; Query OK, 0 rows affected (0.000 sec)

the SQL Error Log is rotated in the background and you can see this in $datadir:

shell> ls -ltr sql_errors.log* -rw-rw---- 1 mysql mysql 53994 Jul 30 10:28 sql_errors.log.4 -rw-rw---- 1 mysql mysql 0 Jul 30 10:28 sql_errors.log.3 -rw-rw---- 1 mysql mysql 1517 Jul 30 10:29 sql_errors.log.2 -rw-rw---- 1 mysql mysql 0 Jul 30 10:29 sql_errors.log.1 -rw-rw---- 1 mysql mysql 6337 Jul 30 10:34 sql_errors.log

You should think in the way of: FLUSH SQL_ERROR LOGS;

The variable itself always stays OFF:

SQL> SHOW GLOBAL VARIABLES LIKE 'sql_error_log_rotate'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | sql_error_log_rotate | OFF | +----------------------+-------+
Status Information

There are currently no status variables available to show how many errors were sent to the application.

Options

There is a configuration option NOT shown in the variable output: sql_error_log. Valid values are OFF, ON, FORCE and FORCE_PLUS_PERMANENT. It looks like that this option can only me used when injected directly into the call of the mysqld (mysqld_safe, systemd unit file, etc.) and thus it is useless for most cases because you do not want to change those things in production (upgrade, etc.).

Enabling/Disabling of the SQL Error Log

You can disable the SQL Error Log with the following command:

SQL> SET GLOBAL sql_error_log_rate = 0;

You enable it again by setting the value greater than 0.

Interpretation of the SQL Error Log

The format of the SQL Error Log looks roughly as follows: timestamp user[user] @ host[host] ERROR nnnn: Error Message : SQL Query

Here some examples:

shell> grep 2020 sql_errors.log 2020-07-30 10:30:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS -- All users 2020-07-30 10:36:51 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 2020-07-30 10:37:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 2020-07-30 10:40:03 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null)

This helped us for example to identify easily what privileges were missing for our applications.

Aggregation of the SQL Error Log

A simple way to aggregate the SQL Error Log is the following command:

shell> cat sql_errors.log* | sed 's/users2020/users\n2020/' | grep ^2020 | cut -b21- | sort | uniq -c 21 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_current' : SELECT th.PROCESSLIST_USER AS user 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_summary_by_user_by_event_name' : SELECT user, SUM(sum_created_tmp_disk_tables) AS created_tmp_disk_tables 22 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT processlist_user AS user, COUNT(*) AS connections 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT th.processlist_user AS user, SUM(trx.trx_rows_locked) AS rows_locked 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT trx.trx_started, trx.trx_query 16 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT DISTINCT user AS user FROM mysql.user 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND create_user_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND grant_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "root" AND host NOT IN ("localhost", "127.0.0.1", "::1", "chef") 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND super_priv = "Y" 20 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 32 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null) 1 [] @ localhost [] ERROR 1160: Got an error writing communication packets : (null) 1 root[root] @ localhost [] ERROR 1049: Unknown database 'bla' : (null) 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SONAME 'sql_errlog'' at line 1 : UNINSTALL PLUGIN sql_error_log SONAME 'sql_errlog' 1 root[root] @ localhost [] ERROR 1193: Unknown system variable 'sql_error_log_rotat' : set global sql_error_log_rotat=on 1 root[root] @ localhost [] ERROR 1229: Variable 'sql_error_log_rotate' is a GLOBAL variable and should be set with SET GLOBAL : set session sql_error_log_rotate=on 1 root[root] @ localhost [] ERROR 1238: Variable 'sql_error_log_filename' is a read only variable : set global sql_error_log_filename='/home/mysql/database_slow/mariadb-105/log/sql_errors.log'

The sed command was needed because it looks like not every new entry starts at the beginning of a new line (bug?).

How to get warnings

The default sql_mode in MariaDB 10.5 is set to:

SQL> SHOW GLOBAL VARIABLES LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+

by adding TRADITIONAL to the sql_mode:

SQL> SET SESSION sql_mode=concat(@@sql_mode, ',TRADITIONAL');

you can make the SQL Error Log plugin also aware of warnings:

SQL> insert into test.test values (null, 'Some data', '2020-02-31'); Query OK, 1 row affected, 1 warning (0.001 sec) SQL> set session sql_mode=traditional; SQL> insert into test.test values (null, 'Some data', '2020-02-31'); ERROR 1292 (22007): Incorrect datetime value: '2020-02-31' for column `test`.`test`.`ts` at row 1

Just for curiosity, this query was never logged to the SQL Error Log:

SQL> SELECT 5/0; +------+ | 5/0 | +------+ | NULL | +------+ 1 row in set, 1 warning (0.000 sec)

But this seems to be more of a parser issue?

Logging comments

Comments are also logged. But the MariaDB client by default strips comments away. When you run the MariaDB client with the --comment option they are present to the server:

shell> mariadb --user=root --comment test SQL> /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31'); -- blub ERROR 1365 (22012): Division by 0

And it will be shown in the SQL Error Log:

2020-07-30 11:23:31 root[root] @ localhost [] ERROR 1365: Division by 0 : insert into test.test values (5/0, 'Some data', '2020-02-31') 2020-07-30 11:23:08 root[root] @ localhost [] ERROR 1365: Division by 0 : /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31') -- blub
Summary

The SQL Error Log is a cool plugin which helps us to identify problems and bugs in the application. This is similar functionality like in MariaDB/MySQL PERFORMANCE_SCHEMA: Finding syntactically wrong/erroneous MySQL queries.

Taxonomy upgrade extras: mariadbsqlerrorloggingerror logsyntax

FromDual Ops Center File Transfer

Shinguz - Tue, 2020-07-28 09:59

With the FromDual Ops Center file transfer tool you can easily upload files from your personal computer to the focmm machine, download files from the focmm machine to your personal computer or transfer files from the focmm machine to any of your database machines or between them. This feature is made for importing, exporting or transferring data from, to or between your different database instances. For example to copy a production schema to a testing database instance.

For backup and restore of a database instance or schema see Operations.

You can reach the file transfer tool under the menu Tools on the left and then File transfer.

Upload

For uploading a file just click to the Browse... button, select a file from your local file system and then click to Upload File.

After a while, when the upload is completed, the file will appear in the Download section of your focmm machine.

Alternatively you can also upload files from your personal computer to the focmm machine via your favourite file transfer tools like scp or sftp.

Download

If you want to download a file from your focmm machine to your personal computer just click on one of the files shown in the download section. A window will pop-up and let you choose the location to store the file to.

Alternatively you can also download files from your focmm machine to your personal computer via your favourite file transfer tools like scp or sftp.

Transfer

To transfer files between your focmm machine and your database machines or between your different database machines you have to choose the machines first, then navigate through your file system tree on each server and then press the transfer link (arrow left to right or arrow right to left) to transfer the file.

Taxonomy upgrade extras: focmmFromDual Ops Centerfilefile transfer

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

  • The first column shows if the crontab job is active or not.
  • The second column indicates the O/S user the crontab job should run as.
  • Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
  • Now follows the actual command which should be run with a comment describing the command.
  • The column Last indicates when the job was run last successfully.
  • And RC shows the last Return Code of the job (0 - success, otherwise error number).
  • With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmmFromDual Ops Centercrontabcentralization

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

  • The first column shows if the crontab job is active or not.
  • The second column indicates the O/S user the crontab job should run as.
  • Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
  • Now follows the actual command which should be run with a comment describing the command.
  • The column Last indicates when the job was run last.
  • And RC shows the last Return Code of the job (0 - success, otherwise error number).
  • With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmmFromDual Ops Centercrontabcentralization

Increase file limit of a running process

Shinguz - Fri, 2020-06-19 18:53

Asking stupid questions and googling for them is fun some times...

Today I was asking myself if one could rise the file limit for a running MariaDB mysqld process online without restarting the database instance?

And I found an answer on serverfault: Set max file limit on a running process:

PID=$(pidof mysqld) grep -e 'Max open files' -e Limit /proc/${PID}/limits Limit Soft Limit Hard Limit Units Max open files 1024 4096 files prlimit --pid $PID | grep -e NOFILE -e DESC RESOURCE DESCRIPTION SOFT HARD UNITS NOFILE max number of open files 1024 4096 files prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 1024 4096 sudo prlimit --nofile=2048:8192 --pid ${PID} prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 2048 8192
Literature

prlimit(1)

See also:


Taxonomy upgrade extras: open_files_limitLimitNOFILEfile handles

Increase file limit of a running process

Shinguz - Fri, 2020-06-19 18:53

Asking stupid questions and googling for them is fun some times...

Today I was asking myself if one could rise the file limit for a running MariaDB mysqld process online without restarting the database instance?

And I found an answer on serverfault: Set max file limit on a running process:

PID=$(pidof mysqld) grep -e 'Max open files' -e Limit /proc/${PID}/limits Limit Soft Limit Hard Limit Units Max open files 1024 4096 files prlimit --pid $PID | grep -e NOFILE -e DESC RESOURCE DESCRIPTION SOFT HARD UNITS NOFILE max number of open files 1024 4096 files prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 1024 4096 sudo prlimit --nofile=2048:8192 --pid ${PID} prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 2048 8192
Literature

prlimit(1)

See also:


Taxonomy upgrade extras: open_files_limitLimitNOFILEfile handles

FromDual Performance Monitor for MariaDB 1.2.0 has been released

Shinguz - Fri, 2020-06-12 16:47

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

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

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

Download

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

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB 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 us.

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 1.2.0

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

Upgrade of fpmmm tarball from 1.0.x to 1.2.0

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-1.2.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.2.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 1.2.0

This release contains new features and various bug fixes.

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

shell> fpmmm --version
General
  • MariaDB 10.5 problems fixed. Fpmmm supports MariaDB 10.5 now!
  • Naming convention for Type changed from host to machine and mysqld to instance, including downwards compatibility.
  • New host screens added.
  • All Screens removed because they are customer specific, we have host screens now.
  • Zabbix templates adapted to the more flexible trigger URL.
  • Renamed all files to make it more agnostic.

Server
  • Code made more robust for cloud databases.
  • Free file descriptors removed because it is always zero, trigger added for 80% file descriptors used.
  • Cache file base bug in getIostat fixed.
  • Server graph for file descriptors improved
  • I/O queue ymin set to 0.
  • Server template optimized.
  • Iostat graphs added to server template.
  • Integrated iostat data into fpmmm.
  • All registered devices and bug on svctm fixed.
  • More info added when server module is called with --debug option.
  • Disk status items cleaned-up and filesystem names added for creating new items.
  • Interface eth1 removed but list of all interfaces added.
  • NUMA trigger added.
  • Macros for network interfaces added.

Data
  • Data module added to measure schema and instance size.
  • Code made ready for cloud databases.

Galera
  • Galera cluster size graph y axis set to 0.
  • 3 Galera graphs added, URL added to some triggers, title of one trigger changed.
  • gmcast segment added.
  • Locale state name of item fixed, local status removed, cluster and local state added, gmcast segment added to template.
  • wsrep version fixed to new format.
  • Item name change for cluster_conf_id.
  • Dirty code fixed, found on cloud databases.

User
  • Module for per user data added.
  • Dirty code fixed, found on cloud databases.
  • User info for transactions added.
  • Tmp disk tables and sort merge passes per user information added.

Agent
  • Output format zabbix, icinga, nagios and centreon should be supported now.
  • Error messages for connect improved.
  • Option --debug added, one message was not handled correctly in verbosity level.
  • Parameters in function goThroughAllSections cleaned-up.
  • Option -h added, info more clear when wrong options were used.
  • URLs added to fpmmm template.
  • fpmmm check and trigger improved.
  • Made error handling better after test of 1.1.0 on CentOS 7.
  • fpmmm trigger error message improved.

InnoDB
  • NUMA information and warning trigger added to InnoDB module.
  • Trigger for innodb_force_recovery made repeatable.
  • Alert level for innodb_force_recovery increased, InnoDB non default page size alert added.
  • InnoDB deadlock detection is alarmed, when disabled.
  • innodb_metrics only works with SUPER privilege, fixed.
  • InnoDB Log Buffer much to small trigger added on Innodb_log_waits item.
  • Items and graphs for InnoDB temporary tables added.

MyISAM
  • Items and graphs for MyISAM temporary tables added.

Aria
  • Items and graphs for Aria temporary tables added.

Security
  • Expired user added for MariaDB including alert.

Slave
  • URL added and two triggers made repeatable.

Backup
  • Backup will report EVERY failure and URL is now useful!

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

FromDual Performance Monitor for MariaDB 1.2.0 has been released

Shinguz - Fri, 2020-06-12 16:47

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

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

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

Download

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

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB 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 us.

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 1.2.0

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

Upgrade of fpmmm tarball from 1.0.x to 1.2.0

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-1.2.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.2.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 1.2.0

This release contains new features and various bug fixes.

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

shell> fpmmm --version
General
  • MariaDB 10.5 problems fixed. Fpmmm supports MariaDB 10.5 now!
  • Naming convention for Type changed from host to machine and mysqld to instance, including downwards compatibility.
  • New host screens added.
  • All Screens removed because they are customer specific, we have host screens now.
  • Zabbix templates adapted to the more flexible trigger URL.
  • Renamed all files to make it more agnostic.

Server
  • Code made more robust for cloud databases.
  • Free file descriptors removed because it is always zero, trigger added for 80% file descriptors used.
  • Cache file base bug in getIostat fixed.
  • Server graph for file descriptors improved
  • I/O queue ymin set to 0.
  • Server template optimized.
  • Iostat graphs added to server template.
  • Integrated iostat data into fpmmm.
  • All registered devices and bug on svctm fixed.
  • More info added when server module is called with --debug option.
  • Disk status items cleaned-up and filesystem names added for creating new items.
  • Interface eth1 removed but list of all interfaces added.
  • NUMA trigger added.
  • Macros for network interfaces added.

Data
  • Data module added to measure schema and instance size.
  • Code made ready for cloud databases.

Galera
  • Galera cluster size graph y axis set to 0.
  • 3 Galera graphs added, URL added to some triggers, title of one trigger changed.
  • gmcast segment added.
  • Locale state name of item fixed, local status removed, cluster and local state added, gmcast segment added to template.
  • wsrep version fixed to new format.
  • Item name change for cluster_conf_id.
  • Dirty code fixed, found on cloud databases.

User
  • Module for per user data added.
  • Dirty code fixed, found on cloud databases.
  • User info for transactions added.
  • Tmp disk tables and sort merge passes per user information added.

Agent
  • Output format zabbix, icinga, nagios and centreon should be supported now.
  • Error messages for connect improved.
  • Option --debug added, one message was not handled correctly in verbosity level.
  • Parameters in function goThroughAllSections cleaned-up.
  • Option -h added, info more clear when wrong options were used.
  • URLs added to fpmmm template.
  • fpmmm check and trigger improved.
  • Made error handling better after test of 1.1.0 on CentOS 7.
  • fpmmm trigger error message improved.

InnoDB
  • NUMA information and warning trigger added to InnoDB module.
  • Trigger for innodb_force_recovery made repeatable.
  • Alert level for innodb_force_recovery increased, InnoDB non default page size alert added.
  • InnoDB deadlock detection is alarmed, when disabled.
  • innodb_metrics only works with SUPER privilege, fixed.
  • InnoDB Log Buffer much to small trigger added on Innodb_log_waits item.
  • Items and graphs for InnoDB temporary tables added.

MyISAM
  • Items and graphs for MyISAM temporary tables added.

Aria
  • Items and graphs for Aria temporary tables added.

Security
  • Expired user added for MariaDB including alert.

Slave
  • URL added and two triggers made repeatable.

Backup
  • Backup will report EVERY failure and URL is now useful!

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

FromDual Performance Monitor for MySQL 1.2.0 has been released

Shinguz - Fri, 2020-06-12 16:42

FromDual has the pleasure to announce the release of the new version 1.2.0 of its popular Database Performance Monitor for MySQL fpmmm.

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

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

Download

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

In the inconceivable case that you find a bug in the FromDual Performance Monitor for 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 us.

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 1.2.0

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

Upgrade of fpmmm tarball from 1.0.x to 1.2.0

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-1.2.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.2.0 fpmmm
Changes in FromDual Performance Monitor for MySQL 1.2.0

This release contains new features and various bug fixes.

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

shell> fpmmm --version
General
  • MySQL 8.0 problems fixed. Fpmmm supports MySQL 8.0 now!
  • Naming convention for Type changed from host to machine and mysqld to instance, including downwards compatibility.
  • New host screens added.
  • All Screens removed because they are customer specific, we have host screens now.
  • Zabbix templates adapted to the more flexible trigger URL.
  • Renamed all files to make it more agnostic.

Server
  • Code made more robust for cloud databases.
  • Free file descriptors removed because it is always zero, trigger added for 80% file descriptors used.
  • Cache file base bug in getIostat fixed.
  • Server graph for file descriptors improved
  • I/O queue ymin set to 0.
  • Server template optimized.
  • Iostat graphs added to server template.
  • Integrated iostat data into fpmmm.
  • All registered devices and bug on svctm fixed.
  • More info added when server module is called with --debug option.
  • Disk status items cleaned-up and filesystem names added for creating new items.
  • Interface eth1 removed but list of all interfaces added.
  • NUMA trigger added.
  • Macros for network interfaces added.

Data
  • Data module added to measure schema and instance size.
  • Code made ready for cloud databases.

User
  • Module for per user data added.
  • Dirty code fixed, found on cloud databases.
  • User info for transactions added.
  • Tmp disk tables and sort merge passes per user information added.

Agent
  • MySQL 8.0 compatibility issue fixed with user privileges.
  • Output format zabbix, icinga, nagios and centreon should be supported now.
  • Error messages for connect improved.
  • Option --debug added, one message was not handled correctly in verbosity level.
  • Parameters in function goThroughAllSections cleaned-up.
  • Option -h added, info more clear when wrong options were used.
  • URLs added to fpmmm template.
  • fpmmm check and trigger improved.
  • Made error handling better after test of 1.1.0 on CentOS 7.
  • fpmmm trigger error message improved.

InnoDB
  • NUMA information and warning trigger added to InnoDB module.
  • Trigger for innodb_force_recovery made repeatable.
  • Alert level for innodb_force_recovery increased, InnoDB non default page size alert added.
  • InnoDB deadlock detection is alarmed, when disabled.
  • innodb_metrics only works with SUPER privilege, fixed.
  • InnoDB Log Buffer much to small trigger added on Innodb_log_waits item.
  • Items and graphs for InnoDB temporary tables added.

MySQL
  • Metadata Lock (MDL) error message improved.
  • Metadata Lock (MDL) naming improved.
  • Metadata Lock (MDL) counters, checks, graphs, triggers and Metadata Lock itself added.
  • Connection graphs yellow made a bit darker.

MyISAM
  • Items and graphs for MyISAM temporary tables added.

Aria
  • Items and graphs for Aria temporary tables added.

Security
  • Expired user added for MySQL including alert.

Slave
  • URL added and two triggers made repeatable.

Backup
  • Backup will report EVERY failure and URL is now useful!

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

FromDual Performance Monitor for MySQL 1.2.0 has been released

Shinguz - Fri, 2020-06-12 16:42

FromDual has the pleasure to announce the release of the new version 1.2.0 of its popular Database Performance Monitor for MySQL fpmmm.

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

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

Download

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

In the inconceivable case that you find a bug in the FromDual Performance Monitor for 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 us.

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 1.2.0

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

Upgrade of fpmmm tarball from 1.0.x to 1.2.0

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-1.2.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.2.0 fpmmm
Changes in FromDual Performance Monitor for MySQL 1.2.0

This release contains new features and various bug fixes.

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

shell> fpmmm --version
General
  • MySQL 8.0 problems fixed. Fpmmm supports MySQL 8.0 now!
  • Naming convention for Type changed from host to machine and mysqld to instance, including downwards compatibility.
  • New host screens added.
  • All Screens removed because they are customer specific, we have host screens now.
  • Zabbix templates adapted to the more flexible trigger URL.
  • Renamed all files to make it more agnostic.

Server
  • Code made more robust for cloud databases.
  • Free file descriptors removed because it is always zero, trigger added for 80% file descriptors used.
  • Cache file base bug in getIostat fixed.
  • Server graph for file descriptors improved
  • I/O queue ymin set to 0.
  • Server template optimized.
  • Iostat graphs added to server template.
  • Integrated iostat data into fpmmm.
  • All registered devices and bug on svctm fixed.
  • More info added when server module is called with --debug option.
  • Disk status items cleaned-up and filesystem names added for creating new items.
  • Interface eth1 removed but list of all interfaces added.
  • NUMA trigger added.
  • Macros for network interfaces added.

Data
  • Data module added to measure schema and instance size.
  • Code made ready for cloud databases.

User
  • Module for per user data added.
  • Dirty code fixed, found on cloud databases.
  • User info for transactions added.
  • Tmp disk tables and sort merge passes per user information added.

Agent
  • MySQL 8.0 compatibility issue fixed with user privileges.
  • Output format zabbix, icinga, nagios and centreon should be supported now.
  • Error messages for connect improved.
  • Option --debug added, one message was not handled correctly in verbosity level.
  • Parameters in function goThroughAllSections cleaned-up.
  • Option -h added, info more clear when wrong options were used.
  • URLs added to fpmmm template.
  • fpmmm check and trigger improved.
  • Made error handling better after test of 1.1.0 on CentOS 7.
  • fpmmm trigger error message improved.

InnoDB
  • NUMA information and warning trigger added to InnoDB module.
  • Trigger for innodb_force_recovery made repeatable.
  • Alert level for innodb_force_recovery increased, InnoDB non default page size alert added.
  • InnoDB deadlock detection is alarmed, when disabled.
  • innodb_metrics only works with SUPER privilege, fixed.
  • InnoDB Log Buffer much to small trigger added on Innodb_log_waits item.
  • Items and graphs for InnoDB temporary tables added.

MySQL
  • Metadata Lock (MDL) error message improved.
  • Metadata Lock (MDL) naming improved.
  • Metadata Lock (MDL) counters, checks, graphs, triggers and Metadata Lock itself added.
  • Connection graphs yellow made a bit darker.

MyISAM
  • Items and graphs for MyISAM temporary tables added.

Aria
  • Items and graphs for Aria temporary tables added.

Security
  • Expired user added for MySQL including alert.

Slave
  • URL added and two triggers made repeatable.

Backup
  • Backup will report EVERY failure and URL is now useful!

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

Stupid Error Messages

Shinguz - Fri, 2020-05-22 11:02

Very often I see some stupid error messages as a (power-)user. I do not know if this is because of lazy developers or managers not having enough focus on more useful error messages.

If the error messages would be more clear it would help me as a power-user to fix my problems faster and fix it possibly myself instead of asking questions or even open support cases.

That would also safe costs on the support service side if end-users would be enabled to fix their problems themself. If this is what software vendors really want...

Sometimes strace helps to understand the problem. But why do I need external tools to do the job?

Some examples

Bad: Could not add A-record.

Better: Could not add A-record lamp-database.org because it already exists.

Bad: Error 2.

OK I can help myself with:

perror 2 OS error code 2: No such file or directory

but still bad.

Better: No such file or directory. File I was looking for: /tmp/doesnotexist.txt

Unique Error Code Policy

FromDual has introduced a unique error code policy: Whenever you get an error code of any FromDual product we can tell you within seconds in which product at what place in the code (function) you hit the error (at least in theory and in 98% in practice).

grep -r '3170' * focmm/lib/Node.inc: $rc = 3170;

Our system is not perfect and could be done better. But this concept has evolved over time and was not there from the beginning. Possibly today we would make it a bit more sophisticated. For example: Error Codes can be reused (in the same product) if not needed any more. This causes the problem that we also need to know the version of the product to match the error number exactly to the code. Or in some projects we are running soon out of error numbers because we have chosen a too small range. 1000 error numbers is NOT enough for medium size products.

I think Microsoft has a similar concept?

Some developers tend to just rise stack traces. Java developers are specialists in this. This is completely useless for the (power-)user because most of them do not know what to do with a stack trace and they do not really see the real problem because of all the stack trace noise in the error log file. So the real information is overlooked. And information filtering is not everybody's strength...

Possibly I will add here some more examples in the future...

More examples

VPN GUI: Your computer's security is unsatisfactory. You computer does not meet the following security requirements. Please follow the instructions below to fix these problems. When you are done click Try Again. 1. External User, Reasons: Required process not found. Required file not found.

This example is from an expensive software of a big Enterprise Security Company. And the install instruction was also bad. When it would tell us at least which process and/or which file was not found it would already be a little help.

Taxonomy upgrade extras: errordevelopersoftware

Stupid Error Messages

Shinguz - Fri, 2020-05-22 11:02

Very often I see some stupid error messages as a (power-)user. I do not know if this is because of lazy developers or managers not having enough focus on more useful error messages.

If the error messages would be more clear it would help me as a power-user to fix my problems faster and fix it possibly myself instead of asking questions or even open support cases.

That would also safe costs on the support service side if end-users would be enabled to fix their problems themself. If this is what software vendors really want...

Sometimes strace helps to understand the problem. But why do I need external tools to do the job?

Some examples

Bad: Could not add A-record.

Better: Could not add A-record lamp-database.org because it already exists.

Bad: Error 2.

OK I can help myself with:

perror 2 OS error code 2: No such file or directory

but still bad.

Better: No such file or directory. File I was looking for: /tmp/doesnotexist.txt

Unique Error Code Policy

FromDual has introduced a unique error code policy: Whenever you get an error code of any FromDual product we can tell you within seconds in which product at what place in the code (function) you hit the error (at least in theory and in 98% in practice).

grep -r '3170' * focmm/lib/Node.inc: $rc = 3170;

Our system is not perfect and could be done better. But this concept has evolved over time and was not there from the beginning. Possibly today we would make it a bit more sophisticated. For example: Error Codes can be reused (in the same product) if not needed any more. This causes the problem that we also need to know the version of the product to match the error number exactly to the code. Or in some projects we are running soon out of error numbers because we have chosen a too small range. 1000 error numbers is NOT enough for medium size products.

I think Microsoft has a similar concept?

Some developers tend to just rise stack traces. Java developers are specialists in this. This is completely useless for the (power-)user because most of them do not know what to do with a stack trace and they do not really see the real problem because of all the stack trace noise in the error log file. So the real information is overlooked. And information filtering is not everybody's strength...

Possibly I will add here some more examples in the future...

Taxonomy upgrade extras: errordevelopersoftware

Stupid Error Messages

Shinguz - Fri, 2020-05-22 11:02

Very often I see some stupid error messages as a (power-)user. I do not know if this is because of lazy developers or managers not having enough focus on more useful error messages.

If the error messages would be more clear it would help me as a power-user to fix my problems faster and fix it possibly myself instead of asking questions or even open support cases.

That would also safe costs on the support service side if end-users would be enabled to fix their problems themself. If this is what software vendors really want...

Sometimes strace helps to understand the problem. But why do I need external tools to do the job?

Some examples

Bad: Could not add A-record.

Better: Could not add A-record lamp-database.org because it already exists.

Bad: Error 2.

OK I can help myself with:

perror 2 OS error code 2: No such file or directory

but still bad.

Better: No such file or directory. File I was looking for: /tmp/doesnotexist.txt

Unique Error Code Policy

FromDual has introduced a unique error code policy: Whenever you get an error code of any FromDual product we can tell you within seconds in which product at what place in the code (function) you hit the error (at least in theory and in 98% in practice).

grep -r '3170' * focmm/lib/Node.inc: $rc = 3170;

Our system is not perfect and could be done better. But this concept has evolved over time and was not there from the beginning. Possibly today we would make it a bit more sophisticated. For example: Error Codes can be reused (in the same product) if not needed any more. This causes the problem that we also need to know the version of the product to match the error number exactly to the code. Or in some projects we are running soon out of error numbers because we have chosen a too small range. 1000 error numbers is NOT enough for medium size products.

I think Microsoft has a similar concept?

Some developers tend to just rise stack traces. Java developers are specialists in this. This is completely useless for the (power-)user because most of them do not know what to do with a stack trace and they do not really see the real problem because of all the stack trace noise in the error log file. So the real information is overlooked. And information filtering is not everybody's strength...

Possibly I will add here some more examples in the future...

Taxonomy upgrade extras: errordevelopersoftware

FromDual Ops Center for MySQL and compatible databases 1.0.0 has been released

Shinguz - Mon, 2020-05-11 15:58

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

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

The main task of Ops Center is to support you in your daily MySQL and compatible databases operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MySQL and compatible databases (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 MySQL and compatible databases 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 1.0.0

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 1.0.0

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

Changes in Ops Center 1.0.0 Machine
  • Expression Server replaced by Machine.
  • Better error handling in getOperatingSystem function.
  • Machine does not belong to a Cluster any more: Column cluster_id from table machine removed.
  • Machine rename prepared.
  • 2 bugs on refresh and edit Machine fixed.
  • Instances added to Machine overview.

Instance
  • Expression Node replaced by Instance.
  • Instance added to Machine and Cluster overview.
  • Database Account management implemented.
  • Bug in create instance (server_id) fixed.
  • Bugs fixed in Instance backup, Instance restore, Instance performance and create Account.
  • Variable skip_name_resolve added to create Instance. This makes error probability smaller for accounts with same username but different host.
  • Menu title Configuration renamed to Settings.
  • Configuration file management implemented.
  • Deleting Instance belonging to a Cluster is not possible any more. Cluster must first release instance before instance can be deleted.
  • Column instance_type removed.
  • Menu Title Variables renamed to Configuration.
  • Show Processlist refurbished, filter fields smaller and daemon and system users filtered out.
  • Schema management implemented.
  • MySQL compatible database version 10.4 defaults for Variables added.
  • Missing backup-target is only ERR and not EMERG any more.
  • Instance performance advises added for sar.
  • Function copyFileFromRemoteServer should preserve timestamp now.
  • Connection changed from connect to real_connect to allow SSL connections.
  • SSL connection from Ops Center to Target Database and Repository Database is possible now.

Cluster
  • CHANGE MASTER TO and Cluster change fixed for systems with more than one Cluster.
  • CHANGE MASTER TO password is hidden now as well, all password can be viewed and copied.
  • Cluster bugs fixed.
  • Cluster functions cleaned-up.
  • Galera safe to bootstrap improved.
  • Machine removed from Cluster dashboard.
  • Instance added to Cluster overview.

Load-Balancer
  • Better error handling for Load-Balancer.
  • MaxScale Load-Balancer added in overview.
  • ProxySQL Load-Balancer basics implemented.

Virtual IP (VIP)/Floating IP
  • Put Edit VIP buttons in right order.

Tools
  • Tool for File Transfer added.
  • Tool config-diff compare button added also to top of table.

Configuration
  • No changes.
Database-as-a-Service (DBaaS)
  • Customer group replaced by Resource group.
  • FK added to Resource costs.
  • User Group added.
  • Logout User put into own function.
  • Tabindex for User edit/add added.
  • User cannot delete itself any more.
  • User comment and User group responsibility added.
  • User is logged out from focmm when deleted.

Building and Packaging
  • No changes.

Themes / UI
  • Keybord usage and Tab Index implemented in some forms.
  • jquery upgraded from 3.4.1 to 3.5.0

General
  • All HTML code removed in code.
  • Constant clean-up.
  • Machine, Instance and Cluster overview are sorted now.
  • Menu Instance, Machine and Cluster are sorted now, getXxxx function extended to make this possible.
  • Order of object and function changed in url.
  • Spell check error fixed.
  • Title moved a bit more to the right.
  • All tests passed for MySQL compatible database 10.5 and MySQL 8.0. Both can be considered as supported now.
  • Repository version 124 added, create separated for next major step.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaasfocmm

FromDual Ops Center for MySQL and compatible databases 1.0.0 has been released

Shinguz - Mon, 2020-05-11 15:58

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

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

The main task of Ops Center is to support you in your daily MySQL and compatible databases operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MySQL and compatible databases (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 MySQL and compatible databases 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 1.0.0

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 1.0.0

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

Changes in Ops Center 1.0.0 Machine
  • Expression Server replaced by Machine.
  • Better error handling in getOperatingSystem function.
  • Machine does not belong to a Cluster any more: Column cluster_id from table machine removed.
  • Machine rename prepared.
  • 2 bugs on refresh and edit Machine fixed.
  • Instances added to Machine overview.

Instance
  • Expression Node replaced by Instance.
  • Instance added to Machine and Cluster overview.
  • Database Account management implemented.
  • Bug in create instance (server_id) fixed.
  • Bugs fixed in Instance backup, Instance restore, Instance performance and create Account.
  • Variable skip_name_resolve added to create Instance. This makes error probability smaller for accounts with same username but different host.
  • Menu title Configuration renamed to Settings.
  • Configuration file management implemented.
  • Deleting Instance belonging to a Cluster is not possible any more. Cluster must first release instance before instance can be deleted.
  • Column instance_type removed.
  • Menu Title Variables renamed to Configuration.
  • Show Processlist refurbished, filter fields smaller and daemon and system users filtered out.
  • Schema management implemented.
  • MySQL compatible database version 10.4 defaults for Variables added.
  • Missing backup-target is only ERR and not EMERG any more.
  • Instance performance advises added for sar.
  • Function copyFileFromRemoteServer should preserve timestamp now.
  • Connection changed from connect to real_connect to allow SSL connections.
  • SSL connection from Ops Center to Target Database and Repository Database is possible now.

Cluster
  • CHANGE MASTER TO and Cluster change fixed for systems with more than one Cluster.
  • CHANGE MASTER TO password is hidden now as well, all password can be viewed and copied.
  • Cluster bugs fixed.
  • Cluster functions cleaned-up.
  • Galera safe to bootstrap improved.
  • Machine removed from Cluster dashboard.
  • Instance added to Cluster overview.

Load-Balancer
  • Better error handling for Load-Balancer.
  • MaxScale Load-Balancer added in overview.
  • ProxySQL Load-Balancer basics implemented.

Virtual IP (VIP)/Floating IP
  • Put Edit VIP buttons in right order.

Tools
  • Tool for File Transfer added.
  • Tool config-diff compare button added also to top of table.

Configuration
  • No changes.
Database-as-a-Service (DBaaS)
  • Customer group replaced by Resource group.
  • FK added to Resource costs.
  • User Group added.
  • Logout User put into own function.
  • Tabindex for User edit/add added.
  • User cannot delete itself any more.
  • User comment and User group responsibility added.
  • User is logged out from focmm when deleted.

Building and Packaging
  • No changes.

Themes / UI
  • Keybord usage and Tab Index implemented in some forms.
  • jquery upgraded from 3.4.1 to 3.5.0

General
  • All HTML code removed in code.
  • Constant clean-up.
  • Machine, Instance and Cluster overview are sorted now.
  • Menu Instance, Machine and Cluster are sorted now, getXxxx function extended to make this possible.
  • Order of object and function changed in url.
  • Spell check error fixed.
  • Title moved a bit more to the right.
  • All tests passed for MySQL compatible database 10.5 and MySQL 8.0. Both can be considered as supported now.
  • Repository version 124 added, create separated for next major step.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaas

FromDual Ops Center for MySQL and compatible databases 1.0.0 has been released

Shinguz - Mon, 2020-05-11 15:58

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

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

The main task of Ops Center is to support you in your daily MySQL and compatible databases operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MySQL and compatible databases (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 MySQL and compatible databases 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 1.0.0

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 1.0.0

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

Changes in Ops Center 1.0.0 Machine
  • Expression Server replaced by Machine.
  • Better error handling in getOperatingSystem function.
  • Machine does not belong to a Cluster any more: Column cluster_id from table machine removed.
  • Machine rename prepared.
  • 2 bugs on refresh and edit Machine fixed.
  • Instances added to Machine overview.

Instance
  • Expression Node replaced by Instance.
  • Instance added to Machine and Cluster overview.
  • Database Account management implemented.
  • Bug in create instance (server_id) fixed.
  • Bugs fixed in Instance backup, Instance restore, Instance performance and create Account.
  • Variable skip_name_resolve added to create Instance. This makes error probability smaller for accounts with same username but different host.
  • Menu title Configuration renamed to Settings.
  • Configuration file management implemented.
  • Deleting Instance belonging to a Cluster is not possible any more. Cluster must first release instance before instance can be deleted.
  • Column instance_type removed.
  • Menu Title Variables renamed to Configuration.
  • Show Processlist refurbished, filter fields smaller and daemon and system users filtered out.
  • Schema management implemented.
  • MySQL compatible database version 10.4 defaults for Variables added.
  • Missing backup-target is only ERR and not EMERG any more.
  • Instance performance advises added for sar.
  • Function copyFileFromRemoteServer should preserve timestamp now.
  • Connection changed from connect to real_connect to allow SSL connections.
  • SSL connection from Ops Center to Target Database and Repository Database is possible now.

Cluster
  • CHANGE MASTER TO and Cluster change fixed for systems with more than one Cluster.
  • CHANGE MASTER TO password is hidden now as well, all password can be viewed and copied.
  • Cluster bugs fixed.
  • Cluster functions cleaned-up.
  • Galera safe to bootstrap improved.
  • Machine removed from Cluster dashboard.
  • Instance added to Cluster overview.

Load-Balancer
  • Better error handling for Load-Balancer.
  • MaxScale Load-Balancer added in overview.
  • ProxySQL Load-Balancer basics implemented.

Virtual IP (VIP)/Floating IP
  • Put Edit VIP buttons in right order.

Tools
  • Tool for File Transfer added.
  • Tool config-diff compare button added also to top of table.

Configuration
  • No changes.
Database-as-a-Service (DBaaS)
  • Customer group replaced by Resource group.
  • FK added to Resource costs.
  • User Group added.
  • Logout User put into own function.
  • Tabindex for User edit/add added.
  • User cannot delete itself any more.
  • User comment and User group responsibility added.
  • User is logged out from focmm when deleted.

Building and Packaging
  • No changes.

Themes / UI
  • Keybord usage and Tab Index implemented in some forms.
  • jquery upgraded from 3.4.1 to 3.5.0

General
  • All HTML code removed in code.
  • Constant clean-up.
  • Machine, Instance and Cluster overview are sorted now.
  • Menu Instance, Machine and Cluster are sorted now, getXxxx function extended to make this possible.
  • Order of object and function changed in url.
  • Spell check error fixed.
  • Title moved a bit more to the right.
  • All tests passed for MySQL compatible database 10.5 and MySQL 8.0. Both can be considered as supported now.
  • Repository version 124 added, create separated for next major step.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaas

Shutdown with MySQL 8

Shinguz - Wed, 2020-04-01 16:52

On StackExchange for Database Administrators I recently have seen a question which attracted my interest.

The question puzzled me a bit because the answer seems too easy. Further the question was not so clear. An all theses factors smell dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MySQL Error Log and there you will find a log sequence similar to the following:

2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully 2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads 2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients 2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115 user: 'enswitch' 2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events 2020-03-30T08:04:51.924644Z 0 [Note] Binlog end 2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram' ... 2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog' 2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took to shutdown MySQL. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour back you can stop MySQL 8 as follows:

SQL> SET GLOBAL log_error_verbosity = 3; SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19). 2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully 2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads ... 2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

SQL> SHOW GLOBAL VARIABLES LIKE 'log_error'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | log_error | /home/mysql/database/mysql-80/log/error.log | +---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/<hostname>.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or /var/log/messages or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out when it started. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

shell> stat error.log File: error.log Size: 29929 Blocks: 64 IO Block: 4096 regular file Device: 801h/2049d Inode: 5373953 Links: 1 Access: (0640/-rw-r-----) Uid: ( 1001/ mysql) Gid: ( 1001/ mysql) Access: 2020-03-30 10:13:59.491446560 +0200 Modify: 2020-03-30 10:13:56.587467485 +0200 Change: 2020-03-30 10:13:56.587467485 +0200 Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

  • How many memory blocks of your database are swapped out.
  • How many pages are dirty and must be written to disk.
  • How fast is your I/O system (IOPS).

You can gather these information as follows:

  • How much Swap must be swapped in you can find here.
  • The number of dirty pages (pages modified but not written to disk yet) you can find with: SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
  • And about the IOPS you have to ask your hardware spec sheet (150 to 100'000 IOPS).

I hope with this answer I have covered all the possibly questions about shutting down MySQL 8.0?

Taxonomy upgrade extras: mysqlshutdownslow

Shutdown with MySQL 8

Shinguz - Wed, 2020-04-01 16:52

On StackExchange for Database Administrators I recently have seen a question which attracted my interest.

The question puzzled me a bit because the answer seems too easy. Further the question was not so clear. An all theses factors smell dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MySQL Error Log and there you will find a log sequence similar to the following:

2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully 2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads 2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients 2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115 user: 'enswitch' 2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events 2020-03-30T08:04:51.924644Z 0 [Note] Binlog end 2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram' ... 2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog' 2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took to shutdown MySQL. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour back you can stop MySQL 8 as follows:

SQL> SET GLOBAL log_error_verbosity = 3; SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19). 2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully 2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads ... 2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

SQL> SHOW GLOBAL VARIABLES LIKE 'log_error'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | log_error | /home/mysql/database/mysql-80/log/error.log | +---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/<hostname>.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or /var/log/messages or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out when it started. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

shell> stat error.log File: error.log Size: 29929 Blocks: 64 IO Block: 4096 regular file Device: 801h/2049d Inode: 5373953 Links: 1 Access: (0640/-rw-r-----) Uid: ( 1001/ mysql) Gid: ( 1001/ mysql) Access: 2020-03-30 10:13:59.491446560 +0200 Modify: 2020-03-30 10:13:56.587467485 +0200 Change: 2020-03-30 10:13:56.587467485 +0200 Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

  • How many memory blocks of your database are swapped out.
  • How many pages are dirty and must be written to disk.
  • How fast is your I/O system (IOPS).

You can gather these information as follows:

  • How much Swap must be swapped in you can find here.
  • The number of dirty pages (pages modified but not written to disk yet) you can find with: SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
  • And about the IOPS you have to ask your hardware spec sheet (150 to 100'000 IOPS).

I hope with this answer I have covered all the possibly questions about shutting down MySQL 8.0?

Taxonomy upgrade extras: mysqlshutdownslow

Shutdown with MySQL 8

Shinguz - Wed, 2020-04-01 16:52

On StackExchange for Database Administrators I recently have seen a question which attracted my interest.

The question puzzled me a bit because the answer seems too easy. Further the question was not so clear. An all theses factors smell dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MySQL Error Log and there you will find a log sequence similar to the following:

2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully 2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads 2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients 2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115 user: 'enswitch' 2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events 2020-03-30T08:04:51.924644Z 0 [Note] Binlog end 2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram' ... 2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog' 2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took to shutdown MySQL. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour back you can stop MySQL 8 as follows:

SQL> SET GLOBAL log_error_verbosity = 3; SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19). 2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully 2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads ... 2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

SQL> SHOW GLOBAL VARIABLES LIKE 'log_error'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | log_error | /home/mysql/database/mysql-80/log/error.log | +---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/<hostname>.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or /var/log/messages or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out when it started. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

shell> stat error.log File: error.log Size: 29929 Blocks: 64 IO Block: 4096 regular file Device: 801h/2049d Inode: 5373953 Links: 1 Access: (0640/-rw-r-----) Uid: ( 1001/ mysql) Gid: ( 1001/ mysql) Access: 2020-03-30 10:13:59.491446560 +0200 Modify: 2020-03-30 10:13:56.587467485 +0200 Change: 2020-03-30 10:13:56.587467485 +0200 Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

  • How many memory blocks of your database are swapped out.
  • How many pages are dirty and must be written to disk.
  • How fast is your I/O system (IOPS).

You can gather these information as follows:

  • How much Swap must be swapped in you can find here.
  • The number of dirty pages (pages modified but not written to disk yet) you can find with: SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
  • And about the IOPS you have to ask your hardware spec sheet (150 to 100'000 IOPS).

I hope with this answer I have covered all the possibly questions about shutting down MySQL 8.0?

Taxonomy upgrade extras: mysqlshutdownslow

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

Pages

Subscribe to FromDual Aggregator – FromDual TechFeed (en)