You are here

MySQL Tech-Feed (en)

FromDual Performance Monitor for MariaDB/MySQL allows SNMP monitoring

Shinguz - Thu, 2022-06-23 12:03

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

In this article we will show you the few simple steps it needs to send the data from your fpmmm Agent to your SNMP Manager.

Check if SNMP Manager can be reached

To make sure fpmmm can send data to the SNMP Manager at all you can test the connection with the following command to send SNMP Notifications to the SNMP Manager:

# COMMUNITY='public' # MANAGER='192.168.56.102' # PORT='162' # TRAP_OID='1.3.6.1.4.1.57800.1.1.2' # OID='1.3.6.1.4.1.57800.1.1.1' # TYPE='c' # VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) # snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

Then check in the SNMP Manager Logfile if the traps arrive.

Copy fpmmm MIBs to your fpmmm Agent Machine

You can see where your fpmmm MIBs are expected on your database machine (where your fpmmm Agent is located) with the following command:

# net-snmp-config --default-mibdirs ${HOME}/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

Copy the fpmmm MIBs as follows (we assume that you work with the same user as your fpmmm Agent works (zabbix or mysql)):

# mkdir -p ${HOME}/.snmp/mibs # cp /opt/fpmmm/tpl/FromDual-fpmmm-MIB.mib ${HOME}/.snmp/mibs

or choose any other location you want to have them.

To check if the FromDual MIBs are recognized correctly run these commands:

# snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun # snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

Do the same for the SNMP Manager according to your installation instructions of your SNMP Manager.

Configure your FromDual Performance Monitor to send SNMP traps

The fpmmm Configuation File (/etc/fpmmm.conf) should contain at least the following lines to make fpmmm sending SNMP traps instead of Zabbix messages:

[default] # SNMP Server SnmpServer = 192.168.56.102 SnmpCommunity = public SnmpTrapPort = 162 OutputFormat = snmp

Do a test run of fpmmm with:

# /opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf

Then check again on your SNMP Server machine if the SNMP traps arrive:

snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (1215419) 3:22:34.19#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.1.1.1 = Counter32: 3 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.2 = Counter32: 151 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.3 = Counter32: 1

This is already all about making fpmmm capable sending SNMP traps.

Sources
Taxonomy upgrade extras: performanceperformance monitoringmonitorfpmmmSNMP

FromDual Performance Monitor for MariaDB 2.0.0 has been released

Shinguz - Thu, 2022-05-19 20:09

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

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

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

Download

The new FromDual Performance Monitor for MariaDB (fpmmm) can be downloaded from here or you can use our FromDual repositories. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB please report it to the FromDual Bug-tracker or just send us an email.

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

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 2.0.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade of fpmmm tarball from 1.x to 2.0.0

There are some changes in the configuration file (fpmmm.conf):

  • The access rights should be change as follows: chmod 600 /etc/fpmmm.conf
  • The key Methode was spelled wrong in the configuration file. It was renamed to Method.
  • The key PidFile is ambiguous which could lead to problems and bugs. Thus it was changed to either MyPidFile for fpmmm and DbPidFile for the database.

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-2.0.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-2.0.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 2.0.0

This release contains new features and various bug fixes.

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

shell> /opt/fpmmm/bin/fpmmm --version
General
  • Some trigger url links were improved in various templates.
  • Zabbix 5.0 templates were added.
  • New MyEnv library added.
  • oncePerHourRun and oncePerDay run implemented.
  • Code clean-up in various functions: sendData, cacheFile, initValue, mr_version, mylog, extractVersioncomment. This should make fpmmm dramatically faster in some scenarios.
  • Error handling improved, error messages improved, some Linux command replaced by PHP commands, typos fixed.
  • Better handling of non available variables and status information.
  • All old php5 stuff removed.
  • Various fixed of minor bugs.
  • Ambiguous PidFile replaced by MyPidFile for fpmmm and DbPidFile for database PID file. Caution during Upgrade!
  • Lock location changed from /var/run to /run/lock.
  • Several distribution related and new MariaDB privilege related bugs fixed.
  • Variables default, configuration and configuration file sorted out.
  • Lock file handling improved, Nagios functionality added.
  • Config file is checked if it is readable for others.
  • Own locking mechanism replaced by O/S flock.
  • Option checks activated.
  • Newer distros added (Debian 11, Ubuntu 22.04, Rocky Linux 8).
  • PHP 8, MySQL 8 and MariaDB 10.6 tests passed.

Server
  • Item entropy added.
  • Trigger urls fixed.
  • Items added to templates: Server Entropy.
  • NUMA count fixed.
  • Linux md drives are supported now as well.
  • smartctl error handling improved.
  • NUMA node memory balancing graph added and trigger set.
  • CPU time values were far too high. This is fixed with the new CPU measuring variant.
  • Broken md devices is added to server module + trigger.
  • Server disk: minor device is only gathered in extensive, loop device is skipped.
  • Filesystem locks item and trigger added.
  • Path /usr/sbin for tool smartctl added for Debian.
  • Skip loop and snap devices.
  • Server does not wait 1 second any more for CPU info.
  • New Feature GatherData = {normal|minimal|extensive} added.

Data
  • Disabled binary log is caught in data.
  • #innodb_temp schema size added.

Galera
  • tx_isolation made MySQL 8 ready.
  • Bug fixed in case when Galera is disabled but module is activated.
  • Tables without Primary Key and non InnoDB table items and triggers added.
  • Various triggers added to check correct Galera configuration.
  • Change of cluster configuration id fixed.
  • XA trigger fixed.
  • Galera cache item + trigger fixed.
  • Some more items added for Galera compatibility checks.
  • Items wsrep_xa_statements, wsrep_log_bin, wsrep_log_slave_updates and gcache.size added.

User
  • Code refactored and cleaned-up.
  • Problem during max_connections reached fixed.

Agent
  • Trigger links fixed.
  • New fpmmm version items and triggers added.
  • fpmmm version stuff fixed.
  • Lay off of software-release function and implement check for fpmmm version.

InnoDB
  • InnoDB template fixed on deadlock item.
  • All locking related graphs moved together into same InnoDB screen.
  • innodb_buffer_pool_max_dirty_pages_pct added.
  • Fix division by zero bug.
  • Bug in InnoDB status fixed.
  • InnoDB buffer pool dirty pages trigger added and a small number of buffer pool instances trigger removed.
  • Trigger for innodb_flush_log_at_trx_commit set from info to warning because this is security related.
  • Innodb_log_occupancy, Innodb_checkpoint_age, innodb_log_group_capacity and trigger added.
  • innodb_file_format item added.
  • Items innodb_max_dirty_pages_pct added.
  • InnoDB metrics added.
  • Redo log occupancy increased.

MyISAM
  • none

Aria
  • none

Security
  • none

Master
  • Sync_binlog warning change into the opposite. Security first!
  • BINLOG CLIENT privilege replaced by BINLOG MONITOR for MariaDB 10.5.

Slave
  • Template link fixed.
  • Table with missing Primary Key item added.
  • Link to new graphs fixed for 5.0.

Backup
  • Restore items moved from backup to restore template.
  • Items for restore added for automatized restore testing.
  • Trigger links in backup template fixed.
  • Trigger is fired if backup did not happen within last 36 hours.

MySQL
  • Trigger for transaction_cache_size and statement_cache_size was improved.
  • Trigger urls fixed.
  • Table Open Cache and Table Definition Cache triggers link changed to item values.
  • Storage_engine added for compatibility reasons with default_storage_engine.
  • Processlist total threads added.
  • Error log parser added.
  • com_change_db and queries added.
  • Refactoring of code.
  • Processlist graph and items added, code made better.
  • P_S metadata lock warning improved.
  • Metadata lock info plugin installation message added.
  • Storage_engine removed.

Nagios
  • FromDual Nagios plug-ins implemented in fpmmm.

SNMP
  • SNMP implemented for most important MariaDB and Galera metrics.

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

MariaDB MaxScale Load Balancer with Master/Master Replication

Shinguz - Fri, 2022-05-13 16:39

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. To do this it needs to retrieve the available users from the database first. To get these users the MaxScale account needs some privileges:

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'secret'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

For monitoring the replication and doing proper switchover and failover MaxScale further needs a monitoring account:

CREATE USER 'maxscale_monitor'@'%' IDENTIFIED BY 'secret'; GRANT REPLICATION CLIENT on *.* to 'maxscale_monitor'@'%'; GRANT REPLICATION SLAVE on *.* to 'maxscale_monitor'@'%'; GRANT SUPER, RELOAD on *.* to 'maxscale_monitor'@'%';

And last we need an application account for our test application:

CREATE USER 'app'@'%' IDENTIFIED BY 'secret'; GRANT ALL on test.* to 'app'@'%';
Starting MariaDB MaxScale Load Balancer

Because we do not use the provided DEB/RPM packages on our systems but generic binary tarballs, which are not available, we have to start the MaxScale Load Balancer a bit over-complicated:

# export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/mysql/product/maxscale/usr/lib/x86_64-linux-gnu/maxscale # ldd ./maxscale # ./maxscale --help # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log --config-check # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log

The MaxScale configuration file we used in these tests looks as follows:

# # maxscale_mm.cnf # [maxscale] threads = auto [master1] type = server address = 192.168.1.11 port = 3306 protocol = MariaDBBackend [master2] type = server address = 192.168.1.12 port = 3306 protocol = MariaDBBackend [MultiMasterMonitor] type = monitor module = mariadbmon servers = master1,master2 user = maxscale_monitor password = secret enforce_read_only_slaves = true auto_rejoin = true # auto_failover = true [WriteService] type = service router = readconnroute router_options = master servers = master1,master2 user = maxscale password = secret [ReadService] type = service router = readconnroute router_options = slave servers = master1,master2 user = maxscale password = secret [WriteListener] type = listener service = WriteService protocol = MariaDBClient port = 3306 [ReadListener] type = listener service = ReadService protocol = MariaDBClient port = 3307

If the start was successful can be seen for example with:

# ps -ef | grep maxscale

If you start the MariaDB MaxScale the first time no user/password is needed. So we can connect the the MaxScale Load Balancer with the maxctrl client:

# ./maxctrl maxctrl> list listeners ┌──────────────────┬──────┬──────┬─────────┬──────────────┐ │ Name │ Port │ Host │ State │ Service │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ WriteListener │ 3306 │ :: │ Running │ WriteService │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ MaxAdminListener │ 3307 │ :: │ Running │ ReadService │ └──────────────────┴──────┴──────┴─────────┴──────────────┘ maxctrl> list services ┌──────────────┬───────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ WriteService │ readconnroute │ 0 │ 0 │ master1, master2 │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ ReadService │ readconnroute │ 0 │ 0 │ master1, master2 │ └──────────────┴───────────────┴─────────────┴───────────────────┴──────────────────┘ maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-134 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Relay Master, Slave, Running │ 0-3308-134 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴────────────┘ maxctrl> list sessions ┌────────┬──────┬─────────────────────┬──────────────────────────┬──────┬─────────────┐ │ Id │ User │ Host │ Connected │ Idle │ Service │ ├────────┼──────┼─────────────────────┼──────────────────────────┼──────┼─────────────┤ │ 746468 │ app │ ::ffff:192.168.1.99 │ Fri May 13 15:28:55 2022 │ 0.4 │ ReadService │ └────────┴──────┴─────────────────────┴──────────────────────────┴──────┴─────────────┘

To not allow everybody to access MariaDB MaxScale we set a password for the admin user:

maxctrl> alter user admin secret OK

Then you can connect with username and password:

# ./maxctrl --user=admin --password=secret maxctrl> list users ┌───────┬──────┬────────────┐ │ Name │ Type │ Privileges │ ├───────┼──────┼────────────┤ │ admin │ inet │ admin │ └───────┴──────┴────────────┘
Testing connections over the MaxScale Load Balancer

To test if connections are ending up on the correct database instances we used the following commands:

# mariadb --user=app --host=192.168.1.1 --port=3306 --password=secret test --execute='SELECT @@hostname, @@port' # mariadb --user=app --host=192.168.1.1 --port=3307 --password=secret test --execute='SELECT @@hostname, @@port'
Monitoring of nodes in MariaDB MaxScale

To see what is going on inside the MariaDB MaxScale Load Balancer we used the following command to create a simple real time monitor:

# watch -d -n 1 ./maxctrl --user=admin --password=secret list servers
Switchover and Failover with MariaDB MaxScale

To use MariaDB MaxScales switchover and failover capabilities the Master/Master Replication must be configured to use Global Transaction IDs (GTID). To change to GTID based replication you can use the following commands on both masters:

SQL> STOP SLAVE; SQL> CHANGE MASTER TO MASTER_USE_GTID = SLAVE_POS; SQL> START SLAVE;
Switchover in MariaDB MaxScale

A graceful switchover is used in a controlled situation if we want to switch the roles between the active master and the passive master and vice versa... This can be used for example before a maintenance operation: We do the maintenance operation on the passive Master first, then we switch the roles and then we can do the maintenance operation on the now new passive Master:

maxctrl> call command mariadbmon switchover MultiMasterMonitor

After switching for and back a few times we found out, that the MariaDB MaxScale Monitor has replaced our replication user replication by his own user maxscale_monitor. This is not documented? and I do really not like it, especially if it is done silently...

Failover in MariaDB MaxScale

To provoke/simulate a failover situation we stopped the active Master. Then we see in our monitor that the active Master is down and the passive Master is still running:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3309-142 │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Slave, Running │ 0-3309-142 │ └─────────┴──────────────┴──────┴─────────────┴────────────────┴────────────┘

We further observe, that our application (the famous insert_test.sh) is not working any more but throwing errors... Then we trigger a manual failover:

maxctrl> call command mariadbmon failover MultiMasterMonitor

It looks like an automatic failover is possible (auto_failover = true) but we do not recommend this set-up and thus we did not further investigate in this feature.

After the manual failover the former passive Master becomes active Master:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3308-365829 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-370235 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

Now we simulate the repair of the former failed active Master by just restarting it:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Slave, Running │ 0-3308-401309 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Master, Running │ 0-3308-401309 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

It comes back into the MariaDB MaxScale but just as a simple Slave. So a failover seems to break our Master/Master replication for some reasons. This must be repaired afterwards. Whereas a simple switchover seems to work properly. If this is a bug or intended behaviour I do not know... With the following command on master2 the loop is closed again:

SQL> CHANGE MASTER TO master_host='192.168.1.11', master_port=3306, master_user='maxscale_monitor', master_password='secret', master_use_gtid=slave_pos; SQL> START SLAVE;

Now everything is fine and working as expected again:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 1 │ Relay Master, Slave, Running │ 0-3308-440194 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-440194 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴───────────────┘

Stopping and starting the passive Master had not effect at all.

Switchover with a lagging passive master

To test this scenario we created an artificial lag of the passive Master by setting innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. When the passive master was lagging far enough (about 30 seconds) we tried an switchover:

maxctrl> call command mariadbmon switchover MultiMasterMonitor Error: timeout of 10000ms exceeded

So it is not really clear what happens in this case and the error message is not really telling us about the problem. Further this operations somehow breaks Master/Master replication again.

Failover with a lagging passive master

If we try a failover instead of a switchover we get at least a bit a more meaningful error message:

Error: Server at http://127.0.0.1:8989 responded with 403 Forbidden to `POST maxscale/modules/mariadbmon/failover?MultiMasterMonitor` { "links": { "self": "http://127.0.0.1:8989/v1/maxscale/modules/mariadbmon/failover/" }, "meta": { "errors": [ { "detail": "Can not select 'master1' as a demotion target for failover because it is a running master." }, { "detail": "Failover cancelled." } ] } }
Draining a node with MariaDB MaxScalse

If we try to drain a passive Master we get some warnings but it seems like the result is the expected:

maxctrl> drain server master2 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 OK maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-631119 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Maintenance, Running │ 0-3308-631119 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────┴───────────────┘
Un-drain a node with MariaDB MaxScale

For un-draining the node we tried:

maxctrl> clear server master2 drain OK

On the first look everything seems to be OK. But the State of master2 was still in Maintenance. The command:

maxctrl> clear server master2 maintenance

did the job... If this is a bug or should be considered as an operator error I do not know...

Draining the active Master caused a switchover.

Sources
Taxonomy upgrade extras: replicationmaxscaleload balancerproxyactive-activemaster-mastermariadb

Streaming backup with MariaDB Backup

Shinguz - Thu, 2022-05-12 20:48

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /mnt/backup/" # mariadb-backup --user=root --prepare --target-dir=/mnt/backup

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

Shinguz - Fri, 2022-02-25 14:53

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

This release is a new minor release. It contains mainly 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
  • Newest myEnv library copied from myEnv project.
  • Some code clean-up.

FromDual Backup Manager (bman>
  • Option --source-data which was introduce in MySQL 8.0.26 caused a problem in the mysqldump command. Fixed.
  • Tool XtraBackup 8.0 does not support --no-timestamp option any more. Fixed.
  • Library bman.inc restructured into smaller files.
  • Requirement checks for myenv and brman separated.
  • Start backup message move much further up in output.
  • Connection handling of bman made more OO style to nicer cope with some special cases (DB down).
  • Directory for fpmmm is created automatically now if it does not exist.
  • Clean-up file size returned to bman and can now be reported correctly to fpmmm.
  • Output of configuration files read improved.
  • Configuration files of brman are now checked if group or other can read the files.
  • Hostname is converted to IP first now before we check if it is a remote location in doBinlogBackup.
  • Check in doSchemabackup for missing RELOAD privilege (rc=4198) was done in a wrong way. Fixed.
  • Error message improved in doBinlogBackup.
  • Various typos fixed.
  • Various bugs in bman fixed found with testing and tests improved.

FromDual Recovery Manager (rman)
  • Rman also checks configuration file for readability for group and others now.
  • Option --target in rman is accepted now as configuration parameter.

FromDual brman Catalog
  • Some bugs bman_catalog fixed found with testing.

Testing
  • XtraBackup version check bug fixed in full physical backup test.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

Sharding do-it-yourself

Shinguz - Tue, 2022-02-15 12:18

As already mentioned earlier, we roughly have a hand full of customers which are playing with the though of sharding solutions. They typically have many different customers (clients, tenants) and the number of customers becomes so huge (thousands to millions) that one machine cannot cope with the load any more.

So splitting the load by customers to different machines makes sense. This is quite easy when customers are separated per schema. In the good old times of Open Source our customers have implemented those solutions themselves. But nowadays it looks like do-it-yourself is not sexy any more. It seems like this core competence of a business advantage must be outsourced. So some vendors have already made some solutions available to solve this need: Sharding Solutions.

My question here is: Can a generic sharding solution build exactly what your business needs? Are you still capable to optimize your business process in the way you need it when you buy a 3rd party solution?

And: If you use another product, you have also to build up the know-how how to use it correctly. So I am really wondering if it is worth the effort? Buy or make is the question here. So we made a little Proof-of-Concept of a sharding solution. And it did not take too long...

The concept

First of all we have 2 different kinds of components:

  • The Fabric Node - this is the database where all the meta information about the shards are stored.
  • The Shards - these are the databases where all the customer data are stored.

And we need all this more or less in a highly available fashion.

The fabric table can look as simple as this:

CREATE TABLE `tenant` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tenant_id` varchar(128) NOT NULL, `schema` varchar(128) NOT NULL, `machine` varchar(128) NOT NULL, `port` smallint(5) unsigned NOT NULL, `locked` enum('no','yes') NOT NULL DEFAULT 'no', PRIMARY KEY (`id`), UNIQUE KEY `tenant_id` (`tenant_id`), UNIQUE KEY `schema` (`schema`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO tenant VALUES (NULL, 'Customer 1', 'customer_1', '192.168.33.21', 3306, 'no') , (null, 'Customer 2', 'customer_2', '192.168.33.22', 3306, 'yes') , (null, 'Customer 3', 'customer_3', '192.168.33.23', 3306, 'no') ; SQL> SELECT * FROM tenant; +----+------------+------------+---------------+------+--------+ | id | tenant_id | schema | machine | port | locked | +----+------------+------------+---------------+------+--------+ | 4 | Customer 1 | customer_1 | 192.168.33.21 | 3306 | no | | 5 | Customer 2 | customer_2 | 192.168.33.22 | 3306 | yes | | 6 | Customer 3 | customer_3 | 192.168.33.23 | 3306 | no | +----+------------+------------+---------------+------+--------+
Connection

Now our application needs to know on which shard the data for a specific customer is stored. This will be found in the fabric. So our application has to do first a connect to the fabric and then a connect to the shard. To make it more transparent for your application you can encapsulate everything in one method. And if you want to optimize the connecting you can store the sharding information in a local cache.

$dbhFabric = getFabricConnection($aFabricConnection); $aShardConnection = readShardConnection($dbhFabric, $aTenant); $dbhShard = getShardConnection($aShardConnection); // Do everything needed for the tenant... $sql = sprintf("SELECT * FROM `customer_data` limit 3"); $result = $dbhShard->query($sql);

For the PoC we create 3 different schemas (customer_1, customer_2 and customer_3) with some customer data in it:

CREATE TABLE `customer_data` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_name` varchar(128) NOT NULL, `customer_data` varchar(128) NOT NULL, `customer_number` INT UNSIGNED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO `customer_data` VALUES (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) ;
Resharding

One of the challenges of such a construct is that the intensity of usage in the shards changes over time. All new customers are possibly placed in the same new shard. The new customers start playing around with your application. They do more and more. So a shard becomes overloaded sooner or later. Or the other way around: You have a lot of customers which were very enthusiastic about your product in the beginning and not so much any more now. So you loose customers on the older shards. Or you have to replace an old shard by newer hardware. All this leads to the situation, that your solution is not balanced any more and must be re-balanced. We call this resharding:

This can be done quite easily with a few commands:

-- Lock tenant UPDATE `tenant` SET `locked` = 'yes' WHERE `tenant_id` = 'Customer 3'; -- Wait some time until cache expires or invalidate cache mariadb-dump --user=root --host=192.168.33.23 --port=3306 customer_3 | mariadb --user=root --host=192.168.33.21 --port=3306 # Check error codes very well. Possibly do some other additional checks! -- Update tenant to new shard UPDATE `tenant` SET `machine` = '192.168.33.21', `port` = 3306 WHERE `tenant_id` = 'Customer 3'; -- DROP tenant in old shard mariadb --user=root --host=192.168.33.23 --port=3306 --execute="DROP SCHEMA `customer_3`" -- Unlock tenant UPDATE `tenant` SET `locked` = 'no' WHERE `tenant_id` = 'Customer 3';
Monitoring

To find out which customers cause a lot of load and which shards are over- or under-loaded you need some kind of sophisticated monitoring. You possibly want to know things like:

  • CPU usage (mpstat)
  • I/O usage (iostat)
  • NW usage
  • RAM usage (free)
  • Number of connections per shard and per customer
  • Number of queries per shard and per customer
  • etc.

Those metrics can be found with some queries:

SHOW GLOBAL STATUS LIKE 'Bytes%'; SELECT * FROM information_schema.global_status WHERE variable_name IN ('Threads_connected', 'Threads_running', 'Max_used_connections') ; SELECT processlist_db, COUNT(*) FROM performance_schema.threads WHERE type = 'FOREGROUND' GROUP BY processlist_db ; SELECT * FROM sys.schema_table_lock_waits WHERE object_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics WHERE table_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics_with_buffer WHERE table_schema LIKE 'customer%';; SELECT table_schema, SUM(data_length) + SUM(index_length) AS schema_size FROM information_schema.tables WHERE table_schema LIKE 'customer%' GROUP BY table_schema ;
Missing features

If you really think you need it, you can also make a nice GUI to show all those metrics. But be prepared: This will cost you most of your time!

We assume that all the shards are accessed with the same user as the fabric is accessed. In reality this is possibly not the case. But the tenant table can be easily extended.

To make the whole concept much easier we omitted the idea of number of replicas which is known from other solutions. We think having every shard redundant by a Master/Slave replication is sufficient.

If you find anything which is missing in this concept study or if you experience some problems we did not thought about, we would be happy hearing from you.

Taxonomy upgrade extras: shardingmulti-tenant

Containers and databases

Shinguz - Fri, 2022-02-11 15:44

In the last months we got more and more requests for supporting MariaDB/MySQL/Galera Cluster in (mostly Docker) containers.

Because of its additional layer and added complexity I do not like containers much. Containers are more complicated during troubleshooting and debugging problems.

Other people have already written more than enough about the advantages of containers. What is more difficult to find are the disadvantages of technologies. Thus I focus on those:

Wrong technology?

Container solutions were designed to deal with stateless applications that have ephemeral data. Containers spin up a quick microservice and then destroy it. This includes all the components of that container (including its cache and data). The transient nature of containers is because all of the components and services of that container are considered to be part of the container (essentially it is all or nothing). Serving the container a data volume owned by the underlying O/S by punching a hole through the container can be very challenging.
Most of the development efforts put into the various solutions had one goal in mind: Statelessness. There are solutions that can help keep your data persistent, but they are very quickly evolving. They require a high level of complexity, that negate any efficiency gains due to increased operational complexity (and risk). [1]

Stability

These container solutions are meant for quick development and deployment of applications that are broken into tiny components: microservices. Normally, these applications evolve very quickly in organizations that are very software/developer-driven. That seems to be how these container solutions (again, especially Docker) are developed as well. New features are pushed out with little testing and design. The main focus seems to be the latest feature set and being first to market. They “beg for forgiveness” instead of “ask for permission.” On top of that, backward compatibility is a distant concern (and even that might be an overstatement). This means that you are going to have to have a mature Continuous Delivery and testing environment as well as a known and tested image repository for your containers. [1]

We have seen complaints about Galera Cluster stability issues inside Docker containers. The signs were pointing without doubt to network issues. If these were real network issues or just container network issues we could not find out yet.

Networking can be tricky in containers world when you want to limit the access within containers and also have proper network communications where required. [3]

Docker might even make your application slower. If you are working with it, you should set limits on how much memory, CPU, or block I/O the container can use. Otherwise, if the kernel detects that the host machine’s memory is running too low to perform important system functions, it could start killing important processes. If the wrong process is killed (including the Docker itself), the system will be unstable.

Performance

We have heard reports that performance overhead of Docker containers can be up to 10%. If this is still true with the right configuration and and recent version must be shown. [2]

You should not expect Docker to speed up an application in any way. [5]

Security

Since there is no full operating system people tend to overlook the security aspect of containers, but if you look up online, you will see that hackers are targeting systems that are hosted in containers and not secured properly.
Since the containers use the same kernel, they are not 100 isolated, so you should be aware of the risks if you are using multiple containers in one server, and make sure you know what you are doing and which containers are running on the same kernel along with your stuff! [3]

All containers share access to a single host operating system. You risk running Docker containers with incomplete isolation. Any malicious code can get access to your computer memory. [5]

Running applications with Docker implies running the Docker daemon with root privileges. Any processes that break out of Docker container will have the same privileges on the host as it did in the container. Running your processes inside the containers as a non-privileged user cannot guarantee security. It depends on the capabilities you add or remove. To mitigate the risks of Docker container breakout, you should not download ready-to-use containers from untrusted sources. [5]

Data storage is intricate – By design, all of the data inside a container leaves forever when it closes down except you save it somewhere else first. There are ways to store data tenaciously in Docker, such as Docker Data Capacities, but this is arguably a test that still has yet to be approached in a seamless manner. [6]

Container O/S is the same as host O/S. If the host O/S is upgraded all the containers get also a new O/S.

Popular docker images have many vulnerabilities. So build and harden your images yourself. [10, 11, 12]

One of the most famous Docker security vulnerabilities can be found here: Alpine Linux Docker Images Shipped for 3 Years with Root Accounts Unlocked and Alpine Linux Docker Image Vulnerability CVE-2019-5021 and Docker Image Vulnerability (CVE-2019-5021).

Complexity

Containerization also means consolidation. And as in consolidated systems usually you can have side effects or effects caused by someone you did not expect.

Debugging

Debugging problems in a container environment becomes more complex because the many additional layers added. Then the necessary information and metrics are not there or not available in the way as expected. This makes troubleshooting more complicated.

Policy

Docker implementation is quite complex. A load of technological supports are necessary for Docker implementation including orchestration, container management, app stack, data screenshots, networking of containers, and so on.

The container ecosystem is split – But the core Docker platform is open source, some container products do not work with other ones. [6]

Features

Container technologies require kernel features which were not present in earlier kernels. This made system maintenance more complicated. This problem may have been solved in the meanwhile?

If you are aware of any other disadvantage not mentioned above please let us know. Some of these disadvantages might have been reduced in the recent years.

Literature
Taxonomy upgrade extras: containerdockerkubernetes

Learning from the Bugs Database

Shinguz - Thu, 2022-01-20 15:05

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

if one runs DML on a table that has no indexes, a full table scan is done. with RBR, the slave might need to scan the full table for *each* row changed.

The consequence of this behaviour is that the Slave starts lagging. It was further mentioned:

Worst part is that PROCESSLIST, etc provide absolutely NO obvious indication what is going on, for something that may take 12 hours, 3 days or even more...

Symptoms of this problem are described as follows:

Observe 78,278 row locks but only 10,045 undo log entries, so many more rows being scanned than changed. Also observer 16 row deletes per second but 600,754 row reads per second, same mismatch between counts suggesting unindexed accesses are happening.

You may also see "invalidating query cache entries (table)" as a symptom in the processlist. If you see that, check to see whether this is the possible root cause instead of giving full blame to only the query cache."

The suggested workaround is: add a primary key to the table.

But some user complain:

in my case, my only decent primary key is a surrogate key - and that's untenable because of the locking and lost concurrency (even with lock_mode = 2). Even if I solved that, I'd have to use the surrogate in partitioning - which more or less defeats the purpose of partitioning by lopsiding the partitions.

and others claim:

Adding an "otherwise usable (i.e. to improve query times)" PK is not really an option for them since there are no short unique columns.

A long composite key is also not an option because:

  • In InnoDB tables, having a long PRIMARY KEY wastes a lot of space.
  • In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

And then comes a first suggestion for solving the issue:

So, we can create a normal short/auto-increment PK, but this is more or less the same as having the internal/hidden InnoDB PK (which does not seem to be used properly for RBR replication purposes).

As mentioned before, possibly the internal/hidden InnoDB PK can be used to resolve this bug.

Short after we get an important information and the learning of the day:

there's nothing that makes the InnoDB internal key consistent between a master and a slave or before and after backup and restore. Row with internal ID 1 can have completely different end user data values on different servers, so it's useless for the purpose being considered here, unfortunately.

Nor is there any prohibition on a slave having a unique key, which will be promoted to PK, even if there is no unique key on the master. They can even have different PKs and there can be good application reasons for doing that. Though we could require at least a unique key on all slaves that matches a master's PK without it hurting unduly.

It is possible to recommend at least _a_ key (not necessarily unique) on the slave and have replication try key-based lookups to narrow down the number of rows that must examined. That combined with batch processing should cut the pain a lot because we can reasonably ask for at least some non-unique but at least reasonably selective key to use. But this is only recommend, not require. If people want no key, we should let them have no key and be slow.

Then we got some further information why moving back to SBR is a bad idea:

It is my opinion that switching to SBR has way too many trade offs (if even for one table) to call it an acceptable workaround. The main crux for this argument being that just about the only time you run into this bug is when you have tables with a massive amount of rows - which is exactly where you start paying heavy penalties for SBR (Locking)."

And a new potential problem rises up:

As far as how the server should know which key to use - am I correct in assuming that it will use the optimizer to determine the index, and you are asking what would happen if the optimizer picked the wrong one?"

Another suggestion for improvement:

Batching looks promising, at least it would reduce the number of scans. But it would still be very painful if no key could be used. While using a key would be very painful if a high percentage of the rows in the table were being touched. So maybe some mixed solution that depends on the count of rows being touched might be best.

In about 2012 they had an implementation for batch jobs.

And you can force a Primary Key now in MySQL 8.0 since 2021 with sql_require_primary_key.

How MariaDB solves the problem you can find here: Row-based Replication With No Primary Key.

Taxonomy upgrade extras: primary keyreplicationRow Based Replication (RBR)Statement Based Replication (SBR)

MariaDB Deadlocks

Shinguz - Tue, 2022-01-11 15:18

We get ever and ever again customer requests concerning Deadlocks. First of all, Deadlocks are usually an application problem, not a database problem! The database itself manifests the application problem with the following message which is sent to the application as an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If your application receives this error message you know where in your application you have a problem. But a deadlock is always a problem between 2 different connections. So how you can find the other part of the problem?

The full Deadlock situation is shown with the following command:

SQL> SHOW ENGINE InnoDB STATUS\G ... ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-12-23 18:55:18 0x7f51045e3700 *** (1) TRANSACTION: TRANSACTION 847, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 46, OS thread handle 139985942054656, query id 839 localhost root Updating delete from t where id = 10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 847 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) TRANSACTION: TRANSACTION 846, ACTIVE 25 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 39, OS thread handle 139985942361856, query id 840 localhost root Updating delete from t where id = 11 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 6; hex 00000000034f; asc O;; 2: len 7; hex 770000019d031d; asc w ;; *** WE ROLL BACK TRANSACTION (2) ...

Unfortunately with the SHOW ENGINE INNODB STATUS command you can only show the last Deadlock error. If you want to log all the Deadlock errors in the MariaDB error log you can activate the Deadlock error reporting with the following configuration option in your MariaDB configuration file (my.cnf):

innodb_print_all_deadlocks = ON
How to solve Deadlocks?

Deadlocks always can occur in a transactional database system with fine-grained locking. So your application must be aware of Deadlocks and catch the Deadlock error and retry the transaction. The error message already indicates this. It is an advice for YOU as a developer:

try restarting transaction
How to avoid deadlocks?

Deadlocks are an hot spot on some rows. Hot spot means: Many (at least 2) connections do something (INSERT, UPDATE, REPLACE, DELETE, SELECT FOR UPDATE) on the same rows (which then are locked).

So how can you solve a Deadlock problem? Solving in the meaning of "reduce the probability of a Deadlock close to zero". Zero Deadlocks guarantee you can only achieve in no-concurrency scenarios.

  • Look at the design of your application: Is it necessary that the 2 conflicting transactions are done in the way you are doing it right now? Can you change the behaviour of your application?
  • Reduce locking time: As shorter the locks are hold as smaller the probability is that you run into a Deadlock situation. So make sure your conflicting transactions are running as fast as possible and the locks are hold as short as possible. Check that all the transactions are using perfect indexes.
  • Reduce the amount of locks: The fewer rows your conflicting transactions lock the smaller is the chance for a Deadlock. For example: Several small INSERTs in several transactions instead of one huge INSERT in one big transaction should cause less locks and thus a smaller probability of Deadlocks. Only change (REPLACE, INSERT ON DUPLICATE KEY UPDATE) what is really needed to change. Check before you change, that rows need to be changed.
  • Do your tasks less often. For example polling every 10 seconds instead of every second, or do refreshes of data less often.
  • Timely defer concurrent transactions. For example 2 batch jobs: Do they really need to run at the exact same time?
  • Reduce concurrency (parallelism), if possibly.
  • Catch the Deadlock error and retry the transaction again a few milliseconds later. If this does not happen too often it is not a problem for your database.
  • Change transaction isolation level to a level causing less locks. You can do that per session (SET SESSION ...) to reduce the impact on the whole system or globally (SET GLOBAL ...). But changing the isolation level globally potentially can have also an impact on other parts of your application or even completely different applications running on the same database.

And to be aware: You cannot completely avoid (100%) Deadlocks. They always can happen and you have to cope with it!

Do not mix up Deadlocks with Galera cluster conflicts which look similar at the first look but are not.

Literature
Taxonomy upgrade extras: deadlock

MariaDB Devroom at FOSDEM 2022 CfP is now open

Shinguz - Thu, 2021-12-09 16:36

Also in 2022 there will be a FOSDEM (Free and Open source Software Developers' European Meeting) on 5 and 6 February 2022. This time again online from Brussels (Belgium).

MariaDB has again its own Devroom and the Call for Papers (CfP) is now open for your submissions. We are looking for interesting topics about your daily business, technical presentations, war stories, point of views of management, etc. The deadline for the CfP is before 21 December 2021.

For further information about how to submit a presentation please look here: https://mariadb.org/cfp-for-the-mariadb-devroom-fosdem-2022-now-open/

If you need any help or if you have any question please let us know...

Taxonomy upgrade extras: fosdem2022developersocial event

MariaDB Connection ID

Shinguz - Mon, 2021-11-29 15:09

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2372 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2373 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 2322 | +---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

SQL> SELECT id AS connection_id, user, host, IFNULL(CONCAT(SUBSTR(info, 1, 32), '...'), '') AS query FROM information_schema.processlist; +---------------+-------------+-----------+-------------------------------------+ | connection_id | user | host | query | +---------------+-------------+-----------+-------------------------------------+ | 2383 | root | localhost | SELECT id AS processlist_id, use... | | 6 | system user | | | +---------------+-------------+-----------+-------------------------------------+

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT * FROM information_schema.processlist WHERE id != CONNECTION_ID(); +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | 2398 | app | localhost:35512 | NULL | Sleep | 4 | | NULL | 4771.722 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14030 | NULL | 6946 | | 2392 | root | localhost | NULL | Sleep | 36 | | NULL | 36510.867 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14021 | NULL | 3850 | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-08-19 15:09:19 7fbb6c328700 Transaction: TRANSACTION 543875059, ACTIVE 0 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 124441421, OS thread handle 0x7fbb6c328700, query id 7822461590 192.168.1.42 fronmdual update INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130) Foreign key constraint fails for table `test`.`contact`: , CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `contact_id` tuple: DATA TUPLE: 2 fields; ... But in parent table `test`.`user`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 41; compact format; info bits 0 ... ------------ TRANSACTIONS ------------ Trx id counter 2499 Purge done for trx's n:o < 2486 undo n:o < 0 state: running History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data select * from test.test Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

PERFORMANCE_SCHEMA threads view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id FROM performance_schema.threads WHERE processlist_id = CONNECTION_ID(); +-----------+----------------+ | thread_id | processlist_id | +-----------+----------------+ | 2369 | 2321 | +-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

sys Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'. 2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses: 2021-11-25 16:22:34 1796 [Note] - 192.168.1.142 2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication) 2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)
MariaDB General Error Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02 4568 Connect fpmmm_agent@localhost on using TCP/IP 4568 Query SET NAMES utf8 4568 Query SHOW GRANTS 4568 Query SELECT "focmm" 4568 Query SHOW GLOBAL VARIABLES 4568 Query SHOW /*!50000 GLOBAL */ STATUS 4568 Quit
MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729 9:22:57 # User@Host: root[root] @ localhost [] # Thread_id: 34 Schema: test QC_hit: No # Query_time: 0.003995 Lock_time: 0.000114 Rows_sent: 2048 Rows_examined: 2048 # Rows_affected: 0 Bytes_sent: 79445 SET timestamp=1627543377; select * from test;
MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log 2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0 20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0 20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0 20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064 20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test, 20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0 20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0 20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0
INFORMATION_SCHEMA

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related topics to MariaDB Connection ID Thread Cache

When using the MariaDB Thread Cache it looks like the thread_id (and also the Connection ID) is changed each time a new connection is created. This is not what I expected, at least for the Thread ID. If I take a thread from the pool I would expect the same or at least another old thread_id again.

Connection Pooling

If you are using application side Connection Pooling different application connection handles will share the same DB connection. So you have to expect traffic from different application parts under the same Connection ID on the database side.

Pseudo thread ID

Since MySQL 8.0.14 there is a variable called pseudo_thread_id. It is for internal server use. Changing the variable on session level also changes the value of the function CONNECTION_ID(). I have no idea what this variable is used for.

Other related information
Taxonomy upgrade extras: connectionmax_used_connections

MariaDB / MySQL Advanced training end of October 2021

Shinguz - Mon, 2021-10-04 15:12

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

Automated MariaDB restore tests

Shinguz - Fri, 2021-08-27 19:38

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup...

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

  • Check that your backup was running fine. For example by checking the return code of your backup.
  • Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
  • Check the size of your backup. If the size of your backup significantly changed, it is worth to have a closer look at your backup.
  • And finally make your monitoring system aware if the backup was NOT running at all and if you are sure your backup is really triggered...

Backup test with FromDual Enterprise Tools

All this functionality is integrated in the newest releases of FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) and the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) in combination with the great monitoring solution Zabbix.

You have to run Backup Manager with the options --fpmmm-hostname and --fpmmm-cache-file:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily \ --fpmmm-hostname=mariadb-106 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-106.cache

then Backup Manager knows that it has to collect the metrics: return code, backup time and backup size and deposits them for the next Performance Monitor run. The next fpmmm run then will automatically gather these data and send it to the Zabbix Server.

In the Zabbix Monitor then you can see how your backup behaves:

and you get alerts if backups failed or did not even happen:

Restore Tests with FromDual Enterprise Tools

If your backup was running fine, returned with a zero return code, had the usual size and was running in a normal amount of time it does not necessarily mean that you also can do a restore with this backup!

For the sissies among us we also should do a restore test. To justify restore tests we have worked out 2 different concepts:

Staging systems restore test

Many of us have different stages of systems: Production, Quality Assurance, Integration, Testing and Development. All those systems need to be provisioned from time to time with new and fresh data from production. These systems would be ideal candidates for your daily restore tests. Also here the FromDual Enterprise Tools can help you: The backup, done with Backup Manager is shipped automatically to the other system where the Recovery Manager is restoring the database. Booth tools report their results to the Performance Monitor which sends the results to Zabbix. And Zabbix sends complains to the administrators if the backup or the restore did not happen or failed:

Spider system restore test

If you have many systems to do restore test or if you do not want to expose your precious data to other stages than production we recommend you the restore spider concept: Every production database does its daily backup on its local backup store. And additionally you have a centralized restore system which has access to the backup of each database. So this central spider restore system grabs now the backup of each database one after the other and does a restore on its restore system. The result will be reported to the Performance Monitor again and you get notice by Zabbix is the Restore did not happen, not work or was talking longer than a predefined time:

Taxonomy upgrade extras: BackupRestorebrmanfpmmmmonitoring

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

Shinguz - Mon, 2021-08-23 17:14

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

This release is a new minor release. It contains mainly 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
  • Redhat 8 RPM package added to FromDual repository.
  • Stripped unnecessary files from tarball.
  • From now on we fully support MySQL 5.7 to 8.0.
  • From now on we fully support PHP 8.
  • From now on we fully support MariaDB 10.3 to 10.6.
  • All old PHP 5 stuff was removed.
  • Library from myEnv updated.
  • Distribution distinguishing code was cleaned-up and Ubuntu, Rocky Linux, AlmaLinux and CloudLinux should pass checks correctly now.

FromDual Backup Manager
  • Schema backup was not ignoring SYS schema when doing test for non-transactional (Aria) tables. This test is done correct now.

FromDual Recovery Manager
  • Restore return code and restore time hook added for integration into fpmmm monitoring. Automated restore tests are better supported now.

FromDual brman Catalog
  • none

FromDual brman Data masking / data obfuscating
  • none

Testing
  • All tests passed for MySQL 8.0.
  • All tests passed with PHP 8.
  • Various tests added.
  • All tests passed for MariaDB 10.6.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

Monitoring your MariaDB database with SNMP

Shinguz - Mon, 2021-08-16 14:24
Table of Contents
What is SNMP?

A customer recently had the question if an how his MariaDB database can be easily monitored with SNMP?

SNMP means Simple Network Management Protocol. It is a widely used and standardized protocol for monitoring the health of network and other devices (including services). In principle you can monitor nearly everything with SNMP.

On Linux a common implementation of SNMP is Net-SNMP, a suite of applications used to implement SNMP v1, SNMP v2c and SNMP v3 using both IPv4 and IPv6.

SNMP is a typical client-server architecture: The client which is collecting and sending the monitoring data is called agent and the server collecting all the monitoring data is called manager.


Source: Wikipedia: SNMP

An agent can be polled by the manager to collect the monitoring data (Request/Responses) or it can send monitoring data on its own (Trap). The latter one is called a SNMP Trap.


Source: Cisco: Understanding Simple Network Management Protocol (SNMP) Traps

Each measuring event type get its own Object Identifier (OID) which looks for example as follows: 1.3.6.1.4.1.57800.1.1.1. This is a representation of a tree hierarchy called MIB (Management Information Base):


Source: DPS Telecom SNMP OID: Introduction for Industry Professionals

An OID can also be represented in a human readable textual form which looks for example as follows: FromDual-fpmmm-MIB::fpmmmStart

SNMP Agent (snmptrap)

In this project we concentrate on the SNMP trap agent (snmptrap). Which sends an asynchronous notification to the manager (snmptrapd). To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmp snmp-mibs-downloader

These 2 packages contain:

snmpSNMP (Simple Network Management Protocol) applicationssnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is made so complicated because of some legal reasons:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf
SNMP Manager (snmptrapd)

There are 2 different types of SNMP managers. The SNMP daemon (snmpd) and the SNMP trap daemon (snmptrapd). We concentrate on the later one in this project. To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmptrapd snmp-mibs-downloader

These 2 packages contain:

snmptrapdNet-SNMP notification receiversnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is so complicated because of some legal problems:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf $ sed -i 's/export MIBS=/# export MIBS=/g' /etc/default/snmpd

For our tests we use the following configuration file:

# cat /etc/snmp/snmptrapd.conf disableAuthorization yes authCommunity log,execute,net public createUser myuser MD5 mypassword DES myotherpassword [snmp] logOption s 2 [snmp] logOption f /var/log/snmptrapd-direct.log format2 %V\n% Agent Address: %A \n Agent Hostname: %B \n Date: %H - %J - %K - %L - %M - %Y \n Enterprise OID: %N \n Trap Type: %W \n Trap Sub-Type: %q \n Community/Infosec Context: %P \n Uptime: %T \n Description: %W \n PDU Attribute/Value Pair Array:\n%v \n -------------- \n _EOF

Then the SNMP trap daemon has to be (re-)started:

$ systemctl start snmptrapd.service

The log messages then can be found in /var/log/snmptrapd-direct.log or otherwise like this: grep snmptrap /var/log/syslog.

If you write your own MIBs they can be located here: /usr/share/snmp/mibs.

Test the SNMP Agent

An SNMP trap is send as follows:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID='1.3.6.1.4.1.57800.1.1.2' $ OID='1.3.6.1.4.1.57800.1.1.1' $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

And then you will see in the SNMP trap daemon error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72136167) 8 days, 8:22:41.67 SNMPv2-MIB::snmpTrapOID.0 = OID: 1.3.6.1.4.1.57800.1.1.2 1.3.6.1.4.1.57800.1.1.1 = Counter32: 1628864995

Or more MariaDB specific:

$ VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"
Creating your own MIB

How you write your own MIBs you can find here: Writing your own MIBs.

MIBs can/should be located under $HOME/.snmp/mibs or /usr/local/share/snmp/mibs. The MIB search path can be found with this command:

$ snmptranslate -Dinit_mib .1.3 2>&1 | grep MIBDIRS $ ll /root/.snmp/mibs /usr/share/snmp/mibs /usr/share/snmp/mibs/iana /usr/share/snmp/mibs/ietf /usr/share/mibs/site /usr/share/snmp/mibs /usr/share/mibs/iana /usr/share/mibs/ietf /usr/share/mibs/netsnmp

A tool for checking your MIB is smilint:

$ apt-get install smitools $ smilint snmp/FromDual-fpmmm-MIB.mib --level=6 snmp/FromDual-fpmmm-MIB.mib:90: warning: node `fpmmmLastrun' must be contained in at least one conformance group

If you want to extend the MIB search path you can modify the MIBDIRS environment variable:

$ export MIBDIRS=/home/oli/fromdual_devel/fpmmm/snmp:/home/oli/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

To check if your MIB is correctly translated into an OID and vice versa you can use the tool snmptranslate:

$ snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun $ snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

And if the translation works you can send an SNMP trap with the MIB instead of the OID:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID="FromDual-fpmmm-MIB::fpmmmStart" $ OID="FromDual-fpmmm-MIB::fpmmmLastrun" $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

and this should also be translated correctly in the snmptrapd error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72176488) 8 days, 8:29:24.88 SNMPv2-MIB::snmpTrapOID.0 = OID: FromDual-fpmmm-MIB::fpmmmStart FromDual-fpmmm-MIB::fpmmmLastrun = Counter32: 1628864995
Sending MariaDB SNMP traps from PHP

It looks like the PHP native SNMP functions do not provide anything for sending SNMP traps. But luckily there is the FreeSDx/SNMP PHP library by ChadSikorra on GitHub which can do the job.

After installing PHP composer installing of the FreeDSx/SNMP library was no problem:

$ php composer.phar require freedsx/snmp Using version ^0.4.0 for freedsx/snmp ./composer.json has been updated Running composer update freedsx/snmp Loading composer repositories with package information Updating dependencies Lock file operations: 3 installs, 0 updates, 0 removals - Locking freedsx/asn1 (0.4.4) - Locking freedsx/snmp (0.4.0) - Locking freedsx/socket (0.3.1) Writing lock file Installing dependencies from lock file (including require-dev) Package operations: 3 installs, 0 updates, 0 removals - Downloading freedsx/socket (0.3.1) - Downloading freedsx/asn1 (0.4.4) - Downloading freedsx/snmp (0.4.0) - Installing freedsx/socket (0.3.1): Extracting archive - Installing freedsx/asn1 (0.4.4): Extracting archive - Installing freedsx/snmp (0.4.0): Extracting archive 2 package suggestions were added by new dependencies, use `composer suggest` to see details. Generating autoload files

This we need for adding SNMP support to our FromDual Performance Monitor for MariaDB and MySQL (fpmmm). A simple PHP SNMP trap example you can find as follows:

$aAutoload = require_once('vendor/autoload.php'); use FreeDSx\Snmp\SnmpClient; use FreeDSxph\Snmp\Exception\SnmpRequestException; use FreeDSx\Snmp\Oid; $snmp = new SnmpClient([ 'host' => '192.168.56.102' , 'community' => 'public' , 'version' => 2 , 'port' => 162 , ]); try { $date = time(); $trapOid = '1.3.6.1.4.1.57800.1.1.2'; // FromDual-fpmmm-MIB::fpmmmStart $Oid = '1.3.6.1.4.1.57800.1.1.1'; // FromDual-fpmmm-MIB::fpmmmLastrun # The parameters are: # 1. The system uptime (in seconds) # 2. The trap OID # 3. The OIDs and their values $snmp->sendTrap(60, $trapOid, Oid::fromCounter($Oid, $date)); } catch ( SnmpRequestException $e ) { printf('Unable to send trap: %s', $e->getMessage()); }
Literature
Taxonomy upgrade extras: SNMPmonitoring

MariaDB/MySQL Environment MyEnv 2.0.3 has been released

Shinguz - Fri, 2021-07-02 15:32

FromDual has the pleasure to announce the release of the new version 2.0.3 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.x to 2.0.3 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.3.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.3 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.3 MyEnv
  • MariaDB 10.6 my.cnf hash added and bug in stop instance for fqdn fixed.
  • Function print replaced by output() function.
  • Function checkDataseOld function removed.
  • Functions checkDatabase, startDatabase and stopDatabase replaced by their Instance analogon.
  • Feature: CDPATH added to variables.conf.template pointing to instancedir.
  • Function print replaced by output in function checkMyEnvRequirements.
  • log_warnings commented out in my.cnf.template to make it work with MySQL 8.0.
  • Debug information improved.
  • my.cnf updated with new 8.0 findings.
  • Ubuntu tag fixed.
  • Variable gcache.recover added to template.
  • Error handling changed from procedural style to OO style.
  • Naming of return values fixed.
  • Function my_exec made variable naming more clear.
  • InnoDB monitor enabled by default.
  • Constants made more human readable.
  • Variable sync_binlog in my.cnf template adjusted to reasonable value.
  • my.cnf template updated to newest state.
  • New MySQL 5.7 Ubuntu repo hash for my.cnf added.
  • Package php-posix replaced by php-process on Redhat/CentOS.
  • Branch Homebrew added to extract Branch for MacOS.
  • Function formatTime added.
  • Comments changed from php5 to php7.
  • New MySQL 8.0 tmp schema #innodb_temp added to hideschema parameter.
  • Socket is right now when port is not 3306.
  • New interfaces for start/stopping instances and code cleaned up.

MyEnv Installer
  • Skipping init script is possible now in installMyEnv.
  • Instance proposal is now checked if it already exists in add instance in installMyEnv.
  • On Debian systems now systemd unit file advice is shown as well instead of initV files advice in installMyEnv.

MyEnv Utilities
  • Using a password on the command line hack replace by better solution for some utilities.
  • Utility pcs_standby_node.sh added.
  • Utilities: connect_times.php and galera_monitor.sh added.
  • Utilities: mixed_test.php, binlog_push.php and binlog_apply.php added.
  • VIP v2 start stop script added (vip2.sh).
  • Utility netstat.php added.
  • Utility insert_test.bat added and is working now like on Linux.
  • insert_test.sh made more flexible to run modify column command.
  • insert_test.php made ready for new 5.7/8.0 timestamp behaviour.
  • Utility connect_test.php added.
  • MySQL Group Replication monitor mad configurable and output made nicer.

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

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleasemysqld_multi

Query performance comparison between MariaDB Column Store and other Storage Engines

Shinguz - Thu, 2021-06-03 15:53

Storage Engines like InnoDB, Aria and MyISAM are Row Stores. They store rows one after the other in blocks or even directly in a single file (MyISAM). On the other hand a Column Store like MariaDB Column Store stores all the same attributes (columns) of the rows together in chunks.

This is how the table sales_fact looks like:

CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL ) ENGINE=ColumnStore DEFAULT CHARSET=utf8; CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL, KEY `i_sales_customer_id` (`customer_id`), KEY `i_sales_product_id` (`product_id`), KEY `i_sales_promotion_id` (`promotion_id`), KEY `i_sales_store_id` (`store_id`), KEY `i_sales_time_id` (`time_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As you may have noted the Column Store table does NOT contain indexes!

And this shows how it is stored in memory and on disk (left Row Store, right Column Store; in reality it is a bit more complicated):

     

Considering this and depending on your queries a Column Store can have some significant response time advantages compared to a Row Store. And the other way around as well!

Comparison with MyISAM

So let us compare some queries with the same set of data. We are still working with one single-node MariaDB Column Store as described here.

We have chosen MyISAM for comparison with MariaDB Column Store because it is the fastest storage engine we have so far for low concurrency SELECT statements. As data set we have used the good old foodmart schema prepared for Column Store. We concentrated on the sales_fact table because this is the table with the biggest amount of rows. The MyISAM key_buffer_size was set to 128 Mibyte which is big enough for all the indices.

Simple SELECT queries SELECT COUNT(*) FROM table

As experienced MyISAM users know already for this query MyISAM is unbeatable fast:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact; 1 row in set (0.040 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact; 1 row in set (0.000 sec)

This query is mainly to make sure that both tables have the same amount of data...

SELECT * FROM table or CHECKSUM TABLE table

In these queries we do a full table scans. This is probably the worst pattern you can do to Column Store. And MyISAM is significantly faster in this:

SQL> SELECT * FROM foodmart_cs.sales_fact; 1078880 rows in set (1.833 sec) SQL> SELECT * FROM foodmart_myisam.sales_fact; 1078880 rows in set (0.607 sec)

But this pattern is NOT what a Column Store is made for...

The Column Store architecture consists of 2 different types of modules: The User Module (UM) consisting of the MariaDB Server (mysqld), the Execution Manager (ExeMgr) and the Distribution Managers (DMLProc, DDLProc and cpimport). These processes are responsible for parsing SQL and distributing and executing the SQL statements.

The other type of module is the Performance Module (PM) consisting of the Managing and Monitoring Process (ProcMgr and ProcMon), the Primary Process (PrimProc) which handles the query execution and the Performance Module process which handles loads and writes (WriteEngineServer and cpimport). The Performance Module basically performs the work and does the I/O operations.

So we have the communication from the MariaDB server to the User Module to the Performance Module and this in normal situation over a network. So it is obvious that this costs a lot of time for huge data sets.

This can be shown when we execute the CHECKSUM TABLE command which does similar things like SELECT * FROM table but does NOT return the full result set:

SQL> CHECKSUM TABLE foodmart_cs.sales_fact; +------------------------+------------+ | Table | Checksum | +------------------------+------------+ | foodmart_cs.sales_fact | 2218293488 | +------------------------+------------+ 1 row in set (1.370 sec) SQL> CHECKSUM TABLE foodmart_myisam.sales_fact; +----------------------------+------------+ | Table | Checksum | +----------------------------+------------+ | foodmart_myisam.sales_fact | 2218293488 | +----------------------------+------------+ 1 row in set (0.853 sec)

It is interesting that Column Store is less slower here compared to the full table scan. It looks like it can already parallelize some of the work in this step already?

SELECT min(column), max(column) FROM table

Now let us come to more data warehouse (DWH) like queries:

SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_cs.sales_fact; 1 row in set (0.104 sec) SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; 1 row in set (0.001 sec)

Also here MyISAM is horribly fast because it can short cut:

SQL> EXPLAIN SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

It would be interesting to compare those queries with InnoDB which cannot do these short cuts.

SELECT column, COUNT(*) FROM table GROUP BY column

This is a query where MyISAM cannot shortcut and we see already an advantage for MariaDB Columns Store.

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id; 673 rows in set (0.099 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id; 673 rows in set (0.138 sec)

It would be interesting to see those number with really huge amount of rows (109 to 1010 rows) which do not fit into caches any more...

SELECT column, COUNT(*) FROM table GROUP BY column ORDER BY column

The last result was pretty much chaotic. So let us test an ordered result:

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.100 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.136 sec)

The ORDER BY is probably executed in the MariaDB Server. So for this small data set the network communication can be ignored. No difference observed.

SELECT COUNT(*) FROM table where column >= value

This SELECT queries data from a very big range (99%). We know MyISAM uses the index by doing an index-only-scan. MariaDB Column Store does NOT have indexes:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.054 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.159 sec) SQL> EXPLAIN SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | sales_fact | range | i_sales_time_id | i_sales_time_id | 4 | NULL | 1055594 | Using where; Using index | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+

Column Store outperforms MyISAM by factor of 3 already with a small data set.

The same query with a very small range of data (1%):

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 1090; 1 row in set (0.042 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 1090; 1 row in set (0.005 sec)

Column Store becomes only slightly faster with the smaller result set but here MyISAM key usage has a dramatic impact. Would be interesting to see the difference if the MyISAM data/key cannot be kept in memory any more.

SELECT SUM(column3) FROM table WHERE column1 = value AND column2 BETWEEN value AND value SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.072 sec) SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.002 sec)

As soon a MyISAM has an index with high cardinality and a strong filter it outperforms Column Store.

Let us go a step back to the query with the big range (99%). But forcing MyISAM to do a table access instead of an index-only-scan:

SQL> SELECT SUM(store_sales) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.117 sec) SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.133 sec)

Ohh! Here the MariaDB optimizer was clever enough to see that a full table scan is cheaper than accessing the index:

SQL> EXPLAIN SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | sales_fact | ALL | i_sales_time_id | NULL | NULL | NULL | 1078880 | Using where | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+

So MyISAM is only slightly slower than Column Store. But if we force MyISAM to use the index it becomes dramatically slow (about 9 times):

SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact FORCE INDEX (i_sales_time_id) WHERE time_id >= 400; 1 row in set (1.040 sec)

So here again: MariaDB Column store starts making fun if a huge amount of data is used...

SELECT SUM(column3) FROM table WHERE column1 = value OR column2 = value

We know that WHERE clauses with OR are always bad for the optimizer. So let us try this:

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.209 sec) MariaDB [(none)]> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM sales_fact 4995 (promotion_id,store_id,store_sales,unit_sales) 0 3163 0 0.172 132 TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1

Column Store has to touch 4 out of 8 column (50%) to get the result. How the Elapsed time is calculated I have to investigate some more...

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.873 sec) SQL> EXPLAIN SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | 1 | SIMPLE | sales_fact | index_merge | i_sales_promotion_id,i_sales_store_id | i_sales_promotion_id,i_sales_store_id | 4,4 | NULL | 706357 | Using union(i_sales_promotion_id,i_sales_store_id); Using where | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+

MyISAM tries to short cut by an INDEX MERGE operation which we know is not super fast but still better than a full table scan. But still MariaDB Column Store is about 4 times faster than MyISAM.

More complex SELECT queries from DWH benchmarks.

I borrowed these queries from Øystein's blog:

SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_cs.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; ERROR 1815 (HY000): Internal error: IDB-3012: Scalar filter and semi join are not from the same pair of tables. SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_myisam.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; 1 row in set (0.184 sec)

So some more complex queries seems not to work with Column Store yet. I have to figure out yet what to do in this case...

So let us try some more complex queries from Sergei:

SELECT SUM(store_sales) FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set, 1 warning (0.210 sec)

We get a warning when using JOINs:

SQL> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 9999 | Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-2651; BlocksTouched-2651; PartitionBlocksEliminated-0; MsgBytesIn-41KB; MsgBytesOut-74KB; Mode-Distributed | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Seems to be some statistics about how the query/join was executed...

SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set (0.412 sec)

With JOINs I was a bit more sceptic because of my former experience with NDB but it looks like also with JOINs Column Store outperforms MyISAM with already a small amount of data.

The table customer is a typical dimension table so this would probably be a candidate for hybrid approach (HTAP)?

Converting the customer table to MyISAM did NOT make it significantly slower:

1 row in set, 1 warning (0.215 sec) +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows CES UM - - - - - - 0.013 4093 BPS PM sf 4995 (customer_id,store_sales,unit_sales) 0 2636 0 0.182 132 HJS PM sf-c 4995 - - - - ----- - TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1
Converting the foodmart DWH schema to MariaDB Columns Store

Converting the existing foodmart schema to MariaDB Column Store was a bit cumbersome...

We used the normal mysqldump and replace the storage engine:

$ zcat foodmart_dump.sql.gz | sed 's/ENGINE=InnoDB/ENGINE=ColumnStore/' | mysql --user=root foodmart_cs ERROR 1178 (42000) at line 25: The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

The command ALTER TABLE ... ENGINE = ColumnStore seems to be a bit more flexible but it is horribly slow...

The foodmart dump optimized for MariaDB Column Store can be found here.

Problems between MariaDB server and Column Store dictionary SQL> RENAME TABLE customer TO customer_a; Query OK, 0 rows affected (0.137 sec) SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.018 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync) SQL> DROP TABLE customer; Query OK, 0 rows affected (0.014 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1146 (42S02): Table 'foodmart_cs.customer' doesn't exist SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.017 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync)

To fix this the following DDL command will help:

SQL> CREATE TABLE customer (id INT) ENGINE = ColumnStore COMMENT='SCHEMA SYNC ONLY'; Query OK, 0 rows affected (0.006 sec)

See also: Recovering from error "Cannot create table: table already exists (your schema is probably out-of-sync)"

Conclusion

MariaDB Column Store can outperform MyISAM already with a single-node set-up and a small dataset if you are choosing the right queries. It would be interesting to see the performance gains with a multi-node set-up and a huge data set. I was told that MariaDB Column Store makes sense from 100 Gibyte upwards... Our data set was about 100 Mibyte!

Taxonomy upgrade extras: mariadbdwhColumnStorequeryperformancemyisam

Create a single-node MariaDB ColumnStore test installation

Shinguz - Wed, 2021-05-19 19:37
Table of Contents

For a long time I wanted to investigate a bit more into the MariaDB ColumnStore architecture. Now I took the time to start with.

MariaDB ColumnStore is an interesting technology if you want to do reports (OLAP, DWH, BI) over only a few columns on a huge amount of rows and if you have only or mostly batch writes. These data are ideally spread (sharded) over several machines and the results where calculated on those different machines an aggregated. Because the data are stored in columns the locality of your data should be better and thus a more efficient caching (in-memory) should result in faster response times for you reporting queries.

MariaDB Column Store can be deployed as a single-node or a multi-node set-up. The first one is considered for testing, the later one for production purposes.

For setting up a testing system I have chosen a single-node set-up on a VirtualBox VM first.

MariaDB ColumnStore hardware requirements

There are different informations available about the minimal recommended hardware resources. The range lasts from 8 to 32 physical cores with 16 to 64 Gibyte of RAM, HDD disks are fine because the system is optimized for block streaming (sequential read and write). At least an 1 Gbit network is recommended. [ 1 ]

For our first test we rely on 1 virtual Machine with 4 Gibyte of RAM (possibly with 2 Gibyte of RAM it still would work?), 10 Gibyte of HDD disk space and 1 vCPU.

O/S (Linux) settings

Check Linux kernel settings:

$ sysctl -a | grep -e '^vm.swap' -e '^vm.vfs' -e '^net.core..mem_max' -e '^net.ipv4.tcp_.mem' -e '^net.ipv4.tcp_no_metrics' -e '^net.core.netdev_max' net.core.netdev_max_backlog = 1000 net.core.rmem_max = 212992 net.core.wmem_max = 212992 net.ipv4.tcp_no_metrics_save = 0 net.ipv4.tcp_rmem = 4096 131072 6291456 net.ipv4.tcp_wmem = 4096 16384 4194304 vm.swappiness = 60 vm.vfs_cache_pressure = 100

Check network speed:

$ ethtool enp0s9 | grep Speed Speed: 1000Mb/s

Persist the Linux kernel settings:

$ cat >/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf <<_EOF # # /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf # # minimize swapping vm.swappiness = 10 # optimize Linux to cache directories and inodes vm.vfs_cache_pressure = 10 # Increase the TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase the TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 # for 1 GigE, increase this to 2500 # for 10 GigE, increase this to 30000 net.core.netdev_max_backlog = 2500 _EOF

and make them active:

$ sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf

Disable AppArmor (for installation only?):

$ systemctl status apparmor $ systemctl stop apparmor $ systemctl disable apparmor $ aa-status

Configure Character Enconding (why exactly?):

$ localedef -i en_US -f UTF-8 en_US.UTF-8
Installation of MariaDB ColumnStore

The first problem I had was finding a good an reliable information about where to download the software. Finally I ended up here: MariaDB ColumnStore download. But MariaDB ColumnStore is the wrong tab. You have to choose the MariaDB Community Server tab.

If you prefer, the software can also be downloaded from the command line:

$ wget https://dlm.mariadb.com/1623874/MariaDB/mariadb-10.5.10/repo/ubuntu/mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar

and there are a lot of packages contained in this tar-ball:

$ tar xf mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar -rw-rw-r-- 1 oli oli 9059256 May 7 04:34 galera-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 5955264 May 7 04:34 galera-arbitrator-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 1698 May 7 04:35 InRelease -rw-rw-r-- 1 oli oli 149208 May 7 04:34 libmariadb3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3276 May 7 04:34 libmariadb3-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3248 May 7 04:34 libmariadbclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5130428 May 7 04:34 libmariadbd19_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6226020 May 7 04:34 libmariadbd-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 159556 May 7 04:34 libmariadb-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3452 May 7 04:34 libmariadb-dev-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 libmysqlclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6483096 May 7 04:34 mariadb-backup_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4672 May 7 04:35 MariaDB-C74CD1D8-public.asc -rw-rw-r-- 1 oli oli 1604684 May 7 04:34 mariadb-client-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3088 May 7 04:34 mariadb-client_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 784824 May 7 04:34 mariadb-client-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4428 May 7 04:34 mariadb-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 5811572 May 7 04:34 mariadb-plugin-columnstore_10.5.10-5.5.2+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 558288 May 7 04:34 mariadb-plugin-connect_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5216 May 7 04:34 mariadb-plugin-cracklib-password-check_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6172 May 7 04:34 mariadb-plugin-gssapi-client_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 7592 May 7 04:34 mariadb-plugin-gssapi-server_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 1187228 May 7 04:34 mariadb-plugin-mroonga_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 63820 May 7 04:34 mariadb-plugin-oqgraph_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3122524 May 7 04:34 mariadb-plugin-rocksdb_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 845428 May 7 04:34 mariadb-plugin-s3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 354276 May 7 04:34 mariadb-plugin-spider_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4562128 May 7 04:34 mariadb-server-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3216 May 7 04:34 mariadb-server_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 6932836 May 7 04:34 mariadb-server-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 mysql-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 42897 May 7 04:34 Packages -rw-rw-r-- 1 oli oli 2278 May 7 04:35 README -rw-rw-r-- 1 oli oli 816 May 7 04:34 Release -rw-rw-r-- 1 oli oli 833 May 7 04:35 Release.gpg -rwxrwxr-x 1 oli oli 1010 May 7 04:35 setup_repository*

Installation of MariaDB ColumnStore was straight forward:

$ apt-get install gnupg2 $ ./setup_repository $ apt-get update $ apt-get install mariadb-server mariadb-plugin-columnstore libjemalloc1 mariadb-backup mariadb-client
Configuration of MariaDB ColumnStore

The minimum recommended MariaDB configuration for ColumnStore looks like this:

$ cat >/etc/mysql/mariadb.conf.d/zz-fromdual.cnf <<_EOF # # /etc/mysql/mariadb.conf.d/zz-fromdual.cnf # [mariadb] log_error = mariadbd-error.log character_set_server = utf8 collation_server = utf8_general_ci columnstore_use_import_for_batchinsert = ALWAYS _EOF

In the MariaDB ColumnStore documentation you can find all other Server System Variables and Options (columnstore*).

Cross Engine Join User

For Cross Engine Joins a special user is required. These credentials are stored in the file /etc/columnstore/Columnstore.xml and can be queried as follows:

$ mcsGetConfig -v -a | grep Cross CrossEngineSupport.Host = 127.0.0.1 CrossEngineSupport.Port = 3306 CrossEngineSupport.User = root CrossEngineSupport.Password = CrossEngineSupport.TLSCA = CrossEngineSupport.TLSClientCert = CrossEngineSupport.TLSClientKey =

and changed as follows:

$ mcsSetConfig CrossEngineSupport Host 127.0.0.1 $ mcsSetConfig CrossEngineSupport Port 3306 $ mcsSetConfig CrossEngineSupport User cross_engine $ mcsSetConfig CrossEngineSupport Password secret

Caution: Querying the Cross Engine Join User password can be done by an non-privileged O/S user. Thus I consider this as a security relevant bug. This is known already since 2018 and should be fixed in version 5.6.1 :-( (MCOL-1175, MCOL-4714).

Start the ColumnStore Processes

Starting MariaDB ColumnStore after the configuration changes are quite intuitive:

$ systemctl restart mariadb $ systemctl restart mariadb-columnstore

After these commands the mariadbd and some ColumnStore processes are started:

$ ps aux | grep -e mysql -e VSZ USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND mysql 1380 0.0 2.4 1274896 99020 ? Ssl 11:20 0:00 /usr/sbin/mariadbd mysql 1499 0.0 0.4 267392 16904 ? Sl 11:21 0:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 0.0 0.2 530448 11648 ? Sl 11:21 0:00 /usr/bin/controllernode mysql 1522 0.0 0.2 2391288 10560 ? Sl 11:21 0:00 /usr/bin/PrimProc mysql 1545 0.0 0.3 300192 14116 ? Sl 11:21 0:00 /usr/bin/WriteEngineServer mysql 1560 0.0 0.2 212452 10188 ? Sl 11:21 0:00 /usr/bin/ExeMgr mysql 1571 0.0 0.4 342364 18704 ? Sl 11:21 0:00 /usr/bin/DMLProc mysql 1592 0.0 0.3 193640 12444 ? Sl 11:21 0:00 /usr/bin/DDLProc

It looks like each of those processes works already multi-threaded:

$ ps -eLf | grep -e mysql -e PID UID PID PPID LWP C NLWP STIME TTY TIME CMD mysql 1380 1 1380 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1381 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1382 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1383 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1384 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1385 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1389 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1391 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1392 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1407 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1499 1 1499 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1501 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1502 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1503 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1504 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 1 1507 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1567 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1573 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1580 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1581 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1596 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1522 1 1522 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1523 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1524 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1525 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1526 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1527 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1528 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1529 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1530 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1531 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1532 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1533 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1534 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1535 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1561 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1563 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1586 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1593 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1545 1 1545 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1545 1 1546 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1560 1 1560 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1562 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1564 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1565 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1572 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1577 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1571 1 1571 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1587 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1588 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1592 1 1592 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1594 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1595 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc

In the database you can run this SELECT query to check which MariaDB ColumnStore version is running:

SQL> SELECT plugin_name, plugin_status, plugin_type_version, plugin_library_version, plugin_license, plugin_maturity, plugin_auth_version FROM information_schema.plugins WHERE plugin_library LIKE 'ha_column%'; +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | plugin_name | plugin_status | plugin_type_version | plugin_library_version | plugin_license | plugin_maturity | plugin_auth_version | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | Columnstore | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_COLUMNS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_TABLES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_FILES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_EXTENTS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ 5 rows in set (0.002 sec)

or if the MariaDB ColumnStore is enabled at all with:

SQL> SHOW ENGINES; +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Columnstore | YES | ColumnStore storage engine | YES | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
Create the Cross Engine Join User in the database

The Cross Engine Join User specified above must also be created inside the database:

SQL> CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY 'secret'; SQL> CREATE USER 'cross_engine'@'localhost' IDENTIFIED BY 'secret'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'localhost';

This is basically all you have to do for a running single-node MariaDB ColumnStore installation.

MariaDB ColumnStore schemas

MariaDB ColunStore creates 3 new schemas in the database:

SQL> SHOW SCHEMAS; +---------------------+ | Database | +---------------------+ | calpontsys | | columnstore_info | | infinidb_querystats | | information_schema | | mysql | | performance_schema | +---------------------+

We will investigate later what they are used for.

Server System Variables and Server Status Variables

A detailed description of the Server System Variables (columnstore_*) can be found in the MariaDB documentation (old source: infinidb_*).

SQL> SHOW GLOBAL STATUS LIKE 'columnstore%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Columnstore_commit_hash | source | | Columnstore_version | 5.5.2 | +-------------------------+--------+ 2 rows in set (0.001 sec)
SQL> SHOW GLOBAL VARIABLES LIKE 'columnstore%'; +-------------------------------------------------+--------+ | Variable_name | Value | +-------------------------------------------------+--------+ | columnstore_cache_flush_threshold | 500000 | | columnstore_cache_inserts | OFF | | columnstore_compression_type | SNAPPY | | columnstore_decimal_scale | 8 | | columnstore_derived_handler | ON | | columnstore_diskjoin_bucketsize | 100 | | columnstore_diskjoin_largesidelimit | 0 | | columnstore_diskjoin_smallsidelimit | 0 | | columnstore_double_for_decimal_math | OFF | | columnstore_group_by_handler | ON | | columnstore_import_for_batchinsert_delimiter | 7 | | columnstore_import_for_batchinsert_enclosed_by | 17 | | columnstore_local_query | 0 | | columnstore_orderby_threads | 16 | | columnstore_ordered_only | OFF | | columnstore_replication_slave | OFF | | columnstore_select_handler | ON | | columnstore_select_handler_in_stored_procedures | ON | | columnstore_string_scan_threshold | 10 | | columnstore_stringtable_threshold | 20 | | columnstore_um_mem_limit | 0 | | columnstore_use_decimal_scale | OFF | | columnstore_use_import_for_batchinsert | ALWAYS | | columnstore_varbin_always_hex | OFF | +-------------------------------------------------+--------+ 24 rows in set (0.003 sec)

MariaDB ColumnStore also writes to the MariaDB error log:

$ cat mariadbd-error.log 210511 11:20:47 Columnstore: Started; Version: 5.5.2-2

The MariaDB ColumnStore configuration is stored in /etc/columnstore. Those files can be read by everybody on the system by default. So it possibly makes sense to prohibit access:

$ chown mysql: /etc/columnstore/* $ chmod o-rw /etc/columnstore/*

We have not seen yet an negative impact after this "hardening":

$ ll total 100 drwxr-xr-t 2 mysql mysql 4096 May 11 11:17 ./ drwxr-xr-x 78 root root 4096 May 11 10:12 ../ -rw-r----- 1 mysql mysql 19169 May 11 11:17 Columnstore.xml -rw-r----- 1 mysql mysql 19145 May 11 10:12 Columnstore.xml-20210511 -rw-rw---- 1 mysql mysql 19153 May 11 11:17 Columnstore.xml.columnstoreSave -rw-r----- 1 mysql mysql 15372 May 5 23:00 ErrorMessage.txt -rw-r----- 1 mysql mysql 5619 May 5 23:00 MessageFile.txt -rw-r----- 1 mysql mysql 7456 May 5 23:03 storagemanager.cnf
MariaDB Column Store on the file system

It is also interesting to know where on the filesystem the MariaDB ColumnStore files are located. By default they are located under /var/lib/columnstore (Note: Can they be located somewhere else and how?).

$ tree * data1 ├── 000.dir │   └── 000.dir │   ├── 003.dir │   │   ├── 233.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 004.dir │   │   ├── 000.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 018.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 007.dir │   │   ├── 209.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf │   │   └── 212.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 008.dir │   │   ├── 013.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 028.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 011.dir │   ├── 012.dir │   ├── 013.dir │   ├── 014.dir │   ├── 015.dir │   ├── 016.dir │   ├── 017.dir │   ├── 018.dir │   │   ├── 164.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   └── 019.dir │   ├── 000.dir │   │   └── 000.dir │   │   └── FILE000.cdf ... ... │   └── 221.dir │   └── 000.dir │   └── FILE000.cdf ├── bulkRollback ├── dbroot1-lock ├── systemFiles │   └── dbrm │   ├── BRM_savesA_em │   ├── BRM_savesA_vbbm │   ├── BRM_savesA_vss │   ├── BRM_savesB_em │   ├── BRM_savesB_vbbm │   ├── BRM_savesB_vss │   ├── BRM_saves_current │   ├── BRM_saves_em │   ├── BRM_saves_journal │   ├── BRM_saves_vbbm │   ├── BRM_saves_vss │   ├── oidbitmap │   ├── SMTxnID │   └── tablelocks └── versionbuffer.cdf local └── module storagemanager └── storagemanager-lock 688 directories, 353 files
Creating our first MariaDB ColumnStore table

For the very first tests we use our well known test table. But for MariaDB ColumnStore tables you should NOT use the test schema: ColumnStore tables should not be created in the mysql, information_schema, calpontsys or test databases. (Why not test?).
In other documents about MariaDB ColumnStore we can clearly see, that the one who was writing the article was using the test schema [ 2 ]. An other restriction is: ColumnStore stores all object names in lower case. So probably CamelCase table names will not work as expected or they will be converted to lower case.

SQL> SET SESSION default_storage_engine = ColumnStore; SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

Oppps! What is this? After some searching I found that MariaDB ColumnStore does not know indexes: There is no need for indexing. See also MCOL-1080. As such indexes typically used to optimize query access for row based systems do not make sense since selectivity is low for such queries.

After removing the Primary Key the next try:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Also AUTO_INCREMENT seems not to be supported:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

Hmmm. What is wrong now? Checking the MariaDB ColumnStore datatypes does not show any obvious problem. Also the the page ColumnStore Create Table was not helpful. Relying on intuition I tried this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); ERROR 1815 (HY000): Internal error: The default value is out of range for the specified data type.

Becoming stressed a bit now this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL ); ERROR 1815 (HY000): Internal error: Config::Config: error accessing config file /etc/columnstore/Columnstore.xml

OK. This makes sense. We were a bit to restrictive in the first run above when we were hardening the MariaDB ColumnStore configuration file.

After fixing this we tried again and it finally works...

Query OK, 0 rows affected (1.755 sec)

For comparison only: This CREATE TABLE DDL statement with InnoDB takes about 20 ms.

Migrating MariaDB (InnoDB, MyISAM, Aria) tables is not so straight forward as it could be. So the MariaDB ColumnStore seems to be still a bit picky! Interesting is, that if we look at the CREATE TABLE statement it is shown in a non usable form:

SQL> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=Columnstore DEFAULT CHARSET=utf8 1 row in set (0.001 sec)

See also: MCOL-4716, MCOL-1039 and MCOL-4039. This problem seems to be known since a while yet...

This also means that if you want to use mysqldump for migrating the table structure it will not work for restore without editing:

$ mysqldump --user=root --no-data --skip-lock-tables test > /tmp/test_structure_dump.sql
Querying and changing data in MariaDB ColumnStore

Note: This is NOT the preferred way how you should add a massive amount of data into MariaDB ColumnStore!

SQL> INSERT INTO test VALUES (1, 'some data', NULL); Query OK, 1 row affected (0.224 sec) SQL> INSERT INTO test VALUES (2, 'some data', NULL); Query OK, 1 row affected (0.137 sec) SQL> INSERT INTO test VALUES (3, 'some data', NULL); Query OK, 1 row affected (0.145 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 2 | some data | 2021-05-11 14:01:35 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 3 rows in set (0.104 sec) SQL> DELETE FROM test WHERE id = 2; Query OK, 1 row affected (0.194 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 2 rows in set (0.022 sec) SQL> UPDATE test SET data = 'new data' WHERE id = 3; Query OK, 1 row affected (0.222 sec) Rows matched: 1 Changed: 1 Warnings: 0 SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | new data | 2021-05-11 14:03:11 | +----+-----------+---------------------+ 2 rows in set (0.018 sec)

So basic functionality (INSERT, UPDATE, DELETE and SELECT) works. But as we can see, response times for these tiny data sets and single row DML commands are fare above as we are used with other MariaDB Storage Engines (InnoDB, Aria, MyISAM), which is expected... But at least it works. So this is not the best use case for MariaDB ColumnStore. And SELECT * FROM <table> is probably one of the worst things you can do to a ColumnStore in general.

MariaDB ColumnStore using sequences instead of AUTO_INCREMENT

Because AUTO_INCREMENT is not supported in MariaDB ColumnStore we try to use a SEQUENCE instead:

SQL> CREATE SEQUENCE test_s START WITH 5 INCREMENT BY 1; SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP(); Query OK, 1 row affected (1.403 sec) Records: 1 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4 rows affected (1.210 sec) Records: 4 Duplicates: 0 Warnings: 0

Querying the data shows that sequences work but not as expected. They are evaluated once per query and not once per row:

SQL> SELECT * FROM test; +----+---------------+---------------------+ | id | data | ts | +----+---------------+---------------------+ | 1 | Some data | 2021-05-19 16:58:09 | | 2 | Some data | 2021-05-19 16:58:14 | | 3 | Some data | 2021-05-19 16:58:18 | | 5 | Some new data | 2021-05-19 16:59:00 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | +----+---------------+---------------------+ 8 rows in set (0.028 sec)

And if we do this for many rows, we can suddenly see that this becomes much faster than with other MariaDB Storage Engines:

SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 131072 rows affected (1.221 sec) Records: 131072 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 262144 rows affected (2.236 sec) Records: 262144 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 524288 rows affected (2.266 sec) Records: 524288 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 1048576 rows affected (4.316 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 2097152 rows affected (8.367 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4194304 rows affected (15.472 sec) Records: 4194304 Duplicates: 0 Warnings: 0

To compare with InnoDB: About above 1 Mio rows seems to be the break even for MariaDB ColumnStore:

SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 1048576 rows affected (3.499 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 2097152 rows affected (10.100 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 4194304 rows affected (21.766 sec) Records: 4194304 Duplicates: 0 Warnings: 0
MariaDB Column Store Query Execution Plan

If we want to see how the queries are executed the usual EXPLAIN command does not help much:

SQL> EXPLAIN SELECT * FROM test; +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+

Instead we should do it in MariaDB ColumnStore like this:

SQL> SELECT calSetTrace(1); +----------------+ | calSetTrace(1) | +----------------+ | 0 | +----------------+ SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.090 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 69 44 0 0.066 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

If we run the query a second time we can see that the physical I/O (PIO) has been reduced, so everything comes out of memory:

SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.021 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 0 44 0 0.000 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

See also: Query execution and Viewing the ColumnStore query plan.

Taxonomy upgrade extras: dwhColumnStoreData Martmariadbreporting

FromDual Ops Center for MariaDB, MySQL and compatible databases 1.1.0 has been released

Shinguz - Mon, 2021-05-17 17:09

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

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

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

Download

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

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB, MySQL and compatible databases 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 1.1.0

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 1.1.0

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

Changes in Ops Center 1.1.0 Machine
  • Machine check error messages improved.

Instance
  • Function check_instance made more robust against system outages.
  • Safe account error message improved.
  • Instance restart prepared.
  • read_only in repo check added.
  • Old function removed.
  • Replication check moved from cluster check to instance check.
  • Instance checks for IO and SQL thread added.
  • Function testNode renamed to testInstance.
  • Code clean-up and fix in instance check.
  • Bug in account creation fixed for MariaDB 10.1 and MariaDB 10.0.
  • Instance check improved.
  • Target info was added in edit instance.
  • Check master_log_slave_updates fixed.
  • Function: chanageVariabelsInConfiguatrion added.
  • Functions makeInstanceReadOnly and makeInstanceReadWrite redesigned and cleaned-up.
  • Instance.inc renamed to TargetOperations.inc
  • Database.inc renamed to Target.inc
  • log_slave_updates check added to instance.

Cluster
  • Failover bugs fixed, error messages improved.
  • m/s cluster failover.
  • Change master to can now copy masters file and pos to change master to command.
  • Functionality switchover added.
  • failover_slave_id renamed to failover_instance_id, back button in cluster made correct, check on stopped instance fixed.
  • Cluster operations simplified and A_I inc and A_I offset added.
  • Adding and deleting cluster fixed.
  • read_only is also deployed during failover.
  • Final error failover fixed and log_slave_status check added to cluster.
  • Show cluster overview fixed.
  • Function getMasterInfo fixed for MySQL and MariaDB.
  • Missing read_only check on old master was added.
  • Failover took wrong user.
  • Cluster failover for mm cluster fixed.
  • Cluster operations made more colorful to show important operations better.
  • Function checkVipUser made ready for 3 node cluster.
  • New master is set correctly now.
  • Added more information for failover.
  • M/s and m/m failover should work now.
  • Error messages made more proper.
  • Bug in readonly in repo check fixed.
  • active_master_id added.
  • Failover vip in cluster only if vip is present.
  • Reattach slaves implemented.
  • Failover slave feature implemented.
  • Cluster was only shown if VIP was there, fixed
  • One test only true for m/m was also done on m/s, fixed.
  • Check instance should not interfere with cluster failover any more.
  • read_only and activity added to cluster operations view.
  • Cluster failover is moved from VIP to cluster.
  • Cluster operations simplified.
  • Cluster failover implemented.
  • Failover rules moved from vip to cluster.
  • Cluster test finished.
  • m/m a/a and a/p introduced.
  • Cluster vip check added.
  • Unique server_id check implemented for cluster.
  • Slave read_only in repo check implemented for repl cluster.
  • Slave activity on cluster implemented.
  • Cluster tests for m/s and m/m properly improved.
  • Replication check moved from cluster check to instance check.
  • Slave is shown in cluster overview.
  • Cluster checks improved and checks display made unique.
  • VIP is shown in replication cluster.
  • Bug in cluster handling and usability improved.
  • HTML code clean-up.
  • Some clean-up and M/M cluster fixed.

Load-Balancer
  • No changes.

Virtual IP (VIP)/Floating IP
  • Some VIP cosmetics fixed.
  • Amount of error messages reduced during vip start.
  • Bug in check for new VIP fixed.
  • VIP add check improved.
  • Bug in VIP check fixed, various smaller code clean-up.
  • VIP failover improved.
  • Arping command not found behaviour improved.
  • VIP code cleaned-up.
  • VIP interface check added.
  • VIP nonsense interface already used check removed.
  • Column of primary and failover machine added to VIP overview.
  • Edit VIP machines are sorted in drop down.
  • Key shortcuts added to VIP buttons and html made nicer according to our standards.

Tools
  • Error logging in file transfer improved.
  • Crontab title changed from hr to hour.
  • Crontab error 3800 relaxed to warning.
  • Temporay crontab is now cleaned-up to not leave any remains.
  • Bug in tools function fixed.
  • File transfer bugs fixed.
  • Run Crontab Entry now was implemented.
  • Adding Crontab entries with keyboard shortcuts is possible now.
  • Bug in Crontab with htmlspecialcharacters fixed.
  • Jobs: Error messages made more verbose in regular jobs.
  • Jobs: Jobs are sorted by start_ts DESC now.
  • Jobs: Made job deletion dramatically faster.
  • Jobs: Clean-up job remaining.
  • Jobs: Button in Job Tool provisioned with keys.
  • Jobs: Job checks improved.
  • Jobs: Os_user added to job. This means job can be started under every user now!
  • Jobs: Special characters from crontab inherited to jobs fixed.
  • Jobs: More verbose error messages in job state preparing.
  • Jobs: Job gets a start timestamp as soon as it moves to state Preparing.
  • Jobs: Code cleaned-up and some bugs fixed.
  • Jobs: Function getJobsWithFilter replaces by readJobs.
  • Jobs: Function addJob replaced by createJob.
  • Crontab: User in crontab can now be chosen, but not in jobs yet!
  • Crontab: Special characters from crontab inherited to jobs fixed.
  • Crontab: Only log output when error returned, not OK.

Configuration
  • Configuration: If focmm crontab is not activated complaints are shown on prominent place.
  • Configuration: Delete focmm crontab added.
Database-as-a-Service (DBaaS)
  • No changes.

Building and Packaging
  • Files directory added to package build instructions to make file copy work.
  • rpmlint warning removed.
  • Some lintian warnings implemented.
  • rpmlint warning removed.

Themes / UI
  • Nasty CSS error removed.

General
  • myEnv lib updated.
  • myEnv library updated.
  • Documentation improved and code cosmetics fixed.
  • Various minor bugs fixed.
  • CHANGELOG updated, license key changed.
  • myEnv library updated.
  • Library jquery updated from 3.5.0 to 3.6.0.
  • CHANGELOG updated.
  • Return codes made unique.
  • Some minor bugs fixed.
  • Status in menu on the left fixed.
  • PoC added.
  • Compare tables PoC added.
  • Smaller code fixes.
  • Copyright updated from 2020 to 2021.
  • Better error message, GUI test improved.
  • Minor bug fixed and typos fixed.
  • Function run remote command improved.
  • Wording improved.
  • Accesskey added to SubmitButton and ContinueButton.
  • Constant LOG_ERROR added.
  • ssh function run on remote server optimized.
  • Typos fixed and page titles made more precise.

Repository
  • Node.inc renamed to Catalog.inc.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaasfocmm

Limiting MySQL tmpdir size

Shinguz - Thu, 2021-04-29 17:37

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the file system as volume and mount it in a way you can use it as an separate mount point for your tmpdir directory. So in case your application runs amok it just fills up your tmpdir volume/file and not the whole / (root) filesystem.

# fallocate -l 4G /mysql-tmpdir # mkfs.ext4 /mysql-tmpdir # mke2fs 1.44.1 (24-Mar-2018) Discarding device blocks: done Creating filesystem with 1048576 4k blocks and 262144 inodes Filesystem UUID: 74c51e5c-bed8-4a7d-8f1b-e89669726e1d Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736 Allocating group tables: done Writing inode tables: done Creating journal (16384 blocks): done Writing superblocks and filesystem accounting information: done # grep loop /proc/mounts # mount | grep loop # mkdir /var/lib/mysql-tmpdir # mount -o loop=/dev/loop0 /mysql-tmpdir /var/lib/mysql-tmpdir # df -h | grep -e mysql-tmpdir -e Size Filesystem Size Used Avail Use% Mounted on /dev/loop0 3.9G 16M 3.7G 1% /var/lib/mysql-tmpdir # umount /var/lib/mysql-tmpdir # chown -R mysql: /var/lib/mysql-tmpdir

Add the entry to your /etc/fstab:

/mysql-tmpdir /var/lib/mysql-tmpdir ext4 loop 0 0

Test the fstab entry:

# mount /mysql-tmpdir # sudo -u mysql touch /var/lib/mysql-tmpdir/test

Configure your MySQL database accordingly (my.cnf):

tmpdir = /var/lib/mysql-tmpdir

and after database restart:

SQL> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | tmpdir | /var/lib/mysql-tmpdir | +---------------+-----------------------+

Then create some implicit temporary tables on disk:

# ls -la drwx------ 2 mysql mysql 16K Apr 29 16:41 lost+found -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAD' -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAI'

and check if tmpdir is really capped at 4 Gibyte:

# dd if=/dev/zero of=/var/lib/mysql-tmpdir/tmp bs=1M count=5000 dd: error writing '/var/lib/mysql-tmpdir/tmp': No space left on device 3731+0 records in 3730+0 records out 3912126464 bytes (3.9 GB, 3.6 GiB) copied, 13.708 s, 285 MB/s
Literature
Taxonomy upgrade extras: myisamtmpdirtemporary table

Pages

Subscribe to FromDual aggregator - MySQL Tech-Feed (en)