Solution/ideas regarding bad performances in IPS 4.4

[Infoclimat]

Hello,

We recently migrated a medium-size community (> 3M messages) to IPS 4.4.

However, we started to have huge load spikes on the database server, leading to huge queue of queries being processed, and awful loading times by the user-side. By analyzing the mysql queries, we found something interesting, much of slow queries were like:

# Time: 190302 20:54:50
# User@Host: mysql[mysql] @  [127.0.0.1]
# Query_time: 8.030073  Lock_time: 0.000043 Rows_sent: 25  Rows_examined: 2303585
SET timestamp=1551556490;
/*IPS\Content\_Item::_comments:1861*/ SELECT pid FROM `forums_posts` WHERE pid IN(SELECT forums_posts.pid FROM `forums_posts` WHERE topic_id=25329 AND (queued IN(0,2)) ORDER BY post_date asc) LIMIT 6125,25;

This SQL query did not make sense to me, the dependent subquery being unnecessary in that particular case. By digging into IPS source code, we found in system/Content/Item.php a method "_comments()", in which, at some point, do something special for topics with more than 500 posts. That was the case for the queries which were problematic.

<?php /* extract from system/Content/Item.php */

/* Large topics, private messages, etc. benefit greatly from splitting the query into two queries */
elseif ( $this->mapped('num_comments') >= 500 )
{
	/* Its more efficient to just get the primary ids first without joins and then we can fetch all the data on the second query once the offset gets larger */
	if ( $offset > 5000 )
	{
		/* Avoid a file sort */

By disabling that special optimization (commenting out that part of the code), there is no more load spikes on the database server when browsing large topics.

It seems that the optimization stated here works only in some cases. We are running mysql 5.5, and the forums_posts table is MyISAM. sort_buffer_size=8Mo, read_rnd_buffer_size=256Ko

Any tips on this? Maybe that optimization only works with innodb, or on more recent mysql versions ?

Thanks for your ideas. By the way, performance in IPS 4.4 was greatly improved.

F.A.

[Nathan Explosion]

14 minutes ago, Infoclimat said:

We are running mysql 5.5

Extend that version number a little - 5.5.?

Would recommend you raise a ticket to support to get this looked into aswell; we're peers, not the developers, so we can only provide a guess on what the optimisation may require.

 

[sound]

have just seen that the  ip prune query task appears to be causing slow mysql queries of 6+ seconds

when the task is run it is apparently  updating every forums_posts every time that is within the date conditional,

as in even those that have already been pruned rather than just those that just need it

have put a ticket in but if the feature is switched on you may want to switch it off

[bfarber]

On 3/3/2019 at 6:49 AM, Infoclimat said:

Hello,

We recently migrated a medium-size community (> 3M messages) to IPS 4.4.

However, we started to have huge load spikes on the database server, leading to huge queue of queries being processed, and awful loading times by the user-side. By analyzing the mysql queries, we found something interesting, much of slow queries were like:

# Time: 190302 20:54:50
# User@Host: mysql[mysql] @  [127.0.0.1]
# Query_time: 8.030073  Lock_time: 0.000043 Rows_sent: 25  Rows_examined: 2303585
SET timestamp=1551556490;
/*IPS\Content\_Item::_comments:1861*/ SELECT pid FROM `forums_posts` WHERE pid IN(SELECT forums_posts.pid FROM `forums_posts` WHERE topic_id=25329 AND (queued IN(0,2)) ORDER BY post_date asc) LIMIT 6125,25;

This SQL query did not make sense to me, the dependent subquery being unnecessary in that particular case. By digging into IPS source code, we found in system/Content/Item.php a method "_comments()", in which, at some point, do something special for topics with more than 500 posts. That was the case for the queries which were problematic.

<?php /* extract from system/Content/Item.php */

/* Large topics, private messages, etc. benefit greatly from splitting the query into two queries */
elseif ( $this->mapped('num_comments') >= 500 )
{
	/* Its more efficient to just get the primary ids first without joins and then we can fetch all the data on the second query once the offset gets larger */
	if ( $offset > 5000 )
	{
		/* Avoid a file sort */

By disabling that special optimization (commenting out that part of the code), there is no more load spikes on the database server when browsing large topics.

It seems that the optimization stated here works only in some cases. We are running mysql 5.5, and the forums_posts table is MyISAM. sort_buffer_size=8Mo, read_rnd_buffer_size=256Ko

Any tips on this? Maybe that optimization only works with innodb, or on more recent mysql versions ?

Thanks for your ideas. By the way, performance in IPS 4.4 was greatly improved.

F.A.

This code has changed for our upcoming 4.4.1 maintenance release already.