InnoDB is the main ACID-compliant Storage Engine used in MySQL.

learn more… | top users | synonyms

1
vote
1answer
198 views

What will happen if MySQL cannot write to InnoDB log?

What happens to MySQL if the partition that contains InnoDB's log files (innodb_log_group_home_dir) becomes full, read-only or otherwise unwritable? If this did happen, what recovery process would ...
0
votes
1answer
85 views

Column locking in innodb?

I know this sounds weird, but apparently one of my columns is locked. select * from table where type_id = 1 and updated_at < '2010-03-14' limit 1; select * from table where type_id = 3 and ...
0
votes
1answer
495 views

Mysql- “FLUSH TABLES WITH READ LOCK” started automatically

I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
1
vote
1answer
1k views

Deleting huge chunks of data from mysql innodb

I need to delete a huge chunk of my data in my production database, which runs about 100GB in size. If possible, i would like to minimize my downtime. My selection criteria for deleting is likely to ...
0
votes
1answer
84 views

Should I upgrade memory for my dedicated server?

My memory usage hangs around 25% (swap is generally 1%) on my dedicated server and load is around 2-5. My host recommended that I upgrade from 2GB of ram to 4GB so that I can increase my ...
0
votes
1answer
644 views

InnoDB table locks without apparent reason

I have an InnoDB table for visitors' counting, which has perfectly worked for several years, but it failed twice yesterday, may be cause of the increase of visitors Without apparent reason, this table ...
2
votes
1answer
2k views

Innodb : cannot allocate the memory for the buffer pool

My innodb keeps crashing. This is the error message below. Does anyone know why this keeps happening? InnoDB: by InnoDB 49201616 bytes. Operating system errno: 12 InnoDB: Check if you should increase ...
1
vote
3answers
681 views

Roughly how long should an “ALTER TABLE” take on an 1.3GB INNODB table?

I'm trying to optimize a table that hasn't been optimized in a long time. It has around 2.5 million rows with 1.3 GB of data and a 152 MB index. I started optimizing it about 15 minutes ago and I have ...
3
votes
1answer
1k views

What are the pros & cons of these MySQL engines for OLTP — XtraDB, PBXT, or TokuDB?

I'm working on a social website with an approximate read/write split of 90/10. Trying to decide on a MySQL engine. The ones I'm interested in are: XtraDB PBXT TokuDB What are the pros and cons of ...
4
votes
2answers
3k views

MySQL InnoDB Corruption after power outage, possible to recover?

I recently started trying to get Redmine up and running after a power outage that seems to have corrupted our InnoDB database in MySQL. Redmine had an extensive set of documentation that I would like ...
7
votes
5answers
16k views

How to improve MySQL INSERT and UPDATE performance?

This question can probably be asked on StackOverflow as well, but I'll try here first... Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance ...
1
vote
1answer
295 views

What is the fastest way to optimize a MySQL table?

I've ran many different DELETE queries against many tables in the huge database (hundreds of millions records). What would be faster - to run OPTIMIZE TABLE against each modified table. Or just to ...
2
votes
2answers
1k views

Tuning mysql's query cache

I'm looking into tuning the query cache on mysql on my VPS [a 360MB setup]. When I started, it was set at 32MB, and I was getting a non-trivial amount of qcache_lowmem_prunes. I added a cronjob to run ...
42
votes
1answer
40k views

InnoDB: Error: log file ./ib_logfile0 is of different size

I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine. innodb_buffer_pool_size = 2560M innodb_log_file_size = 256M innodb_log_buffer_size = 8M ...
2
votes
1answer
1k views

InnoDb Overhead?

I just converted several large tables from MyISAM to InnoDB. When I view the tables in phpMyAdmin, they are showing a significant amount of overhead (One table has 6.8GB). Optimizing the tables ...
4
votes
3answers
5k views

How do I set the default table type as innodb in my.cnf?

For mysql...is there a way to set it so that every table that gets created is innodb?
2
votes
1answer
149 views

How do I backup a database to a .sql statement?

How do I backup a database to a single SQL file? After it's backed up, how can I load it/restore the database with InnoDB? It must be InnoDB.
1
vote
4answers
1k views

Which operating systems are used by large social networking companies?

I often see that Apache is used as a web server and also ruby on rails, php and so on is used. So all in all the most large social network companies using unix operating systems. Because I want to ...
5
votes
5answers
6k views

phpMyAdmin showing database is MyISAM but tables are InnoDB

Is it possible for a database to be of one type (MyISAM) and the tables to be of another type (InnoDB) or is this a bug in phpMyAdmin? It is showing exactly that. I tried to verify the database ...
1
vote
4answers
3k views

Moving a mysql innodb data partition from one computer to the other

/etc/mysql/conf.d/myserver: [mysqld] innodb_file_per_table datadir = /elastidb/lib/mysql #log-bin = /elastiblockdb/log/mysql/mysql-bin.log general_log = true log = ...
2
votes
2answers
262 views

Visualizing InnoDB's Foreign Keys

What would be the best way to get a visual representation of a group of MySQL tables' foreign key constraints? (other than drawing them out by hand) Something like this:
1
vote
2answers
322 views

Why does not size of mysql ibdata2 change?

I set the following configuration in MySQL5.0 innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend:max:16000M Though the data increase, size of ibdata2 does not change Why does not size of ...
0
votes
4answers
365 views

MySQL InnoDB unrecoverable crash

Whenever I start MySQL server 5.1.33 or 5.1.40 (developers build) and try to read from one table the server crashes with this message in log files: InnoDB: Error: (1500) Couldn't read the MAX(userFK) ...
0
votes
2answers
757 views

Will dropping AUTO_INCREMENT rebuild table in MySQL?

I've got table with following schema: CREATE TABLE `tblsomething` ( `something_id` int(11) NOT NULL AUTO_INCREMENT, … PRIMARY KEY (`something_id`) ) ENGINE=InnoDB AUTO_INCREMENT=144620955 I ...
2
votes
2answers
108 views

innodb storage options

I'm trying to figure out how to set up the storage for a site that currently requires only one server, but will certainly grow to demand more. I the database is largely innodb tables. I understand ...
16
votes
4answers
5k views

what is the difference between INNODB and MYISAM

I am using MYSQL as my database for the current web project. And i am a newbie to MYSQL. Could any one explain me please what is the difference between INNODB and MYISAM. I hope the answers will help ...
6
votes
5answers
6k views

How to fine-tune our MySQL server?

MySQL is not my thing, yet I need to fine-tune one of our servers. Here are the requirements/specs: The MySQL server has only one significant database We only have one "type" of application ...
3
votes
2answers
3k views

Converting big table from MyISAM to Innodb

I have a table with about 300M rows in MyISAM format I want to convert to Innodb My original goal was to reduce usage by changing the table schema to have simpler indexes. I dumped all the table, ...
3
votes
2answers
351 views

What is the best way to check the status of a MySQL slave that is replicating from a master?

Specifically I have a set of InnoDB tables and we are backing these up into MyISAM tables to provide full text search. The slave DB routinely falls behind the Master. I am looking for advice on ...
4
votes
6answers
5k views

Is there any equivalent of mysqlhotcopy for InnoDB?

Currently I'm using mysqldump to make backups which is slow, but OKish. The big problem is restoring database, which takes a few days. The dump is around 7GB gzipped, so it's not a tiny database, but ...
2
votes
2answers
1k views

MySQL config for drupal

I am running MySQL 5.0.75 on Ubuntu on a dual core machine with 8 gigs of ram. I am running a Drupal 5 and Drupal 6 site of the database. I am sure my MySQL config is not optimal but I am wondering ...
0
votes
1answer
166 views

Strange MySQL table problem

I have copied the /var/lib/mysql/mydbname db to a new machine from a crashed server (booted using a live cd) but now I cannot access the tables within the db... All the tables only have a .frm ...
2
votes
1answer
731 views

What are reasonable values for --throttle on xtrabackup?

Percona's xtrabackup utility has a --throttle option to reduce the IO load of the backup job. The docs say that the value passed is the number of read/write pairs per second. Is 1000/sec an ...
1
vote
4answers
424 views

Altering a column length on a large Innodb table with minimal downtime

I need to change the the length of a column in a MySQL innodb table with 164M rows. Here's the script I want to run: ALTER TABLE SESSION_DECISION CHANGE COLUMN NAME NAME VARCHAR(255); When I tried ...
9
votes
2answers
1k views

What should I know before going live with an InnoDB database?

What things should a server- or DB-admin know and do before making a InnoDB database available. I'm thinking about things like setting correct values for innodb_buffer_pool_size and ...
16
votes
5answers
3k views

How do you choose a MySQL database engine

In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys). Does it always come down to trying both and measuring? Or ...