MariaDB 10.5 and Memory Instrumentation in Performance Schema - Basic Checks

My MariaDB 10.5-related talk at upcoming MariaDB Server Fest is already announced, so I have no other option but to continue writing blog posts while preparing for it. 

In the previous one we enabled memory instrumentation and tried to run some basic queries again the performance_schema.memory_summary_global_by_event_name table. Now I'd like to use it for checking how much memory is allocated to the Performance Schema itself and when this allocation happens. For this I am starting 10.5 with performance_schema and memory instrumentation enabled:

openxs@ao756:~/dbs/maria10.5$ bin/mysqld_safe --no-defaults --port=3311 --socket=/tmp/mariadb105.sock --performance_schema --performance-schema-instrument='memory/%=ON' &
[4] 19144
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show global variables like 'performance_schema';;select version(), count(*) from performance_schema.setup_instruments where name like 'memory%' and enabled='yes'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
+----------------+----------+
| version()      | count(*) |
+----------------+----------+
| 10.5.6-MariaDB |      270 |
+----------------+----------+

Let's start the initial memory usage for allocations related to the performance_schema:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+----------+------+----------+
| alloc    | free | used     |
+----------+------+----------+
| 97903648 |    0 | 97903648 |
+----------+------+----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 97902160

So we know that now with mostly default settings 10.5 uses a bit more than 93M of memory We can also note a difference in the sum of memory allocated according to the memory_summary_global_by_event_name table and the total memory from show engine performance_schema status output. The difference is resent in MySQL 5.7 as well:

openxs@ao756:~/dbs/5.7$ bin/mysqld_safe --no-defaults --port=3310 --socket=/tmp/mysql57.sock --performance_schema=1 --performance-schema-instrument='memory/%=ON' &
[5] 20459
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 137641608 |    0 | 137641608 |
+-----------+------+-----------+
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 137640120
openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select 137641608 - 137640120"
+-----------------------+
| 137641608 - 137640120 |
+-----------------------+
|                  1488 |
+-----------------------+

The difference is the same, 1488 bytes, so MariaDB probably inherited the problem that I've reported as Bug #100624 - "Total memory allocation for P_S is different in different sources".

Let's also get top 5 memory users:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC alloc, SUM_NUMBER_OF_BYTES_FREE free, CURRENT_NUMBER_OF_BYTES_USED used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%' order by 2 desc limit 5"
+------------------------------------------------------------------------------+----------+------+----------+
| EVENT_NAME                                                                   | alloc    | free | used     |
+------------------------------------------------------------------------------+----------+------+----------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name  | 10457088 |    0 | 10457088 |
| memory/performance_schema/memory_summary_by_thread_by_event_name             |  5898240 |    0 |  5898240 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name    |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name    |  5228544 |    0 |  5228544 |
+------------------------------------------------------------------------------+----------+------+----------+

Now let's add some load, 4 threads in a simple sysbench test:

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=4 --time=10 --mysql-socket=/tmp/mariadb105.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
...
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 109110496 |    0 | 109110496 |
+-----------+------+-----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 109108816
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select 109110496 - 97903648, 109108816 - 97902160"
+----------------------+----------------------+
| 109110496 - 97903648 | 109108816 - 97902160 |
+----------------------+----------------------+
|             11206848 |             11206656 |
+----------------------+----------------------+

We can see that after the load total memory usage by the performance_schema increased, based on both sources! Moreover, both sources report different values and different increase, but it's still around 11M. If we try more threads:

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=34 --time=10 --mysql-socket=/tmp/mariadb105.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
...
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 109258144 |    0 | 109258144 |
+-----------+------+-----------+
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 109256272
openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select 109258144 - 109110496"
+-----------------------+
| 109258144 - 109110496 |
+-----------------------+
|                147648 |
+-----------------------+

we see some additional, small increase in total memory allocated and used. Repeating the load with the same concurrency does not change memory allocated. Top 5 memory users now are a bit different:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"select EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC alloc, SUM_NUMBER_OF_BYTES_FREE free, CURRENT_NUMBER_OF_BYTES_USED used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%' order by 2 desc limit 5"
+------------------------------------------------------------------------------+----------+------+----------+
| EVENT_NAME                                                                   | alloc    | free | used     |
+------------------------------------------------------------------------------+----------+------+----------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name  | 10457088 |    0 | 10457088 |
| memory/performance_schema/table_handles                                      |  9502720 |    0 |  9502720 |
| memory/performance_schema/memory_summary_by_thread_by_event_name             |  5898240 |    0 |  5898240 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name |  5228544 |    0 |  5228544 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name    |  5228544 |    0 |  5228544 |
+------------------------------------------------------------------------------+----------+------+----------+

Note that memory/performance_schema/table_handles allocation had increased dynamically and the second row now. 

These dynamic allocations are expected based on fine MySQL manual:

"The Performance Schema dynamically allocates memory incrementally, scaling its memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated, it is not freed until the server is restarted."

Now back to the table we've used. We do not have any nice comments for the columns:

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb105.sock -e"show create table performance_schema.memory_summary_global_by_event_name\G"            *************************** 1. row ***************************
       Table: memory_summary_global_by_event_name
Create Table: CREATE TABLE `memory_summary_global_by_event_name` (
  `EVENT_NAME` varchar(128) NOT NULL,
  `COUNT_ALLOC` bigint(20) unsigned NOT NULL,
  `COUNT_FREE` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_ALLOC` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_FREE` bigint(20) unsigned NOT NULL,
  `LOW_COUNT_USED` bigint(20) NOT NULL,
  `CURRENT_COUNT_USED` bigint(20) NOT NULL,
  `HIGH_COUNT_USED` bigint(20) NOT NULL,
  `LOW_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
  `CURRENT_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
  `HIGH_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Current MariaDB KB article on the table does NOT add much value onm top of the above. So, we have to rely on MySQL manual for explanations of columns' semantics and basic usage principles.

I am trying to focus on features, but end up reporting bugs and problems...

To summarize:

• In MariaDB 10.5 (same as in MySQL 5.7+) memory for performance_schema is allocated not only at startup, but also dynamically at runtime and is never returned back to the system. So we have one more potential "memory leak" to care about.
• Total memory used is reported differently by SHOW ENGINE... and memory_summary_global_by_event_name table. This is a bug.
  
• By default MariaDB 10.5 seems to allocate less memory than MySQL 5.7 for Performance Schema in total. See my old report MDEV-20216 also.
  
• So far we mostly have to rely on fine MySQL 5.7 manual if the details on memory instrumentation are needed.
  

MariaDB 10.5 and Memory Instrumentation in Performance Schema - First Steps

Chances are high for me to speak about MariaDB 10.5 at the upcoming MariaDB Server Fest Online Conference (as my related talk was accepted), so it's time to start writing about it. In this post I am going to do basic checks of the new and long wanted Performance Schema memory instrumentation feature finally ported from MySQL 5.7. Until recently we mostly had to use OS level tools for memory instrumentation, like perf or heap profilers etc.

As usual (see here and there) when I am serious about working with some open source software and formally supporting it, I start with checking how to build it from GitHub source on my own hardware and/or VMs. This time I've tried the usual steps to build MariaDB 10.5.5 on my gold old Ubuntu 16.04 netbook (from fc -l output):

1984     cd git/server/
1985     git branch
1986     git checkout 10.5
1987     git pull
1988     git submodule update --init --recursive
1989     rm CMakeCache.txt
1990     cd build/
1991     rm -rf *
1992     cmake .. -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=OFF -DENABLED_LOCAL_INFILE=1 -DWITH_JEMALLOC=system -DWITH_INNODB_DISALLOW_WRITES=ON -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.5
1993     time make -j 3
...
2001     rm -rf /home/openxs/dbs/maria10.5
2002     make install && make clean
2003     cd /home/openxs/dbs/maria10.5
...
2005     scripts/mysql_install_db --no-defaults
2006     bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mariadb.sock --performance_schema=ON &

I am using out of source build in a separate build subdirectory and clean up the remaining of older builds just in case. Options used are typical for this box. I do not disable the TokuDB engine explicitly as it's no longer included anyway.  

Then I removed previous instance, initialized the database and started it as usual, via mysqld_safe with explicit option to enable Performance Schema as it's disabled in MariaDB by default. Note these details though:

openxs@ao756:~/dbs/maria10.5$ ls -l bin/mysqld
lrwxrwxrwx 1 openxs openxs 8 сер  9 14:04 bin/mysqld -> mariadbd
openxs@ao756:~/dbs/maria10.5$ ls -l bin/mariadbd
-rwxr-xr-x 1 openxs openxs 213124184 сер  9 13:57 bin/mariadbd
openxs@ao756:~/dbs/maria10.5$ ls -l ../maria10.4/bin/mysqld
-rwxr-xr-x 1 openxs openxs 178385776 сер  5 19:18 ../maria10.4/bin/mysqld
openxs@ao756:~/dbs/maria10.5$ ls -l ../8.0/bin/mysqld
-rwxr-xr-x 1 openxs openxs 749000640 лип 13 21:35 ../8.0/bin/mysqld

All the mysql* binaries are now symbolic links to mariadb* ones. Also note the relative sizes of the non-stripped binaries comparing to MariaDB 10.4 and MySQL 8.0.21 (that one is impressive).

Now I can try to connect and check what new memory-related settings and tables are available (note that I had to log in as OS user who started the server, as Unix socket authentication plugin is installed and used by default for by default):

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb.sock test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.5-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for openxs@localhost                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `openxs`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,000 sec)

MariaDB [test]> select table_name from information_schema.tables where table_schema='performance_schema' and table_name like 'memory%';
+-----------------------------------------+
| table_name                              |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+
5 rows in set (0,001 sec)

MariaDB [test]> select count(*) from performance_schema.setup_instruments where name like '%memory%' and enabled='yes';
+----------+
| count(*) |
+----------+
|       70 |
+----------+
1 row in set (0,003 sec)

MariaDB [test]> select count(*) from performance_schema.setup_instruments where name like '%memory%';
+----------+
| count(*) |
+----------+
|      270 |
+----------+
1 row in set (0,003 sec)

MariaDB [test]> select * from performance_schema.setup_instruments where name like '%memory%' and enabled='yes';
+--------------------------------------------------------------------------------+---------+-------+
| NAME                                                                           | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/mutex_instances                                      | YES     | NO    |
| memory/performance_schema/rwlock_instances                                     | YES     | NO    |
...
| memory/performance_schema/prepared_statements_instances                        | YES     | NO    |
| memory/performance_schema/scalable_buffer                                      | YES     | NO    |
+--------------------------------------------------------------------------------+---------+-------+
70 rows in set (0,003 sec)

We have the expected 5 summary tables and some 270(!)  new instruments, of then 70 related to performance_schema itself are enabled by default. Note that MySQL 5.7.30, for example, has more memory instruments:

openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select version(), count(*) from performance_schema.setup_instruments where name like 'memory%'"
+-----------+----------+
| version() | count(*) |
+-----------+----------+
| 5.7.30    |      376 |
+-----------+----------+

 We can check InnoDB memory instrumentation and then enable everything for a quick test:

MariaDB [test]> select * from performance_schema.setup_instruments where name like '%memory%innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
...
| memory/innodb/ut0mem                      | NO      | NO    |
| memory/innodb/ut0new                      | NO      | NO    |
| memory/innodb/ut0pool                     | NO      | NO    |
| memory/innodb/ut0rbt                      | NO      | NO    |
| memory/innodb/ut0wqueue                   | NO      | NO    |
| memory/innodb/xtrabackup                  | NO      | NO    |
+-------------------------------------------+---------+-------+
72 rows in set (0,003 sec)

MariaDB [test]> update performance_schema.setup_instruments set enabled = 'yes', timed = 'yes' where name like '%memory%';
Query OK, 270 rows affected (0,003 sec)
Rows matched: 270  Changed: 270  Warnings: 0

My main initial source of information is this table, summartising memory usage globally:

MariaDB [test]> desc performance_schema.memory_summary_global_by_event_name;
+------------------------------+---------------------+------+-----+---------+-------+
| Field                        | Type                | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+-------+
| EVENT_NAME                   | varchar(128)        | NO   |     | NULL    |       |
| COUNT_ALLOC                  | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_FREE                   | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NUMBER_OF_BYTES_ALLOC    | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NUMBER_OF_BYTES_FREE     | bigint(20) unsigned | NO   |     | NULL    |       |
| LOW_COUNT_USED               | bigint(20)          | NO   |     | NULL    |       |
| CURRENT_COUNT_USED           | bigint(20)          | NO   |     | NULL    |       |
| HIGH_COUNT_USED              | bigint(20)          | NO   |     | NULL    |       |
| LOW_NUMBER_OF_BYTES_USED     | bigint(20)          | NO   |     | NULL    |       |
| CURRENT_NUMBER_OF_BYTES_USED | bigint(20)          | NO   |     | NULL    |       |
| HIGH_NUMBER_OF_BYTES_USED    | bigint(20)          | NO   |     | NULL    |       |
+------------------------------+---------------------+------+-----+---------+-------+
11 rows in set (0,002 sec)

MariaDB [test]> select event_name,
    ->        sum_number_of_bytes_alloc,
    ->        high_number_of_bytes_used
    -> from
    ->   performance_schema.memory_summary_global_by_event_name
    -> where current_count_used > 0
    -> order by 2 desc;
+--------------------------------------------------------------------------------+---------------------------+---------------------------+
| event_name                                                                     | sum_number_of_bytes_alloc | high_number_of_bytes_used |
+--------------------------------------------------------------------------------+---------------------------+---------------------------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name    |                  10457088 |                  10457088 |
| memory/performance_schema/memory_summary_by_thread_by_event_name               |                   5898240 |                   5898240 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name      |                   5228544 |                   5228544 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name      |                   5228544 |                   5228544 |
...
| memory/sql/dboptions_hash                                                      |                      1088 |                      1088 |
| memory/sql/user_var_entry                                                      |                       512 |                       512 |
| memory/sql/THD::db                                                             |                         8 |                         8 |
| memory/sql/MPVIO_EXT::auth_info                                                |                         8 |                         8 |
+--------------------------------------------------------------------------------+---------------------------+---------------------------+
71 rows in set (0,002 sec)

Obviously memory is mostly used for the Performanc e Schema itself so far. Now let's run some sysbench test and check what memory usage is reported after it:

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=1 --mysql-socket=/tmp/mariadb.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua prepare
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=4 --time=30 --interval=2 --mysql-socket=/tmp/mariadb.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)
...
[ 2s ] thds: 4 tps: 588.22 qps: 9423.03 (r/w/o: 8244.59/0.00/1178.44) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 4 tps: 629.50 qps: 10081.05 (r/w/o: 8822.04/0.00/1259.01) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 4 tps: 604.08 qps: 9672.34 (r/w/o: 8464.17/0.00/1208.17) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 4 tps: 617.97 qps: 9882.99 (r/w/o: 8647.05/0.00/1235.94) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
...

After the test is completed I tried the following query:

MariaDB [test]> select event_name, high_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name where event_name not like '%performance_schema%' order by 2 desc limit 10;
                                            
+----------------------------------------------+---------------------------+
| event_name                                   | high_number_of_bytes_used |
+----------------------------------------------+---------------------------+
| memory/innodb/mem0mem                        |                   9377611 |
| memory/sql/thd::main_mem_root                |                   4286600 |
| memory/innodb/row_merge_sort                 |                   3145728 |
| memory/innodb/row0merge                      |                   2097316 |
| memory/memory/HP_PTRS                        |                   1016352 |
| memory/sql/Prepared_statement::main_mem_root |                    590400 |
| memory/sql/TABLE                             |                    525608 |
| memory/innodb/std                            |                    399192 |
| memory/innodb/row0log                        |                    131474 |
| memory/sql/TABLE_SHARE::mem_root             |                     42272 |
+----------------------------------------------+---------------------------+
10 rows in set (0,003 sec)

The result was surprising to me, as I do not sdee thye InnoDB buffer pool memory usage above. But this is yet another case of Performance Schema instrument that is NOT dynamic. I've reported a bug about these years ago, see Bug #68097 - "Manual does not explain that some P_S instruments must be enabled at startup".

So, I restarted with all memory instruments enabled and reppeated the test:

MariaDB [test]> shutdown;
Query OK, 0 rows affected (0,001 sec)

MariaDB [test]> exit
Bye
[1]+  Done                    bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mariadb.sock --performance_schema=ON

openxs@ao756:~/dbs/maria10.5$ bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mariadb.sock --performance_schema=ON --performance-schema-instrument='memory/%=ON' &
[1] 29502
openxs@ao756:~/dbs/maria10.5$ 200809 14:53:59 mysqld_safe Logging to '/home/openxs/dbs/maria10.5/data/ao756.err'.
200809 14:53:59 mysqld_safe Starting mariadbd daemon with databases from /home/openxs/dbs/maria10.5/data

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb.sock -e"select count(*) from performance_schema.setup_instruments where name like 'memory%' and enabled='yes'"
+----------+
| count(*) |
+----------+
|      270 |
+----------+

openxs@ao756:~/dbs/maria10.5$ sysbench --table-size=1000000 --threads=4 --time=30 --report-interval=2 --mysql-socket=/tmp/mariadb.sock --mysql-user=openxs --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0-faaff4f (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 2 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 2s ] thds: 4 tps: 606.19 qps: 9715.08 (r/w/o: 8500.70/0.00/1214.39) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 4 tps: 641.03 qps: 10262.98 (r/w/o: 8980.92/0.00/1282.06) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00
...

openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mariadb.sock test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.5-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select event_name, high_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name where event_name not like '%performance_schema%' and high_number_of_bytes_used > 0 order by 2 desc;
+----------------------------------------------+---------------------------+
| event_name                                   | high_number_of_bytes_used |
+----------------------------------------------+---------------------------+
| memory/innodb/buf_buf_pool                   |                 134217728 |
| memory/sql/XID                               |                  19922944 |
| memory/innodb/ut0pool                        |                   4194480 |
| memory/innodb/hash0hash                      |                   2484000 |
| memory/innodb/os0event                       |                   2120064 |
| memory/memory/HP_PTRS                        |                   1016352 |
| memory/sql/Prepared_statement::main_mem_root |                    590400 |
| memory/sql/TABLE                             |                    351264 |
...
| memory/sql/MDL_context::acquire_locks        |                        16 |
| memory/sql/NAMED_ILINK::name                 |                        16 |
| memory/sql/ignored_db                        |                         8 |
+----------------------------------------------+---------------------------+
70 rows in set (0,003 sec)

Now to line in the outut is exactly the one I've expected. As for the rest, some would require additional efforts to find out what this memory is used for. In conclusion I'd like to use a nice query from this useful blog post to summarise memory usage per "subsystem":

MariaDB [test]> select substring_index(substring_index(event_name, '/', 2), '/', -1) as event_type,
    -> round(sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) as MB_CURRENTLY_USED
    -> from performance_schema.memory_summary_global_by_event_name
    -> group by event_type
    -> having MB_CURRENTLY_USED>0;
+--------------------+-------------------+
| event_type         | MB_CURRENTLY_USED |
+--------------------+-------------------+
| innodb             |            136.95 |
| memory             |              0.21 |
| performance_schema |            104.06 |
| sql                |              0.71 |
+--------------------+-------------------+
4 rows in set (0,003 sec)

Basically, with all instrumentation enabled you can now use Performance Schema in MariaDB 10.5 to find and study memory usdage and leaks, same as in MySQL 5.7+.

Let's check the harvest, what was grown up under the name of MariaDB 10.5 GA...

To summarize:

• MariaDB 10.5 is real, you can build it from GitHub source and it works, including new features memory monitoring tables in Performance Schema. Just make sure to properly enable the instrumentation at startup.
• The public documentation at the moment is very limited or does not exist, so we have to rely on fine MySQL manual (that also misses too many details) etc. I've created this task asking to add documentation for memory* (or all) instruments.
  
• It's yet to be checked if any known related Performance Schema bugs in MySQL are inherited by MariaDB 10.5.
• It's yet to be determined what memory instruments from recent MySQL 5.7.x are missing in recent MariaDB 10.5.x and what are added, and why. Looks like many from the check below:

openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select name from performance_schema.setup_instruments where name like 'memory%' order by name" > /tmp/mysql57.txt
openxs@ao756:~/dbs/5.7$ bin/mysql --socket=/tmp/mariadb.sock -e"select name from performance_schema.setup_instruments where name like 'memory%' order by name" > /tmp/mariadb.txt
openxs@ao756:~/dbs/5.7$ diff -u /tmp/mysql57.txt /tmp/mariadb.txt > /tmp/ps_memory.txt
openxs@ao756:~/dbs/5.7$ more /tmp/ps_memory.txt
--- /tmp/mysql57.txt    2020-08-09 17:30:27.199204590 +0300
+++ /tmp/mariadb.txt    2020-08-09 17:30:31.459270994 +0300
@@ -1,22 +1,12 @@
 name
-memory/archive/FRM
-memory/archive/record_buffer
-memory/blackhole/blackhole_share
-memory/client/MYSQL
-memory/client/MYSQL_DATA
-memory/client/MYSQL_HANDSHAKE
-memory/client/mysql_options
-memory/client/MYSQL_RES
-memory/client/MYSQL_ROW
-memory/client/MYSQL_STATE_CHANGE_INFO
 memory/csv/blobroot
 memory/csv/row
 memory/csv/tina_set
 memory/csv/TINA_SHARE
 memory/csv/Transparent_file
 memory/innodb/adaptive hash index
-memory/innodb/api0api
 memory/innodb/btr0btr
+memory/innodb/btr0buf
...
openxs@ao756:~/dbs/5.7$ cat /tmp/ps_memory.txt | grep '^+'
+++ /tmp/mariadb.txt    2020-08-09 17:30:31.459270994 +0300
+memory/innodb/btr0buf
+memory/innodb/fil0crypt
+memory/innodb/fts0blex
+memory/innodb/fts0file
+memory/innodb/fts0tlex
+memory/innodb/sync0start
+memory/innodb/trx0seg
+memory/innodb/ut0new
+memory/innodb/xtrabackup
+memory/partition/Partition_admin
+memory/partition/Partition_share
+memory/partition/partition_sort_buffer
openxs@ao756:~/dbs/5.7$

So, stay tuned! More blog posts on MariaDB 10.5 Performance Schema improvements are to be expected.