Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,240 questions
0
votes
2
answers
13
views
Are leaf nodes on an InnoDB secondary index sorted by both secondary and primary key columns?
For example, if a table has columns A, B, ID, and (ID) is the primary key, and (A) is a secondary index.
I wonder if the leaf nodes on the secondary index (A) is sorted by (A,ID), or just (A), and the ...
- 101
0
votes
1
answer
112
views
Unexplained MYSQL memory allocation
I’m running MySQL server on Linux.
I have a long-standing issue on my database server — it keeps consuming an excessive amount of memory for no clear reason. As a result, I was forced to periodically ...
0
votes
0
answers
36
views
Table is freeze, not able to do any change in Innodb table in MySQL
I am using MySQL 8.0. I have a sales table with only 30,000 rows. I had a problem where the table was frozen and I couldn't alter the values in any of the columns. I attempted to duplicate the data ...
- 1
-1
votes
1
answer
149
views
MySQL 8.0.41 - 83 % Waiting for row lock on AO_319474_QUEUE
Environment
We run three Jira Service Management DC clusters that all share the same topology:
MySQL 8.0.36 on Ubuntu 22.04 – 32 vCPU, 100 GB RAM, buffer-pool now 80 GB (only ~33 GB in use), NVMe ...
0
votes
2
answers
114
views
Mysql Innodb Row/Page Size Limits
I have a purely theoretical question that would help me understand how exactly data is stored in the database.
Database settings:
MySQL 8.0.15, Innodb;
Innodb_file_per_table - ON (default);
...
1
vote
0
answers
122
views
How to decrypt a mariadb database or change the encryption key?
I successfully encrypted my MariaDB database using file_key_management and following this guide. Now I'd like to change the encryption key and suppose I need to decrypt the database first, before I ...
- 11
0
votes
0
answers
68
views
mysql 8.0.33 queries suddenly hangs in `waiting for handler commit` state
I have a large production MySQL 8.0.33 primary node with two replication nodes.
Today, our database suddenly hung. SHOW FULL PROCESSLIST showed many connections in the Waiting for handler commit state,...
- 3
3
votes
1
answer
379
views
How to recreate corrupt innodb tables with innodb_file_per_table?
MySQL refuses to start up and I was only able to start in read-only mode using innodb_force_recovery = 6. Every answer I've found on this topic suggests making a backup, deleting ibdata and ...
- 810
2
votes
1
answer
118
views
Why does replacing EXISTS with (SELECT EXISTS) significantly speed up performance, in spite EXPLAIN returning the same explanation for both?
MySQL (libmysql - mysqlnd 5.0.12-dev - 20150407. I tried to get it updated a few times but I get push back).
When I run this:
SELECT (SELECT EXISTS(select 1 from cadran_item where id_parcelle = ...
2
votes
1
answer
173
views
Why does innodb next-key locks lock more range than the condition?
Assume the following table:
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id int unsigned auto_increment primary key,
name varchar(255) not null,
age tinyint unsigned,
key (age)...
- 155
0
votes
4
answers
162
views
Performant way to perform update over 300M records in MYSQL
Context
I have an entity called 'Interaction'.
interaction | CREATE TABLE `interaction` (
`id` varchar(36) NOT NULL,
`entity_id` varchar(36) DEFAULT NULL,
`request_date` datetime DEFAULT NULL,
...
- 123
0
votes
2
answers
140
views
Slow select on empty table after delete
I've been running into this issue on a production instance and so tried a small scale local re-create and managed to do it.
I have a simple test table
CREATE TABLE `test` (
`id` int unsigned NOT ...
- 103
0
votes
1
answer
120
views
Mysql huge table vs small ram allocation
We have a percona mysql database where one of the table which is touching almost 26.8Gb in size. The server is just 4Gb but its specifically only hosting mysql. Now when I am trying to run a simple ...
- 227
3
votes
1
answer
223
views
Innodb - Can you safely ignore the warning "Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size"
We get this warning in our online shop
report.WARNING: Memory size allocated for the temporary table is more
than 20% of innodb_buffer_pool_size. Please update
innodb_buffer_pool_size or decrease ...
- 135
0
votes
2
answers
100
views
innodb fetch time, not query execution time
A very basic query but from a huge table
SELECT profile_id FROM clients WHERE is_vip = 0
execution has a duration of 0.716s and data fetch time of 15.072s
Obviously my question is if I can speed this ...
- 101