Get 10-15% more performance with MySQL/Rails
Posted by admin October 30, 2005 @ 07:43 AM
Rails 0.14.x and forward contains a hefty performance increase for the MySQL adapter, but it requires that you have the latest version of the underlying, native bindings (2.7). On Stephan Kaes’ benchmark suite, the upgrade is 10-15%:
You can get the latest MySQL bindings with gem install mysql (or gem install mysql -- --include=/usr/local/lib on OS X). The additional speed upgrade will automatically kick in as soon as you have the latest bindings (to check, open the console, require ‘mysql’, puts Mysql::VERSION, and expect to see 20700).
UPDATE: If you’re on Windows, Justin Rudd went through the trouble of describing how to make it work there.
Just two wee notes:
1) In my case i had to do a:
gem install mysql——with-mysql-dir=/usr/local/mysql
in order to upgrade the gem.
2) In script/console:
>> require ‘mysql’ => false >> puts Mysql::VERSION 20700
Looks like “require ‘mysql’” is already done by the console so it’s not really needed.
@David: Yay! Improvements in performance are always welcome :) Good job!
When trying to do this on a WinXP box with ruby 1.8.2, I encountered the following errors…
C:\>gem install mysql Attempting local installation of ‘mysql’ Local gem file not found: mysql*.gem Attempting remote installation of ‘mysql’ Building native extensions. This could take a while… ERROR: While executing gem … (RuntimeError) ERROR: Failed to build gem native extension. Gem files will remain installed in c:/ruby/lib/ruby/gems/1.8/gems/mysql-2.7 for inspection. ruby extconf.rb install mysql\nchecking for mysql_query() in mysqlclient.lib… no checking for main() in m.lib… yes checking for mysql_query() in mysqlclient.lib… no checking for main() in z.lib… no checking for mysql_query() in mysqlclient.lib… no checking for main() in socket.lib… no checking for mysql_query() in mysqlclient.lib… no checking for main() in nsl.lib… no checking for mysql_query() in mysqlclient.lib… no
Any ideas? MySQL installed in “c:/program files/mysql”
@Kevin: Have you tried:
gem install mysql——with-mysql-dir=c:/program files/mysql
As you can see on my previous post I had to do something similar.
@Ruben: I get an ‘invalid option’ with that on my platform.
@Kevin: You probably need quotes around the path, since Program Files contains a space.
i got the same problem of kevin, and based on ruben’s advice i’m getting this now:
C:\ruby\lib\ruby\gems\1.8\gems\mysql-2.7>”c:\ruby\bin\ruby.exe” “c:\ruby\bin\gem ” install “mysql—with-mysql-dir=C:/Program Files/MySQL/MySQL Server 4.1/bin”
Attempting local installation of ‘mysql—with-mysql-dir=C:/Program Files/MySQL/M ySQL Server 4.1/bin’ Local gem file not found: mysql—with-mysql-dir=C:/Program Files/MySQL/MySQL Ser ver 4.1/bin*.gem Attempting remote installation of ‘mysql—with-mysql-dir=C:/Program Files/MySQL/ MySQL Server 4.1/bin’ ERROR: While executing gem … (Gem::GemNotFoundException) Could not find mysql—with-mysql-dir=C:/Program Files/MySQL/MySQL Server 4.1 /bin (> 0) in the repository MSH C:\ruby\lib\ruby\gems\1.8\gems\mysql-2.7>
any help would be much appreciated. thanks!
@Mike, I tried it with quotes as well, but still got the ‘invalid option’ message. It’s choking on the ‘—with-mysql-dir’ option and not the value it is assigned.
I had some problems installing this as well. But I found the the fix at
http://wiki.rubyonrails.org/rails/pages/MySQL+Database+access+problem
I had the same problem and tried to “gem install mysql�. It failed. I ran extconf.rb for mysql sources that gem wisely saved. Extconf.rb looks for mysql lib and include directories in /usr/local whereas in my case they were in /usr/local/mysql. Adding parameters to extconf.rb solved the problem:
cd /usr/lib/ruby/gems/1.8/gems/mysql-2.7 sudo ruby extconf.rb—with-mysql-dir=/usr/local/mysql sudo make sudo make install
Oh, also… this blog converts double – to an em dash, so you can’t really copy and paste the code sometimes.
—!= – -
That’s right. I haven’t noticed that behaviour on the post formatting.
long dash !=
(remove this)(two single dashes)Sorry about that.
Anyway, i think J. Weir has expressed it clear enough :-)
Uh… no. I have not been able to get J. Weir’s solution to work on a windows machine.
of if a
-!= – -, when what does the -equal?Okay, I hope this makes it clear:
http://rafb.net/paste/results/61CAVX53.html
Well, thanks for the clarification, the dashes are clear now. However, now I get a different error:
Building native extensions. This could take a while…
c:/ruby/lib/ruby/1.8/mkmf.rb:649:in `dir_config’: private method `split’ called for true:TrueClass (NoMethodError)
are there speed comparisons of using rails with different datbase drivers? I use only postgresql on my rails projects
I noticed that my mysql connection was broken this afternoon until I upgraded the binding, I’m not sure when that started. Possibly when I upgraded to .14.1 and Mysql 5.
thank for Ruben . this my error i use this link http://rafb.net/paste/results/61CAVX53.html
Attempting local installation of ‘mysql’ Local gem file not found: mysql*.gem Attempting remote installation of ‘mysql’ Building native extensions. This could take a while… c:/ruby/lib/ruby/1.8/mkmf.rb:649:in `dir_config’: private method `split’ called for true:TrueClass (NoMethodError) from extconf.rb:12 ERROR: While executing gem … (RuntimeError) ERROR: Failed to build gem native extension. Gem files will remain installed in c:/ruby/lib/ruby/gems/1.8/gems/mysql-2.7 for inspection. ruby extconf.rb install mysql——with-mysql-dir C:/Program Files/MySQL/MySQL Server 4.1\n
Results logged to c:/ruby/lib/ruby/gems/1.8/gems/mysql-2.7/gem_make.out
my error !
RE: #14 & #17
This is because of an issue w/ gem.cmd: if you look command line after running “gem install mysql dashdash dashdashOptions=Values”, it is dropping one set of “dashdashes”.
Try:
1) cd RUBYDIR (ex: c:\ruby\bin\) 2) ruby gem install mysql (dashdash) (dashdash)with-mysql-dir=/your/path
None of the above helps, unfortunately :-(
@ EJC . . . I noticed that the equal sign after—with-mysql-dir is dropped, but all the dashes remain:
C:\Documents and Settings\Catherine Zeta Jones>”c:\Progra1\Ruby\bin\ruby.exe” “c:\Progra1\Ruby\bin\gem” install mysql——with-mysql-dir “c:\program files\xampp\mysql” Attempting local installation of ‘mysql’ Local gem file not found: mysql*.gem Attempting remote installation of ‘mysql’ Building native extensions. This could take a while… c:/progra1/ruby/lib/ruby/1.8/mkmf.rb:649:in `dir_config’: private method `split’ called for true:TrueClass (NoMethodError) from extconf.rb:12 ERROR: While executing gem … (RuntimeError) ERROR: Failed to build gem native extension. Gem files will remain installed in c:/Progra1/Ruby/lib/ruby/gems/1.8/gems/mysql-2.7 for inspection. ruby extconf.rb install mysql——with-mysql-dir c:\program files\xampp\mysql\n
Results logged to c:/Progra~1/Ruby/lib/ruby/gems/1.8/gems/mysql-2.7/gem_make.out
C:\Documents and Settings\Catherine Zeta Jones>
No luck on windows here either. I end up with the same error as #14, #17 and #19 on windows server 2003 and mysql 5.0. I’ve tried literally every possible combination of quotes, dashes and so on. It always seems to crash on trying to split the path up in mkmf.rb:649, while there don’t seem to come along something that looks like a ‘dir’ and smells like a ‘dir’, or why else would it be called for true:TrueClass? Kind of annoying!
regards Jan Prill
TO ALL WINDOWS USERS.
Mysql ruby extension does not compile with defauls options neither with extconf.rb options.
You have to create a make file by hand and compile the extension manually. That’s how i’ve done it. With the last 2 previous versions. Check this page (http://seagecko.org/thoughts/in-the-past/2004/09/09/ruby-mysql-and-windows/) explains the “pain” of compiling mysql ruby extension on windows platforms.
Tonight i’ll compile this new mysql extension with my custom makefile and then will post the results.
This might be of some help. Though I haven’t tried compiling it myself, thus you still might have to build against mysql 5.
http://groups.google.nl/group/comp.lang.ruby/browse_thread/thread/f6892110e5209a3e/889e72ac81fac4ff?hl=nl#889e72ac81fac4ff (It’s the link under my nick)
Hmm, when i uses that precompiled binary I get to enjoy the weirdest errors.
rhtml templates that were working fine now generate errors: Showing app/views/index/index.rhtml where line #0 error on line 0?! right.. and the part of the file shown that is assumed to have the error is different for every view :/
On OS X 10.4.2 I had to run:
sudo gem update mysql——with-mysql-config
On OS X 10.3, I also had to run:
sudo gem update mysql——with-mysql-config
On OS X 10.4.3, fresh install, I needed:
sudo gem install mysql——with-mysql-config
Whoops. Got ahead of myself. I’m on 10.4.3 and Rails 0.14.2. gem tells me I have successfully installed mysql, however, when I try requiring it in the console I get:
>> require ‘mysql’ dyld: NSLinkModule() error dyld: Symbol not found: _sprintf$LDBLStub Referenced from: /usr/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.bundle Expected in: flat namespace
Trace/BPT trap
Alrighty, working now… you need to use gcc 4.0.
After 3 days of cursing I finally got the bitch working on WinXP (I think..). I compiled against both mysql server version 4.1.15 and 5.0.15. (I currently only use the former, as the latter gives problems with older aps)
Anyway, the result is on http://jeroen.concept-q.biz/files/
I’d appreciate feedback if it works for you too, as I’m sure others would like to know whether or not these libraries are useful for them (I haven’t done extensive testing so I cannot claim it works). If it doesn’t work I’m sorry to say my C knowledge has already been stretched to its limits and I’m rather clueless on how to solve any problems.
Just me testing comments. Ignore!
bindings update for RedHat worked with this: gem install mysql——with-mysql-config
After 4 days of trying I throw in the towel. Tried all the suggested alternatives here and on other websites still get: ERROR: While executing gem … (RuntimeError) ERROR: Failed to build gem native extension. Gem files will remain installed in th-mysql-config/gems/mysql-2.7 for inspection. ruby extconf.rb update mysql -with-mysql-config\nchecking for mysql_query() in -lmysqlclient… no checking for main() in -lm… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lz… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lsocket… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lnsl… no checking for mysql_query() in -lmysqlclient… no
OS X 3.9, MySQL 5, Ruby 1.82, Gem 0.8.11
PHP only took me 12 mins by comparison
After 4 days of trying I throw in the towel. Tried all the suggested alternatives here and on other websites still get: ERROR: While executing gem … (RuntimeError) ERROR: Failed to build gem native extension. Gem files will remain installed in th-mysql-config/gems/mysql-2.7 for inspection. ruby extconf.rb update mysql -with-mysql-config\nchecking for mysql_query() in -lmysqlclient… no checking for main() in -lm… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lz… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lsocket… no checking for mysql_query() in -lmysqlclient… no checking for main() in -lnsl… no checking for mysql_query() in -lmysqlclient… no
OS X 3.9, MySQL 5, Ruby 1.82, Gem 0.8.11
PHP only took me 12 mins by comparison