Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: innodb_file_per_table (reset)

How to reclaim space in InnoDB when innodb_file_per_table is ON
+1 Vote Up -0Vote Down

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space except dump/reload method.

When Innodb_file_per_table is ON, each table stores data and indexes in it’s own tablespace file. However, the shared tablespace-ibdata1 can still grow and you can check more information here about why it grows and what are the solutions.

http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

Following

  [Read more...]
Thoughts on Upcoming MySQL 5.6 Defaults
+0 Vote Up -0Vote Down

Read the original article at Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner. With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • 5 Ways to Boost MySQL Scalability
  • 5 Ways to fortify MySQL replication
  •   [Read more...]
    5 Ways to Boost MySQL Scalability
    +0 Vote Up -0Vote Down

    There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see.

    1. Tune those queries

    By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive

      [Read more...]
    Connecting orphaned .ibd files
    +2 Vote Up -0Vote Down

    There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in ibdata1 file and every InnoDB table has two files e.g.  actor.frm and actor.ibd.

    The annoying thing about .ibd files you can’t easily copy the an .ibd file to another MySQL server. If you try to very often you’ll get an error in the log:

    InnoDB: Error: tablespace id is 10 in the data dictionary
    InnoDB: but in file ./sakila/actor.ibd it is 15!

    However sometimes you have to connect the .ibd file to an alien ibdata1.

    There are several situation when

      [Read more...]
    Leveraging the InnoDB Plugin
    +3 Vote Up -0Vote Down

    Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

    #my.cnf
    [mysqld]
    innodb_file_per_table
    

    The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

    • Backup all InnoDB tables via mysqldump
    • Drop InnoDB tables
    • Verify InnoDB not used
    • Stop MySQL
    • Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
    • Remove ibdata? files
    • Start MySQL
    • Create Tables
    • Reload Data
    • Verify InnoDB Operation
      • The primary

      [Read more...]
    Spreading .ibd files across multiple disks; the optimization that isn’t
    +1 Vote Up -0Vote Down

    Inspired by Baron's earlier post, here is one I hear quite frequently -

    "If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

    There are a few things wrong with this advice:

  • InnoDB does not support these symlinks.  If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed.  Your "optimization" is lost.
  • Striping (with RAID) is usually a far better optimization.  Striping a table across multiple disks effectively balances the  'heavy
  •   [Read more...]
    How many files does InnoDB have open?
    +0 Vote Up -0Vote Down

    If you use innodb_file_per_table = 1 and innodb_open_files = X (whatever amount is suitable for your server) there’s no way internal to MySQL for finding out how many IBD files InnoDB actually has open. Neither SHOW GLOBAL STATUS LIKE ‘innodb%’ nor SHOW ENGINE INNODB STATUS provide this information.

    Many sites do have a growing number of tables, so you’ll want to know when it’s time to up the number (and potentially also open-files-limit). Solution: sudo lsof | grep -c “\.ibd$”

    Fixing InnoDB IMPORT TABLESPACE Error: ERROR 1030 (HY000): Got error -1 from storage engine
    +0 Vote Up -0Vote Down
    SetupWe have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.ProblemWhen transporting an .idb file from one server to another, I ran into the following error:ERROR 1030 (HY000): Got
    innodb_file_per_table Revisited
    +0 Vote Up -0Vote Down
    In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

    Someone suggested a better way:

    1. Alter all innodb tables to MyISAM
    2. Stop the server
    3. Add innodb_file_per_table to my.cnf
    4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
    5. Move all innodb files (logs, data) to a backup directory
    6.








      [Read more...]
    Showing entries 1 to 9

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.