Back up your MySQL database!

Posted on December 8, 2012 by Darren — Leave a comment ↓

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:

  1. http://webcheatsheet.com/sql/mysql_backup_restore.php
Tagged with: ,
Posted in Uncategorized

Hibernate query optimization

Posted on September 2, 2012 by Darren — Leave a comment ↓

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 ..

  1. http://dev.mysql.com/doc/refman/5.0/en/explain-output.html
Tagged with: , ,
Posted in Programming

Monitoring latency in Jetty request log

Posted on August 11, 2012 by Darren — Leave a comment ↓

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:

203.203.32.252 –  -  [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>
  </New>
 </Set>
</Ref>

That’s it. Now latency (in millisecond) is logged at the end of the log line. Enjoy :)

203.203.32.252 –  -  [25/Jul/2012:23:59:58 +0000] “GET /jetty/tut/XmlConfiguration.html HTTP/1.1″ 200 708394 888
Tagged with: ,
Posted in Programming

New “Search” feature in WretCha(瘋無名)!

Posted on August 5, 2012 by Darren — Leave a comment ↓

Based on a user review, I’ve been thinking about how to enable users to see more albums. How about adding “Search” functionality? In the next release v.2.0.5, WretCha will have “Search”. Here is the use case. One day, suddenly you would like to see Joanna’s (Assume she is a hot celebrity -_-) photos. Now you can search for it on WretCha. Sounds good doesn’t it?

1. Click the magnify glass on the upper right corner.

2. Select the “Search” radio button and type in “Joanna”.

3. BAM! All the albums related to “Joanna” displayed.

4. Pick one you interested (Based on the thumbnail and title) from the list. Then enjoy it!

Posted in Android, WretCha

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

Posted on July 14, 2012 by Darren — Leave a comment ↓

Recently, I added pinch/double-tap zoom feature to WretCha. 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. https://github.com/sephiroth74/ImageViewZoom.git

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.

<it.sephiroth.android.library.imagezoom.<span class="hiddenSpellError">ImageViewTouch
android:id="@+id/ImageView01"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:layout_weight="1"
android:scaleType="centerCrop" />


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

ImageViewTouch img = (ImageViewTouch) dialog.findViewById(R.id.ImageView01);
img.setFitToScreen(true);
img.setImageBitmap(fullSizeBitmap);

8. That’s it. Test it out!

Reference:

Tagged with: ,
Posted in Android, Programming

Like WretCha (瘋無名) on Facebook!

Posted on July 10, 2012 by Darren — Leave a comment ↓

WretCha (瘋無名) is on Facebook now!

Please “Like” us to help more people discover this awesome Android App. The bigger WretCha community is, the more fun the users will get! You can click the “Like” button on the sidebar, or visit us on http://www.facebook.com/WretCha

Here are the reasons why you should like us:

  1. WretCha is the only cloud based Wretch Viewer in Play Store.
  2. WretCha saves you lots of time finding hot-pictures.
  3. WretCha let you sharing your thoughts with fellow WretCha community.
  4. WretCha is FREE! Just download it from Play Store and enjoy it at NO COST!

Thanks again for you time and support. With your help I believe WretCha community can grow bigger and stronger.

Tagged with: , ,
Posted in WretCha

Here’s a toast to 1000 downloads of WretCha

Posted on July 1, 2012 by Darren — Leave a comment ↓

Today is exactly one month after WretCha (瘋無名) was released to Play Store. It just passed the 1,000+ downloads mark recently. Yay! The download rate was not very high but pretty steady.

1000 downloads

The past month I had a lot of fun developing WretCha. I pushed out total 20 releases. Some of them are feature releases such as able to download photo and leave comments. Some are enhancements related to stability and performance. Then the rest are bug fixes. The number of release time seems a little bit high. I think some of my users are probably annoyed by that. My apologies. I just can’t wait to push the better version to my users.  :)

Now, here is a toast to WretCha and all its users. I will keep improving it. Hopefully, more and more people will find it useful and fun. Cheers.

Tagged with: , , ,
Posted in WretCha
Follow

Get every new post delivered to your Inbox.