Brian Miezejewski will be presenting at the North Texas MySQL Users Group on June 1st on performance tuning on your system. Brian is a top-level tuning guru and this is your chance to get an expert to examine your system.

So on your system, run the following:

mysqladmin -u -p va >varis.txt


Then during your peak usage time (if possible):

mysqladmin -u -p ex -i 15 -r >stats.txt


Let it run for 10 minutes before hitting ctrl-c to kill it. Bring in the varis.txt and the stats.txt files for tuning and review.


Meeting: June 1st
7:00 PM
Sun Offices
Suite 700
16000 Dallas Tollway
Dallas


NorthTexasMySQL.org

Yesterday, The Pythian Group issued a press release about my book, Pythian’s partnership with Sun, and our new “MySQL Adoption Accelerator Package”. I am not a marketing guru, but I can tell you what we the package means in terms of new work that the MySQL teams have been doing.

Basically, the MySQL Adoption Accelerator Package combines customized training with a comprehensive audit of systems. The name “Adoption Accelerator” makes it sound like it’s only for new applications that are almost ready to go live. What the program actually does is have us evaluate your systems, and intensively train you in the areas you want and need. The program is designed to suit all your needs, whether it’s teaching you about one topic (say, query optimization) or an entire range of topics, from Architecture to ZFS (special issues with running MySQL on ZFS, that is, but that did not fit a cute …

I saw something interesting today when helping out someone on the #mysql IRC channel. It was a cnf file that was designed to destroy a server. Before I get into the why-not, here are the goods:

...snip...
read_buffer = 128M
join_buffer = 128M
key_buffer = 512M
max_allowed_packet = 200M
thread_stack = 192K
thread_concurrency = 8
thread_cache_size = 64
query_cache_limit = 256M
query_cache_size = 256M
table_cache = 8192
query_cache_type = 1
sort_buffer = 128M
record_buffer = 128M
myisam_sort_buffer_size = 128M
thread_cache = 64
max_user_connections = 500
wait_timeout = 200
max_connections = 4096
tmp_table_size = 1000M
max_heap_table_size = 1000M
...snip...


Now, you may ask why these settings are bad. I will tell you. First, an equation for calculating per-thread …

This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.

What can you find on the web?

A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material—the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.

So, what is swappiness?

Towards the end of the email thread quoted in the article, you’ll find this definition (sort of):

> I’ve read the source for where …

Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage. Server Variables For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data,

When I wrote my previous post titled all about storedconfigs, I was pretty confident I explained everything I could about storedconfigs… I was wrong of course

A couple of days ago, I was helping some USG admins who were facing an interesting issue. Interesting for me, but I don’t think they’d share my views on this, as their servers were melting down under the database load.

But first let me explain the issue.

The issue

The thing is that when a client checks in to get its configuration, the puppetmaster compiles its configuration to a digestible format and returns it. This operation is the process of transforming the AST built by parsing the …

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:

• Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
• More efficient memory allocation: ability to use more scalable platform memory allocator
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements mean for performance and scalability?

In brief, we’ve tested …

I vaguely recall a couple of blog posts recently asking something like "what's the formula to compute mysqld's worst-case maximum memory usage?" Various formulas are in wide use, but none of them is fully correct. Here's why: you can't write an equation for it.

The most popular equation, of course, is the one in the error log after a crash: "It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections". This was never true -- not even when ISAM and friends were the only storage engines, which was before most of us remember. And at a minimum, most servers these days need to consider the added memory for InnoDB: specifically, the buffer pool. (There's also an innodb_additional_mem_pool_size but it's typically set pretty small, like 20M).

But even that's not enough. Staying with InnoDB, we also need to count the data dictionary. This can take many gigabytes on servers with a lot …

These days I'm working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven't worked much with stored procedures, I though it's going to be a piece of cake. In the end - it was, but there's a catch.

My initial idea was - I'll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn't really all the way I expected..

For a showcase I have created a function "whatstheweatherlike". Let's call it and see what shows up in the slow query log:

PLAIN TEXT SQL:

1. mysql> SELECT whatstheweatherlike(5);
2. +----------------------------------------------------------+
3. | …

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as you would reach much sooner with focused approach.

The time is critical for many performance optimization tasks not only because of labor associated expenses but also because of the suffering - slow web site means your marketing budget is wasted, customer not completing purchases, users are leaving to competitors, all of this making the time truly critical matter.

So what can be the goal ? Generally I see there are 2 types of goals seen in practice. One is capacity goal this is when the system is generally overloaded so everything is slow, when you're just looking to see how you can get most out of your existing …