InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
0answers
395 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
34 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
5 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
76 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
78 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
38 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
66 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
68 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
170 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
37 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
101 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
171 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
108 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
57 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
47 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
59 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
84 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
99 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
35 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
56 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
56 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
50 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
409 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
113 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
50 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
80 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# Mono,...
1
vote
0answers
105 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
50 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
547 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
62 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
64 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
80 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
94 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
21 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
45 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
49 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
68 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
48 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
73 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
60 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 ...
2
votes
1answer
52 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
81 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
0answers
24 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 /usr/sbin/mysqld(my_print_stacktrace+0x35)...
3
votes
0answers
183 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 ...