HACKS:

Archive: MySQL

August 6, 2008

Memcached and high performance MySQL

Memcached is a distributed object caching system that was originally developed to improve the performance of LiveJournal and has subsequently been used as a scaling strategy for a number of high-load sites. It serves as a large, extremely fast hash table that can be spread across many servers and accessed simultaneously from multiple processes. It's designed to be used for almost any back-end caching need, and for high performance web applications, it's a great complement to a database like MySQL.

In a typical environment, a web developer might employ a combination of process level caching and the built-in MySQL query caching to eke out that extra bit of performance from an application. The problem is that in-process caching is limited to the web process running on a single server. In a load-balanced configuration, each server is maintaining its own cache, limiting the efficiency and available size of the cache. Similarly, MySQL's query cache is limited to the server that the MySQL process is running on. The query cache is also limited in that it can only cache row results. With memcached you can set up a number cache servers which can store any type of serialized object and this data can be shared by all of the loadbalanced web servers. Cool, no?

To set up a memcached server, you simple download the daemon and run it with a few parameters. From the memcached web site:

First, you start up the memcached daemon on as many spare machines as you have. The daemon has no configuration file, just a few command line options, only 3 or 4 of which you'll likely use:

# ./memcached -d -m 2048 -l 10.0.0.40 -p 11211

This starts memcached up as a daemon, using 2GB of memory, and listening on IP 10.0.0.40, port 11211. Because a 32-bit process can only address 4GB of virtual memory (usually significantly less, depending on your operating system), if you have a 32-bit server with 4-64GB of memory using PAE you can just run multiple processes on the machine, each using 2 or 3GB of memory.

It's about as simple as it gets. There's no real configuration. No authentication. It's just a gigantor hash table. Obviously, you'd set this up on a private, non-addressable network. From there, the work of querying and updating the cache is completely up to the application designer. You are afforded the basic functions of set, get, and delete. Here's a simple example in PHP:

$memcache = new Memcache; $memcache->addServer('10.0.0.40', 11211); $memcache->addServer('10.0.0.41', 11211);

$value= "Data to cache";

$memcache->set('thekey', $value, 60);
echo "Caching for 60 seconds: $value <br>\n";

$retrieved = $memcache->get('thekey');
echo "Retrieved: $retrieved <br>\n";

The PHP library takes care of the dirty work of serializing any value you pass to the cache, so you can send and retrieve arrays or even complete data objects.

In your application's data layer, instead of immediately hitting the database, you can now query memcached first. If the item is found, there's no need to hit the database and assemble the data object. If the key is not found, you select the relevant data from the database and store the derived object in the cache. Similarly, you update the cache whenever your data object is altered and updated in the database. Assuming your API is structured well, only a few edits need to be made to dramatically alter the scalability and performance of your application.

I've linked to a few resources below where you can find more information on using memcached in your application. In addition to the documentation on the memcached web site, Todd Hoff has compiled a list of articles on memcached and summarized several memcached performance techniques. It's a pretty versatile tool. For those of you who've used memcached, give us a holler in the comments and share your tips and tricks.

Memcached
Strategies for Using Memcached and MySQL Better Together
Memcached and MySQL tutorial (PDF)

Posted by Jason Striegel | Aug 6, 2008 10:37 PM
Data, Linux, Linux Server, MySQL, Software Engineering | Permalink | Comments (1) | TrackBack | Digg It | Tag w/del.icio.us

July 26, 2008

MySQL performance tuning

Jay Pipes, MySQL employee and co-author Pro MySQL, gave a great presentation to Google employees which covers a number of techniques for tuning performance on MySQL. His examples include debugging and analyzing problems as well as best practices for table and index design, query and join operations, and server variable adjustments.

It's a little over 40 minutes long, but incredibly informative, whether you're a casual querier or a power MySQL user. Though some of this stuff is MySQL (or MyISAM or InnoDB) specific, the majority of the content is essential material for the average database application developer.

If you don't have time to sit through it (shame on you) or you're looking to jump right to a specific topic, there's a nice time-coded dissection of the talk over at Peteris Krumins' blog. There's something so appropriate about adding a search index to a video about MySQL optimization.

Performance Tuning Best Practices for MySQL
Video Index

Posted by Jason Striegel | Jul 26, 2008 12:11 PM
MySQL, SQL, Software Engineering | Permalink | Comments (0) | TrackBack | Digg It | Tag w/del.icio.us

May 8, 2007

MySQL database migration: latin1 to utf8 conversion

Until version 4.1, MySQL tables were encoded with the latin1 character set. By default, the character set is now utf8. This is a good thing in terms of non-latin character support, but if you're upgrading from an older database you may run into a lot of character encoding problems.

I've recently migrated several older databases from 4.0 to MySQL 5 and converted a few tables from latin1 to utf8, and I've put together a few tricks that might help you through the same. So far, I've basically run into three main issues: converting a table from the latin1 charset to utf8, upgrading a whole database set that was properly exported with mysqldump, and (the worst) migrating an entire set of database's MYI and MYD files that weren't properly exported.

Converting a latin1 table to utf8
If you've already migrated successfully to the new MySQL version, but you have an older latin1 encoded table and you simply want to change it's character set to utf8, it's a fairly simple exercise. First, export the table with mysqldump:

mysqldump -u username -p database --default-character-set=latin1 table > tableoutput.sql

First make a back up of that file and just edit it to adjust two things. Find the line that contains "SET NAMES latin1" and change that to "SET NAMES utf8". Then, look at the table definition and change "DEFAULT CHARSET=latin1" to "DEFAULT CHARSET=utf8". Finally, reimport the table:

cat tableoutput.sql | mysql --default-character-set=utf8 -u username -p database

When it's imported back in, it will properly be created as a utf8 table and you shouldn't have any character encoding problems. You can do this for a whole database with the same technique and using a search and replace to switch latin1 to utf8.

Upgrading a whole database set that was properly exported with mysqldump
This is a pretty simple scenario. You've exported your entire database from the previous MySQL version with something like this:

mysqldump --default-character-set=latin1 -u username -p database > dboutput.sql

You can then import it either as a latin1 table, or convert it to utf8 as we did above. Just make sure to adjust the "SET NAMES" and "CHARACTER SET" values appropriately, and set the "--default-character-set" parameter to utf8 or latin1, as appropriate.

Note that the default character set is now utf8 unless you change it in the my.cnf file. I've been on systems where it's been set to latin1 for compatability reasons, however, so you can't just assume this. Make sure that all of your database code (in your php, perl, whatever) issues the "SET NAMES utf8" SQL statement before issuing any further SQL commands. This will ensure that what you set in INSERTs and receive in SELECTs isn't mis-encoded. If you are using a legacy latin1 table, make sure you do the same, but with the latin1 setting.

Migrating an entire set of database's MYI and MYD files that weren't properly exported
I was a bonehead the other day and didn't properly export one of my server's databases before upgrading it to MySQL 5. After upgrading, all of my databases were corrupted, including the mysql database, meaning I couldn't even log in. The mysql_upgrade command didn't fix things properly, and it was all because MySQL assumed my tables from that older version were encoded in utf8.

It was, like, a bummer.

I don't know if there is an easier way, but this is how I managed to transition all those MYI and MYD files to the new database, without reverting to the older version and exporting properly.

First, you need to change the mysql config so that when it loads your old databases it assumes they are latin1 encoded. To do this, edit the my.cnf file. Find all the lines that say "default-character-set=utf8" and change them all to say "default-character-set=latin1".

Start up mysqld and have it ignore permissions:

mysqld_safe --skip-grant-tables &

Now, run "mysql_upgrade". When it finishes, do a SELECT on the mysql.user table. You should see that usernames and encoded passwords haven't been truncated in half and replaced with gobbledeguk.

Kill and restart the mysql server. You should be able to log in, meaning the mysql users table is, in fact, uncorrupted. You'll find that all your other tables are fine as well. One thing that you might notice is that the mysql_upgrade script will have updated the mysql databases to use utf8. All of your other databases are still in latin1, however, so you'll need to convert them to utf8 (if you desire) using the instructions above. Also, if you don't require legacy support for older applications, you should probably go back to the my.cnf file and change the default character set back to utf8 for everything.

Your thoughts
From this point forward, everyone should be using utf8 across the board, but it seems like making the jump is more painful than it should be. If you know any good MySQL character encoding tips and tricks, help out your fellow hacker. Share them in comments!

Posted by Jason Striegel | May 8, 2007 08:56 PM
MySQL | Permalink | Comments (5) | TrackBack | Digg It | Tag w/del.icio.us

April 26, 2007

MySQL Storage Engine for Amazon S3

In a MySQL Conference presentation with the subheading "How to store a hundred billion BLOBs without buying a single disk," Mark Atwood described a new open source storage engine that he built which allows you to store data in Amazons S3 service using MySQL.

It doesn't (yet) allow you to create any generic table schema you desire. Instead, you create tables with a simple key/value pair structure. For the scenario where storing data in S3 might be most useful, however, this is actually the data structure that you'd most likely need. You can use your local mysql server to store your indexes and data relationships, then outsource large BLOB data such as images or videos to the slower remote S3 service, identifying each resource by a unique key.

It looks like it's pretty simple to use. Here are a few example SQL statements that make use of the MySQL S3 engine (lifted from Mark's slides).

Connecting to the AWS server
CREATE SERVER 'MyAWSAcct'
FOREIGN DATA WRAPPER 'AWS'
OPTIONS
(USER 'aws id string',
PASSWORD 'aws secret string');


Creating a table
CREATE TABLE 'bierce' (
'word' VARCHAR(255) NOT NULL PRIMARY KEY,
'defn' BLOB)
CHARSET=utf-8
ENGINE=AWSS3
CONNECTION='awss3 DevilDictionary $server MyAWSAcct';


Insert and Select

INSERT INTO bierce (word, defn) VALUES
('AUTHOR', 'One noted for confusing bitterness with humor.');

SELECT defn FROM bierce WHERE word='WIT';


One note on selects: make sure you use a WHERE clause to select a single value by key or you'll be transfering the whole table across the network. This is not only slow, but it will cost you in bandwidth (your own bandwidth costs plus the S3 bandwidth fee).

You can view Mark's full presentation online. There's a powerpoint slideshow, and a seperate text file containing the slide notes -Link.

The full source of the MySQL S3 plugin is available for download as well -Link.

Posted by Jason Striegel | Apr 26, 2007 07:37 PM
MySQL | Permalink | Comments (0) | TrackBack | Digg It | Tag w/del.icio.us

February 26, 2007

Translate SQL Syntax Between Databases

SQL::Translator is an interesting Perl module that, among other things, can convert database table definitions to and from several DB platforms. Essentially, this could allow you to write and maintain your table definition code for a single platform, say MySQL, and then use Translator to output table structure into Oracle, Sybase, or PostgreSQL dialects.

Manipulation of data, such as SELECT, INSERT, UPDATE and DELETE are not yet supported, so you're still on your own there if you're writing platform agnostic code. That said, this is an incredibly useful tool. Just consider this example that Chris Dolan posted on use Perl:

MySQL understands this syntax:

create table book (
id int,
author_id int,
FOREIGN KEY fk_author_id (author_id) REFERENCES author (id)
) TYPE=InnoDB;

but not this nicer syntax (it silently ignores the "references" clause):

create table book (
id int,
author_id int references author (id),
) TYPE=InnoDB;

Perl to the rescue! I can write my schema in the latter syntax and use SQL::Translator to rewrite into the supported syntax.

References:

• MySQL foreign key syntax translator (4 line perl script!)
• SQL::Translator on CPAN

Posted by Jason Striegel | Feb 26, 2007 12:14 AM
Data, MySQL, Perl | Permalink | Comments (0) | TrackBack | Digg It | Tag w/del.icio.us

February 3, 2007

Simple Zip Code Geocoding

The ability to geocode, or translate into latitude and longitude, postal codes is a fairly useful hack to have in your programming toolbox. Quick and dirty zip geocoding allows you to do some neat things fairly efficiently and with a minimal amount of code. Though it's U.S. centric, it allows you to add location-based functionality to your apps without requiring any real personal information to be transfered or stored.

If your application only needs to convert a zip code (or any address) into a lat/lon coordinate, say for simple mapping purposes, the easiest solution is to use the Google Maps Geocoding API. In addition to the client-side javascript functionality, you can directly query the geocoding system from php using an http request like this:

https://maps.google.com/maps/geo?q=12345&output;=xml&key;=yourkeyhere

Just change 12345 to the zip (or any address) that you are looking up, and "yourkeyhere" should be your Google Map API key, which you can obtain here. Developer.com has a good PHP example for making use of the returned XML in your server-side code.

Often times, it's useful to be able to do zip lookups based on a geographic region. Maybe you want a list of all zip codes within a certain radius or bounding box. Applications for this could include clustering map items that are near eachother, or searching a database for items that are nearest to a given location. For this, it's really nice to have a MySQL table that contains zip codes along with their lat/lon coordinates. Fortunately, several people have compiled this sort of information from public domain data, and you can even download a full MySQL table dump here, for free.

At this point, it's a pretty simple matter to query the database for location-based information. For instance, let's say you have a web site with a guestbook that allows guests to leave their name and zip. You could easily whip up an application that tells your guests how many other guests are in their area by using a basic bounding box with a query like this:

SELECT guest.name from guest, zipcode
WHERE guest.zip = zipcode.zip
AND zipcode.lat < [maxlat] AND zipcode.lat > [minlat]
AND zipcode.lng < [maxlng] AND zipcode.lng > [minlng]

These are just a few ideas, but hopefully this should be enough to get you started. If you have some good ideas for other geocoding applications (or any mapping/gis hacks in general), please give us a shout in the comments.

Posted by Jason Striegel | Feb 3, 2007 09:42 PM
Ajax, Google Maps, Mapping, MySQL | Permalink | Comments (3) | TrackBack | Digg It | Tag w/del.icio.us

Bloggers

Welcome to the Hacks Blog!

Recent Posts

• Fish blimp
• Start Chrome in incognito mode
• High-speed photography with an Arduino
• Using web services with Google Docs
• iPhone NDA is dead, dev forums appear on Apple
• Simple stock quote grabbing with Perl
• Use large SD Cards on the Wii
• Scripting Photoshop with Javascript - anti-redaction example
• Boxee (and Hulu) on your Apple TV
• LED hard drive clock

www.flickr.com More photos in Hacks