Gold Apple Software Blog

Tuning the MySQL Query Cache - Part 2

Submitted by Geoff on Wed, 2011-10-19 21:19

It's been quite some time since I wrote my original post on optimizing the query cache, which set out a plan for testing improvements. Unfortunately I can't recall the dates of all the changes I made, and the server workload hasn't been consistent, but I think some trends can still be recognized.

The big question I had initially was if whether changing the value of query_cache_min_res_unit could have a significant effect on the query cache's memory utilization, and correspondingly reduce a high prune rate. The default value of 4K was almost twice the average size of a stored result, so I planned to reduce the setting in 1K increments and observer any effects.

Storing NULL values to database in CakePHP

Submitted by Geoff on Tue, 2011-09-06 11:02

After searching for a while, I was unable to find information on how to make sure CakePHP will store a NULL value to the database. If you unset the value from the data array sent to saveAll(), it will not be changed from its current value: this will suffice for saving new rows to the database if their default value is NULL, but it will not overwrite an existing value with NULL.

The solution is simple, just not described well: set the value of the data array item to null.

The following code snippet is from an instance where a user clears the value from a text field in order to remove the data. If unchanged an empty string would be stored instead of the desired NULL value (and would later cause errors due to a UNIQUE key constraint).

A Simple Issue Tracker With Drupal

Submitted by Geoff on Tue, 2011-07-26 18:27

As I started working on more projects simultaneously, I found it a necessity to find a better way to track tasks. My best previous system was a wall full of colour-coded sticky notes, but I had difficulty translating it from keeping track of one organization's tasks to keeping track of all my freelance work. I felt this site was the best place for me to keep track of things, with the potential to allow better interactivity with clients later, and so wanted to integrate something with Drupal.

Drupal.org's own project module (with Project issue tracking) is available, but as the description states, "Projects are generally assumed to represent software that has source code, releases, and so on." While the projects I work on generally have source code, they are not nicely packaged releases, and I can't use the issue tracking without the projects. There are other Drupal modules available to support issue tracking, but I found they were all pretty complex and customized. I figured Views and CCK would provide most of the features I would need on top of core Drupal, and so set out to construct a simple issue tracker.

Reducing Drupal's On-Disk Temporary Table Usage

Submitted by Geoff on Sat, 2011-05-14 13:39

MySQL frequently creates temporary tables to store intermediary query data, such as when sorting a result set. These tables are stored in memory until they grow past the tmp_table_size or max_heap_table_size configuration variables, unless they contain a BLOB or TEXT column (among some other factors), in which case they will be placed directly on disk. This can have a significant performance impact due to the extra IO operations, which are particularly important to avoid in a virtualized environment if possible.

Pushing to multiple repositories with EGit

Submitted by Geoff on Fri, 2011-05-13 11:37

One handy feature that I recently discovered in newer versions of EGit is the ability to push to multiple remote repositories at once.

Previously for a Drupal module I would set up two remotes, origin (pointing to the Drupal.org sandbox repository) and github. To push local commits to both then always required going through the push dialog twice; selecting the remote to push to, confirming the branches to push, and then finally initiating the transfer.

Now, by specifying multiple push targets in your origin configuration, you can now simply use Push to Upstream, either from the repository viewer or the project's team menu, and have your commits sent to both remote repositories simultaneously without requiring any extra dialogs. Once complete, the status dialog will now include a column to dispay the result of pushing to each repository.

Shortcut for cloning Drupal Git Repositories

Submitted by Geoff on Mon, 2011-03-14 12:02

Taken from a conversation on Groups.Drupal.org where several people shared some of their shortcuts for using git. The following Bash function will clone a git repository from git.Drupal.org, and optionally set up and switch to a remote tracking branch of the specified version.

Drop multiple MySQL tables

Submitted by Geoff on Thu, 2011-02-03 13:17

Since MySQL's DROP TABLE statement requires specifying each table you wish to drop, dropping many tables at once can be a pain; especially if you have InnoDB tables with foreign key constraints. With the following stored procedure, tweaked from some of the helpful comments on the MySQL documentation, you can drop any tables matching a particular regular expression pattern.

Organizing Eclipse Projects with Working Sets

Submitted by Geoff on Tue, 2010-11-09 16:11

My first use of Eclipse was in university, where to keep organized I would separate each course into its own workspace. This was bred out of the fact the each course could typically have several labs and a term project, each with starter code from the professor in its own pre-made Eclipse project. It was simply easier to have separate workspaces, each with their own "Lab 1" project than to sort through every project in a single workspace. This presented its own challenges, however, as each workspace would have to be configured correctly and on occasion it would be necessary to review code between courses

Eclipse has remained my prefered IDE for PHP development. A similar organizational issue has arisen as each client has their own project(s), and particularly with Drupal many contributed modules are involved as well. It's simply not feasible to have separate workspaces for each client, especially when they utilize so much common code. This is also compounded if you are working with multiple versions of Drupal or contributed modules through CVS (or learning about Git).

Using Eclipse and EGit for Drupal Development

Submitted by Geoff on Sat, 2010-10-30 12:35

Drupal will be migrating from CVS to Git (hopefully) soon, so there are benefits to introducing Git to your current workflow. While CVS remains the primary code repository for Drupal projects, a Git mirror has been set up for some time and can be used now. EGit is a team plugin for Eclipse that enables you to manage Git repositories from within Eclipse, utilizing a Java port of Git so that it is entirely self-contained. Though it is under active development, it looks to have made some big strides in a short period and is likely suitable for most users.

Optimizing MySQL Query Cache for Drupal

Submitted by Geoff on Thu, 2010-07-22 23:14

In running a VPS with a few Drupal sites, I ran into the baffling instance of having an underutilized query cache, but a relatively high prune to insert ratio. Initially set up on a 360MiB Linode instance, the query cache was at a size of 20MiB with approximately 4MiB (20%) free. After Linode improved their RAM allocations and the server had 512MiB of RAM available, I correspondingly increased the query cache to 32MiB but a larger portion remained unutilized with 10MiB (30%) now free. I don't have historical numbers on the number of prunes, but the value seemed to remain similarly high.