Category: MySQL

After a lot of development and tons of regression testing, Tera-WURFL 2.1.4 is finally ready for release. There has finally been a break in commits on the GitHub repo for more that a week, and no bug reports on the development branch.

Changes from Tera-WURFL 2.1.3

  • Switched to better XML Parser (old parser, SimpleXML will still be used if XMLReader is missing)
  • Improved matching for Apple, Android, Nokia, BlackBerry / RIM, DoCoMo, KDDI, LG and OperaMini
  • Allowed preloading a custom TeraWurflConfig so the default is ignored
  • Updated source documentation
  • Updated instructions
  • Greatly improved cmd_line_admin.php
  • Converted line endings to \n
  • Various bugfixes
  • Improved Remote Client
  • Added builtin regression testing (thanks digitalronin!). Tip: from the shell, go to the test/ dir and type “rake sanity” to make sure your patches are compatible with the loaded WURFL
  • Moved default config to “TeraWurflConfig.php.example” so your config isn’t overwritten
  • Fixed MSSQL DB Connector bug

If you rely on Android or BlackBerry detection, you will really want to get version 2.1.4 – it will significantly improve detection of these devices.

I’ve been delaying the Tera-WURFL 2.1.3 release since mid-July because of the large number of feature requests and improvements coming in, but the time has come to release it. I’m still waiting for the unit test code to stabilize a bit before I put it into the tree, but if you are interested in it, follow me on GitHub and you should see it soon.

Here’s a short list of features and improvements in Tera-WURFL 2.1.3:

  • Native support for MongoDB
  • Better support and performance for Microsoft SQL Server
  • Command line administration utility
  • Better webservice performance using JSON
  • Better Python client with JSON support
  • Improved performance in MySQL5
  • Device Images are available via the PHP webservice client
  • Loaded WURFL version is now tracked in the DB
  • MySQL connector allows for alternate port
  • Better overall detection

If you’re a command line junky like me, I think you’ll really like the CLI administration tool.  To use it, just go to your Tera-WURFL/admin/ directory and type php cmd_line_admin.php

# php cmd_line_admin.php

Tera-WURFL Stable 2.1.3
The command line WURFL updater for Tera-WURFL
Loaded WURFL: - 2010-09-09 04:08:06
Last Updated: Wed, 15 Sep 2010 20:38:59 -0500
Usage: php cmd_line_admin.php [OPTIONS]

Option                     Meaning
--help                    Show this message
--update=   The source of the WURFL file:
Update from your local wurfl.xml file:
Update from
--clearCache              Clear the device cache
--rebuildCache            Rebuild the device cache by redetecting all
cached devices using the current WURFL
--stats                   Show statistics about the Tera-WURFL Database

You can update the WURFL by passing the —-update=local or –update=remote parameter (crontab will like this):

# php cmd_line_admin.php --update=remote
Downloading WURFL from ...

done (/testtw/2.1.3/mongo/data/wurfl.xml: 14.80 MB [897.30 KB compressed])
Downloaded in 4.6609511375427 sec @ 1.58 Mbps

Database Update OK
Total Time: 22.146492004395
Parse Time: 3.8051941394806 (TeraWurflXMLParser_SimpleXML)
Validate Time: 0.024373054504395
Sort Time: 1.2780990600586
Patch Time: 0.50953578948975
Database Time: 8.9265999794006
Cache Rebuild Time: 7.6026899814606
Number of Queries: 2456
PHP Memory Usage: 54.49 MB
WURFL Version: - 2010-09-17 15:50:54 (Fri Sep 17 15:56:32 -0500 2010)
WURFL Devices: 13687
PATCH New Devices: 39
PATCH Merged Devices: 1

You can also see stats on the Tera-WURFL database:

# php cmd_line_admin.php --stats
Tera-WURFL Stable 2.1.3
Database Type: MongoDB (ver MongoDB 1.6.2)
Loaded WURFL: - 2010-09-17 15:50:54
Last Updated: Sat, 18 Sep 2010 15:28:05 -0500
Config File: /testtw/2.1.3/mongo/TeraWurflConfig.php
---------- Table Stats -----------

Rows:    13726
Devices: 6229
Size:    30.00 MB

Rows:    522
Size:    1.23 MB

Head over to and give version 2.1.3 a try!

I’ve been working hard on Tera-WURFL 2.1.3 lately.  I’ve been meaning to release it for over a month now, the problem is that I just keep finding improvements to make.  Since Tera-WURFL’s development is now hosted on GitHub, you can always see what I’m doing and grab a copy for yourself.  New in version 2.1.3 is a MongoDB Database Connector.  I keep getting pounded by people about NoSQL and I haven’t given it a fair chance until now.  Simon Harris from was nice enough to contribute the initial MongoDB connector, and I’ve spent a few days working on it and testing it against MySQL5.  At first the performance seems outstanding – almost double that of MySQL5 – but I noticed some inconsistencies with my tests, so I built a benchmarking script and an comparison tool that automatically tests one installation against another and checks for consistency.  I’ve tested three different database connectors against each other and my memcached-based Tera-WURFL Enterprise.

Testing Setup

In order to test the Database Connectors, I brought up a virtual machine on an under-utilized ESXi server (dual 6-core Opterons and 32GB FB-DDR3).

Virtual Machine Specs:

  • 4x Opteron 2.2GHz cores
  • 12GB FB-DDR3 RAM
  • 20GB of storage on a SAS array over MPIO iSCSI
  • Ubuntu Server 10.04.1
  • MySQL 5.1
  • MongoDB 1.7.0

This machine doesn’t have extremely fast CPUs, just a lot of them.  At any rate, the specs were consistent so I was able to get a reasonable test result.  In order to test the performance of the DBs, I ran about 66,000 unique user agents (these are UAs that are not easily matched and create a lot of DB load) through the connectors and measured the rate at which they detected them.

The results

The following results are the average speed at which the different DB Connectors detected the test user agents.  The two graphs show the difference between uncached and cached detections.  For each database, I ran the test with the all the WURFL capabilities loaded (blue) and with only [‘product_info’][‘is_wireless_device’] loaded (red) to get an idea of the performance benefit of using Tera-WURFL’s CAPABILITY_FILTER option.

Uncached Device Detection Performance in Tera-WURFL 2.1.3

* note: memcached is not capable of serving uncached detections, so it is left out of this test.

Cached Device Detection Performance in Tera-WURFL 2.1.3


I was very surprised by these results so I tested them repeatedly just to be sure.  I was so excited that MongoDB would be the clear winner here, but it just isn’t the case for uncached detections.  Mongo did fare well when serving the cached full capabilities – 63% higher than MySQL5.  MS SQL Server 2005 was particularly painful to test.  By comparison, MongoDB took about 8 minutes to churn though all 66,000 user agents, but my initial test with MS SQL Server was 3 hours and 8 minutes.  Since I tested all the other connectors on Ubuntu Server, and the MS SQL Server connector requires Microsoft’s PHP SQL Server Driver, I had to run the tests on a different VM.  On this VM I was getting between 1-3 uncached detections per second, but I moved the testing to my development laptop (2.8GHz Core 2 Duo, 4GB RAM and a Samsung 250GB SSD) and was able to achieve better results.  MySQL fared very well overall and remains at the top of the list.  I have not mentioned the MySQL Nested Set connector before, but it’s been included in Tera-WURFL for a couple versions now.  It just extends the MySQL5 connector and adds right and left values to all of the devices in the WURFL so it can return the complete fallback tree for a given WURFL ID in a single indexed query.  Unfortunately, this seems to provide a negligible improvement in speed.  I would still recommend it for environments where the webserver is on a different host than the database.

Look for Tera-WURFL 2.1.3 to be released within a couple weeks.  I’m trying to get some unit-testing code integrated before I release it.

*** UPDATE ***

I’ve retested MongoDB and MySQL5 again on my production server against a typical distribution of 44887 user agents.  The performance was much better, but the MySQL <-> MongoDB performance was proportional to the first results:

Uncached Cached
All Capabilities Min Capabilities All Capabilities Min Capabilities
MySQL 303 510 2735 7740
MongoDB 195 255 3182 6154

I recently created Tera-WURFL Explorer to allow people to browse through the WURFL, search for devices and upload images to the WURFL images collection. I originally used MySQL’s FULLTEXT index to let people search for devices, but quickly realized that it did not suit my needs. The main problem was that it does not index words smaller than what is specified in my.cnf (ft_min_word_len), and if you want to change it, you need to change it server-wide. This was not a good option for a large virtual host setup since it would affect all the FULLTEXT indices on the server; also, if you do change it, you need to reindex every FULLTEXT column in every database to prevent data corruption.

I did some research on search engines and eventually settled on Sphinx – mainly because it has a cool name, but also because there are some big-name success stories from companies like Craigslist who switched to it and never looked back.

Here’s how I installed it on Ubuntu 9.10:

First, you need to install the dependencies and download sphinx, then extract the archive and make it:

apt-get install g++ libmysql++-dev
cd /tmp
tar -zxvf sphinx-0.9.9.tar.gz
cd sphinx-0.9.9
./configure --prefix=/usr/local/sphinx
make install

Now all the sphinx-related files are in /usr/local/sphinx.
Next, I created a system user and group called “sphinx”:

adduser --system --group sphinx

Note: on RedHat-like systems, you can use “adduser -r -n sphinx”

Now, I created an init script for it. I would recommend downloading my init.d script.

mv searchd /etc/init.d/
chown root:root /etc/init.d/searchd
chmod 755 /etc/init.d/searchd

This script adds the following functionality:

# Start the Sphinx service
service searchd start
# Stop Sphinx
service searchd stop
# Check if Sphinx is running
service searchd status
# Reindex every Sphinx index (works while started or stopped)
service searchd reindex

Now we’ll add sphinx to the startup and use the config option to setup sphinx to run as the sphinx user:

update-rc.d searchd defaults
service searchd config

Note: on RedHat-like systems you can use “chkconfig –add searchd”

Lastly, you need to configure sphinx. I would copy the default config file and edit that one:

cp /usr/local/sphinx/sphinx.conf.dist /usr/local/sphinx/sphinx.conf

You can follow along with the comments in the file, or jump on the documentation site and figure out what all the settings do.

Now everything is setup and should work properly!
If you followed my directions and put the tarball in /tmp, the sphinx PHP and Python APIs and some examples are in /tmp/sphinx-0.9.9/api/. You should put a copy of the PHP or Python API somewhere else on the system so you can use it from your applications.

To see my use of the Sphinx search engine, take a look at this site:

I’m just about to release Tera-WURFL 2.1.0 (Stable) and I thought I’d give you an idea of what to expect. I was originally positioning this as a revisional update (i.e. 2.0.1), but decided to make it a minor version upgrade because it adds a lot of functionality and includes a few more settings than version 2.0.0.

Here’s what’s new:

1. Experimental support for Microsoft SQL Server 2005/2008: with the MSSQL2005 Database Connector, you can now use MS SQL Server as a Tera-WURFL backend! The support is experimental for now, although I’ve put it through my barrage of over 45,000 user agents and it works properly. The Reduction in String stored procedure still needs to be optimized since I just ported it from MySQL 5 to T-SQL. The MSSQL backend is considerably slower than MySQL, and I beleive it’s that procedure slowing it down.

2. SimpleDesktop Matching Engine: Tera-WURFL wasn’t originally designed to differnetiate between desktop and mobile browsers (either was WURFL for that matter), but with this release I’ve introduced the SimpleDesktop Matching Engine which, when enabled, uses keywords and regular expressions to detect 90% of desktop browsers without having to resort to searching the database for a matching WURFL entry. In my tests performance increased by 176% for detection of 45,000 actual unique user agents (both mobile and non-mobile). This feature also dramatically decreases the number of items in your cache by using a single cache item for all desktop browsers.

3. Capabilities Filter: I’ve been meaning to implement the Capability Filter for a long time, but two very high traffic clients of mine convinced me to sit down and get it finished. This adds a new setting in TeraWurflConfig.php called CAPABILITY_FILTER. If you set it to false it will be disabled and all the capabilities in the WURFL will be stored in the database and available to your scripts (this is the pre-2.1.0 behaviour). Here’s where the magic starts, you can set it to an array of the capabilities and groups of capabilities that you want to store and use, for example, if you just want the know what kind of device is visiting your site and whether or not it’s wireless, you can use this filter:

public static $CAPABILITY_FILTER = array(

This will shrink your device database by more than a factor of 10. I tested the filter with 20 capabilities against 45,000 unique user agents and it reduced the size of the cache from 645MB (without filtering) to 92MB (with filtering), then down to 24MB with both filtering and SimpleDesktop.

You can look forward to the Tera-WURFL 2.1.0 release around February 10, 2010.

Tera-WURFL is available at it’s usual location,

I ran a database of 23,902 unique user agents through both Tera-WURFL 2.0.0 RC4 and the new WURFL PHP API (1.0.1-rc2). Here are the results:

Total Time: 12.081017971039
Devices Processed: 23902
Total Queries: 23912

Total Time: 217.57795381546
Devices Processed: 23902
Total Queries: N/A

Both results were obtained after repeated testing to verify that the user agents were cached.

Tera-WURFL started in late 2006 as a personal project to make the WURFL PHP Tools faster by storing the device capabilities in a MySQL Database instead of flat files. It’s now late 2009 and I’ve finally got Tera-WURFL 2.0 up and running. As of now I’ve released RC4 and I’m working on RC5. Hopefully I can get version 2.0.0 Stable out before I leave for Iraq. Here’s a breakdown of the features of Tera-WURFL 2:

Rewrote some of the UserAgentMatchers and deleted others to bring Tera-WURFL on par with the Java WURFL API. With the introduction of desktop browser UserAgentMatchers, we no longer need to use the large web patch; instead, you can just use the 8KB one from (included). Also, I fixed some typos and bugs here and there. NOTE: if you are upgrading from version 2.0.0 RC1-RC3 you should delete all your database tables before you update. You can leave the terawurflcache table if you want to retain your cache. Although it will still technically work even if you don’t delete the tables, you will be orphaning some unecessary tables in your database.

Complete code-rewrite from the ground up. The 2.x version of Tera-WURFL is loosely based on a pre-release of the Java WURFL Evolution Library, but the API is taken from Tera-WURFL 1.5.2. The following is a list of features found in Tera-WURFL 2.0:

  • User Agent Matchers have been created for each of the major manufacturers. These allow for specific matching methods to be applied to the user agent like string searching, RIS (Reduction in String) and LD (Levenshtein Distance).
  • Multiple patch files are now supported. Tera-WURFL ships with the current wurfl.xml, web_browsers_patch.xml and custom_web_patch.xml. Patch files can be added to TeraWurflConfig.php by separating them with semicolons in the TeraWurflConfig::PATCH_FILE directive. Patch files are loaded in order from left to right on top of the WURFL file, so if you want to override every other patch file, specify it last.
  • The custom_web_patch.xml file can be edited from the Web Administration page, and allows you to easily add non-mobile user agents to the patch file. The devices with these user agents will be detected as generic_web_browser (non-mobile).
  • Persistent Caching means that your cached devices stay cached. When you update the WURFL file or your patches, your device cache is also updated via the new database.
  • Cache Browser allows you to see what devices are hitting your site and what their capabilities were detected as.
  • Installation Script is better than 1.5.2. Once you download Tera-WURFL and extract it, edit TeraWurflConfig.php then go to /admin/install.php and follow the directions to finish installation.
  • PHP short_open_tags are no longer required to run Tera-WURFL. PHP has this feature disabled by default now.
  • Conclusive vs. Inconclusive Matching. If a device is matched with the UserAgentMatcher’s primary matching method it is considered a conclusive match, if it is detected via a recovery matcher or by the CatchAllMatcher it is an inconclusive match. This information is available via the tera_wurfl capability group.
  • tera_wurfl Capability Group. The TeraWurfl->Capabilities array now contains a group called “tera_wurfl”. This group contains the following Tera-WURFL related information:
    • num_queries – the number of database queries required to lookup the device.
    • actual_root_device – the WURFL ID of the actual device (not subrevision or generic), this can be null.
    • match_type – either conclusive or inconclusive.
    • matcher – the name of the UserAgentMatcher that detected the device.
    • match – whether or not there was an actual match. If there was no match, Tera-WURFL guessed which generic device is most similar to the device.
    • lookup_time – the time in seconds that it took to detect the device.
    • fall_back_tree – the complete fallback tree that built the capabilities of the device. This is a list of all the WURFL IDs from the detected device down to the base generic device.

Example Script

// Include the Tera-WURFL file
// Instantiate the Tera-WURFL object
$wurflObj = new TeraWurfl();
// Get the capabilities from the object
$matched = $wurflObj->GetDeviceCapabilitiesFromAgent();
// Show whether there was a conclusive match
if($matched){echo "Match found";}else{echo "Match NOT found";}
// Print the capabilities array
echo "<pre>".htmlspecialchars(var_export($wurflObj->capabilities,true))."</pre>";

Thanks to everyone who helped me test Beta 1.4.5RC1 – this pre-release was the stepping stone to version Stable 1.5.0.
I was lucky to get some help testing 1.4.5RC1 from a client that gets very high traffic. After upgrading from 1.4.4 to 1.4.5RC1, his server’s mysqld process went from 20-40% down to 6%!

Here’s a quote from him:
“The system gets over 2 million WAP hits per day … The impact on the server is definitely significant. Before this, mysqld was using between 20% and 40% of the CPU, and now it’s down to 6% or less. Load average on the box was averaging 3.84 before, and now it’s averaging 2.72.”

I actually released “Stable 1.5.0” a few weeks ago and since released “Stable 1.5.1” but apparently I never applied the changes to the main site so you wouldn’t have seen 1.5.1 in the downloads section. It’s up now so check it out! It features a very high speed caching system that caches the entire device capabilities tree for top performance.

I’m currently looking for suggestions for new features and improvements that I can include in future releases of Tera-WURFL. If you have something you’d like to see please let me know!

To download Tera-WURFL visit

After many many hours hunched over my keyboard, I have finished a major revamp of the Tera-WURFL code. Here's what's new:

Completely rewrote the error logging system and verified it's operation after a bug was discovered (thanks Neil!)
Added many features to the web administration console – seriously – check out the online demo!
Changed default DATADIR to the included 'data' directory
Completely rewrote the README file to include detailed installation instructions and other useful info
Optimized the clean installation process – now Tera-WURFL has a brain!
Included the current stable release of the wurfl.xml file so you don't need to download it
Included database statistics, log file monitoring and global configuration in new web interface
Changed default log level to LOG_WARNING instead of LOG_ERR
Described in great detail the purpose of the different database tables

If you are using Tera-WURFL 1.4.3 or earlier I would highly recommend that you upgrade to 1.4.4 due to some fairly significant bugs!

Tera-WURFL Website

Let me introduce my latest and greatest software: Tera-WURFL! What the heck is that?!?! Well, ‘Tera’ is just plain cool because it comes from Tera Technologies 🙂 and WURFL is the ‘Wireless Universal Resource FiLe. After a while of testing the WURFL PHP Tools by Andrea Trasatti, I began to notice some major downfalls in the package’s performance. Although the authors did all they could do to make it as fast as possible, the package still uses a file based caching method (albeit a fast one) so store device information. I found the lack of database support disturbing and decided to rewrite the class in my free time and release it myself [:)]. So here it is – Tera-WURFL. My version uses MySQL >= 4.1 and PHP >= 4.3 and performs up to 15x faster than the file based version! I am currently tied down trying to make non-free software and other things so I don’t have a whole lot of extra time to work on Tera-WURFL – sorry.

Basically, this software sits on your webserver and when a web browser requests your site, Tera-WURFL determines whether it’s a desktop web browser or a mobile WAP device, and if it is a mobile device it gives you a TON of information about it like the Make, Model, Screen Size, Ringtone Formats, Image Formats, etc… It’s really quite increadible!

Oh yeah – also uses this detection method to deliver rich media content to handheld devices.