InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

4
votes
2answers
1k views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...
4
votes
2answers
7k views

MySQL performance impact of increasing innodb_buffer_pool_size

I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB. My queries are responding well now i.e under mili seconds. ...
4
votes
2answers
668 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
4
votes
1answer
7k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
4
votes
4answers
17k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
3
votes
2answers
1k views

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb Moving the undo log files outside seemed logical but this procedure seems ...
3
votes
2answers
342 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
3
votes
2answers
9k views

What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?

2M rows inserted using LOAD DATA INFILE to innodb takes 7.5min, and profiling shows 99% of that time is in "System lock". Does this tell me anything useful? mysql> set profiling=1; ...
3
votes
1answer
3k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
3
votes
2answers
2k 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 ...
3
votes
1answer
6k views

When are InnoDB table index statistics updated?

In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated? What events trigger such updates? I've seen comments suggesting that the following might trigger it: table is ...
2
votes
2answers
6k views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
2
votes
1answer
368 views

Why is there no data loss with mysql innodb_flush_log_at_trx_commit set to 0?

Here is the MySQL info: mysql> show variables like 'innodb%flush%log%'; +--------------------------------+-------+ | Variable_name | Value | ...
2
votes
1answer
5k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
1
vote
3answers
888 views

MySQL High Availability for InnoDB

I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine. I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over ...
1
vote
1answer
541 views

Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
0
votes
1answer
497 views

innodb completely crashed after interruption

I am having a major crash on mysql affecting a lot of websites. We had a crash on our SAN storage. Since that time, mysql refuse to start. I tried to reinstall mysql, but still no luck. I think ...
5
votes
1answer
5k views

Slow InnoDB INSERT/UPDATE/DELETE

Background A couple of weeks ago all of my INSERT's/UPDATE's/DELETE's became overall slow (not SELECT's). Before that, everything was normal (mysql statements executed in <0,01 seconds). Now a ...
5
votes
3answers
4k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
4
votes
1answer
5k views

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system. Even though the guilty records have been removed or corrected, scanning ...
4
votes
1answer
11k views

MySQL: Row size too large (> 8126)

I have a table which has 300 columns in it. Each column is up to 255 bytes (there IS a business justification for this). When I try it create it using VARCHAR(255), I go past the limit for the ...
4
votes
2answers
2k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
4
votes
2answers
8k views

Internal reason for killing process taking up long time in mysql

I copied a big table's structure with (it is an InnoDB table btw) CREATE TABLE tempTbl LIKE realTbl Then I changed an index, and filled it up so I could run some test. Filling it was done using: ...
3
votes
2answers
641 views

Copying datadir directory enough for backing up a database with InnoDB tables?

I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer. I used to copy database with only MyISAM ...
3
votes
2answers
1k views

Database space doesn't match ibdata1 size

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table. In the MySql workbench, when I query for the databases used space, with this query SELECT ...
3
votes
2answers
2k views

MySql replication with Raw Data and InnoDB tables

I want to start replication on DB that has many InnoDB tables and MyISAM tables. I did everything according to manual Creating a Data Snapshot Using Raw Data Files. Firstly, I tried copied all ...
2
votes
3answers
144 views

Drop Tables with Bigger size

We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that ...
2
votes
1answer
1k views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
2
votes
1answer
484 views

Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course. What are the advantages and disadvantages of either ...
2
votes
1answer
734 views

TokuDB database size unknown in phpmyadmin

In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show unknown in phpmyadmin.
2
votes
3answers
1k views

How long can Deadlock info hold in innodb status page?

When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp ------------------------ LATEST DETECTED DEADLOCK ------------------------ 121118 17:14:31 Questions Any idea how long Deadlock ...
2
votes
1answer
2k 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 ...
2
votes
2answers
2k 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.
1
vote
1answer
45 views

How can I 'further' improve my table and queries

This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query. I also changed my application so that unused filters weren't ...
1
vote
1answer
93 views

InnoDB issue after power outage a few weeks ago - no backup

A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have ...
1
vote
1answer
561 views

Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
1
vote
0answers
176 views

invalidating query cache entries(key) [duplicate]

when I tried to delete some records from a MYSQL server (version 5.0.95-log) and checked the process list I got the result as follows mysql> show processlist \G *************************** 2. ...
1
vote
1answer
2k views

How to know the current log sequence number in 5.0.X

I would like to test the optimal value for iblog files on my mysql which is 5.0.77 I found the below procedure in mysqlperformance blog: ...
1
vote
1answer
956 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 ...
0
votes
2answers
71 views

Storing multiple types of users and their respective credentials in separate or same table?

So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design. In ...
0
votes
0answers
74 views

hot/live sql backup

please excuse my ignorant question as I am still fairly new to database things so I have a mysql database and a part of it has file with average size of 10M I store them in their own directory and ...
0
votes
1answer
659 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
0
votes
0answers
691 views

InnoDB errors after having deleted “ibdata1”

By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly. After reading so many forums, I found a post that recommended deleting the 3 files ...
0
votes
1answer
4k views

MySQL database drop insanely slow

I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server. I installed it under my user home directory like I'm used to doing. But this time the queries are extremely slow to ...
0
votes
2answers
1k views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
0
votes
2answers
1k views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
14
votes
2answers
4k 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 ...
16
votes
3answers
9k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
8
votes
3answers
16k views

Making sense of INNODB buffer pool stats

After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. ...
12
votes
5answers
14k 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; ...