InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

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 ...
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 ...
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 ...
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
28 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
99 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
0answers
53 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 ...
0
votes
0answers
25 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
111 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....
0
votes
0answers
68 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'...
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 ...
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
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....
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
125 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
108 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
167 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-...
1
vote
2answers
70 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 ...
1
vote
1answer
39 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 ...
0
votes
2answers
77 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 ...
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
26 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 ...
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 ...
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 ...
1
vote
0answers
378 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
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
29 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 ...
0
votes
1answer
19 views

InnoDB, Writing TRIGGER to INSERT into tables

Table1 "user_address" Field "u_id" Field "street_name" Field "locality_name" Table2 "street_master" Field "s_id" Field "s_name" I am supposed to write a trigger which BEFORE INSERT ON user_address(...
3
votes
1answer
70 views

InnoDB - Intermittent CPU spikes on a large database

I have a machine with a large MySQL 5.6 database (multiple tables with 10-100 million rows). It has a considerable amount of load, especially during the evening and is running on a 16 core machine. ...
1
vote
3answers
68 views

character set, not set correctly

I have two database with a table with utf8 table, I wanted to change the encoding of a column, I used two scenarios but a problem happened to me. at first the table was like this : CREATE TABLE `...
1
vote
1answer
36 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the pt-online-...
0
votes
1answer
62 views

Preventing MySQL index corruption

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen ...
1
vote
1answer
64 views

Are InnoDB files binary compatible between Windows and Linux?

I have seen many posts about recovering InnoDB files. The versions I have are: Windows: 5.6.17 Linux 5.0.96 (I cannot upgrade this at this time) If I create a new InnoDB file on the Windows ...
1
vote
1answer
139 views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
0
votes
0answers
35 views

How to improve my big MySQL database with low concurrence?

I have a MySQL database who is been used to storage a large amount of data, it doesn't have a lot of concurrence( its minimum), but every day a big amounts of data is loaded and also a profiling ...
0
votes
1answer
67 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
1
vote
1answer
79 views

Which error codes are safe for `slave_skip_errors` with MySQL Innodb

Does anyone know which error codes are safe to ignore during replication, meaning the slave will remain consistent with the master? For example [mysqld] slave-skip-errors=1007,1008,1050,1051,1054,...
0
votes
1answer
113 views

memory leaks in innodb

I have MySQL server 5.6 and I use stress test to evaluate the performance of it. after the test the result of mysqltuner.pl was like below : root@master-1:/home/debian# perl mysqltuner.pl Please ...
0
votes
1answer
98 views

Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

I changed values for my Mysql parameters 1. innodb_flush_log_at_trx_commit=2(before it was 1) 2. sync_binlog=0(before it was 1) This has changed some of my parameters on AW RDS drastically ,here ...
0
votes
1answer
52 views

Is it good idea to use RAID 1 mirroring to back up database content

I'm working on web hosting project. The planned system structure is going to be like this: one main hard-drive will contain the binary of mysql. A second hard-drive will contain the datadir the ...
0
votes
0answers
26 views

Problem creating database and tables from script [duplicate]

I've done a model diagram with Mysql Workbench, when export the SQL script I run it on my MySql on my develpment ubuntu and works fine but when I run it on the production server the scripts fails. The ...