Archive for March, 2007

What do you mean my table is full?!?

Friday, March 23rd, 2007

At my last job I mainly did database work and very little coding. This was unusual since I’m primarily a code monkey, but I was supposed to be starting with a 2-month project adding some features to a reporting application and unfortunately it turned into the job from hell. I lasted 4 months – the job market in Boston is way too hot right now to put up with a crap job like that.

The database had worked fine for about a year with limited data, but around the time I started we added a very large data feed and brought the system to its knees in about 3 weeks (reports that had finished in under an hour now took 1-2 days). In retrospect I think the simple fix would have been to aggressively normalize the database – that would have made adding indexes and other standard performance fixes more applicable – but we were so busy fighting fires we didn’t really have time to fix things properly. Classic catch-22.

I’d only been there about a month and a half when a data load failed with the error “the table is full”. Ugh. I started poking around and the table in question was only 4GB. I know MySQL can handle huge databases – so WTF? I found this excellent writeup by Jeremy Zawodny.

We were running version 4.1, and hadn’t done much at all to the configuration; it was pretty much running a default install, using MyISAM tables. So the fix was pretty simple:

alter table foo max_rows=1000000000;

Jeremy was certainly right – it took a long time (on a dual Xeon PowerEdge with 8GB ram):

Query OK, 12770057 rows affected (7 hours 31 min 23.87 sec)
Records: 12770057 Duplicates: 0 Warnings: 0

Looking occasionally at “show processlist;” and the final file times

Dec 11 15:26 foo.frm
Dec 11 18:19 foo.MYD
Dec 11 22:57 foo.MYI

it took about 3 hours to copy the data into the new temporary table but 4 1/2 hours to build the indexes (“Repair with keycache”).

So that fixed that table, but we were running several identical databases, one for each data feed, so all tables in all databases that might fill up had to be fixed, and that took another few days. Good times.

Using Generics to Simplify Hibernate/Spring DAOs (update #2 for JPA)

Thursday, March 22nd, 2007

I’ve been working with JPA and Hibernate Annotations and needed to update my base DAO interface and implementation class from its native Hibernate implementation.

The interface is a little different from the one I’d been using so I’m including the new one:

package com.foo.bar.dao;

import java.io.Serializable;
import java.util.Collection;

import com.foo.bar.model.DatabaseObject;

/**
 * Base DAO.
 * @author Burt
 * @param <T> the entity type
 */
public interface BaseDAO<T extends DatabaseObject> {

  /**
   * Get the total number of instances.
   * @return  the count
   */
  long getTotalCount();

  /**
   * Find all.
   * @return  all instances
   */
  Collection<T> findAll();

  /**
   * Find an instance by primary key.
   * @param pk  the primary key
   * @return  the instance
   */
  T findByPrimaryKey(Serializable pk);

  /**
   * Insert a new instance.
   * @param t  the new instance
   * @return  the instance updated with its primary key
   */
  T create(T t);

  /**
   * Save changes to an existing instance.
   * @param t  the instance
   * @return  the updated & refreshed instance
   */
  T update(T t);

  /**
   * Delete an existing instance.
   * @param t  the instance
   */
  void delete(T t);
}

and the new base implementation class:

package com.foo.bar.dao.jpa;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.Collection;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.foo.bar.annotation.DependencyInjection;
import com.foo.bar.dao.BaseDAO;
import com.foo.bar.model.DatabaseObject;

/**
 * Abstract base class for Hibernate DAOs.
 *
 * @author Burt
 * @param <T> the entity type
 */
@Transactional
@Repository
public abstract class BaseDaoImpl<T extends DatabaseObject>
       implements BaseDAO<T> {

  protected final Logger _log = Logger.getLogger(getClass());

  private Class _entityClass;

  private EntityManager _entityManager;

  /**
   * Inject the entity manager.
   * @param entityManager
   */
  @PersistenceContext
  @DependencyInjection
  public void setEntityManager(final EntityManager entityManager) {
    _entityManager = entityManager;
  }
  protected EntityManager getEntityManager() {
    return _entityManager;
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#getTotalCount()
   */
  public long getTotalCount() {
    return (Long)getEntityManager().createQuery(
      "SELECT COUNT(*) FROM " + getEntityClass().getSimpleName())
      .getSingleResult();
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#findAll()
   */
  @SuppressWarnings("unchecked")
  public Collection<T> findAll() {
    return getEntityManager().createQuery(
        "FROM " + getEntityClass().getName())
        .getResultList();
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#findByPrimaryKey(java.io.Serializable)
   */
  @SuppressWarnings("unchecked")
  public T findByPrimaryKey(final Serializable pk) {
    return (T)getEntityManager().find(getEntityClass(), pk);
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#create(com.foo.bar.model.DatabaseObject)
   */
  public T create(final T t) {
    getEntityManager().persist(t);
    return t;
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#update(com.foo.bar.model.DatabaseObject)
   */
  public T update(final T t) {
    t.setModified(new Date());
    getEntityManager().merge(t);
    return findByPrimaryKey(t.getPk());
  }

  /** (non-Javadoc)
   * @see com.foo.bar.dao.BaseDAO#delete(com.foo.bar.model.DatabaseObject)
   */
  public void delete(final T t) {
    getEntityManager().remove(t);
  }

  protected synchronized Class getEntityClass() {

    if (_entityClass == null) {
      Type type = getClass().getGenericSuperclass();
      loop:
      while (true) {
        if (type instanceof ParameterizedType) {
          Type[] arguments =1) {
              _entityClass = (Class)argument;
              break loop;
            }
          }
        }
        type = ((Class)type).getGenericSuperclass();
        if (type == Object.class) {
          throw new RuntimeException(
              "Could not find a DatabaseObject subclass parameterized type");
        }
      }
    }
    return _entityClass;
  }
}
  1. ParameterizedType)type).getActualTypeArguments(); for (Type argument : arguments) { if (argument instanceof Class && DatabaseObject.class.isAssignableFrom(((Class)argument[back]

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.