July 6, 2007 - 6:47am
I have a somewhat related
I have a somewhat related issue!
I have finally moved our church website over to Drupal from another CMS! However, the page load times are much longer. When I check the
Home » Administer » Logs » Status report >> SQL status, I see the following Query Performance:
Select_full_join 14 The number of joins without an index; should be zero.
Select_range_check 0 The number of joins without an index; should be zero.
Sort_scan 11803 The number of sorts done without using an index; should be zero.Each time I refresh the page the Sort_scan number increases by 3. I'm concerned because there are two counters that should be zero but are not.
Also, each time I refresh the SQL status page, the number of SELECT-statements value increases by something like 54!
Can anyone guide us towards how to optimize the MySQL database access? I too am using the "normal caching", which I understand only works for anonymous users.
Thanks!
July 6, 2007 - 8:58am
I'm not your guy...
I'm not a guy to be commenting on MySQL stuff, really, since I'm no programmer...but just a couple thoughts from my experience since MF is gone for the week:
Just in terms of general speed, make sure you enable css caching as well. That will give you a big display-speed boost in addition to the page caching.
With database queries, you always gotta ask yourself which modules you have installed. If you find your queries are out of control, try disabling one module at a time and see if any one of them is making more queries than it should.
A great tool that MF uses to analyze problems like this is the devel module. It allows you to see each and every query that's being made when a page loads. Definitely check that out, it will help you hunt down your issues.
-Rob Feature
Geeks and God Co-Host
www.bobchristenson.com
July 9, 2007 - 3:41pm
eAccelerator and MySQL query cache
I have recently upgraded my web servers to Apache2, MySQL5 and PHP5 since I have more time in the summer with my job (education related).
I found this article discussing the performance benefits of a couple of PHP chaching systems. I now run eAccelerator on our Apple OS X servers. I can't tell an improvement since this is in test and on my dev laptop, but based on the article it should provide a benefit.
Also, make sure your MySQL query cache site is setup in your my.cnf files. Some installs do no have this settings configured, but the latest MySQL 5 binary install for OS X did. You can learn more about these mysql settings here.
There are many things you can do to improve your sever and Drupal performance. Check out some of the great info on this post.
Most of the tips require access to the system at a level you do not have in a shared hosting environment. Of course, if your site is having severe performance issues, you may want to move to a VPS or dedicated server.
Thanks,
Shrop
July 10, 2007 - 10:51pm
A couple caching thoughts
Here is a start with drupals caching. First, regular and aggressive caching only work for anonymous users. For anonymous users caching takes drupal down to 1 sql query. Logged in users will not share this benefit. So, realize the difference.
Now, the Content module (a.k.a. CCK) does work with aggressive caching even though it says it doesn't. See http://drupal.org/node/108688 for details.
Aggressive caching won't buy you any more advances on the SQL side.
Do you want caching info beyond drupal? On the server side?
Matt Farina
Geeks and God Co-Host
www.mattfarina.com
I've started using "normal caching" which is actually pretty nice but only seems to take me down from 70 queries to 64 queries. :(
Is there a way to minimize this even more? Drupal tells me the module "Content" is not compatible with Aggressive Caching... does that mean Content Templates or is it really saying that any content on a drupal site does not support aggressive caching?
My host is going through some MySQL issues right now, and I wouldn't mind disabling comments and forums until they sort it out if that meant I could have Drupal bypass the database altogether unless I manually went to the admin panel.
Any tips?
Eek! I just noticed that because I make extensive use of custom fields my "archives" page executes over 200 queries without cache and 189 queries with cache!
For data that will only change once a week, that kind of scares me.
The Geekons Christian Technology Podcast (not as good as G&G, but whatever)