InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
130 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
2
votes
1answer
94 views

Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at ...
0
votes
2answers
228 views

Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql I dumped data, dropped databases, and started mysql service. I got the ...
0
votes
0answers
72 views

hot/live sql backup

please excuse my ignorant question as I am still fairly new to database things so I have a mysql database and a part of it has file with average size of 10M I store them in their own directory and ...
0
votes
1answer
34 views

Is it possible to restore a database snapshot from a 64 bit system to a 32 bit one?

I would like to perform a restoration test on a snapshot backup of my MariaDB database using a test server running on a 32-bit OS. My production server is a 64-bit system. I would like to know if the ...
0
votes
1answer
366 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
1
vote
1answer
426 views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
0
votes
0answers
131 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 ...
0
votes
1answer
791 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
1
vote
1answer
47 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 ...
4
votes
3answers
800 views

MySQL buffer pool usage + high load

We've got a quite powerful server: HP ProLiant DL360p Gen8, 2xIntel Xeon E5-2630 2.30GHz, 128GB of RAM. The whole data set should fit in memory: mysql> SELECT ...
1
vote
1answer
332 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. ...
1
vote
1answer
258 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: ...
0
votes
2answers
290 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 ...
7
votes
3answers
1k 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 ...
2
votes
2answers
214 views

ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC Open log Flush log Open datafile Flush data O_SYNC NONE NONE fsync () ...
1
vote
1answer
139 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
2answers
467 views

Restore InnoDB to different machine

It was pointed to me from serverfault that the following question would be a better fit here so this is basically my original question from there. I currently have backups that were taken with ...
1
vote
1answer
827 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
1answer
283 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
66 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
268 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
3k 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. ...
3
votes
2answers
4k 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
985 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
152 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
82 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
351 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
449 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
306 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
22 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
962 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 ...
1
vote
1answer
44 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 ...
1
vote
1answer
259 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
328 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
37 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 ...
4
votes
1answer
702 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 ...
2
votes
1answer
420 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
21 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 ...
2
votes
3answers
5k 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
227 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
14 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
60 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
49 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
59 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
68 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
285 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
112 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
149 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
131 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 ...