Vladimir Limonov Posted December 23, 2018 Share Posted December 23, 2018 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! Link to comment Share on other sites More sharing options...
jair101 Posted December 23, 2018 Share Posted December 23, 2018 The speed looks the same to me, it is fairly quick for all forums. Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 23, 2018 Author Share Posted December 23, 2018 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? Link to comment Share on other sites More sharing options...
jair101 Posted December 23, 2018 Share Posted December 23, 2018 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? Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 23, 2018 Author Share Posted December 23, 2018 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. Link to comment Share on other sites More sharing options...
Makoto Posted December 23, 2018 Share Posted December 23, 2018 Are you running the latest release of IPS? If so, have you opened a support ticket so IPS can investigate the issue for you? Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 23, 2018 Author Share Posted December 23, 2018 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. Link to comment Share on other sites More sharing options...
Adlago Posted December 23, 2018 Share Posted December 23, 2018 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. Link to comment Share on other sites More sharing options...
Makoto Posted December 23, 2018 Share Posted December 23, 2018 No, a 275kb avatar would not produce 18 second load times. Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 23, 2018 Author Share Posted December 23, 2018 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. Link to comment Share on other sites More sharing options...
Makoto Posted December 23, 2018 Share Posted December 23, 2018 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! Link to comment Share on other sites More sharing options...
Adlago Posted December 23, 2018 Share Posted December 23, 2018 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 Link to comment Share on other sites More sharing options...
Makoto Posted December 23, 2018 Share Posted December 23, 2018 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. Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 25, 2018 Author Share Posted December 25, 2018 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? Link to comment Share on other sites More sharing options...
Jim M Posted December 25, 2018 Share Posted December 25, 2018 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. Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 25, 2018 Author Share Posted December 25, 2018 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. Link to comment Share on other sites More sharing options...
bfarber Posted December 26, 2018 Share Posted December 26, 2018 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). Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 27, 2018 Author Share Posted December 27, 2018 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. Link to comment Share on other sites More sharing options...
bfarber Posted December 27, 2018 Share Posted December 27, 2018 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? Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 29, 2018 Author Share Posted December 29, 2018 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. Link to comment Share on other sites More sharing options...
Makoto Posted December 29, 2018 Share Posted December 29, 2018 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? Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted December 30, 2018 Author Share Posted December 30, 2018 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). Link to comment Share on other sites More sharing options...
bfarber Posted January 2, 2019 Share Posted January 2, 2019 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". Link to comment Share on other sites More sharing options...
Vladimir Limonov Posted January 3, 2019 Author Share Posted January 3, 2019 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. Link to comment Share on other sites More sharing options...
bfarber Posted January 3, 2019 Share Posted January 3, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.