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

I will probably be coming back to your weblog for more soon With support for power unit-GL/2 and horsepower RTL files, customary and HDI printer drivers, and enclosed PosterArtist light coding system, the iPF750 provides the flexibleness and suppleness needed for personal and small workgroup applications. if you need driver printer, you can check at driverresetter is place for download driver printer australia and you can check the driver for Download Canon imagePROGRAF iPF750 Driver Printer herereadmore
Samsung Galaxy Grand a pair of Review and Specifications. Samsung was one among the first companies to primarily run with the idea of giant - screen phones . Others had tried before and unsuccessful , but Samsung managed to indicate its original Galaxy Note into a massive succes if you need more info about gadgets, you can check at update your gadgets info here and you can check for the Samsung Galaxy Grand 2 Review and Specifications herereadmore
Mitsubishi hasn’t had a significant piece of the european market in years, but the japanese complete remains churning out models there. It’s recent one, though, merely might even be what the company has got to get Europeans’ attention. just for the uk market, if you need the last info of autocar, you can check at autocar review update everyday and you can check the Mitsubishi Lancer Evo X FQ-440 MR 2020 herereadmore
Yapenbit College is an institution of Education and Training in the Field of Information Technology / IT under the Educational Development Foundation for Information and Technology (YAPENBIT) Aceh is located at Lhokseumawe. if you need the last info of technology, you can check at update pengetahuan teknologimu di echokita and you can check the Belajar Komputer Gratis 3 Tahun di Yapenbit herejangan ngintip backlink :p

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

Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
https://www.rebelmouse.com/getthegirlcodereviews/

Review writing is one of the most difficult things in the field. If you are also looking for someone who could help you in this regard, we can made your search easy. You must visit topreview4you.com and analyse their material that will ensure you about the quality work provided by them.

This is a good post. This post gives truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works.
https://www.rebelmouse.com/textyourexbackreview/

Great Article it its really informative and innovative keep us posted with new updates. its was really valuable. thanks a lot.
click here to read more at rebelmouse

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.

When you restart your MySQL server it resolution outset putting the medatadata in the database binders that determination be designed from besides on, unfortunately this does hardly receive disencumber of the mammoth ibdata1 cabinet. goats milk stuff

I think most people would do the same when they are headed with the situation. http://uqw.me/Cc2

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

As a web developer active among Drupal for about a year present, undivided of the fortes I do a numerous is importing databases of current ventures, dropping them during I twist up plus reimporting a understudy to harass encore. Consequently I stake negative also many web-developers understand about MAMP's avid MySQL or I would've heard about it previously. Cool cufflinks

Copy-editing makes improvements towards style, accuracy AS WELL AS tone regarding writing previously That is typeset AS WELL AS before publication or maybe printing. the actual process does not change your current copy editing proofreading voice of any author or perhaps the substance of your text. your current copy-editor carries out their function previously proofreading which will be your own last stage of your editorial process.

I have found your article here and now I am loving the whole website! It is full of awesome publications, thanks for that. I am going to bookmark it right now!

If you are looking for cheap dresses at lowest Karen Millen Outlet price, then you're comming the Karen Millen dresses right place. Our website offer high quality Karen Millen dresses for ladies for many years, you will never knew how you love this brand name Karen Millen Sale UK in London and many other fashion cities by young and old. welcome to choose your favorite style in our outlet store online, Good Luck!

When we start having coding issues with your site, you can be tempted to take drastic measures, I think looking for viable solutions can also work, something I'm getting the hip to, right now.