Ramblings of a web guy (Tag: mysql)

PHP 5.3 and mysqlnd - Unexpected results

brianlmoon — Tue, 03 Aug 2010 08:00:00 -0500

I have started seriously using PHP 5.3 recently due to it finally making it into Portage. (Gentoo really isn't full of bleeding edge packages people.) I have used mysqlnd a little here and there in the past, but until it was really coming to my servers I did not put too much time into it.

What is mysqlnd?

mysqlnd is short for MySQL Native Driver. In short, it is a driver for MySQL for PHP that uses internal functions of the PHP engine rather than using the externally linked libmysqlclient that has been used in the past. There are two reasons for this. The first reason is licensing. MySQL is a GPL project. The GPL and the PHP License don't play well together. The second is better memory management and hopefully more performance. Being a performance junky, this is what peaked my interests. Enabling mysqlnd means it is used by the older MySQL extension, the newer MySQLi extension and the MySQL PDO driver.

New Key Feature - fetch_all

One new feature of mysqlnd was the fetch_all method on MySQLi Result objects. At both dealnews.com and in Phorum I have written a function to simply run a query and fetch all the results into an array and return it. It is a common operation when writing API or ORM layers. mysqlnd introduces a native fetch_all method that does this all in the extension. No PHP code needed. PDO already offers a fetchAll method, but PDO comes with a little more overhead than the native extensions and I have been using mysql functions for 14 years. I am very happy using them.

Store Result vs. Use Result

I have spoken in the past (see my slides and interview: MySQL Tips and Tricks) about using mysql_unbuffered_query or using mysqli_query with the MYSQLI_USE_RESULT flag. Without going into a whole post about that topic, it basically allows you to stream the results from MySQL back into your PHP code rather than having them buffered in memory. In the case of libmysqlclient, they could be buffered twice. So, my natural thought was that using MYSQLI_USE_RESULT with fetch_all would yield the most awesome performance ever. The data would not be buffered and it would get put into a PHP array in C instead of native code. The code I had hoped to use would look like:

$res = $db->query($sql, MYSQLI_USE_RESULT);
$rows = $res->fetch_all(MYSQLI_ASSOC);

But, I quickly found out that this does not work. For some reason, this is not supported. fetch_all only works with the default which is MYSQLI_STORE_RESULT. I filed a bug which was marked bogus. Which I put back to new because I really don't see a reason this should not work other than a complete oversight by the mysqlnd developers. So, I started doing some tests in hopes I could show the developers how much faster using MYSQLI_USE_RESULT could be. What happened next was not expected. I ended up benchmarking several different options for fetching all the rows of a result into an array.

Test Data

I tested using PHP 5.3.3 and MySQL 5.1.44 using InnoDB tables. For test data I made a table that has one varchar(255) column. I filled that table with 30k rows of random lengths between 10 and 255 characters. I then selected all rows and fetched them using 4 different methods.

In addition, I ran this test with mysqlnd enabled and disabled. For mysqli_result::fetch_all, only mysqlnd was tested as it is only available with mysqlnd. I ran each test 6 times and threw out the worst and best result for each test. FWIW, the best and worst did not show any major deviation for any of the tests. For measuring memory usage, I read the VmRSS value from Linux's /proc data. memory_get_usage() does not show the hidden memory used by libmysqlclient and does not seem to show all the memory used by mysqlnd either.

So, that is what I found. The memory usage graphs are all what I thought they would be. PDO has more overhead by its nature. Storing the result always uses more memory than using it. mysqli_result::fetch_all uses less memory than the loop, but more than directly using the results.

There are some very surprising things in the timing graphs however. First, the tried and true method of using the result followed by a loop is clearly still the right choice in libmysqlclient. However, it is a horrible choice for mysqlnd. I don't really see why this is so. It is nearly twice as slow. There is something really, really wrong with MYSQLI_USE_RESULT in mysqlnd. There is no reason it should ever be slower than storing the result and then reading it again. This is also evidenced in the poor performance of PDO (since even PDO uses mysqlnd when enabled). PDO uses an unbuffered query for its fetchAll method and it too got slower. It is noticably slower than libmysqlclient. The good news I guess is that if you are using mysqlnd, the fetch_all method is the best option for getting all the data back.

Next Steps

My next steps from here will be to find some real workloads that I can test this on. Phorum has several places where I can apply real world pages loads to these different methods and see how they perform. Perhaps the test data is too small. Perhaps the number of columns would have a different effect. I am not sure.

If you are reading this and have worked on or looked at the mysqlnd code and can explain any of it, please feel free to comment.

Want to understand MySQL indexes?

brianlmoon — Tue, 15 Jun 2010 17:14:40 -0500

I have introduced some new people to MySQL recently and had to back track the years to figure out how I learned what I learned about MySQL indexes. A quick way to get up to speed about MySQL indexes is these three podcasts by Sheeri Cabral.

Those three episodes do a good job of explaining how indexes work so that you have a better understanding of how MySQL indexes find your data.

MySQL Conference Review

brianlmoon — Sat, 17 Apr 2010 10:54:52 -0500

I am back home from a good week at the 2010 O'Reilly MySQL Conference & Expo. I had a great time and got to see some old friends I had not seen in a while.

Oracle gave the opening keynote and it went pretty much like I thought it would. Oracle said they will keep MySQL alive. They talked about the new 5.5 release. It was pretty much the same keynote Sun gave last year. Time will tell what Oracle does with MySQL.

The expo hall was sparse. Really sparse. There were a fraction of the booths compared to the past. I don't know why the vendors did not come. Maybe because they don't want to compete with Oracle/Sun? In the past you would see HP or Intel have a booth at the conference. But, with Oracle/Sun owning MySQL, why even try. Or maybe they are not allowed? I don't know. It was just sad.

I did stop by the Maatkit booth and was embarrassed to tell Baron (its creator) I was not already using it. I had heard people talk about it in the past, but never stopped to see what it does. It would have only saved me hours and hours of work over the last few years. Needless to say it is now being installed on our servers. If you use MySQL, just go install Maatkit now and start using it. Don't be like me. Don't wait for years, writing the same code over and over to do simple maintenance tasks.

Gearman had a good deal of coverage at the conference. There were three talks and a BoF. All were well attended. Some people seemed to have an AHA! moment where they saw how Gearman could help their architecture. I also got to sit down with the PECL/gearman maintainers and discuss the recent bug I found that is keeping me from using it.

I spoke about Memcached as did others. Again, there was a BoF. It was well attended and people had good questions about it. There seemed to be some FUD going around that memcached is somehow inefficient or not keeping up with technology. However, I have yet to see numbers or anything that proves any of this. They are just wild claims by people that have something to sell. Everyone wants to be the caching company since there is no "Memcached, Inc.". There is no company in charge. That is a good thing, IMO.

That brings me to my favorite topic for the conference, Drizzle. I wrote about Drizzle here on this blog when it was first announced. At the time MySQL looked like it was moving forward at a good pace. So, I had said that it would only replace MySQL in one part of our stack. However, after what, in my opinion, has been a lack of real change in MySQL, I think I may have changed my mind. Brian Aker echoed this sentiment in his keynote address about Drizzle. He talked about how MySQL AB and later Sun had stopped focusing on the things that made MySQL popular and started trying to be a cheap version of Oracle. That is my interpretation of what he said, not his words.

Why is Drizzle different? Like Memcached and Gearman, there is no "Drizzle, Inc.". It is an Open Source project that is supported by the community. It is being supported by companies like Rackspace who hired five developers to work on it. The code is kept on Launchpad and is completely open. Anyone can create a branch and work on the code. If your patches are good, they will be merged into the main branch. But, you can keep your own branch going if you want to. Unlike the other forks, Drizzle has started over in both the code and the community. I personally see it as the only way forward. It is not ready today, but my money is on Drizzle five or ten years from now.

Logging with MySQL

brianlmoon — Wed, 24 Mar 2010 00:20:12 -0500

I was reading a post by Dathan Vance Pattishall titled "Cassandra is my NoSQL solution but..". In the post, Dathan explains that he uses Cassandra to store clicks because it can write a lot faster than MySQL. However, he runs into problems with the read speed when he needs to get a range of data back from Cassandra. This is the number one problem I have with NoSQL solutions.

SQL is really good at retrieving a set of data based on a key or range of keys. Whereas NoSQL products are really good at writing things and retrieving one item from storage. When looking at redoing our architecture a few years ago to be more scalable, I had to consider these two issues. For what it is worth, the NoSQL market was not nearly as mature as it is now. So, my choices were much more limited. In the end, we decided to stick with MySQL. It turns out that a primary or unique key lookup on a MySQL/InnoDB table is really fast. It is sort of like having a key/value storage system. And, I can still do range based queries against it.

But, back to Dathan's problem: clicks. We store clicks at dealnews. Lots of clicks. We also store views. We store more views than we do clicks. So, lots of views and lots of clicks. (Sorry for the vague numbers, company secrets and all. We are a top 1,000 Compete.com site during peak shopping season.) And we do it all in MySQL. And we do it all with one server. I should disclose we are deploying a second server, but it is more for high availability than processing power. Like Dathan, we only use about the last 24 hours of data at any given time. There are three keys for us doing logging like this in MySQL.

Use MyISAM

MyISAM supports concurrent inserts. Concurrent inserts means that inserts can add rows to the end of a table while selects are being performed on other parts of the data set. This is exactly the use case for our logging. There are caveats with range queries as pointed out by the MySQL Performance Blog.

Rotating tables

MySQL (and InnoDB in particular) really sucks at deleting rows. Like, really sucks. Deleting causes locks. Bleh. So, we never delete rows from our logging tables. Instead, nightly we rotate the tables. RENAME TABLE is an (near) atomic process in MySQL. So, we just create a new table.

create table clicks_new like clicks;
rename table clicks to clicks_2010032500001, clicks_new to clicks;

Tada! We now have an empty table for today's clicks. We now drop any table with a date stamp that is longer than x days old. Drops are fast, we like drops.

For querying these tables, we use UNION. It works really well. We just issue a SHOW TABLES LIKE 'clicks%' and union the query across all the tables. Works like a charm.

Gearman

So, I get a lot of flack at work for my outright lust for Gearman. It is my new duct tape. When you have a scalability problem, there is a good chance you can solve it with Gearman. So, how does this help with logging to MySQL? Well, sometimes, MySQL can become backed up with inserts. It happens to the best of us. So, instead of letting that pile up in our web requests, we let it pile up in Gearman. Instead of having our web scripts write to MySQL directly, we have them fire Gearman background jobs with the logging data in them. The Gearman workers can then write to the MySQL server when it is available. Under normal operating procedure, that is in near real time. But, if the MySQL server does get backed up, the jobs just queue up in Gearman and are processed when the MySQL server is available.

BONUS! Insert Delayed

This is our old trick before we used Gearman. MySQL (MyISAM) has a neat feature where you can have inserts delayed until the table is available. The query is sent to the MySQL server and it answers with success immediately to the client. This means your web script can continue on and not get blocked waiting for the insert. But, MySQL will only queue up so many before it starts erroring out. So, it is not as fool proof as a job processing system like Gearman.

Summary

To log with MySQL:

Use MyISAM with concurrent inserts
Rotate tables daily and use UNION to query
Use delayed inserts with MySQL or a job processing agent like Gearman

Happy logging!

PS: You may be asking, "Brian, what about Partitioned Tables?" I asked myself that before deploying this solution. More importantly, in IRC I asked Brian Aker about MySQL partitioned tables. I am paraphrasing, but he said that if I ever think I might alter that table, I would not trust it with the partitions in MySQL. So, that kind of turned me off of them.

Using ini files for PHP application settings

brianlmoon — Tue, 19 Jan 2010 17:24:35 -0600

At dealnews we have three tiers of servers. First is our development servers, then staging and finally production. The complexity of the environment increases at each level. On a development server, everything runs on the localhost: mysql, memcached, etc. At the staging level, there is a dedicated MySQL server. In production, it gets quite wild with redundant services and two data centers.

One of the challenges of this is where and how to store the connection information for all these services. We have done several things in the past. The most common thing is to store this information in a PHP file. It may be per server or there could be one big file like:








if(DEV){


    $server = "localhost";


} else {


    $server = "10.1.1.25";


}





?>

This gets messy quickly. Option two is to deploy a single file that has the settings in a PHP array. And that is a good option. But, we have taken that one step further using some PHP ini trickeration. We use ini files that are loaded at PHP's startup and therefore the information is kept in PHP's memory at all times.

When compiling PHP, you can specify the --with-config-file-scan-dir to tell PHP to look in that directory for additional ini files. Any it finds will be parsed when PHP starts up. Some distros (Gentoo I know) use this for enabling/disabling PHP extensions via configuration. For our uses we put our custom configuration files in this directory. FWIW, you could just put the above settings into php.ini, but that is quite messy, IMO.

To get to this information, you can't use ini_get() as you might think. No, you have to use get_cfg_var() instead. get_cfg_var returns you the setting, in php.ini or any other .ini file when PHP was started. ini_get will only return values that are registered by an extension or the PHP core. Likewise, you can't use ini_set on these variables. Also, get_cfg_var will always reflect the initial value from the ini file and not anything changed with ini_set.

So, lets look at an example.



; db.ini


[myconfig]


myconfig.db.mydb.db     = mydb


myconfig.db.mydb.user   = user


myconfig.db.mydb.pass   = pass


myconfig.db.mydb.server = host

This is our ini file. the group in the braces is just for looks. It has no impact on our usage. Because this is parsed along with the rest of our php.ini, it needs a unique namespace within the ini scope. That is what myconfig is for. We could have used a DSN style here, but it would have required more parsing in our PHP code.










/**


 * Creates a MySQLi instance using the settings from ini files


 *


 * @author     Brian Moon 


 * @copyright  1997-Present dealnews.com, Inc.


 *


 */





class MyDB {





    /**


     * Namespace for my settings in the ini file


     */


    const INI_NAMESPACE = "dealnews";





    /**


     * Creates a MySQLi instance using the settings from ini files


     *


     * @param   string  $group  The group of settings to load.


     * @return  object


     *


     */


    public static function init($group) {





        static $dbs = array();





        if(!is_string($group)) {


            throw new Exception("Invalid group requested");


        }





        if(empty($dbs["group"])){





            $prefix = MyDB::INI_NAMESPACE.".db.$group";





            $db   = get_cfg_var("$prefix.db");


            $host = get_cfg_var("$prefix.server");


            $user = get_cfg_var("$prefix.user");


            $pass = get_cfg_var("$prefix.pass");





            $port = get_cfg_var("$prefix.port");


            if(empty($port)){


                $port = null;


            }





            $sock = get_cfg_var("$prefix.socket");


            if(empty($sock)){


                $sock = null;


            }





            $dbs[$group] = new MySQLi($host, $user, $pass, $db, $port, $sock);





            if(!$dbs[$group] || $dbs[$group]->connect_errno){


                throw new Exception("Invalid MySQL parameters for $group");


            }


        }





        return $dbs[$group];





    }





}





?>

We can now call DB::init("myconfig") and get a mysqli object that is connected to the database we want. No file IO was needed to load these settings except when the PHP process started initially. They are truly constant and will not change while this process is running.

Once this was working, we created separate ini files for our different datacenters. That is now simply configuration information just like routing or networking configuration. No more worrying in code about where we are.

We extended this to all our services like memcached, gearman or whatever. We keep all our configuration in one file rather than having lots of them. It just makes administration easier. For us it is not an issue as each location has a unique setting, but every server in that location will have the same configuration.

Here is a more real example of how we set up our files.



[myconfig.db]


myconfig.db.db1.db         = db1


myconfig.db.db1.server     = db1hostname


myconfig.db.db1.user       = db1username


myconfig.db.db1.pass       = db1password





myconfig.db.db2.db         = db2


myconfig.db.db2.server     = db2hostname


myconfig.db.db2.user       = db2username


myconfig.db.db2.pass       = db2password





[myconfig.memcache]


myconfig.memcache.app.servers    = 10.1.20.1,10.1.20.2,10.1.20.3


myconfig.memcache.proxy.servers  = 10.1.20.4,10.1.20.5,10.1.20.6





[myconfig.gearman]


myconfig.gearman.workload1.servers = 10.1.20.20


myconfig.gearman.workload2.servers = 10.1.20.21

Forums are crap. Can we get some help?

brianlmoon — Mon, 12 Oct 2009 10:01:44 -0500

Amy Hoy has written a blog post about why forums are crap. And she is right. Forum software does not always do a good job of helping people communicate. I have worked with Amy. She did a great analysis of dealnews.com that led to our new design. So, she is not to be ignored.

However, as a software developer (Phorum), I see a lot of problems and no answers. And it is not all on the software. Web site owners use forums to solve problems that they really, really suck at. Ideally, every web site would be very unique for their audience. They would use a custom solution that fits a number of patterns that best solves their problem. However, most web site owners don't want to take the time to do such things. They want a one stop, drop in solution. See the monolith that is vBulletin, scary.

And what if a forum is the best solution? Well, software developers, in general, are not good designers. They don't think like normal people. And they don't see their applications as a whole, but as pieces that do jobs. The forum software market has been run by software developers for over 10 years. Most of them all are still copies of what UBB was 13 years ago. And software (like Phorum) that has tried to be different is shunned by the online communities of the world because they don't work/look/feel like every other forum software on the planet.

So, as software developers, what are we to do? We want to make great software. We want to help our users help their users. But, what we have been doing for 10+ years has only been adequate. As the leader of an open source forum software project, I am open to any and all ideas.

Wordcraft 0.10 available

brianlmoon — Mon, 10 Aug 2009 00:12:20 -0500

The latest package of Wordcraft, the PHP/MySQL based blog software that runs this site, is available for download from Google Code. Just some minor bug fixes and cosmetic stuff. Its getting a little use in the wild. That is always fun to see.

The rise of the GLAMMP stack

brianlmoon — Fri, 22 May 2009 11:34:39 -0500

First there was LAMP. But are you using GLAMMP? You have probably not heard of it because we just coined the term while chatting at work. You know LAMP (Linux, Apache, MySQL and PHP or Perl and sometimes Python). So, what are the extra letters for?

The G is for Gearman - Gearman is a system to farm out work to other machines, dispatching function calls to machines that are better suited to do work, to do work in parallel, to load balance lots of function calls, or to call functions between languages.

The extra M is for Memcached - memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.

More and more these days, you can't run a web site on just LAMP. You need these extra tools (or ones like them) to do all the cool things you want to do. What other tools do we need to work into the acronym? PostgreSQL replaces MySQL in lots of stacks to form LAPP. I guess Drizzle may replace MySQL in some stacks soon. For us, it will likely be added to the stack. Will that make it GLAMMPD? We need more vowels! If you are starting the next must use tool for running web sites on open source software, please use a vowel for the first letter.

Scaling for the Expected and Unexpected - Speaking at Velocity

brianlmoon — Thu, 07 May 2009 11:57:27 -0500

Last year I was surprised to be going to Velocity. Read the post, it was an adventure. But, I really like the conference. It is the perfect conference for me. While a good majority of my work is done coding PHP/MySQL apps, I tend to focus on architecture, frameworks, performance and that kind of stuff. So, a web performance and operations conference is just perfect.

Last year, I was on a panel with some great guys. I was able to share just a bit about my experience dealing with the instant success of a web site. This year, my proposal was accepted to talk more about dealing with success of a web site. The talk will be focused on my experience at dealnews.com and from working with power users for Phorum. Here is the summary:

Lots of people talk about scaling and performance. But, are they preparing for all the things that could happen? There are multiple problems and there is not one solution to solve them all.

Everything is running fine and BAM! – your site is linked from the front page of Yahoo! What do you do? How can you handle that sudden rush of traffic. Requests per second are running 5x normal levels. Servers have CPU spikes. Daemons are hitting the maximums. You are running out of bandwidth. How could you have been prepared for this? What are the tools and techniques for this type of sudden rush?

Or, lets say you have just come out of a meeting where everyone discovered that your site is growing in traffic 70% – 80% year over year. That means that 1 million page views this month will be nearly 3 million this time in 2 years. How can you plan for that? You don’t want to redesign the whole architecture every 2 years. What methods could be used to deal with this constant long term growth?

While there is no magic bullet for either of these scenarios, there are techniques used by many sites out there to help you get through these situations. This session will cover some of these techniques and talk about their pros and cons.

I must admit, this if the first time since 2000 that I am a little intimidated to speak at a conference. The people that present and attend Velocity are so awesome. I just hope I don't disappoint.

Wordcraft 0.9.1 available

brianlmoon — Wed, 08 Apr 2009 08:00:00 -0500

There are several key changes in Wordcraft 0.9.1. The two big things are:

Tokens on post forms in the admin to help ward off CSRF attacks.
Database schema updates automated.

The first comes as a result of us doing the same work on Phorum recently. I realized I needed the same protection in Wordcraft. The second was done out of neccesity as I changed the datetime fields in the database schema into int fields. Not sure why I ever made them datetime fields. Unix timestamps are much easier to work with. It saves many strtotime() calls and will make eventual time zone settings much easier to implement.

In addition to those two big ones, there were some notable small ones:

HTML 4.01 validation fixes
Ensuring UTF-8 on all encoding function calls
Protection against hitting the back button when writing a post (most annoying on Macs as the back button and the beginning of line keystroke is the same).

And there were other a few other bug fixes.

I will or course need many more testers and users before I can ever declare this software as stable. If you need a simple blog, give it a try.

About Wordcraft
Wordcraft aims to be a simple, lightweight blogging application. Wordcraft is written exclusively for PHP 5+ and MySQL 5.0+ using only the PHP mysqli extension, UTF-8, and HTML 4.01 to achieve that simpleness.

Wordcraft 0.8 available

brianlmoon — Mon, 23 Feb 2009 08:00:00 -0600

I am pleased to announce the release of Wordcraft 0.8. I have managed to release about once a month since November. I also have actually gotten some feedback and tickets posted. Thanks to those that have tried it out.

I have decided to go back to YUI's Editor. I tried TinyMCE in the last release. But, using it full time I found it messed with my HTML too much for my liking. When I would switch to raw HTML mode and add something like a

 tag, it would
be lost when saving the data back into the WYSIWYG editor.



I also converted the admin HTML to HTML 4.01 Transitional.  I
never use XHTML anymore these days.  So, I was writing invalid
XHTML inadvertantly.



I worked on the session handling some more in this release. 
Users should stay logged in to the admin better now.



I put comment blocks in all the files and documented every
function.  This should help anyone wanting to dig in and help
out.



I fixed several bugs reported by users (or maybe just testers, not
sure).  Thanks for that and keep the feedback
coming.



Altering several things in a MySQL table at one time
brianlmoon — Sun, 22 Feb 2009 18:00:00 -0600
To some kick ass DBA or someone that works on MySQL internals, this
is probably a no brainer.  But, for just joe schmoe power user
types, we wonder about these things.  We could not find the
answer on the internet, so I decided to blog about it.  We
expected the result we found, but I think it's good
information.



Have you ever had to make changes to several indexes or columns in
a table?  With Phorum, we
keep a series of scripts that run queries to upgrade the database
as we make changes.  Recently I had to delete about 12 keys
and add about 7 back in their place.  I initially thought to
make two sql statements.  One to delete indexes and one to
create new ones.  But, Thomas, one of the other developers,
wondered if that was really any better.  So, I decided to run
some tests.



First I altered a table with 70k rows, dropping one key.  That
took 16 seconds.  I then added a key to that same table. 
Again, about 16 seconds.  So, I then dropped a key and added a
key in one query.  Again 16 seconds.  So, I decided to go
for the ultimate challenge.  I dropped 12 keys and added back
12 keys all in one query.  TADA!  16 or so seconds. 
The table was an InnoDB table, so I repeated after converting the
table to MyISAM.  Again, all the alters took about the same
time, regardless of the number of changes.



Update


I was told in the comments that the data was
not big enough for a real test.  I hold to the fact that
because, as Eric Bergen pointed out, the most time is taken in
copying the data, the data size is not that important here. 
However, I tried it out on a table with 750k rows just to
see.  Dropping 4 keys on a table with 12 keys took 1min
46sec.  Adding 4 keys to the same table, (now with 8 keys)
took 1min 52sec.  Adding 4 keys and dropping 4 keys in one
alter table took 1min 49sec.  So, looks like I was
right.  For fun, I decided to drop all the keys except the
primary.  That only took 28sec.  That is what I
guessed.  The slow part is copying the table to a new
table.  That includes filling in indexes.  It is much
faster to write a table with indexes than it is a table with 12
indexes.  However, that was not my use case and not the reason
for my testing.  But, it is good information to attach to this
blog post.


Wordcraft 0.7 available
brianlmoon — Mon, 12 Jan 2009 08:00:00 -0600

    I am happy to announce Wordcraft
    0.7.  There are two big changes in this release. 
    On the front end, I added a simple search.  It just uses a
    LIKE clause.  But, I figure a lot of blogs never reach
    1,000 posts.  Even at 10,000 posts, a LIKE would not be
    too bad.  On the backend, I have switched the post editor
    to TinyMCE. YUI's
    editor is decent, but it needs polish.  Perhaps my time
    with Wordpress just made me more familiar with it. 
    TinyMCE does save XHTML.  I put some code in the PHP side
    to use Tidy if it is available to convert it to HTML 4.01,
    which I prefer.



    Also in this release:



    Fixed an XSS issue in tag.php.
    

    Fixing a parsing issue with anchor tags when doing
    pingbacks.
    

    Fixed an error when the remote site can not be contacted.
    

    Fix for pingbacks with nice URLs enabled. Was blocking
    pingbacks.
    



Seven Things about me - tagged by Brian DeShong
brianlmoon — Mon, 05 Jan 2009 00:08:27 -0600
So, my Haystacks teammate Brian DeShong tagged me in his
list of seven.  We won

that trivia contest by the way.  It was a real team
effort.



So, here goes my seven things:



    I have six kids.  Okay, let that sink in.  Yes,
    six.  Logan(12), Macy(11), Molly(9), Parker(7), Collin(3),
    and Hudson(6 months).  I know what causes it.  Yes,
    it is hard at times.  But, there are those moments when
    you are sitting in the yard or in the den and all is right in
    the world.  The best program I will ever write will not
    compare to what have done with my children.  They are
    truly my greatest project.  My wonderful wife blogs about
    them at Moonmania.
    

    I started my career as a Visual Basic programmer.  PHP
    and VB are very much alike.  Neither is OOP, yet people
    keep trying to make them so.  You can write truly powerful
    applications if you know what you are doing.  Much of the
    outside community thinks poorly of the language.  In both
    cases I was way too busy making cool things and getting stuff
    done to care or pay attention.
    

    I play disc golf. 
    It is a great sport.  It reminds me of the Open Source
    community in that it is a very community driven sport. 
    The courses are installed and maintained by the players,
    usually on public land such as parks.  The tournaments are
    run by local players.  The sport is largely managed by
    players.  I maintain the web site of one of the larger
    regional (maybe the largest) 
        tournament series in the world.  I have played in
        most cities that I have visited including those I go to for
        conferences.  We have a group of 9 or so people at
        dealnews that play on a semi-regular basis when the weather
        is right.
    

    I never graduated college.  The VB work and then the
    PHP work got in the way.  It is a regret in some
    ways.  But, I can't tell you what I would have changed
    about how my life has gone.  Perhaps I will go back and
    finish my degree at some point.  Sadly, in this world,
    there are doors that are closed to you no matter how brilliant
    you are if you don't have a simple piece of paper.  I know
    people with that piece of paper that ended up working fast
    food.  But, they could get an interview where I could
    not.  They would not get the job, but I can't even get in
    the door.
    

    I have contributed two PHP internals functions. 
    mysql_fetch_assoc and the now deprecated 
        set_file_buffer.  In the case of
        mysql_fetch_assoc, I was fixing what was IMO a bug. 
        mysql_fetch_array originally returned only an associative
        array.  Someone decided it should return the data in
        both associative and numeric forms.  mysql_fetch_row
        already existed to return numeric keys.  So, I simply
        copied it and added mysql_fetch_assoc.  That was the
        real wild wild west days of PHP CVS.  You either had
        full access or you didn't have any access.  I did not
        keep my development up on PHP so I lost karma on the core
        later.  My C is not super polished.  Still I will
        admit I liked being part of the club.
    

    I watch pro wrestling.  There I said it.  Guilty
    pleasure.  The Tivo makes it easy.  2 hour show only
    takes about 40 minutes.  Heh, I skip most of the
    wrestling.  Not everyone is entertaining.  My great
    grandmother would sit and watch it every Saturday night. 
    I was hooked from that point.  I won't get going on
    it.  I would lose you guys.  FWIW, I am a big fan of
    mixed martial arts too.  That stuff IS real and very
    exciting.
    

    I am a compulsive code rewriter and/or write it myself kind
    of guy.  It is something I struggle with every day. 
    It is why I started Phorum
    and Wordcraft. 
    Luckily, the guys I work with at both dealnews and on Phorum
    are good programmers.  My biggest problem is opening a
    file I last worked on 5 years ago.  The way we write web
    applicaitons has evolved so much in the 11 years I have been
    doing this.  I am using the same language, but have such
    different ideas.  I can only imagine what the next 11
    years will bring.
    



I will be tagging these seven.  Forgive me if I get a title
wrong.



    
        Brian
        Aker.  The creators of sites like Facebook and
        Digg may have had some great ideas.  But, without guys
        like Brian Aker, they would be nowhere.  He makes the
        things that make dealnews, Facebook and Digg
        possible.  And he continues to contribute with Drizzle
        and the new C based gearmand.
    

    
        John
        Allspaw.  John manages the operations team for
        Flickr.  He is a smart guy.  He and I see eye to
        eye on a lot of web performance topics.
    

    
        Joe Stump. Joe is
        the lead architect for Digg.  I have not met Joe in
        person.  But, I have heard him interviewed and we are
        on the same page about a lot of things.  I have
        started using some of his contributed PHP code
        lately as well.
    

    
        Alan Kasindorf aka
        Dormando.  Alan works for operations at Six
        Apart.  They bought Danga.  They formerly owned
        Live Journal.  He has kind of become the care taker of
        memcached, gearmand and all those other cool things that
        Danga created.  The community is helping him more each
        day, but he still does a lot of work for those
        projects.  Including, but not limited to coding.
    

    
        Jay Pipes. 
        I think I saw him on someone else's list.  But, I am
        putting him down anyway.  Jay has been good to me over
        the years.  It is cool that someone can chair the
        MySQL Conference, go around talking to user groups and
        commit code to fix MySQL bugs.  Jay is great because
        he and I can disagree on just about everything when it
        comes to programming then toast our beers.  You need
        people like that in the world.
    

    
        John
        Allen.  I work with John at dealnews.  He has
        learned the search engine optimization and marketing world
        the way I learned programming.  John posses skills
        that I don't.  We butt heads on a regular basis about
        things.  But, the end result is always better than
        what existed before we started.  I have learned many
        things from him.
    

    
        Daniel Beckham. 
        Daniel is my partner in crime at dealnews.  We have
        been a two headed monster (yeah, a monster.  Just ask
        the CEO.) for 8 or so years now.  Unfortunately,
        Daniel does not blog or twitter or much of anything like
        that.  So, we likely won't see his seven things. 
        Many of the things I have blogged about when it comes to
        building dealnews' architecture were thought of and done by
        both of us.  I am just a show off extrovert that needs
        a lot of attention.  So, I do all the blogging and
        talking at conferences.  
    

And, I am told I need to post this for my taggies to follow:



    Link your original tagger(s), and list these rules on your
    blog.
    

    Share seven facts about yourself in the post - some random,
    some weird.
    

    Tag seven people at the end of your post by leaving their
    names and the links to their blogs.
    

    Let them know they’ve been tagged by leaving a comment on
    their blogs and/or Twitter.
    



Wordcraft 0.6 available
brianlmoon — Wed, 17 Dec 2008 01:26:32 -0600
I am pleased to announce the release of Wordcraft
0.6.  I have been using it for a month or so now and I am
learning some things.



I had been having trouble logging in lately from multiple
places.  So, instead of trying to work on the built in session
handling I had written, I took my own advice (use stuff that
exists) and just switched to PHP sessions.  All the cookie
stuff is worked out and I can get a lot done with just a little
work.  PHP sessions make me a little nervous.  If you
have lots of applications installed on the same site that use them,
you can get some odd behavior.  But, why reinvent the wheel
right?



I have found myself wanting to save a post while working on
it.  To do that before, I would have to uncheck the Published
box.  To solve this, I changed the behavior of the Save
button.  When pressed it now saves the post, but with the
published flag set to 0.  This lets you save a post while
working on it quickly.  I then added a Publish button to the
post form.  The Publish button will save the post with the
published flag set to 1.  If a post is already published, you
just get an Update button that will save whatever is set in the
form.



From a code perspective, I have made all the code use the same
array for user data.  I had a separate one for the core, one
for the template and one for the admin before.  That was
getting complicated.  So, they all just use the same one
now.



The last thing I did was add meta refresh tags to the admin success
pages.  I like having a success page to tell me something
worked.  But, I really want it to move along after it is
done.  It does that now.  It is set to 3 seconds.  I
may knock that down a bit.  That 3 seconds starts after the
page is fully loaded.  So, it can be more like 5 or 6 if stuff
has to load.


OSCON moves to San Jose - or - I will miss Portland
brianlmoon — Sat, 13 Dec 2008 00:37:22 -0600
I have had the privilege to go to the O'Reilly Open Source Convention 4 of the past 5 years.  During that time, it has been held in beautiful Portland, Oregon.  Well, the OSCON 2009 web site is up and they are moving it to San Jose, CA.  I have never been to the San Jose Convention Center.  The pictures look nice.  I have only been to San Jose at all to get off a plane and go to the Santa Clara Convention Center.  I hope San Jose has more to offer than Santa Clara.  The Santa Clara Convention Center is nice.  And the hotel it is attached to is also quite nice.  I have attended an Apachecon in 2001 and two MySQL Conferences (2007 and 2008) there.  But, outside of the convention center, there is not a lot of fun stuff to do.  You have to travel a good distance to find good medium to high end restaurants.  It is in the middle of a lot of large office buildings with names like Yahoo!, Intel, AMD, nVidia, etc. on them.  The other space is taken up by apartments.  But, I did not start this post to dig on Santa Clara.

I will miss Portland.  It is a neat town.  Lots of good food.  Lots of good beer.  Easy to navigate once you get used to the bridges.  If you like public transport it is quite accesible.  There is good disc golf in the area too.  That was always a plus. 

But, here is to new beginnings in San Jose.  May it be as fun and educational as Portland has been these past few years.


Know PHP/MySQL and wanna earn a buck?
brianlmoon — Mon, 24 Nov 2008 16:53:02 -0600
Apparently, all the work we have put into dealnews.com over the last 11 1/2 years can be had for $250 and can be delivered in 10 hours.

http://www.getafreelancer.com/projects/PHP-Joomla/Clone-dealnews-com-CMS.html

I see it was canceled.  That is smart.  What we do can not be done for that kind of money.  $350?  Maybe. =)

Speaking of dealnews, we have been rated the #1 Black Friday site by PC Magazine.


Open Source Web Design
brianlmoon — Mon, 17 Nov 2008 12:00:00 -0600
So, my wife told me that my site design was boring.  Yeah, she was right.  I am no designer.  I just don't have that gene.  But, during my work on Wordcraft, I came across some cool places to find designs that are relased under Open Source licenses.





Open Designs - This is arguably the the prettiest of the three. The search, however, is painfully slow because all results return on one page.  I guess if you can wait, this is a plus as browsing is easier.  Also, you can pick multiple colors and choose by license.  They only list XHTML templates (at least as search options).  That could be a turn off if you like HTML 4 like me.
Open Web Design - The site itself could use a design overhaul.  But, the content is good.  The search lets you choose primary and secondary color, a unique feature among these sites.  Thumbnails are a bit small though.
Open Source Web Design - Their search is not as powerful as the others, but it does return very fast.  The thumbnails are a nice size.

You will find the same content on all three sometimes.  But, it comes down to browsing and searching.

I found my new design at one of those.  Not sure which, I looked at a lot of them.  I did not use the template's HTML exactly as I like HTML 4.0 and wanted a different sidebar than the original author.  But, the design is the hard part.  So, thanks for Deep Red.


Wordcraft 0.5 available
brianlmoon — Mon, 10 Nov 2008 08:00:00 -0600
Well, I blogged about
Wordcraft the other day.  I have just been running live on
the software for 4 days now.  Well, that post had no URI
associated with it.  It took me two days to figure this
out.  Oops.  Welcome to eating my own dog
food.  So, running this live with actual users (and a host
of bot spam attempts) I am learning a lot and making a lot of
commits.  So, I may very well roll once or twice a week for
the first few weeks.



So, with that, I have packaged 0.5.  There are 15 changes in
this package.  Some features, but mostly bug fixes.  So,
if you could use a simple blog, give it a try and help me debug
it.  If you do, please use the Google Code issue
tracker.  Maybe I can figure out how to have those things
emailed to me.


Wordcraft, a simple PHP blogging application
brianlmoon — Thu, 06 Nov 2008 08:00:00 -0600
So, a while back, not sure when, I was listening to the P3 Podcast
and Paul mentioned his dislike for Wordpress.  He said he wished there
was a simple blogging application.  I am probably misquoting him
horribly.  It was an idea that I had been tinkering with.  So, I
started on Wordcraft in my spare time.  Like super spare time.  That
time between the kids going to bed and me falling  asleep.  So, it took
a while to get it to a usable state.
Up until now, I have used Wordpress.com for my blogging.  It works
quite well.  You can get started quite quickly and it does what most
people need.  My wife uses Blogger for our family blog.  It is, IMO,
not as nice as Wordpress.com in some ways.  But, it does allow you to
edit your styles (for free) and such which is nice.
So, why would I want to reinvent the wheel?  I am a control freak
and rarely run other people's code.  I know, it is a character flaw.  I
am working on it.  So, what did I come up with?
I had some goals when I started on this.



Keep it simple.
Focus on what I am good at doing.




Keeping it simple
I use MySQL.  I didn't try to make it work with every possible
database.  In fact, it only uses the mysqli PHP extenstion.  The few
objects (CAPTCHA) are all PHP 5 objects.  I don't plan to worry about
PHP 4.  The templates don't use a template language.  They use plain
old PHP.  The are scoped to protect template authors from global
scope.  There are only 6 files required to make a new template.  There
are just 589 lines of code in the forward facing scripts.  The admin
has 2,446.
What am I good at doing?
I write PHP/MySQL code that has to work fast for a living.  It is
what I get paid to do.  I am not a designer.  I am not a spam catching
wizard.  I don't write cool javascript widgets.  So, I focused on the
PHP/MySQL parts of the code.  For templates, I used designs that are
released under the Creative Commons license.  I use Akismet and the
CAPTCHA libraries from Phorum for spam catching.  I used the YUI Rich
Editor for the admin where I needed a WYSIWYG widget.  I even link to
the YUI sources that are hosted by Yahoo.  No sense taking on that
bandwidth or storage.
So, what does it do you ask?  Well, here are some of the features:



WYSIWYG editing via YUI.
Comments with optional CAPTCHA and/or Akismet.
Custom pages can be created.
Tagging of posts
Custom publish dates
Automatic Pingback support
Friendly URL support with mod_rewrite
5 Templates in first release.  Easy to build more.
Email notifications to authors

There are some things missing of course.  Internationalization of
both the admin and templates is a big one.  There is no current search
engine for blog posts.  There is no "blog roll" type of feature.  There
is no date based archive.  And I am sure there is more missing.  And I
am sure there are bugs.
But, if you would like to try out yet another PHP application, I welcome you to give it a try.  The code is hosted at Google Code.  It is a BSD licensed application.


PHP Appalachia Corrections
brianlmoon — Tue, 14 Oct 2008 23:03:05 -0500
Just got home finally from PHP Appalachia.  I enjoyed meeting
all the great people.



I presented about what I learned and how we deal with importing
large amounts of CSV data into MySQL.  I threw my idea onto
the wiki at the last minute, made the slides while everyone ate
breakfast and I had planned on researching it all (been a few years
since I wrote it), but we had no reliable internet.  Some
claims I made and their corrections.



    
        

    

    I said our largest file is about 1.8 million lines. 
    WRONG.  Actually it is about 4.6 million.  I was
    correct however that it does finish importing and indexing in
    about 5 minutes.
    

    
        

    

    I claimed I LOAD DATA INFILE to MyISAM first and then
    "insert into ... select from" into an InnoDB table for speed
    reasons.  WRONG.  In fact, I do that because I need
    to merge fields from the file sometimes into one field in the
    databaes.  I could not find a way to do that with LOAD
    DATA INFILE.  As to speed.  I can't say either way as
    I have no solid data.  Sounds like a good test. 
    MyISAM probably still wins on a LOAD DATA INFILE into a blank,
    fresh table based on my experience.
    

    
        

    

    Total rows currently indexed is 7.2 million.  I did
    not make a claim, but I thought I would just mention
    that.  I wanted to include that, but did not have
    Internet.  (Damn you Hughes)
    

    
        

    



Deploying Scalable Websites with Memcached
brianlmoon — Fri, 03 Oct 2008 09:55:45 -0500
I spoke at the MySQL Conference and Expo this year about the architecture we have here at dealnews.com.  After my talk, Jimmy Guerrero of Sun/MySQL invited me to give a webinar on how dealnews uses memcached.  That is taking place next week, Thursday, October 09, 2008.  It is a free webinar.  We have used memcached in a variety of ways as we have grown. So, I will be talking about how dealnews used memcached in the past and present.



For more information, visit the MySQL web site.


strtotime() - The PHP, date swiss army knife
brianlmoon — Fri, 19 Sep 2008 22:02:47 -0500
Man, what did I do before strtotime().  Oh, I know, I had
a 482 line function to parse date formats and return
timestamps.  And I still could not do really cool stuff. 
Like tonight I needed to figure out when Thanksgiving was in the
US.  I knew it was the 4th Thursday in November.  So, I
started with some math stuff and checking what day of the week Nov.
1 would fall on.  All that was making my head hurt.  So,
I just tried this for fun.


strtotime("thursday, november ".date("Y")." + 3 weeks")


That gives me Thanksgiving.  Awesome.  It is cool for
other stuff too.  At its very basic, it can take a MySQL
datetime field and turn it into a timestamp.  Very handy for
date calculations.  It also understands RFC
2822 and ISO 8601 date formats.  These are common in HTTP
headers and some XML documents like RSS and Atom feeds.  Also,
PHP can output those two standard formats with the date() function.  So, this makes
them a good standards compliant way to pass full, timezone specific
dates around.


Shrinking ibdata files after innodb_file_per_table
brianlmoon — Thu, 21 Aug 2008 23:54:21 -0500
Patrick Galbraith wrote in his blog about switching to innodb_file_per_table.  For those that don't know about this setting, it places the data for the tables into .ibd files within the database dir instead of storing it in the ibdata files in the main datadir.  This is useful if you don't want to babysit your innodb tablespace.  At least, that was my main reason for wanting to use it.  There is still dictionary data stored in the ibdata file(s) so you can't just remove them.



Anyhow, the delima Patrick wrote about is recovering the space used by the ibdata files in the datadir after you have converted the tables to one file per table.  I commented on his blog, but thought it worth a full post to be sure others could find my solution.



	Backup your data (cuz, you never know)


	Convert all tables to MyISAM


	Stop MySQL.


	Delete ib* in the datadir


	Restart MySQL.  MySQL will recreate the files.


	Convert all tables to InnoDB




It worked for me.  Your mileage may vary.  No warranty that you won't lose all your data.  Try it on a dev server first.



The benefits of this were that the data was online while we converted the tables.  The only downtime was while we shut down MySQL, removed the files and waited on MySQL to recreate a small 10MB ibdata file and the ib_logfiles.  I am not 100% sure you have to remove the ib_logfiles, but I did for good measure.  I just run with a single 10MB autoextend ibdata file.  I think it is at 34MB or so on our main database server now.


dealnews is hiring a Systems Administrator
brianlmoon — Wed, 20 Aug 2008 18:02:15 -0500
We are hiring!  dealnews is looking for a full time systems administrator.  The developers have been sharing the sys admin load for over 10 years now.  But, we really need a dedicated person now.  If you are interested, see our jobs page.


Replication is much better than cold backups
brianlmoon — Sun, 17 Aug 2008 01:58:47 -0500
So, I wrote about the begining of our wild database issues. Since then, I have been fighting a cold, coaching little league football and trying to help out in getting our backup solutions working in top shape.  That does not leave much time for blogging.



Never again will we have ONLY a cold backup of anything.  We were moving nightly full database dumps and hourly backups of critical tables over to that box all day long.  Well, when the filesystem fails on both the primary database server and your cold backup server, you question everything.  A day after my marathon drive to fix the backup server and get it up and running, the backup mysql server died again with RAID errors.  I guess that was the problem all along.  In the end, we had to have a whole new RAID subsystem in our backup database server.  So, my coworker headed over to the data center to pull the all nighter to get the original, main database server up and running.  The filesystem was completely shot.  ReiserFS failed us miserably.  It is no longer to be used at dealnews.



Well, today at 6:12PM, the main database server stops responding again.  ARGH!!  Input/Ouput errors.  That means RAID based on last weeks experience.  We reboot it.  It reports memory or battery errors on the RAID card.  So, I call Dell.  Our warranty on these servers includes 4 hour, onsite service.  They are important.  While on the phone with Dell, I run the Dell diagnostic tool on the box.  During the diagnostic test, the box shuts down.  Luckily, the Dell service tech had heard enough.  He orders a whole new RAID subsystem for this one as well.



There is one cool thing about the PERC4 (aka, LSI Megaraid) RAID cards in these boxes.  They write the RAID configuration to the drives as well as on the card.  So, when a new blank RAID card is installed, it finds the RAID config on the drives and boots the box up.  Neato.  I am sure all the latest cards do it.  It was just nice to see it work.



So, box came up, but this time we had Innodb corruption.  XFS did a fine job in keeping the filesystem in tact.  So, we had to go from backups.  But, this time we had a live replicated database that we could just dump and restore.  We should have had it all along, but in the past (i.e. before widespread Innodb) we were gun shy about replication.  We had large MyISAM tables that would constantly get corrupted on the master or slave and would halt replication on a weekly basis.  It was just not worth the hassle.  But, we have used it for over a year now in our front end database servers with an all Innodb data set.  As of now, only two tables in our main database are not Innodb.  And I am trying to drop the need for a Full-Text index on those right now.



So, here is to hoping our database problems are behind us.  We have replaced almost everything in one except the chassis.  The other has had all internal parts but a motherboard.  Kudos to Dell's service.  The tech was done with the repair in under 4 hours.  Glad to have that service.  I recommend it to anyone that needs it.


Database nightmare
brianlmoon — Tue, 05 Aug 2008 09:39:20 -0500
Its 12:30AM (00:30 for you Euros).  I am watching The Daily Show on Tivo.  All is well.  Then the phone beeps.



MySQL Main is critical



SSH?  no.



Digi console?  no.



About a week ago, we had a mysqldump file that was corrupt.  We cleaned it up.  My worst fears came to my mind.  We tried power cycling it.  It did not come back.



While my coworker was dealing with the facilily people, I worked on the backup server.  Had to ensure the last full backup was in place and apply the incremental data.  Suddenly, my SSH connection dies.  OMG.  THAT DUMB A** GUY POWER CYCLED THE WRONG BOX!!! --- FS corrupted.  Damn you ReiserFS!



By now, it's 4AM.  Tech took an hour to get to the rack.  It is 20 feet from his cubicle.  I get in the car.  I am two hours away from the facility.  No sleep.  It is still dark.  I play loud music.  I talk to myself.  I curse the guy that power cycled the wrong box. The sun comes up and it is easier to drive.



I sit here, waiting on the OS to finish installing so I can restore the backup and incremental data again.  Hours of content lost.  The content team is hand writing HTML that other developers are rsyncing around to the servers.



The good news?  All the work done in 2007 to separate our front end and backend worked.  The front end works fine (99%).  Just no new content.  Well, except for the hand done HTML.



Note to self: Get that main database replication working again.  ASAP.


Where Drizzle fits in for me
brianlmoon — Thu, 24 Jul 2008 13:17:26 -0500
So, most of you have heard about Drizzle by now.  For those that have not, you can check out many, many blog posts or the Launchpad page.



The thread on Slashdot about Drizzle was quite negative.  Most misunderstand what Drizzle is about.  SQLite is not a good solution when you have 100 web servers.  Let me describe how it I would use it and maybe that will help some understand it.



When it comes to MySQL use, dealnews has two very different use cases.  The first is an enterprise storage system that involves content creation, reporting and data warehousing.  For that layer of our business, we are using more and more advanced features as they become available.  We use triggers and stored procedures.  We use complex data types for specific use cases.  All those features are a big gain.



The other way that we use MySQL is for serving content to our readers.  I have written about this before.  For this purpose, we avoid joins, don't use any advanced features.  We do use replication, indexes and intelligent queries.  We don't (as one slashdot reader claimed) do all of our processing in the code.  That would be stupid.  If you do that you are ignorant.  I will stop talking about that before this becomes a rant.  I do believe in letting MySQL do my work for me.



This is where Drizzle fits in.  To serve content, I don't need stored procedures, triggers, views or any of that other stuff.  The whole database that the front end web servers use is basically a view.  It is a denormalized, prepared version of the real data.  I store objects. But, I have to be able to sort and filter the data in a way that SQL allows me to do.  CouchDB sounds interesting.  Maybe one day it will be there.  It is sill in the optimization phase.



Now, some say that this is just MySQL 3.x all over again.  Well, you clearly have not been listening to the really smart people that are working on Drizzle.  They are doing more than just removing the 4.1 and 5.x features from MySQL.  They are removing things that don't make sense for this use case.  They are adding things that do make sense.  They are replacing parts of the code base where there is a better library or way of doing it.  At this point, they have no feature requirements to meet.  They have no deadlines.  They are making what they think the high volume web world and/or cloud computing needs.  They are making it plugable:  think Apache modules or PHP extensions.  So, if you need feature XYZ that was yanked out, you can add it back in (hopefully) via the internal API.  There is a lot more going on here than just removing "features".



So, I am cheering on the folks working on Drizzle.  I have joined their community and will provide what feedback I can from userland.  I am no C++ coder.  I can read it.  I can debug it.  But, writing it or doing heavy lifting is not in my skill set.  Hopefully I can contribute


Usability FAIL
brianlmoon — Tue, 22 Jul 2008 09:52:03 -0500
I can't be at OSCON this year.  But my colleague Rob is and he just posted a usability post about, of all things, the Double Tree hotel where I am sure a lot of you are staying.  Great stuff.


Caching and TTL behavior
brianlmoon — Wed, 02 Jul 2008 23:56:25 -0500
So, I am working on MemProxy some.  Mainly, I am trying to implement more of the Cache-Control header's many options.  The one that has me a bit perplexed s-maxage.  Particularly when combined with max-age.



s-maxage is the maximum time in seconds an item should remain in a shared cache.  So, if s-maxage is set by the application server, my proxy should keep it for that amount of time at the most.  Up until now, I have just been looking at max-age.  But, s-maxage is the proper one for a proxy to use if it is present.  I do not send the s-maxage through because this is a reverse proxy and, IMO, that is proper behavior for an application accelerating proxy.  However, I do send forward the max-age value that is set by the application servers.  If no max-age is set, I send a default as defined in the script.  Also, if no-cache or no-store is set, I send those and a max-age of 0.



My problem arises when max-age is less than s-maxage.  Up until now, I have sent a max-age back to the client that represents the time left for the cached item in my proxy's cache.  So, if the app server sent back max-age=300 and a request comes in and the cache is found and the cache was created 100 seconds ago, I send max-age-200 back to the client.  But, I was only using max-age before.  Now, in cases where s-maxage is longer than max-age, I would come up with negative numbers.  That is not cool.  The easiest solution would be to always send the original max-age back to the client.  But, that seems kind of lame.



So, my question is, if you are using an application (HTTP or otherwise) accelerator, what would you expect?  If you application set a max-age of 300 would you always expect the end client to receive a max-age of 300?  Or should it count down over time?  The only experience I have is a CDN.  If you watch CDN traffic, the max-age gets smaller and smaller over time until it hits 0.  I have not tried sending an s-maxage to my CDN.  I don't know what they would do with that.  Maybe that is a good test.



UPDATE: Writing this gave me an idea.  If the item will be in the proxy cache longer than the max-age ttl, send the full max-age ttl.  Otherwise, send the time left in the proxy cache.  Thoughts on that?



(thanks for being my teddy bear blogosphere)