Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
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. ...
user avatar
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 ...
user avatar
  • 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
user avatar
  • 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: ...
user avatar
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 ...
user avatar
  • 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), ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
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) ...
user avatar
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 ...
user avatar
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: ...
user avatar
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:- ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
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: ...
user avatar
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 ...
user avatar
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 ***...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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) ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 -...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 121

1
2 3 4 5
44