How to use cronjob to execute periodical MySQL queries?

For my new project, I needed to aggregate user view counts periodically.  After i googling around, i figured using cronjob probably was the most popular way of doing it. So i wrote my sql queries to a file and schedule a cronjob to execute the script every 30 minutes. Here are the detail steps of how i did it:

1. Write sql queries to a text file named recurring-job.sql. Put it under your user’s home directory /home/[user].

update videos v
set number_of_views = (select count(1) from video_views where video_id = v.video_id),
last_update_time = NOW();

2. Edit cronjob by issuing following commands.

crontab -e [for edit] -l [for display]

3. Add a new line into cronjob configuration. This configuration will execute the sql script every 30 minutes.

# m h dom mon dow command
*/30 * * * * /usr/bin/mysql --user=[user_name] --password=[password] [db_name] < /home/[user]/recurring-job.sql

4. Hit ctrl-X and type “Yes” to save and exit.

That’s it! But how do I make sure the job actually run?

grep CRON /var/log/syslog

Happy Coding!

Tagged with: , ,
Posted in Programming

Back up your MySQL database!

Couple days ago, short after I woke up in the morning, I decided to do some  data cleaning work to my live MySQL database. So I wrote a not so complicated SQL statement based on the logic in my head and hit “Execute”. Wait~ I just deleted all the freaking data, around 50k entries, in that table! Those records are the user-generated data for about 6 months period. Thank god I back up my database once a week. So I recovered my database from the snapshot from 3 days ago and only lost a small portion of data.

Here are the lessons I learned:

  1. Don’t run SQL statement against your production database unless you are fully awake :)
  2. Backup your database regularly!

Following are the Unix commands I use to back up and restore my database:

Back up!

mysqldump -u root -p your-database-name | gzip > "/home/username/mysql_backups/your-database-name_`date '+%m-%d-%Y'`.sql.gz"

This command helps you back up your database in compressed format with a nice timestamp in the file name. For example: dbname_11-02-2012.sql.gz

Restore it

gunzip [backupfile.sql.gz]

This command unzips your backup file.

mysql -u root -p your-database-name < [backupfile.sql]

This command restores the database from the unzipped backup file.

Further Readings:

Tagged with: ,
Posted in Programming

Hibernate query optimization

Today I would like to share my experience of Query Optimization. I’ve heard of Query Optimization for a long time, but I’ve never realized how powerful it was until now. Following are some graphs showing how my system performed before and after I optimized my query. Not to mention that it cut my query time by 80%. Isn’t that amazing?

CPU usage before/after query optimization

IO rate before/after query optimization

My web service is using Hibernate in data access layer. At the beginning, I just wrote HQL (Hibernate Query Language) code to retrieve my objects based on business logic. And it worked. So I never thought about looking into the exact SQL query it generated. Until my query became slower and slower while my database grew bigger.

Here are some pointers I learned through the tuning process. (My frameworks: Hibernate + Spring Annotation)

1. Take a look at your actual queries

First make sure you set the show_sql property to true in your Hibernate configuration. Then you will see the Hibernate logs every SQL query in the stderrout.log file. Below is an example:

Hibernate: select photo0_.photo_id as col_0_0_, count(votes1_.vote_id) as col_1_0_, (select count(votes2_.vote_id) from votes votes2_ where photo0_.photo_id=votes2_.photo_id and votes2_.thumbs_up=0) as col_2_0_, (select count(comment3_.comment_id) from comments comment3_ where comment3_.photo_id=photo0_.photo_id) as col_3_0_, photo0_.photo_id as photo1_8_, photo0_.book_id as book2_8_, photo0_.inappropriate as inapprop3_8_, photo0_.thumbnail as thumbnail8_, photo0_.w_id as w5_8_ from photos photo0_ inner join votes votes1_ on photo0_.photo_id=votes1_.photo_id where (photo0_.inappropriate is null or photo0_.inappropriate=0) and votes1_.thumbs_up=1 and votes1_.time_stamp>? group by photo0_.photo_id order by count(votes1_.vote_id) desc limit ?

2. Use explain to analyze your query

Take the query logged by Hibernate and add explain keyword in front of the query. Run it against your database. Then you will get MySQL explains how it will process the query. What type of join? Which index will it use? etc.. Especially pay attention to the extra values from the result. Watch out “Using filesort” and “Using temporary“. Those indicators mean your query consumes higher CPU and memory. At least that was the reason slowed down my query significantly. And It turned out there was other way to achieve the same result without the heavy cost.

Further Reading ..

Tagged with: , ,
Posted in Programming

Monitoring latency in Jetty request log

Are you interested in the response time of your web page/service? As the number of users grows, my web service started to show some performance degradation. But I couldn’t know how slow it was since I didn’t have elapse time measurement. In other words, I need a baseline in order to optimize my server configuration and application. So, I was interested.

The default request log from Jetty is very neat. The default NCSARequestLog logger records pretty much everything that I am interested (client IP, date, method, URL, result, size etc..) except the elapsed time. This is the default request log that it creates: –  -  [25/Jul/2012:23:59:58 +0000] “GET /jetty/tut/XmlConfiguration.html HTTP/1.1″ 200 708394

The good news is that NCSARequestLog has an option to include latency in the log entry. Here is how to do it:

My environment (Ubuntu 10.04 + Jetty 6.1.22)

  1. Open [JETTY HOME]/etc/jetty.xml.
  2. Locate <Ref id=”RequestLog”> section.
  3. Add <Set name=”logLatency”>true</Set> to setter list.
  4. Save it and restart Jetty.
<Ref id="RequestLog">
 <Set name="requestLog">
  <New id="RequestLogImpl" class="org.mortbay.jetty.NCSARequestLog">
   <Set name="filename"><SystemProperty name="jetty.logs" default="./logs"/>/yyyy_mm_dd.request.log</Set>
   <Set name="filenameDateFormat">yyyy_MM_dd</Set>
   <Set name="retainDays">90</Set>
   <Set name="append">true</Set>
   <Set name="extended">false</Set>
   <Set name="logCookies">false</Set>
   <Set name="LogTimeZone">EST</Set>
   <Set name="logLatency">true</Set>

That’s it. Now latency (in millisecond) is logged at the end of the log line. Enjoy :) –  -  [25/Jul/2012:23:59:58 +0000] “GET /jetty/tut/XmlConfiguration.html HTTP/1.1″ 200 708394 888
Tagged with: ,
Posted in Programming

Step-by-step of how to add pinch and double-tap zoom to ImageView

Recently, I added pinch/double-tap zoom feature to one of my projects. I would like to share how I did it in case other people need to do exactly same thing.

There are a couple of solutions out there. But not all of them work for me. After spent some time trying out solutions from StackOverFlow, I found a nice work provided by Alessandro, which works great for me. It supports both pinch and double-tap zoom. Following are the steps I took to integrate his ImageViewTouch class to my app.

1. Check out his code from Github.

2. There are two projects under his repository. ImageViewTouch project is the one that we need. Compile it.

3. After successful compilation. You will get a imageviewtouch.jar file inside the bin folder.

4. Copy the imageviewtouch.jar file to your Android project. You could create a sub-directory named libs under your project and put the jar under it.

5. Right click the jar and choose Build Path > Add to Build Path.

6. Declare ImageViewTouch component in your layout xml. Remember to put the full class name instead of just “ImageViewTouch” in your declaration tag.

android:scaleType="centerCrop" />

7. In your Activity class, do following to assign Bitmap to the ImageViewTouch component.

ImageViewTouch img = (ImageViewTouch) dialog.findViewById(;

8. That’s it. Test it out!


Tagged with: ,
Posted in Android, Programming

New features in .NET 4.0

I know .NET 4.0 has been released more than 1 year already. Finally, today I have some time to pay attention to it. I saw a very nice presentation given by Scott Hanselman about the new features of .NET 4.0. There are tons of new features in both C# language, .NET framework and CLR. I only take some notes about the features that I am interested in right now.

1. WCF Data Service

WCF service is a message base web service. It’s better suit for the transactional operations (ex. Purchase, TranferMoney .. ). In the other hand, WCF Data Service is resource base web service. Which better suit for exposing data. Which is an implementation of oData (Open Data Protocol). OData enables you to request and write data to resources by using Atom and JSON format.

2. Parallel Computing

The .NET Framework 4 introduces a new programming model for writing multithreaded and asynchronous code that greatly simplifies the work of application and library developers. The new model enables developers to write efficient, fine-grained, and scalable parallel code in a natural idiom without having to work directly with threads or the thread pool. The new System.Threading.Tasks namespace and other related types support this new model. Parallel LINQ (PLINQ), which is a parallel implementation of LINQ to Objects, enables similar functionality through declarative syntax.

3. dynamic type

.NET 4.0 introduces a new type, dynamic. The type is a static type, but an object of type dynamic bypasses static type checking at the compile time. Microsoft introduced a Dynamic Language Runtime (DLR) which is a runtime environment that adds a set of services on top of Common Language Runtime (CLR) to support this dynamic type.

More Resources:

Tagged with:
Posted in Programming

How to start a new Thread?


First, we need a class that implement Runnable interface.

public class FetchStockPrices implements Runnable{
    public void run() {
    // Long running process goes in here!!
    // Retrieve stock price information from remote web service

Next, we instantiate the runnable class and pass it into the Thread constructor.

FetchStockPrices job = new FetchStockPrices();
Thread worker = new Thread(job);

Finally, we call the start() method of the Thread object. This will spawn a new thread from the executing thread and execute the target run() method defined in the runnable class.


Now, let’s take a look at the C# version. They are really similar.


First, we create a class that contain the long running method DoWork().

public class FetchStockPrices{
    public void DoWork() {
    // Long running process goes in here!!
    // Retrieve stock price information from remote web service

Next, create the thread object, passing in the FetchSrockPrices.DoWork method via a ThreadStart delegate.

FetchStockPrices job = new FetchStockPrices();
Thread worker = new Thread(new ThreadStart(job.DoWork));

Finally, start the thread.

Tagged with: , ,
Posted in Programming

Get every new post delivered to your Inbox.