InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
52 views

Need help with corrupted MySQL database

I could use some step by step help on using innodb to hopefully recover a corrupted MySQL database. We are running moodle on a ubuntu server which uses the LAMP stack. The database has been corrupted ...
4
votes
2answers
77 views

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking ...
2
votes
0answers
89 views

TokuDB vs InnoDB Database data size

I have two percona servers, one running with TokuDB tables and the other with InnoDB tables. On each server, i have create a single database, with a single table in it containing 10 millions records ...
1
vote
0answers
33 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
1
vote
1answer
45 views

Galera cluster: change the size of InnoDB Redo log files online?

We have a 3 node Galera cluster. I need to change the mysqld innodb_log_file_size parameter (see Changing the Number or Size of InnoDB Redo Log Files). I do this by stopping all 3 nodes and ...
0
votes
1answer
56 views

How does InnoDB represent composite integer keys?

Let's say I have the following table: Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket)); How is the composite primary key representation when stored on disk? ...
1
vote
2answers
48 views

Get table sizes without SELECT permission

I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database: SELECT table_name, data_length, index_length FROM information_schema.TABLES The user the ...
1
vote
1answer
329 views

MySQL 5.6 Server start fails: InnoDB Operating system error number 23

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench ...
0
votes
1answer
93 views

Does innodb_buffer_pool_size accept “*GB” values?

I thinks innodb_buffer_pool_size is specified in bytes, would typing in values with unit (i.e 1024MB or 1GB) work? Has anyone already tried this? Specs: MySQL 5.6
1
vote
0answers
50 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
0
votes
1answer
65 views

Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT?

Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's SQL Query window or MySQL Connection Navigator instead of in a c# Mono,...
1
vote
0answers
101 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
1
vote
1answer
48 views

Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

I'm struggling to find enough documentation to help me answer my own question. The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value ...
5
votes
2answers
545 views

Foreign Keys doesn't work as I expect

Here are my tables; CREATE TABLE `borclular` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tc` char(11) COLLATE utf8_turkish_ci NOT NULL, `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL, ...
1
vote
1answer
62 views

InnoDB AUTO_INCREMENT resetting itself without MySQL restart

We've recently noticed the following scenario happening in many of our databases: A record is inserted, and receives an AUTO_INCREMENT primary key. The record is deleted. Another record is inserted, ...
0
votes
1answer
59 views

Does Mysql locks table while insert operation

I am using Mysql(INNODB) and does Mysql locks table when I do some insert operation? I have a huge table and it has lots of indexes. I do some research and Mysql doc says INNODB only locks rows not ...
0
votes
1answer
77 views

MySQL 5.7 not accepting values of innodb_buffer_pool_instances greater than 1

I've been tweaking the values of the buffer pool size (innodb_buffer_pool_size) and the number of buffer pool instances (innodb_buffer_pool_instances) in the my.ini initialization file to optimize ...
0
votes
0answers
73 views

How can I restore a MySQL dump faster on Windows?

I am restoring some InnoDB and MyISAM .sql files that are on the order of GBs (ranging from 1 GB to ~ 10GBs) and they are taking a day or longer to restore. The command I am using currently is: use ...
1
vote
0answers
21 views

How to restore InnoDB from files

here is the situation I fu*d up my mysql setup, before I did that I copied entire /var/lib/mysql directory to another location. Now fresh installation of mysql is back and I cannot restore my files. ...
0
votes
0answers
44 views

Is there an equivalent of virtual columns in mysql?

Currently I'm taking a look at amazon's new aurora database and I'd like to check how my current setup performs there. The original table is on maria DB and contains a virtual column `...
0
votes
0answers
47 views

limit of open files: Debian Jessies´ MySQL vs Community Oracle MySQL

I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6. I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables. Whilst for two or three years, I ...
1
vote
2answers
66 views

Why database size is smaller in RDS

I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer. I use this query: SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + ...
0
votes
2answers
45 views

MySql not using Buffer Pool

I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than ...
0
votes
0answers
7 views

Committing one transaction halts another transaction with InnoDB tables on MariaDB?

I was hoping someone could clarify what is happening and what the effect is. I need to wrap a process in a database transaction to protect against failure. The problem is that as things stand the ...
0
votes
1answer
72 views

Innodb full table Scan

mytable is an InnoDB table with (a, b) as the primary key. Does this query check all table? Select * from mytable where ( b='2' and c='3') I want to find out if 'cluster' structure ...
0
votes
0answers
58 views

How to start mysql without recovery rollback

I started a delete operation on table "A" in mysql which ran for 12 hours (lots of triggers and cascading deletes) already before I decided to kill this transaction. I waited then for another 12 hours ...
2
votes
1answer
52 views

Why might a query not run until after I restart the MySQL server?

Why might MySQL not execute a query? Why might it freeze? It simply sits there and does nothing. Show processlist gives a result of "sending data" for as many hours as it takes for me to give up and ...
1
vote
1answer
78 views

Innodb composite primary key with 4 columns vs MyIsam

I've created "MY TABLE" based on Innodb with composite primary key because I am storing many trees inside this table. The PK is for example (a,b,c,d). 1- Does Innodb store rows with same (a,b,c) near ...
0
votes
0answers
22 views

MySQL 5.6.18 got signal 11

My MySQL Server 5.6.18 got signal 11 today very randomly. Has not happened before. This is the stack trace : stack_bottom = 7fdb9bcb1e0f thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)...
3
votes
0answers
167 views

MySQL process stuck on “cleaning up”, locking table forever

It was suggested I post here as opposed to StackOverflow. I'm new to debugging mysql/innodb issues so here is the best I have: I'm trying to run an integration test so these results are near ...
0
votes
0answers
57 views

Individual inserts wait for several seconds for primary key lock (inno DB)

my application inserts about 2,000 records in a table per hour, at some time the database started to delay this inserts throwing a "lock time wait" exception, after some research I got this output ...
0
votes
0answers
28 views

Get records with specific group name from table A and see if any exist in table B, if it does then add matching records in Table B to group

Let me start off by saying I am not a DB admin or a DB pro, I work with SQL as a php developer but that's about it. So I hope I can explain this properly and I hope one of you DB guys/gals can help. ...
1
vote
0answers
93 views

How to resolve Innodb lock

I am trying to run a sitemap generator job for Magento and here is what I get as Innodb status after the query has failed to complete even after several hours. I do not know much about DBA stuff and ...
3
votes
2answers
36 views

import mysql schema data folder instead of from a dump

current state I have prod and dev mySQL databases. To create the dev database, I used the various schema backups generated from production and imported them like so: mysql -u my_user -p target_db &...
0
votes
0answers
32 views

cannot install_db after deleting /var/db/mysql

I am trying to reset mysql5.6-server back to its installation defaults and blowout/nuke all the databases to start off fresh and clean. [root@yoda:~] # rm -fr /var/db/mysql [root@yoda:~] # mkdir /var/...
2
votes
0answers
103 views

MySQL: Is the read committed isolation level safe with mixed binary log format?

When using the MIXED format with the READ COMMITTED isolation level, am I safe? The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed) If you use ...
3
votes
2answers
71 views

Should I normalise this database?

I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system. I have two table designs in mind. One is this: CREATE TABLE `...
7
votes
0answers
317 views

Avoid MySQL self-crashing on long semaphore waits [closed]

I run heavy queries on my InnoDB databases every night, and sometimes it causes long semaphore waits. More background in this question. What I have observed is that if the data is stored on an SSD, ...
3
votes
0answers
64 views

How does mysql(innoDB) handle same foreign key for join

I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key ...
0
votes
1answer
50 views

Does innodb_data_reads include reads from the buffer pool?

We have set up some monitoring on many of our databases that serve many different applications. It seems that they all report back more writes than reads when in reality that is very unlikely. I ...
4
votes
2answers
157 views

Integrate MySql Full Text searches in CMS

I'm developing my own CMS and using MySql v5.5 DB to save the content items records. Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on ...
0
votes
1answer
115 views

RDS innodb_flush_log_at_trx_commit problem

I use Amazon RDS as mysql db backend for highload project and try to optimize insert performance for innodb tables. According to information from different sources - good candidate to speed up inserts ...
1
vote
1answer
73 views

Small database size but very slow performance. What can I do?

We have this opencart installation with a database size of roughly 120 MB, 90MB of which is MyISAM and other is InnoDB. Most of the queries like : SELECT DISTINCT *, a.*, vd.seo, p.image, p2s....
0
votes
0answers
26 views

Syntax error or access violation: 1286 Unknown storage engine 'InnoDB'

MySQL won't start after I changed the my.cnf file. After a lot of use on my site, MySQL crashes and I get this error: Syntax error or access violation: 1286 Unknown storage engine 'InnoDB' I ...
0
votes
1answer
68 views

mysql query taking differently long

I need some enlightenment on a MySQL query issue: I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me). To be ...
4
votes
1answer
87 views

Should a table be split if only a particular column gets updates?

I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the ...
1
vote
0answers
48 views

mysql innodb - strange data loss

I am running a mysql database on linux with several InnoDB tables. At some point, the ibdata1 file got quite big and I wanted to delete it. So I made a dump, deleted the file and restarted the server. ...
2
votes
1answer
65 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...
1
vote
1answer
78 views

Decrease Query Execution Time

I have been having issues with 2 queries with query execution times up to 250 seconds for each query. The tables joined have each less than 10,000 rows, with exception to 2 tables that have around 60k ...
2
votes
1answer
97 views

Cannot access MySQL database after moving files in data directory

I am using Xampp locally and I recently did a computer upgrade where I went from XP to Windows 7 and also got a new drive to install Windows on. I have all my old Xampp files in: G:\xampp I installed ...