Hi, I've found that this examples run batch messages insertion in a transaction,
so this achieves quite impressive performance, but if I put every message save
into its own transaction, ie, switch message.each do and db.transaction do
messages.each do |m|
db.transaction do |db_in_trans|
db_in_trans.execute("insert into messages(content) values( '#{m}' )")
end
end
ruby speed-test.rb 1
0.09 seconds to insert 1 records at 10.75 records per second
ruby speed-test.rb 10
1.20 seconds to insert 10 records at 8.31 records per second
ruby speed-test.rb 100
11.22 seconds to insert 100 records at 8.91 records per second
ruby speed-test.rb 1000
132.27 seconds to insert 1000 records at 7.56 records per second
the performance goes down pretty badly, goes down to several records per second.
On Fri, Aug 15, 2008 at 5:25 AM, Jeremy
Hinegardner<jeremy / hinegardner.org> wrote:
> On Fri, Aug 15, 2008 at 05:56:46AM +0900, ara.t.howard wrote:
>>
>> On Aug 14, 2008, at 1:10 PM, Martin DeMello wrote:
>>
>>> On Thu, Aug 14, 2008 at 12:06 PM, ara.t.howard <ara.t.howard / gmail.com>
>>> wrote:
>>>>
>>>> buffer them and insert them in a transaction 1000 at a time. ¨Âöå÷éôè
>>>> ruby this should be a peice of cake.
>>>
>>> Do any of the ruby db libraries offer support for doing this efficiently?
>>>
>>> martin
>>
>> pretty much all of them
>>
>
> [...]
>
>> cfp:~/rails_root > ./script/runner a.rb
>> using sqlite3
>> elapsed: 0.222311019897461
>> count: 10000
>>
>> using ar
>> elapsed: 7.75591206550598
>> count: 10000
>>
>> 0.2 seconds for 100000 records seems plenty fast to me. seconds noto
>> much.
>
> If your standard of performance is 10,000 records inserted in a minute, any
> database should be able to satisfy your requirements.
>
> And here's the amalgalite version of ara's test... embedded sqlite in a ruby
> extension.
>
> cat am_inserts.rb
> !/usr/bin/env ruby
> ¨Âåñõéòå §òõâùçåíó§
> ¨Âåñõéòå §áíáìçáìéôå§
>
> ¨ÂéúÉîôåçåò¨ÁÒÇÖ®óèéæô üü ±°ß°°°>
> ¨ÂåóóáçåÁòòáù®îå÷¨óéúå©®íáðû òáîä®ôïß>
> ¨Â¢óðååäôåóô®äâ>
> ¨ÂéìåÕôéìó®òíßÄâ éæ Æéìå®åøéóô¿¨ Äâ > ¨ÂÁíáìçáìéô庺Äáôáâáóå®îå÷Äâ > ¨Ââ®åøåãõôå¨ÃÒÅÁÔÅ ÔÁÂÌíåóóáçåó¨ãïîôåîô©» ¢©
>
> ¨ÂåæïòÔéíå®îï÷®ôïß> ¨Ââ®ôòáîóáãôéïäï üäâßéîßôòáîó> ¨Âåóóáçåó®åáãè äï üí> ¨Ââßéîßôòáîó®åøåãõô娢éîóåòô éîôï íåóóáçåó¨ãïîôåîôöáìõåó£ûíý ©¢> ¨Âîä
> ¨Âîä
> ¨Âæôåò Ôéíå®îï÷®ôïß> ¨Âìáðóåä áæôåâåæïòå
> ¨Âðó óéúå åìáðóå> ¨Âõô¢£û¢¥°®²æ¢ åìáðóåäý óåãïîäôï éîóåòô £ûóéúåòåãïòäáô £û¢¥°®²æíðóý òåãïòäðåóåãïîä>
> ruby am_inserts.rb
> .38 seconds to insert 10000 records at 25999.01 records per second
>
> ruby am_inserts.rb 100000
> .80 seconds to insert 100000 records at 26344.71 records per second
>
> enjoy,
>
> -jeremy
>
> --
> ========================================================================
> ¨ÂåòåíÈéîåçáòäîå ¨Â ¨ÂåòåíùÀèéîåçáòäîåò®ïò>
>
>