multisert gem

[Jeff Iacono]

Hi Dallas.rb,

Happy Friday! I wanted to pass along another quick gem that you'all may find interesting: Multisert

I was running into the following problem:

step 1: pull a bunch of records from a database

step 2: do some calculation on a per record basis

step 3: write the post-calculation data back to a database

step 3 was proving to be problematic due to single INSERTs being slow. In order to batch up records that I wanted written back I wrote Multisert (https://github.com/jeffreyiacono/multisert). With this in place, you hand it a db connection, database and table you want to write to, set of columns and then just shovel in records. Once the internal buffer size is reached, it batches everything up into a multi-insert statement and writes.

It's ended up improving runtime by around 30x (performance charts are in the README).

Be great to get people's thoughts on this? Anyone else find this useful? Areas of improvement? Alternative solution approaches (maybe with queues)?

Side note: for anyone that is really knowledgeable about mysql, any comments on why there is such a vast runtime improvement when batching up inserts would be awesome. It's also strange that batching up 30 records or 1,000 (or 10,000) has roughly the same runtime savings.

Jeff Iacono
917.520.2098
--

twitter.com/jfi

[Clifton King]

Most of the time spent in queries is in the SQL query analyzer/optimizer + round trip times. I'd think with a fast server you would get over 30x

Thanks for sharing! I'll take a look at them gem when I'm back in town

Sent from my phone.

--
 
---
You received this message because you are subscribed to the Google Groups "Dallas Ruby Brigade" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dallasrb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jeff Iacono]

Cool, appreciate it!

I've also been looking at using prepared statements:

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Seeing as we'll be doing the same sort of INSERT over and over, seems like it might be an ideal thing to leverage

Jeff Iacono
917.520.2098
--

twitter.com/jfi

[Stephen Crozier]

Jeff,

Very nice. And very well documented, which I appreciate. Hope to remember this when I need it!

The final write! concerns me a little, as it seems like the kind of thing that someone like me would easily forget. If I get some time, I'll see if I can propose a "solution."

Steve

[Curtis Summers]

Jeff,

I think what you've stumbled onto here has to do with the granularity of commits you're asking MySQL to do.  When you loop through the inserts, you are committing N times (auto-commit per query), so you have N fsyncs to disk, which will take it's sweet time.  When you batch them with your multisert process, you're only committing once per batch.  However, you need not batch them up like this.  Instead, just put these into a transaction.  Here's a modified version of your performance test wrapped in a BEGIN/COMMIT transaction:

def insert_transaction_performance_test connection, cleaner, sample_records, destination

  fields = sample_records.first.keys.join(', ')

  cleaner.ensure_clean_database!

  (timer = Timer.new).start!

  connection.query("BEGIN")

  sample_records.each do |record|

    connection.query %[

      INSERT INTO #{destination} (#{fields})

      VALUES (#{record.map { |k,v| v }.join(', ')})]

  end

  connection.query("COMMIT")

  runtime = timer.stop!

  ensure_data_completeness! connection, destination, sample_records.count

  puts "insert w/o buffer in a transaction took #{runtime.round(2)}s to insert #{sample_records.count} entries"

end

I ran the perf test on 10,000 entries and got the following (on my very slow laptop disk).  Notice my additional line at the end:

$ ruby ./performance/multisert_performance_test.rb 

[2013-04-01 13:29:29 -0500] generating 10000 random entries

[2013-04-01 13:29:29 -0500] generated 10000 random entries

[2013-04-01 13:29:29 -0500] starting performance test: using 10000 random entries, writing to multisert_performance.performance_data

insert w/o buffer took 405.84s to insert 10000 entries

multisert w/ buffer of 10000 took 0.35s to insert 10000 entries

insert w/o buffer in a transaction took 1.77s to insert 10000 entries

Depending on your MySQL config (innodb log file size), batching can still be helpful for large commits--notice that multisert was still faster in this instance.  But in general, it's a heck of a lot easier to just wrap everything up into one transaction for a significant speed boost.

>> It's also strange that batching up 30 records or 1,000 (or 10,000) has roughly the same runtime savings.

This is likely where you're reaching a disk I/O plateau in performance.

Hope that helps,

Curtis

[Jeff Iacono]

Hey Steve,

Thanks, appreciate it!

Definitely agree with you on the final #write! requirement: that's placing the onus on the end user and is not very kind of the codebase.

To resolve this, I added a #with_buffering method, let me know what you think:

relevant commit: https://github.com/jeffreyiacono/multisert/commit/24d1364d3bad9e09c371774b86acb30c50294b28

buffer = Multisert.new connection: dbclient,
                       database:   'some_database',
                       table:      'some_table',
                       fields:     ['field_1', 'field_2', 'field_3', 'field_4']

buffer.with_buffering do |buffer|
  (0..1_000_000).each do |i|
    res = some_magical_calculation(i)
    buffer << res
  end
end

We start by creating a new Multisert instance, providing the database connection, database and table, and fields as attributes. Next, we leverage #with_buffering to wrap our sample iteration. Within the block, we shovel the results fromsome_magical_calculation into the Multisert instance, which then handles all the heavy lifting in terms of writing to the database.

As an aside, #with_buffering is handling the following under the hood:

(0..1_000_000).each do |i|
  res = some_magical_calculation(i)
  buffer << res
end
buffer.write!

As we iterate through, the Multisert instance will build up the records and then write itself to the specified database table when it hits an internal count (default is 10_000 entries, but this can be adjusted via the max_buffer_count attribute). The buffer.write! at the end ensures that any pending entries are written to the database table that were not automatically taken care of by the auto-write that will kick in during the iteration.

Jeff Iacono
917.520.2098
--

twitter.com/jfi