Concurrency model for mysql data [email protected] 2012

Concurrency Model for huge MySQL data

Mu-Fan Teng(@ryudoawaru)@ RubyConf Taiwan 2012

12年12月8⽇日星期六

https://twitter.com/ryudoawaru

https://twitter.com/ryudoawaru

緣起

Background


Legacy environment

• A Mysql Database with 2.3gb data with Big5 charset and ISO-8859-1 encoding.

• The biggest table in DB is 1.5gb.


The purpose


Transcoding to UTF8


Try


Work Flow1. mysqldump with -default-character-

set=latin1 parameter to generate SQL file.

2. Transcoding SQL file with tool like iconv/bsdconv.

3. Edit transcoded SQL file to avoid 「slash」 problem.

4. Restore SQL file to new DB.


Failed!


Cause

• Too big size for most text editor.

• Many mis-encoding text.


Let’s reinvent the wheel!


The new work flow

• Connect DB

• Transcode

• Output db rows to SQL insert statement.

• Write SQL file


CORES_COUNT = 4LIMIT = ARGV[0].to_i || 10000sqls = CORES_COUNT.times.map do |x| sprintf("SELECT * FROM cdb_posts ORDER BY pid LIMIT %d OFFSET %d;", LIMIT, (x * LIMIT))end

class String def to_my_val "'#{Mysql2::Client.escape self.force_encoding(‘Big5-UAO’).encode('UTF-8', :invalid => :replace, :undef => :replace, :replace => '??')}'" endend

procs = sqls.map do |sql| Proc.new do |out| Mysql2::Client.new(database: DBNAME, reconnect: true, encoding: 'latin1').query(sql).each(as: :array) do |row| out.print "INSERT INTO `cdb_posts` VALUES (#{row.map(&:to_my_val).join(',')});\n" end endendprocs.each{|p| p.call(OUT)}


Thanks for Ruby 1.9’s Awesome Encoding class which supports

Big5-UAO.


Reduces almost 80% of encoding problem.


But the file size is too big to wait for transcoding!


So I have to find the concurrency model to

make it faster.


Experiment Target

• Test the difference of performance between thread and fork model.


H&W Platform

• 4 Cores Core2Quad [email protected]

• 8GB RAM

• 1*SSD

• MacOS 10.8

• MRI 1.9.3p194


mailto:[email protected]

mailto:[email protected]

DBNAME = 'wwwfsc'CORES_COUNT = 4ForceEncoding = 'Big5-UAO'LIMIT = ARGV[0].to_i || 10000OUT = '/dev/null'

sqls = CORES_COUNT.times.map do |x| sprintf("SELECT * FROM cdb_posts ORDER BY pid LIMIT %d OFFSET %d;", LIMIT, (x * LIMIT))end

class String def to_my_val "'#{Mysql2::Client.escape self.force_encoding(ForceEncoding).encode('UTF-8', :invalid => :replace, :undef => :replace, :replace => '??')}'" endend

procs = sqls.map do |sql| Proc.new do |out| open(out,'w') do |io| Mysql2::Client.new(database: DBNAME, reconnect: true, encoding: 'latin1').query(sql).each(as: :array) do |row| io.print "INSERT INTO `cdb_posts` VALUES (#{row.map(&:to_my_val).join(',')});\n" end end endend


Benchmark.bm(15) do |x| x.report("Thread"){procs.map{|p| Thread.new{p.call(OUT)} }.each(&:join)} x.report("Fork"){procs.each{|p| fork{p.call(OUT)} }; Process.waitall} x.report("Normal"){procs.each{|p| p.call(OUT)}}end


Result of 100k*4 rows


Thread


Fork


Circumstance

• Thread

‣ CPU utilization rate between 105 and 125 percent.

• Fork

‣ The rate changes frequently between processes.


GVL still effects


Try again


Decompose the steps to find how to skip

GVL.


Experiment No.2


Minify the process to query DB only.


DBNAME = 'wwwfsc'CORES_COUNT = 4limit = ARGV[0].to_i || 10000

sqls = CORES_COUNT.times.map do |x| sprintf("SELECT * FROM cdb_posts ORDER BY pid LIMIT %d OFFSET %d;", limit, (x * limit))endprocs = CORES_COUNT.times.map do |x| Proc.new do client = Mysql2::Client.new(database: DBNAME, reconnect: true) result = client.query(sqls[x]) endend

Benchmark.bmbm(15) do |x| x.report("Thread"){procs.map{|p| Thread.new{p.call} }.each(&:join)} x.report("Fork"){procs.each{|p| fork{p.call} }; Process.waitall} x.report("Normal"){procs.each(&:call)}end


It seems the Mysql2 Gem can skip GVL.


Experiment NO.3


Limit the experiment to I/O operation only.


client = Mysql2::Client.new(database: DBNAME, reconnect: true, encoding: 'latin1')sql_raws = sqls.map do |sql| arr = [] client.query(sql).each(as: :array) do |row| arr << "(#{row.map(&:to_my_val).join(',')})" end arrend

procs = sql_raws.map do |arr| proc do io = open('/dev/null','w') io.write "INSERT INTO `cdb_posts` VALUES " io.write arr.join(',') io.write "\n" io.close endend

Benchmark.bm(15) do |x| x.report("Thread"){procs.map{|p| Thread.new{p.call} }.each(&:join)} x.report("Fork"){procs.each{|p| fork{p.call} }; Process.waitall} x.report("Normal"){procs.each{|p| p.call}}end


Change I/O to different files.


procs = sql_raws.map do |arr| proc do io = Tempfile.new(SecureRandom.uuid)#open('/dev/null','w') puts io.path io.write "INSERT INTO `cdb_posts` VALUES " io.write arr.join(',') io.write "\n" io.close endend


Result reversed


Implement the same change to the first

experiment.


procs = sqls.map do |sql| Proc.new do io = Tempfile.new(SecureRandom.uuid) Mysql2::Client.new(database: DBNAME, reconnect: true, encoding: 'latin1').query(sql).each(as: :array) do |row| io.write "INSERT INTO `cdb_posts` VALUES (#{row.map(&:to_my_val).join(',')});\n" end io.close endend


Dose not effect any


ConclusionThread fork normal

MySQL2-read Fast Fast x

Transcoding & iteration Slow Very fast x

Write to the same I/O Very slow Slow Fast

Write to the different I/O Fast Slow Fast


There is no effective and 「all-around」 concurrency model.


The small I/O can’t release GVL.


Kosaki-san’s slide


http://www.slideshare.net/kosaki55tea/ruby-gvlimprovement-8617719

http://www.slideshare.net/kosaki55tea/ruby-gvlimprovement-8617719

Matz is not a threading guy


http://jstorimer.com/2012/11/08/matz-is-not-a-threading-guy.html

http://jstorimer.com/2012/11/08/matz-is-not-a-threading-guy.html

End


Concurrency model for mysql data [email protected] 2012

News & Politics

Transcript of Concurrency model for mysql data [email protected] 2012