Questions tagged [innodb]

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

Filter by
Sorted by
Tagged with
38 votes
6 answers
28k 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
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
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
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
16 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,314
14 votes
1 answer
18k 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
9 votes
1 answer
23k 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
4 votes
1 answer
4k views

InnoDB: Fatal error: cannot allocate memory for the buffer pool

MySQL Seems to be crashing quite often, I'd say every hour or so. The only thing running on this server that is using MySQL is a Drupal installation that I'm working on, there's only one person that'...
Luke Berry's user avatar
1 vote
1 answer
4k 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 ...
user avatar
70 votes
9 answers
207k 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
  • 833
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
5 answers
32k 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
  • 355
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
13 votes
7 answers
61k 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
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
8 votes
4 answers
8k 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
8 votes
5 answers
7k 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
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
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
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
4 votes
1 answer
638 views

Mysql InnoDB table size performance

I have a table that is closely approaching 2 million records. This table stores a history of transactions. This is on a high traffic website but also the table is not accessed regularly. We ...
pablo's user avatar
  • 3,040
4 votes
1 answer
2k views

innodb memory usage mysql

I have a small vps, with only 256mb of ram, with maximum burst up to 512mb. When I configure my vps without innodb, it only uses 130 mb of ram, so that is no problem for me. But when I turn on innodb, ...
Tiddo's user avatar
  • 1,029
3 votes
1 answer
7k views

MySQL InnoDB: how to switch to Barracuda format?

In new MySQL versions Barracuda file format was introduced. So I havew a few questions: Is it working only with file_per_table option? How to switch existing system to it (preferably the whole ...
Vladislav Rastrusny's user avatar
3 votes
1 answer
3k 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 ...
user avatar
3 votes
2 answers
5k views

After adding skip-innodb mysql doesn't start

I am trying to setup these values: #skip-bdb #skip-locking #skip-innodb When I add them to /etc/mysql/my.cnf and even if I turn ON of of, them after I do the service restart mysql fails to start, ...
Pentium10's user avatar
  • 444
2 votes
2 answers
1k views

Innodb one file per tablespace

I'm walking into an environment that uses MySQL and Innodb as the storage engine. I'd like to enable innodb_file_per_table to increase performance. Considering all of the databases/tables are ...
nulltek's user avatar
  • 1,281
2 votes
3 answers
839 views

MySQL keyring component (not plugin) not loading on server startup

Problem: There is no indication mysqld is actually reading the server manifest and/or the keyring component configuration file. Any help would be greatly appreciated. System Info: Ubuntu 20.04 ...
nerdgeekdork's user avatar
1 vote
1 answer
2k 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 ...
user avatar
1 vote
1 answer
21k views

MySQL server quit without updating PID file

I need help on how to start the mysql server. The problem arise when the root directory was full. To be able to login to directadmin and start mysql, I added a soft link of the /var/lib/mysql/ to /...
Pelang's user avatar
  • 413
1 vote
2 answers
1k views

IO issues with InnoDB, review of assessment required

TL;DR - Having some IO based issues with InnoDB. Before I sign the form to request more hardware can you have a look and see if you agree with my assessment Working for a client, I have inherited a ...
thepearson's user avatar
1 vote
1 answer
4k views

How to use most of memory available on MySQL

I've got a MySQL server which has both InnoDB and MyISAM tables. InnoDB tablespace is quite small under 4 GB. MyISAM is big ~250 GB in total of which 50 GB is for indexes. Our server has 32 GB of RAM ...
Zilvinas's user avatar
  • 383
0 votes
1 answer
3k views

Mysql crashed, dumped DB's in recovery mode. What now?

I ran into an InnoDB issue that only seemed to get worse as I tried to fix it. Started with InnoDB: Unable to lock ./ibdata1, error: 11, I moved and then copied it back. I soon had an ib_logfile issue ...
Tim's user avatar
  • 143
0 votes
2 answers
1k views

Can transactions be forced to occur sequentially in MySQL?

Let's say that I have two servers that simultaneously initiate a transaction on a MySQL 5.5 database using InnoDB. Both transactions do the same thing: Read a single row, then update the row based on ...
Trevor Burnham's user avatar
0 votes
2 answers
24k views

Optimal MySQL my.cnf setting for large Magento site on a dedicated server

Our dedicated server is running a large Magento store with a MySQL database approximately 250MB in size. The server spec is listed as having 5 Xeon 2Ghz processors (4MB cache) and 12GB of memory. I'd ...
Larry B's user avatar
  • 261
0 votes
2 answers
7k views

MySQL maximum possible memory usage above installed RAM

I'm using debian 8 Jessie and MariaDB. My mysqltuner seems to indicate as MariaDB can use 142% of RAM : -------- Storage Engine Statistics ------------------------------------------- [--] Status: +...
poka's user avatar
  • 135