Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

0
votes
0answers
7 views

What steps can I take to recover MySQL InnoDB databases when forced recovery fails?

Coming here from StackOverflow because I saw a similar question being closed as off-topic on there. I have corrupted my local MySQL instance in my development environment. I was running a set of ...
0
votes
1answer
24 views

What is the relation between MySQL pages and Operating system pages?

By MySQL pages i mean this : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html and operating system pages : https://en.wikipedia.org/wiki/Page_(computer_memory) I understand both ...
0
votes
1answer
17 views

Cannot allocate memory for the buffer pool when using remote access MySQL

I tried to separate the webserver and mysql server. MySQL server = 16GB RAM and the config of Innodb_buffer_pool_size = 10GB. And my Web server = 4GB RAM My website connect to my database server ...
0
votes
0answers
20 views

If deleting rows does not affect the index pages of that table, wouldn't this cause problems after some time? (MySQL)

So based on this article : https://www.sciencedirect.com/science/article/pii/S1742287616300500 When a row is deleted, DBMS does not delete the corresponding index values e nor are such index ...
0
votes
2answers
30 views

Time taken to drop/add index is lesser than adding a column. Why is that?

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our ...
0
votes
1answer
27 views

Which MySQL files in InnoDB are made of pages and how are they structred?

Definition of InnoDB page is here : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html InnoDB stores all records inside a fixed-size unit which is commonly called a "page" (though ...
0
votes
1answer
30 views

MySQL innodb: if all the tuples are stored in the clustered Index then why do we call it index?

So not sure If I am confused or not, but based on these websites : https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/innodb-table-and-index.html http://www.mysqltutorial.org/mysql-index/mysql-...
0
votes
0answers
48 views

Server with 58GB of free RAM kills MySQL

We have a OVH Server with Web Server, Database and other processes. It has: Debian 7 64GB of RAM (often up to 7GB of 64GB used) 1 socket, 4 cores x socket, 2 threads x socket = 8 CPU(s) How it's ...
0
votes
0answers
14 views

Error 2013: Lost connection during Query when dumping the table

I try to add the column on the table by using alter query almost query is going to complete in that time it shows an error Error Code: 2013. Lost connection to MySQL server during query. I increase ...
0
votes
1answer
23 views

InnoDB ibdata1 corrupted, innodb_force_recover not working

I think I have checked every post in the internet. Thing is my MySQL (MariaDB) won't come up and after much testing I have seen it is because of the InnoDB files. I have tried with different ...
0
votes
1answer
36 views

What does the DBMS do exactly when i send it a SELECT query regarding the .frm files and database pages? (MySQL)

so i was reading about database pages and DBMS in mysql and it got me really confused so based on their documentation database pages have a part where it points to rows in the page, which have tuples ...
0
votes
2answers
33 views

What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL? Database pages are smallest read write unit ...
0
votes
1answer
13 views

MySQL Range Optimization and Multipart index

I'm new to the MySQL world. When I read about MySQL Range Optimization some questions were raised related to multipart indices. MySQL 5.7 Doc (http://jorgenloland.blogspot.com/2011/08/mysql-range-...
0
votes
1answer
44 views

BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT

This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table: | column name | type | |----------------+-------------| | id ...
1
vote
1answer
44 views

Huge gap in autoincrement primary key

I Have normal InnoDB table with autoincremental primary key. There Is about 100 thousands records. Accidentally was saved record with ID 10 million. Other record continue in this row. Is any possible ...
0
votes
1answer
11 views

#1146 table 'db.tablename' doesnt exist

I use AMPPS for localhost server purpose. I was working on one of my client site on localhost. When I start working the other day, it suddenly start giving me such error table does not exist Even ...
1
vote
3answers
23 views

Does MariaDB 5.5.56 have INFORMATION_SCHEMA.INNODB_METRICS?

I'm trying to debug why I don't have INFORMATION_SCHEMA.INNODB_METRICS. I'm uncertain because I see it in the MySQL 5.5 reference glossary: https://dev.mysql.com/doc/search/?d=10&p=1&q=...
3
votes
1answer
41 views

How to use Limit in query on my index column without scan all rows?

Here is my table : In my table Clustering_key (Primary key and auto incremental) ID (Index Column) Data (Text datatype column) Position(Index column) maintain the order of Data My table have 90,...
0
votes
1answer
50 views

InnoDB Buffer causing a lot of deadlocks and connection/client timeouts

I have a PXC cluster (5.7) with three nodes running on CentOS 7 VMs, each with the following system info: mysql_db# free total used free shared buff/cache ...
0
votes
1answer
33 views

Database schema tracking patient stats over time

I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time. I found a database schema like the one discussed in this Q & A ...
1
vote
1answer
92 views

MySQL crashes and currently the error.log file is huge

I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
0
votes
0answers
9 views

PostgreSQL checkpointing and MySQL InnoDB checkpointing algorithms - what is the main difference?

I have read this http://www.interdb.jp/pg/pgsql09.html and understood why and how PostgreSQL checkpoints works and what problems solves and how system restore itself after any kind of crash. Is there ...
1
vote
1answer
30 views

“Centralising” a “decentral” design

I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of ...
1
vote
2answers
31 views

InnoDB engine seems lock the table automatically in Mysql5.7

I have a table named Students like: mysql> select * from students; +----+-------+---------+ | id | name | classId | +----+-------+---------+ | 1 | Kris | 7 | | 2 | Tom | 7 | | 3 ...
0
votes
1answer
29 views

Why are my InnoDB tables larger than calculated?

I have a DB where some of the tables are much larger than I think that they should be. For 23 of 39 tables the following query overestimates their size, as it is designed to. But for 16 tables my ...
0
votes
0answers
23 views

Mysql does not show correct count on rows

I have this mysql installation that i am using to test some java program that has multiple threads.At first, i noted that some rows dissapeared and i thought it was because of this error InnoDB: ...
1
vote
1answer
26 views

mysql deadlock when update some items whose id are selected from another table

I'm sorry, I just can speak a little English. I have two machines(10.10.102.101, 10.10.102.104) which connect to the same mysql datebase. This is a table and deadlock happened on it. mysql> ...
1
vote
1answer
161 views

MySQL randomly slowing done due to context switching [closed]

I've been tracking down a problem with my config for the last 2 weeks and I've hit a wall. Hopefully somebody will have a clue and be able to point me in the right direction. Randomly throughout the ...
1
vote
2answers
46 views

MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

I am issuing some long-running delete queries to some databases, and I am getting the following error: Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout ...
0
votes
2answers
62 views

How to Purge transaction history in MySQL

It looks like we have hung transaction(s). But we can not find it. show engine innodb status ------------ TRANSACTIONS ------------ Trx id counter 1489951061 Purge done for trx's n:o < ...
0
votes
1answer
19 views

Need some insights on 'index_page' variables in Innodb_Metrics Table

I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb. For sequencial Inserts I got: index_page_splits: 3199 index_page_merge_attempts: 0 ...
0
votes
2answers
44 views

Log full of [Note] InnoDB: ft_end() after upgrade to MariaDB 10.3.10

My upgrade path was MariaDB 10.2.18 to MariaDB 10.3.10. There are no bugs reported on mariadb or mysql bug tracker (at least that I could find). ... Oct 24 17:45:33 host1 mysqld[12196]: 2018-10-24 ...
2
votes
0answers
30 views

missing ibd files

I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables. When I try to mysql I get the following messages for the missing ...
1
vote
1answer
31 views

How are page splits determined internally in MySQL?

Going through this answer for a stack-overflow question, I found a fact that In The physical structure of InnoDB index pages I describe the Last Insert Position, Page Direction, and Number of ...
2
votes
1answer
22 views

InnoDB: Getting rid of a phantom table

I was playing around on a test server and i actually removed both "frm" and "innodb" files of particular table. Ok - I tried creating that table again but got information that table already exists and ...
2
votes
2answers
63 views

MySQL Performance vs. Memory

We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD). Besides MySQL, we also run some Java services on this machine, which access the ...
1
vote
2answers
96 views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
0
votes
1answer
22 views

Can select read index page while modify this page in innodb?

When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?
0
votes
2answers
29 views

MySQL - Delete with IN Clause taking more time

In MySQL 5.6 I have around 6 crores of rows. I wanted to remove rows based on Id. Here Id is the primary key. One strange behavior I had today. delete from tbl where id=1; Its executed in ...
1
vote
1answer
29 views

Recommended innodb_file_per_table value for AWS MySQL RDS database

We have the following scenario: Our databases are hosted on AWS MySQL 5.6, and we are planning the following migration: we currently have 10 distinct instances with about 50 databases split between ...
1
vote
2answers
55 views

When mysql innodb undo log write and fsync to disk?

Before transaction commit , every dml operation will modify innodb page which will flush to disk and generate undo log , so when the undo log write and fsync to disk to ensure rollback ?
0
votes
1answer
78 views

What does innodb_table_locks setting do exactly?

Can someone please explain how this setting affects the locking behaviour in innodb (with an example). I ran numerous experiments with different configurations of (autocommit, innodb_table_locks, LOCK ...
2
votes
1answer
24 views

Optimising MariaDB for large AWS machines

Been reading up on DB optimisation (I am an analyst, not really a Sysadmin), and it would seem the biggest adjustment one can make is change the innodb_buffer_pool_size, innodb_buffer_pool_instances ...
1
vote
1answer
74 views

What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool?

We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are: 7.5gb RAM 2 VCPU RDS defaults to a calculation that allocates 75% of RAM to ...
0
votes
1answer
20 views

How mysql innodb know which transaction should rollback after crash restart?

I know when I execute dml sql before commit , innodb will write the result to the innodb page cache which can flush to data page and write to the undo log file ; but at the same time , mysql crash and ...
2
votes
1answer
34 views

Suggestions for large DB maintenance

i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database! Will the size be a problem ...
0
votes
2answers
87 views

InnoDB: mmap failed; errno 12

I am getting following error after modifying the innodb buffer pool size values. InnoDB: mmap(137428992 bytes) failed; errno 12 InnoDB: Cannot allocate memory for the buffer pool 137428992 is ...
0
votes
2answers
69 views

High traffic slows down website with 8Gb RAM Server - my.cnf configured

We configured the my.cnf of our server yesterday. Today comes to a high traffic on our site. The site is going slow as the traffic goes high, and for sometimes get a downtime. We already switch from ...
1
vote
1answer
122 views

Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start

MySQL process was killed due to memory issues. When start command was issued, server started and is working but a status command shows the following: InnoDB: 128 rollback segment(s) are active. ...
1
vote
2answers
62 views

Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about. CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` ...