InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
238 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
2k 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
275 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 ...
6
votes
1answer
3k 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
759 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
1answer
370 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
271 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
1answer
175 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
62 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 ...
1
vote
1answer
351 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
359 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
390 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
106 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
1k 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
110 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
472 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 ...
3
votes
1answer
55 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
1answer
314 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 ...
3
votes
1answer
11k 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) ...
1
vote
0answers
45 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
38 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
1k 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
106 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
3k 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
715 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
672 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
5k 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
213 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 ...
4
votes
1answer
460 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 ...
0
votes
0answers
102 views

Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

I have a table with this structure: CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, ...
21
votes
3answers
22k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
3
votes
3answers
6k 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. ...
1
vote
1answer
2k views

Delete data from huge InnoDB table from MySQL Master and Slaves

We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year ...
1
vote
1answer
1k views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
0
votes
2answers
114 views

I can't get an import to go past 1.1gig

I can't get an import to go past 1.1gig. I have a large 4gig InnoDB table/database I would like to get on my "tried and true" box. Current config on this box I want to work with = Dell PowerEdge ...
0
votes
1answer
749 views

Why “.my.cnf” file is empty?

I want to edit some of InnoDB variables ,I go to the .my.cnf which is located in home/.my.cnf I found the file just empty. And when I check SHOW VARIABLES in MySQL it gives me all the variables and ...
0
votes
1answer
345 views

Where does mysql write temporary tables?

MySQL 5.1.x | Windows Server 2008 Data Directory is on a different partition. I have an innodb database (innodb_file_per_table is set) that I'm trying to optimize and I don't appear to have enough ...
0
votes
0answers
113 views

What are the steps to change tables engines from MyISAM to InnoDB?

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table : ALTER TABLE t ...
2
votes
2answers
554 views

MySQL - INSERT … SELECT .. FROM .. - Single transaction or transaction per row?

Question: I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup). I am using the following syntax to do ...
0
votes
1answer
40 views

is it possible to set engine for database (not the tables)?

There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server. They were imported and were displayed in the tree structure of phpmyadmin (under the ...
1
vote
1answer
196 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
1
vote
2answers
936 views

InnoDB + TokuDB fills up RAM then start swapping

I'm trying to face difficulties with my database lately. I've migrated from MyISAM to TokuDB v7.0.4 and InnoDB. I'm using MariaDB - 5.5.34-MariaDB-1~wheezy-log. My problem is that MariaDB fills up RAM ...
1
vote
2answers
3k views

ibdata1 grows exponentially when innodb_file_per_table is configured

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month). Is My my.cnf file is ...
2
votes
3answers
6k views

Purge old records from mysql ibdata1 file

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for ...
0
votes
0answers
52 views

How do I save file data to my newly mounted SSD in mysql innodb continually?

I've recently mounted a new SSD since my disk's empty space had less than 10% (mysql unbuntu linux) and this is what I did in my.cnf file to save data to my new path: innodb_buffer_pool_size=2G ...
2
votes
1answer
439 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
3
votes
2answers
143 views

optimal size of innodb buffer pool

I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about ...
1
vote
1answer
3k views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` ...
0
votes
1answer
337 views

What happens when the index size of my Mysql instance exceeds the innodb_buffer_pool_size?

I am using Amazon's RDS db.m1.small. innodb_buffer_pool_size is set to 1125.00 MB. Current index size is 1110.00 MB and growing slowly but steadily. I do not need more hard drive space. I want ...
1
vote
1answer
96 views

Cant unlock file (Errcode: 11) on my new tables

I get the following error on my MySQL database Cant unlock file (Errcode: 11) But this error only happens when I try to access my new table I created below has two tables 1st I created recently and ...