Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,046
questions
0
votes
0answers
12 views
Why does insert on duplicate-key error create a share lock?
In the mysql docs is the following example for locking on insert:
INSERT sets an exclusive lock on the inserted row. [...] If a duplicate-key error occurs, a shared lock on the duplicate index ...
0
votes
0answers
9 views
How to avoid duplicate in mysql INNODB transaction for non index key?
I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
1
vote
1answer
21 views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
0
votes
1answer
21 views
Optimize Table query causing Fatal error during execution with InnoDB table
I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However ...
0
votes
1answer
22 views
Should I use MyISAM over InnoDB to save disk space?
I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an ...
0
votes
1answer
55 views
Innodb primary key advice
We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly.
The existing schema has a uuid4 as a primary key, and then two additional indexes ...
1
vote
1answer
28 views
Latest value of each GROUP with INNER JOIN
I'm using sensors to get temperature and humidity every X minutes.
I've one table for sensors and one table for their results.
tb_sensors:
tb_results:
I want to get the latest measurement for every ...
0
votes
1answer
21 views
How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database? [closed]
I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control.
Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical ...
2
votes
1answer
12 views
what happens If I remove mysql-bin.index file?
Because of the file storage full, we are removing mysql bin log files.
Accidentally, we removed mysql-bin.index file.
What happens if I remove mysql-bin.index file?
These below are my DBMS info. Thank ...
1
vote
2answers
61 views
MariaDB: how to reduce ibdata file size
I found that My MariaDB's ibdata file keep increasing.
So, I've searched for this, and found that innodb_file_per_table should be set as 1.
but, my DBMS's configuration has already set as 1;
Why ...
1
vote
2answers
47 views
MySQL: DELETE didn't shrink DB size, nor table size
In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same.
Table size:
+-------------------------...
0
votes
1answer
29 views
Assign different RAM usage (buffer pool size) per db in mysql
I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb.
However, the InnoDB Buffer Pool is only 16Go, ...
0
votes
0answers
14 views
MySQL 8.0.x: Discard temporary table left behind during a pre-migration (5.7.x) crash when the parent schema is already dropped
We had a MySQL 5.7.x instance serving a production schema foo, and the server itself was migrated to 8.0.21 on 13 Sep 2020.
Recently we migrated data from foo to a new schema bar. After migration we ...
0
votes
0answers
34 views
MySQL: message “Incorrect key file for table” “try to repair it”
I'm using MySQL 5.7.10
I have a table like this, with 100M rows and a size of 16GB .
CREATE TABLE `my_table` (
`id` DOUBLE NOT NULL AUTO_INCREMENT,
`entity_id` DOUBLE NOT NULL,
`concept_id`...
1
vote
0answers
18 views
MySQL pagination
MySQL - I am trying to fix this pagination issue on my servers . The RAM is 40 GB and the innodb_buffer_pool_size is set to 36 GB which is a little more than 80%. Still getting paging alerts. Any ...
0
votes
1answer
49 views
Mariadb error 1071: Specified key was too long; max key length is 1000 bytes
A bit of context: I am trying out flowable, however tomcat chokes at a certain point while populating the database, the usual exception wall of text boils down to the title of this post. First time I ...
0
votes
1answer
132 views
MySql InnoDB crash recovery - File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
Mysql 8.0.23 on CentOS 7.9 with WHM
I ended up with a corrupted InnoDB table. I gave up on a long query and killed it. Then the query was still blocking so I did a forceful server reboot. I know now ...
0
votes
2answers
25 views
in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?
I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload.
Based on this What are the main differences between InnoDB and MyISAM?
When ...
1
vote
4answers
86 views
Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird
-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --
-- further update. After doing ...
1
vote
2answers
26 views
How do I add composite MySQL Indexes with denormalization and JOINS?
I have the following tables:
CREATE TABLE base_event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by ... -- some columns
);
CREATE TABLE transaction_events (
event_id ...
0
votes
1answer
8 views
delete var/lib/mysql folders after dropping database via mysql
I am aware that you don't want to remove the innodb tables from /var/lib/mysql for a linux distro of mysql (I have Ver 14.14). So I used the command
drop database mydb1;
drop database mydb2;
-- etc
...
0
votes
1answer
29 views
Consistent InnoDB dumps with mysqldump
The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other ...
0
votes
1answer
28 views
Improving remake of primary key performance
I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size.
I want to drop the existing primary key (id) and create a new composite primary key of 2 columns:
...
0
votes
1answer
30 views
When dropping a partition on InnoDB table does mysql flush pages in the whole table or only in a partition to be dropped?
When issuing ALTER TABLE .. DROP PARTITION p1 mysql have to flush pages to disk. My question is: does mysql flush pages in the whole table (in each partition) or only in a partition to be dropped? ...
3
votes
0answers
46 views
Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB
I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
1
vote
0answers
106 views
MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?
I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
1
vote
1answer
62 views
Mysql 5.7 innodb_buffer_pool_size - zero I/O?
I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server ...
-1
votes
3answers
57 views
Optimal Indexing Strategy
For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the ...
0
votes
0answers
67 views
InnoDB: page_cleaner: settings might not be optimal (how to tune)
I am having similar issues to this question but I'm trying to understand how to tune the database correctly (rather than just lowering settings and hoping ;)
In the error log I have these entries (...
2
votes
1answer
158 views
Missing mysql, sys, performance_schema
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database ...
1
vote
2answers
88 views
“Alter Table” modify multiple columns vs multiple Alter table single column in INNODB?
I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin.
Engine is INNODB with file per table enabled.
I used a query to generate identify all utf8 columns from ...
0
votes
2answers
44 views
Select query is too slow
I am facing a really strange problem with my database. I have a table with less than 20 columns. When i run a select * query, it's taking almost 6 to 7 seconds to execute. I couldn't identify the ...
1
vote
1answer
66 views
Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?
We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for ...
1
vote
1answer
79 views
Measuring IOPS for innodb_io_capacity
Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:
measure random write throughput of your storage and set ...
0
votes
2answers
46 views
How does MySql(InnoDB) update secondary non unique index?
Let's say we have a table with the following structure:
CREATE TABLE Persons (
PersonID int NOT NULL PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255)
);
CREATE INDEX Lastname ON ...
2
votes
1answer
21 views
Why MySQL pick a different index for the same query structure but different values?
Right now i have some issues understanding how MySQL picks the index to use on a query. I am executing a "COUNT QUERY", but everytime i use a different foreign key for the query, MySQL picks ...
0
votes
0answers
59 views
How to restore a MySQL InnoDB with ONLY .ibd files?
I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able ...
0
votes
0answers
21 views
Innodb buffer pool internal
I'm learning the Innodb buffer pool of mysql 5.7.
And I have some question about that.
What is the relation of buffer pool,buffer block,buffer page and buffer chunk?
How to locate a row in the ...
0
votes
0answers
16 views
Restore from ibd files where is no frm files [duplicate]
I had an Ubuntu 18.04 with mysql 5.7. I upgraded it to Ubuntu 20.04 with mysql 8.x. But this 8.x not was compatible with my Drupal 7 and I tried to downgrade. After this I tried with a 5.7 docker ...
0
votes
0answers
20 views
InnoDB engine lock tables higher than tables used
I am running the SQL statement
CREATE OR REPLACE TEMPORARY TABLE my_schema.tmpx_test AS
SELECT col1
, SUBSTR(col2, 1, 5) AS col3
FROM my_schema.my_table
WHERE col4 = 'my_string'
AND POSITION('...
1
vote
2answers
166 views
How to optimize Mysql database of 250 million rows for bulk inserts and selects
I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being ...
0
votes
0answers
31 views
MySQL - how to optimize select within a range bound by values in 2 different columns - innodb
How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed.
Each instance of a word points to only 1 ...
0
votes
0answers
135 views
Cannot rejoin or add instance to MySQL InnoDB cluster
I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster ...
1
vote
1answer
55 views
When a table is locked but SHOW PROCESSLIST shows nothing
We had some slowness in our application. When I checked I figure out that one particular table was locked so all transactions were rejected by Lock wait timeout exceeded. When I did SHOW PROCESSLIST; ...
1
vote
2answers
52 views
Speed up large database queries
I am running a large local database with MySQL and InnoDB. The size is around 30GB for data and 6GB for Keys. The machine has 16GB of RAM.
The table looks like this
symbol VARCHAR(10),
iso_datestamp ...
1
vote
1answer
46 views
Safe values for InnoDB variables when using FULLTEXT searches
I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The ...
0
votes
0answers
11 views
Is it theoretically possible to INSERT and DELETE a row using the InnoDB storage engine before any changes are written to disk?
I'm debating whether to use the InnoDB or MEMORY storage engine for a high-frequency INSERT/DELETE access token table, and I want to minimize the amount of disk writes. If it's possible for InnoDB to ...
0
votes
0answers
15 views
mysqldump causing database to get not saved records
I wish there was a safe way to backup mysql database. The database itself is getting not saved records if the records was about to be saved duding the dump. It is happening occasionally, maybe when ...
0
votes
1answer
674 views
MariaDB crashed: Unknown/unsupported storage engine: InnoDB
I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables:
2020-10-17 0:51:18 ...
0
votes
0answers
57 views
Cannot drop empty database in mariadb
I am having trouble dropping a database on my server
innodb-file-per-table is set to true
We previously had a huge table (1TB) which we used a drop table command. This result i believe was ...
