InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
47 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
16 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
51 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
69 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
313 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 ...
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
27 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
31 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
18 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 ...
3
votes
1answer
60 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
54 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
32 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 ...
0
votes
1answer
60 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
53 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
98 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
25 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
61 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
55 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] ...
0
votes
1answer
77 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
78 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
48 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 ...
1
vote
1answer
46 views

How can I 'further' improve my table and queries

This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query. I also changed my application so that unused filters weren't ...
0
votes
0answers
43 views

Need help with corrupted MySQL database

I could use some step by step help on using innodb to hopefully recover a corrupted MySQL database. We are running moodle on a ubuntu server which uses the LAMP stack. The database has been corrupted ...
4
votes
2answers
69 views

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking ...
2
votes
0answers
72 views

TokuDB vs InnoDB Database data size

I have two percona servers, one running with TokuDB tables and the other with InnoDB tables. On each server, i have create a single database, with a single table in it containing 10 millions records ...
1
vote
0answers
32 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
1
vote
1answer
38 views

Galera cluster: change the size of InnoDB Redo log files online?

We have a 3 node Galera cluster. I need to change the mysqld innodb_log_file_size parameter (see Changing the Number or Size of InnoDB Redo Log Files). I do this by stopping all 3 nodes and ...
0
votes
1answer
53 views

How does InnoDB represent composite integer keys?

Let's say I have the following table: Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket)); How is the composite primary key representation when stored on disk? ...
1
vote
2answers
41 views

Get table sizes without SELECT permission

I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database: SELECT table_name, data_length, index_length FROM information_schema.TABLES The user the ...
1
vote
1answer
242 views

MySQL 5.6 Server start fails: InnoDB Operating system error number 23

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench ...
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# ...
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 ...
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 ...
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, ...
1
vote
1answer
58 views

InnoDB AUTO_INCREMENT resetting itself without MySQL restart

We've recently noticed the following scenario happening in many of our databases: A record is inserted, and receives an AUTO_INCREMENT primary key. The record is deleted. Another record is inserted, ...
0
votes
1answer
43 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
62 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 ...
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. ...
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 ...
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 ...
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
2answers
43 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 ...
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 ...
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
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 ...