Linked Questions

55 votes
3 answers
72k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
Googlebot's user avatar
  • 4,531
28 votes
2 answers
86k views

`ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
Petr's user avatar
  • 537
14 votes
2 answers
38k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
Abdul Manaf's user avatar
  • 9,677
5 votes
2 answers
12k views

Scheduled optimization of tables in MySQL InnoDB

What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
Shahid's user avatar
  • 73
4 votes
2 answers
9k views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
Martin's user avatar
  • 151
5 votes
1 answer
11k views

How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine. When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work. I manually ran OPTIMIZE TABLE ...
THpubs's user avatar
  • 153
2 votes
2 answers
5k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
giorgio79's user avatar
  • 1,407
4 votes
2 answers
6k views

What happens when InnoDB hits its tablespace autoextend max?

The latest version of High Performance MySQL (3rd ed.) recommends that one: ...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't ...
HTTP500's user avatar
  • 359
8 votes
1 answer
4k views

How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
Dan's user avatar
  • 81
1 vote
1 answer
2k views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
JShean's user avatar
  • 169
1 vote
1 answer
2k views

MySQL Data Files Missing?

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there ...
Alan's user avatar
  • 207