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

Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included)

Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again). I have made the big fault of recommending a database upgrade of our productive environment to ...
0
votes
1answer
11 views

How DML queries execute in MySQL InnoDB

I want to know if I fire query like insert into emp ... What are the steps involved in query ? It will go through InnoDB buffer pool? I know how select query execute in MySQL when MySQL uses default ...
0
votes
0answers
5 views

ERROR InnoDB: Trying to access page number .. in space .., space name…, which is outside the tablespace bounds

I am running a mysql database server (5.7) on a virtual ubuntu 16.04 host. This host collects syslog messages from my other network hosts, adds the values to a pipe and creates mysql records for each ...
0
votes
2answers
29 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
0answers
10 views

MariaDB 10.2 crash on innodb table corruption - trying to restore the database

I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
2
votes
0answers
40 views

Recursive query

I'm not sure if I need exactly a recursive query but this is what I'm trying to do. This is my Minimal Complete Verificalbe Code: I have a similar table like this (MySQL v5.7, InnoDB): CREATE TABLE ...
0
votes
1answer
15 views

how to remove Mariadb temp files

I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November. -rw-rw---- 1 mysql mysql 1032192 Nov 19 07:47 #sql899_5d5_0.ibd -rw-...
1
vote
1answer
49 views

MySQL Crashed, innodb trying to access page number which is outside the tablespace bounds

I am running MySQL (5.7) on a Windows 2012 server. One of the table (> 1 million records) seems to be corrupted. The mysql error log shows 2019-01-16T09:41:09.256990Z 0 [Note] InnoDB: Buffer pool(s) ...
0
votes
2answers
35 views

How full is my innodb shared table space?

How can I find out how much data is in the innodb shared table space. I have a massive shared idb file but I suspect that it contains only about 350 Gigs of data. I need to know how full it is so I ...
0
votes
0answers
25 views

What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?

Mysql version: 5.7.18 (Innodb) I have a table with the following specification Structure: CREATE TABLE `dummybigint10` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT 'A ...
0
votes
0answers
17 views

Deciphering InnoDB status logs

I noticed MySQL deadlock errors in my application logs and for investigation, I queried InnoDB status and got the below mentioned Latest detected deadlock *** (1) TRANSACTION: TRANSACTION ...
1
vote
2answers
66 views

MySQL Select Union; Filter by Shared Relation?

Currently, I have 3 tables (see structure below), speakers, venues, and events. I'm trying to do a relevance search on the names of the speakers and venues. However, on the Search Results page, if the ...
0
votes
0answers
13 views

Ignore keys while mysql command line export

I am writing a bash script to export a few tables from the MySQL DB and import in the backup DB. I will repeat this process on an hourly basis and will keep all the tables by changing the table name. ...
0
votes
1answer
24 views

innodb cache duplication due to os page cache and buffer pool

We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows ...
0
votes
1answer
8 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
0
votes
0answers
9 views

amazon mysql 5.6 rds innodb_file_per_table 0 drop then create same database side effects of storage

I had an event last night where I dropped a database and then created the same database right after each other (then populated it with minimal data). Then about 2 hours later I got a disk alert that ...
0
votes
0answers
24 views

What does `queries inside InnoDB` mean in the report of show engine InnoDB status?

> show engine innodb status ===================================== 2018-12-26 05:24:21 7f0c43bfb700 INNODB MONITOR OUTPUT ... ... ... ... -------------- ROW OPERATIONS -------------- 64 queries ...
1
vote
2answers
27 views

InnoDB: will uncommitted transaction produce redo log?

Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log. start transaction; update users set uuid = UUID() from user where id = 1 update ...
-1
votes
1answer
15 views

Cannot find innodb_page_size option in [mysqld] section in my.ini file

Where can I find innodb_page_size option? Since I cannot find it in [mysqld] section in my.ini file. I want to change it to 32k or 64k since I am getting the below error: MySQL: Error Code: 1118 ...
0
votes
1answer
23 views

innodb_page_size is not present in [mysqld] section in my.ini file in MySQL Server 8.0?

I have to edit the innodb_page_size to 32K but I cannot find innodb_page_size in [mysqld] section in my.ini file. I am using MySQL Server 8.0.13. Where can I find it?
1
vote
2answers
69 views

MySQLTuner request - high CPU problems

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately. Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day. ...
0
votes
0answers
26 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
27 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
23 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 ...
-1
votes
0answers
21 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
31 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
29 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
32 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
1answer
43 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
37 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
38 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
22 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
45 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
48 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
13 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
27 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
2answers
53 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
60 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
2answers
121 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 ...
-1
votes
0answers
11 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
32 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
34 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
29 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
30 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
2answers
56 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
111 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
20 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
48 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 ...