InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
2answers
136 views

Buffer pool above 90% utilized causes MySQL to crash

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner? EDIT I'm using Rolando's script to identify how full the innodb_buffer_pool is. SELECT ...
1
vote
1answer
186 views

Why does IN (subquery) perform bad when = (subquery) is blazing fast?

Have a look at these two queries: Simple: mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput = (SELECT DISTINCT lastoutput FROM omgenvelope WHERE ...
1
vote
1answer
412 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
1
vote
1answer
43 views

InnoDB buffer pool active pages

I'd like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW ...
2
votes
0answers
361 views

Recover innodb database from ibdata1 and *.frm files only

I have a problem on my server. I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started. I checked all mysql data, ...
1
vote
1answer
284 views

Optimizing a large number of INSERT … SELECT statements

I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT statement from the other to get the corresponding foreign ...
0
votes
1answer
54 views

Consistent backup of large DB --single-transaction

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication. Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the ...
1
vote
1answer
483 views

InnoDB engine is not loading after tuning parameters

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below ...
2
votes
1answer
2k views

Deleting & Reclaiming space from InnoDB table

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete ...
0
votes
1answer
308 views

Recovering from removal of ibdata1 [duplicate]

I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the ...
0
votes
0answers
26 views

Database cache problem

I have server based on Linux 64 Bit centos 6.5 and Mysql innoDB engine I created database for php script require database installation , after i successfully finished installation for first time i ...
1
vote
2answers
1k views

Two MySQL Server accessing same database over NFS

I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following: ...
1
vote
1answer
332 views

InnoDB primary key efficiency

I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused. Given a simple many-to-many table with two fields, storeId and zoneId, ...
1
vote
1answer
59 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
2answers
48 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?
2
votes
1answer
768 views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
3answers
192 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
1
vote
3answers
2k views

How to decide whether to enable MySQL query cache using InnoDB

How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of: SHOW ...
0
votes
1answer
210 views

MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

We have a 3 node MariaDB Galera cluster. All nodes are identical and the config on each is identical. They have 64GB of RAM each. The output of the following query is 28: SELECT ...
2
votes
2answers
1k views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
-1
votes
1answer
109 views

How does InnoDB store tables in ibdata file? [closed]

There are two tables and their default engine is InnoDB. I want know the following What's inside the ibdata file ? How does InnoDB store these two tables ?
0
votes
1answer
207 views

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

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
1
vote
0answers
446 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
1
vote
0answers
61 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
1answer
323 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
0
votes
2answers
142 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
1
vote
0answers
171 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. ...
2
votes
3answers
5k views

How to repair, or drop/create a corrupted table in mysql?

Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP ...
2
votes
2answers
204 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
0
votes
1answer
86 views

Restore InnoDB from .frm files

My database crashed and my PC and I now only have the .frm and .ibd files left. Is there any way to restore my database?
0
votes
1answer
826 views

Delete Query takes a long time

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. ...
1
vote
1answer
150 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
3
votes
1answer
1k views

MySQL not releasing memory

MySQL seems to want to keep an entire table in cache (table size = ~20GB) after any large inserts or select statements are performed on it. Right now my innodb buffer pool is 20GB. Total RAM is 32GB. ...
1
vote
1answer
268 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
0
votes
1answer
175 views

Slow queries with many self joins in variable-column database

I'm creating a table structure which needs to handle a variable number of columns. To handle that case the table structure is a listing of cells with a column and row number. To view the table a ...
4
votes
0answers
285 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
0
votes
2answers
273 views

Can CPU cores be separated for MySQL?

Is it possible to separate CPU cores in Mysql? I understand it is possible for Memory as we can tune it in innodb_buffer_pool_size or innodb_additional_mem_pool_size. How about CPU cores in ...
0
votes
1answer
51 views

what is the best configuration for innodb_flush_log_at_trx_commit?

There are 3 different configuration is available for innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit = 0 innodb_flush_log_at_trx_commit = 1 innodb_flush_log_at_trx_commit = 2 Which ...
1
vote
1answer
294 views

MySQL 5.1: Upgrade innodb plugin

My application is running on MySQL 5.1.58 community edition. As I studied on SO websites that After acquisition by Oracle, new InnoDB plugin has been released and MySQL 5.1 with InnoDB plugin is the ...
0
votes
0answers
169 views

MariaDB : Larger innodb_buffer_pool_size causing SQL updates to be slower

Has anyone here encountered such scenario before where and update statement took longer time to run when innodb_buffer_pool_size is bigger. I've run a few tests using the same statements on around ...
1
vote
1answer
97 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 ...
1
vote
1answer
159 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
39 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 * ...
3
votes
1answer
217 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
430 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
4
votes
5answers
804 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
3
votes
1answer
175 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 ...
0
votes
2answers
413 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 ...
3
votes
3answers
2k 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 ...
2
votes
2answers
143 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 = ...