InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

3
votes
0answers
174 views

“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28. During high load, randomly we got the below error. MySQLcheck, innochecksum shows no ...
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 ...
2
votes
0answers
78 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
2
votes
0answers
113 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
2
votes
0answers
43 views

Count how many locks required for a MySQL (InnoDB) query

I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to ...
2
votes
0answers
114 views

Sleeping connections

I have a dedicated MySQL server(8GB) with following configuration. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql lower_case_table_names = 1 ...
2
votes
0answers
584 views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
2
votes
0answers
227 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
0answers
167 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` ...
2
votes
0answers
226 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
1
vote
0answers
33 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
vote
0answers
71 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
0answers
73 views

Recover innodb database from ibdata1 and *.frm files only

I have a problem on my server. I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started. I checked all mysql data, ...
1
vote
0answers
33 views

Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

First of all, excuse my English. I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by ...
1
vote
0answers
48 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
1
vote
0answers
82 views

MariaDB Galera Cluster

I have two mariadb servers in cluster that is working galera and xtradb. it is my primary server configuration: [mysqld] datadir=/var/lib/mysql query_cache_size=0 binlog_format=ROW ...
1
vote
0answers
46 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
1
vote
0answers
21 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
1
vote
0answers
130 views

InnoDB: error: space object of table

131212 10:35:18 InnoDB: error: space object of table 'main/wp_m_membership_levels', InnoDB: space id 119 did not exist in memory. Retrying an open. 131212 10:35:18 InnoDB: error: space object of ...
1
vote
0answers
371 views

Analyzing MySQL crash due to corrupt InnoDB page. Failed page checksum

Lately we have been pestered by infrequent sporadic database crashes on one of our MySQL instances. Each time it is due to a failed page checksum. It happens on a specific database but spans multiple ...
1
vote
0answers
51 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
1
vote
0answers
359 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
1
vote
0answers
786 views

How to prevent “ibdata files do not match the log sequence number”?

I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort: ibdata files do not match the log sequence number But I've ...
1
vote
0answers
382 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
1
vote
0answers
857 views

Tables don't exist after MySQL restart

I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior: I have some tables with data in them I made ...
1
vote
0answers
788 views

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ? updated to answer the 3 questions: mysql> show variables like 'version%'; ...
0
votes
0answers
28 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 ...
0
votes
0answers
12 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
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
20 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
0answers
27 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 ...
0
votes
0answers
14 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, ...
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 ...
0
votes
0answers
60 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 ...
0
votes
0answers
18 views

How can I determine the performance (rows/second) for a certain query on one of my tables?

I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records. The table has around 10 million rows. ...
0
votes
0answers
59 views

importing mysql data folder from an old instalation

i want to restore one database from my old mysql folder, i copied mysql/data folder from old installation into my current mysql folder, yet its not starting up any more. when i check the error_log ...
0
votes
0answers
20 views

InnoDB: detected cycle in LRU for buffer pool 0, skipping to next buffer pool

While looking through the error log for something else I noticed a couple instances of message posted as the subject. What is this about? Googling for the "error" doesn't bring much up. This is on ...
0
votes
0answers
23 views

Database cache problem

I have server based on Linux 64 Bit centos 6.5 and Mysql innoDB engine I created database for php script require database installation , after i successfully finished installation for first time i ...
0
votes
0answers
33 views

MySQL best practice DB recovery

I am trying to figure out what is the best practice is for Mysql recovery after a power failure. I need to find a way that if a server (which is running on a virtualbox VM) goes down when it goes ...
0
votes
0answers
51 views

MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

We have a 3 node MariaDB Galera cluster. All nodes are identical and the config on each is identical. They have 64GB of RAM each. The output of the following query is 28: SELECT ...
0
votes
0answers
23 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
0
votes
0answers
134 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
0
votes
0answers
106 views

Converting UUID's from CHAR(32) to BIN(16)

I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)... In each applicable table, we use a two ...
0
votes
0answers
43 views

MariaDB : Larger innodb_buffer_pool_size causing SQL updates to be slower

Has anyone here encountered such scenario before where and update statement took longer time to run when innodb_buffer_pool_size is bigger. I've run a few tests using the same statements on around ...
0
votes
0answers
23 views

Temporary table created on disk, with 14mb of database

i have only one database (on one machine) with size 14 mb for temporary table i have this configration max_heap_table_size= 1000M tmp_table_size= 1000M sort_buffer_size=4M join_buffer_size=4M i ...
0
votes
0answers
169 views

MySQL Innodb Concurrent Inserts and Selects - can't use multiple threads on FreeBSD

We have an application that needs to be dumping rows into some tables at a much faster rate than it currently is. It is a dual processor xeon DB server with 48gb of ram and a 4 TB disk RAID 10 (for a ...
0
votes
0answers
54 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
0answers
57 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
0
votes
0answers
156 views

Migrating from Wamp to Xampp - table doesn't exists

I have a directory with files where MySQL database was stored when there was used Wamp. Now I would like to use this data to read it using Xampp. I tried to set datadir in 'my.ini' file to that ...
0
votes
0answers
70 views

.myd and .myi versus .frm and .ibd

I'm new to MySQL.I have trouble finding the .frm files and .ibd files in Windows 8 Explorer.They seem hidden and I have to find them using 'Find' and 'Open File Location', not sure how to make these ...