InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
73 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
30 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
200 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
10 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 ...
1
vote
1answer
27 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
16 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
408 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
22 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
316 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` ...
3
votes
5answers
678 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
1answer
43 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
15 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
3
votes
1answer
862 views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
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
34 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
64k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
0
votes
1answer
194 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
2
votes
2answers
303 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 ...
1
vote
2answers
744 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
3
votes
2answers
1k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
0
votes
0answers
25 views

How to break down a COMMIT transaction?

I suspect the following transaction is blocking a bunch of others in my mysql-5.6.19b.R1 database: ---TRANSACTION 10769842051, ACTIVE (PREPARED) 51 sec flushing log 3 lock struct(s), heap size 360, 1 ...
0
votes
1answer
320 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
1
vote
1answer
121 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, ...
0
votes
1answer
698 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 ...
0
votes
1answer
43 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
183 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
2answers
2k views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
0
votes
1answer
23 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
37 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
168 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
99 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 ...
2
votes
1answer
143 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
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
138 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
0
votes
1answer
31 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
49 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 ...
1
vote
1answer
212 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
0
votes
1answer
147 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...
1
vote
1answer
124 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 ...
3
votes
2answers
163 views

MySQL 1264 warning from libmysql

I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader). I managed to connect and things are going almost fine. I use mysql_real_connect and ...
0
votes
0answers
28 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
29 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
55 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
304 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
2
votes
2answers
195 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 () ...