InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
61 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 ...
1
vote
1answer
132 views

How to recover from two queries stuck in kill state causing all the database to not respond? [closed]

During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple SELECT * FROM new_table LIMIT 0, 30 which ofcourse ...
0
votes
2answers
65 views

Add column on huge InnoDB table in MySQL 4.1

I need to add a column on a huge InnoDB table (200GB) which resides on MySQL 4.1.21 (SunOS). CPU has 16GB RAM and 200GB of more disk space. Is there any quick way of adding this rather than ALTER ...
4
votes
3answers
4k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
0
votes
2answers
72 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 ...
3
votes
3answers
2k views

Cannot write to mysql database while select query in process

Running Mysql 5.1.57 on InnoDB, and have a database locking issue. I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
0
votes
1answer
162 views

Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a ...
0
votes
1answer
73 views

Why is setting open_files_limit making MySQL slow?

We noticed that adding open_files_limit in /etc/my.cnf is making server slow. Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?
1
vote
1answer
82 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
31 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 ...
1
vote
0answers
79 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, ...
1
vote
1answer
92 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
0answers
63 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 ...
2
votes
1answer
4k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
2
votes
2answers
79 views

Is it suitable innodb_buffer_pool_size for MySQL Master-Slave

I am wondering how to decide the value of innodb_buffer_pool_size for my Master-Slave style database. I have a set of MySQL, 1 x master + 1 x slave. As I know that the innodb_buffer_pool_size is ...
0
votes
1answer
263 views

how to recover .ibd and .frm files in mysql

so i am developing java application with mysql 5.6.13, few days before i purchased new laptop, from previous laptop i forgot to create dump of application instead i copied folder of my database(.ibd ...
0
votes
0answers
20 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
84 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
56 views

How to calculate the space I need for the indexes?

CREATE TABLE info( home CHAR(10) CHARACTER SET latin1 NOT NULL, id TINYINT UNSIGNED NOT NULL, galaxy TINYINT UNSIGNED NOT NULL, galaxy_pos INT UNSIGNED NOT NULL, events TEXT NOT NULL, UNIQUE ...
0
votes
1answer
22 views

what is the best configuration for innodb_flush_log_at_trx_commit?

There are 3 different configuration is available for innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit = 0 innodb_flush_log_at_trx_commit = 1 innodb_flush_log_at_trx_commit = 2 Which ...
1
vote
1answer
110 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 ...
1
vote
1answer
164 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 ...
0
votes
0answers
23 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
183 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
33 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 ...
65
votes
8answers
44k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
1
vote
1answer
82 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
38 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
225 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 ...
2
votes
1answer
2k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
0
votes
0answers
54 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
148 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. ...
0
votes
2answers
114 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
134 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
1answer
59 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 ?
6
votes
2answers
5k views

Cannot open table mysql/innodb_index_stats

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
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
0answers
137 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
33 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
votes
2answers
77 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 ...
2
votes
1answer
7k views

Mysql slowly uses memory until it starts to use swap

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
1
vote
0answers
48 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
13
votes
3answers
4k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
1
vote
1answer
91 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
0
votes
3answers
359 views

How to repair, or drop/create a corrupted table in mysql?

Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP ...
1
vote
2answers
72 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
0
votes
1answer
79 views

Slow queries with many self joins in variable-column database

I'm creating a table structure which needs to handle a variable number of columns. To handle that case the table structure is a listing of cells with a column and row number. To view the table a ...
0
votes
1answer
113 views

Delete Query takes a long time

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. ...
0
votes
1answer
66 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...