InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
26 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
26 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
25 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# ...
1
vote
0answers
53 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
40 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 ...
4
votes
2answers
535 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
0answers
36 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
14 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 ...
1
vote
1answer
30 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
21 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 ...
0
votes
0answers
12 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
34 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
23 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
58 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
33 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
58 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
31 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 ...
0
votes
1answer
91 views

MySQL server experiences periodic down time for 1 to 2 minutes

When under constant load, my MySQL server experiences periodic down times which last between 1 and 2 minutes and have no apparent causes such as too many connections or CPU overload. By down time I ...
2
votes
1answer
41 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
66 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
15 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 ...
3
votes
0answers
71 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
30 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
17 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
36 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
27 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
26 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 ...
2
votes
0answers
46 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
69 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
105 views

Avoid MySQL self-crashing on long semaphore waits

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
49 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
25 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
119 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
48 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
68 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, ...
0
votes
0answers
19 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
48 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
63 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
40 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. ...
3
votes
1answer
52 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
69 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 ...
3
votes
1answer
42 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 ...
1
vote
1answer
23 views

automatic innodb redo log archiving

We have an option to do recovery through bin-logs, but we thought recovery through bin-logs is not 99% consistent because there is no LSN(Log Sequence Number)/SCN (System Change Number) in bin-logs ...
0
votes
1answer
25 views

Prefix index length impact for VARCHAR?

I have a VARCHAR(255) primary field field in an InnoDB database table. I only store values of length 32 bytes in it (32 ASCII characters). My primary key has a prefix length of 767 bytes. Clearly, ...
0
votes
0answers
55 views

how to recover everything from ibdata in mysql

I lost all my databases files after my hard disk was broken, In a way or another I got ibdata files the size for the file is 4GB.After replacing log files and files and type innodb_force_recovery=6 in ...
1
vote
1answer
66 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
0
votes
1answer
15 views

First Insert is skipped by the trigger

I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit. The issue is First insert is somehow ...
2
votes
2answers
30 views

MySQL Database Archive

We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I we are planning archive inactive database.Is there any ...
0
votes
1answer
46 views

How to ORDER a big table?

I have a simple but big table (tens of millions of rows). The performance is quite good except when I need to order the table by the column with format datetime. A simple SELECT query to fetch the ...