I have a D7 site with 3 800 000 nodes. Did a small module to optimize the DB queries which solved my DB performance issues. Now I face another challenge which is Post-execution time too big. I turned off all modules and there are no errors reported in the log. The case is that I have a block view with several attachments to it. The first attachment takes too long during Post-execution phase. It has nothing to do with the attachment itself. If I rearrange the attachments the new attachment happened to be first has the same issue while the attachment that used to be first works fine. I have a pager displaying 8600 items per page inherited by all attachments to this block. The first attachment has DB 0.35 s and Post-execution 91.57 s, while the rest of the attachments have same DB time but Post-execution 2.37 s.
Comments
xdebug
xdebug cachegrind will tell you what's eating up all that time.
It's the
It's the DrupalDefaultEntityController::load
The devel module states slow query this method uses. I optimized the query to avoid the JOIN between the node and node_revision tables. Same query executes fast enough in MySQL console. Something I'm not sure about is how the trick with substituted values the WHERE clause work. I.e query with WHERE (base.nid IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2 ... etc. Does it take additional time to convert these db_condition_placeholder_XX to actual values?
Function in question
https://api.drupal.org/api/drupal/includes%21entity.inc/function/DrupalD...
So the DB query is the slowest part of according to what you measured. The substitution is fast, that's not the issue. Exactly what part of DrupalDefaultEntityController::load is slow?
According to the devel module
According to the devel module it is the DrupalDefaultEntityController::load query:
SELECT revision.vid AS vid, base.uid AS uid, revision.title AS title, revision.log AS log, revision.status AS status, revision.comment AS comment, revision.promote AS promote, revision.sticky AS sticky, base.nid AS nid, base.type AS type, base.language AS language, base.created AS created, base.changed AS changed, base.tnid AS tnid, base.translate AS translate, revision.timestamp AS revision_timestamp, revision.uid AS revision_uid FROM node base INNER JOIN node_revision revision ON revision.vid = base.vid WHERE (base.nid IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4,
...
...
db_condition_placeholder_8639
And it is not the INNER JOIN as I wrote a module that alters this query eliminating the node_revision table and taking all data from the node table. This leads to no significant speed gain.
The Drupal query takes 90 seconds. Same query but with values instead of :db_condition_placeholder_4 executed from MySQL console takes 6 seconds.
db_condition_placeholder_8639
You have over 8k conditions in your WHERE statement. That's the issue.
I altered the WHERE clause to
I altered the WHERE clause to ((base.nid > :db_condition_placeholder_0) AND (base.nid < db_condition_placeholder_8639)) - 2 conditions instead of 8640 conditions. It's still the same.
8639 conditions is bad
8640 vs 8639. This needs to be dropped down to about 10 or so. You should look into paying someone (not me) to fix this for you.
Why do you need to show 8400
Why do you need to show 8400 entries per page?
You are doing node load multiple with 8400 id‘s..
You could try using memcache or redis cache, which should speed up the node loads when they are in cache.. but still the initial load will be that slow..
Everything is fixed now. It
Everything is fixed now. It was the way Drupal builds its cache. All entity fields are stored in cache_fields table. Even if you have requested only one field Drupal builds and stores the entire entity. I made a script to build this table manually and the problem was resolved. Now all fields are loaded form the cache_fields bypassing the sql bottleneck.
Better go and replace the
Better go and replace the database cache with redis or memcache instead of doing what you described.. this is a huge violation what you are doing and the next developer will have hard times when he needs to debug something caused by that changes
There is actually no
There is actually no violation. The same thing Drupal would do with time. I just forced the things a little bit by creating a node view having 8640 items per page and downloaded all the 476 pages using wget. The SQL changes were made using a module I created. I turned off the module so Drupal now uses its original queries. I'm very happy with the outcome. I gained 93 seconds per page. Along with another module I made, addressing the unneeded queries when view attachment uses contextual filters the overall performance gain is over 2 minutes per page on Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz.