InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

65
votes
8answers
39k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
56
votes
6answers
40k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
32
votes
4answers
39k views

How to safely change MySQL innodb variable 'innodb_log_file_size'?

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB: ...
29
votes
3answers
31k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
19
votes
3answers
21k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
18
votes
5answers
22k views

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from ...
17
votes
3answers
30k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
16
votes
4answers
935 views

How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
14
votes
3answers
16k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
12
votes
2answers
3k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
12
votes
4answers
12k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
12
votes
3answers
3k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
12
votes
5answers
11k views

Why do MySQL threads often show “freeing items” status when the query cache is disabled?

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state. The MySQL manual suggests that at least part of the reason for a thread to be in ...
11
votes
3answers
17k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
11
votes
1answer
12k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
11
votes
1answer
15k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
10
votes
5answers
14k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
10
votes
1answer
12k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increases in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
10
votes
3answers
4k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
10
votes
3answers
678 views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
9
votes
3answers
11k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
9
votes
4answers
6k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
9
votes
1answer
6k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
9
votes
1answer
10k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
9
votes
2answers
2k views

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
8
votes
3answers
2k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
8
votes
2answers
677 views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
8
votes
1answer
4k views

Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
8
votes
1answer
3k views

Is it possible to change ENUM() lists?

I wasn't sure if changing ENUM() list isn't possible so I made a test. In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no'). Then I executed... ...
7
votes
4answers
7k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
7
votes
2answers
4k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
7
votes
2answers
2k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
7
votes
3answers
8k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
7
votes
3answers
11k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
7
votes
2answers
10k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
7
votes
2answers
749 views

Investigate peak in MySQL throughput

Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
7
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
6
votes
2answers
1k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
6
votes
3answers
390 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
6
votes
4answers
5k views

How to improve InnoDB DELETE performance?

So I have this audit table (tracks actions on any table in my database): CREATE TABLE `track_table` ( `id` int(16) unsigned NOT NULL, `userID` smallint(16) unsigned NOT NULL, `tableName` ...
6
votes
3answers
7k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
6
votes
2answers
4k views

Ibdata usage and Recommendations?

I have a Production server On November 5,2011 the ibdata size was 100G. In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G. I ...
6
votes
4answers
1k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
6
votes
2answers
1k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
6
votes
2answers
11k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
6
votes
2answers
2k views

What's a good way to add a PRIMARY KEY to a large InnoDB table?

I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows. I want to add a primary ...
6
votes
2answers
13k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
6
votes
1answer
3k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
6
votes
2answers
2k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
6
votes
2answers
3k views

Trouble deciphering a deadlock in an innodb status log

We are accessing MySQL from the Microsoft ADO.NET connector. Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...