mysql

Reclaim Your Hard Drive - Saving Tons of Space with MySQL InnoDB tables

Drupal 7 uses InnoDB tables. InnoDB provides many benefits, but can cause some unexpected headaches. One headache for me is that, by default, MySQL tells InnoDB to create one file on your system, called ibdata1, to hold ALL the data from EVERY InnoDB table you have on your MySQL server. This file never shrinks in size; it only expands to contain new data. If you delete something from MySQL or drop a table, the space that table was using is reallocated for other new data. This isn't a bad thing, especially for those who have a lot of drive space, and not many databases that are altered or dropped quite frequently.

I develop a lot of sites on my little MacBook Air (with a 128GB SSD), so I often download database snapshots from live and testing environments, empty out the tables on my local environment, then import the database dumps. Can you spot the problem here?

Using Daisy Disk I just noticed that my ibdata1 file had grown to more than 10 GB, and my Air's drive only had about 5 GB free space!

So, after reading through MySQL's InnoDB Engine documentation and this answer on Stack Overflow, I found that it's not too hard to change MySQL to keep data tables in their own files, and delete the files after the tables are deleted (thus saving me a ton of space). It just takes a little time and annoyance.

Here's how to do it, roughly:

Drupal 6.x and PHP 5.3.x - Date Timezone warnings

This morning, I was presented with quite the conundrum: one of my servers suddently started having about 4x the normal MySQL traffic it would have in a morning, and I had no indication as to why this was happening; traffic to the sites on the server was steady (no spikes), and I couldn't find any problems with any of the sites.

munin mysql traffic spike

However, after inspecting the Apache (httpd) error logs for the Drupal 6 sites, I found a ton of PHP warnings on almost all the sites. Something like the following:

MySQL Select rows that are in one table but not in another

I've had to do this a couple times, and every time, I look around on Google for some good solutions, but don't find much. Basically, I have two tables of data, and I want to see if there are any rows in the first table that aren't in the second (or, conversely, I only want values that are in the first table AND the second).

To select rows in the first table that don't have any corresponding values in the second, try:

SELECT first.*
FROM first_table first
LEFT JOIN second_table second ON first.id = second.id
WHERE second.id IS NULL

Conversely, if you just want to select rows in the first table that are also in the second (but discard rows that don't have corresponding values in the second), try:

SELECT first.*
FROM first_table first
LEFT JOIN second_table second ON first.id = second.id
WHERE second.id IS NOT NULL

For my Drupal site, I needed to do something like:

MySQL General Errors on MAMP/WAMP/XAMPP

I've been getting errors like General error: Can't create/write to file, Error 2006: MySQL server has gone away, and other similar PDOExceptions and errors from time to time while developing on my Mac using MAMP Pro (this seems to happen more often with Drupal 7 sites than Drupal 6, for reasons I know not). I've noticed a few other developers are getting these errors too, and almost always on local environments as opposed to live servers.

I found that the easiest way to deal with them is by giving MySQL a nice buffer of memory via the max_allowed_packet and innodb_buffer_pool_size settings. Just bump those up to 256M or higher, and the errors above should go away. (In MAMP Pro, just go to File > Edit Templates > my.cnf, and search for those variables. Uncomment the innodb_buffer_pool_size variable if it's commented out.

Drupal Performance Guide - Drupal and the LAMP/LEMP stack

LAMP Stack with Drupal - Druplicon, Linux, Apache, MySQL, PHP

Drupal is a scalable, flexible, and open source content management system that is built to run on a variety of server architectures. The only real requirement is that PHP runs on your system. You can run Linux, Microsoft, Mac OS X, etc., along with Apache, IIS, nginx, MariaDB, MySQL, PostgreSQL, etc. if you're willing to do a few extra things.

However, the overwhelming majority of Drupal websites use the most popular LAMP stack on the backend: Linux, Apache, MySQL and PHP, or the 'LEMP' variation, with Nginx instead of Apache. This white paper (which is a living document – I'll be updating it as time progresses) provides my thoughts on performance considerations for Drupal on a LAMP stack, but this information can be used for pretty much any system on any server, if you look at the basic principles.

Sections:

Getting OSC's Drupal Install Optimized on Slicehost

Initially, when thinking about finally taking the plunge and purchasing a slice or two from Slicehost, I thought, "wow, this is going to be incredibly fast and awesome, compared to my Host Gator account!"

Slicehost + Drupal

But, after setting everything up and putting Open Source Catholic live on the fresh slice, running free -m, and looking at the results, reality set in: 256 MB of RAM is not much to work with if you're running a Drupal site on a LAMP stack! Drupal usually consumes 15-40 MB of RAM per page view for a logged-in user, and if you have a site with 10 or so logged in users at any moment... well, bad things can happen.

For anonymous users, using Boost will help your site fly no matter the amount of RAM you have. But even so, a bunch of requests to uncached pages will cause your site to load a heck of a lot slower, and will fill up your RAM faster than a fire hose fills up an 8 oz. glass!

Using default Apache, MySQL and PHP settings, free -m showed a full 250 MB of RAM used, along with 400-500 swap space used (swap should be kept to a minimum—if you have a lot of swap usage, that means the hard drive is being used instead of RAM, and the hard drive is inherently many times slower!). After performing a few quick modifications to Apache and MySQL, I was able to get this number down to 140 MB RAM / 40-60 MB swap, on average.

I modified the server configuration in two different places: Apache's httpd.conf, and MySQL's my.cnf:

Pages

Subscribe to RSS - mysql