Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
0
votes
0answers
6 views
How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY
I wrote the following SQL query to group results and then sort results in each group by date:
SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
ORDER BY departure_date
...
3
votes
0answers
18 views
Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?
I have a big table (30 millions rows) defined like this:
CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT ...
0
votes
1answer
17 views
Subtract values in same column from 2 different dates in same Mysql table
I have a MySql table like:
Item Date Likes
Book 2012-05-20 402
Book 2012-05-21 432
Book 2012-05-22 450
Pen 2012-05-20 20
Pen 2012-05-...
0
votes
1answer
29 views
How to find the MySQL query that got rolled back
I want to know if a certain transaction got rolled back. If we analyze
SHOW ENGINE INNODB STATUS\G;
we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
1answer
25 views
Planning and Optimizing the table structure InnoDB MySql
I am planning to have an InnoDB MySql table with tens of millions of rows.
Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
2answers
35 views
Upgrade Antelope to Barracuda on Slave
I received a Last_Errno: 1118,
Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row ...
1
vote
0answers
23 views
Why is my InnoDB table size so much larger than the data?
I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
1
vote
1answer
50 views
MariaDB 10.3: InnoDB much slower than MyISAM
I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:
drop ...
0
votes
2answers
30 views
How does MySQL store data, can I detach a mysql Database?
I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
0
votes
0answers
30 views
MySQL InnoDb crash when importing tablespace from remote machine
InnoDB crashes when importing ibd file from remote machine. Source version is MySQL-5.6.40, Destination version is MySQL-5.6.40.
Database was backed up was backed Percona Xtrabackup with xbstream ...
0
votes
1answer
28 views
Safe online dumping of an InnoDB database in the presence of DDL changes
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
1
vote
2answers
59 views
Overly eager explicit row locking causes deadlock
I'm running into deadlock problems on MariaDB using the INNODB engine.
In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
0
votes
0answers
15 views
tokudb, rocksdb or aria engine with huge number of tables
My question is about what engine to use with a super big quantity of tables and lots of tables with some million rows.
My quantity of table is increasing and it will get to near 100 000 tables...
...
4
votes
2answers
47 views
Performance issues with groupwise MAX in JOIN clause
Problem
I have some assets in my application which are updated from time to time in an asynchronous fashion.
The example I'm gonna use here is Vehicles. There are two tables:
Vehicles: holds ...
2
votes
1answer
67 views
SELECT execution is slow on InnoDB when compared to MyISAM
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about ...
5
votes
4answers
453 views
Which columns should be indexed when all may be used in different search queries?
Background
I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
1answer
28 views
MySQL: Change compression on an existing InnoDB table
Is there any way to turn on compression for an existing table? I don't care if there's downtime for this.
The docs only mention setting compression when the table is created.
0
votes
2answers
37 views
Unable to fix ROW size too large even with innodb_file_per_table
I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large"
Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table`
Row size ...
0
votes
1answer
37 views
Unable to turn on compression using innodb
I am unable to create a new table using compression.
I'm using the following options which from what I understand should allow me to use compression.
default-storage-engine = InnoDB
...
2
votes
2answers
57 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
63 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
15 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
18 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.
...
0
votes
2answers
36 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
31 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 ...
1
vote
3answers
205 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
22 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.
0
votes
0answers
82 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
52 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
61 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
1answer
28 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
82 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
40 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
38 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
19 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
71 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
16 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
35 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
11 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
11 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
44 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
3answers
41 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
40 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
30 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
73 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
72 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
32 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
113 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
36 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 ...
