Questions tagged [innodb]
InnoDB is the main ACID-compliant Storage Engine used in MySQL.
35
questions
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 ...
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 ...
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 ...
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
...
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 ...
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?
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 ...
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'...
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 ...
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 ...
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 ...
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... ...
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
...
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—...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
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, ...
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 ...
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
...
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 ...
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 /...
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 ...
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 ...
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 ...
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 ...
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 ...
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: +...