Doctrine queries take over 4 seconds to execute

Question

We're in the last stage of a project and started optimizing it.

After a few tests, we realized that most (if not all) of the time spent loading a page, is spent in Doctrine.

Out of 5 seconds page load, 3 - 4 seconds are spent only on 2 Doctrine queries.

We've enabled query cache on the mysql server and on Doctrine as well as result cache, but this only seems to make the load times worse.

As far as I can gather, the mysql query cache is active:

[edit] for some reason the rest of my question has vanished so I am posting it again[/edit]

Qcache_free_blocks | 57
Qcache_free_memory | 22300072
Qcache_hits | 7117
Qcache_inserts | 3308
Qcache_lowmem_prunes | 0
Qcache_not_cached | 4537
Qcache_queries_in_cache | 1225
Qcache_total_blocks | 2609

The problem is that the cache doesn't seem to be hit, the query that takes most of the time (3 seconds on average) never changes.

Does anyone have any tips on why enabling query / result caching wouldn't improve load times?

Answer 1

Hi,

did you try the query from the console/command line? would be interesting, what time they need, when executed without ORM.

Please let me know, I am interested in using Doctrine, but little afraid of performance.

Answer 2

If I got it right, you don't have the list of the slowest queries at the moment. I recommend to start from this.

Turn on slow query logging:

log_slow_queries=/var/log/mysql.slow.log

And then:

Constrain relationships as much as possible

It is important to constrain relationships as much as possible. This means:

• Impose a traversal direction (avoid bidirectional associations if possible)
• Eliminate nonessential associations

This has several benefits:

• Reduced coupling in your domain model
• Simpler code in your domain model (no need to maintain bidirectionality properly)
• Less work for Doctrine

Stolen from Doctrine best practices