As suggested in another thread I used the Devel module to get an overview of the mySQL queries. I used the frontpage since this is the most used and biggest page. I have some questions:
1) The Devel module displays a column "where" obviously including the function name which makes the mySQL query, but the function name links to the Drupal API which does not include custom module, i.e. I cannot see where to find the function. Is there a way to find the function to optimize?
2) I've left the slow query time to the default value of 5ms (i.e. every query longer than 5ms will be judged a slow query) - is that a good value?
3) Based on 2) there a lot of slow queries displayed: 85ms ("execute"), 66ms ("execute"), 28ms ("cache_set"), 19ms ("cache_get") etc. How can I optimize these queries?
4) There are some queres executes several times: 95 times ("realname_make_name"), 71 times ("get_latest_shout"), etc. How can I cache these queries?
5) Memory usage is Memory used at: devel_init()=2.01 MB, devel_shutdown()=57.46 MB. What does that tell me?
Thank you for your help!
Comments
The Devel module displays a
grep -n "function function_name" -r /path/to/modulewill find the definition of function_name() in any file in the "module" directory, printing the file path and line number.YMMV, and remember that you're not getting totally accurate numbers when this log is enabled thanks to the overhead of doing the logging anyway. I think it's a sane default, but don't sweat it too much if some of the larger or more esoteric queries go over.
I don't know what "execute" is, but cache-related queries do tend to take a bit of time because they're often inserting or extracting quite a big chunk of data. The best way to avoid these queries is to use the cache as sparingly as possible, though since the purpose of the cache is to speed things up in the first place, simply not using it at all would be even worse.
If it's data that just needs to live for the duration of the page request and doesn't need to persist beyond that, static variables should be used instead of the cache system. If you're unfamiliar with them, you can read about static variables here. If you're using Drupal 7, check out the drupal_static() function.
You're working with a lot of data. Possibly more than you need…?
The Boise Drupal Guy!
Thank you for you
Thank you for you answer.
Honestly, I never worked with the command line on a server, even though I just got root access via SSH for our server. Where exactly can I run that command?
It's the frontpage and we display some kind of Facebook-like news stream. Besides that it's actually only 4 views:
- a View containing the featured article (though we filter and sort on a CCK text field)
- a View showing a mini calendar with upcoming events
- a View showing a list of upcoming events
- a View showing 3 random users.
So I don't know where I can optimize..
so im guessing you have
so im guessing you have turned on that option inside views settings to log queries on devel?
Honestly, I never worked with
Ah. Hm. Well, anywhere, so long as the paths are correct. It would sort of make more sense to do it locally rather than on a live server, though… Sorry, you're speaking of optimizing queries, so I figured you were a nerd with coding and CLI skillz.
Random stuff doesn't cache well, for obvious reasons. See if you can ditch this, or failing that, at least have the results cached for a few minutes, even if this means they're not truly random on every page load.
The Boise Drupal Guy!