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
2answers
110 views

MySQLTuner request - high CPU problems

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately. Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day. ...
1
vote
1answer
32 views

“Centralising” a “decentral” design

I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of ...
1
vote
1answer
789 views

mysql deadlock when update some items whose id are selected from another table

I'm sorry, I just can speak a little English. I have two machines(10.10.102.101, 10.10.102.104) which connect to the same mysql datebase. This is a table and deadlock happened on it. mysql> ...
1
vote
0answers
42 views

MySQL InnoDB stop locking when large sql is executed

We face a issue that the InnoDB table is locked, when a sql file that is executed is large than a certain file size. The problem does not appear when the file size is much lower, for example 25MB. ...
1
vote
0answers
55 views

Database with lots of writes to a big table uses less CPU than the same database with less writes?

I'm trying to understand why InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it. I have a table with nearly 5M rows, about 9GB in size. I also ...
1
vote
2answers
2k views

Repair/Recover data from MySQL database

I have a big problem with a MySQL database stored on my raspberry (InnoDB engine). I'm not very expert in this field but I'll try to explain to you the problem. MySQL crash everytime that I try to ...
1
vote
0answers
176 views

Unable to drop table/database when using innodb_force_recovery = 3

I'm attempting to follow several guides to recover a corrupted database, by setting innodb_force_recovery to 3, dumping the database, dropping the database, and then importing it back in. I'm able to ...
1
vote
0answers
66 views

Why does the Select statement stalls when executed directly in sequence?

When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go. If the table is MyIsam, it works ok. If executed [...
1
vote
1answer
507 views

How innodb alter table inplace algorithm inner work?

From the mysql doc , it says : ALTER TABLE operations are processed using one of the following algorithms: COPY: Operations are performed on a copy of the original table, and table data ...
1
vote
0answers
110 views

Unable to create index on InnoDB table (5.5.37)

I've met with the strange trouble with one InnoDB table. Index creation on any column fails with error: Incorrect key file for table 'table', try to repair it Despite this error, this table is ...
1
vote
0answers
412 views

MySQL innodb_row_lock_time is more than wall clock time

We recently started graphing the innodb_row_lock_time status variable in our monitoring system. I noticed something I can't understand on a graph for a busy server: Between 04:55PM and 04:57PM, the ...
1
vote
0answers
789 views

Unable to delete a MySQL InnoDB table

I am using MySQL 5.1.56 I have a DB with about 70 tables and I have an issue with a particular corrupted table e.g. Table_X When I try to access the table mysql> select * from Table_x; ERROR 1105 (...
1
vote
2answers
224 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
1
vote
1answer
58 views

Can insert on tables with FK impact operations on other tables that have FKs to the same table?

Let's assume that we have the following tables on an InnoDB database: user (id_user, name) log (id_log, id_user, info) message (id_message, id_user_from, id_user_to, message) The id_user column on ...
1
vote
0answers
63 views

Never stop growing of MariaDB service memory on Windows

I have this strange problem in a Win7-4GB machine with MariaDB v. 10.1.18 using only InnoDB tables. mysqld.exe service memory grows until the OS error out of memory, it take some hours but at the end ...
1
vote
0answers
12 views

AWS database low Insert/s count

I just started getting into AWS and until now I used my computer as a DB instance(both SSD and Ramdisk). On my computer I get 4.000 to 10.000 inserts/sec of random generated data. I modified the same ...
1
vote
0answers
660 views

Detecting & Preventing Data Loss in MySQL Database (InnoDB)

Around April 2017 I noticed that my database lost records in a few columns of 2 tables, this happened inside 5 of databases my website is using. They are clones just with different data records. This ...
1
vote
0answers
181 views

mysqld slowly eats memory

Summary My company recently deployed mysql innodb cluster as a backend database system using docker. To be exact, the cluster was deployed on Oct 3rd, so it has been almost two months. Recently, ...
1
vote
0answers
158 views

Auto Change MyISAM to Inno DB

I am beginner in web hosting. I have some site which are created by php/codeigniter.. Today All custom sites (created by codeigniter) are showing these msg Error Number: 1286 Unknown storage engine '...
1
vote
0answers
22 views

MySQL deadlocking on single-column table batch INSERT during transaction

I've been trying to work around this but haven't found a way besides retrying, which is inefficient for what should be a fairly simple, high-concurrency operation. I have an InnoDB table on a MySQL 5....
1
vote
0answers
6k views

InnoDB: Waiting for page_cleaner to finish flushing of buffer pool.

I am doing test on a small percona machine on centos 7. Yesterday I tried to restart mysql server but it took too long nearly an hour. So I notice in the log this InnoDB: Waiting for page_cleaner to ...
1
vote
0answers
86 views

Should I add index when indexes are greater than RAM in mysql

Data_length + index_length are greater than innodb_buffer_size, and I have a huge table where the select took longer than 5 minutes. Should I resolve this issue by adding another index on a column, or ...
1
vote
0answers
51 views

mysqldump dies on the same row. Analyze is fine. Timeouts are long

I have an innodb table with about 70M rows. Recently a cron job to back it up with mysqldump started failing at the same row. Mysqlcheck also fails. Mysqlanalyze runs fine and prints OK. Alter ...
1
vote
0answers
737 views

MariaDB memory usage growing

I am using MariaDB 10.2.6 + TokuDB plugin on Debian Stretch. Each storage eninge, InnoDB and TokuDB, is set to a buffer/cache size of 12G. This configuration worked fine with MariaDB 10.1.25 on Debain ...
1
vote
0answers
515 views

Do MySQL locks expire?

Using InnoDB. I've read this article about the InnoDB locking model, but I could not find the answer to this question: Do MySQL locks expire? If a process grabs a lock, will that process still hold ...
1
vote
0answers
472 views

Missing tables from MySQL restore

I'm not a DBA but a JIRA admin. I run a cron backup of my mysql InnoDB JIRA database which runs a script that executes the command below: mysqldump -v --single-transaction --user=DATABASEUSER --...
1
vote
0answers
363 views

Got deadlock for multiple update on same row

In my application, sometimes there will be multiple transactions trying to update the same entry simultaneously. And I got deadlock below. I don't quite understand why. The queries have same where ...
1
vote
0answers
156 views

Why Aren't Deleted Documents Being Pruned When Optimizing InnoDB FullText Tables?

Following the MySQL 5.6 documentation (Example 14.5), I am under the impression that by setting innodb_optimize_fulltext_only=1 and optimizing a table, both the INNODB_FT_INDEX_CACHE and ...
1
vote
2answers
40 views

MySql Freely avaliable dataset

I need MySQL dataset for testing purposes to check and improve performance and optimization. Is there any freely available dataset (MySQL) of 200k rows ??
1
vote
1answer
2k views

What is locking order of MySQL on query with join statement

When I select for update from join multiple tables, matching rows in all joined tables will be locked. But I was wondering what is the locking order, which rows are locked first? Is it determined by ...
1
vote
2answers
317 views

What is the most convenient indexing strategy for two foreign key constraints?

Which of the two following options is more convenient for indexing the relevant foreign keys? Option A: Create Table table3( t3_id int not null auto_increment, t1_id int not null, t2_id int ...
1
vote
0answers
555 views

InnoDB: Cannot open table

Copied to file from (XAMPP) /Applications/XAMPP/xamppfiles/var/mysql to /Applications/AMPPS/var (AMPPS) But I having this error. 2017-01-25 18:01:55 1860 [Warning] InnoDB: Cannot open table ...
1
vote
0answers
11 views

Memory usage when refining temporary table into other smaller temporary table

I'm working on a algorithm to explore different connections between two data sets and I am wondering how smart InnoDB is with memory usage in regards to temporary tables. I have a tree of conditions ...
1
vote
0answers
81 views

Data lost when enabling bi-directional master/slave on MariaDB

I have master/slave setup with two MariaDB 10.x instances. They were bidirectional but for the past two months it was only one direction. When re-enabling the bi-directional it looks as if the master ...
1
vote
1answer
27 views

Use column info as template

I got this wild idea to fix a date format issue i've been having. The idea is to use column info to act as a template for the date format since mm/dd/yy can get really confusing for the database ...
1
vote
1answer
186 views

Small MySQL table swap/replace taking incredibly long time

We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL: RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging ...
1
vote
0answers
20 views

InnoDB: in what scenarios, if any, is it okay to have random insert WRT primary key?

For now let's ignore the benefits of having a surrogate key for business logic reasons. Let's only consider performance (speed and disk usage). CREATE TABLE `person` ( `ssn` bigint(20) unsigned NOT ...
1
vote
1answer
43 views

Fast data loading with duplicate check

We are developing an application that loads data from clients and then processes it in some predictive analytics tasks. Each client has different data structure: some clients will for instance have ...
1
vote
3answers
31 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
1
vote
0answers
84 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
1
vote
0answers
1k views

What exactly is MySQL Spin rounds per wait in SEMAPHORES section?

I am facing slowness issue on MySQL Slave. I am suspecting this due to OS I/O as i compared Spin rounds per wait values with PROD. Slave have higher values. Also slave has less resources compared to ...
1
vote
0answers
48 views

Can MyISAM tables affect the backup recovery of innodb tables on Amazon RDS?

I have an Amazon RDS instance containing several databases. All the tables are innnodb except from one, that is MyISAM and currently cannot be converted to innodb. I know that the recovery of MyISAM ...
1
vote
0answers
201 views

InnoDB: very slow random updates, hanging in “updating” state

I've been fighting my problem for the whole last week, studying stackexchange threads with similar issues in the first place. The main problem is that after switching from MyISAM to InnoDB (...
1
vote
0answers
418 views

Memory leak on MySQL slave?

I have a Percona MySQL master-slave server configuration(5.6.20-68.0). The memory usage by MySQL on the slave server is constantly growing. Now it reached 68G despite the fact that ...
1
vote
0answers
111 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 selects ...
1
vote
0answers
406 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
0answers
112 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. ...
1
vote
0answers
410 views

How to resolve Innodb lock

I am trying to run a sitemap generator job for Magento and here is what I get as Innodb status after the query has failed to complete even after several hours. I do not know much about DBA stuff and ...
1
vote
0answers
1k 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
300 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 ...

1 2
3
4 5
9