I‘ll be giving a webinar version of a well-received presentation from last year‘s Users Conference tomorrow (November 5th) at 10:00 AM Pacific that covers how to configure MySQL Connector/J to deliver the best performance for your Java application running on MySQL. The session is interactive, and we‘ll be answering questions, so bring those along!

Come spend your morning/afternoon break with me and learn a few new tricks for making your applications fly. The information to sign up is at https://www.mysql.com/news-and-events/web-seminars/display-460.html. Hope to see you there!

MySQL Cluster Performance Tuning Best Practices

Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?

Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster.

We will discuss guidelines and best practices covering the following areas:

• General Design Concepts and Guidelines
• Schema Optimization
  • BLOB/Text vs VARBINARY/VARCHAR
  • Partition by Key
• Index Selection and Tuning
…

There are a few parameters that may be worth looking at when first installing InfiniDB; NumBlocksPct for managing memory, PmMaxMemorySmallSide for optimizing join behaviors, and MaxOutstandingRequests which changes how individual queries share resources.

The NumBlocksPct parameter manages the amount of memory on each server allocated to store data blocks and is expressed as a % of server memory. For example, to allocate 5 GB for the data buffer cache for a server with 8GB memory, set thRead More...

There are a few parameters that may be worth looking at when first installing InfiniDB; NumBlocksPct for managing memory, PmMaxMemorySmallSide for optimizing join behaviors, and MaxOutstandingRequests which changes how individual queries share resources.

The NumBlocksPct parameter manages the amount of memory on each server allocated to store data blocks and is expressed as a % of server memory. For example, to allocate 5 GB for the data buffer cache for a server with 8GB memory, set thRead More...

In my last post, I mentioned the factors that affect web performance. Now that we know what we need to measure, we come to the harder problem of figuring out how to measure each of them. There are different methods depending on how much control you have over the system and the environment it runs in. Additionally, measuring performance in a test setup may not show you what real users experience, however it does give you a good baseline to compare subsequent tests against.
Web, application and database serversBack end servers are the easiest to measure because we generally have full control over the system and the environment it runs in. The set up is also largely the same in a test and production environment, and by replaying HTTP logs, it's possible to simulate real user interactions with the server.

Some of the tools one can use to …

This presentation was be done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.

It also went through how to use SHOW ENGINE INNODB STATUS to tune several InnoDB variables:

innodb_adaptive_hash_index
innodb_commit_concurrency
innodb_concurrency_tickets
innodb_file_io_threads
innodb_log_buffer_size
innodb_max_purge_lag
innodb_sync_spin_loops
innodb_thread_concurrency
innodb_thread_sleep_delay

The slides can be downloaded from:

http://technocation.org/files/doc/ShowEngineInnoDBStatus.pdf

(Note that the slides open up to the middle section, which …

While going about my day, I stumbled upon John Allspaw’s tweet about his experience with MySQL 5.0.51 versus Percona’s 5.0.83 highperf Percona build. For those that don’t remember John from the MySQL Conference & Expo’s, he’s the guy managing operations at Flickr, and he recently even wrote a book about webops.

Click on the photo above, to see when the upgrade happened. Amazed?

Haven’t tested this out myself, though I’m curious to see how this stacks up against MySQL 5.4, which is also aimed at being a high performance release. In another note, it seems like the mysql-cacti-templates could use a bit more verbose …

This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.

When I looked at the log on disk, I saw it was a symlink to /dev/null. They were running MySQL 5.0, so I immediately realized that it was a hack to have a general log they could “turn on” without having to restart mysqld.

On a different server, I saw the same link to /dev/null done with a slow query log.

The drawbacks to doing it this way is that MySQL still has the overhead of writing to the log file. The I/O overhead is greatly reduced because the writes are to /dev/null, but there’s still overhead from other resources such as RAM, CPU, etc.

…

In my old post there is a bug when run in MySQL 5.1.30 and old, because the status variable Queries was added in MySQL 5.1.31. So i change to choose between Queries and Questions status variables, and I think the Queries represent more accurate result.

http://forge.mysql.com/tools/tool.php?id=217

By the way:

# Queries The number of statements executed by the server. This variable includes

A couple of days ago I posted about scaling writes in mysql. I didn't say much about read performance in that post because a) it was irrelevant at the time, and b) there are thousands of articles all over the web that already cover read performance.

In this post I'm going to cover some of the things that I did to improve read performance for my own application. It may be relevant to others, however, you'd still need to read a whole bunch of other articles to understand MySQL read performance.

Looking at our access patterns, it turned out that there were two classes of read queries.

1. Reads to build the daily summaries
2. Reads from the summary tables in response to user actions

The former dealt with far more data at one go, but was only run once. Queries for this pattern were slow …