InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
203 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 ...
1
vote
1answer
116 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
653 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
108 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
2k 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
270 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 ...
1
vote
1answer
57 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 ...
3
votes
4answers
351 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 ...
0
votes
0answers
252 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
160 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 ...
0
votes
0answers
60 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
100 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
46 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
158 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 ...
2
votes
1answer
190 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
64 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
383 views

The total number of locks exceeds the lock table size, even after increasing buffer pool size

I am trying to partition a table with 200 million records in it. This table already exists: CREATE TABLE linkmap_ntoken ( target_nToken varchar(200) NOT NULL, ntoken_hashcode int(15) NOT NULL, ...
0
votes
1answer
423 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 ...
2
votes
2answers
150 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 ...
2
votes
1answer
259 views

Strange things with Avg_row_length

The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366. Later I dumped table with mysqldump, droped table, then created again ...
1
vote
1answer
137 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
40 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
187 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
0answers
90 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 ...
2
votes
1answer
4k 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
29 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
205 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
1answer
133 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
1
vote
2answers
783 views

MySql InnoDB tables are missing after restarting

I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at ...
3
votes
1answer
170 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
63 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, ...
3
votes
3answers
1k 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
768 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
305 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
1answer
112 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 ...
1
vote
2answers
116 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 ...
0
votes
2answers
96 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 ...
0
votes
2answers
99 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
179 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
1answer
316 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 ...
5
votes
1answer
1k 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 ...
0
votes
0answers
90 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 ...
1
vote
2answers
248 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
109 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 ) ...
3
votes
1answer
2k 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 ...
1
vote
2answers
554 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 ...
3
votes
1answer
352 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 ...
0
votes
0answers
33 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 ...
1
vote
1answer
210 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 - ...