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.

Possibly. 128M is an old, too-small, default value for that setting. Change to about 70% of available memory, which might be: innodb_buffer_pool_size = 5G Because it is a "cache", a bigger value will cut down on I/O, which is a big part of any big query on a big table. UPDATEing a 16GB table will be quite slow, but for another reason. The system will keep an old copy of all the rows in case you crash. (That way it can recover the data back to consistent state.) That is, the UPDATE will take a long time, regardless of the cache size. All actions on all tables do use the buffer_pool. UPDATE will not chunk by itself. You can do the chunking. See http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks . (It is written in a DELETE context, but the changes for UPDATE should be easy.) Another approach to consider: Build a new table with the old column(s) plus the new column(s), such as CREATE TABLE newtable ( id ..., my_mon ..., my_wave ..., PRIMARY KEY(id) ) ENGINE=InnoDB SELECT id, my_mon, concat(...) AS my_wave;

Consider a Galera Cluster spread across 3 such pieces of crappy hardware. It can keep running if any one node dies. If that node somehow spontaneously comes back to life, the other two will help it get caught up with recent changes. Ditto if the dead node is manually rebooted. If two nodes die, you are SOL. With 5 nodes, the Cluster could survive 2 failures before needing manual intervention. (7 or more nodes may run into other issues; I don't recommend it.)

Yeah, your DB got corrupted. A couple of things might have caused this, 1 is if you moved the tables from one place to another without the innodb log files, as stated in the output, the other reason is a plain corruption due to hardware error = disk. Now, the first thing to do here is: Restore from backup? but I assume you don't have. Enforce the innodb recovery by adding this to your my.cnf file innodb-force-recovery = 6 Then, try to restart the DB, you will see a lot of garbage on the screen. If it finished correctly, then try to connect to the DB If you were able to connect, then stop it, remove the enforce parameter from the config file, then move the obdadta*, ib_logdata* and db directories out of the folder: cd /var/lib/mysql mv ibdata* ib_logfile* dbname ... /backup_location/ Start your DB normally, this will generate all the innodb files again. If all this doesn't work, then it is seriously damaged, but the sequence is something that the enfoce will usually fix. Quick edit here to ensure a better procedure. Backup the whole data directory before the procedure. tar -xzvf mysql.tar.gz /var/lib/mysql

A guess... Now that DROP INDEX is "instant" and ADD INDEX is done in the background, here is a possible explanation: ALTER TABLE t DROP INDEX a_b, ADD INDEX "a_b_c" (a,b,c); may run this way: Instantly DROP the old index -- now the table has no index starting with (a,b) Launch a background task to do the ADD. Return to the user -- allowing them to perform queries for which there may be no index Eventually the new index is finished. Now the same query will be fast. If that guess is correct, then this would be the cure: ALTER TABLE ADD INDEX "a_b_c" (a,b,c); Wait a while. (No easy way to tell when it will be finished -- Watching SHOW PROCESSLIST is a crude solution.). ALTER TABLE DROP INDEX "a_b";

after changing the innodb_file_per_table setting Messing with directory settings does not move files around. That setting takes affect only at the time of CREATE TABLE or ALTER TABLE. Existing tables that were built with the setting OFF will stay in ibdata1. It is not easy to shrink ibdata1. Furthermore it cannot be removed, not made "empty"; things other than tables go in there. What is your ultimate goal? If it is to shrink ibdata1, then there are lots of Q&A here that go through the steps to achieve that. Or, perhaps, the new directory is on a separate physical drive. (This is unlikely to make much difference in performance.) If you are simply "moving" things to a different directory, then the following may work. (Caution! I don't know for sure.) Backup! (Either with mariadump or by copying the entire directory tree) Stop MariaDB Change the dir setting as you did. Manually move ib* and any database directories to the new location. Start MariaDB

Be sure that innodb_buffer_pool_size is about 70% of available RAM. This is the most important setting. It controls the amount of RAM being used for caching, hence it helps speed up most queries. Leave innodb_log_buffer_size at the default given by the install. This setting is much less important. It has to do with writing redo log entries to disk. (Cf "transactions")

According to https://www.percona.com/blog/2010/06/10/reasons-for-run-away-main-innodb-tablespace/ , here are the reasons you are probably experiencing these things: Running Transaction which does a lot of changes – If transaction modifies a lot of rows it has to use a lot of space in undo segment and there is no way around it. Be careful in particular with update or delete transactions which go over a lot of rows. In many cases doing such processes in chunks, updating/deleting may be thousands of rows per transaction may be better if your application can handle it. Note ALTER TABLE will not require excessive amount of undo space even for very large tables as it internally commits every 10000 rows. Running Very Long Transaction If you’re running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we’re speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes. Purge Thread Falling Behind This is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records when they are no more needed which means undo space can just grow until it consumes all free space (or specified max size for ibdata1 file). “Good” thing is performance typically starts to suffer terribly well before that and it gets noticed. There are to things you can do about this problem first you can use https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can’t keep up. This however does not work in all cases. If you’re running XtraDB you can also use https://www.percona.com/doc/percona-server/5.1/performance/innodb_purge_thread.html to use dedicated purge thread, which works a bit faster as it does not need to compete with other activities of main theread. You can also use more than one purge thread by setting this variable to higher values though this functionality is a bit experimental. I have mentioned this post many years ago ... Jun 04, 2014 : https://dba.stackexchange.com/questions/50223/can-i-move-the-undo-log-outside-of-ibdata1-in-mysql-5-6-on-an-existing-server/66569#66569 Apr 23, 2013 : https://dba.stackexchange.com/questions/40730/how-can-innodb-ibdata1-file-grows-by-5x-even-with-innodb-file-per-table-set/40734#40734 POSSIBLE SUGGESTIONS Make sure you kill all Sleeping Connections. They may still be holding Invisible Table Locks. There are situations where InnoDB will retain table locks after a rollback without knowing who made the locks (See my May 13, 2012 post https://dba.stackexchange.com/questions/17788/cannot-update-certain-rows-in-innodb-tables/17795#17795 ). History length should dissipate if such locks are present and subsequently removed. Perhaps experiment with https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_purge_lag on the replica to see if history length drops.

In addition to the IO cost, a compute cost is added. This cost is #rows * row_evaluate_cost. By default row_evaluate_cost = 0.2. In your case, the are 100 rows, so the compute cost will be 1 + 100*0.2 = 21.

Suggested indexes: parametry_zbozi: INDEX(vyplneni_id, id_zbozi) zbozi: INDEX(id_nadrazeneho_zbozi, id_zbozi) A likely reason for a difference is the number of rows in some of the tables. Or SHOW VARIABLES. Notice how one of the Explains used "BNL" but the other did not. This is partially controlled by join_buffer_size`. Is any of those tables a "many-to-many" table? ON pz.id_zbozi IN (z.id_zbozi, z.id_hlavni) is especially hard to Optimize. Maybe a UNION would help. For further discussion, please provide SHOW CREATE TABLE for each table.

True. OPTIMIZE TABLE locks the table. But there is essentially no reason to "optimize" an InnoDB table. Simply don't do it.

(I do not have much to back up this Answer. But I know a lot about the history.) The original implementer of Innobase (Heikki Tuuri in about 2000) decided that ping-ponging between two files was the right way to go, so that is what he implemented. MySQL built a mechanism to handle various "engines" and absorbed Innobase, renaming it InnoDB. There is some evidence to indicate that more than 2 files is actually counter-productive. Philosophy After that, "if it ain't broke, don't fix it". MySQL always seems to have an attitude of enhancing only what is really needed. Some things get implemented (or fixed) because a single customer either submits code or pays to get it done. I guess no one thought innodb_log_files_in_group was broken enough to fix. The Query Cache is going away (gone in MySQL). It was implemented before 2000 to beat the pants of the competition in some benchmark. But it was implemented in a clunky way. Once multi-Master and Cluster topologies became popular, the QC had to go. The QC cannot easily be replicated. Aurora re-implemented it at least to scale better. MyISAM is all-but-deprecated by MySQL (but not MariaDB). It, too, does not play nicely with clustering. MariaDB.com has some of the MySQL developers from the 1990's.

1 and 2 requests do not use indices. Key = null You build workers, but incorrect SQL queries (in your case, because this is MySQL, apparently defaulted in non-resistant mode). If you want to compare productivity, remove the GROUP BY for start-up and where use only the primary index column. In fact, your current test doesn't show a flat score. GROUP BY, in addition, uses additional optimization indices that can be read in the literature (loose index scan)Learn theory!

Try it. --innodb_force_recoveryBut we need to be careful! Start creating a backup copy of these files. Detailed guidance https://habrahabr.ru/post/125358/ I can find you.