Need to improve page execution time

Posted by Flying Drupalist on April 17, 2009 at 11:53pm

I'm using Amazon EC2 small instance with EBS, which according to Amazon is:

Small Instance (default)*

1.7 GB memory
1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit)
160 GB instance storage (150 GB plus 10 GB root partition)
32-bit platform
I/O Performance: Moderate

Software wise:
Web server Apache/2.2.8 (Ubuntu) PHP/5.2.4-2ubuntu5.5 with Suhosin-Patch mod_perl/2.0.3 Perl/v5.8.8

MySQL database 5.0.51a

I've installed APC and cache router, but wasn't really able to tell if it's working:
http://drupal.org/node/434950
http://drupal.org/node/436236

I've enabled normal page caching on performance.

Here are some random sampling of devel's performance logging:

Home page: Page execution time was 11464.9 ms. Executed 2218 queries in 864.8 milliseconds.
Video node: Page execution time was 4083.13 ms. Executed 471 queries in 69.54 milliseconds.
Some node: Page execution time was 7954.56 ms. Executed 438 queries in 272.49 milliseconds.
Book node: Page execution time was 7806.4 ms. Executed 451 queries in 147.17 milliseconds.
Image node: Page execution time was 4078.98 ms. Executed 420 queries in 189.44 milliseconds.

Basically performance is atrocious.

I must be doing something really really wrong, as this is a dev site and has no traffic. What's wrong and how do I fix this? Thanks!

Comments

I also add that these are

Posted by Flying Drupalist on April 17, 2009 at 11:54pm

I also add that these are using the default theme garland. My theme gets similar scores.

With that number of queries,

Posted by Jamie Holly on April 18, 2009 at 12:35am

With that number of queries, I would say that cacherouter isn't working. If you disable that (remove the cache inc in settings.php), what kind of times do you get?

HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

I swear I replied yesterday,

Posted by Flying Drupalist on April 18, 2009 at 4:59pm

I swear I replied yesterday, but g.d.o ate my post.

Here are the results from which settings set:
Executed 1512 queries in 667.46 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 3901.92 ms.
Executed 1512 queries in 533.31 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 3745.59 ms.
Executed 1513 queries in 742.52 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 3888.04 ms.

And settings not set:
Executed 1747 queries in 861.28 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 3945.06 ms.
Executed 1748 queries in 921.89 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 3866.73 ms.
Executed 1747 queries in 768.85 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 4149.45 ms.

So it did make some difference. But, this is hardly the 50% results I normally find people talk about.
My goal is to get the post-cache loading under a second. Is this possible? Why is there such a huge discrepancy between the query execution and the page execution. Is this normal? I thought with APC caching php execution php should take no time at all. What's going on? :(

The home page has waaay to

Posted by Coornail on April 18, 2009 at 10:51am

The home page has waaay too much queries. What if you install Devel module for a few seconds, and find out what are those queries?

Is this sufficient

Posted by Flying Drupalist on April 18, 2009 at 4:46pm

Is this sufficient information? Or do you want to see the lower parts of the chart as well? These are the top entries when sorting by load time.

http://img17.imageshack.us/img17/2493/develload.jpg

Looking through the bottom of the chart, I see a lot of facebook_status_get_status, user_load, node_load, activity comments load, custom_filter_get_rules, and _voting_api_select.

I uninstalled nodewords and user karma and the numbers are a little lower:
Executed 1622 queries in 778.47 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 8520.28 ms.

You still got a ton of

Posted by Jamie Holly on April 19, 2009 at 1:13am

You still got a ton of queries, and I don't see anything on your homepage that needs that many. For example - it is calling user_load 191 times, but I don't see 191 places where that would be used.

What I would do is keep devel outputting the queries, then start dropping blocks. See if any one block removes a lot of queries.

For the queries- your times are rather high. I would check to make sure you have all your indexes in the database. If you have phpmyadmin installed, check the overhead of the tables also (without phpmyadmin do SHOW table_status FROM {databasename} - look at the data free column - if that's high then you have a problem). If that's high then do an optimize tables on all your tables.

HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

Thanks for the great advice,

Posted by Flying Drupalist on April 19, 2009 at 4:14am

Thanks for the great advice, the activity block looks like it's dastardly expensive. 1000 queries. Facebook status module is also expensive.

Just turning the activity block off: Executed 851 queries in 399.13 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1909.39 ms.

Wow, big difference. I'll be using block_cache_alter on it, as well as trying other different things, such as loading it in ajax.

Turning off facebook: Executed 688 queries in 151.73 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1701.51 ms.

Significant difference! Luckily facebook status has a new version which should increase performance, will be updating to that ASAP.

My primary menu sadly, is also expensive, turning it off:
Executed 468 queries in 217.13 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1354.28 ms.

I'm not sure there's much I could do about that, though I have more menu items than the typical user since they never seen the admin menu.

When you say the times are high for the queries, do you mean the query execution or the page execution?

"I would check to make sure you have all your indexes in the database."
I'm not sure how to do this, http://pastebin.ca/1396230 http://pastebin.ca/1396231 Is that sufficient? Do I have to run a check against every table?

I also optimized my tables. It helped a bit.
Executed 685 queries in 169.96 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1611.58 ms.

This is with menus on, so compare that to this:
Executed 688 queries in 151.73 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1701.51 ms.

I'm really wondering though why there's such a huge difference between the page execution time and the queries time. The posts I read of other people tuning seem to have them very close to each other. Isn't that my most major problem? To shorten the difference?

APC standard config is

Posted by bennos on April 18, 2009 at 12:19pm

APC standard config is bad.

try following.

Optimize the mysql server. if you are not prof. use something like tuningprimer. http://forge.mysql.com/projects/project.php?id=44
Optimize the APC config.

More info you can find on Khalid HP, some benchmarks and config.

http://2bits.com/articles/importance-tuning-apc-sites-high-number-drupal...

My site is still in dev, I

Posted by Flying Drupalist on April 18, 2009 at 4:35pm

My site is still in dev, I don't know if tuningprimer would really help me.

Here are my settings:
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
max_connections = 100

table_cache = 64

thread_concurrency = 10

* Query Cache Configuration

#
query_cache_type = 1
query_cache_limit = 8M
query_cache_size = 64M

Is APC config just turning up the memory?

Here's apc.php:
http://test2.geassdai.com/apc.php

After running tuningprimer I

Posted by Flying Drupalist on April 18, 2009 at 5:41pm

NVM, fixed it.

While trying to run a

Posted by Flying Drupalist on April 18, 2009 at 3:58pm

While trying to run a backup, I get: Fatal error: Allowed memory size of 209715200 bytes exhausted (tried to allocate 16 bytes) in /ebs1/www/includes/database.mysqli.inc on line 115

Which makes me think that the server is somehow horribly misconfigured.

that's 200MB

Posted by greggles on April 18, 2009 at 4:22pm

That means you've got 200MB as the memory_limit in your php.ini which is a relatively high number, but nothing to be concerned about.

My question is: Why are you trying to do a backup via Drupal? You should probably be using mysqldump directly or some other admin tool instead.

--
_{Growing Venture Solutions | Drupal Dashboard | Learn more about Drupal - buy a Drupal Book}

knaddison blog | Morris Animal Foundation

What concerns me is that the

Posted by Flying Drupalist on April 18, 2009 at 4:39pm

What concerns me is that the same backup works on my old (vastly inferior) server at 128MB limit. How can you tell if you completely screwed up the configurations?

Though of course I'll use something else to backup.

Correcting your my.cnf

Posted by ronaldbradford on April 18, 2009 at 5:36pm

Your my.cnf file must contain a group identifier.

e.g.

[mysqld]
key_buffer_size= 32M

etc.

FYI: key_buffer is a redundant option and can be removed.

How big is your database (specifically indexes)? 32M is a very small value for this buffer.

Thanks for that, I fixed my

Posted by Flying Drupalist on April 19, 2009 at 1:17am

Thanks for that, I fixed my my.cnf

How do I check the size of my indexes?

I took the 32M from 2bits' example.

Sizing your database

Posted by ronaldbradford on April 19, 2009 at 4:02am

The following query will provide an overall summary.

Show A summary of all schemas

select table_schema,
sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb,
sum(index_length)/1024/1024 as index_mb,
count(*) as tables
from information_schema.tables
group by table_schema
order by 2 desc;

You should also then review the Drupal database schema in question (I assume you are not using MySQL for multiple purposes)

use dbname;

select table_name,engine,row_format, table_rows, avg_row_length,
(data_length+index_length)/1024/1024 as total_mb,
(data_length)/1024/1024 as data_mb,
(index_length)/1024/1024 as index_mb
from information_schema.tables
where table_schema=DATABASE()
order by 6 desc
limit 30;

Assuming you are using MyISAM, then your key_buffer_size should support at least the size of indexed used by the system (should you have sufficient RAM). If your tables are Innodb, then different settings would be necessary.

Your next step is to get APC

Posted by meba on April 19, 2009 at 7:43am

Your next step is to get APC or xCache running. That will significantly lower the PHP time part.

I think it is on. I ran some

Posted by Flying Drupalist on April 20, 2009 at 2:13am

I think it is on. I ran some tests using ab.

Off:
http://pastebin.ca/1396941

On:
http://pastebin.ca/1396944

These numbers are amazing, only 10% loading time?

But in real terms, it shaves off only 1 second from 3 second page loads. I need to get it down more.

I'm amazed at these numbers:
http://nadeausoftware.com/articles/2007/01/essential_performance_tuning_...

Wow 82 ms loading time...

What about memcache? Should

Posted by Flying Drupalist on April 20, 2009 at 2:19am

What about memcache? Should I try installing that?

Yes

Posted by joshk on April 21, 2009 at 10:52pm

Yes you should, especially if you want to run "in the cloud."

Basically, if you plan to deploy on a cloud (ec2 or otherwise), any time you hit the disk (e.g. any mysql query), you face peril. Cloud I/O sucks, and can be inconsistent.

Memcache prevents you from pulling cache data out of mysql (off disk) and can also improve performance by relieving PHP/Drupal of the task of serializing/unserializing structured cache objects. This can significantly cut execution times on complex installs w/lots of views/cck/etc.

There are patches for the advcache module, which can further cut reliance on mysql in favor of memcached objects (e.g. a persistant node and user cache). It's not marked for release yet, but if you're comfortable with patching, you should look into that as well. This kind of architecture -- data-object level caching for nodes, users, etc -- is pretty critical for any cloud-based deployment to really perform well IMHO.

http://www.chapterthree.com | http://www.outlandishjosh.com

https://pantheon.io | http://www.chapterthree.com | https://www.outlandishjosh.com

How can reduce the query?

Posted by shyamprakash on November 4, 2011 at 12:22pm

I have install devel module i found below excuted query for this page http://sourcing.indiamart.com/apparel/

Executed 371 queries in 137.78 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 738.31 ms.

My concern is how can we reduce query from 371 to 10-20. Is it possible in Drupal?