I'm working on archiving a bunch of tables in an environment where archiving was never done, with some data going back 10 years. I've written a script to perform the work, which loops through the primary key (an autoincrement bigint) *n* rows at a time, calling a procedure to archive the data to a separate table and then deleting that same data from the main table. I'm doing it in small batches to prevent any long term locking of the main tables. It also sleeps in between each loop iteration. Batch size and sleep time are configurable via a config file. On my test system, for this table, I'm using a batch size of 1000 and a sleep time of 0. Instance class is r7g.4xl. Most tables archive at several thousand rows per second, which is acceptable. But I have one table whose archiving is going very slowly; averaging under 550 rows/sec. There is no other activity in the database (there are other archives running against other DBs in the cluster at the same time, but killing them didn't improve the performance of this one). Here's the table schema (the schema for the archive table is identical):

CREATE TABLE inbox_item (
  id bigint NOT NULL AUTO_INCREMENT,
  user_id bigint NOT NULL,
  template_id bigint NOT NULL,
  url varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  created_at datetime NOT NULL,
  hash varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  parameters varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hash_uidx (hash),
  KEY template_id_idx (template_id),
  KEY user_id_created_at_idx (user_id,created_at)
) ENGINE=InnoDB AUTO_INCREMENT=442872663 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Note that while there are two large varchars, total actual data width is under 300 bytes. Here's the procedure that's being called:

CREATE DEFINER=root@% PROCEDURE archive_inbox_item_proc(IN pkmin bigint, IN pkmax bigint, IN querymax bigint)
begin
        declare exit handler for sqlexception
        begin
                get diagnostics condition 1
                    @err = MYSQL_ERRNO, @msg = MESSAGE_TEXT;
                select -1;
                select concat('Error ', cast(@err as char), ': ',@msg) 'Error';
                rollback;
        end;

        start transaction;
        insert ignore into inbox.inbox_item_archive select arctable.* from inbox.inbox_item as arctable  where created_at = pkmin and arctable.id = pkmin and arctable.id < querymax and arctable.id <= pkmax ;
        select row_count();
        commit;
end

pkmin is always the actual minimum pkey value. There are no foreign keys or triggers referencing the table. Here's the table status:

Name: inbox_item
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 299879061
 Avg_row_length: 243
    Data_length: 72988737536
Max_data_length: 0
   Index_length: 126937300992
      Data_free: 45770342400
 Auto_increment: 442872663
    Create_time: 2025-03-28 06:15:36
    Update_time: 2025-08-05 18:04:55
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:

Any ideas on what's causing this to run so slow relative to other tables in other databases?

Running MySql database Ver (8.x) on Windows IIS on AWS EC2. The application is for aviation industry, so I have used the **point** data type for location fields, assuming that I would store latitude/longitude in it. I don't anticipate doing Geometry calculations, all they need to know is the distance between two points. They will probably trust some official source for that. I am unaware of how this data type actually works. What is one advantage to retaining **Point**?

I am using Mysql 8.0 in Laragon on Windows 10. Apparently, I did something which made MySQL stop working. Tried to restart several times but it's showing this same error again and again. 2021-12-15T08:24:35.815298Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2021-12-15T08:24:35.815417Z 0 [System] [MY-010116] [Server] D:\laragon\bin\mysql\mysql-8.0.13-winx64\bin\mysqld (mysqld 8.0.13) starting as process 14852 mysqld: File '.\binlog.000327' not found (OS errno 2 - No such file or directory) 2021-12-15T08:24:42.400102Z 0 [ERROR] [MY-010958] [Server] Could not open log file. 2021-12-15T08:24:42.400850Z 0 [ERROR] [MY-010041] [Server] Can not init tc log 2021-12-15T08:24:42.401518Z 0 [ERROR] [MY-010119] [Server] Aborting 2021-12-15T08:24:43.574538Z 0 [System] [MY-010910] [Server] D:\laragon\bin\mysql\mysql-8.0.13-winx64\bin\mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL. I do not understand why it's showing. So I looked into the directory. Clearly the binlog.000327 file is missing. Now on google search, I could not find any solution on how to resolve this specific problem. **Should I copy binlog.000326 and rename it binlog.000327? Will it work?** I'm skeptical of doing this because it's a binary file and it might cause other issues such as database data loss. **Or is there a proper fix to this solution?**

Long story short: I have the buffer pool size = 40G. I have 64GB RAM on my server. But MySQL actually uses 73G of memory (53G physical and the rest swapped). MySQL version: 8.0.22-0ubuntu0.20.04.2 How is that possible? And how to deal with it? I'd like MySQL to not swap, I think this degrages it's performance significantly. Just a note: I'm using MySQL in a non-standard way, there are no "users", there are just couple of scripts running which perform very heavy calculations, most of queries run several hours. My mysqld.cnf:

sync_binlog=0
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=10G
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=10G
innodb_lock_wait_timeout=18000 #5 hours
innodb_deadlock_detect=0
innodb_online_alter_log_max_size=512M
max_heap_table_size=2G
tmp_table_size=2G
max_allowed_packet=1G

ft_min_word_len=1
ft_stopword_file=''
innodb_ft_min_token_size=1
innodb_ft_enable_stopword='OFF'
innodb_ft_result_cache_limit=4000000000

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION'

binlog_expire_logs_seconds=115000 #1.5 days
wait_timeout=86400 #24 hours
event_scheduler='OFF'
transaction_isolation='READ-UNCOMMITTED'
range_optimizer_max_mem_size=0

SHOW ENGINE INNODB STATUS:

=====================================
2023-01-06 09:03:14 0x7f3b5454f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5817581 srv_active, 0 srv_shutdown, 1176515 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2481543293
OS WAIT ARRAY INFO: signal count 2225015700
RW-shared spins 2256999997, rounds 3189111343, OS waits 930003374
RW-excl spins 2051400118, rounds 48471954436, OS waits 1345519963
RW-sx spins 2536334, rounds 3679029, OS waits 15196
Spin rounds per wait: 1.41 RW-shared, 23.63 RW-excl, 1.45 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-12-21 14:40:46 0x7f45e015e700 Transaction:
TRANSACTION 7045075024, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2964075, OS thread handle 139938089002752, query id 350171612 10.0.0.4 root updating
delete from users where id = 12
Foreign key constraint fails for table jokii.matching_actions:
,
  CONSTRAINT matching_actions_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id)
Trying to delete in parent table, in index PRIMARY tuple:
DATA TUPLE: 11 fields;
 0: len 8; hex 000000000000000c; asc         ;;
 1: len 6; hex 0001a3eb5050; asc     PP;;
 2: len 7; hex 0200001a561e07; asc     V  ;;
 3: len 10; hex 4d617463686572363636; asc Matcher666;;
 4: SQL NULL;
 5: len 60; hex 2432792431302445326e484c77516a312f70742f504651786463676a2e303054734b35495a337a326a316b50576939312f756f72393167766842742e; asc $2y$10$E2nHLwQj1/pt/PFQxdcgj.00TsK5IZ3z2j1kPWi91/uor91gvhBt.;;
 6: len 60; hex 473877353072535a363456334755694e486663763156355570756244525a774d46417a66344b516c63654c774c7049453730444552414f4f65474a6a; asc G8w50rSZ64V3GUiNHfcv1V5UpubDRZwMFAzf4KQlceLwLpIE70DERAOOeGJj;;
 7: len 1; hex 02; asc  ;;
 8: len 1; hex 81; asc  ;;
 9: len 4; hex 63a31ad0; asc c   ;;
 10: len 4; hex 63a31ad0; asc c   ;;

But in child table jokii.matching_actions, in index matching_actions_user_id_foreign, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 000000000000000c; asc         ;;
 1: len 8; hex 0000000000010f66; asc        f;;

------------
TRANSACTIONS
------------
Trx id counter 7136759163
Purge done for trx's n:o = 7136654785, sees < 7136654785
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 85252
22562121354 OS file reads, 1883479287 OS file writes, 354203769 OS fsyncs
1 pending preads, 0 pending pwrites
10835.12 reads/s, 16384 avg bytes/read, 21.00 writes/s, 9.48 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 15702, free list len 939386, seg size 955089, 152372692 merges
merged operations:
 insert 494772961, delete mark 1167112219, delete 295481119
discarded operations:
 insert 19938, delete mark 1, delete 1
Hash table size 10624987, node heap has 51992 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 11 buffer(s)
Hash table size 10624987, node heap has 15 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 2 buffer(s)
Hash table size 10624987, node heap has 13876 buffer(s)
607081.07 hash searches/s, 161045.98 non-hash searches/s
---
LOG
---
Log sequence number          143119571682666
Log buffer assigned up to    143119571682666
Log buffer completed up to   143119571682666
Log written up to            143119571682666
Log flushed up to            143119571682278
Added dirty pages up to      143119571682666
Pages flushed up to          143117988865165
Last checkpoint at           143117988865165
1177812297 log i/o's done, 6.95 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 43956305920
Dictionary memory allocated 1494388
Buffer pool size   2621440
Free buffers       28
Database pages     2555398
Old database pages 943378
Modified db pages  4050
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 184186084759, not young 878129585715
0.00 youngs/s, 0.01 non-youngs/s
Pages read 22562341496, created 30581445, written 547935573
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 1 / 1000 not 109 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2555398, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   655360
Free buffers       27
Database pages     638847
Old database pages 235844
Modified db pages  837
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46030684170, not young 220509855311
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5453016045, created 7695472, written 136263915
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 1 / 1000 not 120 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638847, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size   655360
Free buffers       1
Database pages     638884
Old database pages 235857
Modified db pages  966
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46060288135, not young 222302083098
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5787215064, created 7628619, written 134915374
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 1 / 1000 not 126 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638884, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size   655360
Free buffers       0
Database pages     638767
Old database pages 235814
Modified db pages  1065
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 45912035006, not young 218035144334
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5544539793, created 7640673, written 134899528
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 94 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638767, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size   655360
Free buffers       0
Database pages     638900
Old database pages 235863
Modified db pages  1182
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46183077448, not young 217282502972
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5777570594, created 7616681, written 141856756
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 1 / 1000 not 101 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638900, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Process ID=795, Main thread ID=139890893248256 , state=sleeping
Number of rows inserted 630429294, updated 2109116770, deleted 293525232, read 16421762044116
3.17 inserts/s, 0.07 updates/s, 0.00 deletes/s, 3431194.61 reads/s
Number of system rows inserted 197979, updated 1398, deleted 197627, read 592382
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

SHOW GLOBAL STATUS: https://pastebin.com/raw/HWRKtpgC

I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree . Specifically, the question states as follows: > You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N. Both columns have datatype as integer. Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node: > > - Root: If node is root node. > - Leaf: If node is leaf node. > - Inner: If node is neither root nor leaf node. Here is my attempt so far:

SELECT N,  
CASE  
    WHEN bst_outer.P IS NULL THEN "Root"  
    WHEN NOT EXISTS(SELECT bst_inner.N FROM BST bst_inner WHERE bst_inner.P = bst_outer.P) THEN "Leaf"  
    ELSE 'Inner'  
END   
FROM BST bst_outer;

The output after running the above query gives incorrect result, as there appears to be no nodes with Leaf classification. It seems like that the error lies on my second WHEN statement, but I am not sure how to fix it.

I have 2 tables with below structures. question table id, audio, s_id 1, ad1, 500 2, ad2, 500 3, ad3, 500 answers table where q_id is FK of question table. id, s_id, q_id, number, answer 1, 500, 1, 088888801, 1 1, 500, 3, 088888801, 3 1, 500, 1, 090078601, 2 1, 500, 2, 090078601, 4 1, 500, 3, 090078601, 2 Lets take this example for better understanding of above structure. I have generated phone calls on 2 numbers (090078601, 088888801), They both have picked up the call. On call, There will be total of 3 questions that will play. As you can see the answer table, Number 088888801 has answered question 1 and 3, he has skip question number 2, if user skip a question, there will be no entry in answer table. But number 090078601 has answered all 3 questions. I want to fetch all record but if a user has skipped the answer, I want to show that this number has skipped that question, Here is my query SELECT r.number, GROUP_CONCAT(COALESCE(r.answer, 'skip')) as answer FROM questions LEFT JOIN answers ON r.q_id= q.id WHERE q.s_id= 500 group by r.number order by q.id ASC This query returns me below | number | answer | | -------- | ----- | | 088888801 | 1,3 | | NULL | NULL | | 090078601 | 2,4,2 | What I actually want is | number | answer | | -------- | ----- | | 088888801 | 1,'skip', 3 | | 090078601 | 2,4,2 | NOTE: I want an optimized query because There can be around 24 questions and more than 200000 numbers, If each number answers 24 questions there will be 4.8M records in answer table. This is just for one S_ID, There will be more records for other s_id, But I want query only on s_id because s_id is unique for each survey that runs.

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative name. All names are ranked and the one with the highest ranking-position shall be used. Unranked names are “0” or “-1” and should be ignored and since the name-ranking-system is bad, the one to use is not always “1”. In the case of no name being there, the main entry should still be returned. In short: I need a left join that takes all entries of table “main” and joins them with the name that has the smallest, greater than 0, position, if there is one. main:

| main_ID | val_A | val_B |
+---------+-------+-------+
|       2 | some  | stuff |
|       3 | and   | more  |
|       4 | even  | more  |

names:

| name_ID | main_ID | name           | position |
+---------+---------+----------------+----------+
|       1 |       2 | best name      |        1 |
|       2 |       2 | some name      |        0 |
|       3 |       3 | alt name       |        3 |
|       4 |       2 | cool name      |        2 |
|       5 |       3 | abandoned name |       -1 |
|       6 |       3 | awesome name   |        2 |

what I want to get:

| main_ID | val_A | val_B | name         |
+---------+-------+-------+--------------+
|       2 | some  | stuff | best name    |
|       3 | and   | more  | awesome name |
|       4 | even  | more  |              |

We have a Windows Server 2022 with **56Gb** RAM. This server is dedicated to MySQL DB and during idle it has ~**50.9Gb** available memory. But while bulk inserting rows (200k rows) into a table from an application I'm facing the following error. Unfortunately, application does not support batch inserts yet.

Message: MySql.Data.MySqlClient.MySqlException (0x80004005): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

Initially, I tried with default configuration then changed configuration after searching online for the issue and still facing the same error. As insertion query is executed, memory usage increases from ~**4Gb** to **55Gb**+ with MySQL service consuming ~**53Gb**. Following are the configurations set in my.ini

[mysqld]

default-storage-engine=INNODB

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

max_connections=200

thread_stack=100M

table_open_cache=10000

temptable_max_ram=5G

tmp_table_size=5G

internal_tmp_mem_storage_engine=TempTable

myisam_max_sort_file_size=2146435072

myisam_sort_buffer_size=6G

key_buffer_size=2048M

read_buffer_size=256M

read_rnd_buffer_size=512M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=256M

innodb_buffer_pool_size=35G

innodb_redo_log_capacity=100M

innodb_thread_concurrency=13

innodb_autoextend_increment=64

innodb_buffer_pool_instances=16

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

innodb_lock_wait_timeout=50

innodb_flush_method=normal

flush_time=300

join_buffer_size=256K

max_allowed_packet=1G

max_connect_errors=100

open_files_limit=10000

sort_buffer_size=4G

binlog_row_event_max_size=8K

sync_source_info=10000

sync_relay_log=10000

mysqlx_port=33060

upon checking online for solution, I increased innodb_buffer_pool_size and key_buffer_size . I also increased thread_stack size. Please suggest If I'm missing any configuration or what configurations need to be adjusted to not get the error when bulk insert (~1M+ rows). Thanks.

I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case. MySQL version is 8.0.31. This is the query: SELECT COUNT(distinct(u.Id)) FROM Users AS u LEFT JOIN Bots ON u.Id = Bots.UserId WHERE Bots.UserId IS NULL; Essentially, bots is a separate table of userIDs with some additional bot-specific columns. The query is finding out how many users are not bots. Schema:

------------------------------------------------------------------------------+
| Bots  | CREATE TABLE Bots (
  UserId varchar(26) NOT NULL,
  Description text,
  OwnerId varchar(190) DEFAULT NULL,
  LastIconUpdate bigint DEFAULT NULL,
  ... truncated
  PRIMARY KEY (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

| Users | CREATE TABLE Users (
  Id varchar(26) NOT NULL,
  CreateAt bigint DEFAULT NULL,
  UpdateAt bigint DEFAULT NULL,
  DeleteAt bigint DEFAULT NULL,
  Username varchar(64) DEFAULT NULL,
  Password varchar(128) DEFAULT NULL,
  AuthData varchar(128) DEFAULT NULL,
  AuthService varchar(32) DEFAULT NULL,
  Email varchar(128) DEFAULT NULL,
  EmailVerified tinyint(1) DEFAULT NULL,
  Nickname varchar(64) DEFAULT NULL,
  FirstName varchar(64) DEFAULT NULL,
  LastName varchar(64) DEFAULT NULL,
  Position varchar(128) DEFAULT NULL,
   ... truncated
  PRIMARY KEY (Id),
  UNIQUE KEY Username (Username),
  UNIQUE KEY AuthData (AuthData),
  UNIQUE KEY Email (Email),
  KEY idx_users_update_at (UpdateAt),
  KEY idx_users_create_at (CreateAt),
  KEY idx_users_delete_at (DeleteAt),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Users.Id is varchar(26) and a PK. For Bots, UserId is PK with same varchar(26). Ignore the distinct in the query for now. We don't actually need that and the problem at hand is unrelated to it. With that out of the way, let's get into the problem. The system has around 100K rows in Users and 10 rows in Bots. Most of the times, the query finishes in couple of ms. Here is an EXPLAIN output of the good case

Aggregate: count(distinct u.Id) (cost=71155.80 rows=1) (actual time=463.397..463.397 rows=1 loops=1)
    -> Filter: (bots.UserId is null) (cost=58476.70 rows=126791) (actual time=0.051..234.996 rows=131491 loops=1)
        -> Nested loop antijoin (cost=58476.70 rows=126791) (actual time=0.050..223.681 rows=131491 loops=1)
            -> Covering index scan on u using idx_users_update_at (cost=14099.85 rows=126791) (actual time=0.038..34.614 rows=131496 loops=1)
            -> Single-row covering index lookup on Bots using PRIMARY (UserId=u.Id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=0 loops=131496)

You can see that it converts the LEFT JOIN with IS NULL into an antijoin and takes around 400ms, and around 200ms is going into calculating the distinct. All good here. But here is the case with the bad execution:

Aggregate: count(distinct u.Id) (cost=71154.70 rows=1) (actual time=10298.826..10298.826 rows=1 loops=1)
    -> Filter: (bots.UserId is null) (cost=58475.80 rows=126789) (actual time=0.045..9853.564 rows=131489 loops=1) 
        -> Nested loop antijoin (cost=58475.80 rows=126789) (actual time=0.044..9840.607 rows=131489 loops=1) 
            -> Covering index scan on u using idx_users_update_at (cost=14099.65 rows=126789) (actual time=0.035..151.337 rows=131494 loops=1) 
            -> Single-row covering index lookup on Bots using PRIMARY (UserId=u.Id) (cost=0.25 rows=1) (actual time=0.074..0.074 rows=0 loops=131494)

You can see that the execution plan is exactly the same, but somehow the nested loop antijoin is now taking 9.8s rather than 200ms: actual time=0.044..9840.607. I have been scratching my head to find an explanation but unable to. And even more interestingly, this is the query that actually fixes the problem: EXPLAIN ANALYZE SELECT COUNT(distinct(u.Id)) FROM Users AS u WHERE u.Id NOT IN (SELECT UserId from Bots);

---+
| -> Aggregate: count(distinct u.Id)  (cost=203038.61 rows=1) (actual time=339.482..339.482 rows=1 loops=1)
    -> Nested loop antijoin  (cost=119428.11 rows=836105) (actual time=0.170..103.207 rows=154346 loops=1)
        -> Covering index scan on u using idx_users_update_at  (cost=19095.51 rows=167221) (actual time=0.092..30.890 rows=154351 loops=1)
        -> Single-row index lookup on  using  (UserId=u.Id)  (actual time=0.000..0.000 rows=0 loops=154351)
            -> Materialize with deduplication  (cost=1.25..1.25 rows=5) (actual time=0.056..0.056 rows=5 loops=1)
                -> Filter: (Bots.UserId is not null)  (cost=0.75 rows=5) (actual time=0.019..0.030 rows=5 loops=1)
                    -> Covering index scan on Bots using PRIMARY  (cost=0.75 rows=5) (actual time=0.017..0.027 rows=5 loops=1)

Effectively, this is being translated into the same thing isn't it? The sub-query gets materialized into a table, and then antijoined into Users again. But with this query, the runtime is always within the 300-400ms range and never regresses. What gives? Is this a known quirk of MySQL that I am unaware of?

New user here, excuse me if that isn't grammatically correct, but this is what I want to do - I have a server set-up with multiple sites on one VM instance - I want to create an admin mysql database which only the account holder has access to and multiple "child" databases for each individual site on the instance which do not have root user but can be accessed via the parent/admin user. Is there a way to do this?

ps ax | grep mysql 38737 pts/4 R+ 0:00 grep --color=auto mysql [root@ ~]# chown mysql:mysql -R /var/lib/mysql/* [root@ ~]# chmod 755 -R /var/lib/mysql/* [root@ ~]# service mysql restart Redirecting to /bin/systemctl restart mysql.service Failed to restart mysql.service: Unit mysql.service not found. [root@ ~]# mysql > ERROR 2002 (HY000): Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (111) [root@ ~]# sudo pkill mysql [root@ ~]# sudo pkill mysqld [root@ ~]# sudo service mysql restart Redirecting to /bin/systemctl restart mysql.service Failed to restart mysql.service: Unit mysql.service not found. [root@ ~]# sudo systemctl restart mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details. [root@ ~]# sudo systemctl status mysqld ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-12-27 19:02:07 UTC; 7s ago Process: 39028 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS) Process: 39004 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=1/FAILURE) Main PID: 33163 (code=killed, signal=KILL) Dec 27 19:02:07 j systemd: mysqld.service: Failed with result 'exit-code'. Dec 27 19:02:07 j systemd: Failed to start MySQL 8.0 database server. Dec 27 19:02:07 j systemd: mysqld.service: Service RestartSec=100ms expired, scheduling restart. Dec 27 19:02:07 j systemd: mysqld.service: Scheduled restart job, restart counter is at 5. Dec 27 19:02:07 j systemd: Stopped MySQL 8.0 database server. Dec 27 19:02:07 j systemd: mysqld.service: Start request repeated too quickly. Dec 27 19:02:07 j systemd: mysqld.service: Failed with result 'exit-code'. Dec 27 19:02:07 j systemd: Failed to start MySQL 8.0 database server. [root@ ~]# sudo systemctl start mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running). I'm not sure if this is the right thing to do, but I used mysqlslap to compare the performance before and after the config alterations. As advised by mysqltuner, I raised innodb_buffer_pool_size and innodb_redo_log_capacity values, but it seems that the performance haven't changed (average time to run mysqlslap queries 0.386 seconds, and 0.387 seconds after configuration changes). Shouldn't the queries be faster with a innodb_buffer_pool_size almost 40x bigger? What's the correct way to measure the before/after performance? What am I doing wrong? Any advices? This was my original /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
user            = mysql
bind-address            = *
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 8M
max_allowed_packet      = 128M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log                = 1
slow_query_log_file   = /var/log/mysql/mysql-slow.log
long_query_time = 2
max_binlog_size   = 100M

log_timestamps=SYSTEM
skip_name_resolve=ON

I tested using mysqlslap:

mysqlslap --concurrency=100 --iterations=20 --number-int-cols=30 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-execute-number=10

That resulted:

Benchmark
        Average number of seconds to run all queries: 0.386 seconds
        Minimum number of seconds to run all queries: 0.345 seconds
        Maximum number of seconds to run all queries: 0.531 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

Then I runned ./mysqltuner.pl, with the results:

>>  MySQLTuner 2.2.7
         * Jean-Marie Renouard 
         * Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Using mysql to check login
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 4.4G (Tables: 942)
[OK] Total fragmented tables: 0

[OK] Currently running supported MySQL version 8.0.33-0ubuntu0.22.04.4

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (986B)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 MB
[!!] Log file /var/log/mysql/error.log isn't readable.

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 30m 4s (74K q [41.427 qps], 2K conn, TX: 14G, RX: 62M)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 704.7M
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 1.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 248M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 613.2M (1.91% of installed RAM)
[OK] Maximum possible memory usage: 704.7M (2.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/74K)
[OK] Highest usage of available connections: 68% (103/151)
[OK] Aborted connections: 0.00% (0/2704)
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 4% (122 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 159
[OK] Temporary tables created on disk: 0% (0 on disk / 1K total)
[!!] Thread cache hit rate: 31% (1K created / 2K connections)
[OK] Table cache hit rate: 94% (69K hits / 73K requests)
[OK] table_definition_cache (2000) is greater than number of tables (1271)
[OK] Open file limit used: 0% (3/10K)
[OK] Table locks acquired immediately: 100% (182 immediate / 182 locks)
[OK] Binlog cache memory access: 99.99% (15178 Memory / 15180 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 248.8M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :8.0M
[--]  +-- KB Used Size :1.5M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 4.4G
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (75%): 100.0M / 128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.95% (10397117 hits / 10402401 total)
[!!] InnoDB Write Log efficiency: 77.34% (100765 hits / 130280 total)
[OK] InnoDB log waits: 0.00% (0 waits / 29515 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    thread_cache_size (> 9)
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_redo_log_capacity should be (=32M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

On running ./tuning-primer.sh the results were:

-- MYSQL PERFORMANCE TUNING PRIMER --
      - By: Matthew Montgomery -

MySQL Version 8.0.33-0ubuntu0.22.04.4 x86_64

Uptime = 0 days 0 hrs 33 min 13 sec
Avg. qps = 40.26
Total Questions = 80240
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 

Visit https://github.com/BMDan/tuning-primer.sh  for the latest version of
this script, or to suggest improvements.

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
Since startup, 0 out of 80261 queries have taken longer than  to complete.
Your long_query_time seems reasonable.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html 

WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 8
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 103
The number of used connections is 68% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 749 M
Current InnoDB data space = 3.64 G
Current InnoDB buffer pool free = 25 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 348 M
Configured Max Per-thread Buffers : 287 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 439 M
Physical Memory : 31.30 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
No key reads.  If you aren't using MyISAM, this is normal.  If you are
using MyISAM, this is very, very bad.
Current MyISAM index space = 0 bytes
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Your server does not support the query cache.  That's probably a good thing.

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 187 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 1090 tables
You have 2109 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1244 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 84 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 80525
Your table locking seems to be fine

Based on mysqltuner and tuning-primer results, I added these lines in the end of /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size=5G
innodb_redo_log_capacity=1250M

Restarted with sudo systemctl restart mysql.service, and verified the values:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5368709120 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_redo_log_capacity | 1310720000 |
+--------------------------+------------+
1 row in set (0.01 sec)

And runned mysqlslap again:

Benchmark
        Average number of seconds to run all queries: 0.387 seconds
        Minimum number of seconds to run all queries: 0.349 seconds
        Maximum number of seconds to run all queries: 0.552 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

In MYSQL, I have a select query which is usually fetch the data from 4M records. There I'm using like operator with % and % this is not using indexing filter and reading total 4M records to fetch 2 records. How can I optimize it. SELECT r_ss.siteId, r_ss.referrer, r_ss.supplierhash, min(r_ss.bid_floor) as bid_floor, sum(r_ss.adCall_yesterday) as adCall_yesterday, sum(r_ss.adCall_today) as adCall_today FROM reporting.total_data_for_site r_ss WHERE (r_ss.adCall_today > 1 OR r_ss.adCall_yesterday > 100) AND CASE WHEN ( '1367,1397,1624' IS NOT NULL AND '1367,1397,1624' '' ) THEN r_ss.siteId IN ( SELECT * FROM tmp_site ) ELSE 1 = 1 END AND r_ss.deviceid in (38,39,41) AND r_ss.referrer LIKE CONCAT('%','watchfree', '%') group by r_ss.siteId,r_ss.referrer ;

I have two MySQL NDB community clusters 8.0.30, each with 3 VMs Cluster#1_VM1: mysqld + ndb_mgmd Cluster#1_VM2: mysqld + ndb Cluster#1_VM3: mysqld + ndb Cluster#2_VM1: mysqld + ndb_mgmd Cluster#2_VM2: mysqld + ndb Cluster#2_VM3: mysqld + ndb I have bidirectional replication between Cluster#1_VM2 and Cluster#2_VM2. VM2/3 have identical parameters. The choice of such architecture might not be perfect but I think it was not relevant here. mysqld process consumes a lot of memory on VMs with replication. And replication seems to be the only difference between those VMs. (Though I'm not quite sure how is load distributed among mysqld processes) I would assume that ndbd would be the one to use most of the RAM since it has 8GB of DataMemory. But somehow mysqld is also utilizing a lot of RAM too. I've checked several other questions here (e.g. https://dba.stackexchange.com/questions/286946/high-memory-usage-on-replica-mysql-8 ) Most of them are related to innodb and the queries for e.g. buffer utilization show nothing that could show me where that high memory utilization comes from. So far I've checked - buffer utilizations for innoDB (I have NDB, I know) - engine status for innodb and ndb - no hint of high mem usage, or at least not clearly visible All I know for now is that it grows over time after restart of the process. But don't know yet how to limit/control the amount of memory consumed by mysqld in this case.

I am experiencing an issue with MySQL memory usage increasing over time to a point where OOM Killer kills the MySQL process. I have 3 servers configured with Group Replication (1 Primary and 2 secondary). The servers are running on AWS EC2: m5.2xlarge (32GB RAM, 8vCPU,500GB Amazon EBS Storage) Debian 11 MySQL 8.0.36 Link to additional requested information: https://justpaste.it/f0f4n Updated additional information after 44 hours of server running: https://justpaste.it/6v4pl The memory usage issue seems to occur faster on a server that is the PRIMARY server in the cluster but does affect all servers. I have been trying, without success, to figure out what the cause of this could be. The system memory usage for the MySQL process and the listed memory allocation in MySQL do not correspond. *The additional memory usage does seem to be related to HIGH_NUMBER_OF_BYTES_USED of "memory/innodb/ut0rbt". It seems that this memory accounts for the reported memory discrepancy. In all instances where the memory is growing, this reported information is growing as well.* Below is the output of top - As you can see, the current physical RAM in use is by MySQL is 27.0G Tasks: 118 total, 1 running, 117 sleeping, 0 stopped, 0 zombie %Cpu(s): 13.4 us, 0.5 sy, 0.0 ni, 85.3 id, 0.5 wa, 0.0 hi, 0.2 si, 0.0 st MiB Mem : 31650.4 total, 263.6 free, 27895.9 used, 3490.9 buff/cache MiB Swap: 4096.0 total, 0.1 free, 4095.9 used. 3302.6 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 757962 mysql 20 0 33.3g 27.0g 0 S 55.4 87.3 1104:36 mysqld Below is the memory usage reported by MySQL: mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;

+---------------------------------------------------------------+---------------+
| event_name                                                    | current_alloc |
+---------------------------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                                    | 8.18 GiB      |
| memory/group_rpl/GCS_XCom::xcom_cache                         | 1023.71 MiB   |
| memory/innodb/log_buffer_memory                               | 256.00 MiB    |
| memory/temptable/physical_ram                                 | 94.00 MiB     |
| memory/performance_schema/events_statements_summary_by_digest | 40.28 MiB     |
| memory/mysys/KEY_CACHE                                        | 32.00 MiB     |
| memory/innodb/ut0link_buf                                     | 24.00 MiB     |
| memory/sql/TABLE                                              | 23.30 MiB     |
| memory/innodb/lock0lock                                       | 22.22 MiB     |
| memory/innodb/memory                                          | 21.93 MiB     |
+---------------------------------------------------------------+---------------+
10 rows in set (0.01 sec)

mysql> select format_bytes(sum(current_alloc)) from sys.x$memory_global_by_current_bytes;

+----------------------------------+
| format_bytes(sum(current_alloc)) |
+----------------------------------+
| 9.95 GiB                         |
+----------------------------------+
1 row in set (0.00 sec)

Below is a report on what my expected memory usage would be based on the configuration of the server.

+------------------------------------------+--------------------+
|                          key_buffer_size |          32.000 MB |
|                         query_cache_size |           0.000 MB |
|                  innodb_buffer_pool_size |        8192.000 MB |
|          innodb_additional_mem_pool_size |           0.000 MB |
|                   innodb_log_buffer_size |         256.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |        8480.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.250 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.250 MB |
|                             thread_stack |           1.000 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          16.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          17.906 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                129 |
|                          max_connections |                300 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |       10789.906 MB |
|                              TOTAL (MAX) |       13851.875 MB |
+------------------------------------------+--------------------+

I have a suspicion that the memory usage is somehow related to the memory/innodb/ut0rbt based on looking at the HIGH_NUMBER_OF_BYTES_USED being 16GB in the below report. I cannot find much information on memory/innodb/ut0rbt when searching and am not sure where to go to from here. Any guidance would be appreciated. SELECT * FROM performance_schema.memory_summary_global_by_event_name where SUM_NUMBER_OF_BYTES_ALLOC !=0 ORDER BY HIGH_NUMBER_OF_BYTES_USED DESC;

+--------------------------------------------------------------------------------+-------------+-------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| EVENT_NAME                                                                     | COUNT_ALLOC | COUNT_FREE  | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+--------------------------------------------------------------------------------+-------------+-------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| memory/innodb/ut0rbt                                                           | 12367972599 | 12367971756 |             1175789585128 |            1175789498761 |              0 |                843 |       168276872 |                        0 |                        86367 |               16869347952 |
| memory/innodb/buf_buf_pool                                                     |          64 |           0 |                8783134720 |                        0 |              0 |                 64 |              64 |                        0 |                   8783134720 |                8783134720 |
| memory/temptable/physical_ram                                                  |       18599 |       18501 |               26578851040 |              26476087456 |              0 |                 98 |             506 |                        0 |                    102763584 |                1634745920 |
| memory/mysys/TREE                                                              |       13933 |       13930 |                4655917920 |               4655878864 |              0 |                  3 |            1568 |                        0 |                        39056 |                1119816488 |
| memory/group_rpl/GCS_XCom::xcom_cache                                          |      767889 |      300297 |                1872249111 |                798535661 |              0 |             467592 |          495763 |                        0 |                   1073713450 |                1075974241 |
| memory/innodb/memory                                                           |   532219874 |   532207809 |            13596646725344 |           13596623477632 |              0 |              12065 |           87170 |                        0 |                     23247712 |                 889900184 |
| memory/sql/String::value                                                       |   179746709 |   179745073 |              186307408232 |             186301810472 |              0 |               1636 |            8326 |                        0 |                      5597760 |                 444524496 |
| memory/innodb/log_buffer_memory                                                |           1 |           0 |                 268436464 |                        0 |              0 |                  1 |               1 |                        0 |                    268436464 |                 268436464 |
| memory/sql/Unique::merge_buffer                                                |          36 |          36 |                 301991328 |                301991328 |              0 |                  0 |              26 |                        0 |                            0 |                 218104848 |
| memory/sql/THD::main_mem_root                                                  |   200742977 |   200742846 |             3390843376248 |            3390840251216 |              0 |                131 |            3117 |                        0 |                      3125032 |                  88720256 |
| memory/sql/Log_event                                                           |    43535365 |    43535358 |               20931402783 |              20931400073 |              0 |                  7 |          269641 |                        0 |                         2710 |                  49982659 |
| memory/sql/JSON                                                                |     9507512 |     9507456 |                 859590368 |                859587008 |              0 |                 56 |          549870 |                        0 |                         3360 |                  47863656 |
| memory/group_rpl/certification_info                                            |     1743130 |     1742155 |                 164631088 |                158843768 |              0 |                975 |          426208 |                        0 |                      5787320 |                  43207824 |
| memory/group_rpl/transaction_data                                              |     2931445 |     2931445 |                3611045127 |               3611045127 |              0 |                  0 |              15 |                        0 |                            0 |                  42283650 |
| memory/performance_schema/events_statements_summary_by_digest                  |           1 |           0 |                  42240000 |                        0 |              0 |                  1 |               1 |                        0 |                     42240000 |                  42240000 |
| memory/group_rpl/Gcs_message_data::m_buffer                                    |      711966 |      711966 |                3274265418 |               3274265418 |              0 |                  0 |               4 |                        0 |                            0 |                  38611767 |
| memory/mysys/KEY_CACHE                                                         |           3 |           0 |                  33556016 |                        0 |              0 |                  3 |               3 |                        0 |                     33556016 |                  33556016 |
| memory/sql/TABLE                                                               |     2498162 |     2490152 |                2662845439 |               2638261263 |              0 |               8010 |            9131 |                        0 |                     24584176 |                  28309707 |
| memory/sql/Filesort_buffer::sort_keys                                          |     3493367 |     3493366 |              115200850575 |             115200817775 |              0 |                  1 |             636 |                        0 |                        32800 |                  25885528 |
+--------------------------------------------------------------------------------+-------------+-------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+

In the project I am working on, there is a **Booking** table. - There are 8 types of Bookings. - I have elected to have just one table - There are 12 common fields If I store the rest of the fields as columns, and use Views to extract the data for each type of booking. Am I able use the View for editing (if I don't use joins in the View)? This would allow me to rename some of the fields to something more suitable for that type of booking.

In the project I am working on, there is a **Booking** table. - There are 8 types of Bookings. - I have elected to have just one table - There are 12 common fields I could store the rest of the fields as columns, but I am thinking of using JSON storage (which I have never used). It will add complexity to the code (in terms of readability) to split the JSON in the JavaScript application for editing and then recombining. Can I get MySQL to do the splitting and combining of JSON fields?

According to the "Top SQL" view in *AWS Performance Insights*, commit tops the list of SQL statements in terms of *average active session (AAS)*. And most of the time seems to be spent in wait/io/redo_log_flush. I have read the [docs on io/aurora_redo_log_flush](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-waits.io-auredologflush.html) - which I assume to be the name of wait/io/redo_log_flush prior to Aurora 3 - but they don't really help me, because there are apparently only 0.25 commits per second, but the load is close to 15 AAS. So it is not a problem of too many small commits. So why would such a low commit rate lead to such a high AAS? How can I dig deeper into this? N.B. We are using *8.0.mysql_aurora.3.02.1*

In MySQL I have a table "resources" that represents a list of resources. eg: CREATE TABLE resources ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, ...etc... PRIMARY KEY(id) ) There are about a dozen other tables in the database that have a foreign key referencing resources. eg: CREATE TABLE something ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, resourceId INT UNSIGNED NOT NULL, ...etc... PRIMARY KEY(id), FOREIGN KEY(resourceId) REFERENCES resources(id) ) CREATE TABLE somethingElse ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, resourceId INT UNSIGNED NOT NULL, ...etc... PRIMARY KEY(id), FOREIGN KEY(resourceId) REFERENCES resources(id) ) etc... For housekeeping, I need to periodically delete all of the rows in resources that DO NOT have a reference in any other table. At the same time I also need to clean some things that are outside of the database that these "orphaned" resources refer to. (eg deletion of a corresponding file.) To do this, I start with the following select statement, to select all rows in resources that are not referenced anywhere by a foreign key: select r.id, r.name from resources as r left join something as a on a.resourceId = r.id left join somethingElse as b on b.resourceId = r.id left join someOtherStuff as c on c.resourceId = r.id left join oneMoreExample as d on d.reosurceId = r.id ...etc... where a.resourceId is null and b.resourceId is null and c.resourceId is null and d.resourceId is null ...etc... limit 100; And then I deal with the results accordingly. This works, and currently this query only takes about 3 seconds to run, which tbh isn't bad. But I am worried that so many left joins have the potential to turn into a bit of bottlenck over time. I am wondering: Are there any other ways this query could / should be written, that might be more optimal / performant ?

Background - I am using XAMPP, which has MariaDB on my Windows development machine. I cant remove this as I have a number of projects that depend and use this. On my new project on AWS EC2, Windows Server, I have IIS and MySql installed. Issue - 1. When I try my DB modifications on the dev machine, they don't always transfer over. I have to tweak them. 2. To confirm that my DB is identical to the Production one, I would like to backup the DB on the real server and restore it on my machine. But this is even worse, too many changes ae required. Question - How can I run both Maria and MySQL on my Dev machine? - MariaDB needs to remain unaffected - MySQL does not have to run with Apache, the Development IDE has its own webserver for debugging. - They both need to be available. Some projects will use MySQL, others will use MariaDB. No project will use both simultaneously. Note - I hadn't tried installing MySQL, as I didn't want MariaDB to break - which would affect other projects.