Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,173
questions
2
votes
2
answers
50
views
Do MySQL/InnoDB stats tables persist through major version upgrades?
After performing a major version upgrade of a PostgreSQL database, the ANALYZE command needs to be run in order to repopulate tables for query statistics, which do not persist through the upgrade. ...
- 121
0
votes
1
answer
25
views
Does B-tree in InnoDB use mark-delete?
I learnt that all secondary B-trees in InnoDB store pairs of the form (search key, TID) where TID is the primary keys of the records. I have the following questions:
(1) In InnoDB, does the secondary ...
- 13
1
vote
1
answer
18
views
How is InnoDB recover ? the crash happenning when buffer_pool memory copy to double_write_buffer
I know that double-write is purposed to solve the partial-page-write problem.but how to recover when the crash happenning during buffer_pool memory copy to double_write_buffer
- 13
1
vote
1
answer
38
views
Understanding MySQL Locks and Deadlocks
I was just checking the articles regarding the MySQL table locking system. I wanted to learn about how we can avoid the deadlocks in MySQL while UPDATE/DELETE operations.
What I have done so far:
...
1
vote
1
answer
18
views
How to change the engline type from InnoDb to MyISAM of an existing db?
I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key.
How can I change the engine type of that particular db ...
- 27
1
vote
3
answers
55
views
Forcing InnoDB to build the secondary index
When INSERTing, InnoDB defers the building of secondary indexes. For example,
CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
...
- 4,261
2
votes
1
answer
42
views
Imporve MySQL InnoDB Full-text Search Performance
1.Background:
DB: MySQL 8.0, InnoDB engine;
Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Old ...
- 21
1
vote
0
answers
31
views
How to recover corrupted innodb table from .ibd file?
I forgot to dump the databases before upgrading mysql from 5.5 to 5.7. After upgrade, I ran mysql_upgrade, which ended with the error:
Error occurred: Error during call to mysql_check.
It seems one ...
- 111
0
votes
1
answer
18
views
MySQL InnoDB Cluster config - configure node address
I first asked this question on Stack Overflow but I woke up this morning to the realization that it belongs over here.
I'm setting up an InnoDB Cluster using mysqlsh. This is in Kubernetes, but I ...
- 101
1
vote
2
answers
38
views
How to tell if a particular MariaDB / MySQL server supports the Barracuda storage format?
In creating some DDL-generating code for MySQL / MariaDB (inside WordPress as it happens) I need to find out whether the RDBMS server I'm talking to can support the newer Barracuda / DYNAMIC / InnoDB ...
- 499
0
votes
1
answer
23
views
pt-online-schema-change failed when creating new tablle, specified key was too long; max key length is 767 bytes issue
The version, mysql is InnoDB 5.6.10-log, pt-online-schema-change 3.1.0. table info is
`email` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`uid` varchar(255) COLLATE utf8mb4_unicode_ci ...
- 101
0
votes
0
answers
51
views
Waiting for table level lock innodb
We had a table which in empty. But due to some business logic the table gets queried anyway. This was working fine but today suddenly all the queries on that table were taking a lot of time due to ...
0
votes
0
answers
29
views
InnoDB Corruption
We had a previous client who's server crashed. They were running MySql 5 on Debian Lenny and doing daily backups. When they contacted us to help them restore the database from the backups we realized ...
- 1
2
votes
1
answer
21
views
MySQL InnoDB migrations custom implementation: How to deal with DML statements which trigger a commit in the background?
After some discussion on that topic I can assume that there is a quite frustrating fact about MySQL InnoDB: It does not support (atomic) transactions when it comes to DML.
If you have a database ...
- 195
1
vote
1
answer
61
views
single insert ignore on a table with no auto-increment
For this table, in mysql 5.7,
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL,
`val` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
The table doesn't have ...
- 113
1
vote
1
answer
17
views
innodb cluster group replication Error_code: 1594
So we have an innodb cluster consisting of 4 nodes. one of the nodes crashed (OS crash), while starting the node it shows the following error:
2022-06-13T06:46:02.529058Z 491 [ERROR] Slave SQL for ...
- 11
0
votes
1
answer
18
views
difference between page cleaner and background writer
page cleaner
InnoDB performs certain tasks in the background, including flushing of dirty pages from the buffer pool. Buffer pool flushing is performed by page cleaner threads.
background writer
...
- 93
0
votes
0
answers
24
views
InnoDB Deadlock when transaction waiting for lock already acquired
Transaction (2) holds the following lock:
RECORD LOCKS space id 11404 page no 1144152 n bits 72 index PRIMARY of table [tableName] /* Partition [tableName]_p59 */ trx id 28648068046 lock_mode X Record ...
0
votes
0
answers
32
views
Is there a max limit of ibdata files I can add to innodb?
As the title suggests I was supposed to add an additional ibdata file for our mysql archive server since it was near full. Its an old server and I just stumbled on it today. As I checked it, the my....
1
vote
1
answer
82
views
What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?
I've been trying to find the answer to this online for a while with no luck.
What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) ...
- 41
2
votes
2
answers
98
views
Database becoming unresponsive after time (4 to 24 hours)
Since a couple of days my mariadb database randomly becomes unresponsive after random intervals (which have been between 4 and 20 hours).
The database comes back to life when rebooting the ct.
And ...
- 41
0
votes
1
answer
77
views
mysqld Shutdown every 1 hours
Please help me solve a problem which initiates a mysql shutdown every hour. Naturally the site on the server can't connect to the database and I get a bunch of errors in the site's logs.
config: ...
0
votes
1
answer
314
views
Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future
I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs.
Error 2:- ...
- 140
1
vote
1
answer
99
views
Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table
I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with:
ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine
I'm finding the ...
- 13
0
votes
0
answers
131
views
How to understand and decrease Innodb_rows_read.avg (RDS INNODB)?
I am looking to understand what's behind Innodb_rows_read value and how to decrease it. For some unknown reasons my RDS server has been over 90% of CPU for more than 5 days and after investigation, ...
- 109
0
votes
0
answers
150
views
Why mysql 8.0.17 is clean after crash
I was working with my website and after I started a very huge process in the server (CentOS 8.5.2111), the server can not allocate more tasks because memory full, and MySQL (8.0.17) started with ...
EsopMx
0
votes
2
answers
71
views
The UPDATE queries is 50x slower than its SELECT query
The UPDATE queries is 50x slower than its SELECT query.
I have a table named sync_read and the have the following columns
id
club_id
created_at
user_id
queue_id
The table have the following Indexes:
...
- 101
0
votes
2
answers
26
views
InnoDB Clustered Index is stored under memory?
firstly i'm newbie about internals databases, sorry if my question is dumb.
I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory.
And i have ...
- 1
1
vote
2
answers
65
views
Change location of innodb_temporary
I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see:
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
***...
- 111
2
votes
2
answers
326
views
MySQL Innodb, why does the size of primary index depend upon the order of insertion of records
I have a table with this schema
CREATE TABLE `UserIdPhoneNo` (
`userId` int NOT NULL,
`phoneNo` bigint NOT NULL,
PRIMARY KEY (`userId`,`phoneNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
I am ...
- 123
0
votes
1
answer
256
views
Mysql service stops with [ERROR] InnoDB: CORRUPT LOG RECORD FOUND
In my Plesk Obsidian version 18.0.42 running on CentOS Linux release 7.9.2009 with mysql version 10.2.43-MariaDB. All of a sudden database has stopped working. Mysql service never comes up on start ...
- 140
0
votes
1
answer
213
views
Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0
CreateTableConstraintStep ddlList =
(CreateTableConstraintStep) dslContext
.createTableIfNotExists(getDSLTableName(List))
.columns(fields)
...
0
votes
1
answer
318
views
Can it be safe to disable binary logging in MySQL?
Is the MySQL binlog similar to Oracle's redo log or PostgreSQL's Write-Ahead Log?
If yes, how come it be possible to disable bin logging? If the machine crashes while the data files are being updated, ...
0
votes
1
answer
38
views
Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)
I have a InnoDB table with the following columns:
BIGINT -> 8 bytes
BIGINT -> 8 bytes
ENUM(0,1) -> 1 byte
MEDIUMINT -> 3 bytes
INT -> 4 bytes
= 24 bytes per row
It contains 10454004 ...
- 634
2
votes
0
answers
31
views
Table going into Repair mode during upgrade
Recently I was doing an upgrade in production system from MySQL 5.6 to 5.7. there is a particularly large innodb table about 2.5 TB which uses hash partitioning. when I ran mysql_upgrade I noticed ...
- 21
0
votes
1
answer
52
views
What is the performance overhead of IFNULL in the select statement of mysql 5.7+ query
I've recently made an upgrade to a mysql query performed by my web server when serving request. There are two main permutations of the query, one will typically be limited to returning 500 rows and ...
0
votes
0
answers
39
views
Why MariaDB doesnt respect adaptive flushing setting?
Using MariaDB 10.4.24. I have adaptive flushing enabled (innodb_adaptive_flushing=ON). My understanding is, that InnoDB will flush dirty pages slowly, but steadily. However my checkpoint age keeps ...
- 115
0
votes
1
answer
45
views
Importing data into Mysql slowly
This file is 23GB in size, it was created with Dump from the workbench
It starts well, but when it reaches 1/3 of the recorded data, the import starts to be extremely slow.
What do you suggest to ...
- 25
4
votes
1
answer
460
views
Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS
We had an AWS MySQL RDS t3.xlarge instance with 2000 IOPS. Also, I have a cron job written in java which inserts data in a table: it can insert up to 12M rows daily. Since AWS RDS cost is quite high ...
- 141
1
vote
1
answer
128
views
Why MySQL dump consume memory after finished
Hi I have a cron job to backup a lot of my database.
I create a script to run the dump command for each database every night, I created it like this.
mysqldump --defaults-extra-file=$DEFAULT_FILE -e -...
- 11
0
votes
1
answer
199
views
No free buffers in MariaDB 10.6
I have recently updated MariaDB from 10.4 to 10.6. One major change in the underlying InnoDB engine is the removal of buffer pool instances, so there is always only one.
I believe this affects some ...
- 115
2
votes
1
answer
180
views
MySQL 5.7.37 is consuming CPU and High memory ( 90-95% )
I have a problem with my MySQL database.
RAM usage is too high, CPU too.
Does it come from my configuration ?
Here's my configuration :
auto_generate_certs ON
auto_increment_increment 1
...
- 21
0
votes
1
answer
192
views
how to recover from failed transaction due to crash [mysql]?
I am trying to start MySQL server, but it keeps throwing errors. Upon inspecting I found out that the server was stopped unexpectedly and there was a transaction in the process that didn't finish. I ...
- 101
0
votes
0
answers
114
views
Can't UPDATE a database table
I'm getting a 'Error synchronizing data with database' error on MySQL 5.6.10 (on AWS).
Trying to update a field (using dBeaver) but getting error message/
Further: Data truncated for column ...
- 1
0
votes
1
answer
116
views
Does innodb_buffer_pool_size setting affect update queries?
I have a huge database(16GB) to which I wish to add a new column based on an existing column.
The existing column looks like this:-
MariaDB [demo]> SELECT my_mon FROM mytable;
+----------+
| my_mon ...
- 151
0
votes
1
answer
20
views
How can MySQL be optimized for unattended operation on unreliable hardware?
I run some personal services on a not-entirely-reliable single-board computer. Sometimes it locks up and needs to be power cycled.
One of these services is currently configured to use MySQL with ...
- 101
0
votes
1
answer
279
views
Recover corrupted database
The MySQL database running in an old Ubuntu 14.04 seems corrupted as successive sequence numbers don't match.
220127 15:16:11 InnoDB: Error: page 14357 log sequence number 41558915039
InnoDB: is in ...
joaomerod
0
votes
1
answer
71
views
drop and re add an index on innodb table causes slow performance
We just made a code and db update, that involves a table on production.
This INNODB table was "altered" by adding a new index and dropped the old one.
The new is made by 3 params and the old ...
- 101
0
votes
1
answer
177
views
mariadb innodb file per table data location
I have the following settings:
datadir = /var/db/mysql/data
innodb_data_home_dir = /var/db/mysql/innodb
innodb_file_per_table = ON
My intention is to ...
- 101
0
votes
0
answers
116
views
InnoDB - Fulltext (match against) extremely slow, wrong indexes?
I've recently migrated my myisam-tables to InnoDB and some things are a bit weird for me. For starters, the indexes looks a bit different (and maybe is the reason why the new InnoDB-implementation is ...
- 121