InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
37 views

MySQL: Point in Time query using InnoDB log sequence number?

Is it possible to do a point in time query based on a log sequence number? For example, I would like to do something similar to Oracle's "as of" query select foo from bar as of scn 123923455; ...
2
votes
1answer
76 views

Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

I am working with version 10.0.19-MariaDB-1~trusty-log and I'm only able to restart mysqld with innodb_force_recovery=6 and I don't know why. The output of the /usr/sbin/mysqld is the following: ...
98
votes
7answers
99k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
0
votes
1answer
16 views

MySQL InnoDB Error [duplicate]

I'm currently experiencing issues with my server MySQL databases as I keep receiving the following error: InnoDB: The log sequence in ibdata files is higher InnoDB: than the log sequence number in ...
0
votes
1answer
51 views

MySQL: equivalent of Oracle's SCN and “as of” queries?

Does MySQL have a similar concept to Oracle "SCN" (system change number)? Is there an equivalent way to do "as of" queries? e.g. select foo from bar as of SCN 104086584;
0
votes
1answer
47 views

mysql innodb db full text search FTS_DOC_ID column

I am adding full text search to already existing mysql tables that have primary auto incrementing key of id integer. When I add the full text index I get a warning: /*!50604 ALTER TABLE ...
0
votes
0answers
36 views

Is it safe to run parallel innodb single-transaction of individual tables

I can't seem to find the internals for --single-transaction mysqldump option for InnoDB tables, but is it possible to use --single-transaction for single tables in a parallel dump so that the ...
0
votes
1answer
50 views

Mysql is shutting down in every 5 minutes

My MySQL 5.6 is shutting down about in every 3-5 minutes. I only can use it after restart. I don't know what happened; everything was OK 2 days ago. What do you suggest to me? Here is my ERR file ...
0
votes
2answers
71 views

Does MySQL Workbench lock database during migration process?

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read ...
2
votes
3answers
980 views

How long can Deadlock info hold in innodb status page?

When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp ------------------------ LATEST DETECTED DEADLOCK ------------------------ 121118 17:14:31 Questions Any idea how long Deadlock ...
3
votes
1answer
123 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
0
votes
0answers
37 views

What would cause database CPU load to increase 5x+ during peak hours, when traffic only doubles?

I'll start with a screenshot of CPU load on my database server for the past three days. http://i.imgur.com/sSZJdCb.jpg As you can see, it spikes from 20% to 100% load every day (would be even higher ...
0
votes
0answers
48 views

Memory usage still growing

I have problem with my production mysql server. The memory usage is still growing and I don't know why. Trouble started when we changed the server. My mysql version: 5.5.44-0+deb8u1-log - (Debian). ...
10
votes
2answers
18k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
0
votes
0answers
11 views

Memcached innodb plugin load testing

I am testing with memcached innodb plugin for MYSQL and so far I have been able to create containers and insert data. This works well for me. I only have one issue and that is to load test/stress ...
2
votes
1answer
82 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 ...
0
votes
1answer
518 views

innodb_buffer_pool & innodb_buffer_pool_instances [duplicate]

I have a database dedicated server with one quad-core cpu and 48GB Memory. Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system. At First, to ...
0
votes
1answer
50 views

Why does STRAIGHT_JOIN consume more CPU?

Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea? When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, ...
1
vote
3answers
776 views

MySQL High Availability for InnoDB

I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine. I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over ...
2
votes
3answers
194 views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
0
votes
3answers
501 views

Lock mysql table to prevent select and insert at the same time

I know this question has been asked earlier and I have also read the documentation. But I guess I'm stupid not understand. I have a code that inserts a row and the row should be unique in several ...
1
vote
2answers
28 views

MySQL Drive Storage Allocation

In our production servers for SQL Server, we usually split our drives by Software, Logs, Data, and Backups. I've been assigned to help with a MySQL installation (On Windows Server) and I'm not as ...
0
votes
1answer
92 views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
1
vote
2answers
71 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...
0
votes
0answers
41 views

MySQL database hangs but less CPU

I create an application for 30 concurrent users using C# and InnoDB of MySQL (mysql-5.1.35). The size of database is more than 100M lines (~20GB). Most of data is come from 7 active table (2M ...
0
votes
1answer
53 views

How does lock works when delete rows through secondary index in InnoDB?

step1: InnoDB Transaction Isolation Level Repeated Read. step2: Table schema CREATE TABLE `follows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `followable_type` varchar(255) DEFAULT NULL, ...
0
votes
2answers
45 views

MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa?

I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance. Which MySQL command/commands will do it for all of the tables instead of doing manually one by ...
3
votes
2answers
42 views

innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

I use the default settings of MySQL 5.5 on various servers and I have the following table: create table test._perf ( name varchar(255) not null ) engine=InnoDB; I have an .sql script which ...
12
votes
4answers
16k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
4
votes
1answer
188 views

MySQL Write performance bottleneck

We are trying to diagnose a MySQL Bottleneck, specifically for data writes. Basically, we have a single process that picks files from a folder and load into a MySQL table X using LOAD DATA CONCURRENT ...
1
vote
1answer
64 views

LAST_INSERT_ID without AUTO_INCREMENT

I have following table, which does have an UUID as primary key, which also acts as foreign key. CREATE TABLE `xxx` ( `guid` char(36) NOT NULL, .... PRIMARY KEY (`guid`) ) ENGINE=InnoDB ...
1
vote
1answer
68 views

How to stop Mysql ibd files from continuously growing

I have a mysql 5.5.44-0ubuntu0.14.04.1-log DB that has 2 tables that get a lot of data written to them then deleted within 48 hours, it is for a Salt system. The my.cnf is specifying ...
0
votes
1answer
21 views

Columnar & Row Oriented DB

In a previous question, I discussed some challenges with a row-oriented table. I have two tables in this application. The first table is well-suited for row-oriented operation; the second is ...
0
votes
1answer
20 views

Get Master Server Information from Slave instance

We need to get Master server information such as server IP and Port, we know this information is dumped into master.info file by default but what if it required within MySQL prompt as we are using ...
0
votes
1answer
353 views

Lowering MySQL Memory Usage without Restart

We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...
2
votes
2answers
92 views

Automatically set timestamps for “create” and “modified”?

I have currently following SQL table: CREATE TABLE IF NOT EXISTS `xyz` ( `id` bigint(20) NOT NULL, `guid` char(36) NOT NULL, `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
1
vote
0answers
34 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
1
vote
1answer
52 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
0
votes
1answer
28 views

MySQL 100+ instances on one Windows server

Please don't ask why I need 100+ instances on one server because I cannot say; suffice to say that I'm not impressed. They will be very small databases and not definitely not under any great load ...
3
votes
2answers
69 views

Moving an ibd file temporarily to free up disk space

I want to optimize an InnoDB table but I do not have enough disk space. I have enabled innodb_file_per_table, so each table has *.ibd for each of their partitions. For example: 512MB ...
6
votes
2answers
170 views

Where does InnoDB store transaction data before committing it?

I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology. I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not ...
5
votes
2answers
151 views

MySQL InnoDB over a Raw Device in an Active/Passive topology

We have an Active/Passive topology where there are two x86 complexes with a shared raw storage, where only one of the nodes in a given moment has an access to the shared storage (AKA the active node). ...
4
votes
4answers
15k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
1
vote
1answer
81 views

How do flush innodb table, copy files, then unlock tables from Windows bat file?

I can successfully execute "FLUSH TABLES myTable FOR EXPORT;" in MySql Workbench. Then I can copy the cfg and ibd files I need, and then I can go back to workbench and run "UNLOCK TABLES" and all is ...
29
votes
1answer
39k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
0
votes
0answers
25 views

Replication of MySQL databases, but only some of the data

I need to set up a system to do basically a partial backup/replication of a couple of MySQL databases (actually Percona XtraDB 5.5, all tables are InnoDB). There are a few hundred tables in one of ...
3
votes
1answer
199 views

Configuring MySQL for Power Failure

I have an embedded device running a MySQL database with a web based application. The problem is when I shutdown my embedded device it just cuts off the power, and I can not have a controlled ...
0
votes
1answer
60 views

MySQL keep crashing over and over on reading ibd files

My MySQL instance keeps crashing over and over again, I am running it with innodb_force_recovery = 3 innodb_purge_threads=0 I have tried it with force_recovery = 1 as well. It has happened before but ...
1
vote
1answer
269 views

Corrupted InnoDB table, mysqlcheck and mysqldump crash server

I manage a Percona XtraDB Cluster that uses a network storage with a flapping connection. Periodically we experience a high iowait with crashes and remount of the fs in read-only. Replacing the ...
0
votes
1answer
2k views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...