Home About Archives   Contact

Archive for the ‘Performance’ Category

Improve performance of MySQL driver for RoR

Last week I was working on the performance tuning of a rails application. I ran a profiler and found something very interesting.

I found that there is a procedure that is called very often and takes a lot of time.

The procedure was Mysql#get_length:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# File src/rails-1.2.3/activerecord/lib/active_record/vendor/mysql.rb
  def get_length(data, longlong=nil)
    return if data.length == 0
    c = data.slice!(0)
    case c
    when 251
      return nil
    when 252
      a = data.slice!(0,2)
      return a[0]+a[1]*256
    when 253
      a = data.slice!(0,3)
      return a[0]+a[1]*256+a[2]*256**2
    when 254
      a = data.slice!(0,8)
      if longlong then
        return a[0]+a[1]*256+a[2]*256**2+a[3]*256**3+
          a[4]*256**4+a[5]*256**5+a[6]*256**6+a[7]*256**7
      else
        return a[0]+a[1]*256+a[2]*256**2+a[3]*256**3
      end
    else
      c
    end
  end

There is obviously space for improvement! Replace the multiplications by shifts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

class Mysql
  def get_length(data, longlong=nil)
    return if data.length == 0
    c = data.slice!(0)
 
    case c
    when 251
      return nil
    when 252
      a = data.slice!(0,2)
      return a[0]+(a[1]<<8)
    when 253
      a = data.slice!(0,3)
      return a[0]+(a[1]<<8)+(a[2]<<16)
    when 254
      a = data.slice!(0,8)
      if longlong then
        return a[0]+(a[1]<<8)+(a[2]<<16) +(a[3]<<24)+(a[4]<<32)+(a[5]<<40)+(a[6]<<48)+(a[7]<<56)
      else
        return a[0]+(a[1]<<8)+(a[2]<<16)+(a[3]<<24)
      end
    else
      c
    end
  end
end

I ran the profiler again and, well… a wisdom of my university times popped on my mind: “There is an elegant, simple, nice and obvious solution for each problem. Unfortunately, it is wrong!”

Performance remained the same. So, deeper investigation is needed! It was not difficult to find out that most of the times the “else” branch is executed. I tried something like this: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

  def get_length(data, longlong=nil)
    return if data.length == 0
    c = data.slice!(0)
 
    return c if c<251
 
    case c
    when 251
      return nil
    when 252
      a = data.slice!(0,2)
      return a[0]+(a[1]<<8)
    when 253
      a = data.slice!(0,3)
      return a[0]+(a[1]<<8)+(a[2]<<16)
    when 254
      a = data.slice!(0,8)
      if longlong then
        return a[0]+(a[1]<<8)+(a[2]<<16) +(a[3]<<24)+(a[4]<<32)+(a[5]<<40)+(a[6]<<48)+(a[7]<<56)
      else
        return a[0]+(a[1]<<8)+(a[2]<<16)+(a[3]<<24)
      end
    else
      c
    end
  end

And the performance?

Improved! The toplevel cumulative time is down by over 20 seconds.
Now, here’s how you can embed this hack into your application:

  • Create a file called e.g. mysql_fix.rb
  • Add there
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

require 'active_record/vendor/mysql'
 
class Mysql
  def get_length(data, longlong=nil)
    return if data.length == 0
    c = data.slice!(0)
 
    return c if c < 251
 
    case c
    when 251
      return nil
    when 252
      a = data.slice!(0,2)
      return a[0]+(a[1]<<8)
    when 253
      a = data.slice!(0,3)
      return a[0]+(a[1]<<8)+(a[2]<<16)
    when 254
      a = data.slice!(0,8)
      if longlong then
        return a[0]+(a[1]<<8)+(a[2]<<16) +(a[3]<<24)+(a[4]<<32)+(a[5]<<40)+(a[6]<<48)+(a[7]<<56)
      else
        return a[0]+(a[1]<<8)+(a[2]<<16)+(a[3]<<24)
      end
    else
      c
    end
  end
end
  • Put it into e.g. lib/zmok directory
  • Add into your environment.rb following line
1

require File.join(File.dirname(__FILE__), '../lib/zmok/mysql_fix')

It is funny how my performance tuning session ended. Instead of changing my Rails application, I ended up improving the MySQL driver.

RSS Feed Published on March 4th, 2008 No Comments Posted by Roman Mackovcak