Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

439 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
920 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 ...
6
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 ...
5
votes
1answer
7k 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
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
1answer
857 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 ...
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
2k 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
185 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
506 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
999 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 ...
4
votes
1answer
917 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
483 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
182 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
144 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
207 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
171 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
650 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
189 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
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
201 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
295 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
0answers
23 views

How to estimate table row size in MySQL InnoDB?

My particular problem that I'd like to solve is a table with very wide column (sometimes > 1000 columns) which requires me to split the table. In order to do so smartly, I'd like to split right ...
2
votes
0answers
139 views

MySQL/InnoDB cluster unable to reboot

I have a three-node MySQL/InnoDB cluster (version 8.0.25), that has been deployed by Juju/OpenStack a couple of month ago. The cluster has recently crashed and I unfortunately can't manage to bring it ...
2
votes
0answers
33 views

Is it worth using a JSON column type, even if it's only used for simple serialized strings or integers?

I have a table with an unidexed value column of VARCHAR(1024), and I've used my ORM to automatically convert it to and from JSON. The entire reason for this is I want it to be able to store any data ...
2
votes
0answers
138 views

Got fatal error 1236 from master when reading data from binary log

I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ? Background Needed to perform a host updates on master host. Hence ...
2
votes
1answer
124 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
244 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
1k 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
391 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
111 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
64 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
455 views

MySQL innodb_row_lock_time is more than wall clock time

We recently started graphing the innodb_row_lock_time status variable in our monitoring system. I noticed something I can't understand on a graph for a busy server: Between 04:55PM and 04:57PM, the ...
2
votes
0answers
950 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
68 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
885 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
639 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
2k views

What is locking order of MySQL on query with join statement

When I select for update from join multiple tables, matching rows in all joined tables will be locked. But I was wondering what is the locking order, which rows are locked first? Is it determined by ...
2
votes
1answer
997 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 ...

1
2 3 4 5
9