Re: MySQL total overheat (somewhat complex database)

On Sat, Jul 28, 2012 at 2:44 PM, lubos <lubosboucek@gmail.com> wrote:

Hello,

I have a quite sophisticated database with frequently interconnected tables and on the top level table, Django produces queries like this:

SELECT `data_schedule`.`id`, `data_schedule`.`process_id`, `data_schedule`.`hardware_id`, `data_schedule`.`task_id`, `data_schedule`.`start`, `data_schedule`.`times`, `data_schedule`.`done`, `data_process`.`id`, `data_process`.`string_id`, `data_process`.`operation_id`, `data_string`.`id`, `data_string`.`immutable`, `data_operation`.`id`, `data_operation`.`string_id`, T5.`id`, T5.`immutable`, `data_hardware`.`id`, `data_hardware`.`string_id`, `data_hardware`.`workspace_id`, `data_hardware`.`warmup`, T7.`id`, T7.`immutable`, `data_workspace`.`id`, `data_workspace`.`string_id`, T9.`id`, T9.`immutable`, `data_task`.`id`, `data_task`.`request_id`, `data_task`.`thread_id`, `data_task`.`amount`, `data_request`.`id`, `data_request`.`order_id`, `data_request`.`item_id`, `data_request`.`amount`, `data_order`.`id`, `data_order`.`string_id`, `data_order`.`subject_id`, `data_order`.`type`, `data_order`.`shipped`, `data_order`.`paid`, `data_order`.`end`, T13.`id`, T13.`immutable`, `data_subject`.`id`, `data_subject`.`string_id`, T15.`id`, T15.`immutable`, `data_item`.`id`, `data_item`.`string_id`, `data_item`.`measure_id`, `data_item`.`amount`, T17.`id`, T17.`immutable`, `data_measure`.`id`, `data_measure`.`string_id`, T19.`id`, T19.`immutable`, `data_thread`.`id`, `data_thread`.`string_id`, T21.`id`, T21.`immutable` FROM `data_schedule` INNER JOIN `data_process` ON (`data_schedule`.`process_id` = `data_process`.`id`) INNER JOIN `data_string` ON (`data_process`.`string_id` = `data_string`.`id`) INNER JOIN `data_operation` ON (`data_process`.`operation_id` = `data_operation`.`id`) INNER JOIN `data_string` T5 ON (`data_operation`.`string_id` = T5.`id`) INNER JOIN `data_hardware` ON (`data_schedule`.`hardware_id` = `data_hardware`.`id`) INNER JOIN `data_string` T7 ON (`data_hardware`.`string_id` = T7.`id`) INNER JOIN `data_workspace` ON (`data_hardware`.`workspace_id` = `data_workspace`.`id`) INNER JOIN `data_string` T9 ON (`data_workspace`.`string_id` = T9.`id`) INNER JOIN `data_task` ON (`data_schedule`.`task_id` = `data_task`.`id`) INNER JOIN `data_request` ON (`data_task`.`request_id` = `data_request`.`id`) INNER JOIN `data_order` ON (`data_request`.`order_id` = `data_order`.`id`) INNER JOIN `data_string` T13 ON (`data_order`.`string_id` = T13.`id`) INNER JOIN `data_subject` ON (`data_order`.`subject_id` = `data_subject`.`id`) INNER JOIN `data_string` T15 ON (`data_subject`.`string_id` = T15.`id`) INNER JOIN `data_item` ON (`data_request`.`item_id` = `data_item`.`id`) INNER JOIN `data_string` T17 ON (`data_item`.`string_id` = T17.`id`) INNER JOIN `data_measure` ON (`data_item`.`measure_id` = `data_measure`.`id`) INNER JOIN `data_string` T19 ON (`data_measure`.`string_id` = T19.`id`) INNER JOIN `data_thread` ON (`data_task`.`thread_id` = `data_thread`.`id`) INNER JOIN `data_string` T21 ON (`data_thread`.`string_id` = T21.`id`) ORDER BY `data_schedule`.`id` ASC

MySQL, however, isn't able to process it and after few minutes it holds all processor performance.

I would be glad for any idea.

Since you said this is caused by admin, did you modify the admin for this class to pull in all of these details? If not, you could probably tell it specifically which fields to show to reduce the number of joins it has to execute.

With that being said -- it may be worth-while to look at the way your data is structured and see if you can simplify it. However, I have no idea of the domain of your project and what you've modeled. 

I don't know how much experience you have with data modeling but if you'd like to post your models (like Cal mentioned) then we may be able to offer more suggestions on ways to enhance performance which would probably help in a lot more places than this one instance. Again, I'm not trying to look down at you so please don't take it that way :) There's many out there who are much smarter and more experienced in the realm of data modeling than myself!

Good luck!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.