Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
1
vote
1answer
101 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 ...
2
votes
3answers
4k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
6
votes
2answers
26k views

Won't start: InnoDB: Corrupted page … Tablespace was not found

After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows: InnoDB: Checksum mismatch in datafile: ./...
0
votes
2answers
89 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 ...
18
votes
5answers
49k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
4
votes
2answers
2k views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
2
votes
1answer
33 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 ...
2
votes
1answer
187 views

Daemon crashes daily due to lock error

For a while now our MySQL has been crashing daily, sometimes multiple times even. I've troubleshot a lot but nothing seems to help, it's also a live server so I have to be careful with tests/...
1
vote
2answers
578 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
32 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
18 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
22 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 ...
103
votes
5answers
138k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
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('...
0
votes
1answer
81 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; +-----------+----------...
31
votes
4answers
48k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
1
vote
1answer
76 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
59 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
51 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 ...
3
votes
2answers
6k views

MySQL auto increment column increases after insertion error occurs

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row ...
0
votes
3answers
1k views

MySQL UUID() behavior

4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk". I answered, after trying it, UPDATE table SET uuidcol = UUID(); It worked for me, but about half (it ...
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 ...
1
vote
3answers
2k views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)? Does the engine ...
0
votes
2answers
421 views

Foreign key constraint is incorrectly formed (MySQL) [closed]

So, I have these two tables CREATE TABLE IF NOT EXISTS `inart_ps_categories` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `inart_ps_category_id` int(11) UNSIGNED NOT NULL, `parent_id` int(11)...
0
votes
0answers
65 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 ...
4
votes
1answer
6k views

How can I find out the last time a MySQL table was analyzed?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.
0
votes
2answers
28 views

innodb_force_recovery

I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump. Customer is now saying some of data is wrong. (References to another table) Is this possible?
2
votes
2answers
93 views

What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server ...
0
votes
1answer
493 views

mysql ibd file in file system

I am in searching of where mysql stored its indexes for innodb engine.I found it stores in ibd file but where I will found those files on system.Can anyone help please?
1
vote
1answer
16 views

MySql IDB files not named after tables

In my Linux filesystem, there are many ibd files (InnoDB file per table is ON) which are not named after tables. What are these files and what data do they contain? The question has arisen due to ...
6
votes
3answers
21k views

MySQL - Simple update is very slow

We are having trouble with simple updates on a single table taking a long time. The table contains ~5 Million rows. Here is the table: CREATE TABLE Documents ( GeneratedKey varchar(32) NOT NULL, ...
0
votes
0answers
40 views

Does commit after a normal select can call any fsync or flush operations?

I'm using Mariadb 10.2.16. 3 Node cluster. Coms counts taken at particular time for all 3 (insert,update,commit). Com_inserts are in 1.1K Com_updates are in 1.6k Com_commit are in 10K When ...
2
votes
3answers
189 views

15 seconds to insert a single row in MYSQL 5.7 (happens occasionally)

I'm seeing very sporadic slow queries in MYSQL, at least, that's where Datadog is suggesting the issues are. I have, for example, 5 queries out of 726K traces (both selects & updates) in the last ...
0
votes
0answers
176 views

MySQL replication error 1452

could you help me to figure out the solution for a MySQL replication error? mysql replication error: (Last_Errno: 1452) Last_SQL_Error: Error
'Cannot add or update a child row: a foreign key ...
0
votes
1answer
26 views

is it possible to reset innodb_row_lock without restart MySQL 5.7

Now I could using this command to check the innodb_row_lock values: show status like 'innodb_row_lock%'; Innodb_row_lock_current_waits 4 Innodb_row_lock_time 22471927 Innodb_row_lock_time_avg ...
-1
votes
1answer
24 views

Nullable integer as part of composite primary key

I want to store phone numbers as integers and I've created this SQL: DROP TABLE `user_id_phone`; CREATE TABLE `user_id_phone` ( `user_id` int unsigned NOT NULL, `country_code` ...
0
votes
0answers
19 views

What files of Mysql lib directory safe to be deleted and to be recreated with innodb_file_per_table settings?

I have brand new mysql 8 setup and installed on a centos 7. Unfortunately I forget to set innodb_file_per_table. I would like to dump the database and recreate with the innodb_file_per_table settings. ...
10
votes
2answers
17k views

MySQL not releasing memory

MySQL seems to want to keep an entire table in cache (table size = ~20GB) after any large inserts or select statements are performed on it. Right now my innodb buffer pool is 20GB. Total RAM is 32GB. ...
1
vote
2answers
54 views

MySQL performance degraded after database migration?

I migrated my MySQL database from GCP to Azure (both 5.7), but it seems to have affected performance. Server before migration: 2 VCPUS with 7.5GB memory Server after migration: 2 VCPUS with 8GB memory ...
0
votes
1answer
54 views

How to do safety mirroring innodb table on mysql?

I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report). As I know this heavy select query will ...
0
votes
1answer
32 views

order by slowing down query with multiple joins and limit/offset on larger result sets

I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on ...
1
vote
2answers
2k views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
11
votes
2answers
21k views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
9
votes
5answers
17k views

MySQL .ibd file is too big

I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all ...
0
votes
0answers
48 views

Taking InnoDB MySQL/MariaDB cold file backup and restoring on new Omeka DB

I have an unresponsive Omeka MySQL/MariaDB database on a Windows server. It is unresponsive in the sense that it will not start from the XAMPP panel: 10:44:15 AM [mysql] Attempting to start ...
0
votes
1answer
444 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
0
votes
1answer
88 views

MySQL table queries extremely slow

I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like CREATE TABLE `measurement` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` int(11) DEFAULT NULL, `field2` ...
6
votes
4answers
7k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
0
votes
1answer
76 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...

1
3 4
5
6 7
42