InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
66 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 ...
2
votes
1answer
90 views

Moving the binary location of MySQL for replication

Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave? I want this because in our current ...
0
votes
1answer
66 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
48 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
56 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# ...
8
votes
1answer
9k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
1
vote
1answer
47 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 ...
1
vote
0answers
97 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 ...
3
votes
5answers
355 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
0
votes
1answer
44 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 ...
5
votes
2answers
544 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, ...
3
votes
3answers
4k views

ibdata1 grows exponentially when innodb_file_per_table is configured

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month). Is My my.cnf file is ...
0
votes
0answers
63 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 ...
3
votes
1answer
7k views

Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?

I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
0
votes
1answer
4k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
1
vote
0answers
20 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. ...
37
votes
4answers
39k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
7
votes
0answers
248 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, ...
4
votes
1answer
4k views

MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the slave. As for all the replication, ...
13
votes
4answers
90k views

MySQL: how to restore table stored in a .frm and a .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
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 ...
1
vote
2answers
64 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
0answers
44 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 ...
21
votes
6answers
5k views

DELETE command not completing on 30,000,000 row table

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data made by an error by our programmer. Before I add any new, ...
0
votes
2answers
44 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 ...
5
votes
3answers
5k views

Frequently slow queries while in the 'Update' state

I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ ...
1
vote
1answer
77 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
1answer
68 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
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 ...
2
votes
1answer
51 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
299 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 ...
2
votes
2answers
1k views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
1
vote
2answers
786 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
12
votes
3answers
12k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
0
votes
0answers
50 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 ...
3
votes
0answers
142 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 ...
1
vote
2answers
320 views

MySQL - Slow InnoDB Write Speeds on Windows 10

I have Windows 10 bootcamped. I've installed the latest version of MySQL community server. I've also installed WordPress onto IIS. The MacBook has 16Gb of memory. Here is my config file [client] ...
0
votes
0answers
21 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 ...
0
votes
0answers
51 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 ...
2
votes
0answers
92 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 ...
2
votes
1answer
235 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
0answers
27 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. ...
3
votes
2answers
34 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 ...
1
vote
0answers
68 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 ...
0
votes
1answer
44 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 ...
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 ...
3
votes
2answers
70 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 ...
4
votes
2answers
151 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 ...
3
votes
0answers
63 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 ...
2
votes
1answer
230 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz ...