Hi everyone,
I'm currently working on a project which will execute a rake task from
time to time, and said taks 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, it's
just
#################
######## 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 teste performance, for the single query variant and the multiple
queries variant, and then changed MySQL 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 checked process info, and it's 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 :(
Cheers,
Paulo Pereira
THE NEW MYSQL OPTIONS:
[mysqld]
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M
read_rnd_buffer_size=2M
max_heap_table_size=256M
tmp_table_size=256M
myisam_sort_buffer_size=64M
thread_cache=256
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
