One of the forums opens much longer than others

[Vladimir Limonov]

Hi guys,

I'm curious why one of the forums on my website takes much longer to open it than the others?

Here it is -- http://forum.astrakhan.ru/forum/2-internet-интеллигенция/

Basically, it usually opens in 3 to 10 seconds while other forums take 1 to 3 seconds.

Does anyone have an idea of why it happens?

Thanks in advance!

[jair101]

The speed looks the same to me, it is fairly quick for all forums. 

[Vladimir Limonov]

12 minutes ago, jair101 said:

The speed looks the same to me, it is fairly quick for all forums. 

I forgot to mention that it happens when you logged in and didn't read any forum for a while.

Few users confirmed such behavior. Any ideas on how to figure out the reason?

[jair101]

36 minutes ago, Vladimir Limonov said:

I forgot to mention that it happens when you logged in and didn't read any forum for a while.

Few users confirmed such behavior. Any ideas on how to figure out the reason?

Which plugins do you have installed? Any speicifc ad that is running in this forum and is it by any chance visible only to logged in member?

[Vladimir Limonov]

52 minutes ago, jair101 said:

Which plugins do you have installed? Any speicifc ad that is running in this forum and is it by any chance visible only to logged in member?

None of above. I tried to opened it now, and it took me 18 seconds.

The only thing about this forum is that it contains the longest threads on the website.

[Makoto]

Are you running the latest release of IPS?

If so, have you opened a support ticket so IPS can investigate the issue for you?

[Vladimir Limonov]

17 minutes ago, Makoto said:

Are you running the latest release of IPS?

If so, have you opened a support ticket so IPS can investigate the issue for you?

It's not the latest one but when I asked them about it one year ago, they proposed me something which didn't help at all. Moreover, once they tried to open the website it performed normally so I they weren't been able to reproduce the problem in order to fix it.

My colleague suggests migrating to MYISAM back from INNODB since this virtual machine lacks on RAM but I'm not sure this can solve the issue.

[Adlago]

Any of your forums that you posted recently - probably you / or who is a user ID 1 / is loading slower because you have a very heavy avatar - 275 Kb gif.

[Makoto]

No, a 275kb avatar would not produce 18 second load times.

[Vladimir Limonov]

 

1 hour ago, Adlago said:

Any of your forums that you posted recently - probably you / or who is a user ID 1 / is loading slower because you have a very heavy avatar - 275 Kb gif.

It's not about content loading but rather server response. For some reason it takes many seconds to generate the first response before the page even starts rendering.

I believe something is wrong with SQL queries. Somehow it affects this particular page.

[Makoto]

It's hard to say what the specific issue is here without taking a look at it personally.

I do provide server optimization services if that is something you would be interested in @Vladimir Limonov.

I could take a quick peak at your server configuration and provide a free consultation on what might be causing your performance issues as well. Just send me a PM if that's something you think you'd be interested in!

[Adlago]

 

4 minutes ago, Vladimir Limonov said:

 

It's not about content loading but rather server response. For some reason it takes many seconds to generate the first response before the page even starts rendering.

I believe something is wrong with SQL queries. Somehow it affects this particular page. 

A forum from your link works well for me.
Here's a test - 3.8 s fuly loaded

TTFB is only 240 ms

[Makoto]

3 hours ago, Vladimir Limonov said:

I forgot to mention that it happens when you logged in and didn't read any forum for a while.

I'm sure your willingness to help is appreciated @Adlago, but this isn't a client-side performance issue so the tools you are using aren't going to help identify the problem here.

[Vladimir Limonov]

Guys, I've tried to use mysqltuner.pl in order to find out what's going on with MySQL server, and it recommends me to optimize 'ibf_core_log' table since it contains 1.3 Gb of data which can be optimized. But when I run this it says --

MariaDB [forum_2]> optimize table ibf_core_log;
+----------------------+----------+----------+-------------------------------------------------------------------+
| Table                | Op       | Msg_type | Msg_text                                                          |
+----------------------+----------+----------+-------------------------------------------------------------------+
| forum_2.ibf_core_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| forum_2.ibf_core_log | optimize | error    | The table 'ibf_core_log' is full                                  |
| forum_2.ibf_core_log | optimize | status   | Operation failed                                                  |
+----------------------+----------+----------+-------------------------------------------------------------------+
 

Is it possible to just truncate this table?

Moreover, I noticed that some of the tables are MyISAM while others are InnoDB. Is it okay?

[Jim M]

If there are a lot of recent errors in your core_log table you should investigate those. As yes, you can truncate the table to make it smaller but if those errors still exist, it will just fill right back up again. 

[Vladimir Limonov]

I examined mysql_slow.log and it's full of the following queries --

/*IPS\Content\_Comment::getItemsWithPermission:209*/ SELECT COUNT(*) as cnt FROM `ibf_forums_posts` AS `forums_posts`  STRAIGHT_JOIN `
ibf_forums_topics` AS `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND forums_topics.approved=1  STRAIGHT_JOIN `ibf_core
_permission_index` AS `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND co
re_permission_index.perm_type_id=forums_topics.forum_id AND (( FIND_IN_SET(2,perm_2) ) OR perm_2='*' )  LEFT JOIN `ibf_forums_forums`
AS `forums_forums` ON forums_topics.forum_id=forums_forums.id AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(2,forums_forums.pa
ssword_override) ) ) AND forums_forums.min_posts_view<=0 WHERE forums_posts.author_id=1259 AND ( forums_forums.can_view_others=1 OR fo
rums_topics.starter_id=0 ) AND queued=0 AND forums_posts.queued!=-2;

As you may notice, it tries to make a query for forum_id=2 which is exactly the forum I have the problems with.

The only thing which differs in those queries is forum_posts.author_id=VALUE. Those queries take up to 10 seconds.

 

# Query_time: 9.441513  Lock_time: 0.000096  Rows_sent: 1  Rows_examined: 71323

# Query_time: 11.440145  Lock_time: 0.000053  Rows_sent: 1  Rows_examined: 76173

# Query_time: 11.765414  Lock_time: 0.000073  Rows_sent: 1  Rows_examined: 8788

...etc.

[bfarber]

I would hazard a guess that you have a sidebar block (in the sidebar, or the header/footer drag'n'drop zones) that is causing the slowness. The block may not show to guests, or it may simply be getting cached more effectively for guests than for logged in users, or users in specific groups (such as administrators).

[Vladimir Limonov]

5 hours ago, bfarber said:

I would hazard a guess that you have a sidebar block (in the sidebar, or the header/footer drag'n'drop zones) that is causing the slowness. The block may not show to guests, or it may simply be getting cached more effectively for guests than for logged in users, or users in specific groups (such as administrators).

Hi, thanks for the guess but I have sidebar disabled anywhere except the main page so this is not the issue here.

This forum opens pretty fast for non-logged in users but freezes for a while for any other users (not only administrators).

EXPLAIN EXTENDED didn't give me any hint on why it happens. Lock time is pretty small as well.

[bfarber]

WHERE forums_posts.author_id=1259 AND ( forums_forums.can_view_others=1 OR forums_topics.starter_id=0 ) AND queued=0 AND forums_posts.queued!=-2

This is a pretty strange query. It's looking for posts that are approved and written by author id 1259 specifically. Do you have any third party plugins or applications that may be doing this? Or are you following a special link, such as a filter link that only shows you topics you've posted in?

[Vladimir Limonov]

On 12/27/2018 at 9:44 PM, bfarber said:

WHERE forums_posts.author_id=1259 AND ( forums_forums.can_view_others=1 OR forums_topics.starter_id=0 ) AND queued=0 AND forums_posts.queued!=-2

This is a pretty strange query. It's looking for posts that are approved and written by author id 1259 specifically. Do you have any third party plugins or applications that may be doing this? Or are you following a special link, such as a filter link that only shows you topics you've posted in?

Hi, nope, I've never used any filters. All I do is open this forum link being logged in. But as you can see, here is what triggers this query --

/*IPS\Content\_Comment::getItemsWithPermission:209*/

When I run this query, it takes a lot of time (5-10 seconds). On the second run it performs in less than a second thanks to the cache.

[Makoto]

It does sound like you may have some performance related issues with your database server.

Maybe your database server is running out of memory and resorting to swap or something similar?

Or if you're on a VPS with too little memory, it can be easy to hit heavy performance bottlenecks in scenarios like this.

Could you post the full output of mysqltuner.pl here?

[Vladimir Limonov]

On 12/29/2018 at 2:05 PM, Makoto said:

It does sound like you may have some performance related issues with your database server.

Maybe your database server is running out of memory and resorting to swap or something similar?

Or if you're on a VPS with too little memory, it can be easy to hit heavy performance bottlenecks in scenarios like this.

Could you post the full output of mysqltuner.pl here?

Hi, here it is --

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Temporary table size is already large - reduce result set size
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (1749) variable should be greater than table_open_cache (784)
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    table_open_cache (> 784)
    innodb_buffer_pool_size (>= 7.2G) if possible.
    innodb_log_file_size should be (=384M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=3)

Currently my virtual server has 8GB RAM and 4-core CPU.

Here is my.cnf --

[mysqld]
datadir     = /var/lib/mysql
socket      = /var/lib/mysql/mysql.sock

default-storage-engine = myisam
symbolic-links      = 0
skip-name-resolve   = 1

innodb_buffer_pool_instances    = 4
innodb_file_per_table           = 1

innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 3G

thread_concurrency  = 8
query_cache_type    = 0
query_cache_size    = 0
thread_cache_size   = 8
table_open_cache    = 784
max_allowed_packet  = 16M
key_buffer_size     = 32M

myisam_sort_buffer_size = 64M
read_rnd_buffer_size    = 8M
read_buffer_size        = 2M
sort_buffer_size        = 2M

tmp_table_size      = 256M
max_heap_table_size = 256M
max_connections     = 150
max_connect_errors  = 10240
wait_timeout        = 15
interactive_timeout = 200

performance_schema = ON

Total database size is 8.4GB of which almost ~2GB belongs to `ibf_forums_archive_posts` table.

I also mentioned that most of the tables are InnoDB while some are MyISAM (ibf_core_message_posts, ibf_core_search_index, ibf_core_reputation_index).

[bfarber]

If you haven't done so already you're free to submit a ticket so technical support can take a look and make sure nothing is "off". 

[Vladimir Limonov]

21 hours ago, bfarber said:

If you haven't done so already you're free to submit a ticket so technical support can take a look and make sure nothing is "off". 

As you can see, I have everything enabled. Moreover, it's not the only website I'm currently running on my servers so I'm pretty used for MySQL tuning and optimizations. The reason I didn't change my current settings is that I tried many of them without any impact on my current issue.

[bfarber]

My recommendation to submit a ticket for technical support still stands....I don't know what else to recommend from here. Obviously one individual forum should not take 10 seconds to open.