Last time I demonstrated a case where stored procedures are slow when they have to do computationally expensive work.  The more interesting, to me at least, question is how slow is SQL?  The answer to that is far more complex. 

For example, this simple SQL takes 2.2 seconds to run on MySQL.  This is a painfully faster than the 696 seconds it took a stored procedure to produce similar results. 

select customerName, count(*)
  from Salet s
  join customer c on s.customerId = c.customerId
group by customerName

As demonstrated in the previous article, the equivalent C# code took 1.4 seconds to produce the same results.  Some may find it surprising that it take less time to ship 1 million rows out of the database and then summarize it in code than it does for MySQL to summarize the same data in the database. 

In this simple case the performance difference …

I scrubbed the SQL here to protect the innocent, but check this fun stuff out.  Working with a client who randomly started seeing huge spikes in CPU and disk activity on their server after weeks of seemingly running fine.  I tracked it down to a subset of long running queries.  These queries typically run in around a second per run, but out of the blue they started taking 600 seconds.

Here is the explain for the first query:

Explain:

Boy am I sick or what, maybe I should see a shrink or something.  What’s the first thing that runs through my head when I wake up on a Sunday morning?  Is it thoughts of family, the days events, happy thoughts or something pleasant non technical?  No.  Its that we need a better benchmark suite for MySQL and the open source database community as a whole.  Right now the staple of our benchmarking diet is sysbench and dbt2.  While both have served their purpose they are a bit long in the tooth and do not do a good job of truly capturing what end users are doing out their.  I mean sysbench uses a single table!   Where’s the join love.  Has anyone outside of a PT expert successfuly gotten dbt2 installed?  probably not because its a pain in the rear.   Plus what the hell does box A got a TPM of 5000 vs box B’s TPM of 6000 mean?  Seriously to a client the only thing it tells is in …

We have benchmark parties!  Yes , I spend my weekend glued to the LCD running benchmarks day and night.  While it may seem like the work of a madman or a lonely bunch of geeks without a date who are just trying to pass the time before their WOW quest begins…  its really rather fun.

Check out this:  The RamSan 500 …. I have had access to this bad boy this week to perform all kinds of interesting test.  How fast is it?  50-50 read/write benchmarks I am pulling down 24K iops and I am not taxing the drive ( the benchmark system’s cpu is only a quad core with 2gb of memory )…  I will have a report for you later this week.

What else?  Well my ec2 trials and tribulations continue…  sometimes I feel like I am trying to squeze performance out of an old 386 ( without the co-processor ).

Of course I am …

I was testing up our latest Waffle release when I saw this horrid latency, average in the 12+ms range.  So I decided to dig a bit deeper today:

i’ll start with a Single threaded test on the ec2 setup I was complaining about, this is running waffle 0.4 and memcached 1.2.5.

12ms?  Really, I mean come on…. what are we using 10baseT?

at least it appears somewhat consistent.  Consistently bad, but consistent… and if you can not survive the odd spike to 35ms, what good are you.

As we have mentioned several times you probably know we are planning to migrate our development over to the innodb-plugin and start following the path of the storage engine.  That’s all well and good but what are the warts and bugs and things we are trying to fix?  Ahhh yes the seedy underbelly of Waffle Grid. The reason why we call it experimental.

Big on my list is going to be ensuring data integrity.  Currently you can manually ( or via some weird network corruption ) overwrite a good innodb page with an invalid one, the result? Well lets check it out:

I have just uploaded a technology preview release of Waffle Grid code named “Butter”.  Yes Butter is good on Waffles.

This release is a preview release, experimental, and should probably not be used in production unless you are brave…  I am including the full source for this release.  This includes patched memcached, libmemcached, and MySQL 5.1.30.  I bundled it this way to try and get folks to try it out and let us know what they think.  This is probably going to be the only time we package the full MySQL source as we are now working with the innodb-plugin.  You can still grab this as a patch off of launchpad.

What’s in this release?  Well this is our first “release”  but some of the notable items since the last …

A quick one here, we often talk about effectively utilizing memory to get the most of your MySQL Server.  I wanted to remind folks to not forget about allocating memory to a tmpfs for the tmpdir.  Certainly this is not going to help everyone, but those who create lots of temporary table will find the performance boost most welcome. In fact in some cases you maybe better off allocating a little extra memory to a tmpfs then you would be the innodb buffer pool, but like everything it depends on your workload and environment.

What is tmpfs?  In a nutshell a filesystem ontop of a ramdisk  used for temporay file access.  Read the wiki page for more.   Sounds like a great thing for /tmp right? By the way It really urks me that most people leave /tmp as part of the root filesystem ….  shameful, read my common mistakes post for more complaining …

I harped on this earlier this month. The network is an often over looked, but vital component of every application. I have been in many shops content with running 100Mb/s between the application and database simply because they are no where near maxing out the available network bandwidth between the two servers. What they are forgetting is there is a big latency difference between 10Mb,100Mb, & 1000mb. Speaking from my testing on Waffle Grid we see that under load 100mb connection routinely has network latency in the 3000-4000 microsecond range, while running under load in 1gbe tests we routinely run at around 1100 microseconds. By the way the same test using a Dolphin interconnect card finishes with an average latency of less then 300 microseconds. These tests average less then 5Mb/s being pushed over the network, which from a network perspective would not even hit half the available …