Ok, I am perplexed… i don’t say that often.   I have the privilege of testing out a couple of Dolphin Interconnects with Waffle Grid.   They are proving to substantially improve our transactions throughput, I mean we are getting 3x performance over 1gbe…. but what is perplexing is each run going over the faster interconnects results in 1/3 of the memcached sets/gets that occur when testing over 1gbe!  Same datasets, same tests, repeatable results.  See here:

1gbe:
         cmd_get: 771811
cmd_set: 784119

Dolphin:

         cmd_get: 239423
cmd_set: 271259

So instead of testing out the interconnect performance, I am really seeing better results from a higher cache hit rate.  Less items are hitting the LRU.   So what could it …

I thought I would write a few blog posts on what I am calling the 5 minute DBA. The content of these is  interesting or frequently asked questions that I get when out on a gig, you know those ones were a developer or a dba runs up and says: “Hey how do I do this?” I figure, If nothing else maybe it will save me some time in future.

There are two audiences here. The first is the true 5 minute dba. Their seems to be a lot of folks out their who end up responsible for fixing or maintaining a MySQL database who are not really DBA’s. They maybe developers, sysadmins, or even network guys who know just a  little about databases. These guys and gals become DBA’s five minutes at time during the day generally when something goes wrong.  They tend to be  looking for the quick fix, something that can be done in five minutes or less so they can get back to their other important tasks.

The second group are comprised …

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The 380.6 Mb .mov movie (1 hr 16 min) can be played directly in your browser at http://technocation.org/node/671/play or downloaded at http://technocation.org/node/671/download.

Notes:
The partitioning part of the MySQL Manual is at: …

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The sildes are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The 380.6 Mb .mov movie (1 hr 16 min) can be played directly in your browser at http://technocation.org/node/671/play or downloaded at http://technocation.org/node/671/download.

read more

Have you ever been upset by the Linux tendancy to swap… Especially when trying to allocate a large InnoDB buffer pool.. Look at the following output:

yves@yves-laptop:~$ free
total used free shared buffers cached
Mem: 2041888 1991096 50792 0 52 954592
-/+ buffers/cache: 1036452 1005436
Swap: 975200 1308 973892

There is still 50792 + 52 + 954592 = 1005436 of free memory and Linux starts to swap!!! The reason is hidden here:

yves@yves-laptop:~$ cat /proc/sys/vm/swappiness
60

The swappiness controls the Linux to swap for the File cache. For a file server or a web server or even MySQL with MyISAM tables, the file cache is interesting but for InnoDB or NDB Cluster it is close to useless. Only put a “0″ in that proc entry (echo 0 > /proc/sys/vm/swappiness) and add …

A quick note…

As mentioned several times here, hardware can not be treated as a black box.  Every mysql professional who is charged with performance tuning has to understand where often overlooked bottlenecks can occur. This can occur anywhere in the system : disk, cpu, memory, networking.   Everyone who reads my blog knows that I have beaten the disk horse until its bloody corpse, although I still believe too many people ignore disk performance… Everyone looks at CPU, in fact every monitoring tool known to man seems to include cpu stats.  But what about network performance?  The performance of the network is even more taken for granted then disk is.  I mean to most people they don’t give a second thought to what’s happening between servers, after all isn’t that the “network teams”  job.   Unfortunately I run into network problems more often then I would like.  What could these  …

Next Thursday (February 5th), we're continuing our series of sessions on MySQL performance measuring and improvements with Allan Packer's presentation titled MySQL Performance and Scalability Project - Issues and Opportunities. Allan works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server.

Allan is based in Australia, so note that this session will take place in the morning (Europe) or evening (APAC), respectively.

For MySQL University sessions, point your browser to this page. You need a browser with a …

With MySQL 5.1, getting and analyzing all the queries hitting the database is very easy even if you don’t have access to the MySQL enterprise Monitor.  You can either activate the general log or, my favourite option, activate the slow query log with a sub millisecond long query time. Long query time below one second is an option introduced in MySQL 5.1. In order to catch all the queries, you will also need to disable the query cache.  The main advantages of the slow query log over the general log is that you have access to more information like the query time, the lock time, the number of rows examined and the number of rows sent back.  All the options surrounding the slow query log in MySQL 5.1 are dynamic so it is easy to turn the logging ON and OFF.  Once you have the queries, tools, like mysqlsla, can be used to anlyzed the queries and group them by type.

All these tips are nice if you are using MySQL 5.1 but what if …

One of the services we (Sun/MySQL) offer is a Performance Tuning and Optimization consulting package. These consulting engagements generally take several different forms as each client has a specific need that must be resolved. But there is a commonality between all of these assignments, they all require some amount of bottleneck detection as well as benchmarking. The topic of benchmarking and bottleneck detection is huge, and has a very broad audience. I really can not do it justice in a single post or two. In fact we are actually planning to give a Webinar in March ( more details will be forth coming ) talking about how we go about finding bottlenecks while out at client sites, and even in an hour I can barely scratch the surface.

What I thought I would share are a few of the easier cases of finding and fixing a bottleneck I have encountered. I am still amazed that more people have not performed similar steps to eliminate …

I spoke with Brian Aker last week about the issues I found with decrement and increment while working on my book "Developing Web Applications with Apache, MySQL, Memcached, and Perl". He suggested I make sure that I was using the latest libmemcached. I checked, and the version of libmemcached that is included with Memcached::libmemcached is 0.21 - this is the version of Memcached::libmemcached I obtained from using CPAN (perhaps CPAN needs a new bundle...). So, I obtained the most recent version of Memcached::libmemcached from the subversion repository at https://perl-libmemcached.googlecode.com/svn, and it includes the lastest libmemcached, version 0.25. To make a long story short, I re-wrote my test script to test each operation individualy and time that, as well as compare the times to Cache::Memcached. The numbers are great and are reason enough to use …