Skip to main content

Questions tagged [innodb]

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

Filter by
Sorted by
Tagged with
110 votes
1 answer
158k 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 ...
jack's user avatar
  • 1,725
71 votes
9 answers
211k views

Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it: ╰$ sudo service mysql restart stop: Unknown ...
Garrett's user avatar
  • 843
38 votes
6 answers
29k views

MySQL InnoDB - innodb_file_per_table cons?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table. I am ...
UpTheCreek's user avatar
  • 1,638
28 votes
3 answers
137k views

Mysql crashed and won't start up

Our production mysql server just crashed and won't come back up. It's giving a segfault error. I tried a reboot, and just don't know what else to try. Here is the stacktrace: 140502 14:13:05 [Note] ...
tilleryj's user avatar
  • 383
22 votes
2 answers
69k views

Best MySQL cache settings for 8gb RAM dedicated MySQL server using only InnoDB (5gb database)

I'm a pretty big noob when it comes to setting up MySQL for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of MySQL, but I do know that ...
billmalarky's user avatar
19 votes
6 answers
8k views

Backing up a MySQL database via ZFS snapshots

I've found a number of sites talking about doing exactly this, but I'm missing a few important details. The general steps are Run FLUSH TABLES WITH READ LOCK Take the ZFS snapshot Run UNLOCK TABLES ...
Andy Shulman's user avatar
19 votes
5 answers
33k views

Modifying columns of very large mysql tables with little or no downtime

I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... ...
apptree's user avatar
  • 365
17 votes
6 answers
59k 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 ...
mmattax's user avatar
  • 1,324
17 votes
5 answers
10k views

What is the difference between InnoDB and MyISAM?

I am using MySQL as my database for my current web project. I am new to MySQL. Please explain to me the difference between InnoDB and MyISAM.
user avatar
16 votes
4 answers
53k views

How to know storage engine used of a database?

Previously, on every database created, I use: mysql -u root -p CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin; GRANT ALL ON dbname.* TO 'dbuser'@'localhost'; and then use the database ...
cewebugil's user avatar
  • 737
16 votes
5 answers
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 ...
Tony Meyer's user avatar
14 votes
1 answer
19k views

How to disable keys in MySQL InnoDB tables?

Is it possible to disable keys in InnoDB tables? If yes, how? If not, why?
user12145's user avatar
  • 1,125
13 votes
4 answers
39k views

Simpler way to convert all tables from InnoDB to MyISAM

Previously, I use this: USE dbname; ALTER TABLE tablename ENGINE=MYISAM; I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
cewebugil's user avatar
  • 737
13 votes
7 answers
62k views

Setting a time limit for a transaction in MySQL/InnoDB

This sprang from this related question, where I wanted to know how to force two transactions to occur sequentially in a trivial case (where both are operating on only a single row). I got an answer—...
Trevor Burnham's user avatar
12 votes
4 answers
46k views

Increase InnoDB buffer pool size

I am having difficulty setting the buffer pool size and log file size for MySql InnoDB. I am far from a MySql expert but have been reading around and it seems that to change this I just add these ...
tgrosinger's user avatar
11 votes
2 answers
21k views

MySQL InnoDB database 'hangs' on selects

I'm trying to fix MySQL configuration on our server. Our app's specifics is that a lot of data is stored in single table (currently over 300 millions of rows). This table is used often for inserts (...
kaczor1984's user avatar
10 votes
2 answers
5k 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 ...
Jacco's user avatar
  • 377
10 votes
3 answers
7k views

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This number ...
tounano's user avatar
  • 221
10 votes
5 answers
955 views

MyISAM for data reads

I have a table with around 1 Billion rows, and its 98% read intensive. I tried tuning the database, with different storage engines (MyISAM and InnoDB) Then ran a few test to view the performance In ...
Akash's user avatar
  • 229
10 votes
1 answer
20k views

MySQL: InnoDB: ERROR: the age of the last checkpoint is X, which exceeds the log group capacity Y?

In a production MySQL environment the following error message is written to /var/log/mysql/error.log every fourth minute: 110723 18:36:02 InnoDB: ERROR: the age of the last checkpoint is 9433856, ...
knorv's user avatar
  • 1,819
9 votes
2 answers
25k views

How big is too big for ibdata file?

My ibdata file is very large, at least it seems to me to be very large. Is this excessive or not that bad? -rw-rw---- 1 mysql mysql 15G Apr 18 10:11 ibdata1
nachito's user avatar
  • 255
9 votes
2 answers
8k 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, ...
Will Glass's user avatar
9 votes
1 answer
24k views

MySQL (MariaDB) crashes frequently

I recently migrated an old server running MySQL to a new VPS running MariaDB 5.5. I don't have too much running on the server (just a few PHP sites) and free memory seems to be OK, but the DB keeps ...
Matt Hamann's user avatar
8 votes
2 answers
7k views

MySQL Slow Writes

Inserts into the following table are taking up to 70 seconds to complete: CREATE TABLE IF NOT EXISTS `productsCategories` ( `categoriesId` int(11) NOT NULL, `productsId` int(11) NOT NULL, ...
Ronn0's user avatar
  • 105
8 votes
5 answers
8k 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 ...
Julien Genestoux's user avatar
8 votes
4 answers
9k views

How to efficiently dump a huge MySQL innodb database?

I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is ...
Jagbir's user avatar
  • 157
7 votes
2 answers
11k views

How do MyISAM and InnoDB Utilize HD Space?

My MySQL server is running out of HD space fast. Most of my larger tables use the InnoDB engine (for no mission-critical reason). In an effort to avoid the dreaded 'drop database to recover innodb ...
Mike B's user avatar
  • 213
6 votes
3 answers
12k 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?
Alex's user avatar
  • 8,641
6 votes
4 answers
28k views

How to permanently increase innodb_buffer_pool_size on Ubuntu

I have read lot of posts & manuals to try to find out how to increase memory in MySQL 5.6 on Ubuntu, which say there are 3 methods: By editing innodb_buffer_pool_size in the my.cnf By command ...
eos's user avatar
  • 561
6 votes
3 answers
5k views

Picking the right innodb_buffer_pool_size

I have a 24GB machine running MySQL (only InnoDB tables). My innodb data set is 11.5GB big, and i have set my innodb_buffer_pool_size to 14GB to allow some growth. So the entire innodb data set is ...
Mr.Boon's user avatar
  • 1,481
6 votes
1 answer
7k views

what is gen_clust_index used for in mysql?

It seems I have a deadlock in mysql (running innodb) that's due in part to a lock on gen_clust_index, however, this isn't a table that I've created. So... what's the short and sweet description of ...
Langley's user avatar
  • 163
6 votes
1 answer
15k views

MySQL, check & enable binary logs

I am running MySQL 5.1.47 on CentOS. How do I check if binary logging for InnoDB is active? How do I enable binary logging if they are not enabled? Just run? mysqld --log-bin
Adrien Hingert's user avatar
6 votes
2 answers
4k views

Way to avoid server downtime when creating Master - Slave relationship?

I am preparing for setting up a MySQL master-slave or master-master relationship. Right now I have a single MySQL production server, and of course I don't want a lot of downtime while I connect the ...
Prof. Falken's user avatar
6 votes
2 answers
4k views

Good/Better config for MySQL on an EC2 Large Instance

I have an EC2 Large instance dedicated to MySQL. It will be serving a Joomla/Magento combo so it has a blend of InnoDB and MyISAM tables. I have only worked with MyISAM in the past and am therefore ...
Tim Reynolds's user avatar
6 votes
3 answers
32k views

Why do MySQL queries pile up in "Sending data" state?

We're using InnoDB tables as the back end of a web application, and everything was fine for about two years until we had to restart MySQL a few weeks ago. (We hadn't disabled reverse DNS lookups, ...
eswald's user avatar
  • 231
6 votes
1 answer
10k views

Is enabling MySQL query_cache_size Innodb Lock Safe?

The query_cache_size looks like the kind of setting that one would usually want enabled which puzzled me since it defaults to 0. Then I read the following about the query_cache_wlock_invalidate ...
Programster's user avatar
6 votes
1 answer
4k views

MySQL show table status always returns Data_free 17825792

When I execute SHOW TABLE STATUS databaseName; I get Data_free info with 17825792 value in all tables : | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | ...
corretge's user avatar
  • 217
6 votes
3 answers
24k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
Tiffany Walker's user avatar
5 votes
5 answers
9k 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. Picture Here http://www....
Brian Armstrong's user avatar
5 votes
2 answers
9k views

Why is MySQL is using so many temporary tables?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
ananthan's user avatar
  • 1,520
5 votes
3 answers
21k views

Huge #sql-xxxx_xxxx.ibd files in mysql-data folder

On one of our mysql databases the size on disk is way larger (28GB) than the actual data (~5GB) so I had a closer look to the files contained in mysql-data I see the following files 12K #sql-...
webgr's user avatar
  • 213
5 votes
2 answers
6k views

Reclaim disk space after deleting Magento MySQL database

I needed to reclaim some disk space so I removed an old Magento database from my server. The database size was around 16 GB. I was surprised that once the database was deleted, it did not really free ...
Josh Pennington's user avatar
5 votes
2 answers
8k views

Disk I/O utilization up to 100% after tuning InnoDB-related MySQL configuration

I have added following lines to "my.conf": query_cache_size=128M innodb_buffer_pool_size=512M innodb_flush_method=O_DIRECT After that Disk utilization started raising, reached 100% and holds steady, ...
spacemonkey's user avatar
5 votes
2 answers
4k views

INNODB mysql. Plugin disabled [duplicate]

When I startup mysql on my unbuntu server I will get a message. 121122 17:39:37 [Note] Plugin 'FEDERATED' is disabled. 121122 17:39:37 InnoDB: The InnoDB memory heap is disabled 121122 17:39:37 ...
alexcunn's user avatar
5 votes
2 answers
3k views

MySQL InnoDB table shows a negative number of rows in phpMyAdmin

I just converted a MyISAM table to InnoDB with around 1.4 million rows. When I converted it to InnoDB, it now shows -1.4 million rows. The table still works as expected, but why does it shows ...
James Simpson's user avatar
5 votes
2 answers
1k views

MySQL 5.5.9 Query Cache not working when schemas have hyphens ("-") in their name

I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU. I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the ...
thepearson's user avatar
5 votes
1 answer
1k views

How to drop an 8 GB InnoDB table smoothly without global locks

MySQL version: 5.1.63. I am about to DROP a bigger ~8 GB InnoDB table. Last week I dropped a four times bigger table on another machine and a global lock kicked in that took us down for ~90 seconds. ...
Ztyx's user avatar
  • 1,405
5 votes
1 answer
794 views

Mysql disaster recovery

We just got a major disaster: somebody made an uncontrolled update on the production database, and obviously, the backup process is not working since a long time, so we got a major data loss. A 40 ...
Alexis Dufrenoy's user avatar
5 votes
1 answer
2k views

16 Cores 12 GB RAM Dedicated MySql Configuration

I have a dedicated 16 Cores 12 GB RAM Mysql server which is performing quite badly since I configure it for a 16 GB server and I guess that explains why in the world the site now takes more than 500 ...
kornesh's user avatar
  • 163
5 votes
1 answer
814 views

MySQL transaction issue with Django and Amazon RDS

I'm having a problem with a MySQL 5.5 database instance running on Amazon RDS. Periodically (once every couple of days) a transaction will fail to complete and appears to leave the tables locked. ...
Richard Owen's user avatar

1
2 3 4 5
8