InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
228 views

some InnoDB settings in My.cnf are messing up everything, why?

When I edit my.cnf in MySQL 5.0 in filemanager and add the following settings, I get logged out automatically from Kloxo and when I remove these line and restart server everything is ok then: ...
7
votes
3answers
13k views

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
2
votes
1answer
262 views

Changing to InnoDB increases iostat

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat. But how can it increase ...
6
votes
1answer
3k views

How large will a MySQL database be relative to the dump file?

I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
1
vote
1answer
634 views

Restore the data from Snapshot Backup when the data spans over multiple directories?

Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF. We have done some testing work on one of the slave say server A.and it takes a huge amount of ...
3
votes
1answer
793 views

MySQL - Installing InnoDB Plugin

I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61. I want to upgrade the master server to a stronger machine with the following: 2 processors - 6 cores each 48GB of RAM To ...
1
vote
1answer
354 views

Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2. I was wondering if my PK ...
2
votes
3answers
512 views

MySQL - Master/Slave Replication with different server specs

I have a Master/Slave replication (using MySql 5.1 / InnoDB). Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) ...
3
votes
1answer
621 views

why is mysql still writing data two hours after all transactions have stopped

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data). Lately, I noticed something peculiar. After I ...
0
votes
1answer
101 views

InnoDB possible error

We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB ...
15
votes
3answers
4k 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 ...
1
vote
2answers
2k views

InnoDB missing from MySQL

I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL server. I recently upgraded it from the dotdeb repository, then installed mysql-server. There is no ...
0
votes
1answer
254 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
3
votes
2answers
1k views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
10
votes
2answers
16k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
2
votes
1answer
1k views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
1
vote
1answer
316 views

MySQL lost records in a master slave scheme

We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter ...
4
votes
1answer
4k views

What is the use of ibdata1 file in mysql?

I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions. ...
1
vote
2answers
3k views

Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery

We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running. As a ...
31
votes
4answers
58k 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?
6
votes
2answers
2k 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 ...
5
votes
1answer
1k views

Which mysql storage engine to choose?

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
2
votes
2answers
176 views

Is this Dedicated Server good enough to Run MySQL on Linux Ubuntu?

I have a web application that runs simple logic. Most of the SQL procedures that I have only do simple selects, some simple join table and some simple inserts. I want to get a dedicated server for My ...
3
votes
3answers
6k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
5
votes
2answers
4k views

Frequently slow queries while in the 'Update' state

I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ ...
4
votes
1answer
4k views

MySQL Innodb corruption

I recently inherited a Drupal site that has an Innodb corruption. MySQL will run for sometime--usually about 24-hours but will eventually need to be manually restarted(see log output below). I have ...
2
votes
1answer
890 views

innodb_flush settings on Amazon RDS

Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent ...
7
votes
2answers
1k 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 ...
1
vote
1answer
2k views

Why do my ON UPDATE/ON DELETE rules disappear?

I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working? I am ...
1
vote
1answer
737 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
2
votes
2answers
3k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
10
votes
3answers
62k views

MySQL: how to restore table stored in a .frm and a .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
1
vote
2answers
17k views

MySql - Clean ibdata1

I have a database that its entire size is ~44GB, out of it ibdata1 is ~35GB that doesnt make sense, the size of the data shouldnt be more then 10GB. i use the following query to get an esstimation of ...
4
votes
4answers
4k views

Prevent reset of auto_increment id in Innodb database after server restart

I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused. Normally, this isn't a ...
4
votes
2answers
3k views

What happens when InnoDB hits its tablespace autoextend max?

The latest version of High Performance MySQL (3rd ed.) recommends that one: ...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't ...
12
votes
1answer
19k 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 ...
4
votes
5answers
13k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
7
votes
2answers
23k 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. ...
2
votes
2answers
2k views

How to sync two MySQL high load tables with 10M rows?

I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and ...
1
vote
1answer
212 views

Unit testing with InnoDB transactions

So, I'm running some unit tests against some InnoDB tables in MySQL. Each unit test is wrapped in a transaction: setUp: SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...
2
votes
2answers
1k views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Title says it all. However i have to enter at least 30 characters.
9
votes
5answers
11k 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; ...
2
votes
3answers
1k views

InnoDB UPDATE slow need a better option

I'm using MySQL 5.5 with a table in InnoDB running: deleteme tinyint NOT NULL DEFAULT 0;` UPDATE `import` SET `deleteme` = 1; -- Set the delete field LOAD DATA LOW_PRIORITY LOCAL INFILE ...
2
votes
3answers
3k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
1answer
875 views

How does innodb_stats_on_metadata relate to query execution plan stability?

The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example ...
1
vote
4answers
4k views

Should I use the Infobright database or is there a better aggregation solution?

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log. We need to be able to aggregate counts of this data (cube) vis a ...
4
votes
2answers
391 views

Foreign key constraints with MySQL/InnoDB

I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL. I assume that they effect ...
11
votes
3answers
11k views

Is innodb_file_per_table advisable?

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave ...
11
votes
4answers
11k 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 ...
6
votes
3answers
18k views

InnoDB row locking - how to implement

I've been looking around now, reading the mysql site and I still can't see exactly how it works. I want to select and row lock the result for writing, write the change and release the lock. ...