InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
118 views

how to recover everything from ibdata in mysql

I lost all my databases files after my hard disk was broken, In a way or another I got ibdata files the size for the file is 4GB.After replacing log files and files and type innodb_force_recovery=6 in ...
1
vote
1answer
176 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
0
votes
1answer
16 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 ...
2
votes
2answers
32 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
52 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 ...
3
votes
1answer
62 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 ...
0
votes
0answers
92 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 ...
2
votes
1answer
135 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 ...
3
votes
2answers
76 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 ...
3
votes
2answers
193 views

Release MySQL Memory and Performance Tunning

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file. The server is a dedicated server for MySQL database and the ...
0
votes
0answers
71 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
28 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 ...
0
votes
1answer
52 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
133 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 : ...
1
vote
1answer
52 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 0000-00-...
0
votes
0answers
11 views

Mtop - How to reset ID numbers?

Using Mtop for slow queries logging in our InnoDB. Since there is actually high database usage, the log IDs in front when using the mtop command are increasing rapidly and I would like to reset them, ...
1
vote
1answer
132 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
46 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 ...
2
votes
2answers
81 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 ...
2
votes
2answers
58 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] datadir=/...
1
vote
0answers
47 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
172 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 ...
0
votes
1answer
34 views

Why does mysql make delete offset?

I am implementing database(College project).So I come across innodb diagrams in github https://github.com/jeremycole/innodb_diagrams/blob/master/images/InnoDB_Structures/B%2BTree%20Record%20Delete%202....
0
votes
0answers
1k 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
47 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
55 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 ...
0
votes
2answers
311 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 ...
0
votes
1answer
55 views

Mysql only using 10% of cache [duplicate]

I have a dedicated Mysql server that only uses 1.1 GB out of 16 GB ram (picture link 1). I belive this leads to my Disk I/O utilization beeing at 75% peaks (picture link1). How can i configure sql ...
2
votes
1answer
2k views

MySQL InnoDB page_cleaner settings might not be optimal

Seeing this note in mysqld.log: [Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.) There seems to be ...
1
vote
1answer
47 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; ...
6
votes
4answers
851 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
222 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 ...
2
votes
1answer
196 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: ...
1
vote
1answer
470 views

Mysql Tuning for a small server

I have a small Cloud Server running under Ubuntu 2 core, 2GB ram and 2GB swap, with Plesk Panel, Apache, Nginx as reverse proxy and Mysql 5.5. I have about 18 domains hosted here and each domain has ...
1
vote
1answer
60 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 ...
0
votes
0answers
112 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
42 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 ...
3
votes
1answer
827 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ---...
0
votes
1answer
97 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
43 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 --single-...
1
vote
2answers
131 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 ...
0
votes
1answer
104 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
1answer
780 views

mariadb won't start (InnoDB error: space header page consists of zero bytes in data file ./ibdata1)

The following messages are in /var/log/mariadb/mariadb.log when I try to start mariadb: 151103 13:42:01 [Note] InnoDB: Initializing buffer pool, size = 128.0M 151103 13:42:01 [Note] InnoDB: Completed ...
0
votes
2answers
132 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
142 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 ...
2
votes
1answer
143 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 ...
0
votes
0answers
47 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
78 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). [...
0
votes
0answers
14 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 ...
0
votes
1answer
60 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, ...