After our maiden voyage, we paused for some peace of mind and data, then explored a type of software called a wiki, which allows anyone to edit anything. Whither does our journey take us today? The in-depth dangers of a content management system? The untamed wilds of forum software? The upselling madness of an ecommerce site? No, dear adventurer: today, we return to the helper monkeys.
With all of this production-quality stuff going on, it's a good idea to make sure things are always smelling as fresh as a summer breeze. If you've run a Windows machine in the past, you may recall the need (nay, recommendation) for regular defrags or registry cleaning. I dunno about you, but even though nothing seemed better, I sure loved looking at all those colored blocks being moved around with Norton's Speed Disk. Thankfully, we don't have to worry about this with OS X, but we can do some similar "preventative optimization" with our databases (though, sadly, with no purdy colors).
A word of ambivalence: you don't have to run these commands, any more than you have to defrag a Windows hard drive on a regular basis. In most cases, these are placebos and panaceas: fun to do and fun to say, but they usually attempt to solve a problem that rarely exists. Running them regularly certainly doesn't harm anything, however, so I'll always err on the side of false hope and great expectations.
To get started, we're going to use the included
mysqlcheck utility--you can get a mouthful of its capabilities by running it with no additional parameters. Be sure that your MySQL server is started, as
mysqlcheck will only function when it is. (Another utility,
myisamchk, can do similar things with the server shutdown, but is not described here.) Our first attempt will repair (
-r) all of the tables of all of the databases (
-A) on our server by issuing a MySQL
REPAIR TABLE statement:
mysqlcheck -p -A -rmanifest_drupal.access OK manifest_drupal.accesslog OK manifest_drupal.aggregator_category OK manifest_drupal.aggregator_category_feed OK . . .
Another flag to
mysqlcheck I use frequently is
-o, which uses the
OPTIMIZE TABLE SQL statement, explained thusly: "OPTIMIZE TABLE should be used if you have deleted a large part of a table or ... have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns) ... You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file." Unless you know databases in depth, you probably won't fully understand what that means. That's quite alright--just run the following, and magical things will happen:
mysqlcheck -p -A -omanifest_drupal.access OK manifest_drupal.accesslog OK manifest_drupal.aggregator_category OK manifest_drupal.aggregator_category_feed OK . . .
I am going to assume, nay, implicitly trust, that you've some sort of backup scheme already in place. Whether it's
rsync, Backup 3, FTPing to another server, or weekly burns, you need something to prevent your production web server (or in-progress sandbox) from being destroyed by an errant failure or lack of judgment. Yeah, yeah, I know: you've heard this a thousand times before. Even so, it amazes me how many folks still don't check for cancer nor replace default config files that admonish against their use (we covered that in part two). Meatware, software, or hardware: bad things happen. Ensure they don't. This has been a public service announcement: Make Mine Morbus!
With that said, backing up your MySQL databases, whether those of your own creation or the one we made for MediaWiki, isn't as simple. Yes, you could add /usr/local/mysql to your backup, which would cover the entire installation plus the databases, but that's less than ideal. Here, the backed-up databases are the binary versions: suitable for the specific version of MySQL you've installed, but not much else.
The proper approach is with a utility called
mysqldump, which was installed along with MySQL. This utility generates a text file of all of the SQL statements that were used to create your database. Anytime you create data with an application such as MediaWiki, you're sending dozens of SQL statements to your database.
mysqldump spits out all of these statements, creating a picture-perfect and plain text version of your content, suitable for importing back into MySQL at a later date.
You can issue
mysqldump --help in your Terminal to see the boatload of options available to you. The manual (or "manpage") is accessible with
man mysqldump. The quickest way to get started is with the command below, which exports all of the SQL statements of db_name into a file called
mysqldump -p db_name > db_name.sql
In MySQL 4.1 (the version we installed), passing no other option to
mysqldump defaults to using the flag
--opt, which is just perfect for our needs (as it's a shortcut to a number of common options). Open up the newly created
db_name.sql and take a look: you'll see hundreds and hundreds of lines of SQL that make up your entire database. If the need ever arises, you can re-import this text file back into MySQL, creating an exact duplicate of the database at the time you ran the
mysqldump command (naturally, this that means having up-to-date dumps will be important):
mysql -p db_name < db_name.sql
Other possibilities include exporting all of your MySQL databases (as opposed to just one, above) to a single file, narrowing down the export to just a single table, or compressing the results:
mysqldump -p -A > all_databases.sqlmorbus@:~ >
mysqldump -p db_name table_name > db_name.table_name.sqlmorbus@:~ >
mysqldump -p -A | gzip -9 > all_databases.sql.gz
Author's Note: Shortly before this article went to press, Apple released Xcode 2.2. Although I have not yet verified it myself, I've been told the update fixes the
php_version.h problem I workaround in the text. If your
php_version.h looks like the one I've included, you can continue installing eAccelerator as normal.
There's an insane number of factors involved when determining how "quick" your website is: hardware, network, software configuration, page and code design, user expectations, and more. We can score a number of Apache-related tips from its own performance tuning page, but there are dozens of articles with suggestions on writing better PHP. Instead of better coding, we're going to focus on faster PHP execution, regardless of the code's quality.
To do this, we're going to install a PHP module called eAccelerator. This module (and others like it) "increases the performance of PHP scripts by caching them in a compiled state, so that the overhead of compiling is almost completely eliminated." If you've used PHP caching systems before, eAccelerator is a continuation of Turck MMCache, no longer in development. Go ahead and grab the latest source code for eAccelerator (0.9.3 as of this writing) and save it to your desktop.
Before we can compile eAccelerator, however, we have to fix a minor bug within the Tiger Developer Tools (up to and including Xcode 2.2 Developer Preview 1). See, eAccelerator is going to be compiled with information from /usr/include/php/main/php_version.h, but that file suggests that we have PHP 4.3.10, when we actually have version 4.3.11. We'll need to change this to represent the correct information. First, we'll make a backup, and then twiddle the file's "write" bit:
cd /usr/include/php/main/morbus@:~ >
sudo cp php_version.h php_version.h.bakmorbus@:~ >
sudo chmod 644 php_version.h
Next, we'll need to change the file so that it looks like the below:
/* automatically generated by configure */ /* edit configure.in to change version number */ #define PHP_MAJOR_VERSION 4 #define PHP_MINOR_VERSION 3 #define PHP_RELEASE_VERSION 11 #define PHP_EXTRA_VERSION "" #define PHP_VERSION "4.3.11"
You'll need some sort of authenticating text editor (I will always prefer BBEdit) to save your changes. With those tweaks done, we can go ahead and compile eAccelerator. Move to your desktop, or wherever else you downloaded the source to, and run through the following:
tar xvzf eaccelerator-0.9.3.tar.gzmorbus@:~ >
cd eaccelerator-0.9.3morbus@:~ >
sudo /usr/bin/phpizemorbus@:~ >
./configure --enable-accelerator=shared --with-php-config=/usr/bin/php-configmorbus@:~ >
sudo make installInstalling shared extensions: /usr/lib/php/extensions/no-debug-non-zts-20020429/
These commands compile and install eAccelerator, but we still have to tell PHP that it should load and configure this new module. A few articles ago, we replaced our /etc/php.ini file with a more secure version, and we'll be going back to this file to make some eAccelerator-related changes. Open that file up in your favorite text editor, look for
extension_dir, and make the following change (you'll notice we're setting
extension_dir to the same value that our
make install reported above):
; Directory in which the loadable extensions (modules) reside. ; extension_dir = "./" extension_dir = "/usr/lib/php/extensions/no-debug-non-zts-20020429/"
Next, look for a block of configuration that starts with:
;;;;;;;;;;;;;;;;;;;;;; ; Dynamic Extensions ; ;;;;;;;;;;;;;;;;;;;;;;
And add the following somewhere after it:
extension="eaccelerator.so" eaccelerator.shm_size="16" eaccelerator.cache_dir="/tmp/eaccelerator" eaccelerator.enable="1" eaccelerator.optimizer="1" eaccelerator.check_mtime="1" eaccelerator.debug="0" eaccelerator.filter="" eaccelerator.shm_max="0" eaccelerator.shm_ttl="0" eaccelerator.shm_prune_period="0" eaccelerator.shm_only="0" eaccelerator.compress="1" eaccelerator.compress_level="9"
Save the file. Astute readers will notice
/tmp/eaccelerator as a value for the
eaccelerator.cache_dir parameter, and we'll need to manually create that directory and give it proper permissions:
mkdir /tmp/eaccelerator chmod 0777 /tmp/eaccelerator
Restart Apache (with
sudo apachectl restart) and reload the MediaWiki site we created last article. You'll know our new eAccelerator is working when
ls /tmp/eaccelerator/ returns a number of files of the following form--these are cached compilations of the various bits of PHP code that power MediaWiki:
eaccelerator-630188432.00949714 eaccelerator-630188432.38849714 eaccelerator-630188432.74949714
With a few more helper monkeys in our entourage, what's next? Our treasure map suggests "content management systems:" far more than blog software, but far less than handcoding everything yourself. Until then, don't hesitate to ask questions, wax poetic about future desires, or lament the errors.
Kevin Hemenway is the coauthor of Mac OS X Hacks, author of Spidering Hacks, and the alter ego of the pervasively strange Morbus Iff, creator of disobey.com, which bills itself as "content for the discontented."
Return to the Mac DevCenter
Copyright © 2009 O'Reilly Media, Inc.