InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
2answers
307 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
1answer
45 views

Error while importing sql - Specified key was too long; max key length is 1000 bytes

I am trying to import an sql file which shows me the following error: Error SQL query: -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE ...
1
vote
1answer
54 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
0
votes
0answers
36 views

Recovering Database from server

my server has some issues and isn't able to start the mysql service. I still have access to the server via shell. So I can get all files I want to have. So I found this guide ...
2
votes
1answer
60 views

row locking within ACID transaction innodb

I have a financial transaction that looks something like this (pseudo): begin select record for update update record(same record as selected) insert another record into another table ...
1
vote
1answer
21 views

mysql multiple database prioritize innodb buffer pool size

With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?
0
votes
0answers
22 views

row locking with innodb [duplicate]

What is the most efficient way to lock a specific row for within a mysql transaction. I am currently using for update using select is there another method that im unaware of? if not are mysql locks ...
2
votes
1answer
12k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
1
vote
1answer
414 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
0
votes
0answers
20 views

Temporary table created on disk, with 14mb of database

i have only one database (on one machine) with size 14 mb for temporary table i have this configration max_heap_table_size= 1000M tmp_table_size= 1000M sort_buffer_size=4M join_buffer_size=4M i ...
1
vote
1answer
54 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
0answers
41 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
3
votes
4answers
155 views

innodb DEPENDENT SUBQUERY very slow

Here is some innodb table. It insert,update,query very frequently. tn is product id col is product sub id tindex is product description.(store by each word) date is product add date stored by ...
2
votes
1answer
884 views

How I prevent deadlock occurrence in my application?

I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database. All the tables in the database have innodb engine. I also created indexes on each tables. Now I am ...
1
vote
2answers
46 views

How to interpret the information spit out by innochecksum?

MySQL is using up a lot of the disk space. Running du -sh on the data directory shows a humongous ibdata1 file (around 1.7 terabytes). I found a very helpful article online and ran a modified version ...
1
vote
1answer
42 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
1
vote
1answer
139 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
0
votes
1answer
56 views

I have delete database from mysql but storage is not freed

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage? I have a mysql database that the total ...
5
votes
1answer
280 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
105 views

Copying MySQL tables, InnoDB tables not recognized

I have to set up a database copy on my local computer. The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI). I created the database by creating a folder in ...
0
votes
0answers
62 views

MySQL Innodb Concurrent Inserts and Selects - can't use multiple threads on FreeBSD

We have an application that needs to be dumping rows into some tables at a much faster rate than it currently is. It is a dual processor xeon DB server with 48gb of ram and a 4 TB disk RAID 10 (for a ...
0
votes
1answer
52 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 ...
2
votes
1answer
122 views

How big can innodb_doublewrite_file grow?

When I use the enable innodb_doublewrite_file ... How big can this file grow ? How can I calculate the optimal disk size, if it would be used only for this file?
0
votes
0answers
36 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
1answer
49 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
2
votes
0answers
33 views

Count how many locks required for a MySQL (InnoDB) query

I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to ...
0
votes
1answer
39 views

Native replication from Percona XtraDB Cluster to a standard stand-alone slave

I'm replicating from one node of a three-member Percona XtraDB cluster to a separate slave, using regular MySQL replication. The node is set to write binlog in ROW mode, with log_slave_updates=true ...
0
votes
2answers
247 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
2
votes
1answer
79 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
0
votes
0answers
31 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
0
votes
1answer
72 views

Recover an Innodb mysql database from an EBS on Ec2

My instance crashed on AWs, and i had to create a new one and mount my Vol that contain my web application and the Mysql Database. After i stoped the Mysql Server i mounted the vol and also mounted ...
1
vote
2answers
86 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
2
votes
2answers
68 views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
0
votes
0answers
52 views

Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6

I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely ...
3
votes
1answer
29 views

Why does TokuDB say to remove innodb_buffer_pool_%?

The TokuDB quick start guide says "After converting your tables to TokuDB, you should comment out the global options related to memory caches (ie: innodb_buffer_pool%)." If TokuDB supports both ...
0
votes
0answers
84 views

Migrating from Wamp to Xampp - table doesn't exists

I have a directory with files where MySQL database was stored when there was used Wamp. Now I would like to use this data to read it using Xampp. I tried to set datadir in 'my.ini' file to that ...
0
votes
1answer
131 views

Why are InnoDB's index keys limited to 767 bytes?

MySQL's official documentation states about InnoDB: An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. The meaning of this ...
0
votes
0answers
32 views

.myd and .myi versus .frm and .ibd

I'm new to MySQL.I have trouble finding the .frm files and .ibd files in Windows 8 Explorer.They seem hidden and I have to find them using 'Find' and 'Open File Location', not sure how to make these ...
0
votes
1answer
765 views

Recover MySQL database from data folder without ibdata1 from ibd files

My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) ...
0
votes
2answers
780 views

Unable to change engine to MyISAM in MySQL

I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine. I have checked following engines are supported in MySQL: show engines\G ...
1
vote
0answers
12 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
0
votes
1answer
31 views

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped?

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? Also, what happens when a new primary key is added?
3
votes
1answer
245 views

Speeding up a NOT IN subquery

Good evening, I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery ...
0
votes
2answers
55 views

Mysql Windows Issues

I've been having allot of issues with my mysql databases on Windows dying on me. About once a week or so it will refuse to work and I'll spend all night poking at it & a few computer restarts ...
5
votes
1answer
360 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 ...
3
votes
1answer
161 views

Mysql database slow responding to connects when having only few concurrent connections

We have a database setup with in master/slave. It runs innodb and its on a 8 core with 32gb. When i run "ab" to do a load test on a website (4 frontend webservers) which does the query's with 10 ...
1
vote
1answer
85 views

Data lost in MySQL InnoDB after crash

I found a 6-days rollback in a MySQL 5.6 community edition server installation. It seems that some logs were lost, and I cannot understand why. 2013-12-22 20:38:54 380 [Note] Plugin 'FEDERATED' is ...
3
votes
1answer
211 views

How to optimize InnoDB tables in MySQL

I’ve been researching how to optimize only fragmented tables in MySQL and reviewed this post on optimizing tables. It basically performs a query against the information_schema database for any table ...
0
votes
1answer
43 views

Is ANALYZE useful immediately after creating an InnoDB index?

Let's create a new index on an existing table: CREATE INDEX `IDX_some_index` ON `some_table` (`a_column`, `another_column`); Is this useful? ANALYZE TABLE `some_table`; Can anyone point to ...
2
votes
1answer
81 views

AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?

I have a Multi-AZ AWS RDS instance running MYSQL 5.5 I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec ...