Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
2
votes
2answers
152 views

When does MySQL begin to start sending data to a client?

I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
0answers
89 views

Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?

Important Update Spent 15 hours on this problem, rebuilt and changed indexes and the table many times. Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue. The "...
1
vote
0answers
25 views

Getting DEADLOCKS frequently on Versioned TABLE

I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we ...
0
votes
0answers
25 views

Is it possible to convince MySQL to use an index with USE INDEX FOR JOIN, even after no longer joining?

It's been a long day, but I've been wresting with a query which wont use an index, and thus takes much longer than it should. I stumbled upon USE INDEX FOR JOIN, but it didn't improve anything. ...
1
vote
3answers
591 views

SELECT … FOR UPDATE returns old value after waiting for lock

It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from ...
0
votes
1answer
206 views

Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

Whenever my MySQL 5.7 starts I get the warning InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set bar is a table I experimented with long time ago with import tablespace, but ...
0
votes
0answers
15 views

InnoDB: does `redo log` contain initial value and final value? or just final value?

start transaction; update users set age = 34 where user_id = 3 // update age from 30 to 34 years old. .... .... when I am updating the age of this user, InnDB will write redo log. What's the ...
4
votes
3answers
3k views

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

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
43 views

How DML queries execute in MySQL InnoDB [closed]

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? Thanks for your help.
2
votes
0answers
430 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
3answers
333 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
191 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 ...
0
votes
2answers
471 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-...
0
votes
2answers
83 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
285 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
24 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
83 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
1answer
89 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
67 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 ...
1
vote
3answers
134 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
173 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
66 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
91 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
1answer
48 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
329 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
2answers
57 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
57 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
79 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
558 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
39 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
289 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
145 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
57 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
411 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
2answers
346 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
81 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
211 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
359 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
67 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 ...
0
votes
2answers
905 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
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
108 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
88 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
70 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
365 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
260 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
743 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
34 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
85 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
241 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 ...