MySQL database Index – Part 1

Why MySQL database index does not help improve performance? And how to use New Relic to find out the cause.

I went through some of the sql tutorial on how to create index in MySQL. It took very little time for me to create my first MySQL index, and job done. That’s what I thought…

After 1 month launching the API, we decide to integrate it with existing sites.

Table A starts from less than 500 rows to 370000++ rows after importing all data from different sites. Immediately the response time of any functions that SELECT from the table can go up to 3000 ms (that’s almost 3 seconds).

After created the index, nothing seems to improve, after observing New Relic for sometime, it showing analysis of index using “PRIMARY”. The query is not using the newly created index.

New Relic Query Analysis before MySQL database index hint.

New Relic is showing very awful and horrible average response time!!

New Relic slowest average response time before MySQL database index hint.

 

Thanks to some tips from a MySQL guru, it seems that MySQL will auto decide which index to use to execute your query if you didn’t specify any.

By using MySQL’s “Index Hint Syntax“, I’m now able to force the sql query to execute with the specified index. :)

SELECT * FROM table1 USE INDEX (indexname1,indexname2)
  WHERE col1=1 AND col2=2 AND col3=3

Ran the same query on MySQL Workbench and immediately i got results with huge performance differences.

Executing MySQL database SELECT statement with and without MySQL database index hint.

After the deployment of the new code by adding “USE INDEX“, New Relic is showing some nice average response time. NO MORE 3000 ms response time!!

New Relic slowest average response time after MySQL database index hint.

 

 

Even though there is huge performance improvement, but I feel that there is room for improvement. After all, New Relic suggest that i can do more. Here is what New Relic suggest. :)

New Relic Query Analysis after MySQL database index hint.

Compress And Client Side Caching Static Content With AWS S3 And CloudFront

I was given an assignment to improve our page load speed after we moved from Akamai to AWS CloudFront. The page load time increased after the switch and this raise some concern, Google’s Page speed and YSlow showing lots of warning which was no shown previously when we are using Akamai.

Few of the factors that cause the slowness are Compression and Caching was not enabled in S3 and CloudFront.

If your storing all your static files in a IIS or Apache server, there are already easy ways to enable compression to your static files. What about S3 and CloudFront!? There isn’t any options for me to do it at the admin console.Took me quite sometime to find the solution as AWS forums and their information didn’t provide and clear answer to me.

Prerequisite

  • 7-Zip
    http://www.7-zip.org/
  • Amazon Web service (AWS) S3
  • AWS Cloud-Front
  • CloudBerry Explorer for Amazon S3 (Free Edition). Download here.
  • What you need to do at your Code

Size Comparison

Check out the size difference after “GZip-ing” your css/jsAWS S3 Gzip-ing comparisonNormal jq.js in firebug

Firebug checking

Gzip-ed jq.jsgz in firebug
Checking in firebug

Walk through

GZip-ing

1) Download 7-Zip and installed to your desktop
2) Start zipping your files
3)Choose “Archive format” as “gzip” format highlighted at the image below.
4) Rename your file extension from “.js.gz” to “.jsgz” and your ready to upload to S3.
Rename your file extension

 

AWS S3

1) Talk to the infra guys and ask them to create a AWS S3 account for you.
2) Download CloudBerry Explorer for Amazon S3 and setup it to access your S3.
3) Start uploading your files to S3 by drag and drop your files to CloudBerry Explorer.
**You are required to upload both the original file and the gzziped files

4) The following dialog box will pop up everytime you drag a file into the explorer. Click on “Set http headers” first
Cloudberry S3

5) Start adding the following http headers to your files.
Cache-Control: max-age=31536000, no-transform, public
Content-Encoding: gzip
Content-Type: application/x-javascript (or text/css depends on what file type you have ziped)
Expires: Tue, 01 Jan 2030 03:54:42 GMT (Pick a date that is far far away from todays date)

**31536000 = 1 year
** You DON’T need to set ‘Content-Encoding: gzip’ for non gziped files.
It will look like something as below once you finish adding the headers.

Set Http Headers

 

What you need to do at your Code 

This is the code to check if the client’s browser support gzip, if not support, will return the normal file. Below are a sample done with ASP.NET and C#:

public static string GetStaticFiles(string fileUrl)
{
    if (HttpContext.Current.Request.ServerVariables["HTTP_ACCEPT_ENCODING"].Contains("gzip"))
    {
      fileUrl = fileUrl.Replace(".css", ".cssgz").Replace(".js", ".jsgz");                
     }
 
     return fileUrl;
 }

 

<script src="<%=GetStaticFiles("http://www.domain.com/js/scripts.js")%>" type="text/javascript"></script>

 

Lesson learn

Lesson 1: iPad & Mac OS’s safari doesn’t accept .gz
Solution: change file extension from .js.gz to .jsgz

Lesson 2: IIS treat .js.gz or .jsgz as file
Solution: If your local/test environment is not referring to the AWS S3, put the “gzip” key at your configuration file. So that you can switch on or off serving gzip-ed contents without changing your codes.

References

 

First experience with Uhuru Cloud

First experience with Uhuru Cloud

It is a “Heroku” for .Net Guys like me, no learning curve for learning command lines to deploy or setup your website or creating a database instance.

Supporting some of the most cool programing language (I only tested out .NET, PHP and Node.js).

Services include them common databases like MySQL, MsSQL and MONGODB. (it is in capital because I tried setting up it on heroku and the command lines kills me a lot.)

Managing with Uhuru

Uhuru comes with a Web Console and a Uhuru Cloud Admin (a Window application, will label it as “UCA” in this post ) which you can installed to your windows and manage your instances with ease.
Response of the UCA is still acceptable, you can create the services like new database, new FTP access to your code folder.

You are also able to assign and manage your team members’ permission like accessing the FTP, databases via the web console.
There is also Visual Studio plug in which you can do management as well, didn’t tested this out yet cause it only supports “Visual Studio 2010″ (I’m on Visual Studio 2008 Standard)

You can find out more on here.

 Tunnel

To access your database or setting up FTP services, you are required to open the Tunnel (something like your infra guys setting up a VPN for you to work from home).

FYI: not all the cPanel type hosting package lets you connect to MySQL remotely, not sure what were they thinking when they come up with their setup.

Find out more at here.

Learning Curve

It only took me half day to deploy a web site, testing out the database (MySQL, MsSQL and Node.js), setting up the FTP and play around with the other features.

Didn’t go full force on testing the performance yet, but will update it in the future.

The not so good stuff:

  1. I can’t find the section to add new team members on the UCA
  2. Not all function is available for command line yet. Tried to setup on my Mac OS and there were some head banging on the table moments, for company that needs to “Puppet”-ize the server for auto scalability (like mine), the “Puppet” master will have tough time trying to implement the scripts.
  3. There are no option for me to setup “Database replication” to make a Master (for data manipulation) and Slave (for read only) environment.
    If your hosting a simple site for your client, this won’t be a show stopper for you.But if your building a product where you have to handle huge traffic, you’ll have trouble scaling without “Replication”.
  4. It is already a very good service since uhuru supports MongoDB, but supporting “Apache Solr” and “Memcache” would be a awesome bonus.
  5. Currently their data center is hosted in California, USA (based on the engineer that support me), so you can’t expect stuff like CDN or location based hosting like AWS and Azure is offering.

Overall

Is a excellent platform for me to host prototype projects, where they support most of the cool technology I’m using.

Also I would use it to host company’s projects that is done during non working hours, the infra guys won’t give you AWS access and they won’t be working after working hours and asking for VPN access for your team members is a pain in the ass. (Company Policy :S )

 

If you would like to join their free premium hosting for 1 year party, click here to find out more.

ASP.NET MVC vs Yii MVC

yii frameworkI wanted to create a simple web application that allows me manage the data in MySQL Database, I do not wish to waste my time on coding simple CRUD functions. I prefer to generate the codes every time i modified the database.

Been notice that ASP.NET MVC would help me to achieve that, able to find samples of running ASP.NET MVC with MySQL (By default, the codes run with MsSQL).  But in the end, i found out few issues of using it.

  1. All Visual Studio Express version does not allow 3rd party extensibility.
    Means I can’t use Entity Framework with .Net Connector by MySQL to auto generate the “Model” with Visual Studio.
    Not everyone have a Standard(Paid licence) version of Visual Studio, I’m using Visual Studio 2008 Standard for the testing above.
  2. Since I’m using Visual Studio 2008 Standard, I’m only able to use ASP.NET MVC2, which now they have launched ASP.NET MVC3 (with better features and support).
So I have to look for other alternatives, but somehow, “Yii” has been a buzzword among the company. There are even presentation and demo on how to create sites in 10mins or so.
Well, is free and there is so many Gurus around to guide me and give me crash course and all I need to do is to learn how to setup Apache & PHP on my Windows. Why not give it a try… after all, if it is PHP, there shoudn’t be a problem generating CRUD and models with MySQL.
After the completed the setup, I start to follow the quick guide on setting up Yii and guide to setup my first Yii Application. Encountered the problems bellow which took some of my time to diagnose:
  • Warning: date(): It is not safe to rely on the system’s timezone settings
    “WTF”
    was the first thing that comes on my mind, then I goggled and found out Setting timezone in your php.ini date.timezone = “UTC” to fix this.
  • CDbConnection failed to open the DB connection: could not find driver
    Again, “WTF” comes in my mind again.

    1. First, I checked if my DB connection setting was correct for Yii, and it looks good.
    2. Next, lots of google results showing “extension=php_mysql.dll was not enabled“. So I enabled it in PHP.ini, and restart the apache. But I still can’t connect my first Yii application.
    3. Dafaq~~~~ done more google and found a new word call “PDO extenstion”. So I run my personal favorite PHP code (sorry, not “hello world”), phpinfo.php page and looked for the PDO section, nothing was loaded.
      Checked with all the Yii Gurus but non of them encounter this issue, reason is they install Apache & PHP via Xampp and Wamp… :(
    4. After sometime google-ing, found out that I need to do the following chances.
      php.ini set extension_dir = “C:\webserver\php\ext”
Damn~~~~ *@#*&*&%*#&*!&$*&!@($& !!!!
And finally, my first Yii Application runs~~~ Horaay….
So the lesson learn in this experience is, all those tutorial or videos of “Creating Yii application in 10min” is for people who knows and been using PHP.
For a .NET guy like me, we’ll need more time than 10 mins to run our “First” Yii application, because we learn how to setup the server first…. So is 20 mins for to really create our “First” Yii application (if we find a video or tutorial on “setting up apache & php in 10 mins“)
Overall, Yii serve the purpose of letting me do more advance stuff instead of wasting time creating simple and boring management page for internal staff.
Next… The UI control challenge awaits me…

 

Setting up Apache & PHP on Windows the manual way.

There is quite a few easy options to run PHP in a Window OS, there is Microsoft’s IIS, Apache Friend’s Xampp, Wamp Server. Tried setting up via Xampp on my Mac, ended up with lots of confusion after skipping a lot of process.

So decided to do it the manual way, is a bit confusing to search for a tutorial on doing this.
Then comes a youtube video link shared by a PHP fan (Thanks Moh, your a life saviour).

After completed the steps, I have problem starting the Apache Services. Checked the Event Log and found “Apache error – cound not bind to address 0.0.0.:80“.

Realized that I had Microsoft IIS installed and running as well, I do not want to stop the IIS services as my main task are done in .NET. So I went to look for solutions.

I end up change the Apache Server to use a different port from a forum post, here are the steps:

  1. look for httpd.conf which is located in “conf” folder inside your Apache folder. (For my case is C:\apache\conf)
  2. Search for the words that starts with “Listen“, somewhere around line 120.
  3. Change “Listen 80” to “Listen <new port number>
  4. Save and start the Apache Services.
  5. Done

After that, I start testing my first PHP code with phpinfo() (The “Hello World” test seems to be boring for me).

My first PHP code runs and I continue on setting up Yii Framework

 

Building my first Window Phone 7 app.

After winning the “Innovation Day” on 2010 with “iProperty Android App”, we participated on “iProperty Hack day 2011″ with extending the company’s services to the Window Phone 7 platform. The ideas are the same as the Android App, but this time I participate with a team (consist of 1 designer and another web developer).

We didn’t win but we are given a go on developing the app.

Designer
For the designer, it was a golden opportunity to learn about “Metro UI” which personally I feel that the it is one of the key factor to become “iPhone” or “Android” killer. The simplicity and the standard of the design both on the phone and the apps, makes me feel that even my non IT literate dad can use it.

Web Developer
For the web developer, who is passionate on native mobile application development. It was a wonderful entry to the mobile development world.

Why? Because:

  1. Microsoft Visual Studio
    We .Net Developers eat “Microsoft Visual Studio” for breakfast, so we familiar with the IDE. No time wasted on making the IDE run at all.
  2. C# as the language
    Our “Mother Language”, what more can we say?
  3. The “Emulator” was fast and smooth, I was developing in a low end notebook and the “Emulator” still runs nicely.
  4.  Full of resource on how to get started. Oh yeah, less time taken on Google!!
  5. Toolkit that those awesome people created, that have a list of beautiful & fantastic UI component to use.

Me
For me? Well, comparing with Android, i suffered less due to the few factors mention above. 😀

But there is more, we have invested lots of our time creating the web services and the framework for the company’s site. We get to reuse those stuff (done with blood and sweat) without any efforts at all.
Oh yeah, is like adding Wings to them and make them fly!!

The surprising experience of implementing “Image Lazy Loading“, when we are starting to adding this to the app, I explained to the Web Developer on how complicated this is. It was based on my horrible experience applying it on the Android Application, I banged my head on the wall countless times on this!!

After hearing what I say, The Web Developer took over my laptop, added the URL to the image to a image control in the XAML. And run the application, and immediately i saw the “Image Lazy Loading” itself…

Next thing I realized is, open source stuff makes you become a complicated person, and forgot how easy and joyful programming with Microsoft Tools.

Team
By now, you must be wondering whether I’m over promoting. There is  however some challenges during the development.

  1. Some learning curve on XAML
  2. Understanding “Metro UI” and applying to the applications.
  3. Endless (Bottomless) list
    It was the most challenging part in the Android development previously, same situation here.
  4. Live Tiles
    The minimum interval is 30 minutes, so you have to manually run the Live Tiles when first running the application to test if it is working.
    It took us sometime, but glad we figured it out in the end. 😀

We gone through these difficult moments with “Pair Programming“, which really help us shorten the time wastage!!
The Web Developer paired with The Designer to understand more on “Metro UI”.
Me and The Web Developer paired to learn & solve all the obstacles.

We spent 2 to 3 days, and 3 to 4 hours each day after work, paring to solve our challenges, learning each other’s coding skills and knowledge.

Then we take up different areas and work on it after we went home.

Experience Gain
I can say out loud out with confident that with “Pair Programming”, we can learn any programming language we want. We can even conquer the world!!! (A bit over I guess :D)

The links to the application if your interested.
iProperty SG Window Phone 7 App
iProperty MY Window Phone 7 App

Mono doesn’t support ASP.NET Web Site Projects

When I first heard about Mono and it’s wonderful features that allows to host ASP.NET web sites in Linux environment, I have to admit… I jizzed a little in my pants… :B

Imagine hosting ASP.NET code without the need of Microsoft Licence, makes me love Open Source more. Man… I was excited… until today…

The adventures begins when I start setting up MonoDevelop and source control with Git on my Mac, cloned the repository of code (which was previously written with Visual Studio and running nicely in the production server).

Visual Studio's "Open Web Site"

Couldn’t find “Open Web Site” options that I use to open my ASP.NET Web Site in Visual Studio (refer to image1). Tried opening and created few new projects to run my existing codes, in the process, I have to manually add reference to some of the .NET Framework’s DLL (Example: System.Data.SqlClient).

 

 

After few try and failure, I seek answers from the wise “Google”. After that I was screaming OMFG (in my mind) when i saw the few discussions (the discussion that made me drop down from cloud 9 is listed at the bottom of this post).

It turns out that Mono only supports ASP.NET Web Application Project, and not Web Sites Project.

Is a disappointment at the moment, but I strongly believe that the best of Mono will continue to grow and become awesome one day. Until then, We’ll will have to wait patiently.

I’ll write another post to explain why ASP.NET Web Application Project doesn’t fit in a consumer based web site. Stay tuned. :B

Updates on 29 April 2012:
It seems that I’ve made a mistake on Mono, Mono do support ASP.NET Web Site Projects.
Is MonoDevelop that doesn’t support it, a Mono rookie mistake i made like others.
Tested and verified!!
Found out the answer by a link sent by “The General”, click here to find out more.

Reference:

Generate Nested object with Json.NET’s JsonWriter

Been using Json.NET for the pass few months for projects that is related to JSON, a wonderful features provided is to allow you to Serialize your .Net objects to JSON format automatically.

string json = JsonConvert.SerializeObject(myObject);

For my case, my objects is shared between search results and detail page, search result required around 15 values where as the detail page have 50 values.

Using the JsonConvert.SerializeObject() method will make my search result’s JSON large in size, so i decided to use the manual way of generating the JSON via JsonWriter.

However, took me some time to generate a nested object JSON string.

Code   
  1. using (JsonWriter jsonWriter = new JsonTextWriter(sw))
  2. {
  3.     jsonWriter.WriteStartObject();
  4.     jsonWriter.WritePropertyName("model");
  5.     jsonWriter.WriteValue("Impreza");
  6.  
  7.     jsonWriter.WritePropertyName("brand");
  8.     jsonWriter.WriteValue("Subaru");
  9.  
  10.     jsonWriter.WritePropertyName("Engine");
  11.     jsonWriter.WriteStartObject();
  12.     jsonWriter.WritePropertyName("cc");
  13.     jsonWriter.WriteValue("2500");
  14.     jsonWriter.WritePropertyName("type");
  15.     jsonWriter.WriteValue("boxer");
  16.     jsonWriter.WriteEndObject();
  17.  
  18.     jsonWriter.WriteEndObject();
  19. }
Results   
{
    "model": "Impreza",
    "brand": "Subaru",
    "Engine": {
        "cc": "2500",
        "type": "boxer"
    }
}

Reference:
Serializing/Deserializing Dictionary of objects with JSON.NET

Android Project “NoClassDefFoundError” after ADT 17 upgrade

Was in the middle of bug fixing and enhancing 3 Android Projects of mine today, noticed that ADT 17 is out, so decided to upgrade it.

Before the upgrade, my projects seems to be running and working fine.
After the upgrade,  everything seems to crash and the following error is shown at LogCat

04-04 05:10:31.249: E/dalvikvm(230): Could not find method com.google.android.apps.analytics.GoogleAnalyticsTracker.getInstance, referenced from method my.com.website.Menu.onCreate

After google around, found that someone found the solutions, here are the steps I’ve done:

  1. Remove the libraries from the standard Java build path:
    Right click on the project name > Properties > Java Build Path > tab libraries > remove everything except the “Android x.x” (1.6 in my case) and the “Android Dependencies”
  2. Create a folder named “libs” in the project
  3. copy and paste all the external .jar that is required in your project folders via Window Explorer.
  4. Go back to Eclipse and refresh the project and build your project. (I’m developing in Window Environment)
  5. If successful, you’ll be able to see “Android Dependencies” have the list of external .jar files that your project is referencing.
Here are the screen shots of before and after the changes:
Before upgrade to ADT 17
Before upgrade to ADT 17
After upgrade to ADT 17
After upgrade to ADT 17
The original link to the post that help me solve this issue. Link