InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

2
votes
1answer
31 views

InnoDB: “Only one log file found” after binary import

My first one on DBA... After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different ...
0
votes
1answer
16 views

How can I tell when InnoDB recovery is complete/check its progress?

I'm writing a process to bring up a new instance from our nightly database snapshots, and I want to watch its progress and perform some actions once the database is up. When mysql first comes up, it ...
0
votes
0answers
17 views

Why does Amazon RDS memory utilization drop and disk space utilization rises?

I encountered a problem with my Amazon RDS(db.m2.2xlarge) where DB connections spiked up and my website started responding slowly. On looking up the metrics I was not sure why my freeable memory shot ...
2
votes
2answers
61 views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
0
votes
2answers
32 views

Building a dedicated physical MySQL server [closed]

I have a database that I use to mine a "primary" 20-column table with 18 million rows and growing by about 2 million a week, and that may accelerate. I am working to pre-process data by running PHP ...
1
vote
2answers
78 views

Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error or it says it does not exist. I've read the other ...
0
votes
4answers
33 views

Converting to MyISAM, `foreign_key_checks = 0` Not Working

At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables. However, when I attempt to alter ...
0
votes
1answer
19 views

MySQL Update Multiple Rows in Large Table

I have a large InnoDB MySQL database table. I would like to run this query against the database table: Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = ...
0
votes
0answers
30 views

MySQL Table with 568 columns: 1118, 'Row size too large…'

I'm currently migrating a database from Oracle to MySQL5.6. One table consists of 568 columns. When atempting to create the table I received this Error 1118, 'Row size too large..' I already ...
1
vote
2answers
60 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
2
votes
2answers
58 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
2
votes
1answer
61 views

Optimize Percona MySQL InnoDB 3.2TB database

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project) I have a database server that ...
0
votes
0answers
13 views

Disable procedures for data load in MySql using Innodb engine

I have a database with an integrated ledger for a multi-unit company. As we bring new units into the database we load their existing ledger data into ours. Unfortunately the existing procedures on the ...
2
votes
2answers
81 views

How to monitor InnoDB Disk I/O Required for MySQL?

I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and ...
0
votes
1answer
30 views

InnoDB issue after power outage a few weeks ago - no backup

A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have ...
0
votes
0answers
12 views

Performance hit from moving DB to another drive symlink? [migrated]

We're considering moving one database (there are 4 of them in total) with InnoDB tables, to a separate partition in a Ubuntu 14.04 VPS. This DB gets read/write intensive (500 writes per sec, about ...
1
vote
1answer
40 views

Safely changing innodb_buffer_pool_size on MySQL replication master?

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown. What is the ...
0
votes
1answer
52 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
-1
votes
1answer
29 views

60M+ InnoDB table simple query performance HELP

I know it is a repeated question but really need help here. What can I do to improve the performance of this query? Considering that I have no freedom to change system variables/configs :( Thanks in ...
1
vote
1answer
41 views

Benefits of Barracuda and Compression

I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression. My server is currently using Antelope, as it is ...
0
votes
1answer
28 views

ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT? What are the benefits between each other?
0
votes
1answer
16 views

SHOW INNODB STATUS averages calculated

I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed ...
0
votes
1answer
46 views

Can't create table, but table doesn't exist

I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db: mysql> USE my_db; mysql> DROP TABLE my_user; mysql> ERROR 1051 (42S02): ...
1
vote
1answer
32 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
0
votes
0answers
11 views

What is data like *after* fixing a corrupt MyISAM or InnoDB table?

I collect user data and store in an a database. Some of the tables are MyISAM and some are InnoDB. Lately, both have been experiencing regular corruption for reasons not clear. I know that restoring ...
0
votes
0answers
21 views

Innodb Deadlock During Insert

What could be the reason behind 2 inserts causing a deadlock? Here is what I get from log file: *** (1) TRANSACTION: TRANSACTION 6CA06B9, ACTIVE 6 sec inserting mysql tables in use 1, locked 1 LOCK ...
0
votes
1answer
33 views

MyISAM InnoDB performance

MyISAM vs InnoDB I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself. When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver. ...
2
votes
1answer
26 views

MySQL InnoDB transactions with lot of delete instructions

We have a process that runs frequently and does the following steps on a mysql database of type innodb. SET autocommit = 0; DELETE FROM table WHERE category='something'; -- every 300 rows or whatever ...
1
vote
1answer
35 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
1
vote
1answer
22 views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
1
vote
1answer
39 views

How can I solve this error InnoDB: is in the future?

InnoDB: is in the future! Current system log sequence number 412018685584. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See ...
0
votes
0answers
29 views

Unable to load innodb log file settings from my.cnf

I'm coming up against the error: InnoDB: ERROR: the age of the last checkpoint is 9449527, which exceeds the log group capacity 9433498. If you are using big BLOB or TEXT rows, you must set ...
1
vote
1answer
62 views

More Details for MySQL's errorno:150

When MySQL can't create a table due to a foreign key constraint problem, it returns the following error Can't create table 'dbname.table_name' (errno: 150) Is there anyway to have MySQL tell you ...
2
votes
1answer
148 views

MySQL Index creation failing on table is full

UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space. Original Q: I'm trying to add an index to an InnoDB table, and ...
1
vote
0answers
34 views

LOCK=NONE when adding NOT NULL constraint fails with “cannot silently convert NULL values” error despite no NULL values being present

I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table ...
-1
votes
1answer
48 views

2 tables in mysql database overloading cpu even after optimize

thanks for reading this question i hope someone can give some insight into some steps. Here is my steps report. I added memcached instead of mysql cache. optimize the 2 tables from buddypress that ...
2
votes
0answers
48 views

Denormalizing to speed up inserts

I have a MySQL InnoDb table with approximately 40 million rows and the following columns: param1 (int), param2 (int), param3 (int), geo (char2), value1 (bigint), value2 (bigint) I have an ...
1
vote
1answer
44 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
87 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
54 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
15 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
418 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
46 views

Mysql Queries cause high load in server

I have problem in the database ,here is phpmyadmin status queries --------------------+ ...
0
votes
0answers
16 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
48 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
96 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
61 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
37 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
102 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
323 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... ...