MySQL Tech-Feed (en)

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 …

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 …

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

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 …

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

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 …

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 …

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 …

When it comes to MariaDB/MySQL Database consulting engagements we often see, that our customers are very confused by many different technologies advertised to them.

Below you will find some simple concepts and ideas we use during our consulting engagements to solve some issues and answer some questions.

General problem solving approach

• 1
  . Written description of the problem to solve. This helps to learn about your problem and better understand your problem.
• 2
  . Searching for a suitable method, technology or …

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

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

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

Download

The new FromDual Performance Monitor for …

You are using an old version of the FromDual Performance Monitor for MariaDB and MySQL (fpmmm). Please consider using a more recent version.

Your InnoDB Redo Log Buffer is too small for large transactions. Make the InnoDB Redo Log Buffer (innodb_log_buffer_size) bigger if you have enough RAM available to avoid additional I/O.
1 Mibyte is good for databases with small transactions. 8 Mibyte is good for medium size transactions. 64 MiByte is good for large transactions.

See also for MariaDB MySQL.

Table of Contents

• Before you start
• Infinite Loop Example

Before you start

SQL> SET SESSION sql_mode='ORACLE';

Infinite Loop Example

DELIMITER /

BEGIN

LOOP
  SELECT 'Hello world from MariaDB anonymous PL/SQL block!';
END LOOP; 

END;
/

DELIMITER ;

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

Because more and …

Table of Contents

• ifconfig
• ip
• ethtool --statistics
• nstat
• netstat --statistics
• ss
• Reasons for dropped packets
• Features of Network card
• Features of Network driver
• Network driver ring buffer
• TCP socket received and send buffer
• Flow Control
• Check over time: sar
• Dropwatch
• perf
• tcpdump
• /proc/net/netstat
• System Tap (stap)
• Literature

When we do MariaDB and MySQL Database server analysis we see from time to time systems with a lot of dropped packets on a network interface:

ifconfig

# export DEV=enp0s25
# ifconfig …

Modern Linux Systems have a mechanism called logrotate to rotate different log files.

The general configuration file is located under /etc/logrotate.conf and specific changes are under /etc/logrotate.d/

By default the logrotate job is started once a day by a O/S cron.daily job: /etc/cron.daily/logrotate

Because the default log rotation configuration does not exactly what I want I have adapted it a bit:

First I need a database user for log rotation:

CREATE USER 'logrotate'@'localhost' IDENTIFIED BY 'secret'; …