InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

9
votes
6answers
16k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
3
votes
1answer
3k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
1
vote
1answer
31 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
6
votes
1answer
8k 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 ...
0
votes
0answers
1k views

MariaDB MySQL server does not start

My MariaDB mysql server doesn't start with error 1067 This is the error log: 140222 0:43:44 InnoDB: The InnoDB memory heap is disabled 140222 0:43:44 InnoDB: Mutexes and rw_locks use ...
0
votes
1answer
83 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in mysql although I feel the schema should be optimized for it since it's based on the wordpress database model. I've got 4 tables... ...
1
vote
2answers
1k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
1
vote
1answer
33 views

Very weird Innodb Corruption Issue

I'll try to keep this to the point. System: OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free) I have a mysqldump dump file (3GB), that was created when the innodb database was working fine. ...
0
votes
1answer
202 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
0
votes
2answers
28 views

MariaDB/InnoDB: What to do on “Locks in Status-Log but no locked table found”

We migrated lately from MySQL to MariaDB. SELECT VERSION() 10.0.13-MariaDB-log When executing this: SHOW ENGINE INNODB STATUS I get (shortened): ... ---TRANSACTION 279257007, ACTIVE 23981 ...
2
votes
1answer
42 views

Is it possible to restore a MySql database only using files from Data directory?

I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to ...
1
vote
1answer
32 views

Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size = ...
0
votes
2answers
457 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
2answers
46 views

Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load. It took me 15 ...
2
votes
1answer
322 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
1
vote
1answer
56 views

MySQL InnoDB 5.6.23 cannot start segmentation fault 11

I have a Linux Gentoo server which has OwnCloud application installed. It works for about 2 months with out any problems, and sudden this morning MySQL died. I have tried to restart mysql server and ...
1
vote
1answer
18 views

if set global transaction, how can I commit?

I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
2
votes
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
2answers
2k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
0
votes
1answer
46 views

MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

I'm trying to convert/copy MyISAM to InnoDB table and I'm getting the following error message: mysql> INSERT INTO test.innodb_table SELECT * FROM test.myisam_table ERROR 1118 (42000): Row size ...
73
votes
9answers
66k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
2
votes
2answers
326 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 ...
1
vote
2answers
2k views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
0
votes
1answer
44 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign ...
0
votes
2answers
186 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
68 views

Rolling update to all rows in innodb table (no rollback if aborted)

I have the following table, which stores the Social Security Death Master File database (~86M rows): CREATE TABLE `death_master_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ssn_an` smallint(5) ...
0
votes
1answer
27 views

InnoDB index concurrent inserts

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, ...
1
vote
2answers
50 views

Speeding Inserts of longtext or longblob columns

i'm currently migrating a DB2 database to MySQL, using SQL WORKBENCH Tool It basically does a select from DB2 and Insert to DB2. It works fine, but on the tables with longblob or longtext columns ...
1
vote
1answer
245 views

Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything. But, Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN ...
7
votes
2answers
2k views

COUNT(*) on InnoDB whenever phpMyAdmin loads

It's a known issue that innodb is slow in SELECT count(*) FROM Table Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb ...
3
votes
2answers
112 views

Inner Join and Right Join same query

I'm using MySQL and InnoDB, I have a table reserver with 2 columns : idReservation and idChambre I have a table reservation and a table chambre Now I want my query to return every "chambre.id" who is ...
3
votes
4answers
5k views

Mysql innoDB write operations are extremely slow

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
1answer
38 views

MySQL query INSERT INTO … failed - The Table “x” is full

I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out. Environment: Webmin/Virtualmin | MySQL version 5.1.73 on ...
0
votes
2answers
73 views

MySQL - Changing from system tablespace to file per table

The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table ...
0
votes
0answers
40 views

Cannot open table slave_master_info

I have a dabatase working on InnoDB engine Now it is damaged. I can´t open any table, it shows error 1146 Table 'database.tablefromdatabase' doesn't exist I tried innodb_force_recovery with all ...
0
votes
0answers
37 views

CPU jumps massively and stays high, under very light load

We had two separate RDS instances exhibit the same pattern today. This was both during low load times, one during the lowest load time for that server. They both started at almost the same time. No ...
0
votes
1answer
59 views

MYSQL any way to improve this query's performance?

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
0
votes
1answer
34 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
0
votes
1answer
38 views

What is the performance benefit when using a temporary table to update a regular table?

I'm trying to understand what the performance benefit between two different methods of updating multiple table rows. For the first method we have an explicit update statement for each line to be ...
0
votes
0answers
43 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
1
vote
2answers
98 views

Large ibdata file in MySQL

I am having MySQL database with all my tables as InnoDB with file_per_table config on. But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have ...
0
votes
0answers
55 views

Why is MySQL not saving inserted data?

So I have the following insert statement that I run through MySQL Workbench: INSERT INTO PeakOnlineCount (time_stamp, LocationId, Count) values ('2015-03-26', 0, 1) If I run the following query: ...
0
votes
0answers
196 views

How to restore mysql database in XAMPP using backup of mysql folder?

I have a backup of the entire xampp/mysql folder. How can I use it to recover my old db on a fresh installation of XAMPP? Simply copying the old xampp/mysql folder to new xampp leads to mysql errors ...
0
votes
2answers
44 views

Slow database drop in mysql

Just installed a new 24 core server and it is noticeably slower when doing truncates and drops. Example: $ mysql -u root lmcdental_test2 Reading table information for completion of table and column ...
0
votes
1answer
19 views

I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB?

FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below: Currently a database I am in charge of locks up every once ...
0
votes
0answers
42 views

Percona Master-Master lag issues

I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation. What could be causing ...
1
vote
1answer
38 views

“Unknown table engine 'innodb'” error after changing my.cnf

I am getting "Unknown table engine 'innodb'" for some query after changing my my.cnf. I have reverted the old my.cnf and restarted mysql and now I can see innodb. Could you please help me figure out ...
0
votes
1answer
96 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
1
vote
1answer
2k 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 ...
4
votes
1answer
63 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...