mysql

MySQL has some powerful, and perhaps underused spatial extensions, but the most interesting functions are still unimplemented: “Note: Currently, MySQL does not implement these functions…”

Among those as-yet unimplemented functions is DISTANCE(). Alternatives can be found here and here, though neither is clean or simple. I wonder if a simple MBRContains() is good enough, though…

Found in the MySQL 5.0 Reference Manual:

• Related(g1,g2,pattern_matrix)

  Returns 1 or 0 to indicate whether the spatial relationship specified by pattern_matrix exists between g1 and g2. Returns –1 if the arguments are NULL. The pattern matrix is a string. Its specification will be noted here if this function is implemented.

(emphasis mine.)

The DevShed technical tour explains that MySQL can spawn new threads, each of which can execute on a different processor/core. What it doesn’t say is that a single thread can only execute on a single core, and if that thread locks a table, then no other threads that need that table can execute until the locking thread/query is complete. Short answer: MySQL works well on multi-core machines until you lock a table.

Watching the WordPress hacker list this week, a couple messages related to selecting information about users schooled me on MySQL syntax. I obviously knew the following would work, but I’d previously used the UNION syntax in similar situations and somehow hadn’t thought of writing it this way:

That’s much cleaner to my thinking, though I’ve no idea which is more optimal. When somebody replied asking for a solution that would work in pre-MySQL 5, this was the response:

And the lesson to me here is that I didn’t realize the syntax allowed us to match multiple conditions for the JOIN. Makes sense, but I just hadn’t thought of it. Thanks go to Phil Williams and Otto for tipping me to these.

Yes, I’m still trying to squeeze more performance out of MySQL. And since small changes to a query can make a big difference in performance…

Here are two really easy things to be aware of:

• Never do a COUNT(*) (or anything *, says Zach). Instead, replace the * with the name of the column you’re searching against (and is hopefully indexed). That way some queries can execute entirely in the keycache (while * forces MySQL to read every matching row from the table).
• When joining two large tables, but only searching against one, put the join statement at the end. Why join the two entire tables when you only have to join the matching rows?

I mention these because, well, I’ve known them forever, but upon seeing them again I realized I hadn’t really obeyed those simple rules in some of my queries.

Separately, there’s some pretty good info on what server variables affect what at mysqlperformanceblog too.

Peter Gulutzan, author of SQL Performance Tuning, writes in The Full-Text Stuff That We Didn’t Put In The Manual about the particulars of word boundaries, index structure, boolean searching, exact phrase searching, and stopwords, as well as offering a few articles for further reading (Ian Gilfillan’s “Using Fulltext Index in MySQL”, Sergei Golubchik’s “MySQL Fulltext Search”, Joe Stump’s “MySQL FULLTEXT Searching”). It’s one of a number of articles in the MySQL Tech Resources collection.

• memcached, a “highly effective caching daemon, …designed to decrease database load in dynamic web applications,” and the related PHP functions
• pspell PHP functions related to aspell and this pspell overview from Zend
• http_build_query, duh?
• current connected mysql threads * unix load average = system busy; reduce operations when $system_busy > $x

I’ve whittled things down to the point where the only baseline change from WordPress 2.0.2 is in the next_posts_link function of the wp-includes/template-functions-links.php file. The change is necessary because WPopac rewrites the SQL search queries in a way that’s incompatible with a piece of this function, but necessary for performance reasons.

Here’s how my version reads:

`

function next_posts_link($label='Next Page »', $max_page=0) {
	global $paged, $result, $request, $posts_per_page, $wpdb, $max_num_pages;
	if ( !$max_page ) {
			if ( isset($max_num_pages) ) {
				$max_page = $max_num_pages;
			} else {
				preg_match('#FROM\s(.*)\sGROUP BY#siU', $request, $matches);

				// added April 5 2006 by Casey Bisson to support WPopac
				// necessary because the preg_match above fails with some queries
				if(!$fromwhere)
					$fromwhere = $wpdb->posts;
				
				// changed April 5 2006 by Casey Bisson to speed the query by eliminating
				// the slow DISTINCT clause
				//$numposts = $wpdb->get_var(“SELECT COUNT(DISTINCT ID) FROM $fromwhere”);
				$numposts = $wpdb->get_var(“SELECT COUNT(*) FROM $fromwhere”);
				$max_page = $max_num_pages = ceil($numposts / $posts_per_page);
			}
	}
	if ( !$paged )
		$paged = 1;
	$nextpage = intval($paged) + 1;
	if ( (! is_single()) && (empty($paged) || $nextpage < = $max_page) ) {
		echo '<a href=“';
		next_posts($max_page);
		echo '”>'. preg_replace('/&([^#])(?![a-z]{1,8};)/', '&$1', $label) .'</a>';
	}
}

`

Zach suggested it last week, but it’s only now that I’ve gotten around to setting up MySQL’s slow query log.

It’s easy enough, you’ve just got to put a couple lines like this in your my.cnf (which is in /etc on my server):

log-slow-queries = /var/log/mysql/mysql-slow.log<br /> long_query_time = 10

This should get most people running, but this story in Database Journal offers a few more details. Potentially more useful is this guide to query and index optimization (though it’s probably a little out of date).

A couple notes from the past few days of tweaks and fixes:

• Hyper-threading has a huge effect on LAMP performance.
   
• From now on, I’ll have bad dreams about running MySQL without Query Caching in the way that I used to have nightmares about going to school wearing only my underwear. The difference is that big.
   
• WordPress rocks, but it has some queries that will kill large databases. I’m playing with baseline when I fix ’em, but it’s worth it.
   
• Being highly ranked for searches related to bears and bear lovers on the weekend that Grizzly Man airs on TV is both good and bad: it doubled my previous high for daily page-loads, but killed my server and taught me some lessons about sloppy coding.
   
• The support techs are Lunarpages continue to pretty much rock.
   
• Having friends who cut their teeth on high-performance PHP/MySQL and are willing to spend the night workshopping it with you is indispensable.
   
• It’s hard to beat the calming beauty of driving home through a snowfall on back roads at 2 AM carried by your life’s soundtrack.
   

I picked Plesk over CPanel as my server control panel because it was cheaper, looked better, and seemed to have all the features I wanted. What I didn’t know was that it came with PHP4 and MySQL3 at times when each was a major version ahead of that. When the good folks at my hosting provider tried to upgrade this, it conflicted with Plesk and they have to back off.

The answer, it seemed, was that I’d have to migrate from Plesk to CPanel to get those features. And now I’ve got a big database project, that’s looking more necessary than ever.

Why? Because MySQL 3.x doesn’t support query caching, boolean full-text searching, or complex subqueries.

In a simpler world, everything would be up to date and working, but in this world I’m trying to find a convenient time to migrate my stuff to CPanel.

I have over 1000 posts here at MaisonBisson, but even so, the table with all those posts is under 3MB. Now I’ve got a project with 150,000 posts — yes, 150,000 posts! — and the table is about 500MB. An associated table, structured sort of like WP’s postsmeta, has over 1.5 million records and weighs in at over 100MB (not including the 150MB of indexes).

Up to now I’ve been a “throw more hardware at it” sort of guy — and in a server with only 1GB of RAM, that’s probably the best solution — but I also think it’s time I learned some MySQL optimization tricks. Zach‘s been pushing me to get mytop installed for quite a while, and I finally got around to it.

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time::HiRes, you’ll get good real-time queries/second stats.

Here’s an interesting GeoPlace.com article on open source GIS tools, including GIS extensions to PosgreSQL and MySQL. Via The Map Room.