InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
66 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 ...
1
vote
1answer
60 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
32 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.
3
votes
3answers
113 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 ...
1
vote
1answer
45 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 ...
3
votes
2answers
408 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large (~...
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
58 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
10 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 ...
0
votes
1answer
39 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
27 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
97 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
30 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.... https://blogs.oracle.com/datawarehousing/entry/...
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
2answers
316 views

Trick for AUTO_INCREMENT on composite keys in InnoDB

In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table: CREATE ...
0
votes
0answers
52 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
63 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
61 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 ...
2
votes
1answer
156 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 mysql-...
0
votes
0answers
24 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 ...
2
votes
1answer
136 views

Query about MySQL Semaphores in Engine Status

During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following: ---------- SEMAPHORES ---------- OS WAIT ARRAY ...
0
votes
0answers
66 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. Here'...
0
votes
2answers
64 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 fi....
1
vote
1answer
109 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 rows....
1
vote
1answer
32 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 ...
0
votes
1answer
116 views

Consistent backup of large DB --single-transaction

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication. Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the ...
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 ...
4
votes
2answers
2k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
0
votes
1answer
22 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
44 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 myadmin....
1
vote
0answers
121 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
106 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 ...
1
vote
1answer
2k views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
4
votes
1answer
11k views

MySQL: Row size too large (> 8126)

I have a table which has 300 columns in it. Each column is up to 255 bytes (there IS a business justification for this). When I try it create it using VARCHAR(255), I go past the limit for the ...
2
votes
2answers
9k views

Unable to drop the table since its showing waiting for meta data lock

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
2
votes
1answer
131 views

autoincrement column issue in MYSQL

We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is ...
1
vote
1answer
38 views

Innodb Isolation levels and locking performance

I am reading a lot about locking and isolation levels in MySQL and InnoDB. I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ ...
1
vote
2answers
69 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 ...
0
votes
2answers
76 views

Storing multiple types of users and their respective credentials in separate or same table?

So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design. In ...
3
votes
1answer
54 views

Dead Locks between an insert ignore and and an update

Using MySQL InnoDB. As far as I know and read so many time dead locks only happens when one transaction is locking resource A and waiting for B and another transaction does the opposite. So with ...
1
vote
1answer
56 views

My MySQL Server Died

Approximately 1 time per week my MySQL server is shutting down. This is what the log looks like: 2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (...
0
votes
0answers
24 views

innotop “Command Summary”

We provide DBaaS. This is innotop C output: We have only 8% read workload? The rest is write? I don't understand this is very uncommon workload for DBaaS? Statistics since last restart? What is ...
1
vote
0answers
376 views

Negative InnoDB Write Log efficiency in mysqltuner.pl results

I noticed something strange in results from mysqltuner.pl. I see this in mysqltuner output from three of my four servers with MariaDB 10.1.12: [OK] You have the latest version of MySQLTuner(1.6.5)...
2
votes
0answers
92 views

InnoDB crashes on trx rollback

I have InnoDB assertion failure during the trx rollback 160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo 160316 10:47:31 InnoDB: Assertion failure in thread ...
2
votes
1answer
33 views

Saving data for a colossal maze

I want to build a labyrinth in which players can interact with others through leaving messages for one another, so in its most basic form each "tile" of the map is a forum thread or chat room. ...
0
votes
0answers
4 views

Different innodb_buffer_pool usage in nodes of a cluster

I have a 3-nodes Percona XtraDB Cluster (MySQL 5.6) that was put into operation three weeks ago. I noticed that the innodb_buffer_pool usage is very different in each of the nodes. In node #1, usage ...
0
votes
0answers
28 views

MySQL Storage Engine and Memory

I recently found out that, the databases does not have standard architecture(data model), some of them are, No relationship between tables(entities). No indexes defined. Only integer and varchar ...
1
vote
1answer
32 views

InnoDB slow query (few derived and dependent queries)

I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins). I need to get all 0 values from those tables in the last WHERE ...
9
votes
4answers
18k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
0
votes
2answers
170 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...