InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
2answers
191 views

MySQL query INSERT INTO … failed - The Table “x” is full

I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out. Environment: Webmin/Virtualmin | MySQL version 5.1.73 on ...
2
votes
2answers
1k views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
1
vote
2answers
77 views

DB randomly locks up when innodb_buffer_pool_size is set high

I have been struggling to work out what could be happening here but for many months, since upgrading to mysql 5.6 we seem to be experiencing random lag spikes in page loads which has been tracked back ...
1
vote
1answer
295 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
5
votes
5answers
15k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
2
votes
2answers
29 views

MySQL Database Archive

We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I we are planning archive inactive database.Is there any ...
0
votes
1answer
15 views

First Insert is skipped by the trigger

I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit. The issue is First insert is somehow ...
0
votes
0answers
41 views

How to minimize the InnoDB page evictions in a MySQL full in-memory db?

I have a MySQL (Percona 5.6) database, on a server which has enough memory to contain the entire dataset. Specifically: Total memory allocated 98901688320 Innodb_buffer_pool_bytes_data 79246950400 ...
3
votes
1answer
26 views

How we can recover MySQL database (Innodb,myisam)

I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows. Transaction ID based recovery Cancel based, Change ...
2
votes
1answer
235 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
1answer
45 views

How to ORDER a big table?

I have a simple but big table (tens of millions of rows). The performance is quite good except when I need to order the table by the column with format datetime. A simple SELECT query to fetch the ...
0
votes
0answers
24 views

After recovering full backup with innobackupex, innodb system tablespace appears to be corrupt

I've a full backup of a database. From time to time, I recover those full backups on a recovery machine. This process has worked fine always, until today. The backup went OK, zero warnings, nor ...
3
votes
1answer
37 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 ...
7
votes
2answers
10k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
1
vote
2answers
417 views

ERROR Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

We are facing the issue in replication that we are not getting any error in Show slave status\G output rather facing the following issue in Error Log File [ERROR] Error reading packet from server: ...
2
votes
2answers
39 views

Mysql hand machine due to high load average and CPU

Mysql is used by only one application which only fire query periodically. When i restart server,It run smooth for period of time. and then crash the whole server. My my.cnf file [mysqld] ...
3
votes
4answers
234 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
4
votes
2answers
56 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
15
votes
3answers
6k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
2
votes
2answers
44 views

what is the difference between UPDATE … LIMIT and SELECT … FOR UPDATE?

I have read an article, there is one sentence about MySql: As one might expect, lock contention is an issue and we use several strategies to mitigate it including a separate table for each ...
1
vote
1answer
72 views

Slow MySQL InnoDB performance of table with 100 million rows

I have the database in this format. CREATE TABLE `dna` ( `p_id` bigint(20) NOT NULL AUTO_INCREMENT, `dna_bits` bit(32) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`p_id`), KEY `dna_bits` ...
0
votes
0answers
34 views

Simple InnoDB update query locks transaction - why?

I have the following lines in my error log (often repeating after a recent code update): *** (1) TRANSACTION: TRANSACTION 2959776709, ACTIVE 0 sec starting index read mysql tables in use 1, locked ...
1
vote
1answer
38 views

How to make a unique datetime without seconds?

I need to build a table accepting one row for each minute. If I set the type of primary key to datetime, 60 records can be added for each minute. Is there any trick to design a table to keep ...
1
vote
1answer
27 views

Theory of indexes for query with joins

I found the following talk by Zardosht Kasheff enlightening: http://www.infoq.com/presentations/Index-SQL but it only covers the case of simple queries on a single table. Is there something similar ...
8
votes
3answers
11k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
0
votes
1answer
42 views

Restored database copy larger than original

I've created a copy of a database for testing. It's on the same server as the original. However, tables are listed as being larger on the restored copy than on the original. My original database is ...
0
votes
0answers
60 views

“mysqld got signal 11” error

I have a MySQL 5.6.22 InnoDB database and just now, suddenly out of nowhere, when I try to start it up using /etc/init.d/mysqld start, I see this error in the error log and the server won't start up : ...
2
votes
1answer
75 views

Is it ok to have 'view count' column in the same table?

Let's say I have a website similar to YouTube where I'm storing and displaying the 'view counts' for each video. It is ok if I'm storing the view count column in the same table as the actual view ...
2
votes
3answers
128 views

UPDATE query periodically is slower by factor of 5000x

I run a site (Magento) which have a problem with a specific UPDATE query. Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 ...
0
votes
2answers
3k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
0
votes
1answer
58 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
1
vote
2answers
3k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
6
votes
4answers
424 views

Mysql (percona-server 5.5) completely stalls during import (myloader)

While configuring our new servers (IBM X3650M4, 2x Intel Xeon E5-2620, 64G RAM, RAID 10 (4x 240G SSD)) we bumped into a strange problem. We're trying to import dumps (mydumper) from our (old) ...
0
votes
2answers
86 views

slow query on mysql innodb table with 2 million rows

I have a table with about 2 million rows (growing at about 1 million every 3 months) and am having trouble with slow queries. I've tried changing my query and indexes (ensuring left most order) to no ...
0
votes
2answers
78 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 ...
12
votes
5answers
22k views

Cannot open table mysql/innodb_index_stats [duplicate]

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
0
votes
1answer
122 views

innodb fulltext ibdata1 size

I am running mariadb 10 and have a large innodb table with a full text index. I have innodb_file_per_table=ON I've noticed though that the ibdata1 file grows at the same rate as my table file. I ...
2
votes
1answer
463 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
0
votes
0answers
35 views

My DB restarts again and again

MySQL keeps restarting and logs keep giving this errors InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd ...
1
vote
5answers
24k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
1
vote
0answers
41 views

Moving to innodb_file_per_table?

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file! As I understand I can reduce its size by moving to ...
0
votes
0answers
66 views

How to find the total disk writes in MySQL

I'm trying to find the total number of disk writes in MySQL. I used the following command in the MySQL terminal. >SHOW GLOBAL STATUS; I got a list of different attributes status and few which ...
2
votes
1answer
147 views

MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

In MySQL documentation it's written: If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. Why ...
0
votes
2answers
93 views

How to check innodb history list length?

The output for show engine innodb status has the history list length as a number, but that output is hard to parse, and there's just too much data when all you want is a single value. Is the value ...
0
votes
1answer
32 views

Why does mysql make delete offset?

I am implementing database(College project).So I come across innodb diagrams in github ...
0
votes
0answers
334 views

MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

I have a table area which contains ~300,000 rows. It is defined as follows: CREATE TABLE `area` ( `area_id` bigint(20) NOT NULL AUTO_INCREMENT, `boundary` geometry NOT NULL, `name` varchar(255) ...
0
votes
0answers
42 views

Our app lags “randomly” due to Disk I/O spikes?

Our database (InnobDb) is spiking at 99% I/O at random times. After the spike it goes down to about 2% until it spikes agian. There aren't any spesefic, huge queries running in these intervals that ...
1
vote
0answers
46 views

Optimizing a group by/where in/min() query in MySQL

Assume I have a table that looks like such (The actual column names of my real table would only make things harder to explain, so I have created a mimic that behaves similarly). id | cond1 | cond2 ...
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: ...