Feed Aggregator

In a recent customer engagement we experienced a lot of corrupted (normal) indexes and also corrupted full-text indexes on InnoDB tables in a Galera Cluster (v10.4.13).

In the error log we did not see which table it was but we have only a few log entries every here and there indicating a full-table index is corrupt:

2020-07-08 22:09:03 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.
2020-07-08 22:09:06 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.
2020-07-08 22:09:09 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

So we tried to figure out which database files were touch in the indicated time:

shell> ls -ltr */FTS_*.ibd | tail -n8
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011d3_INDEX_4.ibd
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011d3_INDEX_5.ibd
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 …

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 …

With this new version of fpmmm you probably get a new warning in your fpmmm error log:

INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (1.2.0) run started.
WARN:     P_S Metadata Lock instrumentation is disabled. (rc=1411)
INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) run finished (rc=0).

In this fpmmm release we start monitoring MariaDB/MySQL Metadata locking problems. For this new functionality the Metadata locking plugin in MariaDB or the Metadata instrumentation in MySQL PERFORMANCE_SCHEMA must be enabled. This can be done dynamically and persistent for MariaDB:

mariadb> INSTALL SONAME 'metadata_lock_info';

and dynamically for MySQL:

mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name = 'wait/lock/metadata/sql/mdl';

or persistent for MySQL:

#
# my.cnf
#
performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'

Sources:

• RSyslog Documentation
• How to Configure Remote Logging with Rsyslog on Ubuntu 18.04

This notes are intended for Ubuntu 18.04!

Install and Configure Rsyslog Server

dpkg -l | grep rsyslogd
apt-get update && apt-get install rsyslog

systemctl start rsyslog
systemctl enable rsyslog
systemctl status rsyslog

Check rsyslog version (v7 vs. v8!):

rsyslogd -v

General configuration

#
# /etc/rsyslog.conf
#

# provides UDP syslog reception
module(load="imudp")
input(type="imudp" port="514")

and/or

# provides TCP syslog reception
module(load="imtcp")
# RPC service is using this port as well.
input(type="imtcp" port="50514")

Restrictions:

# GLOBAL DIRECTIVES

$AllowedSender UDP, 192.168.1.0/24, [::1]/128, *.fromdual.com
$AllowedSender UDP, 192.168.56.0/24, [::1]/128, *.fromdual.com

$AllowedSender TCP, 192.168.1.0/24, [::1]/128, *.fromdual.com
$AllowedSender TCP, 192.168.56.0/24, [::1]/128, *.fromdual.com

Specific configuration:

#
# …

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](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

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

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](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

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

Trx # 86153878218 exists in both Galera Cluster conflics which are 46 seconds apart (= long running transaction?). Table `rt3`.`Tickets` seems to be involved in both cases. One should investigate also in this query or transaction we cannot see here...

MySQL thread id 6147, OS thread handle 0x7f5af77fe700, query id 5394536 192.168.1.42 node1
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 67799 page no 2924419 n bits 136 index `PRIMARY` of table `rt3`.`Tickets` trx id 86153787673 lock_mode X locks rec but not gap
2020-06-09 08:27:24 9737 [Note] WSREP: cluster conflict due to high priority abort for threads:
2020-06-09 08:27:24 9737 [Note] WSREP: Winning thread:
   THD: 26, mode: applier, state: executing, conflict: no conflict, seqno: 1297074501
   SQL: (null)
2020-06-09 08:27:24 9737 [Note] WSREP: Victim thread:
   THD: 6147, mode: local, state: executing, conflict: no conflict, seqno: -1
   SQL: SELECT main.* FROM Attributes main  WHERE (main.ObjectId = 3137700935) AND …

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

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

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

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

Download

The new FromDual Ops Center for MySQL and compatible databases (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MySQL and compatible databases please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for focmm).

Any feedback, statements and …

cd /var/log
ll -d -Z mysql* mysql/*
mkdir mysql
chown mysql: mysql
semanage fcontext -a -t mysqld_db_t "/var/log/mysql/(/.*)?"
restorecon -Rv /var/log/mysql
ll -d -Z mysql* mysql/*

And Non-standard database set up with SELinux

/usr/sbin/logrotate /etc/logrotate.conf

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

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

About time - was, is and will be

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

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

Ups! There are no more “shutting down …” messages like in MySQL 5.7:

2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully
2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads
2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients
2020-03-30T08:04:51.898433Z 0 …

The InnoDB deadlock detector was rewritten in MySQL 5.7.1, in a merge commit that does not mention any WL or bug number, nor any reviewer. I did not review those changes, and I cannot say whether the algorithmic complexity of the deadlock checker was reduced. Possibly the rewrite replaced procedural recursion with iteration over a data structure that explicitly represents a stack.

I think that the InnoDB locking performance could be significantly improved by extending the use of implicit locking to UPDATE and DELETE operations, as noted in MDEV-16232. It could also be helpful to merge multiple record log bitmaps into a single bitmap per page, with 4 bits per record, as noted in MDEV-16406.

Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

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

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

The MySQL documentation tells us the following
[1
]:

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

And about the parameter innodb_deadlock_detect itself
[2
] itself:

This option is used to disable deadlock detection. On …

MariaDB/MySQL Stored Language is called SQL/PSM.
There are 4 different types of Stored Language: Stored Procedures, Stored Functions, Triggers and Events.

Stored Procedures

Stored Procedure with a Cursor:

DELIMITER //
CREATE PROCEDURE cleanup(IN pData VARCHAR(48))
BEGIN
  DECLARE vId INTEGER;
  DECLARE vNotFound INTEGER;

  DECLARE cCleanUp CURSOR FOR
    SELECT id FROM test WHERE data = pData;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET vNotFound = 1;
  
  OPEN cCleanUp;
  lGetRecord: LOOP
  
    FETCH cCleanUp INTO vId;
    IF vNotFound = 1 THEN 
      LEAVE lGetRecord;
    END IF;
  
    DELETE FROM test WHERE id = vId;
  END LOOP lGetRecord;
  CLOSE cCleanUp;
END //
DELIMITER ;

CALL cleanup('Bla1Trx');

Stored Procedure with SUPER PRIVILEGE

Disabling binary log on a per session base requires the SUPER privilege:

SQL> SET SESSION sql_log_bin = off;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

If you do NOT want to GRANT the SUPER …

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

Your FromDual Team

Picture by kalhh on Pixabay

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

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

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

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

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

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

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

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

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

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for focmm).

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

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

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

Die neue Version steht hier zum Download bereit.

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

Wichtige Neuerungen

• fpmmm steht jetzt paketiert für CentOS mit RPM und Ubuntu mit DEB Paketen zur Verfügung.
• MariaDB 10.4 wird ab sofort offiziell durch fpmmm unterstützt.
• fpmmm unterstützt ab sofort ausschliesslich PHP Version 7+.
• Sämtliche Zabbix Templates wurden auf die Version 4.0 upgegraded.
• Ein Backup-Hook wurde eingeführt. Sie können somit Ihr eigenes Backup-System oder den FromDual Backup Manager in fpmmm einbinden.
• Graphen für InnoDB Buffer Pool …