You are here

MySQL Tech-Feed (en)

FromDual Recovery Manager (rman) with progress indicator

Shinguz - Tue, 2019-08-20 21:44

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.

You can check your current rman version as follows:

# ./bin/rman --version 2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less ... progress Print progress information to STDOUT. ...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.

Taxonomy upgrade extras: RestoreRecoverypitrbrmanrmanFromDual Backup and Recovery Manager

Schulung Galera Cluster für MariaDB und MySQL im September 2019 in Berlin

Shinguz - Sun, 2019-08-18 21:38

Die Sommerferien sind vorbei. Mit neuem Elan in den Herbst! Zeit für eine Weiterbildung?

Vom 19. bis 20. September führt FromDual wieder die Galera Cluster Schulung Galera Cluster für MySQL und MariaDB in Berlin durch. Siehe auch unsere weiteren Schulungstermine.

Es hat noch Plätze frei! Anmelden können Sie sich direkt bei unserem Schulungs-Partner, der Heinlein Akademie.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken mit einem Galera Cluster zu betreuen haben und gerne besser verstehen wollen, wie Sie den Galera Cluster sicher und stabil betreiben.

In dieser Schulung behandeln wir, wie Sie einen Galera Cluster richtig designen und aufsetzten, wie Sie ihn installieren, konfigurieren und betreiben. Zudem betrachten wir mögliche Load Balancing Mechanismen und besprechen Performance Fragen zu Galera.

Das Ganze ist mit zahlreichen Übungen versehen, damit Sie das gelernte auch gleich praktisch anwenden können!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser zweitägigen Galera Cluster Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulunggaleraclustertraining

MariaDB and MySQL Character Set Conversion

Shinguz - Sat, 2019-08-17 21:50
Contents 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:

  • 2 smaller changes are easier to control then one big shot.
  • We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So we hope to hit less problems and bugs.

For Upgrading see also MariaDB and MySQL Upgrade Problems

Remark: It makes possibly also sens to think about Collations before starting with the conversion!

Character Sets

Historically MariaDB and MySQL had the default Character Set latin1 (Latin-1 or ISO-8859-1) which was sufficient for most of the western hemisphere.

But as technology spreads and demands increase other cultures want to have their characters represented understandably as well. So Unicode standard was invented. And MariaDB and MySQL applied this standard as well.

The original MariaDB/MySQL utf8(mb3) implementation was not perfect or complete so they implemented utf8mb4 as a super set of utf8(mb3). So at least since MariaDB/MySQL version 5.5 latin1, utf8 and utf8mb4 are available. The current MySQL 5.7 utf8mb4 implementation should cover Unicode 9.0.0:

SQL> SELECT * FROM information_schema.character_sets WHERE character_set_name LIKE 'utf8%' OR character_set_name = 'latin1'; +--------------------+----------------------+----------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+----------------------+--------+ | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+----------------------+--------+

The default Character Set up to MariaDB 10.4 and MySQL 5.7 was latin1. In MySQL 8.0 the default Character Set has changed to utf8mb4. There are no signs so far that MariaDB will take the same step:

SQL> status -------------- mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL) Connection id: 84 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.16 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysql-3332.sock Uptime: 3 days 47 min 13 sec

So we see a general trend from latin1 to utf8(mb3) to utf8mb4 technically and business wise (aka globalization).

For the DBA this means sooner or later we have to think about a conversion of all tables of the whole database instance (all tables of all schemata) to utf8mb4!

Steps to convert Character Set to utf8mb4 Analyzing the Server

First of all one should analyze the system (O/S, database instance and client/application). On the server we can run the following command to verify the actual used and supported Character Set:

# locale LANG=en_GB.UTF-8 LANGUAGE= LC_CTYPE="en_GB.UTF-8" LC_NUMERIC="en_GB.UTF-8" LC_TIME="en_GB.UTF-8" LC_COLLATE="en_GB.UTF-8" LC_MONETARY="en_GB.UTF-8" LC_MESSAGES="en_GB.UTF-8" LC_PAPER="en_GB.UTF-8" LC_NAME="en_GB.UTF-8" LC_ADDRESS="en_GB.UTF-8" LC_TELEPHONE="en_GB.UTF-8" LC_MEASUREMENT="en_GB.UTF-8" LC_IDENTIFICATION="en_GB.UTF-8" LC_ALL=

On the MariaDB/MySQL database instance we check the current server configuration and the session configuration with the following commands:

SQL> SHOW SESSION VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+

These configuration variables are for Client/Server communication: character_set_client, character_set_connection and character_set_results. These for Server configuration: character_set_server and character_set_database (deprecated in MySQL 5.7). And these for System internals and File System access: character_set_system and character_set_filesystem.

Sometimes we see customers using the Logon Trigger init_connect to force clients for a specific Character Set:

SQL> SHOW GLOBAL VARIABLES LIKE 'init_connect'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | init_connect | SET NAMES latin1 | +---------------+------------------+

The SET NAMES command sets the character_set_client, character_set_connection and character_set_results session variables. [ Lit. ]

Analyzing the Application and the Clients

Similar steps to analyze the Application and Clients should be taken. We want to answer the following questions:

  • Support of utf8 of Application/Client O/S (Windows)?
  • Support of utf8 of Web Server (Apache (AddDefaultCharset utf-8), Nginx, IIS, ...)
  • Version of programming language (Java, PHP (5.4 and newer?), ...)
  • Version of MariaDB and MySQL Connectors (JDBC (5.1.47 and newer?), ODBC (5.3.11 and newer?), mysqli/mysqlnd (⋝7.0.19?, ⋝7.1.5?), ...)
  • Application code (header('Content-Type: text/html; charset=utf-8');, <?xml version="1.0" encoding="UTF-8"?>, <meta http-equiv="Content-Type" content="text/html; charset=utf-8">, <form accept-charset="utf-8">, htmlspecialchars($str, ENT_NOQUOTES, "UTF-8"), $mysqli->set_charset('utf8mb4');, mbstring [ Lit. ], etc.

See also: Configuring Application Character Set and Collation

If you do not have your Application under control your DBA can help you to find out with the General Query Log (SET GLOBAL general_log = 1;) what is going on:

190815 19:03:00 12 Connect root@localhost on 12 Query select @@version_comment limit 1 12 Query SET NAMES latin1 12 Query SET NAMES utf8 190815 19:05:24 12 Quit

or with some queries on the PERFORMANCE_SCHEMA:

-- Works since MySQL 5.6/MariaDB 10.0 SQL> SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , sca.attr_name, sca.attr_value FROM performance_schema.threads AS t JOIN performance_schema.session_connect_attrs AS sca ON sca.processlist_id = t.processlist_id WHERE t.processlist_user IS NOT NULL AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | attr_name | attr_value | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ | 103 | 78 | replication | localhost | NULL | _os | linux-glibc2.12 | | 103 | 78 | replication | localhost | NULL | _client_name | libmysql | | 103 | 78 | replication | localhost | NULL | _pid | 29269 | | 103 | 78 | replication | localhost | NULL | program_name | mysqld | | 103 | 78 | replication | localhost | NULL | _platform | x86_64 | | 103 | 78 | replication | localhost | NULL | _client_replication_channel_name | NULL | | 103 | 78 | replication | localhost | NULL | _client_role | binary_log_listener | | 103 | 78 | replication | localhost | NULL | _client_version | 5.7.26 | +-----------+----------------+------------------+------------------+----------------+----------------------------------+---------------------+ -- Works since MySQL 5.7 only SELECT t.thread_id, t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_db , vbt.variable_name, vbt.variable_value FROM performance_schema.threads AS t JOIN performance_schema.variables_by_thread AS vbt ON vbt.thread_id = t.thread_id WHERE t.processlist_user IS NOT NULL AND (vbt.variable_name like 'charac%' OR vbt.variable_name LIKE 'coll%') AND t.thread_id = 103 ; +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | thread_id | processlist_id | processlist_user | processlist_host | processlist_db | variable_name | variable_value | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+ | 103 | 78 | replication | localhost | NULL | character_set_client | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_connection | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_database | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_filesystem | binary | | 103 | 78 | replication | localhost | NULL | character_set_results | latin1 | | 103 | 78 | replication | localhost | NULL | character_set_server | latin1 | | 103 | 78 | replication | localhost | NULL | collation_connection | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_database | latin1_swedish_ci | | 103 | 78 | replication | localhost | NULL | collation_server | latin1_swedish_ci | +-----------+----------------+------------------+------------------+----------------+--------------------------+-------------------+
Preparation of the Server Settings and the Application

To have a better control of the impact of some changes we decided to do some changes on the Application first:

  • Application is setting the Character Set properly itself ($mysqli->set_charset('utf8mb4') [ Lit. ]). In the same step also sql_mode can be set by the application so we can use the defaults on server side in the future.
  • Apache and PHP are configured to support UTF-8.
  • After this step init_connect, character_set_server and character_set_database can be changed to utf8mb4 on the Server and --skip-character-set-client-handshake can be removed at the same time [ Lit. ]
Convert Tables to utf8mb4

First we checked and converted the default Character Set of the Schemata/Databases:

SQL> SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ; +-------------+----------------------------+------------------------+ | schema_name | default_character_set_name | default_collation_name | +-------------+----------------------------+------------------------+ | focmm | latin1 | latin1_swedish_ci | | test | latin1 | latin1_swedish_ci | | foodmart | latin1 | latin1_swedish_ci | | erp | latin1 | latin1_swedish_ci | | world | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+

Converting the Schemata is done as follows:

SQL> ALTER DATABASE ... DEFAULT CHARACTER SET utf8mb4;

which is a fast operation.

To convert the tables there are many different possibilities we considered:

  • The easy one: ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;
  • The possibly faster one: dump/restore with sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/'
  • The possibly fastest one: drop Secondary Indexes, dump/restore with sed, create Secondary Indexes again (mysqlpump).
  • The automated one: With Perconas pt-online-schema-change [ Lit. ] or Facebooks OnlineSchemaChange OSC [ Lit. ]
  • The most elegant but not supported one: Master/Slave Replication.

Already with converting the first table we run into a problem:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

This table had a Primary Key of a length of more than 255 bytes and this is not possible with the old InnoDB Antelope File Format:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb_file_format%' OR Variable_name LIKE 'innodb_large%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_large_prefix | OFF | +--------------------------+----------+

So we have to find out first, which tables are still in old Antelope File Format:

SQL> SELECT table_schema , CASE WHEN row_format = 'Dynamic' THEN 'Barracuda' WHEN row_format = 'Compressed' THEN 'Barracuda' WHEN row_format = 'Compact' THEN 'Antelope' WHEN row_format = 'Redundant' THEN 'Antelope' END AS 'file_format' , COUNT(*) FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql') GROUP BY table_schema, file_format ; +--------------+-------------+----------+ | table_schema | file_format | count(*) | +--------------+-------------+----------+ | foodmart | Barracuda | 23 | | test | Barracuda | 1 | | world | Antelope | 2 | | world | Barracuda | 1 | +--------------+-------------+----------+

Then we could covert the table correctly forcing the Barracuda File format:

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4, ROW_FORMAT=dynamic;
Testing of new Character Set

The last but most important step is to test the changes. Here we recommend to do as many as possible different tests:

  • MySQL CLI: mysql
  • phpMyAdmin
  • MySQL Workbench
  • Other GUIs
  • Your Application

Especially test careful data with umlauts (öäüÄÖÜß), foreign Characters (Turkish, Cyrillic, CJK Characters) and Emojis. Good Candidates are: Lastname, City or free text fields like Comment.

Some aid you also can get from here:


MySQL Pump

mysqlpump — A Database Backup Program

This utility is currently not available for MariaDB. If works for MySQL 5.7 and newer and does NOT support MySQL 5.6. So we cannot use it for the Upgrade Process from MySQL 5.6 to 5.7. Newest MySQL Releases contain Bug fixes and even new Features in mysqlpump so we can assume it is still supported and actively maintained. Recent releases contain some fixes for trivial bugs so we can assume mysqlpump is not widely used yet and not as mature yet as mysqldump. An alternative product would be MyDumper from Domas@Facebook (Lauchpad, GitHub).

Interesting features are:

  • Parallel dumping of databases.
  • Secondary Index restore separated from Table Restore.

Missing features are:

  • Does not support MariaDB and MySQL 5.6 and earlier.
  • No binary log position is stored with backup (--master-data) but only GTID? So we have to use/enable GTID based Replication.

First test:

# mysqlpump --user=root --all-databases --single-transaction --triggers --routines --events --hex-blob > /tmp/mysqlpump_backup.sql Dump progress: 1/4 tables, 0/87 rows Dump progress: 41/46 tables, 789469/6956116 rows Dump progress: 41/46 tables, 1608219/6956116 rows Dump progress: 45/46 tables, 2358412/6956116 rows Dump progress: 45/46 tables, 3437912/6956116 rows Dump progress: 45/46 tables, 4493662/6956116 rows Dump progress: 45/46 tables, 5177662/6956116 rows Dump progress: 45/46 tables, 5568662/6956116 rows Dump progress: 45/46 tables, 6216162/6956116 rows Dump progress: 45/46 tables, 6716662/6956116 rows Dump completed in 9860 milliseconds ALTER TABLE `foodmart`.`account` ADD KEY `i_account_parent` (`account_parent_id`); ALTER TABLE `foodmart`.`category` ADD KEY `i_category_parent` (`category_parent_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_department_id` (`department_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_store_id` (`store_id`); ALTER TABLE `foodmart`.`employee` ADD KEY `i_employee_supervisor_id` (`supervisor_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_store_id` (`store_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_account_id` (`account_id`); ALTER TABLE `foodmart`.`expense_fact` ADD KEY `i_expense_time_id` (`time_id`);

Test against MySQL 5.6:

Server version is not compatible. Server version should be 5.7.8 or above.
MySQL Master/Slave Replication for Character Set conversion

Replication of Columns Having Different Data Types should work since MySQL 5.1.21. General rule when Statement Based Replication is used: If the query would work executed directly on the Slave it should also work on the Slave with Replication! So utf8mb4 on the Slave should also be possible with SBR. In MySQL 5.5.3 Attribute Promotion and Attribute Demotion was introduced with LOSSY and NON_LOSSY slave_type_conversions. But: Replication between columns using different character sets is not supported!

Some tests:

MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); MasterSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 3 | öäüÄÖÜß | 2019-08-15 19:52:29 | +----+----------------+---------------------+ SlaveSQL> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.test' cannot be converted from type 'varchar(255(bytes))' to type 'varchar(1020(bytes) utf8mb4)' ... SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | +----+----------------+---------------------+

See also: Why is varchar(255) not varchar(255)? And:

MasterSQL> SET SESSION binlog_format=STATEMENT; MasterSQL> INSERT INTO test VALUES (NULL, 'öäüÄÖÜß', NULL); SlaveSQL> SELECT * FROM test; +----+----------------+---------------------+ | id | data | ts | +----+----------------+---------------------+ | 1 | öäüÄÖÜß | 2019-08-15 19:48:48 | | 2 | öäüÄÖÜß | 2019-08-15 19:50:00 | | 4 | öäüÄÖÜß | 2019-08-15 19:54:16 | +----+----------------+---------------------+
MySQL Shell, mysqlsh Upgrade Checker Utility

Since MySQL 5.7 we have a new MySQL Utility, the MySQL Shell (mysqlsh). The Upgrade Checker Utility in MySQL Shell will replace mysql_upgrade in MySQL 8.0. So we wanted to know if it is of any use for converting Character Set to utf8mb4? You can use the Upgrade Checker Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. But: The Upgrade Checker Utility does not support checking MySQL Server instances 5.6 an earlier. MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. [ Lit. ]

# ./mysqlsh root@localhost:3319 JS > util.checkForServerUpgrade() The MySQL server at localhost:3319, version 5.6.42-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... Util.checkForServerUpgrade: Detected MySQL server version is 5.6.42, but this tool requires server to be at least at version 5.7 (LogicError) JS > util.checkForServerUpgrade('root@127.0.0.1:3309') The MySQL server at localhost:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... ... 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html test.test.data - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found ... Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. JS > util.checkForServerUpgrade('root@127.0.0.1:3309', {'targetVersion':'5.7.27'}) The MySQL server at 127.0.0.1:3309, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 5.7.27... Util.checkForServerUpgrade: This tool supports checking upgrade to MySQL server versions 8.0.11 to 8.0.17 (LogicError)

Because latin1 tables are still supported in MySQL 8.0 the Upgrade Checker Utility does not complain or do anything for latin1 tables. So it is not of any use for the utf8mb4 conversion task except you already have some old utf8(mb3) tables.

Taxonomy upgrade extras: character setconversionupgradelatin1utf8utf8mb4replicationmysqlshmysqlpump

FromDual Ops Center for MariaDB and MySQL 0.9.2 has been released

Shinguz - Tue, 2019-08-13 16:50

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.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage 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.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.2

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 0.9.2

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

Changes in Ops Center 0.9.2 Instance
  • Replication: Mariadb 10.4 problem with mysql.user table fixed.
  • Restore: Clean-up work done.
  • Restore: Point-in-Time-Recovery is implemented.
  • Operations: Binary Log state added.
Security
  • Potential XSS hole fixed.
  • Bootstrap CSS updated to 4.3.1
General
  • Newest MyEnv Library added.
  • Function array_pop replaced by array_shift.
  • Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.
Build and Packaging
  • Dependency bug in Debian control file fixed.
Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestoreFromDual Ops Centerops center

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.1 has been released

Shinguz - Tue, 2019-08-06 09:12

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

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.1 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.1.tar.gz shell> rm -f brman shell> ln -s brman-2.2.1 brman
Changes in FromDual Backup and Recovery Manager 2.2.1

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
  • No changes.

FromDual Backup Manager
  • Workaround for MariaDB binlog purge problem added do binlog backup.

FromDual Recovery Manager
  • PiTR did not find binary logs in case they were from a foreign instance.

FromDual brman Catalog
  • No changes.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.0 has been released

Shinguz - Mon, 2019-07-29 16:51

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

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.0 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.0.tar.gz shell> rm -f brman shell> ln -s brman-2.2.0 brman
Changes in FromDual Backup and Recovery Manager 2.2.0

This release is a new major release series. It contains some new features. 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
  • Automated testing improved.
  • Packaging optimized. Especially unnecessary dependencies removed.
  • Code redundancy removed, made code more readable and partially code reorganized.

FromDual Backup Manager
  • Requirement checks improved. Warnings do not spoil rc any more.
  • Bug with --pass-through and --per-schema fixed (MGB).
  • Backup size is now correctly reported to fpmmm. And message is written to the log.
  • Timestamp format was wrong in start backup tag.
  • Globals removed from function doPrivilegeBackup.
  • Columns of table mysql.user quoted for MariaDB 10.4 compatibility.
  • Test on fpmmm-cache-file fixed from dir to file.
  • Some bugs in function binlogBackup simulation fixed.
  • Too verbose debugging disabled.
  • Sftp archiving did not fail or complain if directory does not exist.
  • Better input validation for option --archivedestination.
  • Function checkArguments renamed to checkBmanOptions.
  • Wrong error message for servers without binary log removed.
  • Skip gathering of binlog file and pos in case of binary log was not enabled.

FromDual Recovery Manager
  • Missing --stop-instance option added to usage.
  • Omitted option --policy PHP error fixed for logical and physical restore, this allows us to restore from conventional backup infrastructure as well.
  • Tables slave_master_info and slave_relay_log_info in mysql schema are omitted in restore progress output.
  • Error message for missing --backup-name option improved.
  • Error handling and messages improved as preparation for PiTR.
  • Binlog restore type removed and check made accordingly that it is not possible any more.
  • PiTR pre Recover test implemented.
  • Various tests for test automation improved.
  • Binlog position discovery possible also for compressed files.
  • Execution time information added.
  • Option --debug added to rman.
  • Point-in-Time-Recovery for logical and physical backup implemented.
  • Instance starting for physical backup was implemented.
  • Option --simulate was implemented everywhere.
  • Option --disable-log-bin added and error handling improved.
  • Option --config activated in rman.
  • Fast PiTR is implemented.
  • Only full physical restore is supported atm. This was not caught correctly.
  • Schema PiTR also possible.

FromDual brman Catalog
  • No changes.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrman

Schulung MariaDB/MySQL für Fortgeschrittene im August 2019 in Köln

Shinguz - Mon, 2019-07-29 14:29

Sommerferien-Zeit – für all die Daheimgebliebenen dürfte es jetzt hoffentlich etwas ruhiger zu und her gehen. Zeit für eine Weiterbildung? Es bleibt nicht mehr viel Zeit, das Jahres-Schulungs-Budget aufzubrauchen!

Vom 19. bis 23. August führt FromDual wieder einmal die Schulung MariaDB und MySQL für Fortgeschrittene in Köln durch. Siehe auch unsere Schulungstermine.

Es hat noch Plätze frei! Anmelden kann man sich direkt bei unserem Schulungs-Partner, der GFU Cyrus AG.

Diese MariaDB/MySQL Weiterbildung richtet sich an alle DBAs, DevOps und System Administratoren, welche MariaDB und MySQL Datenbanken zu betreuen habe und gerne besser verstehen wollen, wie man das noch besser macht.

In dieser Schulung behandeln wir Backup, Restore und Point-in-Time-Recovery sowohl einer kleinen wie auch einer grossen Datenbank. Aufsetzen von hochverfügbaren MariaDB und MySQL Datenbanken mittels der Master/Slave Replikation sowie dem Galera Cluster inklusive Switch-Over-Techniken. Schliesslich und endlich beschäftigen wir uns auch noch zwei Tage mit Datenbank Performance Tuning (Hardware, O/S, DB Konfiguration, Schema Tuning und SQL Query Tuning, etc.).

Das Ganze ist mir zahlreichen Übungen versehen, damit man das gelernte auch gleich praktisch anwenden kann!

Die Schulung findet in deutscher Sprache statt.

Den detaillierten Inhalt dieser fünftägigen MySQL/MariaDB Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulungtraininggaleraBackupRestorepoint-in-time-recoveryreplikationPerformance Tuning

Who else is using my memory - File System Cache analysis

Shinguz - Fri, 2019-07-19 15:52

See also our former articles:

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

# free --kilo --wide total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744

The values for buffers and especially for cache can be sometimes quite big. In this case they use about 10 GiB. So let us have a look what these things called buffers and cache are, using our valuable RAM... When we check the man pages of free we will find:

# man free ... buffers Memory used by kernel buffers (Buffers in /proc/meminfo) cache Memory used by the page cache and slabs (Cached and Slab in /proc/meminfo) buff/cache Sum of buffers and cache

So let us check a more fine grained information in /proc/meminfo which is an interface to the kernel data structures:

# cat /proc/meminfo | grep -e ^Cached -e Slab -e Buffers Buffers: 307872 kB Cached: 10155156 kB Slab: 609916 kB

Same values! Then let us have a look at the man pages of proc what we can find about these values:

# man proc ... Buffers Relatively temporary storage for raw disk blocks that shouldn't get tremendously large (20MB or so). Cached In-memory cache for files read from the disk (the page cache). Doesn't include SwapCached. Slab In-kernel data structures cache.

So it looks like we have a raw I/O Cache (called Buffer Cache) and a File System I/O Cache (called Page Cache). So how does this work? What is a raw I/O? And is a Files System I/O cached once (Cached) or twice (Cached and Buffers)?

When we dig a bit deeper we can find that prior to Linux Kernels 2.4 the two Caches were distinct. So that was a waste of memory (RAM). It seems like today this is not the case any more [1], [2], [3]. And man pages are a bit out of date or at least not very precise?

Analysing the Linux Page Cache

A very good source when it comes to Linux Performance Tuning and Measuring is Brendan Gregg's Website. To measure Linux Page Cache Hit Ratio he provides a tool called cachestat which is part of the perf-tools collection on GitHub.

With cachestat we get a per second statistics of the Buffer Cache and the Page Cache (without Slabs), Cache Hits, Cache Misses, Dirty Buffer Entries in the Cache and a Cache Hit Ratio:

# sudo cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 1419 8 0 99.4% 338 9406 1368 0 0 100.0% 338 9406 1391 0 0 100.0% 338 9406 8558 0 29 100.0% 338 9406 31870 0 163 100.0% 338 9406 1374 0 24 100.0% 338 9406 1388 0 0 100.0% 338 9406 1370 0 0 100.0% 338 9406 1388 0 0 100.0% 338 9406

Brendan Gregg also mentions a tool called pcstat (on GitHub) by Al Tobey which gets Page Cache Statistics for Files. Unfortunately I had some problems building it on my Ubuntu 16.04 with Go version 1.6. So I built it on an Ubuntu 18.04 (Go 1.10) and copied it over to to Ubuntu 16.04):

# export GOPATH=/tmp/ # cd $GOPATH # go get golang.org/x/sys/unix # go get github.com/tobert/pcstat/pcstat # bin/pcstat $GOPATH/bin/pcstat

Then I tried pcstat out against a MariaDB 10.4 instance. In the output we can see how big the files are in bytes, how many pages of 4 kib this corresponds to, how many of these 4 kib pages are cached and the percentage of pages cached:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

When we run pcstat over time with the famous watch command we can even see how the Page Cache is heating up:

# watch -d -n 1 'pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* ; free -w' +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 2416 | 012.418 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3165 | 004.829 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 5890 | 008.987 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 1164 | 034.976 | +------------------------------------------------------+----------------+------------+-----------+---------+ total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744

An other tool which was discussed on Brendans Website was vmtouch - the Virtual Memory Toucher (on GitHub, Documentation). With vmtouch we can see for example how much of the directory /home/mysql/database/mariadb-104/data (datadir) is currently in cache:

# vmtouch -f /home/mysql/database/mariadb-104/data Files: 503 Directories: 9 Resident Pages: 29356/231060 114M/902M 12.7% Elapsed: 0.009668 seconds

Or more fine grained how much of InnoDB System Files are currently in memory:

# vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [oOooooo ooooo ] 2416/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOO] 3165/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [OOOOOOOOOOOOOOOOOOOOOo ] 23192/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 Files: 5 Directories: 0 Resident Pages: 28773/153604 112M/600M 18.7% Elapsed: 0.005499 seconds

A further question to answer is: Can I see all files cached in the Page Cache? So it seem like this is not easily possible:

There is no efficient search mechanism for doing the reverse - getting a file name belonging to a data block would require reading all inodes and indirect blocks on the file system. If you need to know about every single file's blocks stored in the page cache, you would need to supply a list of all files on your file system(s) to fincore. But that again is likely to spoil the measurement as a large amount of data would be read traversing the directories and getting all inodes and indirect blocks - putting them into the page cache and evicting the very page cache data you were trying to examine. [5]

Also in this article we can read about the Linux File Tools (linux-ftools) by Google. It seems to be a bit more complicated to make them work. So I let it be.

How is the Page Cache related to MariaDB

After all this technical O/S discussion, how is Linux Page Cache related to your MariaDB Database? Your MariaDB Database caches Data and Indexes as well. For the InnoDB Storage Engine this is the InnoDB Buffer Pool and for the Aria Storage Engine this is the Aria Page Cache Buffer. So if your MariaDB Database caches pages and if your Linux O/S caches pages the probability is high they cache the same data twice and thus waste valuable RAM! Fortunately InnoDB is configurable in a way it does NOT cache InnoDB files in the Page Cache. This is controlled with the InnoDB Server Variable innodb_flush_method.

When we look at InnoDB Files which were opened in a "normal" way (default: innodb_flush_method = fsync) we get the following information about how the files were opened (man 2 open and [6]):

# lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,LG,0x80000 8,1 12582912 9175280 ./ibtmp1

The interesting column here is the FILE-FLAG column which indicates (man lsof):

# man lsof FILE-FLAG when g or G has been specified to +f, this field contains the contents of the f_flag[s] member of the kernel file structure and the kernel's per-process open file flags (if available); `G' causes them to be displayed in hexadecimal; `g', as short-hand names; two lists may be displayed with entries separated by commas, the lists separated by a semicolon (`;'); the first list may contain short-hand names for f_flag[s] values from the following table: DIR direct LG large file RW read and write access

The output is not so clear or completely understandable yet thus we want to have the open file flags in hexadecimal notation:

# lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x88002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x88002;0x0 8,1 12582912 9175280 ./ibtmp1

The Linux Kernel open file flags can be found here: fcntl.h. I have extracted the most relevant open file flags for our examination:

#define O_RDWR 00000002 (oct, 0x00002) #define O_DIRECT 00040000 (oct, 0x04000) /* direct disk access hint */ #define O_LARGEFILE 00100000 (oct, 0x08000) #define O_CLOEXEC 02000000 (oct, 0x80000) /* set close_on_exec */

So we can see that these 4 InnoDB files where opened with O_RDWR (RW), O_LARGE_FILE (LG) and O_CLOEXEC (not available (yet?) in lsof translation output).

Now let us start the MariaDB Database with the server variable set to: innodb_flush_method = O_DIRECT and check how the files where opened:

# lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,DIR,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,DIR,LG,0x80000 8,1 12582912 9175280 ./ibtmp1 # lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x8c002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x8c002;0x0 8,1 12582912 9175280 ./ibtmp1

We can see a new flag DIR or 0x04000 which means the files where opened with O_DIRECT. But only the InnoDB Temporary Table Tablespace and the InnoDB System Tablespace but not the two InnoDB Transaction Logs.

Translation of hex to oct: 0x8c002 = 02140002.

But what does O_DIRECT mean? Looking at the open(2) man pages we can find:

# man 2 open O_DIRECT (since Linux 2.4.10) Try to minimize cache effects of the I/O to and from this file. In general this will degrade performance, but it is useful in special situations, such as when applications do their own caching. File I/O is done directly to/from user-space buffers. The O_DIRECT flag on its own makes an effort to transfer data synchronously, but does not give the guarantees of the O_SYNC flag that data and necessary meta‐ data are transferred. To guarantee synchronous I/O, O_SYNC must be used in addition to O_DIRECT.

So O_DIRECT is exactly what we want in this case: Bypassing the File System Page Cache to not cache the Database blocks twice!

To verify the impact we run pcstat again:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 4 | 100.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 140 | 000.720 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 36844 | 056.219 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 65536 | 100.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 13400 | 081.787 | +------------------------------------------------------+----------------+------------+-----------+---------+

But... part of the InnoDB Tablespace files is still cached! Also checking the total amount of Buffers and Cache shows the same:

# free total used free shared buff/cache available Mem: 16106252 4401788 368200 456716 11336264 10691792 Swap: 31250428 1348440 29901988

So restarting the MariaDB database does not purge the Page Cache! Note: This is important to notice because bypassing the Page Cache helps to not wasting valuable RAM but it makes Database restart much more costly because Page Cache does not help/support InnoDB Buffer Pool heating any more!

Then let us clear the Linux Page Cache and check the result:

# echo 1 > /proc/sys/vm/drop_caches # free -w total used free shared buffers cache available Mem: 16106252 4395892 10539864 441708 696 1169800 10882984 Swap: 31250428 1348428 29902000

Checking with pcstat shows now that all InnoDB pages are wiped out of the Page Cache:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

And after a while running traffic on the test table we can see that InnoDB Transaction Log Files are cached again in the Page Cache but NOT the InnoDB Tablespace files:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3012 | 004.596 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 71303168 | 17408 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

Also with vmtouch we can see the difference:

./vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [ ] 0/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOOOo ] 4252/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [ ] 0/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 /home/mysql/database/mariadb-104/data/test/test.frm [ ] 0/1 /home/mysql/database/mariadb-104/data/test/test.ibd [ ] 0/17408 Files: 7 Directories: 0 Resident Pages: 4252/171013 16M/668M 2.49% Elapsed: 0.003264 seconds

And also cachestat shows the effect of a flushed Buffer Cache and Page Cache:

# ./cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 677882 19 740 100.0% 67 1087 679213 10 700 100.0% 67 1087 677236 0 732 100.0% 67 1087 685673 11 932 100.0% 67 1088 677933 5 703 100.0% 67 1088

Caution: Depending on your underlying I/O system it makes nevertheless sense to run your MariaDB Database with innodb_flush_method = fsync in certain cases! See also PostgreSQL behaviour.

Note: This information could also be interesting for PostgreSQL DBAs because they do redundant buffering with their shared_buffers (why plural? It is just one!?!) and the O/S Page Cache as well!

What is Slab

Beside Buffer Cache and the Page Cache itself we have a third thing in the /proc/meminfo statistics listed as Slabs. So what are Slabs? Slab seems to be a specific memory management (allocation) mechanism. It is used for frequently used objects in the Linux Kernel (buffer heads, inodes, dentries, etc.) [7-15]. So it contains something like other Linux Kernel Buffers and Kernel Caches.

What kind of other Linux Kernel Buffer and Kernel Caches exists can be found with the following command:

# sudo cat /proc/slabinfo slabinfo - version: 2.1 # name : tunables : slabdata nf_conntrack_1 14183 15275 320 25 2 : tunables 0 0 0 : slabdata 611 611 0 ext4_groupinfo_4k 8575 8596 144 28 1 : tunables 0 0 0 : slabdata 307 307 0 i915_gem_vma 523 950 320 25 2 : tunables 0 0 0 : slabdata 38 38 0 UDPv6 120 120 1088 30 8 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCPv6 2668 2668 280 29 2 : tunables 0 0 0 : slabdata 92 92 0 request_sock_TCPv6 24 72 328 24 2 : tunables 0 0 0 : slabdata 3 3 0 TCPv6 68 105 2112 15 8 : tunables 0 0 0 : slabdata 7 7 0 cfq_queue 391 442 232 17 1 : tunables 0 0 0 : slabdata 26 26 0 mqueue_inode_cache 72 72 896 18 4 : tunables 0 0 0 : slabdata 4 4 0 fuse_request 20 40 400 20 2 : tunables 0 0 0 : slabdata 2 2 0 fuse_inode 1 21 768 21 4 : tunables 0 0 0 : slabdata 1 1 0 fat_cache 102 408 40 102 1 : tunables 0 0 0 : slabdata 4 4 0 hugetlbfs_inode_cache 28 84 584 28 4 : tunables 0 0 0 : slabdata 3 3 0 squashfs_inode_cache 25 50 640 25 4 : tunables 0 0 0 : slabdata 2 2 0 jbd2_journal_handle 340 340 48 85 1 : tunables 0 0 0 : slabdata 4 4 0 jbd2_journal_head 2040 2040 120 34 1 : tunables 0 0 0 : slabdata 60 60 0 jbd2_revoke_table_s 260 512 16 256 1 : tunables 0 0 0 : slabdata 2 2 0 jbd2_revoke_record_s1152 1408 32 128 1 : tunables 0 0 0 : slabdata 11 11 0 ext4_inode_cache 208751 210840 1072 30 8 : tunables 0 0 0 : slabdata 7028 7028 0 ext4_free_data 320 448 64 64 1 : tunables 0 0 0 : slabdata 7 7 0 ext4_allocation_cont 128 128 128 32 1 : tunables 0 0 0 : slabdata 4 4 0 ext4_io_end 392 560 72 56 1 : tunables 0 0 0 : slabdata 10 10 0 ext4_extent_status 64412 77928 40 102 1 : tunables 0 0 0 : slabdata 764 764 0 dquot 144 160 256 16 1 : tunables 0 0 0 : slabdata 10 10 0 mbcache 226 292 56 73 1 : tunables 0 0 0 : slabdata 4 4 0 dio 273 350 640 25 4 : tunables 0 0 0 : slabdata 14 14 0 pid_namespace 42 42 2224 14 8 : tunables 0 0 0 : slabdata 3 3 0 ip4-frags 32 64 248 16 1 : tunables 0 0 0 : slabdata 4 4 0 RAW 396 396 896 18 4 : tunables 0 0 0 : slabdata 22 22 0 UDP 68 68 960 17 4 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCP 10750 11136 280 29 2 : tunables 0 0 0 : slabdata 384 384 0 request_sock_TCP 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 TCP 119 136 1920 17 8 : tunables 0 0 0 : slabdata 8 8 0 blkdev_queue 27 48 1336 24 8 : tunables 0 0 0 : slabdata 2 2 0 blkdev_requests 394 506 368 22 2 : tunables 0 0 0 : slabdata 23 23 0 blkdev_ioc 516 546 104 39 1 : tunables 0 0 0 : slabdata 14 14 0 user_namespace 104 104 304 26 2 : tunables 0 0 0 : slabdata 4 4 0 dmaengine-unmap-256 15 15 2112 15 8 : tunables 0 0 0 : slabdata 1 1 0 sock_inode_cache 1707 1950 640 25 4 : tunables 0 0 0 : slabdata 78 78 0 file_lock_cache 665 665 208 19 1 : tunables 0 0 0 : slabdata 35 35 0 net_namespace 40 40 7296 4 8 : tunables 0 0 0 : slabdata 10 10 0 shmem_inode_cache 3315 3432 656 24 4 : tunables 0 0 0 : slabdata 143 143 0 taskstats 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 proc_inode_cache 6895 7072 624 26 4 : tunables 0 0 0 : slabdata 272 272 0 sigqueue 100 100 160 25 1 : tunables 0 0 0 : slabdata 4 4 0 bdev_cache 29 76 832 19 4 : tunables 0 0 0 : slabdata 4 4 0 kernfs_node_cache 43625 44982 120 34 1 : tunables 0 0 0 : slabdata 1323 1323 0 mnt_cache 518 546 384 21 2 : tunables 0 0 0 : slabdata 26 26 0 inode_cache 17519 17668 568 28 4 : tunables 0 0 0 : slabdata 631 631 0 dentry 424185 439992 192 21 1 : tunables 0 0 0 : slabdata 20952 20952 0 buffer_head 1112865 1112865 104 39 1 : tunables 0 0 0 : slabdata 28535 28535 0 vm_area_struct 53945 55300 200 20 1 : tunables 0 0 0 : slabdata 2765 2765 0 files_cache 260 299 704 23 4 : tunables 0 0 0 : slabdata 13 13 0 signal_cache 509 630 1088 30 8 : tunables 0 0 0 : slabdata 21 21 0 sighand_cache 346 405 2112 15 8 : tunables 0 0 0 : slabdata 27 27 0 task_struct 1189 1269 3584 9 8 : tunables 0 0 0 : slabdata 141 141 0 Acpi-Operand 5703 5824 72 56 1 : tunables 0 0 0 : slabdata 104 104 0 Acpi-Parse 1314 1314 56 73 1 : tunables 0 0 0 : slabdata 18 18 0 Acpi-State 204 204 80 51 1 : tunables 0 0 0 : slabdata 4 4 0 Acpi-Namespace 4077 4182 40 102 1 : tunables 0 0 0 : slabdata 41 41 0 anon_vma 19831 21522 80 51 1 : tunables 0 0 0 : slabdata 422 422 0 numa_policy 170 170 24 170 1 : tunables 0 0 0 : slabdata 1 1 0 radix_tree_node 321937 327740 584 28 4 : tunables 0 0 0 : slabdata 11705 11705 0 trace_event_file 3985 4002 88 46 1 : tunables 0 0 0 : slabdata 87 87 0 ftrace_event_field 86541 88570 48 85 1 : tunables 0 0 0 : slabdata 1042 1042 0 idr_layer_cache 533 555 2096 15 8 : tunables 0 0 0 : slabdata 37 37 0 kmalloc-8192 1246 1246 8192 4 8 : tunables 0 0 0 : slabdata 502 502 0 kmalloc-4096 658 720 4096 8 8 : tunables 0 0 0 : slabdata 90 90 0 kmalloc-2048 1955 2144 2048 16 8 : tunables 0 0 0 : slabdata 134 134 0 kmalloc-1024 44217 44384 1024 16 4 : tunables 0 0 0 : slabdata 2774 2774 0 kmalloc-512 3037 3808 512 16 2 : tunables 0 0 0 : slabdata 238 238 0 kmalloc-256 17465 20384 256 16 1 : tunables 0 0 0 : slabdata 1274 1274 0 kmalloc-192 27708 28665 192 21 1 : tunables 0 0 0 : slabdata 1365 1365 0 kmalloc-128 140581 143744 128 32 1 : tunables 0 0 0 : slabdata 4492 4492 0 kmalloc-96 168044 168378 96 42 1 : tunables 0 0 0 : slabdata 4009 4009 0 kmalloc-64 117533 123264 64 64 1 : tunables 0 0 0 : slabdata 1926 1926 0 kmalloc-32 80425 90368 32 128 1 : tunables 0 0 0 : slabdata 706 706 0 kmalloc-16 9513 11264 16 256 1 : tunables 0 0 0 : slabdata 44 44 0 kmalloc-8 6616 7168 8 512 1 : tunables 0 0 0 : slabdata 14 14 0 kmem_cache_node 320 320 64 64 1 : tunables 0 0 0 : slabdata 5 5 0 kmem_cache 208 208 256 16 1 : tunables 0 0 0 : slabdata 13 13 0

If you want to see the most frequently used (hottest) Slabs you can see them top-like with slabtop. If you press c you can sort the Slabs by CACHE_SIZE:

# sudo slabtop Active / Total Objects (% used) : 2249113 / 2280136 (98.6%) Active / Total Slabs (% used) : 70256 / 70256 (100.0%) Active / Total Caches (% used) : 86 / 121 (71.1%) Active / Total Size (% used) : 597547.86K / 605445.30K (98.7%) Minimum / Average / Maximum Object : 0.01K / 0.26K / 18.56K OBJS ACTIVE USE OBJ SIZE SLABS OBJ/SLAB CACHE SIZE NAME 294308 289889 98% 0.57K 10511 28 168176K radix_tree_node 105030 104435 99% 1.05K 3501 30 112032K ext4_inode_cache 745446 745446 100% 0.10K 19114 39 76456K buffer_head 59984 59909 99% 1.00K 3749 16 59984K ecryptfs_inode_cache 47520 47157 99% 1.00K 2970 16 47520K kmalloc-1024 215166 214987 99% 0.19K 10246 21 40984K dentry 139744 138452 99% 0.12K 4367 32 17468K kmalloc-128 179508 179011 99% 0.09K 4274 42 17096K kmalloc-96 47140 45768 97% 0.20K 2357 20 9428K vm_area_struct 14700 14700 100% 0.55K 525 28 8400K inode_cache ...
Literature Taxonomy upgrade extras: memoryRAMcachefile systemo_directinnodb_flush_methodpostgresqlinnodbtablespace

Enable General Query Log per Connection in MariaDB

Shinguz - Wed, 2019-07-10 20:27

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:

bin/mysqld, Version: 10.4.6-MariaDB-log (MariaDB Server). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysql.sock Time Id Command Argument 190709 21:27:30 3872 Connect root@localhost as anonymous on test 3872 Query INSERT INTO test VALUES (NULL, CONCAT('Test data insert from laptop5 on ', @@hostname), CURRENT_TIMESTAMP()) 3872 Quit

The General Query Log can be enabled dynamically with the following command:

SQL> SET GLOBAL general_log = 1;

and is written either to a file (defined with general_log_file) or a table (mysql.general_log, defined with log_output=TABLE).

But unfortunately the General Query Log logs all Commands from all Connections. So it can grow quickly and you loose overview over what happens...

With the MariaDB Server System Variables sql_log_off you can configure to NOT log a Connection to the General Query Log. This is the opposite of what we want to have. But with a clever combination of these variables we can manage how to log only specific Connections to the General Query Log.

Preparation for Connection Logging to the General Query Log

First of all we have to enable the General Query Log but disable it for all Connections so nothing is written to the General Query Log:

SQL> SET GLOBAL sql_log_off = on; -- 1 = no logging SQL> SET SESSION sql_log_off = on; -- also for my current session! SQL> SET GLOBAL general_log = 1; -- 1 = enable general log file

Then we need a place where to specify and control which user from which host should be logged. So we can control General Query Log logging dynamically. For this purpose we create a table called general_log_settings in the sys Schema of the MariaDB sys Schema:

SQL> CREATE SCHEMA IF NOT EXISTS `sys`; SQL> use `sys`; SQL> DROP TABLE IF EXISTS `general_log_settings`; SQL> CREATE TABLE `general_log_settings` ( `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '%', `USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '%', `ENABLED` enum('YES','NO') NOT NULL DEFAULT 'YES' ) ENGINE=Aria DEFAULT CHARSET=utf8; SQL> INSERT INTO `general_log_settings` (`HOST`, `USER`, `ENABLED`) VALUES ('%', '%', 'NO');

Now we need a mechanism to enable General Query Log logging for every user. For this we use a Stored Procedure:

SQL> use `sys`; SQL> DROP PROCEDURE IF EXISTS `enable_general_log`; SQL> DELIMITER // SQL> CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `enable_general_log`() DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN DECLARE user VARCHAR(32); DECLARE host VARCHAR(60); DECLARE cnt SMALLINT; SELECT SUBSTRING_INDEX(SESSION_USER(), '@', 1) INTO user; SELECT SUBSTRING_INDEX(SESSION_USER(), '@', -1) INTO host; SELECT COUNT(*) INTO cnt FROM `general_log_settings` WHERE `ENABLED` = 'YES' AND (`HOST` = host OR `HOST` = '%') AND (`USER`= user OR `USER`= '%') ; IF cnt > 0 THEN SET SESSION sql_log_off = off; DO 'Connect'; END IF; END; // SQL> DELIMITER ;

Then we have to grant to ALL! users without the SUPER privilege the privilege to execute this procedure. These users can be found with the following query:

SQL> SELECT user, host, super_priv FROM mysql.user WHERE super_priv != 'Y'; +------+-----------+------------+ | User | Host | Super_priv | +------+-----------+------------+ | oli | localhost | N | | app | % | N | +------+-----------+------------+

To grant the privilege to execute the procedure you must run the queries as follows:

SQL> GRANT EXECUTE ON PROCEDURE `sys`.`enable_general_log` TO 'oli'@'localhost'; SQL> GRANT EXECUTE ON PROCEDURE `sys`.`enable_general_log` TO 'app'@'%';

Caution: If you do NOT grant the execute privilege to all users their new connections will fail as soon as you have enabled the logon trigger!

Then you have to add the users you want to log to the General Query Log to the general_log_settings table:

SQL> INSERT INTO `sys`.`general_log_settings` VALUES ('%', 'app', 'YES'); SQL> SELECT * FROM `sys`.`general_log_settings`; +------+------+---------+ | HOST | USER | ENABLED | +------+------+---------+ | % | % | NO | | % | app | YES | +------+------+---------+

You can test the logging to the General Query Log in your Connection with the following command now:

SQL> CALL `sys`.`enable_general_log`();

If everything is done correctly you should see some entries in your General Query Log as follows:

190709 23:51:21 6695 Query DO 'Connect' 6695 Query select count(*) from test.test 6695 Quit

And you should notice that the Connect Command is missing (see above). This is because enabling the General Query Log happens after the Connect. To give an equivalent entry tag in the General Query Log we have added the DO 'Connect' tag in the Stored Procedure.

Up to this point here all actions should not have any impact (neigther operational nor performance wise) to your MariaDB database system yet.

Activating General Query Log per Connection

Now we want to activate General Query Log logging for every new Connection to your MariaDB database server. This step can have an impact on your MariaDB database system and you should know how an be prepared to revert this step:

SQL> SET GLOBAL init_connect = 'CALL `sys`.`enable_general_log`();';

The init_connect Server System Variable specifies a command or a list of commands that will be executed for each new client connection. We also call this a Logon Trigger. These statements are only executed for users without the SUPER privilege. So you cannot track admin accounts with this method.

Further readings

Please let us know if you think we should add this tool to the MariaDB sys Schema or not.

Taxonomy upgrade extras: general query logconnectionlogon trigger

MariaDB sys Schema

Shinguz - Thu, 2019-06-27 12:25

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.

The sys Schema is: "A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage."

Unfortunately the sys Schema was not ported from MySQL to MariaDB by the company MariaDB Corp. so far. So a good opportunity to act as a community...

In the beginning the sys Schema was hosted on GitHub: mysql/mysql-sys. So we could take the sys Schema from there for MariaDB. But since 3 years ago no more changes appeared on GitHub. It seems like the project was abandoned.

According to a well informed source the project on GitHub is not dead but the developers have just been working on other priorities. And the source announced another release soon (they are working on it at the moment). This statement was made in March 2018 and since then no progress happened.

We can further see that in the meanwhile the MySQL sys Schema has changed from version 1.6.0 to 2.0.0 without any changes on GitHub. So we think the project is really abandoned on GitHub and declared dead.

Because we had in the past many discussions with customers about the sys Schema for MariaDB, FromDual decided to fork the MySQL sys Schema project.

Yesterday we have forked the MySQL sys Schema to the MariaDB sys Schema. And over time it is our intention to port all possible changes to MariaDB sys Schema.

You can find our actual work on GitHub at: FromDual/mariadb-sys.

The current version contains a working fork of MySQL 5.6 sys Schema / v1.5.1. To make it work we had to fix a few little things but now the MariaDB sys Schema is working for MariaDB from version 10.0 up to MariaDB version 10.4.

Community: Please try out MariaDB sys Schema, find bugs, describe how to provoke the problem and report those bugs on GitHub and ideally also provide some fixes...

Our first experiments with MariaDB sys Schema in March 2018 is described here: MySQL sys Schema in MariaDB 10.2.

If you need any MariaDB related help please contact our MariaDB consulting team...

Taxonomy upgrade extras: mariadbsysperformance_schema

Oops! - That SQL Query was not intended... Flashback

Shinguz - Mon, 2019-06-24 14:12

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!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6'; Query OK, 4686 rows affected (0.21 sec) Rows matched: 5666 Changed: 4686 Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; +--------------------------------------+----------+ | prospect_list_id | count(*) | +--------------------------------------+----------+ | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 5666 | +--------------------------------------+----------+

And certainly I did not enter the START TRANSACTION; command before. So no ROLLBACK!

Next look at the backup:

# ll backup/daily/bck_schema_crm_2019-06* -rw-rw-r-- 1 mysql mysql 7900060 Jun 1 02:13 backup/daily/bck_schema_crm_2019-06-01_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900061 Jun 2 02:13 backup/daily/bck_schema_crm_2019-06-02_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7900091 Jun 3 02:13 backup/daily/bck_schema_crm_2019-06-03_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903126 Jun 4 02:13 backup/daily/bck_schema_crm_2019-06-04_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7903192 Jun 5 02:13 backup/daily/bck_schema_crm_2019-06-05_02-13-02.sql.gz -rw-rw-r-- 1 mysql mysql 7903128 Jun 6 02:13 backup/daily/bck_schema_crm_2019-06-06_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7912886 Jun 21 02:13 backup/daily/bck_schema_crm_2019-06-21_02-13-01.sql.gz -rw-rw-r-- 1 mysql mysql 7920566 Jun 22 02:13 backup/daily/bck_schema_crm_2019-06-22_02-13-01.sql.gz

Yes! Backup is there and was done with the FromDual Backup Manager. So I am confident Restore and Point-in-Time-Recovery will work... But the Point-in-Time-Recovery with the Binary Logs for just one schema is a bit tricky and officially not so really supported.

So basically what I want to do is just to undo this UPDATE command. But unfortunately this UPDATE was not a reversible UPDATE command. Then I remembered a presentation about MariaDB 10.2 New Features (p. 41) where the speaker was talking about the flashback functionality in the mysqlbinlog utility.

Undo MySQL Binary Log Events with MariaDB mysqlbinlog utility

First of all I analysed the MySQL Binary Log to find the Binary Log Events to undo:

# mysqlbinlog --start-position=348622898 --verbose mysql-bin.000080 | less # at 348622898 #190622 23:19:43 server id 7 end_log_pos 348622969 CRC32 0xd358d264 Query thread_id=791264 exec_time=0 error_code=0 SET TIMESTAMP=1561238383/*!*/; BEGIN /*!*/; # at 348622969 #190622 23:19:43 server id 7 end_log_pos 348623049 CRC32 0x71340183 Table_map: `crm`.`prospect_lists_prospects` mapped to number 2857 # at 348623049 #190622 23:19:43 server id 7 end_log_pos 348631021 CRC32 0x53d65c9b Update_rows: table id 2857 ... ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 # at 349828089 #190622 23:19:43 server id 7 end_log_pos 349828120 CRC32 0x83f41493 Xid = 8361402 COMMIT/*!*/;

So the relevant part in the MySQL Binary Log is between position 348622898 and 349828120.

Now let us try the reverse operation. But for this we have to solve a little problem. The database is a MySQL 5.7. But the feature --flashback is only available in MariaDB 10.2 and newer. So we have to bring either the MySQL 5.7 Binary Logs to the MariaDB mysqlbinlog utility or the MariaDB mysqlbinlog utility to the MySQL 5.7 Binary Logs.

For a first attempt I moved the MySQL 5.7 Binary Logs to a MariaDB 10.3 testing system and gave it a try if mixing Binary Logs and Utility is working at all:

# mysqlbinlog --start-position=348622898 --stop-position=349828120 -v mysql-bin.000080 | grep -c 'UPDATE `crm`.`prospect_lists_prospects`' 4686

Looks good! Exactly the number of Row changes expected. Then let us look at the statement with --flashback:

# mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 -v | less '/*!*/; ### UPDATE `crm`.`prospect_lists_prospects` ### WHERE ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='73ae6cca-7b34-c4a3-5500-5d0e2674dbb6' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0 ### SET ### @1='ff700497-41cc-e530-a690-5d0e606cd942' ### @2='b851169d-5e94-5c43-3593-5d0e2825d848' ### @3='2078d1ae-f7b4-a082-38a5-5d0e581584fc' ### @4='Prospects' ### @5='2019-06-22 17:07:41' ### @6=0

Looks good! Seems to be the reverse query. And now let us do the final repair job:

# /home/mysql/product/mariadb-10.3/mysqlbinlog --flashback --start-position=348622898 --stop-position=349828120 mysql-bin.000080 \ | /home/mysql/product/mysql-5.7/bin/mysql --user=root --port=3320 --host=127.0.0.1 crm --force ERROR 1193 (HY000) at line 21339: Unknown system variable 'check_constraint_checks'

The --force option was used to motivate mysql utility to continue even if an error occurs. Which was the case in our scenario. This option should usually not be used. We had tried out this step before on a testing system so I was aware what is happening and why this error occurs...

Now the final test on the repaired system shows the situation as it was before the accident:

SQL> SELECT IFNULL(prospect_list_id, 'Total:'), COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id WITH ROLLUP; +--------------------------------------+----------+ | IFNULL(prospect_list_id, 'Total:') | count(*) | +--------------------------------------+----------+ | 1178ec2b-6aa9-43e4-a27e-5d0e264cac4c | 91 | | 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | 946 | | 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | 5 | | 21d9a74f-73af-9a5d-84ba-5d0e280772ef | 107 | | 37230208-a431-f6d8-a428-5d0e28d9ec77 | 264 | | 4b48da8a-33d9-4896-5000-5d0e287ffe39 | 3 | | 5d06f6cc-3fe9-f501-b680-5d0ccfd19033 | 2 | | 5e39a569-3213-cc64-496f-5d0e28e851c9 | 5 | | 680a879c-ff3c-b955-c3b8-5d0e28c833c5 | 315 | | 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 | 980 | | 756c4803-dc73-dc09-b301-5d0e28e69546 | 2 | | 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | 833 | | 913861f0-a865-7c94-8109-5d0e28d714b6 | 12 | | 96a10d6a-c10e-c945-eaeb-5d0e280aa16c | 74 | | a43147a8-90f2-a5b3-5bcf-5d0e2862248a | 15 | | ae869fb1-dd88-19c0-b0d6-538f7b7e329a | 20 | | b57eb9ba-5a93-8570-5914-5d0e28d975a9 | 25 | | b851169d-5e94-5c43-3593-5d0e2825d848 | 978 | | be320e31-1a5b-fe86-09d7-5d0e28a0fd2e | 7 | | c762abde-bc63-2383-ba30-5d0e28a714c9 | 160 | | cbbd0ba7-dc25-f29f-36f4-5d0e287c3006 | 99 | | d23490c8-99eb-f298-6aad-5d0e28e7fd4f | 52 | | d5000593-836c-3679-ecb5-5d0e28dd076c | 57 | | d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | 421 | | df768570-f9b8-2333-66c4-5a6768e34ed3 | 3 | | e155d58a-19e8-5163-f846-5d0e282ba4b8 | 66 | | f139b6a0-9598-0cd4-a204-5d0e28c2eccd | 120 | | f165c48b-4fc1-b081-eee3-5d0cdd7947d5 | 4 | | Total: | 5666 | +--------------------------------------+----------+

Flashback of MySQL 5.7 Binary Logs with MariaDB 10.3 mysqlbinlog utility was successful!

If you want to learn more about Backup and Recovery strategies contact our MariaDB/MySQL consulting team or book one of our MariaDB/MySQL training classes.

Taxonomy upgrade extras: undoBackupbinary logmysqlbinlogflashbackRestorepitrRecoverypoint-in-time-recovery

Do not underestimate performance impacts of swapping on NUMA database systems

Shinguz - Fri, 2019-06-21 09:26

If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

# free total used free shared buff/cache available Mem: 16106252 3300424 697284 264232 12108544 12011972 Swap: 31250428 1701792 29548636

With the command:

# swapon --show NAME TYPE SIZE USED PRIO /dev/sdb2 partition 29.8G 1.6G -1

you can show on which disk drive your Swap Space is physically located. And with the following 3 commands you can find if your system is currently swapping or not:

# vmstat 1 procs ------------memory------------ ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 1701784 692580 355716 11757864 2 12 685 601 237 146 9 3 86 2 0 0 0 1701784 692472 355720 11757840 0 0 0 196 679 2350 2 1 97 1 0 0 0 1701784 692720 355728 11757332 0 0 0 104 606 2170 0 1 98 1 0 # sar -W 1 15:44:30 pswpin/s pswpout/s 15:44:31 0.00 0.00 15:44:32 0.00 0.00 15:44:33 0.00 0.00 # sar -S 1 15:43:02 kbswpfree kbswpused %swpused kbswpcad %swpcad 15:43:03 29548648 1701780 5.45 41552 2.44 15:43:04 29548648 1701780 5.45 41552 2.44 15:43:05 29548648 1701780 5.45 41552 2.44

Side note: Recent Linux distributions tend to use Swap Files instead of Swap Partitions. The performance impact seems to be negligible compared to the operational advantages of Swap Files... [ 1 ] [ 2 ] [ 3 ] [ 4 ]

What is Swap Space on a Linux system

Modern Operating Systems like Linux manage Virtual Memory (VM) which consists of RAM (fast) and Disk (HDD very slow and SSD slow). If the Operating System is short in fast RAM it tries to write some "old" pages to slow disk to get more free fast RAM for "new" pages and/or for the file system cache. This technique enables the Operating System to keep more and/or bigger processes running than physical RAM is available (overcommitment of RAM).
If one of those "old" pages is needed again it has to be swapped in which technically is a physical random disk read (which is slow, this is also called a major page fault).
If this block is a MariaDB or MySQL database block this disk read to RAM will slow down your SELECT queries but also INSERT, UPDATE and DELETE when you do write queries. This can severely slow down for example your clean-up jobs which have to remove "old" data (located on disk possibly in Swap Space).

Sizing of Swap Space for database systems

A rule of thumb for Swap Space is: Have always Swap Space but never use it (disk is cheap nowadays)!

A reasonable Swap Space sizing for database systems is the following:

Amount of RAMSwap Space4 GiB of RAM or lessa minimum of 4 GiB of Swap Space, is this really a Database server?8 GiB to 16 GiB of RAMa minimum of once the amount of RAM of Swap Space24 GiB to 64 GiB of RAMa minimum of half the amount of RAM of Swap Spacemore than 64 GiB of RAMa minimum of 32 GiB of Swap Space

If you have a close look at your Swap usage and if you monitor your Swap Space precisely and if you know exactly what you are doing you can lower these values...

It is NOT recommended to disable Swap Space

Some people tend to disable Swap Space. We see this mainly in virtualized environments (virtual machines) and cloud servers. From the VM/Cloud administrator point of view I can even understand why they disable Swap. But from the MariaDB / MySQL DBA point of view this is a bad idea.

If you do proper MariaDB / MySQL configuration (innodb_buffer_pool_size = 75% of RAM) the server should not swap a lot. But if you exaggerate with memory configuration the system starts swapping heavily. Till to the end the OOM-Killer will be activated by your Linux killing the troublemaker (typically the database process). If you have sufficient Swap Space enabled you get some time to detect a bad database configuration and act accordingly. If you have Swap Space disabled completely you do not get this safety buffer and OOM killer will act immediately and kill your database process when you run out of RAM. This really cannot be in the interest of the DBA.

Some literature to read further about Swap: In defence of swap: common misconceptions

Influence swapping - Swappiness

The Linux kernel documentation tells us the following about swappiness:

swappiness

This control is used to define how aggressive the kernel will swap memory pages. Higher values will increase aggressiveness, lower values decrease the amount of swap. A value of 0 instructs the kernel not to initiate swap until the amount of free and file-backed pages is less than the high water mark in a zone.

The default value is 60.

Source: Documentation for /proc/sys/vm/*

A informative article on StackExchange: Why is swappiness set to 60 by default?

To change your swappiness the following commands will help:

# sysctl vm.swappiness vm.swappiness = 60 # sysctl vm.swappiness=1 # sysctl vm.swappiness vm.swappiness = 1

To make these changes persistent you have to write it to some kind of configuration file dependent on your Operating System:

# # /etc/sysctl.d/99-sysctl.conf # vm.swappiness=1
Who is using the Swap Space?

For further analysing your Swap Space and to find who is using your Swap Space please see our article MariaDB and MySQL swap analysis.

What if your system is still swapping? - NUMA!

If you did everything correctly until here and your system is still swapping you possibly missed one point: NUMA systems behave a bit tricky related to Databases and swapping. The first person who wrote extensively about this problem in the MySQL ecosystem was Jeremy Cole in 2010 in his two well written articles which you can find here:

What NUMA is you can find here: Non-uniform memory access.

If you have spent your money for an expensive NUMA system you can find with the following command:

# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 56 On-line CPU(s) list: 0-55 Thread(s) per core: 2 Core(s) per socket: 14 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz Stepping: 1 CPU MHz: 2600.000 CPU max MHz: 2600.0000 CPU min MHz: 1200.0000 BogoMIPS: 5201.37 Virtualization: VT-x Hypervisor vendor: vertical Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 35840K NUMA node0 CPU(s): 0-13,28-41 NUMA node1 CPU(s): 14-27,42-55

If you are now in the unfortunate situation of having such a huge box with several sockets you can do different things:

  • Configuring your MariaDB / MySQL database to allocate memory evenly on both sockets with the parameter innodb_numa_interleave. This works since MySQL 5.6.27, MySQL 5.7.9 and MariaDB 10.2.4 but there were various bugs in this area in Debian and CentOS packages (e.g. #80288, #78953, #79354 and MDEV-18660).
  • Disable NUMA support in your BIOS (Node Interleaving = enabled). Then there is no NUMA presentation to the Operating System any more.
  • Start your MariaDB / MySQL database with numactl --interleave all as described here: MySQL and NUMA.
  • Set innodb_buffer_pool_size to 75% of half of your RAM. Sad for having too much of RAM.
  • Playing around with the following Linux settings could help to decrease swapping: vm.zone_reclaim_mode=0 and kernel.numa_balancing=0.
Literature

Some further information about Swap Space you can find here:

Taxonomy upgrade extras: swapnumaperformancedatabase

Dropped Tables with FromDual Backup Manager

Shinguz - Fri, 2019-05-24 07:23

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1 to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253). mysqldump: [Warning] Using a password on the command line interface can be insecure. Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 () mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

  • If the table is only temporary create it with the CREATE command as a TEMPORARY TABLE instead of a normal table. This workaround would not work in this case because the table is a caching table which must be available for other connections as well.
  • Try to schedule your application job or your bman job in the way they do not collide. With bman that is quite easy but sometimes not with the application.
  • Try to create the table in its own schema (e.g. cache) which is excluded from bman backup. So you can easily do a bman backup without the cache schema. For example like this: $ bman --target=brman@127.0.0.1:3306 --type=schema --schema=-cache --policy=daily
  • If this strategy also does not work (because you cannot change the application behaviour) try to ignore the table. The underlying command mysqldump knows the option --ignore-table: mysqldump --help ... --ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., --ignore-table=database.table.
    This option can be used in bman as well. Options to the underlying application are passed through FromDual Backup Manager as follows: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--ignore-table=totara.m_report_builder_cache_157_20190521035354'
  • The problem here is, that this table contains a timestamp in its table name (20190521035354). So the table name is changing all the time. To pass through wildcards with --ignore-table is not possible with mysqldump. The tool mysqldump does not support (yet) this feature. The only solution we have in this case is, to ignore the error message with the risk that possible other error messages are also ignored. This is achieved again with the --pass-through option: $ bman --target=brman@127.0.0.1:3306 --type=full --policy=daily --pass-through='--force'

I hope with this few tricks we can help you to make your FromDual Backup Manager (bman) backups hassle-free.

Taxonomy upgrade extras: Backuptablebmandroperror

FromDual Ops Center for MariaDB and MySQL 0.9.1 has been released

Shinguz - Tue, 2019-05-07 17:12

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

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage 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.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.1

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 or 0.9.0 to 0.9.1

Upgrade from 0.3 or 0.9.0 to 0.9.1 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.1 Upgrade
  • Sever upgrade bug fixed which prohibited installation of v0.9.0.
Build and Packaging
  • RPM package for RHEL/CentOS 7 is available now.
  • DEB package for Ubuntu 18.04 LTS is available now.
  • SElinux Policy Package file added.
  • COMMIT tag was not replaced correctly during build. This is fixed now.
Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

FromDual Ops Center for MariaDB and MySQL 0.9 has been released

Shinguz - Tue, 2019-04-30 09:17

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

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage 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.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 to 0.9

Upgrade from 0.3 to 0.9 should happen automatically. Please do a backup of you Ops Center Instance befor you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9

Everything has changed!

Taxonomy upgrade extras: OperationsreleaseBackupfailoverRestore

MariaDB Prepared Statements, Transactions and Multi-Row Inserts

Shinguz - Mon, 2019-04-15 18:09

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli'; SQL> SET @d2 = 'Bla'; SQL> SET @d3 = 'Blub'; SQL> EXECUTE stmt1 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.010 sec) Records: 3 Duplicates: 0 Warnings: 0 SQL> DEALLOCATE PREPARE stmt1; SQL> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Bli | 2019-04-15 17:26:22 | | 2 | Bla | 2019-04-15 17:26:22 | | 3 | Blub | 2019-04-15 17:26:22 | +----+------+---------------------+
Prepared Statements and Transactions SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt2 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?)'; Statement prepared SQL> SET @d1 = 'BliTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> SET @d1 = 'BlaTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'BlubTrx'; SQL> EXECUTE stmt2 USING @d1; Query OK, 1 row affected (0.000 sec) SQL> ROLLBACK; SQL> DEALLOCATE PREPARE stmt2; SQL> SELECT * FROM test; +----+---------+---------------------+ | id | data | ts | +----+---------+---------------------+ | 10 | BliTrx | 2019-04-15 17:33:30 | | 11 | BlaTrx | 2019-04-15 17:33:39 | +----+---------+---------------------+
Prepared Statements and Transactions and Multi-Row Inserts SQL> SET SESSION autocommit=Off; SQL> START TRANSACTION; SQL> PREPARE stmt3 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)'; Statement prepared SQL> SET @d1 = 'Bli1Trx'; SQL> SET @d2 = 'Bla1Trx'; SQL> SET @d3 = 'Blub1Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> COMMIT; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli2Trx'; SQL> SET @d2 = 'Bla2Trx'; SQL> SET @d3 = 'Blub2Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.000 sec) SQL> ROLLBACK; -- Theoretically we should do a START TRANSACTION; here again... SQL> SET @d1 = 'Bli3Trx'; SQL> SET @d2 = 'Bla3Trx'; SQL> SET @d3 = 'Blub3Trx'; SQL> EXECUTE stmt3 USING @d1, @d2, @d3; Query OK, 3 rows affected (0.001 sec) SQL> COMMIT; SQL> DEALLOCATE PREPARE stmt3; SQL> SELECT * FROM test; +----+----------+---------------------+ | id | data | ts | +----+----------+---------------------+ | 1 | Bli1Trx | 2019-04-15 17:37:50 | | 2 | Bla1Trx | 2019-04-15 17:37:50 | | 3 | Blub1Trx | 2019-04-15 17:37:50 | | 7 | Bli3Trx | 2019-04-15 17:38:38 | | 8 | Bla3Trx | 2019-04-15 17:38:38 | | 9 | Blub3Trx | 2019-04-15 17:38:38 | +----+----------+---------------------+

Seems all to work as expected. Now we know it for sure!

Taxonomy upgrade extras: transactioninsertprepared statementsmulti-row insert

Uptime of a MariaDB Galera Cluster

Shinguz - Fri, 2019-03-15 17:58

A while ago somebody on Google Groups asked for the Uptime of a Galera Cluster. The answer is easy... Wait, no! Not so easy... The uptime of a Galera Node is easy (or not?). But Uptime of the whole Galera Cluster?

My answer then was: "Grep the error log." My answer now is still: "Grep the error log." But slightly different:

$ grep 'view(view_id' * 2019-03-07 16:10:26 [Note] WSREP: view(view_id(PRIM,0e0a2851,1) memb { 2019-03-07 16:14:37 [Note] WSREP: view(view_id(PRIM,0e0a2851,2) memb { 2019-03-07 16:16:23 [Note] WSREP: view(view_id(PRIM,0e0a2851,3) memb { 2019-03-07 16:55:56 [Note] WSREP: view(view_id(NON_PRIM,0e0a2851,3) memb { 2019-03-07 16:56:04 [Note] WSREP: view(view_id(PRIM,6d80bb1a,5) memb { 2019-03-07 17:00:28 [Note] WSREP: view(view_id(NON_PRIM,6d80bb1a,5) memb { 2019-03-07 17:01:11 [Note] WSREP: view(view_id(PRIM,24f67954,7) memb { 2019-03-07 17:18:58 [Note] WSREP: view(view_id(NON_PRIM,24f67954,7) memb { 2019-03-07 17:19:31 [Note] WSREP: view(view_id(PRIM,a380c8cb,9) memb { 2019-03-07 17:20:27 [Note] WSREP: view(view_id(PRIM,a380c8cb,11) memb { 2019-03-08 7:58:38 [Note] WSREP: view(view_id(PRIM,753a350f,15) memb { 2019-03-08 11:31:38 [Note] WSREP: view(view_id(NON_PRIM,753a350f,15) memb { 2019-03-08 11:31:43 [Note] WSREP: view(view_id(PRIM,489e3c67,17) memb { 2019-03-08 11:31:58 [Note] WSREP: view(view_id(PRIM,489e3c67,18) memb { ... 2019-03-22 7:05:53 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,49) memb { 2019-03-22 7:05:53 [Note] WSREP: view(view_id(PRIM,49dc20da,50) memb { 2019-03-26 12:14:05 [Note] WSREP: view(view_id(NON_PRIM,49dc20da,50) memb { 2019-03-27 7:33:25 [Note] WSREP: view(view_id(NON_PRIM,22ae25aa,1) memb {

So this Cluster had an Uptime of about 18 days and 20 hours. Why can I seed this? Simple: In the brackets there is a number at the very right. This number seems to be the same as wsrep_cluster_conf_id which is reset by a full Galera Cluster shutdown.

So far so good. But, wait, what is the definition of Uptime? Hmmm, not so helpful, how should I interpret this for a 3-Node Galera Cluster?

I would say a good definition for Uptime of a Galera Cluster would be: "At least one Galera Node must be available for the application for reading and writing." That means PRIM in the output above. And we still cannot say from the output above if there was at least on Galera Node available (reading and writing) at any time. For this we have to compare ALL 3 MariaDB Error Logs... So it does not help, we need a good Monitoring solution to answer this question...

PS: Who has found the little fake in this blog?

Taxonomy upgrade extras: galera clusteruptime

Linux system calls of MySQL process

Shinguz - Fri, 2019-03-15 17:06

We had the problem today that a MySQL Galera Cluster node with the multi-tenancy pattern caused a lot of system time (sy 75%, load average about 30 (you really must read this article by Brendan Gregg, it is worth it!)) so we wanted to find what system calls are being used to see what could cause this issue (to verify if it is a TOC or a TDC problem:

$ sudo strace -c -p $(pidof -s mysqld) -f -e trace=all Process 5171 attached with 41 threads Process 16697 attached ^C Process 5171 detached ... Process 5333 detached Process 16697 detached % time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 66.85 1.349700 746 1810 io_getevents 25.91 0.523055 1298 403 197 futex 4.45 0.089773 1069 84 22 read 2.58 0.052000 13000 4 3 restart_syscall 0.19 0.003802 1901 2 select 0.01 0.000235 3 69 1 setsockopt 0.01 0.000210 18 12 getdents 0.00 0.000078 2 32 write 0.00 0.000056 1 49 fcntl 0.00 0.000026 4 6 openat 0.00 0.000012 2 6 close 0.00 0.000000 0 2 2 open 0.00 0.000000 0 22 stat 0.00 0.000000 0 2 mmap 0.00 0.000000 0 7 mprotect 0.00 0.000000 0 16 pread 0.00 0.000000 0 1 access 0.00 0.000000 0 1 sched_yield 0.00 0.000000 0 5 madvise 0.00 0.000000 0 1 accept 0.00 0.000000 0 1 getsockname 0.00 0.000000 0 1 clone 0.00 0.000000 0 1 set_robust_list ------ ----------- ----------- --------- --------- ---------------- 100.00 2.018947 2537 225 total $ man io_getevents ...

See also: Configuration of MySQL for Shared Hosting.

Taxonomy upgrade extras: system calltable_open_cachetable_definition_cachesystem timeload average

MariaDB and MySQL Database Consolidation

Shinguz - Thu, 2019-03-14 23:05

We see at various customers the request for consolidating their MariaDB and MySQL infrastructure. The advantage of such a measure is clear in the first step: Saving costs! And this requests comes typically from managers. But what we unfortunately see rarely is to question this request from the IT engineering perspective. Because it comes, as anything in life, with some "costs". So, saving costs with consolidation on one side comes with "costs" for operation complexity on the other side.

To give you some arguments for arguing with managers we collected some topics to consider before consolidating:

  • Bigger Database Instances are more demanding in handling than smaller ones:
    • Backup and Restore time takes longer. Copying files around takes longer, etc.
    • Possibly your logical backup with mysqldump does not restore any longer in a reasonable amount of time (Mean Time to Repair/Recover (MTTR) is not met any more). You have to think about some physical backup methods including MariaDB or MySQL Enterprise Backup solutions.
    • Consolidated database instances typically contain many different schemas of various different applications. In case of problems you typically want to restore and possibly recover only one single schema and not all schemas. And this becomes much more complicated (depending on you backup strategy). MariaDB/MySQL tooling is not yet (fully) prepared for this situation (#17365). Possibly your old backup strategy is not adequate any more?
    • When you restore a schema you do not want the application interfering with your restore. How can you properly exclude the one application from your database instance while you are restoring? Locking accounts (possible only with MariaDB 10.4 and MySQL 5.7 and newer). Tricks like --skip-networking, adding Firewall rules, --read-only, database port change (--port=3307), do not work any more (as easy)!
    • In short the costs are: Restore/Recovery Operations becomes more demanding!
  • Do NOT mix schemas of different criticalities into the same database instance! The worst cases we have seen were some development schemas which were on the same high-availability Cluster like highly critical transactional systems. The developers did some nasty things on their development systems (which IMHO is OK for them on a development system). What nobody considered in this case was that the troubles from the development schema brought down the whole production schema which was located on the same machine... Cost: Risk of failure of your important services caused by some non-important services AND planing becomes more expensive and you need to know more about all instances and other instances.
  • This phenomena is also called Noisy Neighbor effect. Noisy Neighbors become a bigger issues with consolidated systems. You have to know much more in detail what you and everybody else is doing on the system! Do you...? Costs are: More know-how is required, better education and training of people, more clever people, better planning, better monitoring, etc.
  • When you consolidate different applications into one system it becomes more critical than the previous ones on their own. So you have to think about High-Availability solutions. Costs are: 1 to 4 new instances (for HA), more complexity, more know-how, more technologies... Do you plan to buy an Enterprise Support subscription?
  • Do NOT mix different maintenances windows (Asia vs. Europe vs. America) or daily online-business and nightly job processing. You get shorter maintenance windows. Costs are: Better planning is needed, costly night and weekend maintenance time, etc...

    Europe12:00China19:00(7 hours ahead of us)US east07:00(5 hours behind us)US west04:00(8 hours behind us)
  • Resource Fencing becomes more tricky. Within the same instance resource fencing becomes more tricky and is not really doable atm. MySQL 8.0 shows some firsts steps with the Resource Groups but this is pretty complicated and is by far not complete and usable yet. A better way would be to install several instances on the same machine an fence them with some O/S means like Control Groups. This comes at the costs of know-how, complexity and more complicated set-ups.
  • Naming conflicts can happen: Application a) is called `wiki` and application b) is called `wiki` as well and for some reasons you cannot rename them (any more).
  • Monitoring becomes much more demanding and needs to be done more fine grained. You want to know exactly what is going on your system because it can easily have some side effects on many different schemas/applications. Example of today: We were running out of kernel file descriptors (file-max) and we did not recognize it in the beginning.
  • Consolidated things are a much a higher Bulk Risk (this is true also for SAN or Virtualisation Clusters). When you have an outage not only one application is down but the whole company is down. We have seen this already for SAN and Virtualisation Clusters and we expect to see that soon also on highly consolidated Database Clusters. Costs: Damage on the company is bigger for one incident.
  • Different applications have different configuration requirements which possibly conflict with other requirements from other applications (Jira from Atlassian is a good example for this).
    Server variables cannot be adjusted any more according to somebody’s individual wishes...
    • sql_mode: Some old legacy applications still require ONLY_FULL_GROUP_BY) :-(
    • The requirements are conflicting: Performance/fast vs. Safe/durability: innodb_flush_log_at_trx_commit, sync_binlog, crash-safe binary logging, etc.
    • Transaction isolation: transaction_isolation = READ-COMMITTED (old: tx_isolation, Jira again as an example) vs. REPEATABLE-READ (default). Other applications which do not assume, that transaction isolation behaviour changes. And cannot cope with it. Have you ever asked your developers if their application can cope with a different transaction isolation levels? :-) Do they know what you are talking about?
    • Character set (utf8_bin for Jira as example again), which can be changed globally or on a schema level, but it has to be done correctly for all participants.
  • Some applications require MariaDB some application require MySQL. They are not the same databases any more nowadays (8.0 vs. 10.3/10.4). So you cannot consolidate them (easily).
  • You possibly get a mixture of persistent connections (typically Java with connection pooling) and non-persistent connections (typically PHP and other languages). Which causes different database behaviour, which has an impact on how you configure the database instance. Which is more demanding and needs more knowledge of the database AND the application or you solve it with more RAM.
  • You need to know much more about you application to understand what it does and how could it interfere with others...
  • When you consolidate more and more schemas into your consolidated database server you have to adjust your database setting as well from time to time (innodb_buffer_pool_size, table_open_cache, table_definition_cache, O/S File descriptors, etc). And possibly add more RAM, CPU and stronger I/O. When is your network saturated? Have you thought about this already?

This leads us to the result that consolidation let us save some costs on infrastructure but adds additional costs on complexity, skills etc. Theses costs will grow exponentially and thus at some point it is not worth the effort any more. This will end up in not only one big consolidated instance but possibly in a hand full of them.

Where this point is for you you have to find yourself...

Alternatives to consolidating everything into one instance
  • 1 Machine can contain 1 to many Database Instances can contain 1 to many Schemas. Instead of putting all schemas into one machine, think about installing several instances on one machine. This comes at the cost of more complexity. MyEnv will help you to manage this additional complexity.
  • 1 Machine can contain 1 to many Virtual Machines (VMs, kvm, XEN, VMWare, etc.) can contain 1 to many Instance(s) can contain 1 to many Schemas. This comes at the cost of even more complexity and pretty complex technology (Virtualization).

Taxonomy upgrade extras: consolidationcentralizationMyEnv

FromDual Performance Monitor for MariaDB and MySQL 1.0.2 has been released

Shinguz - Wed, 2019-03-13 20:58

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

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. 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 Manager for MariaDB and MySQL please report it 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.

Monitoring as a Service (MaaS)

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

Upgrade from 1.0.x to 1.0.2 shell> cd /opt shell> tar xf /download/fpmmm-1.0.2.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.0.2 fpmmm
Changes in FromDual Performance Monitor for MariaDB and MySQL 1.0.2

This release contains various bug fixes.

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

shell> fpmmm --version
fpmmm agent
  • Server entropy probe added.
  • Processlist empty state is covered.
  • Processlist statements made more robust.
  • Error caught properly after query.
  • Branch for Ubuntu is different, fixed.
  • PHP Variable variables_order is included into program.
  • Fixed the documentation URL in file INSTALL.
  • Connection was not set to utf8. This is fixed now.
  • fprint error fixed.
  • Library myEnv.inc updated from MyEnv project.

fpmmm Templates
  • Backup template added.
  • SQL thread and IO thread error more verbose and running again triggers implemented. Typo in slave template fixed.
  • Forks graph fixed, y axis starts from 0.

fpmmm agent installer
  • Error messages made more flexible.

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasrelease

Pages

Subscribe to FromDual Aggregator – MySQL Tech-Feed (en)