InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

4
votes
2answers
6k views

MySql - changing innodb_file_per_table for a live db

I have a large MySql DB (150GB) and only now i've noticed that the innodb_file_per_table is set to off which cause the entire DB to be hosted on one single file (ibdata1). i want to active ...
3
votes
5answers
10k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
2
votes
1answer
1k views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
2
votes
1answer
3k views

MariaDB XtraDB Performance Tuning

I believe that innodb has a single read thread. However, with MariaDB you can set multiple read threads. I'm unsure what the recommended value would be. Questions Does anyone have any experience ...
7
votes
2answers
18k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
7
votes
2answers
5k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
7
votes
2answers
12k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
4
votes
2answers
1k views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
2
votes
1answer
310 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. ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
2answers
935 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
10
votes
2answers
170 views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
7
votes
1answer
4k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
7
votes
2answers
957 views

Investigate peak in MySQL throughput

Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
5
votes
1answer
757 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
5
votes
1answer
2k views

Find out MySQL index size for a concrete index

In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ? I know I can select the index_length from information_schema.tables, but this gives the total ...
4
votes
2answers
325 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
4
votes
3answers
3k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
4
votes
3answers
616 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
3
votes
2answers
608 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
3
votes
1answer
3k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
3
votes
1answer
750 views

InnoDB “corruption”

I use: 5.1.63-0ubuntu0.11.10.1 Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.63 The system is continuously displaying the following error during reading data from a MySQL database: ...
3
votes
1answer
3k views

How can I find who holds the lock based on the hex-dumped?

I've been reading the Diagnosing MySQL InnoDB Locks article. Karl E. Jørgensen writes on 2008 so I'm consfusing it is in effect. I would like to supply a snippet of the SHOW ENGINE INNODB STATUS: ...
3
votes
2answers
652 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
2
votes
2answers
152 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
2
votes
2answers
3k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
2
votes
2answers
6k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
2
votes
2answers
3k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
2
votes
1answer
361 views

Drupal 7 Uses InnoDB, so do I need other configs?

If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff ...
2
votes
1answer
2k 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 ...
2
votes
2answers
5k views

Dynamic change to innodb_flush_log_at_trx_commit

This is related to this question. It does help to get better performance for InnoDB tables. According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change ...
2
votes
1answer
3k views

What exactly are iblog files in mysql

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
2
votes
1answer
2k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
2
votes
1answer
2k views

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all ...
1
vote
1answer
674 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 ...
1
vote
1answer
536 views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
1
vote
1answer
929 views

Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
1
vote
1answer
2k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
1
vote
1answer
296 views

Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
1
vote
2answers
3k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
1
vote
2answers
2k views

Similar function NOWAIT in MySQL

Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
0
votes
0answers
39 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
2answers
2k views

How to optimize MySQL for large BLOB updates

I have a table that holds large BLOBs (up to 10M), and is updated frequently. The problem is that UPDATE statements can take up to 1 second to execute, and due to app design this blocks UI. I need to ...
5
votes
1answer
2k views

Slow InnoDB INSERT/UPDATE/DELETE

Background A couple of weeks ago all of my INSERT's/UPDATE's/DELETE's became overall slow (not SELECT's). Before that, everything was normal (mysql statements executed in <0,01 seconds). Now a ...
5
votes
1answer
3k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
5
votes
1answer
445 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
4
votes
4answers
965 views

Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
3
votes
4answers
127 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
3
votes
1answer
385 views

Speeding up a NOT IN subquery

Good evening, I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery ...
3
votes
2answers
4k views

Finding and fixing InnoDB index corruption

I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a ...