Rake tasks com queries SQL dispendiosas

[Paulo Pereira]

Viva a todos.

Surgiu-me um problema recente, no final da minha tese de mestrado (5 anos na FEUP), onde estou a fazer uma app em rails. Imaginem um StumbleUpon mais simples, e com uma API.

Tenho um algoritmo que preciso de correr, que involve manipular imensos dados numa DB MySQL. Ando a fazê-lo numa rake task, mas não tenho a certeza de como melhorar a performance: usar mais CPU, usar threads, etc. O problema é que não percebo nada de concorrência/threading em Rails, nem de configuração de MySQL.

Vou colar aqui o post que fiz no rubyonrails-talk, mas mais detalhado:

------------------------------------------------------------------------------------------------------------

I'm currently working on a project which will execute a rake task from
time to time, and said task involves a complex recommendation
algorithm that can be accomplished with a complex SQL query.

I may use a single one to do everything, or a lot of them, one per
item to be computed. The nature of the query isn't in cause here, but here it is nevertheless:

#################
######## ONE SINGLE QUERY
query "INSERT INTO deviations(pivot_id, deviant_id, value)
SELECT
  ratings.content_id as pivot_id,
  ratings2.content_id as deviant_id,
  AVG(ratings.rate - ratings2.rate) as value
FROM ratings
INNER JOIN ratings as ratings2
  ON ratings.viewer_id = ratings2.viewer_id
  AND ratings.viewer_type = ratings2.viewer_type
  AND ratings.content_id < ratings2.content_id
GROUP BY pivot_id, deviant_id"

ActiveRecord::Base.connection().execute(query)

######## MULTIPLE QUERIES
sql = ActiveRecord::Base.connection()
ids.each do |id|
  query = "INSERT INTO deviations(pivot_id, deviant_id, value)
  SELECT
    #{id.to_i} as pivot_id,
    ratings2.content_id as deviant_id,
    AVG(ratings.rate - ratings2.rate) as value
  FROM ratings
  INNER JOIN ratings as ratings2
    ON ratings.viewer_id = ratings2.viewer_id
    AND ratings.viewer_type = ratings2.viewer_type
    AND ratings.content_id = #{id.to_i}
    AND ratings.content_id < ratings2.content_id
  GROUP BY deviant_id"
  sql.execute(query)
end
#################

Just for curiosity sake, I have an index on [viewer_id, viewer_type,
content_id]

I tested performance for the single query variant and the multiple
queries variant, and then changed MySQL settings so I can have more memory and
the temp tables aren't stored in the hard drive.

After changing MySQL settings, the single query variant improved,
(268sec vs 80sec) but the multiple queries variant took almost twice
the time. (190sec vs 390sec) What the...??

I'm dealing with a lot of data here, the ratings table has 80.000 rows, and the

resulting deviations table has 400.000. I checked process info, and mysqld is just using

one of the four available CPUs.

- What can I do to improve this performance?
- How to use all the available CPUs?
- Is it to create multiple threads inside the rake task, and then use a
connection per task with multiple queries??
- Am I doing something fundamentally wrong here?

Thanks in advance for some tips, I'm really out of ideas, and this is
my master thesis that ends in two weeks :( 

------------------------------------------------------------------------------------------------------------

Estou bastante surpreendido com a mudança na configuração do MySQL ter *piorado*

uma das variantes... não percebo.

Abraço,

Paulo Pereira
http://paulozoom.com
----
email, msn: paulo...@gmail.com
twitter: http://twitter.com/paulozoom
skype: paulo.zoom

[Psousa]

oi,

sorry, não tenho muita experência em optimização de queries mysql.
não podes usar o backgroundrb para correr as queries periodicamente á
parte do resto da aplicação? até poderias enviar um email no fim. :)

curiosamente há uns plugins por aí que usam 2 cpu's para correr
testes, talvez possas tirar algum código daí.

cumps,
Pedro Sousa

> email, msn: paulo.z...@gmail.com

[Paulo Pereira]

Sim, é para correr à parte da aplicação.

Paulo Pereira
http://paulozoom.com
----
email, msn: paulo...@gmail.com

twitter: http://twitter.com/paulozoom
skype: paulo.zoom