Improve Performance of Grails Queries in MySQL

A web application that I was developing would become extremely slow as the number of requests increased per minute. I couldn't figure out why because with my local database everything seemed fine. Here is the process that I used in MySQL to determine the bottleneck in my database. The final problem was a missing index on a table that was used in the where clause of a select statement over and over again. I included a snapshot the MySQL Process list at the end of the post. Hope this helps someone.

Steps to Follow:

  • show full processlist;
    • shows each database connection that has been allocated for your database
    • contains the time a connection has been Sleep
    • contains how long a Query is taking to execute, you want this to be less than a second ideally
  • describe <query>;
    • this gives you the database's plan for executing the query
    • you want the number of rows that will be looked at to be as small as possible
  • show index from <table>;
    • this will list all indices contained on a table
    • this is a quick way to discover if you are missing a key index that could improve query performance
  • create index <tablename_fieldname_idx> on <table>(field);
    • this will add a new index to a table

Mocking Hibernate Criteria in Grails App

Testing is always a sore spot in many applications, and Grails is no exception, but mocking objects and methods is actually not as bad as you think. Here is a simple way to mock the Hibernate Criteria object that is available from Grails domain classes.

Example:
If this is the code that you want to test, and for this particular test you want to have no results, this how you would mock it out.

Code:


Test Code:


The key piece of code is the creation of the bookCriteria object, in this case it is a member of the test class that is why it is not declared locally in the function. However, you will notice that the bookCriteria is simply a HashMap that returns a empty list for the 'list' parameter. This way when testing 'c.list' a empty list will be returned. Simple hunh!

top Command Line Tool

Recently I have been introduced to the top command line tool. This tool is very useful in viewing the top processes running on a machine and their CPU and Memory usage. I have included a screen shot of the top command's output.

Some useful options are the i option which allows you to toggle back and forth between viewing and not viewing idle processes.

And the M option which allows you to sort the processes by memory usage.