Recent Topics

v 6.6.7 Performance bottlenecks: posts list would be shown slowly (When there are many users and blogs)

Started by on Feb 04, 2016 – Contents updated: Feb 05, 2016

Feb 04, 2016 07:06    

It's based on the topic : http://forums.b2evolution.net/when-use-for-large-organization-some-features-may
For testing , I created 5000 users; each user gets one own blog; in one blog(blogid=1) there are 10000 posts.

When display the postlist, two sql statement will be excuted very slowly(for they are too long to transmite , maybe), as follows:

SELECT COUNT( DISTINCT post_ID )
  FROM evo_items__item
 INNER JOIN evo_postcats ON post_ID = postcat_post_ID
 INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
 WHERE ( 1 )
   AND (( post_ityp_ID IS NULL
    OR post_ityp_ID NOT IN (1000,1400,1500,1520,1530,1570,1600,3000,4000) ))
   AND (post_datestart <= '2016-02-04 14:01')
   AND (( cat_blog_ID=1
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) )
    OR ( cat_blog_ID=2
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) )
    OR ( cat_blog_ID=3
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) )

   ...................... // from 1 to 5000, most part of numbers omit 

    OR ( cat_blog_ID=5000
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) ))

SELECT DISTINCT post_ID
  FROM evo_items__item
 INNER JOIN evo_postcats ON post_ID = postcat_post_ID
 INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
 WHERE ( 1 )
   AND (( post_ityp_ID IS NULL
    OR post_ityp_ID NOT IN (1000,1400,1500,1520,1530,1570,1600,3000,4000) ))
   AND (post_datestart <= '2016-02-04 14:01')
   AND (( cat_blog_ID=1
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) )
    OR ( cat_blog_ID=2
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) )

...................... // from 1 to 5000, most part of numbers omit 

    OR ( cat_blog_ID=5000
   AND ( ( ( post_status = 'private'
   AND post_creator_user_ID = 1 )
    OR post_status IN ( 'published','community','protected','review' ) ) ) ))
 ORDER BY post_datestart DESC, post_ID DESC
 LIMIT 5

Feb 05, 2016 00:52

We will look into it.

A short term solution would be to not use a collection that aggregates all the other collections (which is the case for demo collection #1)

Feb 05, 2016 02:44

@fplanque wrote earlier:

We will look into it.

A short term solution would be to not use a collection that aggregates all the other collections (which is the case for demo collection #1)

OK, I will not use it for a while.


Form is loading...

powered by b2evolution free blog software – This forum is powered by b2evolution CMS, a complete engine for your website.