MySQL On Multi-Core Machines

February 6, 2008 – 7:11 am

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 [...]

By Casey | Posted in Dispatches, Technology | Comments (0)



Apache, MySQL, and PHP on MacOS X

January 24, 2008 – 12:07 pm

p0ps Harlow tweeted something about trying to get an AMP environment running on his Mac. Conversation followed, and eventually I sent along an email that look sorta like this:
If you’re running 10.4 (I doubt it, but it’s worth mentioning because I’m most familiar with it), here’s how I’ve setup dozens of machines for web development [...]

By Casey | Posted in Technology | Comments (4)

WordPress to_ping Query Optimization

January 23, 2008 – 12:34 pm

The WordPress team has taken up the issue of performance optimization pretty seriously, and I look forward to the fruits of their efforts, but I’m also casting a critical eye on my own code. Thanks to caching and a hugely optimized query architecture, Scriblio is now performing better than ever, and I’m now looking at [...]

By Casey | Posted in Technology | Comments (0)

WordPress + Invalid URLs = Extra Database Queries

January 18, 2008 – 8:38 am

After reporting weirdness last week I finally sat down with a completely clean and virgin install of WordPress 2.3.2 and traced what happens when you make a permalink request for a non-existent URL.
Here are two sets of URLs to use as examples and context:

These are valid URLs:

http://site.org/archives/101
http://site.org/page-name

These are _not_ valid URLs:

http://site.org/archivezorz/101
http://site.org/favicon.ico

Valid URLs get parsed, the [...]

By Casey | Posted in Technology | Comments (0)

Bits Of MySQL Query Syntax I’ve Learned This Week

January 16, 2008 – 12:23 pm

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:

SELECT
(SELECT meta_value FROM wp_usermeta WHERE meta_key = ‘first_name’ AND [...]

By Casey | Posted in Technology | Comments (1)



Freaking MySQL Character Set Encodings

November 5, 2007 – 12:40 pm

Derek Sivers‘ plan, with all it’s bin2hex and regexp and back and forth between MySQL and PHP almost looks good compared to what I’m about to do. Really, why is it so difficult to go from latin1 (tables created back in MySQL 3) to utf8? Not only do you have to set the charset on [...]

By Casey | Posted in Technology | Comments (2)

Installing MySQL with YUM

June 12, 2007 – 5:14 pm

how to install and configure MySQL database server
yum, mysql, linux, yellow dog updater modified

By Casey | Posted in Blink | Comments (0)

Easy MySQL Performance Tips

June 5, 2007 – 12:19 pm

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 [...]

By Casey | Posted in Technology | Comments (2)

MySQL Error 28: Temp Tables And Running Out of Disk Space

May 1, 2007 – 7:32 pm

Bam: MySQL error 28, and suddenly my queries came to a stop.
Error 28 is about disk space, usually the disk space for temp tables. The first thing to do is figure out what filesystem(s) the tables are on. SHOW VARIABLES LIKE ?%dir%? will return a number of results, but the ones that matter are tmpdir [...]

By Casey | Posted in Technology | Comments (1)

MySQL Fulltext Tips

September 17, 2006 – 12:09 pm

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 [...]

By Casey | Posted in Blink, Technology | Comments (2)

Things I Need To Incorporate Into Various Projects

August 30, 2006 – 8:57 pm

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

development, memcached, mysql, [...]

By Casey | Posted in Technology | Comments (0)

Catching Bugs Before They Catch You

August 23, 2006 – 8:47 pm

I got itchy about magic quotes the other day because it’s the cause (through a fairly long cascade of errors) of some performance problems and runaways I’ve been seeing lately (pictured above).
But I deserve most of the blame for allowing a query like this to run at all:

SELECT type, data, count(*) AS hits
FROM wpopac_WPopac_bibs_atsk
WHERE data [...]

By Casey | Posted in Libraries & Networked Information, Technology | Comments (0)

T2000 Unboxed And Online

June 11, 2006 – 12:51 pm

My Sun T2000 is here, and with Cliff’s help it’s now patched, configured, and online. (Aside: what’s a Sun Happy Meal?)
I’ll second Jon’s assessment that Sun really should put some reasonable cable adapters in the box, as the the bundle of adapters necessary to make a null modem connection to the box is ridiculously out [...]

By Casey | Posted in Technology | Comments (0)

Solaris + AMP, ASAP

June 7, 2006 – 9:02 pm

A Solaris sysadmin I’m not. But now that I’ve finally got the Sun T2000 server I begged for a while back, I’ve got to ramp it up right quick.
The first task is to get a, um, LAMP environment up and running (SAMP?…oh, Sun wants us to call it AMPS). A bit of Googling turned up [...]

By Casey | Posted in Technology | Comments (4)

More bsuite Hacking

April 5, 2006 – 12:41 pm

Update: bugfix release b2v6 available.
Some conversations with Chow Kah Soon, who’s site is full of diversions from work , finally convinced encouraged me to solve some small problems that were giving him big trouble. Chow Kah Soon is in the lucky, but rare, position of having over 20,000 unique daily visitors to his site, [...]

By Casey | Posted in Technology | Comments (4)