Is there a way to force kill it?
Noway. You must wait for killed query modifications rollback. Even if you should try to restart server the rollback will be continued during the restarting. And forcing this process may cause data damage. Wait...
Is there a way to see what the progress is?
No such feature.
It's probably merging the change buffer into the index pages. That manual page shows some ways to monitor the change buffer size and its progress.
The deferred I/O caused by the change buffer merges is normal.
You could disable your change buffer (see the linked manual page) before you do the big INSERT, but then your INSERT would take hours because all ...
All the optimizations in InnoDB for dirty pages, redo logging, page flushing, io capacity etc. are designed with the assumption that high write traffic is intermittent. That is, it depends on there being period of low write traffic so all the deferred flushing can "catch up."
If you have a very high, continuous rate of writes, and your InnoDB ...
100 disk writes per second -- That sounds like the max speed of an HDD.
100 rows written per second -- That sounds like a very inefficient way to do INSERT (or UPDATE).
autocommit = ON and not inside BEGIN..COMMIT, plus innodb_flush_log_at_trx_commit = 1 and/or sync_binlog = 1 -- That sounds like there will be a flush to the log for every statement.
Show us ...
I suspect that innodb_io_capacity_max=30000 is much too large. Try 5000.
innodb_max_dirty_pages_pct=0 -- Dirty pages are good; don't try to avoid them with "0". The default is 75 (percent); MariaDB 10.5.7 decided that a better default is 90. Try one of those. Note that that setting is GLOBAL and dynamic, so no restart is needed.
By not ...
The size of the buffer_pool is important. It should be set to about 70% of available RAM. If only MySQL is running, set it to 6G on your 8G machine.
innodb_buffer_pool_size is the main tunable in MySQL. It should be bigger than the amount of data + indexes that you have, but not so much that swapping occurs.
Are you saying that the buffer_pool is 78% full?...
The quick fix is to lower innodb_buffer_pool_size to, say, 7G.
But with 20G of RAM, you should not be running out of RAM. Do you have other applications on the same server? And they are the ones causing the crash?
For further analysis of MySQL, please provide all the VARIABLES and STATUS: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning
Not much ...
innodb_log_file_size needs to be at least 10 times the size of the largest BLOB in the transaction.
innodb_log_file_size * innodb_log_files_in_group is limited by disk space, hence won't cause OOM.
innodb_log_buffer_size is in RAM. Its default of a mere 8M is probably fine for all situations. It is just a buffer.
Other than that, I don't think there is any ...
If you use the --single-transaction option, it turns off --lock-tables. That's what the documentation means when it says they are mutually exclusive.
if (opt_single_transaction || opt_lock_all_tables) lock_tables = false;
When the transaction log becomes full, it causes a checkpoint, as explained in the manual:
When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint.
Short Answer: Not really. Don't bother with OPTIMIZE TABLE.
SHOW TABLE STATUS provides Data_free, but there are many flaws in that number, making it mostly useless.
InnoDB keeps its B+Trees relatively well balanced. A DELETE will make some effort to combine two adjacent blocks with little in them.
The disk allocation for a table only grows, ...
This is how I monitor the growth of MySQL table in a dedicated table
The table to keep the values
CREATE TABLE `tables_sizes` (
`table_name` VARCHAR(128) NOT NULL,
`table_size` VARCHAR(25) NOT NULL,
`measurement_type` VARCHAR(10) NOT NULL CHECK (measurement_type IN ('BYTES','ROWSCOUNT')),
`measurement_datetime` TIMESTAMP NOT NULL DEFAULT ...
200GB in 240GB -- That is too tight. You may not be able to run an ALTER. And other queries may blow out the disk space. Since two nodes are that tight, it is likely that when it happens, Galera will lose quorum and stop accepting writes.
DELETE must save the old rows in anticipation of a ROLLBACK or crash. That is what caused the crash.
Probably the ...
No, for the record there is no feature in MySQL or InnoDB to create dedicated buffer pools for particular queries.
You may not have a correct understanding of what the InnoDB buffer pool contains. It does not contain query results, it contains pages of data and indexes, exactly as they are stored on disk. The same page in the buffer pool may serve multiple ...
Your problem may be due to the number of rows you deleted.
In InnoDB, each node in the Galera cluster has to maintain an undo log entry for every row you delete while the DELETE command is running.
When the DELETE command completes, node1 has to receive an acknowledgement from Node2 and Node3 that the transaction is ready to be committed on those nodes as ...
What client language are you using? There may be a way to get the id without touching the table again. It is called LAST_INSERT_ID() in some APIs.
INSERT IGNORE INTO table (hash) VALUES(blah);
reach for last_insert_id in the API. Or possibly
Be aware that IGNORE and IODKU will "burn" an id if it does not actually insert....
So to summarize the problem, input is a hash that needs to be stored. And the auto_increment of that ID is needed.
You've done the right thing and made hash a unique key.
INSERT IGNORE INTO hash_list (hash) VALUES(?);
Is correct. In the application API or SQL (ROW_COUNT()) you will get a rows affected. 0 indicates that it already existed.
If it already ...
innodb_open_files = 10 is much too small. 300 is the default and I have not seen anyone set it lower than 300.
Go to the config settings (see D.O. for how to do that) and change it to 20; see that suffices. If it still complains, keep raising it.
How much RAM is allocated to your MySQL? Maybe you have a 'tiny' allocation, thereby indicating that this ...