Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.

You can download the first release of the benchmarking script here: http://code.google.com/p/dbbenchmark/

Please read the README file or consult the Support page before running the benchmarks.

ODTUG invited me to give a webinar and I said yes, so this Thursday you’re invited to join me as I talk about MySQL performance. We’ve come a very long way towards a MySQL that can perform well on modern hardware, and there really isn’t broad recognition of this. A lot of the best work has gone into the InnoDB “plugin” storage engine, which was announced after my co-authors and I sent High Performance MySQL to the press. I will explain what you should be doing differently now than you did two years ago, and suggest a performance-in-a-nutshell configuration baseline for MySQL that’s quite different from what I’d have said in 2008. You can register for free through GoToWebinar. See you there.

Related posts:

1. …

I’ll be joining the NovaRUG (Northern Virginia Ruby Users’ Group) on Thursday to talk about MySQL performance. See their blog for the details and how to RSVP.

Related posts:

1. Free webinar on MySQL performance this Thursday
2. Speaking at MySQL Meetup in Northern Virginia
3. Speaking at EdUI Conference 2009
4. …

The MySQL query cache can be very useful in environments where data is not modified often, and traffic consists of mostly reads. It can improve performance by quite a bit if used correctly, but can actually degrade performance if configuration, queries, and traffic patterns are not optimized for it.

Let me quickly go over what the query cache is, and what it is not. The query cache does not cache the query execution plan, the full page on disk (which is what the InnoDB buffer pool is used for), DDL statements, or any queries that modify data (INSERT/UPDATE/etc). The query cache *does* cache the full result set of “cacheable” SELECT queries. For a query to be “cacheable”, it must have the following properties:

• It must be deterministic. The query must return the same result set each time that it is run. This means that it may not contain any non-deterministic variables, such as …

As described in the prior post, the shared-disk performance dilemma is simple:

1. If each node stores/processes data in memory, versus disk, it is much faster.
2. Each node must expose the most recent data to the other nodes, so those other nodes are not using old data.

In other words, #1 above says flush data to disk VERY INFREQUENTLY for better performance, while #2 says flush everything to disk IMMEDIATELY for data consistency.

Oracle recognized this dilemma when they built Oracle Parallel Server (OPS), the precursor to Oracle Real Application Cluster (RAC). In order to address the problem, Oracle developed Cache Fusion.

Cache fusion is a peer-based shared cache. Each node works with a certain set of data in its local cache, until another node needs that data. When one node …

The Alternative PHP Cache (APC) is a free and open opcode cache for PHP. Its goal is to provide a free, open, and robust framework for caching and optimizing PHP intermediate code. So what does this mean for Drupal?

An opcode cache is used to keep compiled PHP code in memory. In a nutshell, when there's a request for a Drupal node, there are all kinds of PHP code that need to  be compiled before PHP can execute that code to query the database, create the webpage, and send it to the browser. The more complex your Drupal site, IE the more modules you have installed, the more PHP code that needs to be compiled. This compilation process occurs on every request to your Drupal site. By installing and enabling an opcode cache, your server will compile all the PHP code once, then store it in memory for later use. When a request happens again for the same PHP code, the opcode …

The Alternative PHP Cache (APC) is a free and open opcode cache for PHP. Its goal is to provide a free, open, and robust framework for caching and optimizing PHP intermediate code. So what does this mean for Drupal?

An opcode cache is used to keep compiled PHP code in memory. In a nutshell, when there's a request for a Drupal node, there are all kinds of PHP code that need to  be compiled before PHP can execute that code to query the database, create the webpage, and send it to the browser. The more complex your Drupal site, IE the more modules you have installed, the more PHP code that needs to be compiled. This compilation process occurs on every request to your Drupal site. By installing and enabling an opcode cache, your server will compile all the PHP code once, then store it in memory for later use. When a request happens again for the same PHP code, the opcode …

I will be joining a great list of quality speakers including John Allspaw, Theo Schlossnagle, Rasmus Lerdorf and Tom Cook at Surge 2010 in Baltimore, Maryland on Thu 30 Sep, and Fri Oct 1st 2010.

My presentation on “The most common MySQL scalability mistakes, and how to avoid them.” will include discussing various experiences observed in the field as a MySQL Consultant and MySQL Performance Tuning expert.

Abstract:

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL …

For decades the debate between shared-disk and shared-nothing databases has raged. The shared-disk camp points to the laundry list of functional benefits such as improved data consistency, high-availability, scalability and elimination of partitioning/replication/promotion. The shared-nothing camp shoots back with superior performance and reduced costs. Both sides have a point.

First, let’s look at the performance issue. RAM (average access time of 200 nanoseconds) is considerably faster than disk (average access time of 12,000,000 nanoseconds). Let me put this 200:12,000,000 ratio into perspective. A task that takes a single minute in RAM would take 41 days in disk. So why do I bring this up?

Shared-Nothing: Since the shared-nothing database has sole ownership of its data—it doesn’t share the data with other nodes—it can operate in the machine’s local RAM, only writing infrequently to disk (flushing the data …