Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
1 vote
1 answer
43 views

MySQL 5.7.42, Gradual memory growth over time, memory leak?

I'm currently running a MySQL server (version 5.7.42) in production, and I've noticed a gradual increase in memory usage throughout the day. I suspect there might be a memory leak issue causing this ...
George's user avatar
  • 11
0 votes
0 answers
2 views

"Table doesn't exist in engine" for innoDB tables after recovery attempt

I'm trying to restore a database from an old server, the person in charge lost their data backups, and only mysql folder remains. The database uses both MyISAM and InnoDB engines. For the InnoDB ...
stock omatic's user avatar
0 votes
2 answers
35 views

Deadlock while concurrently updating multiple rows on the same table

We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it --------------...
owl's user avatar
  • 1
-1 votes
0 answers
21 views

XAMPP Control Panel mysql shutdown unexpectedly

When I try to start on mysql from xampp control panel it says: 20:52:08 [mysql] Status change detected: stopped 20:52:08 [mysql] Error: MySQL shutdown unexpectedly. 20:52:08 [mysql] This may ...
Akshat Kr. Pandey's user avatar
0 votes
0 answers
28 views

[MariaDB 10.3 -> MariaDB 10.6][InnoDB] Duplicate entry '...' for key 'unique_index'

Recently we upgraded mariadb from 10.3 to 10.6 and we are facing a weird problem. Although there is a unique key on 3 columns (table_hash,table_id,meta_key) there are multiple records with same data. ...
Gioggiolo's user avatar
0 votes
1 answer
21 views

MySQL gets slow before optimizing table

We have a custom made webshop built on the Laravel PHP framework which processes, on a busy day, 7000 orders a day. Since all orders are sent to a third-party point of sale system via an API, the ...
Rogier's user avatar
  • 1
0 votes
0 answers
25 views

LOAD DATA INFILE query never completes (yet data is loaded), mysqlimport rejects

Using different mysql clients, my LOAD DATA INFILE query never completes, but I can see the data has been successfully loaded. Where can I look to see why the driver gets stuck? My complete command: ...
Hein du Plessis's user avatar
1 vote
2 answers
34 views

Tuning MYSQL 8.0.32 for my server

EDIT: User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning: There are querys running faster in an older server, less powerful, MySQL Version 5.7....
lio89's user avatar
  • 21
0 votes
0 answers
16 views

What kind of lock does INSERT ... ON DUPLICATE KEY UPDATE obtain when the UPDATE is a no-op?

I am working on an apparent performance problem possibly involving row lock contention, and I'm seeking to understand whether INSERT ... ON DUPLICATE KEY UPDATE Foo=Foo (with a no-op in the UPDATE ...
cbmanica's user avatar
  • 101
0 votes
0 answers
34 views

Help me understand if partitioning will help optimize a 1.6TB table [closed]

I am currently managing a MySQL table with hundreds of millions of records and I'm facing performance issues despite optimizing the queries. Deadlocks are still occurring, and I'm considering ...
antimonio's user avatar
1 vote
1 answer
66 views

Why innodb locks index till the end?

Why a query BETWEEN two IDs locks index till the end? UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5; -- locks id>5 for some reason The database is MySQL 8.0.33 GA. Here's the full example, taken ...
John Smith's user avatar
3 votes
2 answers
244 views

How to understand the output of SHOW ENGINE INNODB STATUS. Is it actually doing something?

I am running a query that I expect to take a long time although I got no idea how long. I found out about SHOW ENGINE INNODB STATUS but I am not sure whether it is actually doing something or maybe it ...
raquelhortab's user avatar
1 vote
2 answers
114 views

MySQL's maximum memory usage is dangerously high and CPU run 100%

Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day. I am really not server administrator, This is mysqltuner.pl result, -------- Storage Engine ...
Mathieu's user avatar
  • 11
2 votes
2 answers
158 views

MySQL db optimization error on innodb tables

I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32. When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
PirateRagnaros's user avatar
0 votes
2 answers
35 views

Performance for query filtered by user preference

I have an app, that shows data based on user preference. I will simplify it in this theoretical example: The app shows articles filtered based on user preference for their favorite authors or their ...
Kash's user avatar
  • 103
1 vote
1 answer
62 views

Cascading foreign key DELETE with tables that have RESTRICT constraints

I have a database that stores recipes for multiple organizations. So every table has an org_id pointing to the orgs table to allow each organization to have their own set of recipes and ingredients. ...
JoeFish's user avatar
  • 113
0 votes
1 answer
61 views

MySQL doesn't start

Recently I had an issue with MySQL for an unknown reason. It didn't start and produced an error message, so I decided to update it from 8.0.31 to 8.0.32. The Windows version is 10 Pro for Workstations ...
sevatster's user avatar
-1 votes
1 answer
104 views

How to avoid deadlock inside transaction? [closed]

I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen ...
chell's user avatar
  • 175
1 vote
1 answer
330 views

mysqldump: Error: Index for table is corrupt; try to repair it when dumping table - does this mean repair was done?

I ran into the same database trouble that another user described here and I managed to get mysqld started by setting innodb_force_recovery = 6, which allowed me to perform a backup using mysqldump -u&...
Robert K S's user avatar
1 vote
1 answer
36 views

InnoDB - how MySQL InnoDB pages works with underlying OS file system

I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage. Would you mind to confirm that my understanding is correct? InnoDB store ...
obanadingyo's user avatar
1 vote
2 answers
66 views

MySQL vs PostgreSQL for hash-based table partitioning

I'm planning an open-source a backend service whose primary job is to deliver sync messages between users' devices. I want the self-hosted and hosted versions of the service to share the same ...
Agost Biro's user avatar
1 vote
1 answer
79 views

high CPU with high user connection, mysql database

I use a VPS with 56 CPU cores and 64 GB of memory, a with MySQL database, but the CPU usage is always high for my apps - user 10.000. This is in my mysqld.cnf. What's wrong with my settings? ...
Risky Ageng Kharisma's user avatar
1 vote
1 answer
37 views

MySQL: Materializing table function take so long

Thanks for checking my question I encountered a strange issue. the same query in same db return different actual time here is explain analyze statement explain analyze SELECT COUNT(1) AS `cnt` FROM `...
YOSEPH NOH's user avatar
0 votes
1 answer
76 views

Mariabackup, Xrtrabackup, how those handling InnodDB page compression, meaning: sparse files?

InnoDB engine, when using page compression, heavily depend on file system sparse file support. InnoDB Page Compression. It raises the question, how will mariabackup/xtrabackup handle those InooDB ...
g.pickardou's user avatar
1 vote
0 answers
132 views

Crashing after upgrade to MySQL 8.0.31

We recently upgraded to MySQL 8.0.31 on RDS. The upgrade went fine, but a couple days later the instance crashed because of data corruption and wouldn't restart. We ended up having to go boot in ...
Chris Hynes's user avatar
1 vote
1 answer
43 views

/var/lib/mysql/mysql.ibd big after disabling performance_schema

What I think is the reason for this issue Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.) So, ...
Johny's user avatar
  • 21
1 vote
1 answer
57 views

Migrate MyISAM tables to InnoDB using replication to avoid a long down time

We have to migrate MyISAM tables to InnoDB but we are afread of a long down time. In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes. My question is: ...
Ela's user avatar
  • 488
0 votes
1 answer
119 views

MariaDB/InnoDB performance: composite primary key vs. unused surrogate integer primary key to a table?

Years ago with MySQL/InnoDB, I've researched and found that adding a surrogate integer primary key to a table which doesn't have it always makes sense for performance reason. Even if that key is never ...
Collector's user avatar
  • 135
0 votes
2 answers
220 views

Pt-archiver csv double quote missing for some fields

I am trying to use pt-archive to archive my database. Below is my script for the archiving process. pt-archiver --source h=localhost,D=***,t=** --user=*** --password=**** --where "dateTime<'...
user8012596's user avatar
2 votes
1 answer
164 views

MySQL consumes much more memory than configured in innodb_buffer_pool_size

Long story short: I have the buffer pool size = 40G. I have 64GB RAM on my server. But MySQL actually uses 73G of memory (53G physical and the rest swapped). MySQL version: 8.0.22-0ubuntu0.20.04.2 ...
Stalinko's user avatar
  • 161
0 votes
1 answer
32 views

How can I sort thousands of Replies on a Post, following an algorithm that's not just based on columns of the "Replies" table?

On my website, Replies to Post are sorted based on an algorithm that depends on multiple factors: Reactions, Reply Quality, User Reputation, Reports, Replies to Reply, etc... (these are just a few ...
Nuno's user avatar
  • 758
9 votes
3 answers
2k views

In MySQL/MariaDB, do Indexes' performance degrade as they become larger and larger?

I'm currently exploring the use of PARTITION, for a specific use case I have. I use InnoDB, file per table. MariaDB 10.8. I was reading Rick's PARTITION Maintenance in MySQL webpage. I'd like to ...
Nuno's user avatar
  • 758
2 votes
2 answers
387 views

Database keeps crashing every few hours

The MariaDB server will crash every few hours(it's been a week so yeah, dozens of crashes), and restart automatically. In 2 previous crashes it went as far as the server not starting up again, and I ...
lyeaf's user avatar
  • 155
1 vote
1 answer
39 views

InnoDB Database Using All Disk Space

I have a database for an app, that is currently using MariaDB with about 220 InnoDB tables. The entire database is about 30GB of data. However when attempting to move it to a new server that has the ...
atrljoe's user avatar
  • 111
0 votes
2 answers
140 views

MySQL Error: Can't find record in table_name

I use MySQL version 5.7.39, ENGINE=InnoDB, Isolation = Read-Uncommitted: Table product with structure: CREATE TABLE `product` ( `category_id` varchar(50) NOT NULL, `product_seq` bigint(20) ...
Tran Minh Nhat's user avatar
0 votes
1 answer
61 views

In the MySQL secondary index, are the primary keys on the leaf nodes ordered?

mysql index In the MySQL secondary index, are the primary keys on the leaf nodes ordered? Physical structure in MySQL secondary index
feilong gao's user avatar
1 vote
1 answer
215 views

Calculating MySQL server requirements

I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
webnoob's user avatar
  • 605
1 vote
0 answers
19 views

Delay Async Replication Time ClusterSet

I have following setup: Primary InnoDB Cluster with 3 instances(following Group Replication Protocol), R/W setting. Replica InnoDB Cluster with 3 instances(following Group Replication Protocol), R/O ...
nyx's user avatar
  • 19
0 votes
0 answers
85 views

MySQL InnoDB Full-text search: Override default stopword list

I'm using MySQL8.0 InnoDB and its full-text search features. The default stopwords list of InnoDB only contains 36 words. Extending the stopword list to 500+ words helps to lift our full-text queries ...
123mig's user avatar
  • 21
0 votes
1 answer
378 views

MariaDB not starting in windows 10 after crash

MariaDB 10.7 service suddenly stopped in Windows 10 after a crash and the service is not starting. The pop up shows 'windows cannot start mariadb service). The windows event viewer gives the following ...
sariDon's user avatar
  • 111
2 votes
2 answers
118 views

How to identify if my innodb buffer pool is choking ? (and should i increase it ?)

We have a mysql box where i do rigorous scans/aggregations every few minutes (~ 35GB of data). Innodb buffer pool is ~ 1.5GB. Off late i have started noticing a few latent queries, where the 'read ...
Harshit's user avatar
  • 123
0 votes
1 answer
85 views

Update huge table by values from text file condition

I have 45 million Innodb database which I need to Update with condition from text file containing 1.5 million rows. UPDATE `table` SET `value` = 1 WHERE `column` = '_HERE DATA FROM TEXT FILE_ ...
wtfowned's user avatar
2 votes
3 answers
546 views

Why Optimize Table does not shrink table size?

I implemented this solution for big database, only keep data for 14 days (remove data daily based on date). When I run optimize TABLE table1; The size supposes to decrease but in my case, it increases....
Niyaz's user avatar
  • 218
4 votes
2 answers
265 views

How bad is it, really, to use InnoDB table as a queue?

I have a product which uses two separate tables as queues for some processes. Generally speaking: Interesting events occur in the application, and we write a record to the appropriate queue. That &...
Christopher Schultz's user avatar
0 votes
0 answers
61 views

How can I truncate a table when mysqld is not running?

One of my mysql tables(innodb/Barracuda, mysql version 5.7) is corrupted, which makes mysqld down. I cannot restart mysqld even setting innodb_force_recovery=5. Setting innodb_force_recovery=6 can ...
peter's user avatar
  • 111
0 votes
0 answers
79 views

Mysql innodb - Crashed recovery help

It is really painful our mysql wp db crashed! I backed up /var/lib/mysql and tried innodb_force_recovery up to value 4. But no luck When I try backing up using mysqldump it loses connection while ...
TheMonkeyKing's user avatar
0 votes
1 answer
49 views

my.cnf parameter optimization

Looking for an optimized my.cnf configuration. And more importantly, I am actually looking for the relation between these parameter/system variable and my system's memory. So, if server's memory ...
Shimanto Hassan's user avatar
3 votes
0 answers
59 views

Why BETWEEN takes more locks then needed?

CREATE TABLE my_table ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , data TINYINT DEFAULT NULL , PRIMARY KEY (id) ) ENGINE = INNODB; INSERT INTO my_table(id, data) VALUES (1, 1), (2, 1), (3, 1), (...
Max32Nov's user avatar
0 votes
0 answers
297 views

Unable to Recover MySQL Database [Missing FILE_CHECKPOINT]

Using MariaDB 10.7.3 via Homebrew on MacOS Monterey 12.6.1 While trying to recover the database after a system crash, I am stuck with the following situation. [Note] mysqld (server 10.7.3-MariaDB) ...
Rawkon's user avatar
  • 11
0 votes
1 answer
30 views

Moving Mysql 5.7 database from windows 7 pc to windows 10 pc Mysql 8.0. Can see database in files, but cannot open?

I have been running a Java project created using Netbeans and a Mysql 5.7 database on an HP Windows 7 PC. I moved the project to a Lenovo Windows 10 PC using Netbeans and Mysql 8.0. I can see the ...
user1735386's user avatar

1
2 3 4 5
44