InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
35 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
4
votes
4answers
2k views

Prevent reset of auto_increment id in Innodb database after server restart

I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused. Normally, this isn't a ...
1
vote
1answer
49 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
2
votes
2answers
33 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
1
vote
1answer
67 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
1
vote
1answer
558 views

How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
15
votes
3answers
23k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
3
votes
1answer
31 views

Is the innodb_lazy_drop_table problem solved?

I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to ...
1
vote
3answers
178 views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
0
votes
2answers
71 views

Recovering mysql innodb table from table.ibd backup file

Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server). Now for a scenario, where some how a table has been ...
1
vote
2answers
67 views

Transaction basics. What is the result of 2 transactions running concurrently?

I have a rather basic question on transactions. Assume a table Account with a column amount. Now we have the following code: BEGIN $amount = SELECT amount FROM ACCOUNT WHERE id = 123; $amount = ...
1
vote
2answers
92 views

What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

We have few thousand small databases with total .ibd files of about 800000. We noticed that MySQL takes couple of minutes to stop but about two hours to start. For e.g. while MySQL is starting and ...
0
votes
2answers
764 views

Percona Xtradb Cluster : How to speed up insert?

I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 ...
1
vote
3answers
674 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
2
votes
1answer
255 views

Why does auto increment jumps by more than the number of rows inserted?

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure: INSERT INTO Bids ...
1
vote
3answers
180 views

What does “InnoDB stores its tables and indexes in a tablespace” mean? [duplicate]

Was reading the mysql engine innoDB tutorial, I came across this sentence "InnoDB stores its tables and indexes in a tablespace" Can any body explain me this what does it means. Thank you :)
1
vote
0answers
78 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 ...
0
votes
1answer
58 views

Is there a table size above which SELECT performance degrades significantly?

In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, ...
11
votes
3answers
4k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
0
votes
1answer
51 views

The most efficient method to obtain the total entries into a mysql table

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but ...
1
vote
1answer
431 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
0
votes
1answer
29 views

Check whether MySQL Innodb Rows are compressed

After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?
0
votes
3answers
324 views

MySQL InnoDB Sorting Issue

I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation: "All indexes other than the clustered ...
-1
votes
1answer
17 views

Mysql InnoDB exsited but can't show it

I have a InnoDB table A , While I type "show tables" , I can't see it. Even I type "drop table A" , it responses "table doesn't exist" . While I create a table B which refers from A , table B can ...
0
votes
1answer
125 views

Error 1005: Can't create table can't find where the problem is

I have created a EER model using MySQL Workbench (latest release). Apparently all is fine but when I try to do a FORWARD ENGINEER I get the common Error 1005: Can't create table. As suggested in this ...
0
votes
1answer
4k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
1
vote
1answer
544 views

Compressing Base64 Encoded Image in Database Field. Table Size too Large

Database Info Database Type - Percona MySQL. Table engine - InnoDB. I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 ...
1
vote
1answer
54 views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
19
votes
3answers
24k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
1
vote
2answers
410 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
1answer
96 views

Error while importing sql - Specified key was too long; max key length is 1000 bytes

I am trying to import an sql file which shows me the following error: Error SQL query: -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE ...
1
vote
1answer
155 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
0
votes
0answers
39 views

Recovering Database from server

my server has some issues and isn't able to start the mysql service. I still have access to the server via shell. So I can get all files I want to have. So I found this guide ...
2
votes
1answer
95 views

row locking within ACID transaction innodb

I have a financial transaction that looks something like this (pseudo): begin select record for update update record(same record as selected) insert another record into another table ...
1
vote
1answer
32 views

mysql multiple database prioritize innodb buffer pool size

With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?
0
votes
0answers
22 views

row locking with innodb [duplicate]

What is the most efficient way to lock a specific row for within a mysql transaction. I am currently using for update using select is there another method that im unaware of? if not are mysql locks ...
2
votes
1answer
14k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
1
vote
1answer
604 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
0
votes
0answers
23 views

Temporary table created on disk, with 14mb of database

i have only one database (on one machine) with size 14 mb for temporary table i have this configration max_heap_table_size= 1000M tmp_table_size= 1000M sort_buffer_size=4M join_buffer_size=4M i ...
1
vote
1answer
92 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
0answers
46 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 ...
3
votes
4answers
225 views

innodb DEPENDENT SUBQUERY very slow

Here is some innodb table. It insert,update,query very frequently. tn is product id col is product sub id tindex is product description.(store by each word) date is product add date stored by ...
2
votes
1answer
1k views

How I prevent deadlock occurrence in my application?

I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database. All the tables in the database have innodb engine. I also created indexes on each tables. Now I am ...
1
vote
2answers
74 views

How to interpret the information spit out by innochecksum?

MySQL is using up a lot of the disk space. Running du -sh on the data directory shows a humongous ibdata1 file (around 1.7 terabytes). I found a very helpful article online and ran a modified version ...
1
vote
1answer
86 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
1
vote
1answer
362 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
0
votes
1answer
74 views

I have delete database from mysql but storage is not freed

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage? I have a mysql database that the total ...
5
votes
1answer
831 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
194 views

Copying MySQL tables, InnoDB tables not recognized

I have to set up a database copy on my local computer. The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI). I created the database by creating a folder in ...
0
votes
0answers
145 views

MySQL Innodb Concurrent Inserts and Selects - can't use multiple threads on FreeBSD

We have an application that needs to be dumping rows into some tables at a much faster rate than it currently is. It is a dual processor xeon DB server with 48gb of ram and a 4 TB disk RAID 10 (for a ...