Questions tagged [innodb]
InnoDB is the main ACID-compliant Storage Engine used in MySQL.
390
questions
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
...
- 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 ...
- 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 ...
- 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] ...
- 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 ...
- 323
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
...
- 293
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... ...
- 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 ...
- 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.
user16614
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 ...
- 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 ...
- 909
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?
- 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
- 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—...
- 364
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 ...
- 231
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 (...
- 241
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 ...
- 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 ...
- 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 ...
- 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,
...
- 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
- 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, ...
- 937
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 ...
- 193
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,
...
- 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 ...
- 609
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 ...
- 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 ...
- 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?
- 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 ...
- 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 ...
- 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 ...
- 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
- 309
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 ...
- 302
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 ...
- 171
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, ...
- 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 ...
- 525
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 | ...
- 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 ...
- 6,691
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....
- 1,627
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 ...
- 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-...
- 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 ...
- 288
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, ...
- 217
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 ...
- 51
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 ...
- 1,661
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 ...
- 880
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. ...
- 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 ...
- 255
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 ...
- 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. ...
- 51