Linked Questions

55
votes
3answers
68k 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 ...
24
votes
2answers
48k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and re-...
15
votes
1answer
38k views

What exactly are iblog files in mysql

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
4
votes
3answers
16k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
11
votes
3answers
3k views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
4
votes
3answers
10k views

ibdata1 grows exponentially when innodb_file_per_table is configured

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month). Is My my.cnf file is ...
5
votes
5answers
11k views

MySQL .ibd file is too big

I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all ...
5
votes
2answers
9k 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 ...
5
votes
1answer
10k 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 ...
7
votes
1answer
5k views

How large will a MySQL database be relative to the dump file?

I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
4
votes
1answer
11k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
4
votes
2answers
11k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
2
votes
2answers
7k views

The best MySQL configuration for my server

I'm little newbie in configuring mysql and I need some advices. Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc. I'm using it to ...
3
votes
2answers
4k views

Database space doesn't match ibdata1 size

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table. In the MySql workbench, when I query for the databases used space, with this query SELECT ...
2
votes
1answer
6k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...

15 30 50 per page