InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
2answers
39 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
76 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
65 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
49 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
295 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
937 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
714 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
44 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
133 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
267 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
19 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
41 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
76 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
210 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
26 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
32 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
56 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
38 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
31 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
142 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
61 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
223 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 ...
32
votes
1answer
56k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increases in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
0
votes
1answer
74 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 ...
2
votes
2answers
508 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`), ...
1
vote
1answer
224 views

Slow database drop in mysql

Just installed a new 24 core server and it is noticeably slower when doing truncates and drops. Example: $ mysql -u root lmcdental_test2 Reading table information for completion of table and column ...
0
votes
1answer
536 views

mariadb won't start (InnoDB error: space header page consists of zero bytes in data file ./ibdata1)

The following messages are in /var/log/mariadb/mariadb.log when I try to start mariadb: 151103 13:42:01 [Note] InnoDB: Initializing buffer pool, size = 128.0M 151103 13:42:01 [Note] InnoDB: Completed ...
0
votes
1answer
63 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 ...
2
votes
1answer
62 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
72 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, ...
2
votes
2answers
9k views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
4
votes
1answer
74 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 ...
2
votes
1answer
170 views

“Unknown table engine 'innodb'” error after changing my.cnf

I am getting "Unknown table engine 'innodb'" for some query after changing my my.cnf. I have reverted the old my.cnf and restarted mysql and now I can see innodb. Could you please help me figure out ...
0
votes
0answers
24 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
0answers
10 views

Mtop - How to reset ID numbers?

Using Mtop for slow queries logging in our InnoDB. Since there is actually high database usage, the log IDs in front when using the mtop command are increasing rapidly and I would like to reset them, ...
1
vote
0answers
44 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. ...
0
votes
2answers
3k views

Migrate Jira to MySql - Unknown system variable 'storage_engine'

I'm migrating JIRA in Windows from HSQL to MySQL and I got stuck with the message Unknown system variable 'storage_engine'. Though I created a backup, installed MySQL and copied J connector to libs ...
1
vote
1answer
76 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
70 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
30 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 ...
3
votes
2answers
137 views

Release MySQL Memory and Performance Tunning

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file. The server is a dedicated server for MySQL database and the ...
0
votes
1answer
35 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
94 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
2answers
111 views

DB randomly locks up when innodb_buffer_pool_size is set high

I have been struggling to work out what could be happening here but for many months, since upgrading to mysql 5.6 we seem to be experiencing random lag spikes in page loads which has been tracked back ...
1
vote
1answer
321 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
6
votes
4answers
16k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...