There have been a few posts by Sheeri and Baron today on the MySQL sort_buffer_size variable. I wanted to add some more information about this buffer, what is impacted when it is changed, and what to do about it?

The first thing you need to know is the sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread. I’ve seen clients that set this assuming it’s a global buffer Don’t Assume – Per Session Buffers.

Second, internally in the OS usage independently of MySQL, there is a threshold > 256K. From Monty Taylor “if buffer is set to over 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but …

I wrote about FlashCache there, and since that I run couple benchmarks, to see what performance benefits we can expect.
For initial tries I took sysbench oltp tests ( read-only and read-write) and case when data fully fits into L2 cache.

I made binaries for FlashCache for CentOS 5.4, kernel 2.6.18-164.15, you can download it from our testing stage. It took some efforts to make binary, you may get my instructions for CentOS on FlashCache-dev mail-list, most likely it will not work for different CentOS / Kernel.

The full results, scripts and settings are on …

In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”

Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.

This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral part against the “Battle against any guess.” Baron’s recommendation to leave …

In The Doom of Multiple Storage Engines, Peter talks about how the storage engine concept of MySQL is usually spoken of in positive terms, but there are many negatives.

I have a hard time trying to figure out the deeper meaning behind Peter’s post, given that Percona writes a storage engine for MySQL, XtraDB. Does this mean that Percona will stop developing XtraDB? Does this mean that the Percona Server will diverge farther and farther away from MySQL so that they’re not compatible any more and migrating from MySQL to Percona Server is very difficult?

Or maybe it’s just that Peter is saying one thing and doing the opposite; which just seems wrong because that would be blatant hypocrisy on Percona’s part.

(This idea was a comment on the blog post but seems to be trapped in the spam filter, so I’m …

I ran into an interesting bug/fact today while messing around with MySQL 5.5. It seems that in the DDL, you can’t say “Type=InnoDB|MyISAM|Foo” anymore. You have to say “Engine=InnoDB”.

This will break your propel:build-all , or propel:build-all-load .. or if you manually try to execute the sql from data/sql/*. You’ll get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Type=InnoDB’ at line 11

This issue is resolved in Propel 1.5.0, but I’m not sure when Symfony will include that in the Symfony 1.4 series.

To fix this issue, all you need to do is edit one file:

…

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution.

John Hawkins is now the second person with a real-life WordPress tattoo. You can see it finished here, and two in-progress shots. John used the variation of the logo they did for WordCamp Las Vegas which he also organizes. Perhaps we should create a new category on Code Poet for 9seeds.

This is part three of an ongoing series about my experiences while writing the MySQL Admin Cookbook for Packt Publishing. All previous parts can be found under the mysql-admin-cookbook label.

Even though I said I would be presenting things in mostly chronological order, I think after the previous - rather dry - part, a little more technical and fun information would be nice for a change: The tools used to create the MySQL Admin Cookbook (well, at least those used by Udo and me). To give a detailed account of what software products we used during the whole experience I will split this topic up into multiple posts. Otherwise it would just become either way too …

In The Doom Of Multiple Storage Engines, PeterZ lists a number of unique technological challenges that MySQL has to face precisely because it chooses Storage Engines and the way it implements them.

There is another, nontechnical challenge that needs solving by vendors that are producing commercial closed source storage engines for MySQL - my experience is with one specific vendor, but the situation is basically the same for each and every closed source SE:

Where I work, we are a MySQL support customer. Actually, we are a very satisfied MySQL support customer, because in our opinion the MySQL support just plain rocks and has more than once saved our corporate asses, or at least greatly improved our MySQL experience.

If we were to load a closed source storage engine into our MySQL binaries, we would make life …

I perpetually see something like the following:

My server load is high and my queries are slow and my server crashes. Can you help me tune my server? Here is some information.

[random sample of SHOW GLOBAL STATUS, like the query cache counters]

my.cnf:

[mysqld]
key_buffer_size=1500M
query_cache_size= 64M
max_connections = 256
key_buffer = 8M
sort_buffer_size = 100M
read_buffer_size = 8M
delay_key_write = ALL

There are many problems in this my.cnf file, but the sort_buffer_size is a glaring one that identifies the user as someone who should not be playing with live ammunition. Therefore, I have developed an advanced process for tuning sort_buffer_size, which you can follow to get amazing performance improvements. It’s magical.

1. How expert are you?
   • I know that there is a sort buffer, and that it is related to sort_merge_passes. When …