Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,191
questions
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 ...
- 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 ...
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
--------------...
- 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 ...
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.
...
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 ...
- 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:
...
- 133
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....
- 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 ...
- 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 ...
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 ...
- 11
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 ...
- 133
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 ...
- 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 ...
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 ...
- 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.
...
- 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 ...
-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 ...
- 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&...
- 13
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 ...
- 113
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 ...
- 113
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?
...
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 `...
- 11
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 ...
- 167
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 ...
- 111
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, ...
- 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:
...
- 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 ...
- 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<'...
- 227
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
...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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) ...
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
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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_
...
- 13
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....
- 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 &...
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 ...
- 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 ...
- 101
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 ...
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), (...
- 53
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) ...
- 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 ...
