Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,155
questions
1
vote
1
answer
26
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
0
answers
10
views
InnoDB: Assertion failure in thread 34399657984 in file ut0ut.cc line 921
I've tried to setup and configure WordPress on FreeBSD 13. This is the tutorial that I've followed,but I haven't been able to complete it because a mysql error that I'm not able to fix :
https://...
1
vote
1
answer
50
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 ...
- 11
0
votes
1
answer
43
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
49
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:- ...
- 130
0
votes
1
answer
24
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 ...
- 3
0
votes
0
answers
39
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
42
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
3
answers
63
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
22
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
23
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
297
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
79
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 ...
- 130
0
votes
1
answer
70
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
98
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
22
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 ...
- 599
2
votes
0
answers
30
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
33
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
22
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
36
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
171
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
44
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
113
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
98
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
64
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
46
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
66
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
18
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
145
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
44
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
76
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
55
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
0
votes
1
answer
40
views
InnoDB Buffer Pool and Buffer Log
I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?
1
vote
2
answers
139
views
InnoDB's undo log start growing after simple SELECT with READ-UNCOMMITTED level
I have a percona mysql (5.7) server with 20K QPS (lots of inserts/updates/deletes).
My question is: why issuing a simple, but long, select query (to any table) with trx isolation=READ-UNCOMMITTED ...
- 23
0
votes
2
answers
36
views
MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk
There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table.
table_name
index_name
stat_name
stat_value
sample_size
stat_description
dept
...
- 165
2
votes
1
answer
24
views
MariaDB index chosen differs on ARM and x64 based procesors
When I have been using mariaDB on apple M1 chip, I discovered some diference between index chosen in select statement versus x64 server linux system with this query:
explain
SELECT z.id_hlavni AS ...
- 21
0
votes
1
answer
87
views
OPTIMIZE TABLE seems to "lock" tables (innodb)
During executing an OPTIMIZE TABLE on big tables the process seems to "lock" the tables. Is there something you can do to speed up the process or prevent it from "locking" the ...
- 123
1
vote
3
answers
158
views
Why does MySQL require two or more redo log files for InnoDB?
A single redo log file can also be written in a circular fashion.
In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
- 11
3
votes
1
answer
92
views
Finding number of Innodb pages to read
Is there any way to find number of pages that needs to read when querying for a set of pks?
Eg., select * from Table where id in (1,2,3,4,5,6);
Now is there a way to find the innodb pages that'll be ...
- 31
0
votes
1
answer
290
views
fix InnoDB: corrupted TRX_NO after upgrading to 10.3.31
After upgrading mariadb from 10.3.25 to 10.3.31 it fails to start like described in this bug report
I temporary fixed it by downgrading mariadb to 10.3.25 again like I described here.
To fix this ...
- 798
1
vote
2
answers
72
views
Make running MySQL with InnoDB flush everything to file to allow file-level live backup
A client has a file-level backup, where every relevant file of a critical webserver is synced to another webserver around 1am.
This includes the /var/lib/mysql directory where the databases of that ...
- 13
1
vote
1
answer
60
views
MySql 8.0 Foreign Keys Missing in InnoDB
I have created 10 tables in MySql 8.0 using the InnoDB engine. I defined relationships using the references statement.
When I view the tables using show create table, the foreign keys are missing. I ...
- 111
0
votes
0
answers
278
views
MySQL Error: Multiple files found for the same tablespace ID
Today my MySQL started giving me this error when I try to start the MySQL Service:
[ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID
[ERROR] [MY-012202] [InnoDB] Tablespace ...
- 101
-1
votes
1
answer
123
views
Composite Primary Key and Secondary Covering Index: Does the Secondary Index Ever Need to Include a Primary-Key Column?
MySQL. InnoDB.
Say there's a composite primary key 'a, b'. There is another column 'c.' I have a query hitting 'a, c.' Consider two claims. (A) "A secondary index on 'c' gets me a covering index.&...
- 21
1
vote
1
answer
58
views
Speeding up SELECT FROM a large table
I have a large table (500M rows with ibd size of 1.5TB)
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
ChildID int(11) unsigned NOT NULL,
Number smallint(5) unsigned,
Title varchar(255)...
- 4,179
0
votes
0
answers
37
views
How to recover data from idb files [duplicate]
I've tried:
Used mysqlfrm to recover the table structure.
Created it.
Discarded tablespace.
Copied .idb file to directory.
Imported tablespace.
But file size in information_schema was 0;
Then I ...
0
votes
1
answer
63
views
Force kill long running index creation
I have a 192GB table that I started adding an index to over night.
This was still going several hours later.
At the time the alter table started the database was very quiet.
There were other queries ...
- 263
1
vote
1
answer
43
views
Monitoring MySQL/innoDB post-process
I did INSERT INTO SELECT of a huge table (1TB). When the process is finished, MySQL/InnoDB still does post-processing (creating updating secondary indices, etc.) for hours.
Since these are internal ...
- 4,179
1
vote
4
answers
95
views
Tuning innoDB for a write-intensive machine
I have MariaDB 10.5 on my desktop with multiple disks (SSD and HDD) for write-intensive projects. Writing to a single table is fast and the percentage of dirty pages remains close to zero with 1000-...
- 4,179
0
votes
0
answers
130
views
Recover data from .ibd file with corrupted page
I'm seeing "incorrect information in file" errors when opening my INNODB tables.
I followed the general recommendation to extract the CREATE TABLE statements from the .frm files using ...
- 133