Mastering MAMP's Metadata-Munching MySQL

Since this post ran a bit long I've included a TL;DR section.

The Problem

MAMP's MysQL uses a shared InnoDB file "ibdata1" which grows over time and never shrinks. Not even when dropping databases. All the metadata and indices for all databases stay in there forever.

The Solution

Tell MySQL to store medata and indices in separate files per database.

  1. Dump all databases in files and drop the databases.
  2. Delete ibdata1, ib_logfile0 and ib_logfile1.
  3. Add innodb_file_per_table to the [mysqld] section of /etc/my.cnf
  4. Restart MySQL and reimport the dumped databases.

The Article

Recently I had to import a MySQL dump of 9GB resulting in a database of 21GB. I didn't have enough space on my hard drive and after continuously deleting applications and music as the import went on I realised I wouldn't make it. I aborted the process and dropped the partial database. Funnily enough I noticed I didn't get all my space back. After a little scooting around on the file system I understood something I had been suspecting for a while already. MAMP's default MySQL settings suck.

jeroen@liver:/Applications/MAMP/db/mysql$ ls -hl | grep ibdata1
-rw-rw---- 1 jeroen admin 14G Nov 4 16:25 ibdata1

I've always been slightly opposed to using MAMP for serious work but the company I work for uses it and their setup scripts expect folder layouts the way MAMP sets them up. Now, after a year of using MAMP on a 128GB MacBook Air I saw the shared InnoDB file MySQL uses had grown to 14GB. That is a lot of wasted space.

Why is it wasted space you might ask? Surely MySQL needs it for something. Yes, MySQL needs it. It stores the _current_ database-indices in there as well as the metadata for them, you can even restore whole databases, after having dropped them, from this InnoDB data file. But, it also stores all that information for databases you have already dropped. See, MySQL's ibdata1 file never shrinks, it only grows.

As a web developer working with Drupal for about a year now, one of the things I do a lot is importing databases of ongoing projects, dropping them when I screw up and reimporting a backup to try again. Therefore I bet not too many web-developers know about MAMP's hungry MySQL or I would've heard about it earlier.

There is a solution and it is called innodb_file_per_table. When you put this in the MySQL configuration file it tells MySQL to store the metadata and indices for each database in a separate file within that database's folder. Then, when you drop a database and MySQL deletes the folder it will also remove the metadata files.

If you're wondering where you can find the MySQL config file your best guess is /etc/my.cnf unless of course you're using MAMP because it just starts MySQL with any meaningful parameters, neither does it install a my.cnf anywhere MySQL looks for one. (More info here: http://bensch.be/mysql-config-with-mamp) You can copy one of MAMP's default MySQL config files to /etc/my.cnf. Look for them under /Applications/MAMP/Library/support-files/

None of MAMP's MySQL config files however include the innodb_file_per_table setting so make sure to add this to the [mysqld] section of the file you chose to copy to the /etc/my.cnf:

innodb_file_per_table

When you restart your MySQL server it will start putting the medatadata in the database folders that will be created from then on, unfortunately this does not get rid of the huge ibdata1 file. All current databases must be dumped and dropped first, then the ibdata1, ib_logfile0 and ib_logfile1 fils can be deleted. MySQL can be restarted with the innodb_file_per_table setting in place and the dumped databases can be reimported.

You'll be saving gigabytes of disk space in your day-to-day development.

While I was writing this post I came across this Stack Overflow answer: http://stackoverflow.com/a/4056261 It tells the same story but with a bit more detail about what's in the ibdata1 file.

Comments

Accordingly, during you fall a database besides MySQL expunges the binder it mind similarly eliminate the metadata columns. air conditioning repair chicago

The re enter values to smart working channels will amplify new turns to big success mark today. And it simply connect all new portions after perfect choice. buy instagram followers fast

The surprise mode systems discover new plans and items will completely try your subject and charge will commit with search path. buy facebook photo likes

Getting your blood drawn to run lab work feels like taking your car into the mechanic. What is really going on in there? What absolutely must be inspected? The tests have complicated names and do they tell me what is good, bad, or what I need to do next to stay healthy? crp health

What is health Insurance. The main reason why you need an affordable health insurance is to help you plan ahead in case of unexpected costly emergencies. The only way you can accomplish this is to compare prices from most leading insurance companies and also choosing a health insurance plan that best suits you. coimbra health

When looking to buy natural health supplements, chances are you'll find your options are limited in local stores. Online, there are many possibilities. However, an important question is -- how to know which natural tonics for health and wellness are legitimate ones that provide specific health benefits? broad health

When you are planning a wedding, it pays to keep your eyes and ears open. Potential sources of inspiration are everywhere, and you never know when you might stumble across an idea that would be perfect for your wedding. That is why brides in the know are careful to keep up with the latest wedding news. all consuming news

Many businesses periodically send newsletters by postal mail to keep subscribers current on their affairs or industry-related news, for the purpose of maintaining a loyal customer base to whom they can market additional products or services. 7d news

Remember, you are in control of the essay, so guide it where you desire (just make sure your audience can follow your lead). The argumentative essay is a genre of writing that requires the student to investigate a topic; collect, generate, and evaluate evidence; and establish a position on the topic in a concise manner. shopping cart

The U . s . offers countless choices that will choosing one can possibly be challenging! Narrow along your prime choices along with let all of your family make a decision. smart-travellers

Exactly precisely why perhaps certainly not take many of the burden off yourself by simply enabling all your family members members that may help you in your current daily work opportunities. Here are a number of ideas that one could put straight into action to get started on obtaining your current household engaged. guru4travell

Many merchants charge excessive for most of these products. Try flip-style your outfits in innovative strategies will undertake less place. Trying out and about different flip-style techniques will permit more to adjust to in your current bag. schoolperform

The process of car shopping can be very stressful for anyone. There are endless numbers of vehicles to consider, and it is normal to feel overwhelmed. The information here will help make buying a car as easy and stress-free as possible. http://www.businesscarsview.com

MAMP's MysQL works on the distributed InnoDB data file "ibdata1" which in turn expands with time and never lessens. Not just when giving up sources. All of the metadata as well as indices for all those sources remain in presently there for a long time. Your Bugatti Veyron can be easily the top 10 fastest cars in the world. Rate demos from the summer time of 2010 proven the particular Veyron as the clear success when it comes to best velocity - 253 mph examined separately in a pair of unique period demos.

Preparation along with organization are generally chief components to facilitate a terrific camping getaway. You must be sure to are absolutely prepared before heading camping. traveldeal88

The other key difference between a standard life insurance policy and a PruProtect Optimiser policy is how it impacts your monthly premiums. While most life insurance policies are fixed for the life of the policy, the PruProtect Optimiser policy is a little more complicated. http://www.activeinsuranceadvisors.com/

As a world wide web creator using the services of Drupal approximately 1 year at this point, one of many items I truly do a good deal is importing listings involving continuing tasks, losing these people when i mess in place and also reimporting the backup to try once more. For that reason We gamble not too many web-developers find out about MAMP's starving MySQL or even We would've heard about this before. mcdonalds nutrition is often a important instance of an very successful organization. Suffering from several merchants everywhere, it's extra shops exactly who will probably be start on a daily basis. This trustworthiness of his / her foods, generally speaking is frequent, wherever one is.

Thanks for the useful post. I am only learning Mysql so sometimes it's really hard to come up with an idea by myself. I love all your website, you have some pretty neat advices here. Keep it up mate!
Greeting from Yppe BE

When you restart your MySQL server it volition birth putting the medatadata in the database binders that devise be designed from therefore on, unfortunately this does negative comprehend unburden of the mammoth ibdata1 register. facebook

This topic is something that I have been looking into for a while now and your insight is exceptional. Thanks for sharing this information. agen ibcbet, agen sbobet indonesia

What a great article. I have found it here and now I am loving your website! Going to bookmark it and get back here as soon as possible.

These are really a superb showcases here I am glad to have a review of it and feel to roll on its steps for my benefit always
Apple authorized service center Guwahati

This great article is wonderful and it’s pleasurable to read. I've recognized extremely important issues more than here. We envy the actual important suggestions people provide.Pass bee Media wallet

Bear in mind, you're accountable for the actual composition, therefore guide this in which you wish (just make certain your current target audience can certainly comply with your current lead). The particular argumentative composition is often a genre of producing that will require the actual scholar to investigate a topic; gather, create, and also examine evidence; and also establish a placement about the subject in the to the point approach.

A lot of us pleasant acquired in simple terms before up-wards retaining this type of single surprising web page related to certainly not remote very likely take in. download kik for pc

Wow what a great post. I have found it here and now I am absolutely loving your website. Thanks for all your hard work and keep them coming mate!

Therefore massive forms coalition will be! This kind of assembled to create the method a large amount of All of us get over observed out of your self-esteem, including forms veteran including dynamic contemporarys titillating. All of us use outside agencies for suggest the product or service to everybody. Cheap mobile phone Sydney