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

learn more… | top users | synonyms

3
votes
1answer
1k views

mysql INNODB inserts very slow

The database's schema is as follows. CREATE TABLE `items` ( `id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT , `name` varchar( 45 ) NOT NULL , `main_type` tinyint( 4 ) NOT NULL ...
0
votes
2answers
1k views

Is it ever safe to set innodb_flush_log_at_trx_commit=0?

I have a server (Dell PE2950) with a battery-backed write cache on the raid controller. Is it safe for me to set innodb_flush_log_at_trx_commit=0 on a mysql server with data files stored on a raid0 ...
1
vote
1answer
81 views

What are some general tips to make InnoDB for MySQL perform at its highest?

I've been using MyISAM exclusively for several years now and know the ins-and-outs pretty well of how to optimize it, but I've just recently started using InnoDB for some of my tables and don't know ...
4
votes
2answers
2k views

MySQL InnoDB table shows a negative number of rows in phpMyAdmin

I just converted a MyISAM table to InnoDB with around 1.4 million rows. When I converted it to InnoDB, it now shows -1.4 million rows. The table still works as expected, but why does it shows ...
1
vote
2answers
1k views

IO issues with InnoDB, review of assessment required

TL;DR - Having some IO based issues with InnoDB. Before I sign the form to request more hardware can you have a look and see if you agree with my assessment Working for a client, I have inherited a ...
2
votes
2answers
2k views

mysqld crashes on any statement

I restarted my slave to change configuration settings to skip reverse hostname lookup on connecting and to enable the slow query log. I edited /etc/my.cnf making only these changes, then restarted ...
6
votes
2answers
13k 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, ...
0
votes
2answers
1k views

SHOW ENGINES; does not list InnoDB

Since yesterday, my InnoDB tables in my Mysql installation on Ubuntu don't work anymore, I tried to open them using Navicat, but I get the "Unknown Table engine 'InnoDB'" error. When I try listing ...
1
vote
1answer
2k views

How to use most of memory available on MySQL

I've got a MySQL server which has both InnoDB and MyISAM tables. InnoDB tablespace is quite small under 4 GB. MyISAM is big ~250 GB in total of which 50 GB is for indexes. Our server has 32 GB of RAM ...
0
votes
1answer
228 views

mySQL process overloading server - Need Help

I ran into an issue the last few days, I have a database server 2 Quad Core Processors and 24 gb of ram, recently we have come across a big issue the server is running normally at about 130% cpu then ...
2
votes
1answer
884 views

Facebook's Flashcache writeback block cache

Im quite interested in Facebook's code which was thrown over the wall again.. FlashCache ... Sounds fast, using SSD's to help scale InnoDB/MySQL, but it was designed as a generic caching module that ...
0
votes
1answer
1k views

MySQL innodb has lots of processes hanging in “update” state once every minute

Description of problem I have a fairly large MySQL installation. There are at least 3 separate servers which run the innoDB storage engine. Once every minute, at the same time each minute, for about ...
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 ...
0
votes
1answer
708 views

MySQL ibdata1 permissions error on cloned EC2 instance

I am trying to get a Rails app running on a cloned EC2 instance and am running into some permissions issues with my ibdata1 file. On the original instance, everything behaves as expected. However, ...
2
votes
2answers
206 views

How can I know if there are innodb tables in a mysql server?

I have a mysql server and I want to disable innodb support for it. It's said that I can cut-off my memory usage up to 100MB. I'm pretty sure but not 100% sure, that there is no innodb tables in the ...
0
votes
1answer
3k views

Fail to set innodb as default engine on Ubuntu 10.04

I've got a problem setting innodb as the default database engine in MySQL 5.1 on Ubuntu 10.04. As soon as I switch to innodb as default engine the server refuses to start. This is what I get in the ...
1
vote
2answers
340 views

Will deleting old MySQL data improve performance?

Would deleting old data from my tables improve MySQL query performance? When my queries use indexes? When my queries don't use indexes? When I use InnoDB? When I use MyISAM?
1
vote
3answers
5k views

How-to manage a MySQL table with over 2 million records

I have an InnoDB table running on MySQL 5.0.45 in CentOS. To make matters worse it is all running in a virtual machine. There are multiple tables that have the probability of exceeding 2 million ...
5
votes
1answer
1k views

MySQL and large pages/hugetlb performance gain in numbers?

MySQL has large page/hugetlb support for InnoDB. And there's plenty of posts (example) on the topic. But does anyone have examples of what performance changes they saw ? I have seen other database ...
9
votes
5answers
8k 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... ...
0
votes
2answers
1k views

Mysql start fails with Operating System error 13

I have XAMPP on my Ubuntu Lucid system and everything worked fine. But there seems to be some problem now and mysql wouldn't start. I had tried to recover a few Drupal databases and hence copied ...
4
votes
1answer
3k 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 ...
1
vote
1answer
1k views

Unknown table engine 'InnoDB problem

I installed a MySQL Server 5.1 on my machine (Windows Server 2008) and I chose MyISAM as the default DB storage engine. I created one empty DB on it just for testing. Later I reconfigured it using the ...
2
votes
1answer
1k views

MySQL's innodb_file_per_table and “too many tables”

I'm running MySQL (5.0 but I don't think it matters for anything after 4.1) and I've seen advice about using the innodb_file_per_table configuration option for InnoDB tables. This is usually done so ...
4
votes
2answers
866 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 ...
3
votes
1answer
382 views

high level design of sms transaction handling system

I have been tasked with designing a system do handle SMS transactions. Transactions come in form of HTTP requests from a service provider. In the process of handling transaction, system in question ...
0
votes
1answer
855 views

Mysql file backup

Hi I moved my sites from one windows 2003 to windows 2008 web server. I have some php forum, I copy the data directory of mysql from old server to new one. there are some databases that when I check ...
2
votes
3answers
423 views

InnoDB vs MyISAM

I'm creating a service like FriendFeed. For the database, what's the best to have the tables with InnoDB or MyISAM? There are a lot of selects and Inserts. The table must be serchable, sortable ...
2
votes
2answers
92 views

I already created a mysql database. However, for my future tables that I create, I want to default to INNODB

How can default to INNODB for every table to create in the future?
6
votes
2answers
7k 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 ...
1
vote
1answer
982 views

MySQL / InnoDB / Incorrect key file for table Error

I keep seeing this pop up in my logs every few hours. Trying to get property of non-object -- Incorrect key file for table '/tmp/#sql_ad2_2.MYI'; try to repair it I ran mysqlcheck on all tables, ...
0
votes
1answer
742 views

MySQL Server - Got error -1 from storage engine

I am currently trying to restore a MySQL table from the .ibd file. I have been following the instructions on the MySQL reference manual on how to use DISCARD and IMPORT TABLESPACE to replace the .idb ...
0
votes
4answers
9k views

Unable to enable InnoDB in mySQL on Ubuntu 10.04

I am trying to enable InnDB on my linux server. I have installed Ubuntu 10.04 JeOS on an ESX server. I then installed mySQL and tomcat using aptitude. However when I use SHOW ENGINES; in mySQL it ...
1
vote
1answer
208 views

What will happen if MySQL cannot write to InnoDB log?

What happens to MySQL if the partition that contains InnoDB's log files (innodb_log_group_home_dir) becomes full, read-only or otherwise unwritable? If this did happen, what recovery process would ...
0
votes
1answer
216 views

Column locking in innodb?

I know this sounds weird, but apparently one of my columns is locked. select * from table where type_id = 1 and updated_at < '2010-03-14' limit 1; select * from table where type_id = 3 and ...
0
votes
1answer
568 views

Mysql- “FLUSH TABLES WITH READ LOCK” started automatically

I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
1
vote
1answer
2k views

Deleting huge chunks of data from mysql innodb

I need to delete a huge chunk of my data in my production database, which runs about 100GB in size. If possible, i would like to minimize my downtime. My selection criteria for deleting is likely to ...
0
votes
1answer
84 views

Should I upgrade memory for my dedicated server?

My memory usage hangs around 25% (swap is generally 1%) on my dedicated server and load is around 2-5. My host recommended that I upgrade from 2GB of ram to 4GB so that I can increase my ...
0
votes
1answer
906 views

InnoDB table locks without apparent reason

I have an InnoDB table for visitors' counting, which has perfectly worked for several years, but it failed twice yesterday, may be cause of the increase of visitors Without apparent reason, this table ...
3
votes
1answer
2k views

Innodb : cannot allocate the memory for the buffer pool

My innodb keeps crashing. This is the error message below. Does anyone know why this keeps happening? InnoDB: by InnoDB 49201616 bytes. Operating system errno: 12 InnoDB: Check if you should increase ...
1
vote
3answers
982 views

Roughly how long should an “ALTER TABLE” take on an 1.3GB INNODB table?

I'm trying to optimize a table that hasn't been optimized in a long time. It has around 2.5 million rows with 1.3 GB of data and a 152 MB index. I started optimizing it about 15 minutes ago and I have ...
3
votes
1answer
2k 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 ...
5
votes
2answers
4k 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 ...
8
votes
5answers
30k 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 ...
1
vote
1answer
580 views

What is the fastest way to optimize a MySQL table?

I've ran many different DELETE queries against many tables in the huge database (hundreds of millions records). What would be faster - to run OPTIMIZE TABLE against each modified table. Or just to ...
3
votes
2answers
2k views

Tuning mysql's query cache

I'm looking into tuning the query cache on mysql on my VPS [a 360MB setup]. When I started, it was set at 32MB, and I was getting a non-trivial amount of qcache_lowmem_prunes. I added a cronjob to run ...
70
votes
1answer
84k 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 ...
2
votes
1answer
1k views

InnoDb Overhead?

I just converted several large tables from MyISAM to InnoDB. When I view the tables in phpMyAdmin, they are showing a significant amount of overhead (One table has 6.8GB). Optimizing the tables ...
5
votes
3answers
7k 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?
2
votes
1answer
151 views

How do I backup a database to a .sql statement?

How do I backup a database to a single SQL file? After it's backed up, how can I load it/restore the database with InnoDB? It must be InnoDB.