Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

423 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
1answer
1k views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
7
votes
1answer
848 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
5
votes
0answers
1k views

MySQL: Is the read committed isolation level safe with mixed binary log format?

When using the MIXED format with the READ COMMITTED isolation level, am I safe? The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed) If you use READ ...
4
votes
0answers
3k views

Getting “Cannot open datafile for read-only” for an intentionally DROPped InnoDB table

It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB): [ERROR] InnoDB: Cannot open datafile for ...
4
votes
0answers
1k views

InnoDB crashes on trx rollback

I have InnoDB assertion failure during the trx rollback 160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo 160316 10:47:31 InnoDB: Assertion failure in thread ...
4
votes
2answers
1k views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
4
votes
0answers
181 views

Flexible and robust way to cull older data?

I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...
4
votes
1answer
500 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
4
votes
1answer
6k views

How to prevent “ibdata files do not match the log sequence number”?

I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort: ibdata files do not match the log sequence number But I've ...
4
votes
1answer
809 views

Is this surprising deadlock reasonable or a MariaDB bug?

I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a ...
4
votes
2answers
465 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
3
votes
0answers
46 views

Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB

I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
3
votes
1answer
31 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 ...
3
votes
1answer
8k views

InnoDB: Unable to lock ./ibdata1 error: 11

I'm pretty sure this issue has occured before and I've looked around some of the answers. However, I wasn't able to find someone with an exactly similar problem so I thought I'll ask a new question. :(...
3
votes
1answer
136 views

Table was deleted during ALTER, how to recover?

I tried to ALTER a table to add a FOREIGN KEY as: ALTER TABLE artists ADD FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE; and I received an error of ERROR 2013 (HY000): ...
3
votes
0answers
204 views

When would you use InnoDB PAGE compression over InnoDB TABLE compression?

From what I understand, InnoDB table level compression is "transparent" and appears to perform well on the surface. In what circumstances would it be beneficial to choose InnoDB page level ...
3
votes
0answers
167 views

Fast index creation issue

One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually ...
3
votes
0answers
641 views

TokuDB vs InnoDB Database data size

I have two percona servers, one running with TokuDB tables and the other with InnoDB tables. On each server, i have create a single database, with a single table in it containing 10 millions records ...
3
votes
0answers
180 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
3
votes
0answers
2k views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
3
votes
0answers
2k views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
3
votes
1answer
978 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
3
votes
0answers
3k views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
3
votes
0answers
1k views

“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28. During high load, randomly we got the below error. MySQLcheck, innochecksum shows no ...
3
votes
4answers
190 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
3
votes
1answer
290 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to be ...
3
votes
1answer
3k views

InnoDB page_cleaner error message: The settings might not be optimal

My server is in idle state but I often get this error: Note: InnoDB: page_cleaner: 1000ms intended loop took 35165ms. The settings might not be optimal. flushed=0 and evicted=0, during the time ...
2
votes
1answer
89 views

Partitioning and the InnoDB Buffer Pool

I am trying to maintain a database that contains a table with 40 billion rows (7.2 terabytes) on a server using InnoDB as a storage engine and MariaDB with MySQL 5.5. When my database reaches about 2....
2
votes
0answers
190 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
2
votes
0answers
39 views

Why InnoDB storage engine not loading csv files concurrently?

While loading different csv files to different tables of the same database using innodb storage engine(even tested on aria), csv files seems not to be loaded concurrently. Because, a single csv ...
2
votes
0answers
880 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 ...
2
votes
0answers
360 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 ...
2
votes
1answer
3k 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. ...
2
votes
0answers
1k views

Database design for Subscription, Coupons and Plans

I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem. I am developing a website ...
2
votes
0answers
1k views

MySQL memory usage is too high - OOM

Im using 32vCPU and 120GB VM for Mysql 5.6. it's a dedicated MySQL server. 15000 IOPS SSD disk. Total database size 40G This server was working fine for a couple of months. Memory usage is ...
2
votes
0answers
97 views

IDB file Maximum kb limit

I'm new in handling MySQL database, I made a tracking system I using the MySQL database incorporated on the xampp package as a database. Everything is running smoothly, however upon reaching 90,112 kb ...
2
votes
1answer
63 views

Caching queries that last longer then then X seconds

Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it. ...
2
votes
1answer
788 views

Full database backup using xtrabackup stream

I'm new to Percona XtraBackup. I've been trying to perform a full backup stream from my local ( test server around 600GB ) to remote server. I just have some questions and I need guides, and I think ...
2
votes
0answers
834 views

innodb corrupted - how to rebuild it?

Well, long story... I was working in my iMac, in a huge project, using Mysql Communit. Today, I had a energy problem, and guess what, the computer turned off and MySql does not started anymore. ...
2
votes
1answer
67 views

Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified?

I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query: select i.table_name, i.index_name, i.rows_per_key, coalesce(t.rows_read, 0) as ...
2
votes
0answers
805 views

“Can't write; duplicate key on table” on insert / update?

I have a strange issue with InnoDB on MariaDB. Sometimes (and I can't reproduce it, but it happens regularly and multiple times a day on a busy server) I get this SQL error: Can't write; duplicate ...
2
votes
0answers
584 views

mysql.innodb_index_stats

I am seeing many errors like the following for my MySQL instance. It appears that it is complaining about an index that does not exist anymore. This is an InnoDB table. What do I need to do to update ...
2
votes
1answer
2k views

How to optimize a Full Text Search

So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause. Doing this search slows the query down by about 300ms and since the query is run a few ...
2
votes
1answer
865 views

innodb_buffer_pool_size more data than pool size

I have 64gb ram on server. InnoDB buffer pool is set to 26.1gb [OK] InnoDB buffer pool / data size: 26.1G/24.3G Im wondering what happens if data over grow current pool size, as i understand it ...
2
votes
0answers
1k views

How to get records from corrupted ibd file

Ok, in the last two days I thoroughly searched through the internet, and found multiple propositions on how to handle this, but nothing worked so far. Here is what the problem is and what I tried so ...
2
votes
0answers
1k views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
2
votes
0answers
1k views

Negative InnoDB Write Log efficiency in mysqltuner.pl results

I noticed something strange in results from mysqltuner.pl. I see this in mysqltuner output from three of my four servers with MariaDB 10.1.12: [OK] You have the latest version of MySQLTuner(1.6.5) ...
2
votes
0answers
87 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
2
votes
0answers
395 views

mysql innodb - strange data loss

I am running a mysql database on linux with several InnoDB tables. At some point, the ibdata1 file got quite big and I wanted to delete it. So I made a dump, deleted the file and restarted the server. ...
2
votes
0answers
276 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...

1
2 3 4 5
9