Some drawbacks of INPLACE algorithm are: Long-running online DDL operations can cause replication lag in slaves. Online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed. larger https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_online_alter_log_max_size size extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log. At time can cause high IO usage for a larger table at high concurrency servers ( Aggressive in terms of resource consumption) Check the list of https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html Before running a DDL https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html on a large table, check whether the operation is fast or slow as follows: Clone the table structure. Populate the cloned table with a small amount of data. Run the DDL operation on the cloned table. Check whether the “rows affected” value is zero or not. A nonzero value means the operation copies table data, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replica server one at a time.

I have probably found the cause and it's a weird edge case. I have the cts (from proxmox) running on lvm instead of lvm-thin. And had a backup job using snapshot for the database every 4 hours. My guess is that since snapshot was unavailable it froze the ct for the backup and somehow mariadb did not like that. I have now changed that to a backup once a day with the shutdown option.

vi your my.cnf file; add variables skip-name-resolve; then restart mysqld service

This issue will usually happen after one of the following changes: If the InnoDB log files were deleted and one or more of these events preceded that deletion (none of the events can on their own cause the error it takes both one of the following events and deleting the log files): The hardware server crashed. The mysqld process was terminated with kill -9 or another reason. The data was restored from a hot backup. MySQL was shut down using while innodb_fast_shutdown = 2. MySQL was restored from a backup where the InnoDB log files did not exist or is from a different backup. MySQL was restored from an inconsistent backup. MySQL was started with innodb_force_recovery = 6. Please visit the oracle official guide for this error. https://support.oracle.com/knowledge/Oracle%20Database%20Products/1416063_1.html#aref_section41

Perhaps the setting for tmpdir is pointing into a small disk partition. Check SHOW VARIABLES LIKE 'tmpdir';

It seems like this would do the job much faster: UPDATE sync_read SET sync_read.queue_id = FLOOR(RAND() * POW(10,6)) WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51'; That needs INDEX(club_id, created_at) -- in this order But, does queue_id belong in that table?? Redundancy of queue_id Meanwhile, "My aim is to update the column queue_id, of rows with same (club_id,user_id) to same number" sounds like you have queue_id repeated across multiple rows. Remove that column from this table, and put it into a separate table so that it is not repeated. That table would have 3 columns: (club_id, user_id, queue_id), with the first 2 being the PK. That may require a JOIN in some other queries.

Index and data pages are stored in the innodb buffer pool. Only the active part of these indexes and are loaded. The least recently used data/index is purged out of the innodb buffer pool (memory). Create a primary key, which is therefore a clustered index, of the smallest, commonly searched, unique column(s) for the table. Create the secondary indexes you need to improve the performance of queries. Have enough innodb buffer pool to fit the active working set of data in memory. Look at innodb_buffer_pool_reads vs innodb_buffer_pool_read_requests in the global status to see what is coming off disk and what is still in memory.

mysqld --help --verbose At about line 13, it will say where the config file is. It is probably not spelled the way you spelled it. Mine says ... Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf ...

When you insert presorted data into a b-tree index, the leaf pages will tend to be more densely populated, because the index grows only on the trailing end. When you insert randomly ordered data, new rows will be inserted all over the place, potentially causing more page splits, which will end up more sparse as a result. Can we do something to fix it? There's nothing to "fix" so I would just leave it alone. Your idea that fitting the entire table in memory will materially improve performance is likely wrong. However, if you insist on wasting your time, see suggestions in https://dba.stackexchange.com/questions/110736/mysql-indexes-maintenance , for example.

I cannot see anything in the https://jira.mariadb.org on this error that might affect the 10.2.43 version that you have. There are a potential few causes around corrupted data on disk that there isn't sufficient information to search for. Given you didn't fully recovery from your first crash, there may be underlying issues that caused the second. As the 4 hours of recovery was unmanageable, the backup/restore strategy needs to be re-examined. Its never pleasant, but with planning and practice its manageable. Look at complementing the backup with mariabackup and binary logs a point-in-time recovery mechanism. A logical mysqldump of both of your critical databases and a restore to a clean instance (brand new data-directory) at a non-emergency time would be a good first step in improving reliability. The 10.2 series is soon to be end of life in MariaDB and perhaps planning an upgrade (10.6 maybe) at the same time will put you in a better state if you need to get MariaDB fixes. With a clean install recently happened, if it does occur again, I recommend you create an new bug report with the issue. As this is a crash recovery, the original crash is also a very useful thing to have reported in a bug report.

The computation of when the column will overflow is complex. 250 TINYINTs (1 byte + overhead) would easily fit, but a TEXT, BLOB, and certain other datatypes will take a minimum of 20 bytes; some cases 40 bytes. Hence, the unexpected overflow. I recommend doing one or more of the following: Shrink the datatypes. (This is not applicable to your "table1".) Some programmers blindly use BIGINT and DOUBLE for numeric values. These take 8 bytes each; there are smaller datatypes that my suffice. Do not have an "array" spread across columns. "Vertically" split the table, moving some columns to another table. Then use the same PRIMARY KEY for both tables. (If that is AUTO_INCREMENT, then it applies only to the main table.) As above, but move sets of columns that are often NULL. Then you can simply leave out the row in the new table. (Use LEFT JOIN when putting them back together.) Combine some columns together in some way, perhaps via JSON. This will decrease the number of columns, hence the per-column overhead. And the JSON will use a minimum of 20 (or whatever), which is less than 20 * the number of columns it replaces. If you provide the current SHOW CREATE TABLE with the actual column names, I may have more tips.

Unlike many other DBMSes, MySQL has two levels of logs to perform the "redo" function. Logs used to perform crash recovery are implemented (or not) by the storage engines, e.g. the InnoDB https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html and https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html logs. These logs allow the storage engine to replay committed and undo uncommitted transactions when the server recovers from an abend. They may also be used to allow online backups. The binary log is maintained by the MySQL server process itself, it does not belong to any particular storage engine. As you have mentioned, it allows, among other things, point-in-time recovery (PITR) by replaying transactions on top of a restored backup. It is not used for crash recovery. In other words, if you do not require PITR or replication you can safely disable the binary log; recovery of the database to a consistent state after a crash will be enabled by the storage engine redo/undo logs -- just make sure you use a storage engine that implements them.

InnoDB has a lot of overhead. NULL/NOT NULL length for VARCHAR (N/A in your example) 20-30 bytes overhead for each row -- including transaction-related things fragmentation because of BTree blocking. On average, a block is only 69% full due to the way rows are inserted/deleted without any big reorganization. A simple Rule of Thumb: As a Rule of Thumb, the table will take 2x-3x the space you would predict from BIGINT=8 bytes, etc. SHOW TABLE STATUS is notoriously imprecise. The Data_length is precise (and a multiple of 16KB) and the product of Avg_row_length and Row_size is, within rounding errors, equal to Data_length. But those two are only approximate. Data_free usually shows a multiple of 1MB between 4MB and 7MB. This is due to its preallocating large chunks. Tables that were created with innodb_file_per_table=ON are in their own .ibd file. The others are sharing spacing in the file ibdata1. The Data_free for the former is tied to the table; for the latter, it is for the entire tablespace. The PRIMARY KEY (an index) is included (clustered) with the data. Hence it takes virtually no disk space. (Rule of Thumb: about 1% overhead.) Each secondary key includes a copy of the PK -- in your example, the KEY(ls) will be nearly as big as the table. When talking about a table schema, please provide SHOW CREATE TABLE; there are several details you left out. And the example does not include a TEXT/BLOB that might be stored "off-record". And the example does get into what happens with different charsets/collations.

Let's look at this list of overheads: Overhead for a statement Overhead for a row Overhead for a function Those are in order starting with the most overhead. That is, the function overhead is insignificant in the big picture; don't worry about it. See also COALESCE(). Caching With Engine=InnoDB, the main cache is the "buffer_pool"; innodb_buffer_pool_size should be about 70% of available RAM. All actions on rows happen in the buffer_pool: Read block(s) from disk (if not already in cache) Find the row(s) in the block(s) Fetch (SELECT) or modify row(s) Eventually write the block back to disk (if modified) Since you are asking about SELECT, only steps 1,2,3 are relevant. Furthermore, if you keep Selecting the same row (or nearby rows), step 1 is not repeated. Note: This does not mention any impact of function calls. Note: On a cold system (nothing in the cache), the first SELECT will be slower due to step 1. After that the same or 'similar' queries will be faster (sometimes 10x faster). Comment: ON t1.id = t2.id Do you have two tables with identical PRIMARY KEYS? Why? The main way to have good performance in a JOIN is to have suitable indexes. Let's see the real query and SHOW CREATE TABLE for the tables being Joined. The BNL mentioned in a comment is an internal "caching" to speed up certain Joins. You have little control over its use. It refers to fetching all the stuff needed from a secondary table and storing it in an in-memory hash. It only applies when Joining. Function calls are not involved (in your example). If a function (eg, IFNULL) were used in ON or WHERE, it is likely to prevent the use of an otherwise suitable Index. (cf not "sargable")

CREATE TABLE without those indexes. LOAD DATA ALTER TABLE .. ADD INDEX ...

Is it possible your Aurora Serverless instance isn't autoscaling appropriately because your cron job is a continuously long running query and / or transaction? According to https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.how-it-works.html autoscaling can timeout due to the aforementioned events: When it does need to perform a scaling operation, Aurora Serverless v1 first tries to identify a scaling point, a moment when no queries are being processed. Aurora Serverless might not be able to find a scaling point for the following reasons: Long-running queries In-progress transactions Temporary tables or table locks To increase your Aurora Serverless DB cluster's success rate when finding a scaling point, we recommend that you avoid long-running queries and long-running transactions. Also mentioned in https://aws.amazon.com/blogs/database/best-practices-for-working-with-amazon-aurora-serverless/ : Scale-blocking operations The capacity allocated to your Aurora Serverless DB cluster seamlessly scales. But using long-running queries or transactions and temporary tables or table locks can delay finding a scaling point. And: Long-running queries or transactions For transactions, you should follow standard best practices. For example, keep your transactions simple, short and use a suitable isolation level. The most important practice is to avoid long-running transactions. In general, for any relational database, long-running transactions can cause performance degradation. Specifically for Aurora Serverless, long-running transactions are blocking operations for scaling unless you use the force scaling parameter. Even in this scenario, you must complete a proper rollback first, which can take significant time. This can have a very negative impact on your application. If your cron job is a single query or transaction, then re-writing it to insert data in multiple smaller batches, with breaks, in a more iterative manner, might prove a better workflow for you on Aurora Serverless. If your instance's autoscaling is timing out, then your cron job may be running on an under-provosioned instance the entire time.

Not a problem. Well, not yet. mysqldump works by SELECTing all the data and generating a dump file from that. For InnoDB tables the SELECTs read the data into the "buffer pool". That expands to a maximum as indicated by innodb_buffer_pool_size but never shrinks. That setting should be tuned to use up most of the remaining memory, but not go past all of RAM. (Going past would lead to swapping, which is terrible for performance.) Some guesses based on the graph: The "working set" of your data is under half of the data. (The graph is lower before 2/12.) Today, the graph is touching 100% -- Suggest lowering that setting a little so it won't start swapping. There is no need for a restart. How much RAM do you have? How much data do you have? What is the setting of innodb_buffer_pool_size?

how to recover from failed transaction due to crash The correct answer is "Let the database recover itself". Instance Recovery is part and parcel of every major DBMS, including mysql/mariadb. It just happens automatically. You don't have to do anything. I had to delete some log files because the server was out of space ... Yep; it happens all too often. ... I deleted a mariadb-bin file ... And that was probably the very worst choice you could have made. [Text] log files that you or I might read? Trash 'em with impunity. [Binary] log files used internally by the database? Let well alone. Leave them to the database to manage for itself. You removed the very file that the database needed to perform its Instance Recovery and sort itself out. Anything that you do now (unless you took a a backup of that file before deleting it) will involve losing [some] data ... and that could land you in hot water with Management.

The innodb_lru_scan_depth affects the rate of flushing modified (or "dirty") pages in the buffer pool. The number of free buffers measures only how many pages of the buffer pool are unoccupied. Zero free buffers indicates that the buffer pool is fully occupied, but it does not necessarily indicate that the pages are dirty. They might be mostly or entirely read-only copies of data on disk, loaded into the buffer pool to satisfy SELECT queries, and therefore they do not require flushing. So the innodb_lru_scan_depth would have no effect in that scenario. The number of free buffers alone cannot be used to guide tuning the innodb_lru_scan_depth. Update: I thought of a way the flushing could affect the checkpoints. If your rate of writing transactions is very high, you could be filling the log file faster than the background InnoDB threads can flush dirty pages. In that case, you could increase the log file size to give it more flexibility, or you could tune the innodb_lru_scan_depth to try to flush pages faster. But there's a practical limit to the flushing rate on any storage. Ultimately, you could be trying to write transactions faster than a single server can keep up with. In that case, you need to split the database writes over multiple servers. Many growing applications face this challenge. It's not easy.

@Salim With available information, suggestions to consider REMOVE from your configuration log_error to allow default error log file name innodb_print_all_deadlocks=1 # from OFF to enable identification and pursue correcting. key_cache_block_size=16384 # from 1024 to reduce overhead managing key_buffer_size key_cache_division_limit=50 # from 100 percent for Hot and Warm cache key_cache_age_threshold=7200 # from 300 seconds before AGE-OUT of indx in ram These three variables, after implementation will reduce key_reads Rate Per Second of 1,910. Observations, When your log_error is stderr, there is no hope of determining what happened several hours ago to your server. com_rollback counted 18 events in 3,131 seconds and you have NO record of the cause with current configuration. innodb_print_all_deadlocks may help you here. com_stmt_prepare count is usually paired with com_stmt_close. In your STATUS, reporting, com_stmt_close indicates 46,545 prepares did not have CLOSE, leaving resources in use. Reducing ram available over your 3,131 seconds of uptime.