Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,013
questions
0
votes
0answers
10 views
when mysql lock table in innodb engine [closed]
I have recently come across I have table lock in my transaction. However, as far as I know innodb engine use row lock.
I wanted to know when innodb lock table instead of rowlock (other than lock table....
2
votes
1answer
14 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
19 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
19 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
13 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
17 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
64 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
30 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
14 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 ...
0
votes
1answer
32 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
40 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
34 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
14 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
172 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
36 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 ...
0
votes
2answers
16 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
69 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 ...
1
vote
1answer
15 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 ...
0
votes
2answers
74 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
39 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 ...
0
votes
0answers
42 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
MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)
Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by ...
-1
votes
1answer
14 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
7 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. ...
0
votes
1answer
14 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 ...
0
votes
1answer
66 views
Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?
I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
1
vote
2answers
34 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
27 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 ...
0
votes
0answers
26 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
47 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` ...
3
votes
1answer
79 views
I can´t change innodb_buffer_pool_instances
I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances
innodb_log_file_size = 3G
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
But when
...
0
votes
0answers
68 views
Innodb error log sequence number is in future
2020-08-28 16:46:02 d64 InnoDB: Error: page 1 log sequence number 23113680
InnoDB: is in the future! Current system log sequence number 3181581.
InnoDB: Your database may be corrupt or you may have ...
0
votes
1answer
31 views
MySQL stops in windows VM and shows innodb os error in logs(error no. 5)
2020-08-23 17:22:36 5688 [Note] Plugin 'FEDERATED' is disabled.
2020-08-23 17:22:36 5688 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-08-23 17:22:36 5688 [Note] InnoDB: The InnoDB ...
0
votes
1answer
21 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 ...
0
votes
0answers
102 views
Mysql InnoDB Cluster
I'm trying to setup mysql innodb cluster. I have couple of Digital Ocean (DO) instances running centos 7.
I have installed Mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL) on the DO ...
1
vote
2answers
32 views
How do I enable full text search on MySql 8?
I'm using MySql 8. I would like to speed up searches on my table of the form
select * FROM dirctory_coop where name like '%mystr%';
and someone mentioned using Full text searches -- https://dev....
0
votes
3answers
65 views
Is it normal for MySQL to gradually increase memory usage over time?
Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is ...
-1
votes
1answer
42 views
MySQL Query Fetch Time increases when LEFT JOIN with big table [closed]
I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
2answers
28 views
Transfer rows into a new table, forcing to generate unique primary keys till its insertable
Lets say we have the following scenario...
+-----------------+-------------+---------------+
| Table | PK | Additional |
+-----------------+-------------+---------------+
| ...
0
votes
1answer
48 views
MySQL write performance after truncate & optimize [closed]
Once a year we truncate a table of several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system.
However, as this is write ...
1
vote
1answer
86 views
Issues with MySQL 8.0.17
Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18.
We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
2
votes
3answers
100 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 ...
1
vote
0answers
41 views
Non-existent table that actually exists?
I've been tasked to solve a very difficult and interesting problem.
Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10.
We have an ...
0
votes
2answers
104 views
MySQL taking up 400% CPU
LEMP stack on DigitalOcean (48GB ram, 960GB SSD, 12 vCPU) with WordPress on it. Added a new theme and mysql went through the roof. 400% CPU usage taken up by MySQL itself!
Looked at slow-log to figure ...
0
votes
0answers
138 views
Trying to restore InnoDB tables
I've reinstalled MySQL but when I copy the data dir from the old instance:
maicol07@maicol07$ sudo mysqld --user=root
2020-07-14T13:07:34.031447Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (...
0
votes
0answers
76 views
Restart MySQL server while query is in Killed State
I need to reboot the server (not just MySQL instance) to perform some urgent server updates.
Currently on the MySQL server when I run:
select * from information_schema.processlist where info is not ...
1
vote
2answers
21 views
InnoDB storage for multiple BIT(M) columns?
Let's say if I have a table with four BIT(1) columns. Will InnoDB use 4 bytes to store these columns, or will it be smart and use 1 byte?
(Referring to MySQL 8, if it matters.)
0
votes
1answer
23 views
More tables or More records performance wise
i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below:
id
url
region_1
region_2
region_3
region_4
date_created
My ...
