InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
66 views

temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

I executed some alter statements on my master server, which have been successfully executed on master (now there is no temp table on master) and all alter also have been replicated on slave server ...
0
votes
1answer
150 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 ...
1
vote
1answer
59 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
0
votes
0answers
42 views

MySQL — COMMIT in 'stage/sql/init' state

MySQL 5.6 instance hosts a single database (for the TeamCity CI app). The app is quite write-heavy (about 2/3 of total database activity) and often seen waiting for database to complete a commit, ...
1
vote
1answer
1k views

Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS

I'm getting the following warning in MySQL: Aborted connection (Got an error reading communication packets) I'm using Amazon RDS for MySQL hosting. Both MyISAM and InnoDB storage engines are ...
-1
votes
1answer
70 views

Mysql Queries cause high load in server

I have problem in the database ,here is phpmyadmin status queries --------------------+ ...
0
votes
0answers
21 views

How are performances with InnoDB auto-extending data file

Our innoDB tablespace is configured like this: innodb_data_file_path = ibdata00:40G;ibdata01:40G;ibdata02:100M:autoextend If I take a look to the directory: [root@xxx innodb]$ du -hs ...
1
vote
2answers
57 views

MySQL TEXT Disk Space

I have a plain text file with 3 million articles divided with \n. The file is 58G When I'm adding the articles to an InnoDB table one per row INT id TEXT article_content the .ibd file of this ...
0
votes
1answer
153 views

forcing innodb recovery of a corrupted database

A poorly designed script selected records from two existing tables and added records to a newly created table for 24 hours until I killed it by terminating the MySQL 5.6 command line client and ...
0
votes
0answers
119 views

Why is InnoDB waiting for table metadata lock in simple INSERT/DELETE + SELECT?

I've started using a new MySQL server and moved an InnoDB table there. I'm seeing a weird behavior that when I have a long running INSERT / DELETE, any SELECT from the same table is locked which is ...
2
votes
1answer
43 views

Peer review database design for various reports

I'm currently trying to redesign a poorly thought out database for a peer review system that we use internally at my workplace. Employees can log on and submit a review about another employee who ...
0
votes
1answer
294 views

ibdata1 corrupted, recover from ib_logfile?

A failing hard drive have left me with a corrupted ibdata1 file. I know where the file starts but not where it ends. The hard drive recovery software claims the file is larger than the hard drive ...
0
votes
2answers
629 views

ERROR - Function 'innodb' already exists

I'm looking to start converting some MyISAM tables to INNODB. Running Ubuntu 14.04 Server with MySQL 5.6 ( apt-get upgraded from 5.5 ), 32G ram on 4 core/ 8 thread cpu. Initially had trouble with... ...
2
votes
1answer
108 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
1
vote
1answer
201 views

InnoDB Buffer Pool Hit Rate

As given at http://cherry.world.edoors.com/COBFKUqnUdBY one can obtain buffer hit ratio by following sql query: SELECT round ((P2.variable_value / P1.variable_value),4), P2.variable_value, ...
0
votes
1answer
103 views

How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session. How do I make this setting stick after stopping and restarting MySQL? NOTE: I am on version 5.5.19 of MySQL ...
0
votes
0answers
20 views

How can I determine the performance (rows/second) for a certain query on one of my tables?

I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records. The table has around 10 million rows. ...
1
vote
1answer
87 views

LOAD DATA stuck at Null State MySQL

I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of ...
0
votes
1answer
110 views

Need help to understand how information_schema.TABLES get updated

I created large innodb table mysql> select count(*) from big_data; +----------+ | count(*) | +----------+ | 23822336 | +----------+ checked the stats in informaion_schema.TABLES mysql> ...
1
vote
0answers
132 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
0
votes
2answers
92 views

Buffer pool above 90% utilized causes MySQL to crash

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner? EDIT I'm using Rolando's script to identify how full the innodb_buffer_pool is. SELECT ...
1
vote
1answer
142 views

Why does IN (subquery) perform bad when = (subquery) is blazing fast?

Have a look at these two queries: Simple: mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput = (SELECT DISTINCT lastoutput FROM omgenvelope WHERE ...
1
vote
1answer
117 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
1
vote
1answer
32 views

InnoDB buffer pool active pages

I'd like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW ...
2
votes
0answers
137 views

Recover innodb database from ibdata1 and *.frm files only

I have a problem on my server. I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started. I checked all mysql data, ...
0
votes
0answers
91 views

importing mysql data folder from an old instalation

i want to restore one database from my old mysql folder, i copied mysql/data folder from old installation into my current mysql folder, yet its not starting up any more. when i check the error_log ...
1
vote
1answer
117 views

Optimizing a large number of INSERT … SELECT statements

I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT statement from the other to get the corresponding foreign ...
0
votes
1answer
29 views

Consistent backup of large DB --single-transaction

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication. Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the ...
0
votes
0answers
24 views

InnoDB: detected cycle in LRU for buffer pool 0, skipping to next buffer pool

While looking through the error log for something else I noticed a couple instances of message posted as the subject. What is this about? Googling for the "error" doesn't bring much up. This is on ...
1
vote
1answer
130 views

InnoDB engine is not loading after tuning parameters

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below ...
1
vote
1answer
229 views

Deleting & Reclaiming space from InnoDB table

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete ...
0
votes
1answer
89 views

Recovering from removal of ibdata1 [duplicate]

I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the ...
0
votes
0answers
24 views

Database cache problem

I have server based on Linux 64 Bit centos 6.5 and Mysql innoDB engine I created database for php script require database installation , after i successfully finished installation for first time i ...
1
vote
2answers
390 views

Two MySQL Server accessing same database over NFS

I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following: ...
0
votes
0answers
39 views

MySQL best practice DB recovery

I am trying to figure out what is the best practice is for Mysql recovery after a power failure. I need to find a way that if a server (which is running on a virtualbox VM) goes down when it goes ...
1
vote
1answer
113 views

InnoDB primary key efficiency

I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused. Given a simple many-to-many table with two fields, storeId and zoneId, ...
1
vote
1answer
43 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
2answers
47 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?
1
vote
1answer
204 views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
3answers
108 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
1
vote
1answer
469 views

How to decide whether to enable MySQL query cache using InnoDB

How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of: SHOW ...
0
votes
0answers
77 views

MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

We have a 3 node MariaDB Galera cluster. All nodes are identical and the config on each is identical. They have 64GB of RAM each. The output of the following query is 28: SELECT ...
1
vote
2answers
263 views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
-1
votes
1answer
71 views

How does InnoDB store tables in ibdata file? [closed]

There are two tables and their default engine is InnoDB. I want know the following What's inside the ibdata file ? How does InnoDB store these two tables ?
0
votes
0answers
23 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
0
votes
1answer
130 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
0
votes
0answers
210 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
1
vote
0answers
38 views

Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

First of all, excuse my English. I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by ...
1
vote
1answer
147 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
-1
votes
2answers
80 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...