So since last talking about a new benchmark I found a need for one.  Specifically the need to truly test IO bound workloads in mysql to flex fast storage subsystems.  Tackling a new benchmark is not as easy as I thought.  I am already on version 2 of my code and its still really a mess.  But it appears solid enough to try and give it a few test runs on some really fast disk subsystems.  So Armed with several really bad queries I put together a read-only workload that just hammers the disk and little else…  I am working a little under the gun as I only have access to the TMS Ramsan for 1 more night, and only have access to a Violin memory test box for a short while longer as well.  The problem I am running into with these systems is DBT2 and sysbench bottleneck the CPU before they really flex the disk.  I want to flex the disk damn it!

So how’s it going?  reasonably well. I am not happy with …

It’s a fairly simple rule, and something that should be obeyed for your health and sanity.

There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.

Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: NULL
         type: NULL
possible_keys: NULL
          KEY: NULL
      key_len: NULL
          ref: …

Alright my stats patch appears to be working ( its a first step anyways ), eventually this is going to make it into waffle grid… but I thought it would be useful as a standalone patch.  Basically what this patch does is turn off the dynamic collection of stats by disabling the calls to dict_update_statistics_low & btr_estimate_number_of_different_key_vals for everything but the analyze statement. At this point its not really refined, but it does appear to work.  i.e. It will need more refining in the future and may not 100% work in every little case.  The short of it, this means that once you set your stats you should not have to worry about the cardinality of your tables changing dramatically.

Other databases ( even other storage engines ) allow you to control when and how your stats are gathered.  Lots of shops analyze their tables once a week using a large sample set with the idea that their execution plans will stay …

This is the 137th edition of Log Buffer, the weekly review of database blogs. Dave Edwards is enjoying a week off, and so as part of my plot to take over the world, I am writing this week’s Log Buffer.

First, the fun stuff: Josh Berkus tells us that the American English Translation of the Manga Guide to Databases is available in Japanese Fairies and Third Normal Form.

Then, the basics:
Giri Mandalika points to an article on Using MySQL with Java Technology. This is a basic article on how to connect, and …

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.