Fixing a problem with s3fs, updatedb and high S3 bills

S3FS is a very convenient piece of software. It lets you easily keep all your data in "the cloud" without the possibility of ever overfilling. It is pretty good for things like back end backups copies and infrequently accessed files, but I would never advise for it to be used in conjunction with a web application that may actually serve the files from the mounted resource.

In  continuous effort to get our AWS bill as low as possible, I've stumbled upon an extraordinary number of Tier2 and Tier1 request on one of our Regions.
Out S3 buckets had over 400 million Tier 1, and almost 1 million Tier2 requests per month which I thought is odd, as we paid more for accessing S3 than for storing several TB of data in it.

The problem

As our AWS infrastructure is quite modest compared to some, there is no way we could have as much traffic to generate this amount of S3 usage.


Amazon Simple Storage Service EU-Requests-Tier1
$0.005 per 1,000 PUT, COPY, POST, or LIST requests - 86,694,315 Requests

Amazon Simple Storage Service EU-Requests-Tier2

$0.004 per 10,000 GET and all other requests - 447,163,447 Requests

Looking at our traffic usage on our monitoring TV, powered by ElasticSearch and Kibana (very cool) I could quickly see that 447mln requests is more than we get in 6 months on our servers. There had to be another explanation for this, than just plain traffic.

The search

Amazon gives you a few possibilities to drill down into your usage of their resources. The easiest would be to look at usage reports, under Billing and Cost Management. You get a CSV file in which you can easily check, which actual buckets are responsible for the high usage on a per-month, per-day and per-hour basis.

Looking at the usage patterns on the buckets, it was very quickly apparent, that 95% of the usage was on 2 buckets, used by a legacy perl application, which in order to connect to S3, unfortunately had to use S3FS.

After googling around a bit I found the culprit, it was updatedb ! The indexing process had hammered s3fs with indexing a 4TB bucket every day, all day, for who knows how long...

The solution

On our MAPS cluster, I added 2 lines in the /etc/init/updatedb.conf file, one with the path to be excluded, the other one with the file system:

PRUNEPATHS="/tmp /var/spool /media /home/.ecryptfs /mnt/s3"

PRUNEFS="NFS nfs nfs4 rpc_pipefs afs binfmt_misc proc smbfs autofs iso9660 ncpfs coda devpts ftpfs devfs mfs shfs sysfs cifs lustre tmpfs usbfs udf fuse.glusterfs fuse.sshfs curlftpfs ecryptfs fusesmb devtmpfs fuse.s3fs"

The last step was baking new AMI's on the cluster and launching a new one that was to replace the faulty one. I would also suggest to check in a couple of hours the hourly based s3 usage report if it has gone down.

The other thing you absolutely have to do is to enable tag based billing and programmatic access, as it will work for the months after you have enabled it. This will help to get insight into billing in the future with tools like Netflix's Ice.

Hope you enjoyed this solution.

Cheers,
P

From zero to cloud - guide to setting up a dev process with the Amazon AWS Cloud pt. 1

Hi all,

once again I managed to get some time off to write.

There have been interesting things happening in my career as I had the opportunity to plan and execute an entire companies move from Rackspace to Amazon AWS with a very diverse app base with PHP Symfony 1, 1.4, 2.X apps, Perl (mod perl) apps and Java (Red5) apps, Magento stores, vendor specific tools like Active Collab.

It has taken me quite some time to figure out some problems as there is no real comprehensive thing out there to start easy with the Amazon AWS adventure and the Amazon guides are a bit dry.

To move you closer to the problem domain faced here, I will point out the steps that you need to take to have a complete application development cycle. Later I will hopefully manage to write a post about every step of the way, so it is much easier to understand the big picture, how things work together in an AWS based application.

I will not write about all the problems, that we had to overcome, because of the nature of the applications themselves, as the capacity for an app to be clustered is beyond the scope of this article. The most important rule however is - do not write anything on the server that is important to anyone, either upload it to Amazon S3, some attached webdav or the database (some db's have the option to save files like MongoDB's GridFS).

So the first usual step in the apps development is the actual development no surprises here.

1. Version control and simple collaboration (e.g. code reviews).

GIT and github.com to keep the code, collaborate and make it easy to pull from anywhere. I have actually pushed the company to move away from CVS, which wasn't very difficult as it is frustrating to work with it. Github has a fantastic interface and with the addition of their in-code search it is quite more practical than the dreadful search in Eclipse.

All you need is to register with github.com and follow simple instructions on getting started. We have a pro account with github, so we can keep our repositories private, for small teams you can however go to their competition - bitbucket.org, which has a pay-by-users model and lets you have free private repos.

2. Continuous Integration - making sure your code stays in good condition

Jenkins to run test and build. Well that is also one of the easy ones, there are some more sophisticated vendor solutions out there, but Jenkins with some extensions offers everything we needed. I added GIT, SVN, Google OpenID login, so that everyone from the organisation can log in and Apache Maven support.

The other cool thing Jenkins lets you do is have code statistics, like phpmd and copy/paste detection, code style checking and keeping the statistics for later. We also plan on making Jenkins directly fire up testing infrastructure in Amazon AWS via CloudFormation scripts so basically a file describing exactly your deployment and the relationships between machines. For the sake of simplicity and cost (which will come later in the post) just put Jenkins locally or on a traditional server.

3. Figuring out if everything belongs in the cloud

Now you have the means to code, collaborate and make it a really nice product so now you need to figure out which parts of your infrastructure should go into Amazon AWS.

As with everything, the cloud is only the answer to a very specific question, but certainly no to all of it. The true advantage of cloud computing isn't scaling up, as you can do that with normal infrastructures - it's scaling down in minutes. The advantage of being able to scale the number and size of instances is only relevant if you have fluctuating traffic. That means if the differences of usage during the day are quite higher than 1-2 servers of difference (in terms of computing power) than it's a classic case of lets-go-cloud.

There are some applications however, that are not really suited or would be missing the point of cloud computing. We have found that some applications simply don't belong there:

• Windows based stuff - it's just a question of cost. If  you don't need the scalability for windows boxes, it's far less expensive to put them with a traditional provider, like OVH
• All utility apps - everything you need to have running in single-server mode, so in other words there will never be need to make 2+ of them, but also making less than 1 would not be good. Every utility app, like Jenkins for instance, may or may not need additional nodes to work with, if it doesn't buy a normal server (or 2 if you like resiliency) and put it on there, make regular backups though. In our deployment Jenkins went onto a normal server.
• Everything requiring constantly needing hight computing power. Well every app that doesn't need to scale beyond one/two powerful servers and is constantly on-line is IMHO better off on a traditional server. 

What you have to take into account, that the AWS instances, even though even called "large" are nowhere near a £200 per month server, when it comes to computing power and usually costs the same or more. You get however the ability to run 1 tiny instance during the nights and 20 of them during heave days traffic which at the end might save you some major money.

Our traffic is not only fluctuating weekly, daily but is also strongly seasonal, as we are a ed-tech company. During the summer and all school breaks there is just need for a minimum infrastructure.

4. Moving domains to Route53

Route53 is Amazons DNS service. It offers a very nice interface for your dns administration and it's certainly easier to configure than bind9. This step of course isn't necessary, but it is good to move your domains so that you would have "one (key)ring to rule them all". As with AWS you get API access to almost everything so it is much easier to automate if everything is in one place, plus it's a lot tidier than having to log into somewhere else (or several locations) to control your domains.
We found a small Perl script that runs in CRON, that actually checks the instance tags of each instance for a specific tag and creates domains based on that name, so you can have a bit more simple to write name than the usual public DNS AWS instance domain names (e.g. ec2-54-228-109-17.eu-west-1.compute.amazonaws.com). In order for the script to work, you need to have the Amazon CLI tools installed.

This concludes the things you need to know to start getting familiar with AWS. In the next posts I will write about:

• Getting your team to have access to AWS services with IAM
• Managing some aspects of your deployments with the AWS Console
• Setting up and managing your workflows with Netflix's Asgard control panel
• Deploying your applications to your cloud servers with Capistrano
• Setting up and testing your auto scaling cloud deployments with jMeter and AWS CloudWatch

Hope you enjoyed reading, See you soon.

Amazon EC2 Instance inaccessible because of s3fs

Hi again!

Long time since my last post but always wanted to continue this blog - as it turns out some might be actually reading it ;)

During the time I was gone, I changed jobs a couple of times, even changed countries. I am now based in London, UK - a rather exciting city. Now working a lot with Amazon AWS and Java EE.

This blog post is about problems we had setting up auto scaling on Amazon AWS EC2 cloud. We were using a GUI front-end named Asgard (the home of the gods!) by Netflix trying to set up clusters and test them.

As we are moving more and more of our infrastructure into EC2, some problems become more and more apparent, especially the ability (or in our case - inability) of applications to work in clusters. There are some things that need to be considered, before creating a cluster of you application, which are not in the scope of this post.

Long story short, we needed to make some short cuts and instead of using Amazon S3 as it should - via its HTTP API, we went the s3fs way... it is a neat helper tool but can have nasty effects like memory leaks (saw it grab 50% of the machines RAM) and wild CPU usage... as it had in our case.

I was during a jMeter performance test of our two moved clusters, one is a PHP frontend app cluster and the other is a Perl service application cluster. Both of them started of as small instance clusters however the tests revealed that our Perl apps have a rather annoying problem with going really well and then just dying when overwhelmed. So we thought to go with medium instance sizes for the Perl cluster...

The medium instances hover did not start properly, they couldn't be ssh'd and the "get system log" option revealed that the setup didn't finish. The problematic thing about this was, that even though the small instances started without issue, the medium or large (we didn't try higher ones as it didn't seam there is a point) won't start. In the AWS console the instances passed all checks (which seam like a bit of shitty tests IMHO) but still there was zero movement.

After a few hours of trying different options to connect to the instances, we actually paid for Amazon's business level support - which is amazing. It costs you about 10% of your monthly fee or $100 whichever is higher, but assuming we would spend a week on figuring this out... well worth it. After about 30 minutes with one of their support staff, we finally found the problem.

We had a s3fs share mounted straight to /mnt and with no __netdev options (which basically means, that it need the network connection in otder to be mounted). After changing the mount point to some other folder and adding the fstab option, everything went back to normal and the instances

What we assumed is that the small instance was slow enough to start s3fs after it had started networking, therefore finishing the boot sequence correctly.

Hope this helps anyone stuck with the same problem. If an instance is not booting properly - look at fstab and use s3fs as a temporary option.

Cheers,
P

Symfony Dependency Injection - a really cool PHP tool

Hi!

Today I want to talk a little about a great design pattern called Dependency Injection and it's implementation by SensioLabs called simply Dependency Injection Component.

Now dependency injection is basically the inversion of control in you classes. It can help you to create decoupled structures that can be configured at run time which gives you a lot of flexibility. Let's take a look at an example:

Let's presume, you need to user a tool class inside some other class:

class User {


private $tool;


public function __construct(Tool $tool){
   $this->tool = $tool;
}


public function doStuff()   
   $this->tool->doSomeStuff();
}


As you can see, I've user the strategy pattern, so every time I want the USer to ->doStuff() it uses the ->doSomeStuff() method of the Tool class object.

Now this way is pretty neat, because it avoids  hard-coding the Tool into the User allowing for polymorphism. However creating a lot of such dependencies can be a pain if you have some more complicated stuff to do. Additionally there can be some problems with instantiating the Tool class before usage.

Symfony dependency injection can solve both those problems as it decouples elements and creates them upon usage, not upon configuration and helps to easily create complicated dependencies just before usage. Let's look a the example ripped out of my ZF project:


//retrieving mailer options from configuration

$mailerOptions = $this->getOption('mailer');


//we create a builder object, that will later build our classes
$serviceContainerBuilder = new sfServiceContainerBuilder();


//regitering the mailer service
//this means, that when we call the mailer service
//the Zend_Mail class has to be instantiated with parameters
//and a function called with some additional params


$serviceContainerBuilder->register('mailer', 'Zend_Mail')
->addMethodCall('setDefaultFrom', array($mailerOptions['from'], $mailerOptions['from_name']))
->addMethodCall('setDefaultReplyTo', array($mailerOptions['replyto'], $mailerOptions['replyto_name']))
->addMethodCall('setDefaultTransport', array('%mailer.transport%'));


//everything should be pretty straight forward
//I'm creating a builder instance and configuring it
//with the object class I wan't to have created
//and it's parameters
//the setDefaultTransport param is still open, when you add
//params like %param% sfDI knows that you want to set the param
//before service class instantiation


//setting to Zend_Registry to get the serviceContainer whenever I want
Zend_Registry::set('serviceContainer', $serviceContainerBuilder);



Some of you, who have some experience with dealing with Zend_Mail, know that you need to have a mailer transport of type Zend_Mail_Transport_Abstract.

In my application I have two bootstrapping (preparing basically) files:
1) the main application bootstrap file (from which it was I took the above example)
2) the PHPUnit bootstrap

The testing reasons I want to have a different transport in the second bootstrap that in the reall one. The first mailer should normally send the email but the second one should just log it in a file. Here the dependency injection copes in really handy:

1) Main bootstrap:



$serviceContainerBuilder = Zend_Registry::get('serviceContainer');
$mailerOptions = $this->getOption('mailer');


//I want the main transport to be a posrmarkapp.com using transport
$serviceContainerBuilder->register('mail.transport', 'FT_Mail_Transport_Postmark')
->addArgument($mailerOptions['transport']['api_key'])
->setShared(false);


//the setShared(false) simply tells the container that the mail.transport object isn't unique


//here is the interesting part
//I tell the Container to inject the newly configured container, as the 
//"mailer.transport" argument to the mail.transport container


$serviceContainerBuilder->setParameter('mailer.transport', new sfServiceReference('mail.transport'));



2) The second bootstrap (PHPUnit):



$serviceContainerBuilder = Zend_Registry::get('serviceContainer');
$serviceContainerBuilder->register('mail.transport', 'Zend_Mail_Transport_File')
->addArgument(array('path' => APPLICATION_PATH . '/..tests/log'))
->setShared(false);



This example shows just how you can create another transport for another purpose, just as easily.
Now we want to see it all in action:


//in some form processing command, we retrieve the container and create the service

$mailerService = \Zend_Registry::get('serviceContainer')->getService('mailer');


$tpl = //obviously we need some template


$mailerService->setBodyHtml($tpl->render());
$mailerService->addTo($emailTo);
$mailerService->send();



It's that simple, we can additionally set some parameters before getting the service, adding different behavior to the container.
Some of you might say that you can preconfigure the Zend_Mail class instance and set if directly into the registry. True, but remember that not all pages result in the usage of the mailer, so you would probably waste a lot of resources holding a lot if instantiated objects in the registry.

Hope you enjoyed the examples and start building your complicated class relations with the Symfony Dependency Injection Component. For more information go to http://components.symfony-project.org/dependency-injection/

cheers,
Peter

SilverStripe CMS for PHP programmers, not for dummies

Hi there!

Recently I've came across a pretty standard problem:

1. I had a simple php website with little php really, some routing, contact form handling, teamplating, nothing fancy
2. I hate doing boring stuff like CRUD's and CMS's
3. I make a lot of changes to the website
4. The website comes in 2 languages

If you think "just user some cms" - well I had the same idea. I started from the best known to me - Wordpress. It's pretty robust, but not being written as a CMS, but as a blog, it didn't really fit in. Of course I could force WP to do what I wish, but anyone who developed something in WP knows it's not really for programmers (or by programmers IMO).

The second choice was on pretty standard CMS's like Joomla or  Drupal, still programming was really problematic, extending stuff hard, since I wanted pretty much nothing form Drupal, and had to leave all that was on the old page.

Finally a friend of mine introduced SilverStripe, a New Zeland made CMS/Framework.
I'm a sceptic by nature but this I loved at first sight.

1. You program first what you want (pretty much no prerequisites), and let the framework build the rest. It's really for programmers, you can't just click away compromising modularity or re-usability. Everything is a class, that you can create and use over and over.

2. Every one of my custom objects (like for example ProjectRealisations) are encapsulated in a single class, that was pretty easy to set up and extend.

3. Multilingual setup for the CMS (options to get any additional language) - 5 minutes.

So what I've done is set up a completely custom website with a CMS for multilingual content (with ACL cf course) in a few days, while learning the CMS/Framework (ssbits.com - a big help).

I strongly recommend the framework for small websites, for lazy programmers (like myself;) )

See you next time,
Peter

Flash sites beware - UseItBetter is finally out!

Hi Everyone,

just a quick note that the (super) cool tool for flash sites usability testing, recording and playing visits, making sophisticated site analysis is out!

Almost two years of hard work and the collective work of great minds made it all possible.

Check out more at www.UseItBetter.com

Thanks,
Peter

Importance of versioning software

Why is versioning important? What's the difference of naming 1.0 or 0.1 or whatever. In my experience product versioning is a crucial aspect not only providing law and order but also invoking the paradigm of modern programming techniques. You can compare a good versioning strategy to the Code of Law of a community, however big or small. A good CoL will make your community prosper and evolve even when bad stuff happens (it allways does), however a bad CoL will make your community vulnerable to internal problems, it's productivity and prosperity will suffer in each mishalf.
In IT product development versioning calls to a certain paradigm that is often underestimated by programmers on even team leaders (seen it) - the power of predictability.
Remember, there is always someone waiting for your code. Associating versions with time (like in SCRUM sprints) gives the programmer and the client a common way to express the readiness of a particular piece of logic and not that a simple date wouldn't be enough, but there is a bit more to it.
There are some basic versioning perks that you might want to take advantage of.  For instance combining a time period with a version eg.: each 0.1 is a 3 week sprint gives us has:

PROS:

• gives a common time factor variable associated with development (from the clients pov) - so everyone knows when something is/should be/should have been ready
• when there are more smaller tasks in a given sprint, you are free to prioritize them as you find fit for the best result - no one tells you if something should be 
• you always know how much time there is left for doing something - given that you know how long it is going to take
• you can easily plan ahead, knowing how much time you need for a particular task, just look at your project road map
• less deployment cycles give less space for error while updating production files/data
• less deployment cycles also creates a natural catching net for bugs - most probable that detected bugs are from the last sprint

CONS:

• the main problems are with getting clients to acknowledge some simple truths like "NOT EVERYTHING HAS TO BE DONE ON YESTERDAY"
• there is a real risk of thinking that we are lazy or just thinking of some "stupid academic stuff or suitable for real business" - just have to be as eloquent as possible, explaining that its the best (cheapest) solution possible and will save money (that's the keyword here)
• big risk of hot fixing - everything is starting to be a high priority hot fix for the client
• in some development cycles, where the clients are responsible for quality, you will need to take them into account in the versioning strategy

With the right set of people and a little time, versioning is a great tool for making development and maintenance of software much easier and more reliable.

more on this : wiki page

Hope it's been another inspiring piece of text for all humanity ;)
See you next time.

Why self-made frameworks are too expensive?

As I progress in my PHP programming endeavours I tend to argue a lot about the same topics with different developers. Sometimes there is some reason in madness I hear, but very often there is much overheard information that somehow doesn't make any sense and causes real confusion with some people. As in my opinion many web developers/programmers are very narcissistic, and even if they wont admit it, they think their code is the best for whatever reason, and no Open Source PHP Framework (they're all only open source since it's php, but I like to call it like it is) can match them in the understanding on the domain they are working on and the project will surely die without a custom made framework.
This confusion can sometimes lead to very high development and maintenance cost and I'll try to clear the situation a little bit, so You don't have to make the same mistakes.



The problem:

We have to build an Internet service of some kind, something we haven't exactly been much familiar with because it can be described as innovative.



The typical "solutions" (stuff I hear very often)

1) "Frameworks like Zend Framework or Symfony are slow and heavy, we will need a lot more hardware"
2) We need a lot more features than Open Source frameworks can provide
3) Frameworks are just another dude's point of view and implement things strangely



The crushing(for some) reality:

Let us mark out all the fields that we need to compare home-made-gizmo frameworks with PHP OSF (Open Source Frameworks):

• learning curve
• reliability
• maintainability
• extendability
• performance
• development time
• overall system cost

So lets maybe start with learning curve.

Unless You made a real effort to document everything in detail, add examples, tutorials and so on, event if you make some presentations and in-house training courses... you can't come even close to the POWER OF COMMUNITY.
Open Source PHP frameworks like ZF (my favorite as You might have noticed) have a great community, testing, learning and developing a lot of code with You and for You. You can hire professionals (like I ;-) ) that already know a lot about a system that you are about to design, build or maintain and spare a lot of head aches on paying someone for a month or two just to get around your system.

That's a clear 1-0 for PHP OSF !



Next we have a reliability comparison

So in my opinion there is only one answer here but let's just see what will be.
IMHO self made framework have a overall lower quality of code, thus offering much lower reliability for three simple reasons.

One - the developers working on in house development (and php programmers anyway) often don't know more (that a sad truth to realize) than two design patterns. the first is Singleton, the second is MVC or a mutant, unusable form of it anyway. I don't have any idea why developers confuse MVC's Model with the data layer, and not the business logic layer but what the hell, let's just go with it. the design becomes unclear, the classes all become dependant on each other and there is no way in hell that someone is writing decent unit tests for that monster.

The second reason is that most of the time there is no time to THINK BEFORE YOU CODE and the obvious pressure is on functionality not on the back end. Many people think that they can just code away and if the service is a bulls eye in the market, they.ll just change the framework "with all the money we will get" :) but in reality this seldom happens. they end up with much higher development costs.

The third reason is again COMMUNITY. It is very simple really. While one developer can write one thing, a hundred developers write a hundred things, find a hundred bugs, and since it's Open Source, entirely free. Unit testing everything just to get their piece of code accepted in a frameworks source makes people very proud and you don't have to pay a cent for the reliability these frameworks offer.

So that's 2-0 for PHP OSF ! Great!



Now let's take a look at maintainability and extendability

How are your projects being maintained ? Another "if" maybe? Well let's just say, you're one hell of a programmer and you create object oriented code that uses design patterns in a reasonable manner and is really great to maintain. You have a great FW that is easy to maintain, but almost certainly just for you, with no real documentation no one will even look at it (unless you have some fanatic followers of course ;) ) and it takes a lot of effort to upgrade some part of the application. OSF's again have the power of COMMUNITY that creates updates, bug fixes, and the code is just from start designed to please the most of users, not just one.

Extendability is tightly coupled (ironic isn't it?:) ) with maintainability, so I wont;t get in to deep. The punchline here is, if you create code that can easily change from eg. JSON output to Array output, without changing any conditional statements you almost certainly create code that can be extended to output XML or something else.

A clear 3-0 for OSF...



Now comes the fun part, performance.

Many developers believe that they truly do good for the project by repeating some overheard information with no real scientific background. NO! If you want to be sure, that you create faster code -> test it. Nothing is just faster or just slower.

In my time I've created a version of Zend_Acl that was made entirely with arrays. Since the code almost entirely run in some simple foreaches, it was 10-20x faster than the original. If you take into account only the speed of working, not the speed of development - you are sure to create code that is used only one and rewritten every time. Speed is not a scalar value, its a function of functionality, so most probably, making really good OOP into procedural programming, will make it works faster, and less memory bound, but also will make you work longer to get to the same place.

In my opinion, OOP is not always the best approach, for high traffic websites, most often used load balancer is something like $readServer = $readServerSet[ rand(1,10) ]; It gets the Job done.

Custom made frameworks are in most cases faster that the OOP approach of for instance ZF. It's a question of power really, the simpler the framework, the faster it runs. OSF's are made for many people, not just for your project, so the creators had to compromise function and form.

I will give one for custom frameworks here, but it's not really a win, as I'll try to explain later. So 3-1...

The real kicker and the whole idea of OSF's is to decrease development time because that's what it's all about. the purpose of most applications is to get the creator money :) I know it's pretty harsh but it's the simple truth. So what does it give us, that we have a team skilled in one framework, like ZF, with a lot of extensions developed, with no time spent creating, testing, maintaining their own custom FW?

More Money!

That's what its all about, the time spent developing is one half or even less that in custom frameworks (not enterprise level frameworks of course) and that results in a lot more projects being created, and more money earned.

Needless to say +2 for OSF's ! That's 5-1 !!!



The conclusion:

So for all you rich bastards, that can afford developing shitty frameworks, by unskilled developers who still think, they can do better than Zend, Symfony, Doctrine or whatever...

... think with your wallet :)

best,
Peter

My favorite Christmas songs

Hi!

So this is Christmas...

the time of the year when we basically forget about our computers and focus on the important things in life... eating yourself sick with your whole family.

No Christmas would be complete without a good selection of seasonal music that accompanies us every year, here is my favourites on the subject:

1. John Lennon - so this is Christmas

This song brings back old memories of one of my first Christmases I can remember (surely because the song has about 30 years).

2. Wham! - Last Christmas

A nemesis of polish radio for the past 10 years. Just no holidays without it.

3. Bobby Caldwell & Vanessa Williams - Baby It's Cold Outside

Song written in 1944 by Frank Loesser though the years had a lot of great interpretations but my favorite one is from the '96 Vanessa Williams album - Star Bright. BTW she is a very beautiful woman ;)

4. Skaldowie - z kopyta kulig rwie

This is a polish song from 1968 so you probably wouldn't understand much, but the main theme here is a sleigh ride through the forest (you can find some places that still do it by horse).

5. Chris Rea - Driving Home for Christmas

This list isn't in any particular order because my private Christmas "experience" starts with listening to this song while driving in a snowy, icy road through the forest. The best song ever!

6. Mariah Carey - All I want for Christmas is You

Very warm song performed by the pop diva brings up very warm feelings for sure. This will worm us up in those long and lustrous winters to come.

7. Kenny Rogers & Dolly Parton - Winter Wonderland

Some might say it is a bit corny, in my opinion its a beautiful song that has a lot of positive energy, plus the young Dolly Parton was surely something warming up to look at ;)


Well folks, this concludes my list, feel free to add suggestions to the list,
Have a quiet holiday with your families.
Peter

Control authentication with KeePass and Dropbox

Hi!

A few weeks ago it's been brought to my attention that may people have problem with dealing with password sharing and access control of may resources at the same time.
Imagine you have a team consisting of:

- system admins
- programmers
- marketers

Every position requires a different set of passwords, usually there is a lot of passwords (20+) that for security reasons should not be the same text.

I've actually came across a nice, free and fast resolution for the problem.
KeePass (and for Linux KeePassX) is a free password database that can help put in situations like that.
Your team just has to remember the password to the KeePass database. Every access type, or even person can have it's own database, but there emerges a problem of password versioning. Every change of every password would trigger and endless wave of emails to your employees about the changes. Actually there is a neat resolution for the problem as well.

DropBox is a great network service which i love to use. It makes sharing files between my computers at work and home really easy. No more sending yourself emails with files, just drop something to your dropbox folder on one end, and it automatically updates everywhere.

The idea for sharing with dropbox is that you need to create a "company dropbox" account, that everyone is connected to. Every time you or anybody else updates a file everyone that needs to know about the changes uses another password.

Lets summarize what are the benefits here:

- passwords that don't need to be remembered can be very long and complicated (KeePass has a pass generator too)

- an employee has to remember:
1) Dropbox account and password, that's two
2) Keepass db password, that's one
So only three passwords to remember, plus one per every database the employee has access to.

- passwords are automatically updated across our business

- passwords are pretty safe, keepass encrypts with a 256-bit AES or a 256-bit Twofish algorithm that are considered to be very secure by the cryptographic community

Drawbacks:
- every time someone quite their job, you need to change all their passwords + the drobbox password. Changing all passwords actually isn't that much of a problem, since dropbox + keepass resolves it.

- there is limited possibility to create passwords per employee

After this short summary IMHO this is an easy and secure way to share your passwords (or just keep your passwords at bay) without any serious knowledge about security. You may want to check out the keepass plug-in page for additional functionality http://keepass.info/plugins.html

Thanks, and see you next time,
Peter

Multiple repositories in one with svn:externals

Hi there!

Recently I had a problem with my services:
- I had 2 separate websites that use a kind of token authorization system
- I had a lot of code that was used in both web services

Of course the only smart thing to do when you have a sender and receiver on both ends is that its performed by the exactly same code as follows:

Sender service:
$token = new Token();
echo $token->generateToken();

Receiver service:
$token = new $token($_GET['token']);
echo $token->isValid();

So what we need to achieve here is pretty arbitrary to the point I'm making. Just a simple sending and receiving of some token that has to be validated on the other side.

The problem is that those two sites are entirely different projects that share some common logic. The mistake people make is to copy the common code between the services in the hope that no one will change it, or when changed, someone will change it on both ends. There is a lot of room for mistakes here.

Luckily we have svn:externals to help us with the whole process. What we want to achieve can be summarized in a few simple steps:

1) Isolate the common code in another repository to protect it from unwanted changes. Let's say we will name the repository CORE.

2) Remove the obsolete code from all code base in which i was present.

3) Add a external checkout to our library folder:
>svn propget svn:externals library
core https://svn.example.com/core

What this will do is when someone updates the library folder, svn will know to get the external items from the common code repository.

In this example you will have to commit your code to the external repository, but an easier way is that you create a directory for the code in one repository and add a directory externally to another repository that requires the code.

Hope you will take something from this simple example,
see you next time ;)
Peter

Starting your own IT business? A few lessons to be learned.

Hi!


The thing is I decided to start My own business (http://fasttrack.pl but it isn't much to look at, still working on it), producing software and websites of course. After some three weeks of muddling through the polish law system I finally managed to start it officially :)

After a few months I have some things I want to talk about that may help you in your endeavor.
Aside from all problems, the first assignment (damkorepetycje.pl), a small co repetition system was quite an interesting task. Not only learning (the hard way) that absolutely everything must be perfect up to 1 pixel, but also learned how to employ people. You always have to be in control, if you outsource some project, every few days look at the results, the time/hour costs.

Lesson one:
Always have a written contract with the functionality annexed to it.

Even if someone won't pay you, you still have a chance of getting back the costs.
It's an industry standard so you won't have any problem finding templates on the web.

Lesson two:
Include the first changes (except obvious bugs) in the price, for next changes bill your client separately.

As a young business you lack credibility. The clients are unsure if you won't run and leave them with the problems on the website. If you decide to include project maintenance over an year or so in the project you might get bigger projects.

Lesson three:
If you work with someone, keep track of the work being done.

If you leave everything to your employee/subcontractor there is a big possibility that the client won't get what he/she ordered or anything at all. Every few days have your employees (programmers) show you whet is accomplished and what is still on the to do list. Tell them what has the highest priority for the client. I personally use Redmine to track progress, keep in touch with clients (although there are better tools for that out there), bug tracking and calculate bills (from spent time).

If you're looking for something more sophisticated, actually JIRA has some event where they give you a starter package for 10 users for $10 so its really cheap (for some charity I think).

Lesson three:
Don't ask people to work for you, let them ask, just be in the right place.

Over time I've worked with many of my colleagues that were working with me for some time. Really bib disappointment, pretty much nothing was done right. I've learned to let people look form me, not the other way. Some of the best employees were hired from some small cities university IT forum. The main thought here is - look for people in niches not on php.com or something like that. You wont get someone that is very much skilled but they sure are motivated and in my opinion that's the key to success.


I only hope that the lessons I had to learn the hard way will be of some use to you, my Dear Readers :)

See you soon.
Peter

New exciting work to be done

Hi ppl,

long time no see. Fortunately my new business (creating www software) is growing fast, so I didn't have much time to write but a lot of stuff happened.
I got a new job, a new car and and moved to a quieter city :)

The car:



(not the actual photo, but damn close)
Black Jeep ZJ '96 - sounds pretty old - yep but the advantage is that its a V8 engine with over 200HP, makes a lot of fun:)


The city:



Sopot - it's like the Polish Beverly Hills - "the place to see end be seen" in the summer, now in the early winter its just a nice place to live :)

The job:



UseItBetter.com - a great new adventure. A very innovative project - recording everything that happens on a flash website -> store it -> analyze it -> draw conclusions. The basic idea here is to know what you need to change on a flash website to raise user experience. There are some really interesting technical difficulties that will eventually need solving:

1) The project has potential - fast growth isn't a problem if you have the infrastructure or use a cloud (and your application has basic scalability possibilities)

2) A lot of simple traffic coming in - recording user traffic is a lot of inbound traffic. The architecture has to take into account that traffic can vary with pretty big amplitudes.

3) Big OLAP queries to execute - trends over time, user behavior changes. Analytical queries have a very much different profile that the usual website OLTP stuff, some things just take long, some can be made faster (I hope ;) ).

4) What happens if you get a client that wants to record a lot more? Is the system scalable enough to handle bigger players or is the query execution time multiplier equal to the size of the database?

Some interesting month are ahead, we are yet to an official product start (it's currently in semi open BETA). I hope I will have the pleasure to write about interesting problems, and their smart resolutions.

I didn't write anything about my business, but that's another topic all together :)

See you soon,
Piter

Some feedback on Google Closure Templates

We've been using GCT at myvimu.com (My Virtual Museum). After the introduction of the new technology, we had some problems that needed solving:

1) The GCT compiler is a pretty unhandy tool, after some php modifications, we managed to get results faster.

2) No predefined directory/file handling structure was a big problem. I personally hate the directory maze of some developers, we had to keep it simple yet practical. A simple structure of front-end ( guest users ) and back-end ( logged in users ), and an additional categorization per structure, made every decision about where everything should be really straight forward.

3) Loading of the templates should be automatically. We'll soon migrate to a multilingual system, that has to work automatically from the js template side, so auto loading was an issue, but no more.

Google Closure Tools is a great tool, that, if used with a little brain, can make Your js look and feel a lot better. It's something worth recommending.

Organize your spaghetti javascript/java with Google Closure Templates

If you ever developer something in javascript that has to have some html, its a real chore, for me anyway. Plain dom manipulation is pretty easy, but what do you do when you need to insert some dom nodes that weren't there before? I think it's pretty common to write something like this:

//jQuery javascript:
 javascript: 
 var someHTML = 'A new paragraph!';
 jQuery('#paragraphContainerDiv').html( someHTML ); 

The code, if you have jQuery of course, should insert the paragraph as a new node in the selected container. When you're a more advanced js developer, you'll probably try to generate the nodes with some js functions and then append them to the containers and so on, which by the way make things a lot more complicated to the person that maintains the code later on. If you are smart you'll probably think about templating. The normal approach would be to write some functions in which you input some template data:

//javascript: 
 var Template = {
 helloThere : function (name){
  return 'Hello there '+name+' !';
 }
 } 
 document.write(Template.helloThere('Piter')); 

When you paste the code into you firebug, you probably should see a "Hello there Piter !
" text. What we have achieved is to unbind the html from the javascript, which is the right way to go, but still when you look at the Template.helloThere() function, its a pretty messy piece of code. Let us add some internationalization features like so:

//javascript: 
var Template = {
 helloThere : function (hello, name){
  return ''+hello+' '+name+' !';
 }
 }
 
 //english
 document.write(Template.helloThere('Hi there','Piter'));
 //polish
 document.write(Template.helloThere('Witaj','Piter'));
 //french
 document.write(Template.helloThere('Bonjour','Piter'));
 /japanese
 document.write(Template.helloThere('Konnichiwa','Piter'));

It's not hard to imagine how this template would look like within a real exapmle that has 20-50 html tags, full I18n support and additional data variables. Even if you would pass the variables as arrays the main problem stands: spaghetti html and js.
Fortunately there is a way to rapidly develop templates and manage the very easily, especially in a team when the js programmer and web developer are two different people. The answer is Google Closure Templates. The pretty recently featured Template engine is used among the most commonly used G applications like Gmail, co as Google says it's well battle-tested :-), and well documented. It not only speeds up development of Javascript/Java but also makes the code a lot easier to maintain and localize. Let's Have a look on what the code above would look like in GCT, but first you need to follow the instructions on Google code Closure site, in a shortcut (for windows/javascript):

1. You need to install Java Runtime Environment,
2. Download the closure template compiler (for js)
3. Unpack the library and put it in some locally hosted folder (you need Apache or some other web server)
4. Create a simple.soy file and start editing it

//soy example
{namespace Template}

/**
* Simple soy file example
* Here you declare variables via the documentation comment
* @param hello - the hello message
* @param name - name of the greated person
*/
{template .helloThere}
 
  {$hello} {$name} !
 
{/template}

What you want do do is to compile the simple.soy:

java -jar SoyToSrcCompiler.jar --outputPathFormat simple.js simple.soy

If you get some exceptions... deal with them :) They are usually pretty strait forward and easy to understand. If there are no errors, you'll get a simple.js that is the javascript compiled version of your template file. After linking soyutils.js and simple.js to a html file you can post the code in a script section or just into firebug:

document.write(Template.helloThere({hello:'Hi there', name: 'Piter'})); 
 document.write(Template.helloThere({hello:'Witaj', name: 'Piter'})); 
 document.write(Template.helloThere({hello:'Bonjour', name: 'Piter'})); 
 document.write(Template.helloThere({hello:'Konnichiwa',name:'Piter'})); 

The code looks very similar to the simple javascript function but it has a real advantage, you can request a ready template from your web developer and just fill the gaps with variables, what makes developing really fast and easy for everyone. You even can put your translations in another file so your translators can work at the same time too.

Summarizing Closure Templates have every advantage a normal e.g. php based template engine has, like variables, globals, loops, conditional functions and more. Everything is documented at http://code.google.com/intl/pl-PL/closure/templates/docs/overview.html so as my Linux addicted friends would say RTFM and start using Google Closure Templates. Also there is no catch, Google serves it free under the Apache License 2.0 and encourages strongly to use them.

BTW Still hate Bloggers text editor... that is why every post looks different :)

Get a load of your database - paginated caching

Your site is getting awfully slow? There's just to much reads to your database and you have already tweaked the performance of every query? In most cases data caching is the solution to your problem!


The idea is to cache all processed data you heave retrieved from the database. Let us look on a example. It uses a mockup class that basically can handle any caching system like memcached or xcache:


php:
//just a mockup
abstract class cache{
    static public function save($pool, $data, $ttl){
    //some cache processing
    }
    static public function load($pool){
    }
}


Now what we want to do is to save time by implementing caching on heavy load environments. The way to save some execution time and/or decrease database load is to search the cache first, before even querying the db:




//first define what we will save/load adn the time
//the pool name is mainly for verification (if you even write/read the right thing) and is optional


php:
$pool_name = 'somePoolName_';
$data_name = 'someDataName-userId';
$ttl = 3600;

//load data from cache
$data = cache::load($pool_name.$data_name);

//if theres no data in the cache, we will get a false/null value
if(!$data || $data == 'thereIsNoData'){
    $data = DB::exec($someHeavySql);
    //here's a sensitive point where many people make a mistake
    //if you would just save the data and go on with your life, there is a big probability that
    //you would save a NULL/false value. We need to avoid that

    if(!$data){
        cache::save($pool_name.$data_name, 'thereIsNoData',$ttl);
    }else{
        cache::save($pool_name.$data_name, $data, $ttl);
    }
}


Every time someone generates a page view, the data is either cached, or retrieved and the cache is field. Either way we avoid to execute "$someHeavySql" for $ttl seconds. That's just the easy part. What we need to accomplish here is cache almost everything including paginated results.
It's not hard to imagine the need to paginate query results. Let's just think about a site with products. There are about 500 products in the sites database, and there is no way of showing everything on one page (maybe in 1px font size ;-) ) Because the products page is so popular that our database is barely handling all requests, we decided that we will use a caching layer to help the database a little. The problem is the whole pagination issue. When we cached every site like above, adding the page, we encountered a problem.


php:
//pool name, data name, page

$pool_name = 'products_';

$data_name = 'mainProducts-'.$page;

Every time we change the price of a product, we need to delete the cached data. The problem is that we never know on which page is the product we changed, therefore which one to clear. Obviously we need to delete all the cached pages, the product could change, it could be deleted or there could be a new one inserted. Either way the whole cache becomes obsolete. We could iterate and delete all the pages, but that would be very time costly unnecessary. What we want to achieve is to give the cache name an additional parameter.

php:

$cache_version = cache::load('someCacheWithTheVersion');

$pool_name = 'products_';

$data_name = 'mainProducts-'.$cache_version.'-'.$page;

Now when we want do delete the cache, we just increment the version via cache. All the older cached pages become unused and eventually get deleted by the caches garbage collector (if present). Unfortunately we need to make and additional cache request for every page, but it still saves us a lot of resources.
Another problem is how the development cycle of a site. When for instance you have an array with 10 keys that you always cache, and you've made some changes to the structure of said array. What will happen if you sync the new code do your site? You can imagine the things that could happen from simple errors to site wide errors, data inconsistency and so on. You can flush the whole cache but then your databases will get overwhelmed by all the requests, that of course get cached eventually but firs will produce a very nasty vain on your DBA's forehead ;-). The easiest way to ensure the usage of new data is a additional version attribute for each cache pool:

php:

//name-version array

$caches = array(

'namesCache'=>1,

'someOtherCache'=>3,

};

$pool_version = $caches[ $cache_name ][0];

$pool_name = // the poll name and version number, then data, pages and so on

You don't even need to increase the version numbers, just make sure they change every time, and include them in the pool names within your caching layer class.

Hope this helps someone to get started with caching data :-)


BTW: I'm really starting to hate blogger's WYSIWYG... it is really annoying...

Sphinx and big integers

I encountered a problem that can give headaches even to the toughest programmers. I think that the solution will be quite a relief to some people.
The problem is when indexing in sphinx, the indexer sometimes prints out a warning/error message:

WARNING: DOCID_MAX document_id, skipping

which by the way is very annoying because it stops your indexing :)


I've searched the web for some solution, but I didn't find anything useful. When I experimented a little with the query I have discovered that the base id's are simply to big. The sphinx was built without the --enable-id64 option, what introduced only 32 integers and not micro timestamp id integers that have about 15 digits. 


Hope that this solution  helps some some of you to get to like sphinx ;-)

What motivates IT professionals?

In my practice as a programmer I've come across some interesting people. A lot of my friends and co-workers of course are programmers, designers, network administrators. I is quite fascinating to look from a objective position on everyone and watch what motivates them the most. I've seen a few separate types of things that motivate people around. Here are a few person types I could distinguish:

1. The Banker - Money is the most obvious reason to go to work every day, but not the only, and for many people not the main motivator. Bankers tend to work better given small raises/bonuses now and then, and they are absolutely not to argue with if they want a get-or-leave-raise. The words "either I get a raise in pay or I'll leave" are dead serious with this kind of people, they most probably already found a new employer and just want to give you a last chance to hold them in your company.
2. The Creator - this part of the motivation pie is a pretty big piece in most of my programmer friends. The Creator creates stuff just to see it work ( to see it being used is IMHO something else ). People who are motivated by creating can write something that was written a lot of times and try to make it better every time. This is pretty much the "easiest" employee to keep happy, a creator type just needs a computer and most often someone to participate and share ideas with.
3. The Innovator- now innovation is seen pretty often among IT workers. The Innovator absolutely hates having people to him/her to make another message board or log in form. He/She like a cat wanting to go his/hers own ways. They thrive on new and interesting endeavours and ideas. Now that's a great thing, but people with this quality tend to produce unstable, poorly maintainable solutions, so they can work on another interesting thing. When you have an enough innovative field in IT, you can easily hold the innovator employee, given that he has a choice to go somewhere with a little better pay but a boring business profile.
4. The Egoist - over the time I've seen many people thrilled by the idea that something they create will be used by a large number of people and/or his/hers friends. I think that an Egoist is a Creator that does it just for a different reason.The Egoist is very hard to keep happy. Statistically I can just say that most graphics designers, artists basically, are the egoist type. People with that quality tend to work much harder and better given the right amount of "ego steroids" like ensuring they are the best people for the job or that their work will be most certainly recognised by many people.

Here is the place where the "worker-key-system" comes in. Try to imagine a IT professional as a kind of a small piano keyboard and his/hers employer as the player like in the picture (the employer being the rat ;-) ) 

[image source: www.pixdaus.com]

Now what you want to do as the boss, is to play a good and robust song, or to make a good and robust program with the least amount of spending. This keyboard is a very funny instrument. It already knows some songs, but it won't make absolutely no sound when stricken in the wrong key, and will eventually break if you won't strike the right key with the right force, and produce the same song if you strike the right keys with more force that they need.

Leaving all metaphors, only a good project manager can ensure the right amount of money, creation, innovation and ego by hiring the right people and balancing out the proportions to make a great product with the least amount of money. If your project is another CMS like millions out there, don't hire innovators, the surely aren't the right people for the job, because they'll get bored and leave for a more interesting position. If you are creating a start-up, don't hire bankers, they are very good at what they do, but they will leave your project when given the first opportunity to get more money. There surely are many more aspects to take in account but in my opinion these are the main things that drive IT specialists to work better, harder and enjoy their work more, thus generating many good ideas and products for your company.

How to build a fast, good scaling user notification system pt. 2

This post is the second part of my earlier post on How to build a fast, good scaling user notification system where we discussed the problem areas of the said system. This post will be mostly about a strategy on the infrastructure to store all those notifications and retrieve them as fast as possible.
The most common approach to store data like user notifications would be to create a table containign a PK id, timestamp when the notification was added and of course some additional meta data, for instance:

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `date_added` timestamp NOT NULL default '0000-00-00 00:00:00',
  `user_id` int(10) unsigned NOT NULL,
  `content` char(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Every user entering his/hers notification page (which btw is the most frequently visited page) will most probably generate an sql like this:


SELECT id, date_added, content FROM `notifications` WHERE user_id = 123 ORDER BY date_added DESC limit 0,10

You will need the date_added to show to the user when the event occurred, the PK id to take some additional actions on the event and finally the content of the specified notification, we also have to apply a limit to the query to paginate the results. To make queries faster let us create a covering index for the query e.g. modifying the primary index or adding a separate index:

ALTER TABLE  `test`.`notifications` DROP PRIMARY KEY ,
ADD PRIMARY KEY (  `id` ,  `user_id` ,  `date_added` )

Don't get me wrong, that above is a perfectly good system to store user notifications, it is simple, pretty fast up to a certain point, but we can do it better.

First let us think about the holding of chronological data. When you insert data in a cronological order, the PK id is always in some correlation with the timestamp of when the event occurred. Both fields are always growing but we don't really need the id, only the timestamp is of some value. What we can do is create an ID before inserting the data:


php:
$micro = explode(' ',microtime());
define('MICRO_ID', $micro[1].substr($micro[0],2,5));


That piece of code defines a constant consisting of 10 digits representing the current timestamp and 5 digits representing the micro timestamp. So we have a unique time-related ID that we insert instead of the auto-incremented id and the date_added, and we get a free! index on the timestamp value. To prepare the new structure first we need to change the `notifications` table:

ALTER TABLE  `notifications` DROP  `date_added`;
ALTER TABLE  `notifications` CHANGE  `id`  `id` BIGINT( 15 ) NOT NULL;
ALTER TABLE  `notifications` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `id`, `user_id` );


Let me show you an example of an insert in a ORM like Doctrine:

php:
$nt = new Notifications();
$nt->id = MICRO_ID;
$nt->content = 'Some event description';
$nt->user_id = 1;
$nt->save(); 

Surely ;-) you wonder "How about an multi-server environment?". Well, actually I've seen it work in a a big (100+ servers) environment with just 3 digits of micro time and it worked just great. The key here is to make a combined primary index. The only issue we need to face is when we insert two notifications to the same user in the lifetime of the same program. To be extra careful we can use a ON DUPLICATE KEY clause or simply increment the MICRO_ID constant by 1 via a new variable of course. To construct a proper index, we need to look at the new query retrieving the data:


SELECT id, user_id, content FROM  `notifications` WHERE user_id = 123 ORDER BY id DESC LIMIT 0,10


Because of the user specific nature of the query we have to rebuild the primary index. The trick is to make a combined index on the user_id first and the id, which is the pk, second. When you'll try (try it) to make it the other way around, MySQL will have to make a extra pass to sort the values by id, therefore making it a lot slower (I had a performance drop of over 10 times !).

ALTER TABLE  `test`.`notifications` DROP PRIMARY KEY ,
ADD PRIMARY KEY (  `user_id` ,  `id` );


The explain plan for the SELECT query above:

select type: simple;
type: ref;
possible keys: PRIMARY;
key: PRIMARY;
key len: 4;
ref: const;
Extra: Using Where

That looks pretty nice. Our query uses the index we have created earlier and it is very fast, in addition we save a whole lot of space that the timestamp field would take up.

The normalization of the table was left out for last on purpose. We need to divide the `content` field into some smaller ones. The real advantage of this kind of notification system is that the whole text is in a template which is used by a object in the program itself (example hierarchy generated with yuml.me). You can of course use some complicated design patterns to enhance the flexibility of the system but the baseline is pretty simple:


In this example \tThe render() method generates the html for each notification row via the template set seperatly for each subclass and a prepareData() method to make additional processing of raw data. We obviosly need another field in the notification table containing the type id:

ALTER TABLE `notifications` DROP `content`;
ALTER TABLE `notifications` ADD `type_id` TINYINT NOT NULL ,
ADD `info` CHAR( 100 ) NOT NULL;

The info field stores all the ids, of short text information you need e.g. two ids and some text '123||456||some text' that you can split later on in the mentioned prepareData() method before using the render() method. I'm not going to carry on about the implementation specifics, we just need to know the baseline of the system. We have successfully created a table structure and a class hierarchy to select and insert user notifications. The next post will handle about inserting multiple notifications (notify multiple users about an event), and the structures to provide such functionality.

by the way... WYSIWYG in blogger is really lame... thinking about going to wordpress...

Get data without reading it - the power of covering indexes in MySQL

It's no real breakthrough, but it can have a very significant performance meaning to use covering indexes. Now you are sure to ask what a covering index really is? The answer is quite simple. If a index can provide all the data a query needs it becomes a covering index for it thus covering all the data the query needs. Still sounds too complicated? Let's have a look at a simple example. Let us suppose a pretty common example. A table with users, ids, birth dates, join dates. For some marketing purposes we need to determine the average birth year of users that joined us from the beginning of 2006:

1. Let's create a simple table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `birth_date` date NOT NULL,
  `joined_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ;

2. We need to insert some random data. To make it a more real-life case I inserted 1 000 000 rows, hope my notebook (which has the test mysql running) won't explode ;-)

INSERT INTO `example_table` (`name`, `birth_date`, `joined_date`) VALUES
[a few pretty much random records]

3. What we need to acomplish is the result of a simple sql query:

SELECT AVG(YEAR(`birth_date`)) FROM `users` 
WHERE joined_date >= '2006-01-01 00:00:00'


First I'll try to calculate the average using the AVG() function without any indexes except the PK index. The explain plan looks something like this:

select type: simple,
table: users,
type: all,
possible keys: null,
key_len: null,
ref: null,
rows: 1000000,
extra: Using where

The "null" after the possible_keys indicates that there are no indexes to be used with this select. There is pretty much no difference if the query cache is primed or not if you have to fetch and search for the data making disk I/Os. Let us add two indexes: one for the `birth_year` field and one for the `joined_date` column and see how it looks on the explain plan:

ALTER TABLE  `test`.`users` ADD INDEX  `i_birth` (  `birth_date` );
ALTER TABLE  `test`.`users` ADD INDEX  `i_joined` (  `joined_date` );

Explain plan:

select type: simple,

table: users,

type: all,

possible keys: i_joined,

key_len: null,

ref: null,

rows: 1000000,

extra: Using where

You can see that MySQL tried to find a fitting index to execute the query but still it took forever with an empty query cache, an eternity. That's clearly the wrong way to go.

Now we will create a covering index for the said query.

ALTER TABLE  `test`.`users` ADD INDEX  `i_birth_joined` (  `birth_date`, `joined_date` );

The index has all the fields the query needs to be executed so the explain plan :

select type: simple,

table: users,

type: index,

possible keys: i_birth_joined,

key_len: 7,

ref: null,

rows: 1000000,

extra: Using where; Using index 

The changes are quite clear. The covering index we created caused MySQL to use it not only in the search operation but also as a data provider. That speeds up thing a whole lot but it also slows down every INSERT/UPDATE operation with rebuilding a large index.

Summarizing:

While creating a covering indexes is a good practice to speed up select queries, you have to take into account the downside of building/rebuilding large indexes that cover a lot of data, the obvious extra space the indexes take up in memory and disk space, or finally the temptation to make too much indexes, instead of taking some slower queries to the background via e.g. a cron process that fills a cache.