Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

436 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
386 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 ...
1
vote
0answers
3k 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) ...
1
vote
0answers
96 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
292 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; which ...
1
vote
0answers
95 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
222 views

Mysql stop working woocommerce

I have a WooCommerce website hosted on an Amazon EC2 centOS server with 4GB RAM and 4GB swap. My problem is that sometimes MySQL stops working due to failure to allocate memory and also the website ...
1
vote
0answers
302 views

How to configure correctly Mysql heavy used server to prevent it from write IO huge spikes?

I have mysql 5.6 server which handles thousands of users online. All tables are Innodb. The problem is, that once or twice a day it writes huge amount of data to disk, or it slows down a lot. Than ...
1
vote
0answers
60 views

Migrated servers… Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
1
vote
0answers
156 views

Mariadb support rtree index on innodb soon?

I was looking through mariadb bug list online (https://mariadb.atlassian.net/secure/RapidBoard.jspa?rapidView=24&projectKey=MDEV), but I am unfamiliar with JIRA so I am not sure exactly what I am ...
1
vote
1answer
3k views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
1
vote
0answers
190 views

Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB. I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should ...
1
vote
0answers
5k views

Why is MySQL not saving inserted data?

So I have the following insert statement that I run through MySQL Workbench: INSERT INTO PeakOnlineCount (time_stamp, LocationId, Count) values ('2015-03-26', 0, 1) If I run the following query: ...
1
vote
0answers
36 views

How “relatively safe” is the data which was salvaged from corrupted MySQL InnoDB database?

I encountered the corruption like the following. 150208 14:27:52 InnoDB: Error: page 443375 log sequence number 8941 870477908 InnoDB: is in the future! Current system log sequence number 5968 ...
1
vote
0answers
122 views

InnoDB Deadlocks Involving Currency Transactions

We're dealing with InnoDB deadlocks that are affecting our virtual transaction system. LATEST DETECTED DEADLOCK 150210 20:51:03 *** (1) TRANSACTION: TRANSACTION 0 4240405649, ACTIVE 0 sec, process no ...
1
vote
1answer
69 views

Is >58 hours to create an index normal? Should I cancel and try again?

I have an InnoDB table with ~43,964,557 rows containing 20.5 GiB of data (not counting existing indexes). mysql> show table status like 'backpack_items'; +----------------+--------+---------+------...
1
vote
0answers
444 views

MySQL InnoDB Configuration with 60Gb of Memory

I'm running a large MySQL Database, in the range of 30Gb in size. The MySQL 5.5 server is running on a dedicated server with 60GB of RAM, with no other processes installed apart from monitoring tools, ...
1
vote
0answers
158 views

25.000+ rows - MySQL SELECT consecutive numbers performance issue

I have tried to be as explicit as I could. Below you have the query explained in details What I want to obtain? - I want to SELECT start_number and end_number from a set of consecutive numbers from ...
1
vote
0answers
23 views

Need advice on optimizing database on a recurring basis

I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the ...
1
vote
0answers
2k views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
1
vote
0answers
87 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
1
vote
0answers
50 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
1
vote
0answers
793 views

mysqldump with --single-transaction seems to be “fixing” InnoDB tables with page corruption - why?

I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest ...
1
vote
2answers
1k views

MySQL: Merge Same Database on Several Location into Single Location

I have a registration form, which is exactly the same for several locations. Each of them has a REG column, which is unique for each inserted row. Basically, each data input has a different REG number ...
1
vote
0answers
113 views

Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

First of all, excuse my English. I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by ...
1
vote
0answers
844 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
1
vote
0answers
344 views

MariaDB Galera Cluster

I have two mariadb servers in cluster that is working galera and xtradb. it is my primary server configuration: [mysqld] datadir=/var/lib/mysql query_cache_size=0 binlog_format=ROW ...
1
vote
0answers
65 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
1
vote
0answers
188 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
1
vote
0answers
910 views

InnoDB: error: space object of table

131212 10:35:18 InnoDB: error: space object of table 'main/wp_m_membership_levels', InnoDB: space id 119 did not exist in memory. Retrying an open. 131212 10:35:18 InnoDB: error: space object of ...
1
vote
0answers
65 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
1
vote
0answers
923 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, `...
1
vote
0answers
299 views

Drupal database doesn't work after MySQL restart

I've installed Drupal 7x on my local XAMPP 1.8.3 Server. Everything went fine but after adding some modules, changing the drupal theme and restarting the MySQL Server, the MySQL Server is dropping ...
1
vote
0answers
863 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
1
vote
0answers
124 views

Any drawback to lots of creating and renaming tables at InnoDB?

I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk ...
1
vote
0answers
396 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses ...
1
vote
0answers
2k views

Tables don't exist after MySQL restart

I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior: I have some tables with data in them I made ...
1
vote
1answer
3k views

mysql can't start, can't dump. Error: page 5 log sequence number

I have an error in MySQL while i am starting the MySQL database. The Error show is as mention below; Aug 11 13:03:28 kutuphane mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql ...
1
vote
1answer
2k views

mysql - Recovering databases from ibdata1 , .frm and .idb

I have a server running a mysql instance which crashed today. Now there are a massive amounts of tables in multiple database exceeding over 200GB stored on disk. After the server crash mysql is ...
1
vote
2answers
47 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
1
vote
1answer
458 views

Increased server load after InnoDB deadlock

I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, ...
0
votes
2answers
1k views

Storing multiple types of users and their respective credentials in separate or same table?

So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design. In ...
0
votes
0answers
20 views

MySQL Dead Lock on Delete Issue

I have recently updated my production code with the new query that is supposed to clean up orphaned records. It runs within the transaction as the last step. And, as a result, I am seeing occasional ...
0
votes
1answer
31 views

Mysqld draining memory, using way more then assigned and not giving any back

This is a production server. Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld). Sometimes ...
0
votes
0answers
11 views

InnoDB Format Row from DYNAMIC to COMPRESSED increase table size (and never decrease back)

I recently decided to make some experiment with compressed table format. Most of the time when I convert a table from DYNAMIC to COMPRESSED I get a disk space saving from 50% to 80%. However yesterday ...
0
votes
0answers
11 views

How to resolve the corrupted innodb table

I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not ...
0
votes
0answers
38 views

Why initializing variable with var = (SELECT .. FROM t) puts an S lock on selected rows, but SELECT .. INTO do not put locks?

How to repeat: -- Create table CREATE TABLE test_lock_s ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , user INT UNSIGNED DEFAULT NULL , PRIMARY KEY (id) ); -- Fill it with some data INSERT INTO ...
0
votes
0answers
24 views

MySQL database is corrupted each time the server is restarted

It has happened to me several times that there has been a power failure of the MySQL server. I know that the tables can be corrupted, but in my case it never happened that the server restarted without ...
0
votes
0answers
23 views

How can I tune my MySQL server to bulk import XML file (42 GB)?

I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time ...
0
votes
0answers
10 views

MySQL 5.7 32-bit table is full on unix

As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that ...
0
votes
0answers
9 views

Truncating partition locking whole table in mysql

Novice here. I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is ...

1 2 3
4
5
9