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` ...
