Shinguz's blog

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.

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.

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.

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:

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

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

Table of Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

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

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

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

Table of Contents

• Unlink multiple records from Target List
• Find Target Lists of Target/Prospect
• List all email addresses of all accounts
• Objects without Target List
• Find PLP without Objects
• Find Prospect List Entries per Prospect Type
• Quick set-up on Ubuntu 18.04
• Add targets from old target lists to new target list
• Undelete accounts

Unlink multiple records from Target List

1. Find id of record to unlink.

SQL> SET @uid = 'f232e8d0-9b82-20f7-7c35-5c1a5ce94e0a';

2. Find entry in Target List table:

SQL> SELECT plp.id, plp.related_type, plp.date_modified, plp.deleted
     , pl.name
  FROM prospect_lists_prospects AS plp
  JOIN prospect_lists AS pl ON plp.prospect_list_id = pl.id
 WHERE plp.related_id = @uid
;

3. Update entry in Target List table:

SQL> START TRANSACTION;
SQL> UPDATE prospect_lists_prospects
   SET deleted = 1
 WHERE related_id = @uid
   AND id = '5b3047b2-3b45-1a6e-e6fb-5d3fe1efa93b'
;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
SQL> COMMIT;

Find Target Lists of Target/Prospect

Result:

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

See also our former articles:

• Do not underestimate performance impacts of swapping on NUMA database systems
• MariaDB and MySQL swap analysis

When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available:

The General Query Log in MariaDB is a Log (file or table) that logs every statement sent from the Application to the MariaDB Database Server from the Connect Command to the Quit Command. A simple example you can find here:

The story goes on here.

Ups, that was a quick reaction… https://jira.mariadb.org/browse/MDEV-9077

The MySQL sys Schema is a cool thing and was mostly developed by Mark Leith and Jesper Wisborg Krogh from Oracle/MySQL. The sys Schema is based on the somehow cryptic MySQL PERFORMANCE_SCHEMA which was developed by Oracle/MySQL as well.

I could not resist! I have forked the MySQL sys Schema on GitHub.

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!