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