Feed aggregator

For me it was for a long time not clear if a mariabackup full backup can be used to do partial table or schema restores. Now we faced this challenge with a customer. So time to try it out...

This test was made with MariaDB 10.5.5. So it may not work with some older MariaDB releases...

Backup

Because I do not know during the backup if I need a full or a partial restore I always want to do a full mariabackup backup!

The full backup can be done as normal but the prepare should not be done yet during the backup (or I am not sure if the prepare can be done twice, first without --export during the backup and next with --export during the restore). Further I am not sure yet if a backup treated with --export can later be used for a full restore once more. Further research has to be done in this area...

For a partial table or schema restore we need the CREATE TABLE statements as well. So it makes sense to also backup the table structures already during backups. This avoids troubles or cumbersome and time consuming extracting operations during restore.

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/qamariadb105/data" # # Clean-up # rm -rf ${BACKUPDIR}/* # mariabackup --backup --user=root \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR} # # Backup also table structure for partial table/schema restore # mysqldump --user=root --no-data --all-databases > ${BACKUPDIR}/full_structure_dump.sql
Prepare and Restore one Schema # BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/mariadb-105/data" # SCHEMA="world" # mariabackup --prepare --export \ --databases="${SCHEMA}" \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR}

Additionally you can use the --tables option to only restore some tables: --tables='bla*bla'. The --export option creates the *.cfg files but further does not touch the *.ibd or *.frm files but ibdata?, ib_logfile? and aria_log* files!!! So I guess a backup treated like this cannot be used for a full restore any more... As mentioned above further research has to be done in this area.

-rw-rw---- 1 mysql mysql 551 Nov 11 20:41 world/CountryLanguage.cfg -rw-rw---- 1 mysql mysql 1215 Nov 11 20:41 world/Country.cfg -rw-rw---- 1 mysql mysql 578 Nov 11 20:41 world/City.cfg

From the structure dump we have to extract the CREATE DATABASE and the CREATE TABLE statements.

SQL> DROP SCHEMA world; SQL> CREATE SCHEMA world; SQL> CREATE TABLE ...;

Or more easy:

# mysql --user=root < ${BACKUPDIR}/${SCHEMA}_structure_dump.sql

Then we have to discard all the tablespaces we want to restore:

SQL> ALTER TABLE `world`.`City` DISCARD TABLESPACE; SQL> ALTER TABLE `world`.`Country` DISCARD TABLESPACE; SQL> ALTER TABLE `world`.`CountryLanguage` DISCARD TABLESPACE;

Restore all the files from the backup:

# cp ${BACKUPDIR}/${SCHEMA}/*.ibd ${DATADIR}/${SCHEMA}/ # cp ${BACKUPDIR}/${SCHEMA}/*.cfg ${DATADIR}/${SCHEMA}/

And then re-import the tablespaces:

SQL> ALTER TABLE `world`.`City` IMPORT TABLESPACE; SQL> ALTER TABLE `world`.`Country` IMPORT TABLESPACE; SQL> ALTER TABLE `world`.`CountryLanguage` IMPORT TABLESPACE;

That is it! We have restored one single schema with a physical MariaDB backup...

Possibly the *.cfg files can be cleaned-up yet:

# rm -f ${DATADIR}/${SCHEMA}/*.cfg
Literature

• Full Backup and Restore with mariabackup
• Partial Backup and Restore with mariabackup
• Mariabackup --export Option
• InnoDB File-Per-Table Tablespaces

Taxonomy upgrade extras: BackupRestoreschemadatabasephysical backupmariabackuptable restoreschema restorepartial restoredatabase restore

FromDual has the pleasure to announce the release of the new version 2.2.2 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.2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.2.tar.gz shell> rm -f brman shell> ln -s brman-2.2.2 brman
Changes in FromDual Backup and Recovery Manager 2.2.2

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

• Implode command made compatible to new PHP versions.
• Function readMyCnf restructured to not write to STDOUT any more.
• Max connection reached problem solved.
• myEnv library and constants synced/updated.

FromDual Backup Manager

• Missing return code added.
• RELOAD privilege is checked in doSchemaBackup and BinlogBackup.
• Reset return code to not spoil final return code in doSchemaBackup.
• WARN reduced severity level to INFO to not spoil STDERR in doSchemaBackup.
• Error caught in case when binary logs were deleted but not available in doCleanup.
• doBinlogBackup separated into own file.
• Do not print wrong recommendation in case of too many connections for target.
• Bman error message improved.
• Code clean-up in checkForGeneralTablespaces function.
• Bman falsely warns on MySQL 8.0 for general tablespace (new InnoDB system tablespace). This warning is suppressed now.
• Schema overview output was ugly with long schema names. Now extended to 24 characters.
• Bug caught better for clean-up backups in case the backup was already deleted by somebody else.
• Error handling improved for binlog backup on remote machine, code cleaned-up.
• Separated schema backup into own file.
• Fixed error messages and added remote binary log backup tags for future feature request.
• Function checkForGeneralTablespaces cleaned-up.
• Lock directory moved to /var/lock and alternative location for some Cloud set-ups.
• Physical backup for MariaDB 10.5 fixed.

FromDual Recovery Manager

• Password is not shown any more in the log for restore.
• Physical restore for MySQL 5.7 with Xtrabackup should work now.
• Exception for sync_frm for MySQL 8.0 added.

FromDual brman Catalog

• Bugs around catalog entries fixed.

FromDual brman Data masking / data obfuscating

• Data masking / data obfuscating POC work has been started to work exactly the same way as mariadb-dump / mysqldump.

Testing

• PHP Lint test added.
• Tests improved.
• New MariaDB 10.5 version added and test for stopped instance added.
• Tests for MySQL 8.0 fixed.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

When it comes to SQL Query tuning with customers we often get the slow running SQL query and possibly, in good cases, also the table structure. But very often, for various reasons, we do not get the data.

SQL query tuning on an empty table or a table with only little data is not really fun because either the results of the optimizer have nothing to do with reality or the response times do not really show if your change has improved anything. For example if your query response time before the change was 2 ms and after 1 ms this can be either the consequence of your improvement but more probable a hiccup of your system.

So what to do to get valid results from your SQL query optimizer during SQL query tuning?

• The best case is you get real data from the customer in size and content.
• The second best case is if you get real data from the customer in content. So you can analyze this content and synthetically pump it up.
• The worst case is if you get no data at all from your customer. In this case you have to create your own data set in size (this is easy) and in content. And this is a bit more tricky.

So let us have a look at how we get to this synthetic data.

Creating data volume by pumping up the table

We get from the customer a slow query for a data cleansing job on the call detail record (CDR) table. This table is used in telecom solutions like VoIP (Asterix, OpenSIPS), PBX and so on.

SELECT COUNT(*) FROM cdrs WHERE start >= end;

And fortunately we also get the CDR table:

CREATE TABLE `cdrs` ( `uniqueid` varchar(40) NOT NULL, `callid` varchar(40) NOT NULL, `asteriskid` varchar(20) NOT NULL DEFAULT '', `machine` int(11) NOT NULL DEFAULT 0, `status` varchar(15) NOT NULL DEFAULT '', `start` TIMESTAMP(6) NOT NULL, `end` TIMESTAMP(6) NOT NULL, `scustomer` int(11) NOT NULL DEFAULT 0, `stype` varchar(30) NOT NULL DEFAULT '', `snumber` varchar(255) NOT NULL DEFAULT '', `dcustomer` int(11) NOT NULL DEFAULT 0, `dtype` varchar(30) NOT NULL DEFAULT '', `dnumber` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`uniqueid`), KEY `server` (`callid`), KEY `start` (`start`), KEY `scustomer` (`scustomer`,`start`), KEY `dcustomer` (`dcustomer`,`start`), KEY `asteriskid` (`asteriskid`) );

So how can we pump up this table to get a decent volume? For pumping up the table we use the concept of the Rice/Wheat and chessboard problem:

We first insert one row and then pump it up by adding rows from itself. This gives us an exponential growth and after a few statement we have enough data (possibly time becomes an issue sooner or later):

INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' ; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' FROM cdrs ; -- Repeat this second query about 20 times to get 1 Mio rows. Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.001 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.000 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 8 rows affected (0.000 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 16 rows affected (0.001 sec) Records: 16 Duplicates: 0 Warnings: 0 Query OK, 32 rows affected (0.002 sec) Records: 32 Duplicates: 0 Warnings: 0 Query OK, 64 rows affected (0.002 sec) Records: 64 Duplicates: 0 Warnings: 0 Query OK, 128 rows affected (0.094 sec) Records: 128 Duplicates: 0 Warnings: 0 Query OK, 256 rows affected (1.406 sec) Records: 256 Duplicates: 0 Warnings: 0 Query OK, 512 rows affected (2.747 sec) Records: 512 Duplicates: 0 Warnings: 0 Query OK, 1024 rows affected (4.888 sec) Records: 1024 Duplicates: 0 Warnings: 0

What happened here???

Query OK, 2048 rows affected (0.178 sec) Records: 2048 Duplicates: 0 Warnings: 0 Query OK, 4096 rows affected (0.259 sec) Records: 4096 Duplicates: 0 Warnings: 0 Query OK, 8192 rows affected (1.879 sec) Records: 8192 Duplicates: 0 Warnings: 0 Query OK, 16384 rows affected (4.149 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 32768 rows affected (3.256 sec) Records: 32768 Duplicates: 0 Warnings: 0 Query OK, 65536 rows affected (7.209 sec) Records: 65536 Duplicates: 0 Warnings: 0 Query OK, 131072 rows affected (13.555 sec) Records: 131072 Duplicates: 0 Warnings: 0

Buffer Pool seems to be full! More RAM helps more...

Query OK, 262144 rows affected (6 min 17.659 sec) Records: 262144 Duplicates: 0 Warnings: 0

Increased Buffer Pool (online!) 6 times and waited for dirty page flushing.

Query OK, 524288 rows affected (1 min 14.629 sec) Records: 524288 Duplicates: 0 Warnings: 0

It definitely helped! More RAM helps more!!!

How to get more or less useful or realistic data DescriptionFunctionExampleUnique ID:SELECT UUID();09e16608-017f-11eb-9cc7-a8a15920b138Random Float from 0 TO 99:SELECT RAND() * 100;82.15320322863124Random Integer from 10 to 19:SELECT 10 + FLOOR(RAND() * 10);10Random Float for currencies:SELECT ROUND(RAND() * 1000, 2);628.07Random String:SELECT UUID(), MD5(RAND()), CRC32(RAND());232fccee-017f-11eb-9cc7-a8a15920b138 0e468db120211529f5fc2940994024a8 263783538Random Timestamp ≥ 1970-01-01 00:00:00 UTC:SELECT FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6));1992-06-30 11:04:04.784335Random Timestamp ≥ 2020-01-01 and < 2020-12-31:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2020-01-01') + (365 * 24 * 3600 * RAND()));2020-08-06 04:48:53.342219Some kind of email address:SELECT CONCAT(CRC32(RAND()), '@', MD5(RAND()), '.com');1619088853@6b20a5dad4522feee5efbfd3ebb17d71.comTime range of 21 days from now:SELECT FROM_UNIXTIME(@begin := UNIX_TIMESTAMP()), FROM_UNIXTIME(@begin + (86400 * 21));2020-10-02 11:14:48 2020-10-23 11:14:48Street name:SELECT CONCAT(CRC32(RAND()), 'street ', (1 + FLOOR(RAND() * 100)));3416042219street 14

and there are for sure many more possibilities...

Now back to the query: With no rows the Query Execution Plan with EXPLAIN looks as follows:

SQL> EXPLAIN SELECT COUNT(*) FROM cdrs WHERE start >= end; +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | cdrs | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.004 sec)

If we pump up the table the query execution plan with EXPLAIN looks like this:

SQL> EXPLAIN SELECT COUNT(*) FROM cdrs WHERE start >= end; +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | cdrs | ALL | NULL | NULL | NULL | NULL | 1016314 | Using where | +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.230 sec)

So we have a good starting point for testing tuning measures on this SQL query...

But caution: There is still a logical error in the data above... Did you find it?

If and how this query can be speed up is an other story...

Skew data distribution

What we got so far is a completely random data distribution or at least random in a specific range. This is very often far from reality. So we have to influence or manipulate this random data distribution a bit more into the direction that it reflects our reality.

There are 2 extreme cases: We are only searching for one unique value or all values are equal. In reality we are somewhere in between.

If you where using a UUID or hash function to create the data they should be pretty unique. So this extreme case is covered:

UPDATE cdrs SET start = '2020-04-29 12:00:00.000000', end = '2020-04-29 12:13:13.999999' WHERE uniqueid = 'd00c7166-01a4-11eb-9cc7-a8a15920b138';

Or you can specifically UPDATE ONE row to your needs. This other extreme case also can be solved by a simple UPDATE statement:

UPDATE cdrs SET machine = 42;

If you want to set only every nth row to a specific value the modulo operation might help:

SET @nth = 7; UPDATE cdrs SET machine = 42 WHERE id % @nth; UPDATE cdrs SET machine = IF(HEX(SUBSTR(UNIQUEID, 8, 1)) % @nth, 42, machine);

And finaly if you need monotonic increasing numbers this is a possibility to do it:

SET @row_number = 0; UPDATE cdrs SET machine = (@row_number := @row_number + 1);
Taxonomy upgrade extras: performancetuningqueryOptimizersqlexplainoptimizingquery tuningPerformance Tuning

Yes, I know, MyISAM is deprecated and unofficially discontinued by the vendors. But we still have from time to time customers using MyISAM and even evangelize for MyISAM...

And to be honest in some cases MyISAM has even advantages (beside some huge disadvantages) over other Storage Engines (simple file copy, footprint, single-query latency, ...). But most of our customers are not aware of these advantages and are using MyISAM just because they did it since ever...

One of the biggest problems we see at customers is the MyISAM table lock behaviour. They claim things like the database stalls, crashes or stocks beside other non-qualified expressions. Which is typically not the case but the database just runs out of connections because they reach the max_connections fuse. In fact what happens is that one long running writer connection blocks an important and frequently used (MyISAM) table and other writer and reader connections have to wait (Waiting for table level lock) until the writer finishes its work. If you are lucky the system relaxes again afterwards. If not, the database rejects new connections because in the meanwhile other connections have filled up the allowed number of connections up to max_connections.

Instead of finding and solving the problem, customers typically just increase max_connections until it becomes so cumbersome they cannot live any more with it. And then they show up at FromDual consulting services.

Now, how can we find that this scenario happened? The first thing you should do when this situation happens is to gather immediately the output of the command SHOW FULL PROCESSLIST and store it away for later analysis by your preferred spreadsheet tool or editor (please not a screenshot but just copy plain characters from your CLI!).

If you are a bit more prepared for the situation you can also use:

SELECT thread_id, processlist_id, processlist_user, processlist_host, processlist_db, processlist_time, processlist_state, processlist_info FROM performance_schema.threads WHERE PROCESSLIST_COMMAND != 'Sleep' AND TYPE = 'FOREGROUND' ORDER BY PROCESSLIST_TIME DESC ;

An this is how it looks like if you have a MyISAM table level locking situation:

INSERT INTO ... SELECT * FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 12 | Sending data | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 11 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

or

+-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 21 | Writing to binlog | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 20 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
UPDATE ... SET ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 47 | Updating | UPDATE test SET data = 'Blub' WHERE id > 1000 | | 117892 | 117279 | root | localhost | test | 47 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
DELETE FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 7 | Updating | DELETE FROM test WHERE id > 10 | | 153272 | 152659 | root | localhost | test | 7 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
SELECT ... FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 2 | Sending data | SELECT * FROM test | | 160014 | 159401 | root | localhost | test | 2 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

This means for searching the locker you have to find all connections which are not in processlist command Sleep and which are not in processlist state Waiting for table level lock sort the remaining by processlist time and the one with the longest processlist time is probably the evildoer.

In some cases where you have a Master/Master set-up (or if you have the problem on the slave) data changes can also be induced by the (other) Master. In this situation it looks as follows:

+-------+-------------+-----------+------+-----------+------+-------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-------------+-----------+------+-----------+------+-------------------------------------+------------------------------------------------------------------------------------------------------+ | 15 | system user | | NULL | Slave_SQL | 0 | Update_rows_log_event::find_row(-1) | UPDATE test SET data = 'blabla' | | 16867 | root | localhost | test | Query | 28 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), | +-------+-------------+-----------+------+-----------+------+-------------------------------------+------------------------------------------------------------------------------------------------------+
The PERFORMANCE_SCHEMA An other possibility to find possible locker candidates is the PERFORMANCE_SCHEMA. First you have to make sure, the PERFORMANCE_SCHEMA is enabled and the according instrument and consumers are enabled: SQL> SHOW GLOBAL VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+
SQL> SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait/lock/ta%'; +-----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-----------------------------+---------+-------+ | wait/lock/table/sql/handler | YES | YES | +-----------------------------+---------+-------+
SQL> SELECT * FROM performance_schema.setup_consumers WHERE name LIKE 'events_waits%'; +---------------------------+---------+ | NAME | ENABLED | +---------------------------+---------+ | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | +---------------------------+---------+

and then you can check the according PERFORMANCE_SCHEMA views:

SQL> SELECT @uptime := variable_value FROM information_schema.global_status WHERE variable_name = 'Uptime'; SQL> SELECT @start := DATE_SUB(NOW(), INTERVAL @uptime SECOND) AS start;

General statistics:

SQL> SELECT * FROM performance_schema.table_lock_waits_summary_by_table; +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_READ_NORMAL | SUM_TIMER_READ_NORMAL | MIN_TIMER_READ_NORMAL | AVG_TIMER_READ_NORMAL | MAX_TIMER_READ_NORMAL | COUNT_READ_WITH_SHARED_LOCKS | SUM_TIMER_READ_WITH_SHARED_LOCKS | MIN_TIMER_READ_WITH_SHARED_LOCKS | AVG_TIMER_READ_WITH_SHARED_LOCKS | MAX_TIMER_READ_WITH_SHARED_LOCKS | COUNT_READ_HIGH_PRIORITY | SUM_TIMER_READ_HIGH_PRIORITY | MIN_TIMER_READ_HIGH_PRIORITY | AVG_TIMER_READ_HIGH_PRIORITY | MAX_TIMER_READ_HIGH_PRIORITY | COUNT_READ_NO_INSERT | SUM_TIMER_READ_NO_INSERT | MIN_TIMER_READ_NO_INSERT | AVG_TIMER_READ_NO_INSERT | MAX_TIMER_READ_NO_INSERT | COUNT_READ_EXTERNAL | SUM_TIMER_READ_EXTERNAL | MIN_TIMER_READ_EXTERNAL | AVG_TIMER_READ_EXTERNAL | MAX_TIMER_READ_EXTERNAL | COUNT_WRITE_ALLOW_WRITE | SUM_TIMER_WRITE_ALLOW_WRITE | MIN_TIMER_WRITE_ALLOW_WRITE | AVG_TIMER_WRITE_ALLOW_WRITE | MAX_TIMER_WRITE_ALLOW_WRITE | COUNT_WRITE_CONCURRENT_INSERT | SUM_TIMER_WRITE_CONCURRENT_INSERT | MIN_TIMER_WRITE_CONCURRENT_INSERT | AVG_TIMER_WRITE_CONCURRENT_INSERT | MAX_TIMER_WRITE_CONCURRENT_INSERT | COUNT_WRITE_DELAYED | SUM_TIMER_WRITE_DELAYED | MIN_TIMER_WRITE_DELAYED | AVG_TIMER_WRITE_DELAYED | MAX_TIMER_WRITE_DELAYED | COUNT_WRITE_LOW_PRIORITY | SUM_TIMER_WRITE_LOW_PRIORITY | MIN_TIMER_WRITE_LOW_PRIORITY | AVG_TIMER_WRITE_LOW_PRIORITY | MAX_TIMER_WRITE_LOW_PRIORITY | COUNT_WRITE_NORMAL | SUM_TIMER_WRITE_NORMAL | MIN_TIMER_WRITE_NORMAL | AVG_TIMER_WRITE_NORMAL | MAX_TIMER_WRITE_NORMAL | COUNT_WRITE_EXTERNAL | SUM_TIMER_WRITE_EXTERNAL | MIN_TIMER_WRITE_EXTERNAL | AVG_TIMER_WRITE_EXTERNAL | MAX_TIMER_WRITE_EXTERNAL | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | TABLE | test | test | 994184 | 410880205008924 | 126840 | 413283678 | 101480121005292 | 6 | 7095792 | 300792 | 1182330 | 2797728 | 994178 | 410880197913132 | 126840 | 413285943 | 101480121005292 | 3 | 2583912 | 300792 | 861153 | 1359906 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 4511880 | 608832 | 1503960 | 2797728 | 0 | 0 | 0 | 0 | 0 | 497080 | 410589058214256 | 126840 | 826001559 | 101480121005292 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 2890140 | 162174 | 320724 | 953112 | 497089 | 291136808736 | 287202 | 585276 | 69665964 | | TABLE | test | vol | 110 | 113655888 | 94224 | 1032840 | 3911202 | 64 | 30244998 | 94224 | 472479 | 3911202 | 46 | 83410890 | 318912 | 1812906 | 3246198 | 32 | 11410164 | 94224 | 356511 | 558096 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32 | 18834834 | 96036 | 588447 | 3911202 | 0 | 0 | 0 | 0 | 0 | 23 | 22661778 | 318912 | 985275 | 1338162 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 23 | 60749112 | 1040088 | 2640990 | 3246198 | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+

Locks currently in use (column EXTERNAL_LOCK?):

SQL> SELECT *, DATE_ADD(@start, INTERVAL OBJECT_INSTANCE_BEGIN/1000000000000 SECOND) AS OBJECT_INSTANCE_BEGIN FROM performance_schema.table_handles; +-------------+---------------+-----------------------+-----------------------+-----------------+----------------+-------------------------+----------------+----------------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK | OBJECT_INSTANCE_BEGIN | +-------------+---------------+-----------------------+-----------------------+-----------------+----------------+-------------------------+----------------+----------------------------+ | TABLE | test | test | 139730319589344 | 331762 | 188378355 | WRITE CONCURRENT INSERT | WRITE EXTERNAL | 2020-09-08 21:51:25.730300 | - INSERT | TABLE | test | vol | 139729782302336 | 331762 | 188378355 | READ | READ EXTERNAL | 2020-09-08 21:51:25.729800 | - SELECT | TABLE | test | vol | 139729782023584 | 0 | 0 | READ | NULL | 2020-09-08 21:51:25.729800 | | TABLE | test | test | 139729782284672 | 497001 | 2 | WRITE CONCURRENT INSERT | WRITE EXTERNAL | 2020-09-08 21:51:25.729800 | - INSERT +-------------+---------------+-----------------------+-----------------------+-----------------+----------------+-------------------------+----------------+----------------------------+

Threads currently locking:

SQL> SELECT thread_id, event_id, end_event_id, event_name , DATE_ADD(@start, INTERVAL timer_start/1000000000000 SECOND) AS timer_start , DATE_ADD(@start, INTERVAL timer_end/1000000000000 SECOND) AS timer_end , ROUND(timer_wait/1000000000000, 3) AS timer_wait_s , object_schema, REVERSE(SUBSTR(REVERSE(object_name), 1, 32)) AS object_name, object_type , DATE_ADD(@start, INTERVAL object_instance_begin/1000000000000 SECOND) AS object_instance_begin , nesting_event_id, nesting_event_type, operation FROM performance_schema.events_waits_current ; +-----------+-----------+--------------+-----------------------------+----------------------------+----------------------------+--------------+---------------+----------------------------------+-------------+----------------------------+------------------+--------------------+-------------------------+ | thread_id | event_id | end_event_id | event_name | timer_start | timer_end | timer_wait_s | object_schema | object_name | object_type | object_instance_begin | nesting_event_id | nesting_event_type | operation | +-----------+-----------+--------------+-----------------------------+----------------------------+----------------------------+--------------+---------------+----------------------------------+-------------+----------------------------+------------------+--------------------+-------------------------+ | 373764 | 3 | NULL | wait/lock/table/sql/handler | 2020-09-10 07:45:20.231500 | 2020-09-10 07:46:53.075100 | 92.844 | test | test | TABLE | 2020-09-08 21:51:25.730300 | NULL | NULL | write concurrent insert | | 331762 | 178087926 | NULL | wait/io/table/sql/handler | 2020-09-10 07:46:53.075100 | 2020-09-10 07:46:53.075100 | 0.000 | test | test | TABLE | 2020-09-08 21:51:25.729800 | NULL | NULL | delete | | 331762 | 178087931 | NULL | wait/io/file/myisam/dfile | 2020-09-10 07:46:53.075100 | 2020-09-10 07:46:53.075100 | 0.000 | NULL | w/mariadb-105/data/test/test.MYD | FILE | 2020-09-08 21:51:25.731100 | 178087926 | WAIT | read | +-----------+-----------+--------------+-----------------------------+----------------------------+----------------------------+--------------+---------------+----------------------------------+-------------+----------------------------+------------------+--------------------+-------------------------+

But I still have not found a direct view to prove who is the locker of a specific locked thread/connection. So some work is still to do...

Taxonomy upgrade extras: myisamlocklocking

Das diesjährige Programm des Vorarlberger LinuxDay 2020 wurde veröffentlicht!

Die Vorträge decken Themen querbeet aus dem Opensource Umfeld ab: Linux Mint, Smart Home, Bootloader, Regolith Linux, LibreOffice, MariaDB/MySQL, Forensik, PostgreSQL, Patch-Management, Softwareverteilung, Backup und Videokonferenzdienste.

Im Unterschied zu anderen Jahren findet dieses Jahr die Konferenz am 10. Oktober 2020 rein virtuell statt. Die Teilnahme lohnt sich!

Für MariaDB/MySQL Enthusiasten können wir den Vortrag MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus empfehlen.

Taxonomy upgrade extras: 2020konferenzlinux

Das diesjährige Programm des Vorarlberger LinuxDay 2020 wurde veröffentlicht!

Die Vorträge decken Themen querbeet aus dem Opensource Umfeld ab: Linux Mint, Smart Home, Bootloader, Regolith Linux, LibreOffice, MariaDB/MySQL, Forensik, PostgreSQL, Patch-Management, Softwareverteilung, Backup und Videokonferenzdienste.

Im Unterschied zu anderen Jahren findet dieses Jahr die Konferenz am 10. Oktober 2020 rein virtuell statt. Die Teilnahme lohnt sich!

Für MariaDB/MySQL Enthusiasten können wir den Vortrag MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus empfehlen.

Taxonomy upgrade extras: 2020konferenzlinux

Das diesjährige Programm der DOAG 2020 Konferenz + Ausstellung ist veröffentlicht!

Das Programm ist abwechslungsreich und interessant, wenn auch etwas kleiner als andere Jahre. Die Teilnahme lohnt sich!

Aus MariaDB/MySQL Sicht ist vor allem der Mittwoch, im Raum Kiew (55 Plätze), mit den üblichen Verdächtigen, interessant:

09:00 - 09:45 Sessionkeynote: The new MySQL Database and MySQL Analytics Service - Extreme Performance, Cloud Scale, Significant Cost Savings Nipun Agarwal 10:00 - 10:45 MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus Oliver Sennhauser 11:00 - 11:45 Wie ein Ei dem anderen... MySQL ReplicaSets Matthias Jung 12:00 - 12:45 MySQL 8: Ein Statusbericht 3 Jahre nach dem ersten Release Carsten Thalheimer

Die Konferenz findet dieses Jahr von Dienstag, dem 17. November 2020 bis Donnerstag, dem 19. November 2020 in Nürnberg und in Teilen auch als online Veranstaltung statt. Mehr dazu auf der Website der Konferenz.

Taxonomy upgrade extras: doag2020konferenz

Das diesjährige Programm der DOAG 2020 Konferenz + Ausstellung ist veröffentlicht!

Das Programm ist abwechslungsreich und interessant, wenn auch etwas kleiner als andere Jahre. Die Teilnahme lohnt sich!

Aus MariaDB/MySQL Sicht ist vor allem der Mittwoch, im Raum Kiew (55 Plätze), mit den üblichen Verdächtigen, interessant:

09:00 - 09:45 Sessionkeynote: The new MySQL Database and MySQL Analytics Service - Extreme Performance, Cloud Scale, Significant Cost Savings Nipun Agarwal 10:00 - 10:45 MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus Oliver Sennhauser 11:00 - 11:45 Wie ein Ei dem anderen... MySQL ReplicaSets Matthias Jung 12:00 - 12:45 MySQL 8: Ein Statusbericht 3 Jahre nach dem ersten Release Carsten Thalheimer

Die Konferenz findet dieses Jahr von Dienstag, dem 17. November 2020 bis Donnerstag, dem 19. November 2020 in Nürnberg und in Teilen auch als online Veranstaltung statt. Mehr dazu auf der Website der Konferenz.

Taxonomy upgrade extras: doag2020konferenz

Repositories from your favourite Linux distribution and from your favourite database software vendor get regular package updates. If you do periodic system upgrades (for example every 2 weeks as we do) you get the newest packages of a release series.
Unfortunately recently the software vendors started not only to fix bugs in new releases but also to introduce new features. And when you fix bugs or introduce new stuff new bugs might appear.
To avoid being hit unexpectedly by new bugs you do not want to upgrade untested software. To achieve this you want to keep/not upgrade some important packages. For example the MariaDB or MySQL server package.
This package pinning is called package version lock on CentOS and Redhat and package holding on Debian and Ubuntu.

How you do this I have summarized below:

Debian / Ubuntu

See also apt_preferences (APT pinning) and man 5 apt_preferences

shell> dpkg -l | grep mariadb ii libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C ii mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries ii mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries ii mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) ii mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries ii mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-mark hold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 galera-4 set on hold. libmariadb3 set on hold. mariadb-client-10.4 set on hold. mariadb-client-core-10.4 set on hold. mariadb-common set on hold. mariadb-server-10.4 set on hold. mariadb-server-core-10.4 set on hold. shell> apt-mark showhold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 shell> dpkg -l | grep -e mariadb -e galera hi galera-4 26.4.2-bionic amd64 Replication framework for transactional applications hi libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C hi mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries hi mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries hi mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) hi mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries hi mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-get upgrade Reading package lists... Done Building dependency tree Reading state information... Done Calculating upgrade... Done The following packages were automatically installed and are no longer required: ... Use 'apt autoremove' to remove them. The following packages have been kept back: galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 netplan.io The following packages will be upgraded: ... 67 to upgrade, 0 to newly install, 0 to remove and 11 not to upgrade. Need to get 107 MB of archives. After this operation, 3,876 kB of additional disk space will be used. Do you want to continue? [Y/n]

With the command apt-mark unhold you can release pinned packages again.

CentOS / Redhat

To restrict a package to a fixed version number with yum you have to first install the package yum-plugin-versionlock [ man yum-versionlock ]:

shell> yum install yum-plugin-versionlock

To add a version lock for the MariaDB packages:

shell> yum versionlock add MariaDB-server MariaDB-client MariaDB-common MariaDB-compat galera-4 Adding versionlock on: 0:MariaDB-server-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-client-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-common-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-compat-10.4.10-1.el7.centos Adding versionlock on: 0:galera-4-26.4.3-1.rhel7.el7.centos versionlock added: 5

To list any available updates that are currently blocked by version lock you can run:

shell> yum versionlock status 0:MariaDB-common-10.4.13-1.el7.centos.* 0:MariaDB-compat-10.4.13-1.el7.centos.* 0:MariaDB-client-10.4.13-1.el7.centos.* 0:MariaDB-server-10.4.13-1.el7.centos.* 0:galera-4-26.4.4-1.rhel7.el7.centos.* versionlock status done

And to list the current version lock entries:

shell> yum versionlock list 0:MariaDB-server-10.4.10-1.el7.centos.* 0:MariaDB-client-10.4.10-1.el7.centos.* 0:MariaDB-common-10.4.10-1.el7.centos.* 0:MariaDB-compat-10.4.10-1.el7.centos.* 0:galera-4-26.4.3-1.rhel7.el7.centos.* versionlock list done

With:

shell> yum versionlock delete MariaDB-server

you can remove any matching version lock entries again.

And to clear the whole list of version lock entries use:

yum versionlock clear

If you will do the next yum update you will not see the MariaDB packages in the list any more:

shell> yum update ... Dependencies Resolved ======================================================================================================================================================== Package Arch Version Repository Size ======================================================================================================================================================== Installing: kernel x86_64 3.10.0-1127.18.2.el7 updates 50 M Updating: bind-export-libs x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-libs-lite x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-license noarch 32:9.11.4-16.P2.el7_8.6 updates 90 k binutils x86_64 2.27-43.base.el7_8.1 updates 5.9 M ca-certificates noarch 2020.2.41-70.0.el7_8 updates 382 k dbus x86_64 1:1.10.24-14.el7_8 updates 245 k dbus-libs x86_64 1:1.10.24-14.el7_8 updates 169 k device-mapper x86_64 7:1.02.164-7.el7_8.2 updates 295 k device-mapper-libs x86_64 7:1.02.164-7.el7_8.2 updates 324 k grub2 x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-common noarch 1:2.02-0.86.el7.centos updates 729 k grub2-pc x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-pc-modules noarch 1:2.02-0.86.el7.centos updates 850 k grub2-tools x86_64 1:2.02-0.86.el7.centos updates 1.8 M grub2-tools-extra x86_64 1:2.02-0.86.el7.centos updates 1.0 M grub2-tools-minimal x86_64 1:2.02-0.86.el7.centos updates 174 k kernel-tools x86_64 3.10.0-1127.18.2.el7 updates 8.1 M kernel-tools-libs x86_64 3.10.0-1127.18.2.el7 updates 8.0 M libgudev1 x86_64 219-73.el7_8.8 updates 107 k microcode_ctl x86_64 2:2.1-61.10.el7_8 updates 2.7 M python-perf x86_64 3.10.0-1127.18.2.el7 updates 8.1 M rsyslog x86_64 8.24.0-52.el7_8.2 updates 621 k selinux-policy noarch 3.13.1-266.el7_8.1 updates 497 k selinux-policy-targeted noarch 3.13.1-266.el7_8.1 updates 7.0 M systemd x86_64 219-73.el7_8.8 updates 5.1 M systemd-libs x86_64 219-73.el7_8.8 updates 416 k systemd-sysv x86_64 219-73.el7_8.8 updates 94 k yum-plugin-fastestmirror noarch 1.1.31-54.el7_8 updates 34 k Removing: kernel x86_64 3.10.0-957.1.3.el7 @updates 63 M Transaction Summary ======================================================================================================================================================== Install 1 Package Upgrade 28 Packages Remove 1 Package Total download size: 105 M
Server restart required: reboot

After you did a full upgrade of all packages typically a server restart is required. Because this causes downtime and service loss we only want to do this if it is really necessary. To find out if a server restart is really required you can run the following commands:

Debian / Ubuntu shell> cat /var/run/reboot-required
CentOS / Redhat shell> yum install yum-utils shell> needs-restarting -r Core libraries or services have been updated: kernel -> 3.10.0-1127.18.2.el7 systemd -> 219-73.el7_8.8 dbus -> 1:1.10.24-14.el7_8 Reboot is required to ensure that your system benefits from these updates.

If only some services must be restarted you can see with:

shell> needs-restarting -s systemd-logind.service NetworkManager.service dbus.service avahi-daemon.service sshd.service polkit.service systemd-udevd.service tuned.service systemd-journald.service firewalld.service
Taxonomy upgrade extras: debianubuntucentosredhatpackageupgradelocklocking

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

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

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

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

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:

• File Limit für MySQL hoch setzen
• Could not increase number of max_open_files

Taxonomy upgrade extras: open_files_limitLimitNOFILEfile handles

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:

• File Limit für MySQL hoch setzen
• Could not increase number of max_open_files

Taxonomy upgrade extras: open_files_limitLimitNOFILEfile handles

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

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