InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
87 views

Finding the right MariaDB configuration

I’ve read several articles about how to configure MySQL/MariaDB, but now I’m kind of confused how to find the right values. Some articles suggest not choosing to high values, others recommend adapting ...
1
vote
1answer
38 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
1
vote
1answer
135 views

MySQL InnoDB table accepts Non-Existent Foreign Key

Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page. Working on Windows 7 64-bit. ...
0
votes
1answer
145 views

Recovering from removal of ibdata1 [duplicate]

I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the ...
0
votes
2answers
105 views

Is it safe to copy DB files directly from an LVM snapshot?

I saw two different approaches for backing up database with LVM snapshot. This article suggests spawning another MySQL instance to do an SQL dump. MYSQL Performance Blog suggests that it is safe to ...
1
vote
1answer
79 views

Change table from InnoDB to ARCHIVE in MySQL

I try change my table from InnoDB to ARCHIVE with SQL command: ALTER TABLE data_obook ENGINE = ARCHIVE; But it always show me only error: #1005 - Can't create table 'coincity.#sql-1350_8a' (errno: ...
5
votes
3answers
422 views

InnoDB import performance

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far). I did some research how to ...
1
vote
1answer
142 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 ...
1
vote
1answer
243 views

mysql server cannot launch in windows 7

I have been using MySQL as a windows service successfully on my windows 7 machine for perhaps a year. It has always worked fine. Here are the steps which I think caused it to no longer launch: ...
2
votes
2answers
914 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. ...
2
votes
1answer
133 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
39 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 ...
2
votes
1answer
2k views

MariaDB and XtraDB/InnoDB plugins

In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the ...
0
votes
0answers
78 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 ...
1
vote
2answers
2k 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 ...
70
votes
6answers
59k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
2
votes
2answers
231 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 ...
0
votes
4answers
372 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
75 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
67 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 ...
2
votes
2answers
131 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
1answer
152 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 ...
2
votes
2answers
320 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
0answers
16 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 ...
0
votes
1answer
37 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 ...
4
votes
1answer
4k views

MySQL Innodb corruption

I recently inherited a Drupal site that has an Innodb corruption. MySQL will run for sometime--usually about 24-hours but will eventually need to be manually restarted(see log output below). I have ...
1
vote
1answer
124 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 ...
6
votes
2answers
2k views

MySQL hogging memory

An installation of MySQL 5.6.10 on a virtualized Ubuntu 12.04 is exhibiting massive memory hogging. The mysqld process claims the entire available memory within a couple of hours of uptime and forces ...
0
votes
1answer
157 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
35 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 ...
2
votes
1answer
150 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
145 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
20 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 ...
5
votes
2answers
895 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
0
votes
0answers
13 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
48 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
45 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
38 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
0answers
250 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
1
vote
1answer
53 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 ...
2
votes
1answer
382 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 ...
2
votes
1answer
130 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
0answers
171 views

invalidating query cache entries(key) [duplicate]

when I tried to delete some records from a MYSQL server (version 5.0.95-log) and checked the process list I got the result as follows mysql> show processlist \G *************************** 2. ...
1
vote
1answer
75 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
71 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
3answers
134 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
5
votes
1answer
5k views

Error when dropping InnoDB database

I am trying to drop a database in mysql, but every time I get this error ERROR 2013 (HY000): Lost connection to MySQL server during query even logged in as root. Originally, I had a corrupted ...
1
vote
1answer
113 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
839 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 ...
0
votes
1answer
203 views

How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session. How do I make this setting stick after stopping and restarting MySQL? NOTE: I am on version 5.5.19 of MySQL ...