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 endThere 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 endI 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 endAnd 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.
![]() | Published on March 4th, 2008 | | No Comments | | Posted by Roman Mackovcak |