InnoDB is the main ACID-compliant Storage Engine used in MySQL.

learn more… | top users | synonyms

39
votes
1answer
38k views

InnoDB: Error: log file ./ib_logfile0 is of different size

I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine. innodb_buffer_pool_size = 2560M innodb_log_file_size = 256M innodb_log_buffer_size = 8M ...
15
votes
6answers
8k views

MySQL InnoDB - innodb_file_per_table cons?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table. I am ...
15
votes
4answers
5k views

what is the difference between INNODB and MYISAM

I am using MYSQL as my database for the current web project. And i am a newbie to MYSQL. Could any one explain me please what is the difference between INNODB and MYISAM. I hope the answers will help ...
15
votes
5answers
3k views

How do you choose a MySQL database engine

In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys). Does it always come down to trying both and measuring? Or ...
11
votes
5answers
21k views

Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it: ╰$ sudo service mysql restart stop: Unknown ...
9
votes
2answers
10k views

Best Mysql cache settings for 8gb RAM dedicated mysql server using only Innodb (5gb database)

I'm a pretty big noob when it comes to setting up mysql for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of mysql, but I do know that ...
9
votes
5answers
333 views

MyISAM for data reads

I have a table with around 1 Billion rows, and its 98% read intensive. I tried tuning the database, with different storage engines (MyISAM and InnoDB) Then ran a few test to view the performance In ...
8
votes
2answers
1k views

What should I know before going live with an InnoDB database?

What things should a server- or DB-admin know and do before making a InnoDB database available. I'm thinking about things like setting correct values for innodb_buffer_pool_size and ...
8
votes
2answers
6k views

MySQL InnoDB database 'hangs' on selects

I'm trying to fix MySQL configuration on our server. Our app's specifics is that a lot of data is stored in single table (currently over 300 millions of rows). This table is used often for inserts ...
8
votes
4answers
2k views

How to efficiently dump a huge MySQL innodb database?

I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is ...
8
votes
3answers
2k views

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This ...
7
votes
2answers
13k views

How big is too big for ibdata file?

My ibdata file is very large, at least it seems to me to be very large. Is this excessive or not that bad? -rw-rw---- 1 mysql mysql 15G Apr 18 10:11 ibdata1
7
votes
4answers
3k views

Modifying columns of very large mysql tables with little or no downtime

I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... ...
6
votes
5answers
15k views

How to improve MySQL INSERT and UPDATE performance?

This question can probably be asked on StackOverflow as well, but I'll try here first... Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance ...
6
votes
2answers
5k views

How do MyISAM and InnoDB Utilize HD Space?

My MySQL server is running out of HD space fast. Most of my larger tables use the InnoDB engine (for no mission-critical reason). In an effort to avoid the dreaded 'drop database to recover innodb ...
6
votes
5answers
6k views

How to fine-tune our MySQL server?

MySQL is not my thing, yet I need to fine-tune one of our servers. Here are the requirements/specs: The MySQL server has only one significant database We only have one "type" of application ...
6
votes
2answers
2k views

Good/Better config for MySQL on an EC2 Large Instance

I have an EC2 Large instance dedicated to MySQL. It will be serving a Joomla/Magento combo so it has a blend of InnoDB and MyISAM tables. I have only worked with MyISAM in the past and am therefore ...
6
votes
1answer
6k views

MySQL: InnoDB: ERROR: the age of the last checkpoint is X, which exceeds the log group capacity Y?

In a production MySQL environment the following error message is written to /var/log/mysql/error.log every fourth minute: 110723 18:36:02 InnoDB: ERROR: the age of the last checkpoint is 9433856, ...
6
votes
1answer
2k views

how to disable keys in mysql innodb

how to disable keys in mysql innodb? or is this not possible? why?
5
votes
5answers
5k views

phpMyAdmin showing database is MyISAM but tables are InnoDB

Is it possible for a database to be of one type (MyISAM) and the tables to be of another type (InnoDB) or is this a bug in phpMyAdmin? It is showing exactly that. I tried to verify the database ...
5
votes
2answers
2k views

Way to avoid server downtime when creating Master - Slave relationship?

I am preparing for setting up a MySQL master-slave or master-master relationship. Right now I have a single MySQL production server, and of course I don't want a lot of downtime while I connect the ...
5
votes
1answer
442 views

Mysql disaster recovery

We just got a major disaster: somebody made an uncontrolled update on the production database, and obviously, the backup process is not working since a long time, so we got a major data loss. A 40 ...
5
votes
1answer
1k views

16 Cores 12 GB RAM Dedicated MySql Configuration

I have a dedicated 16 Cores 12 GB RAM Mysql server which is performing quite badly since I configure it for a 16 GB server and I guess that explains why in the world the site now takes more than 500 ...
5
votes
1answer
458 views

MySQL transaction issue with Django and Amazon RDS

I'm having a problem with a MySQL 5.5 database instance running on Amazon RDS. Periodically (once every couple of days) a transaction will fail to complete and appears to leave the tables locked. ...
4
votes
6answers
5k views

Is there any equivalent of mysqlhotcopy for InnoDB?

Currently I'm using mysqldump to make backups which is slow, but OKish. The big problem is restoring database, which takes a few days. The dump is around 7GB gzipped, so it's not a tiny database, but ...
4
votes
2answers
3k views

Simpler way to convert all tables from InnoDB to MyISAM

Previously, I use this: USE dbname; ALTER TABLE tablename ENGINE=MYISAM; I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
4
votes
3answers
5k views

How do I set the default table type as innodb in my.cnf?

For mysql...is there a way to set it so that every table that gets created is innodb?
4
votes
2answers
1k views

INNODB mysql. Plugin disabled

When I startup mysql on my unbuntu server I will get a message. 121122 17:39:37 [Note] Plugin 'FEDERATED' is disabled. 121122 17:39:37 InnoDB: The InnoDB memory heap is disabled 121122 17:39:37 ...
4
votes
2answers
762 views

MySQL 5.5.9 Query Cache not working when schemas have hyphens (“-”) in their name

I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU. I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the ...
4
votes
1answer
981 views

Changing MySQL tmpdir kills InnoDB functionality

I've got a query that keeps failing because I don't have enough temporary space available. So, I created a directory on a different partition at /data/tmp, chmoded this directory to 777, and updated ...
4
votes
1answer
79 views

How to drop an 8 GB InnoDB table smoothly without global locks

MySQL version: 5.1.63. I am about to DROP a bigger ~8 GB InnoDB table. Last week I dropped a four times bigger table on another machine and a global lock kicked in that took us down for ~90 seconds. ...
4
votes
2answers
704 views

Disk I/O utilization up to 100% after tuning InnoDB-related MySQL configuration

I have added following lines to "my.conf": query_cache_size=128M innodb_buffer_pool_size=512M innodb_flush_method=O_DIRECT After that Disk utilization started raising, reached 100% and holds ...
4
votes
2answers
596 views

Guaranteeing ACID properties for InnoDB databases

What steps must one take to ensure that an otherwise defaultly-configured InnoDB server is truly ACID compliant? The InnoDB configuration page mentions that the hardware itself must be configured to ...
4
votes
1answer
2k views

MySQL data recovery (innoDB) - missing .frm files but intact ibdata

A recent server fiasco left me with a by-all-appearances intact ibdata file but only half of my .frm-containing directories. I've copied all of this to a fresh mysql install, and have successfully ...
4
votes
2answers
3k views

MySQL InnoDB Corruption after power outage, possible to recover?

I recently started trying to get Redmine up and running after a power outage that seems to have corrupted our InnoDB database in MySQL. Redmine had an extensive set of documentation that I would like ...
4
votes
1answer
274 views

Mysql InnoDB table size performance

I have a table that is closely approaching 2 million records. This table stores a history of transactions. This is on a high traffic website but also the table is not accessed regularly. We ...
4
votes
2answers
2k views

What is the difference between MySQL Community Edition and Classic Edition?

With the news of Oracle pulling the InnoDB engine from MySQL Classic Edition, you now need to use MySQL Community Edition to get InnoDB for free. Oracle doesn't really make it clear what is different ...
3
votes
2answers
351 views

What is the best way to check the status of a MySQL slave that is replicating from a master?

Specifically I have a set of InnoDB tables and we are backing these up into MyISAM tables to provide full text search. The slave DB routinely falls behind the Master. I am looking for advice on ...
3
votes
2answers
884 views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
3
votes
2answers
3k views

Converting big table from MyISAM to Innodb

I have a table with about 300M rows in MyISAM format I want to convert to Innodb My original goal was to reduce usage by changing the table schema to have simpler indexes. I dumped all the table, ...
3
votes
1answer
556 views

Transferring InnoDB database via rsync

We are in the process of switching to a new RAID array and need to copy our MySQL InnoDB database to the new RAID array while minimizing downtime (50GB database). I understand that using rsync on a ...
3
votes
2answers
7k views

Why do MySQL queries pile up in “Sending data” state?

We're using InnoDB tables as the back end of a web application, and everything was fine for about two years until we had to restart MySQL a few weeks ago. (We hadn't disabled reverse DNS lookups, ...
3
votes
2answers
1k views

Reclaim disk space after deleting Magento MySQL database

I needed to reclaim some disk space so I removed an old Magento database from my server. The database size was around 16 GB. I was surprised that once the database was deleted, it did not really free ...
3
votes
2answers
1k views

Migrating from MyISAM to XtraDB

Just a few questions that I just can't find anywhere about migrating to XtraDB. My group has been using MyISAM dbs for production and was wondering how hard is it to migrate to Percona's XtraDB and ...
3
votes
1answer
184 views

How much disk space does innodb need to ALTER TABLE

I have a large InnoDB table that I want to alter by adding a small column to. I'm prepared to wait a while, but I'm wondering how much disk space will the ALTER TABLE command consume while it's ...
3
votes
1answer
1k views

What are the pros & cons of these MySQL engines for OLTP — XtraDB, PBXT, or TokuDB?

I'm working on a social website with an approximate read/write split of 90/10. Trying to decide on a MySQL engine. The ones I'm interested in are: XtraDB PBXT TokuDB What are the pros and cons of ...
3
votes
2answers
440 views

MySql Data Loss - post mortem analysis - RackSpace Cloud Server

After a recent 'emergency migration' of a RS cloud server, the mysql databases on our server snapshot image proved to be days out of date from the backup date. And yet files that were uploaded ...
3
votes
2answers
1k views

Picking the right innodb_buffer_pool_size

I have a 24GB machine running MySQL (only InnoDB tables). My innodb data set is 11.5GB big, and i have set my innodb_buffer_pool_size to 14GB to allow some growth. So the entire innodb data set is ...
3
votes
2answers
140 views

Dropping Index on 10GB+ InnoDB table takes over 4 hours

This is the table I'm working with: CREATE TABLE IF NOT EXISTS `checklist_answer` ( `id` varchar(36) NOT NULL, `created_by` varchar(36) NOT NULL, `date_created` datetime NOT NULL, ...
3
votes
1answer
2k views

mysql - can't drop table does not exist, can't create table exists

After a server crash we are having some very strange issues with one particular table reference. Opting for a restoration from backup the database was dropped and a backup SQL dump loaded, only this ...