Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

386 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
1answer
985 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....
5
votes
1answer
760 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 ...
4
votes
0answers
1k 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

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
1answer
486 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
5k 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
536 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
429 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
1answer
30 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
0answers
6k 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
118 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
191 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
149 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
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 ...
3
votes
0answers
606 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
2answers
927 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 ...
3
votes
0answers
156 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
165 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 ...
3
votes
2answers
5k views

MariaDB is ignoring /etc/my.cnf

The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728. When I execute: /usr/libexec/mysqld -...
3
votes
0answers
1k 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
915 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
902 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
2answers
2k views

Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

Can two or more instances of the following deadlock with one another? START TRANSACTION SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE : COMMIT Points to keep in mind: mycolumn ...
3
votes
1answer
258 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 ...
2
votes
0answers
66 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
38 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
439 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
242 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
0answers
666 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
692 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
65 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
62 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
0answers
494 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
561 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
61 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
644 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
1answer
580 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
916 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
81 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
382 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
252 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, ...
2
votes
0answers
57 views

FullText search using only some of columns in index

I have a full text index on (name,category,description) as shown below. mysql> show create table phppos_items; +--------------+----------------------------------------------------------------------...
2
votes
0answers
300 views

Mysql Match…against query performance in InnoDB Mysql 5.6

I need to search records in the table which contains millions of records. I have recently updated Mysql version from 5.1 to 5.6. I was using like in query which was taking around 15 sec to 30 sec. ...
2
votes
0answers
906 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
0answers
51 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
0answers
88 views

Denormalizing to speed up inserts

I have a MySQL InnoDb table with approximately 40 million rows and the following columns: param1 (int), param2 (int), param3 (int), geo (char2), value1 (bigint), value2 (bigint) I have an ...
2
votes
0answers
2k views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...