InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
0answers
29 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
0
votes
0answers
12 views

What are the best settings of InnoDB for FULLTEXT queries?

This question has been asked before, but I was unable to find the right answer for my situation. We have a local server with Quad core and 32GB RAM dedicated for a single InnoDB database, which ...
1
vote
1answer
28 views

What happens to mysql information_schema after switching to innodb_file_per_table?

We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box. We've backed all our dbs up and about to ...
0
votes
1answer
35 views

how to purge / shrink .idb tables

I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables? I have tried to use optimize table table_name but after the query the .idb ...
1
vote
0answers
36 views

howto read/interpret “show engine innodb mutex” output?

We use a 3 node Galera cluster with version 5.5.41-MariaDB-wsrep MariaDB [(none)]> show engine innodb mutex; +--------+---------------------------------+------------------+ | Type | Name ...
1
vote
1answer
13 views

MySQL innodb_log_file_size change is not being recognized

I am trying to set the innodb_log_file_size option in a MySQL 14.14 (distrib. 5.6.30) installation on Ubuntu 15.10 to solve an issue (importing a database with large BLOB fields) however the log size ...
1
vote
1answer
59 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways ...
1
vote
0answers
53 views

MySQL is using swap memory while I have plenty of free RAM

I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read ...
0
votes
1answer
53 views

improving performance of concurrent inserts on innodb with randomized primary

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up ...
1
vote
0answers
23 views

Can MyISAM tables affect the backup recovery of innodb tables on Amazon RDS?

I have an Amazon RDS instance containing several databases. All the tables are innnodb except from one, that is MyISAM and currently cannot be converted to innodb. I know that the recovery of MyISAM ...
0
votes
1answer
46 views

mysql crashed, innodb fails to recover

On Mysql 5.0 on CentOS 5.11, mysqld service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database ...
1
vote
2answers
39 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
28 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
0
votes
1answer
52 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
0
votes
0answers
6 views

1K unused pages in each InnoDB buffer pool instance

I have configured a InnoDB buffer pool of 12 Gb, split into a dozen instances 1 Gb each. The buffer pool hit rate is 1000 / 1000, therefore so far it's looking good. Looking at the stats of the ...
0
votes
0answers
17 views

Completely reset innodb logs and files

So long story short, we've been dealing with a corrupt innodb installation on a MariaDB 10.0 server after a power outage, specifically a log in the future error. Rather than try and fake the log ...
0
votes
1answer
14 views

Monitor locks over long period of time

I am having some issues with one of my tables in a production server. I know there are many diffrent threads running on this table. I need to put some kind of monitoring tool to see how many locks ...
2
votes
1answer
59 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
0
votes
0answers
48 views

InnoDB: why “non-locking” SELECT still gets locked?

This issue can be related to my first question: InnoDB: very slow random updates, hanging in “updating” state But i think it needs to be clarified in a separate thread. The Problem: a simple read ...
1
vote
1answer
59 views

Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?

I develop an application that uses MySQL and explicitly uses InnoDB table engine. In a few cases we have clients using this application with MariaDB or Percona. I understand that MariaDB and Percona ...
1
vote
0answers
56 views

InnoDB: very slow random updates, hanging in “updating” state

I've been fighting my problem for the whole last week, studying stackexchange threads with similar issues in the first place. The main problem is that after switching from MyISAM to InnoDB ...
0
votes
1answer
62 views

MySQL - NOT IN … vs LEFT JOIN

I am trying to understand in terms of performance which approach is better: Query 1: SELECT * FROM table1 WHERE col1 NOT IN(SELECT col1 FROM table2) Query 2: SELECT * FROM table1 LEFT JOIN table2 ...
3
votes
0answers
44 views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
1
vote
1answer
53 views

MySQL INSERT … SELECT Performance

I know that this query will lock table2: UPDATE table1... SELECT .. FROM table2 How about this query ? INSERT INTO table1... SELECT .. FROM table2 Does this query also create a lock on table2?
0
votes
1answer
21 views

How can I disable xb_doublewrite?

Getting error while service restart. [ERROR] InnoDB: A parallel doublewrite file /test/mysql/xb_doublewrite found on startup.
1
vote
1answer
41 views

Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files

Trying to migrate individual tablespaces back into the shared ibdata tablespace. MariaDB version 10.0.16 SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going ...
0
votes
1answer
41 views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT ...
0
votes
0answers
55 views

How I can reduce disk IO in MySQL 5.6.26?

I was using MySQL version 5.1.52 for my application but recently I have updated it to 5.6.26, but now I am facing very high disk IO. Following are system configuration: OS : Linux 2.6.39.4 RAM : ...
0
votes
0answers
8 views

Server memory consumption not changed after innodb buffer size change

I observed a strange thing while applying the innodb_buffer_pool_size on mysql. I applied the innodb_buffer_pool_size=20G (75% of my physical RAM size) and as per logs and innodb status the mysql is ...
3
votes
3answers
111 views

What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

Say I have a 1-to-N relationship (person_id, pet_id). I have a table where pet_id is the primary key. I understand that an InnoDB secondary index is essentially a B-tree where the values are the ...
0
votes
1answer
38 views

How to delete rows from the mid of the table without any where condition?

I have a table testing with two columns id: auto_increment name: varchar(20) I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records ...
2
votes
1answer
22 views

In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory ...
3
votes
2answers
85 views

Better understand table indexing best practices

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider: The tables are not that big, I think (1000 - 10,000 rows). The searches are made ...
1
vote
1answer
25 views

Partition Wise Joins in Mysql

Is it possible to create or simulate a partition-wise join in Mysql/Innodb?? Here is a good description of partition-wise joins.... ...
1
vote
2answers
35 views

InnoDB file per tablespace

I am using following syntax to create an InnoDB table: create table InnoDB_Test(a INT) DATA DIRECTORY='/var/log/storage/MySQL' INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;` I want to ...
0
votes
0answers
51 views

Very Very Veeery slow insert queries

I am writing here because I`ve already spent days reading tutorials, other topics in here, and trying different configurations. The project : Fashion website with ~200k products which should be ...
1
vote
1answer
54 views

MySQL - Deleting old data; Select with index taking time

I am a bit confused about 2 queries. First query I am running on a 50m rows table by index on a timestamp row with a limit, I would expect the query to be immediate regardless of the size of the ...
0
votes
2answers
54 views

Memory leak on MySQL slave?

I have a Percona MySQL master-slave server configuration(5.6.20-68.0). The memory usage by MySQL on the slave server is constantly growing. Now it reached 68G despite on fact that ...
0
votes
0answers
20 views

Boosting select queries during INSERT ON DUPLICATE KEY UPDATE into 150Gb innoDb table

I have a single server with 2 innoDb tables: 35Gb (partitioned, 24M rows), 150Gb (without partitioning, 220M rows). 25% data, 75% index. 99% of select queries are SUM + WHERE + GROUP BY. 100% of ...
1
vote
1answer
90 views

InnoDB MVCC vs Locking

From what I understand you InnoDB supports Row Level Locking MVCC (Multiversion Concurrency Control) Row Level Locking Locking is for when multiple writers are trying to update the same ...
0
votes
0answers
40 views

Cannot open InnoDB table though the .frm file for the table exists

tonight, my local Server has crashed because the power went out. I do not have backups because this was only experimental. And for the sake of learning, I'd like to know what to do in this case. ...
1
vote
1answer
31 views

MySQL on idle low-end server unresponsive

I have a small virtual Server: 2 Cores, 2 GB RAM, Windows Server 2012 R2, Plesk, MySQL 5.6 (my.ini see below). Pagefile is disabled. Taskmanager says 1,3 GB RAM used. Only a single Wordpress ...
1
vote
2answers
30 views

MySQL Replication issues on server placed in different continents

I am running 2 cpanel servers with MySQL Replication set on them, one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason ...
0
votes
2answers
56 views

MySQL -Query with Union doesnt use index

Long story short, the following query runs immediately: SELECT COUNT(*) FROM indicators.fileso WHERE sha256 IN ( SELECT a.sha256 FROM temp.ananifilesinfo a JOIN indicators.filesi fi ON ...
0
votes
1answer
20 views

Mysql locking - How to acquire a lock in the DB for each userId

Hi I have a J2EE Server(clustered) that executes requests from multiple users. I would like that each request of a given user will be handled like so: { acquireLockForUser(userId); // i.e. insert ...
1
vote
1answer
36 views

Cannot open some tables in phpmyadmin

Recently i get backup of my database from host in that backup i get table in .ibd,.frm,.myd format . I placed this files in xampp->mysql->data->test . Now i can open the tables in php ...
1
vote
0answers
92 views

How to convert .ibd files to .sql [closed]

Could any one please tell how to convert .ibd , .frm, .MYD, .MYI FILES TO .SQL . Or is there any way to open this files in php my admin .. Please tell how to convert this to .sql file
4
votes
2answers
88 views

What's the risk of keeping a table with row size > 8126 in MySQL 5.7?

I just upgraded from MySQL 5.6 to 5.7, and had the bad surprise to discover that a WordPress plugin table contains a ridiculous 438 fields, and exceeds the row size limit of 8126 bytes. Here is the ...
2
votes
1answer
94 views

ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option

I am trying to import mysqldump into following system: # cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) # rpm -qa | grep ^mysql mysql-community-libs-compat-5.7.11-1.el7.x86_64 ...
1
vote
2answers
64 views

Select query on an index column with gap locking

I am trying to understand in which cases will a select query make a gap locking on an indexed column select. What I read so far: Select queries use the repeatable read mode in InnoDB and will not ...