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.

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.


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.