SecondSight Posted August 21, 2016 Share Posted August 21, 2016 Hello ! Since I upgraded to version 4.1.14, I'm having problems with MySQL on my server. At moments, it begins using a high percentage of CPU and it doesn't stop... The only solution I found is stopping MySQL and restarting it... I would like to find out what is causing the high CPU usage. How can I monitor MySQL to achieve this ? Thank you. Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 22, 2016 Share Posted August 22, 2016 Run this query: show processlist; Link to comment Share on other sites More sharing options...
SecondSight Posted August 22, 2016 Author Share Posted August 22, 2016 Hello ! Here is what I get when I have this problem : Id User Host db Command Time State Info 56445 eximstats localhost eximstats Sleep 32 NULL 59717 forums_forums localhost forums_forums Execute 22 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 59834 forums_forums localhost forums_forums Execute 9 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 59912 forums_forums localhost forums_forums Execute 32 FULLTEXT initialization /*IPS\Content\Search\Mysql\_Query::search:540*/ SELECT main.*, ((MATCH(index_title) AGAINST ('\"prog 60377 forums_forums localhost forums_forums Execute 0 Sending data /*IPS\Node\_Model::contentPostedIn:290*/ SELECT forums_posts.topic_id FROM `ibf_forums_posts` AS `fo 60378 forums_forums localhost forums_forums Execute 0 Creating sort index /*IteratorIterator::rewind:5772*/ SELECT forums_topics.* FROM `ibf_forums_topics` AS `forums_topics` 60380 forums_forums localhost forums_forums Sleep 0 NULL 60382 root localhost NULL Query 0 init SHOW PROCESSLIST Id User Host db Command Time State Info 56445 eximstats localhost eximstats Sleep 44 NULL 59834 forums_forums localhost forums_forums Execute 21 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 59912 forums_forums localhost forums_forums Execute 6 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 60512 forums_forums localhost forums_forums Sleep 0 NULL 60529 forums_forums localhost forums_forums Sleep 0 NULL 60531 forums_forums localhost forums_forums Sleep 0 NULL 60532 forums_forums localhost forums_forums Execute 0 Creating sort index /*IteratorIterator::rewind:5772*/ SELECT forums_topics.* FROM `ibf_forums_topics` AS `forums_topics` 60535 forums_forums localhost forums_forums Sleep 0 NULL 60538 forums_forums localhost forums_forums Sleep 0 NULL 60539 forums_forums localhost forums_forums Sleep 0 NULL 60540 root localhost NULL Query 0 init SHOW PROCESSLIST Id User Host db Command Time State Info 56445 eximstats localhost eximstats Sleep 46 NULL 59834 forums_forums localhost forums_forums Execute 23 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 59912 forums_forums localhost forums_forums Execute 8 FULLTEXT initialization /*IPS\Content\Search\_Results::count:544*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WH 60552 forums_forums localhost forums_forums Sleep 1 NULL 60556 forums_forums localhost forums_forums Sleep 1 NULL 60557 forums_forums localhost forums_forums Sleep 1 NULL 60563 forums_forums localhost forums_forums Sleep 0 NULL 60564 forums_forums localhost forums_forums Prepare 0 Opening tables /*IPS\Content\_Item::_comments:1722*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `ibf_fo 60565 forums_forums localhost forums_forums Sleep 1 NULL 60567 forums_forums localhost forums_forums Sleep 1 NULL 60568 forums_forums localhost forums_forums Sleep 1 NULL 60569 forums_forums localhost forums_forums Sleep 1 NULL 60570 forums_forums localhost forums_forums Sleep 1 NULL 60571 forums_forums localhost forums_forums Sleep 0 NULL 60572 root localhost NULL Query 0 init SHOW PROCESSLIST Link to comment Share on other sites More sharing options...
SecondSight Posted August 26, 2016 Author Share Posted August 26, 2016 Hello ! IPS support gave me a few hints : increasing innodb_ft_cache_size and innodb_ft_total_cache_size and run an OPTIMIZE TABLE query on the ibf_core_search_index table. That's what I did and it seems like it's doing better, but I still have this FULLTEXT initialization lasting several minutes and causing the server load to rise. I've ran mysqltuner, but it doesn't help much... Quote -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 2h 4m 5s (16M q [178.332 qps], 948K conn, TX: 239G, RX: 6G) [--] Reads / Writes: 92% / 8% [--] Binary logging is disabled [--] Physical Memory : 62.9G [--] Max MySQL memory : 30.2G [--] Other process memory: 2.2G [--] Total buffers: 13.4G global + 17.2M per thread (1000 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 22.0G (35.02% of installed RAM) [OK] Maximum possible memory usage: 30.2G (48.10% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (722/16M) [OK] Highest usage of available connections: 51% (512/1000) [OK] Aborted connections: 0.04% (364/948461) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (13 temp sorts / 1M sorts) [!!] Joins performed without indexes: 988 [!!] Temporary tables created on disk: 38% (48K on disk / 126K total) [OK] Table cache hit rate: 98% (1K open / 1K opened) [OK] Open file limit used: 2% (582/26K) [OK] Table locks acquired immediately: 99% (19M immediate / 19M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 23.5% (252M used / 1B cache) [OK] Key buffer size / total MyISAM indexes: 1.0G/56.5M [OK] Read Key buffer hit rate: 99.2% (6M cached / 52K reads) [!!] Write Key buffer hit rate: 43.0% (643K cached / 366K writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 12.0G/11.5G [OK] InnoDB buffer pool instances: 12 [OK] InnoDB Used buffer: 98.44% (774125 used/ 786420 total) [OK] InnoDB Read buffer efficiency: 99.99% (7593582786 hits/ 7594423312 total) [OK] InnoDB Write log efficiency: 94.84% (20716228 hits/ 21844070 total) [OK] InnoDB log waits: 0.00% (0 waits / 1127842 writes) I feel that there is some optimization which can be done, but I don't know what to do. Do you have any idea about what can be done to fix this fulltext initialisation problem and optimizing innodb ? Thank you ! Link to comment Share on other sites More sharing options...
Serega Zavodin Posted August 29, 2016 Share Posted August 29, 2016 Confirmed After update Link to comment Share on other sites More sharing options...
prupdated Posted September 12, 2016 Share Posted September 12, 2016 I had this problem as well. It was something to do with search index queries taking over MySQL up to an hour. It was so bad mysql would lock causing php to queue up until the web server errored out. It took awhile to figure out because it appears to be run as a scheduled task. I tried tuning almost everything in MySQL and other places. Although, I had not tried those parameters mentioned in the previous post here. It had worked semi-fine until this latest update. Ultimately I just reduced my search index setting to the last 3 days. I had already kept it down to 30-60 days so as not to overload the server. I will likely try those new parameters, but with my search index down to 3 days it may not matter much now. The site runs pretty fast now with most of the native search kept to a minimum. My site has over a million topics and 4 million posts. So things that may work on a smaller site or test site may not scale for mine. On IPS 3 it was not a problem. Sphinx could index the entire site from scratch in about 10 minutes. Not so anymore. Getting rid of Sphinx is one thing. But it should be replaced with another heavy hitter like Elasticsearch rather than trying to do it with MySql. I actually custom wrote a script to use Elasticsearch for a related search results block. The time to perform the initial index of all 4 + million posts on my site was under 2 minutes. If I just index the 1 + million topics, it only took about 30 seconds. An actual full text search that returns results by a similarity algorithm against this index takes about 40 milliseconds. This is much faster even than Sphinx was by a huge margin. And of course, light years faster than MySQL. Otherwise for search I just link to Google Custom Search and link to topics in member profiles if they want to see their previous posts. That plus reducing notification history to the bare minimum; turning off notification polling; limiting the number of total topics per forum; and setting guest caching to one hour seem to keep the server overhead down substantially. Link to comment Share on other sites More sharing options...
ASTRAPI Posted September 12, 2016 Share Posted September 12, 2016 Open a support ticket so IPS stuff can check it.... If the issue is related to the latest update they should fix it and push the fix to the latest version so others will avoid it... Link to comment Share on other sites More sharing options...
EmpireKicking Posted September 12, 2016 Share Posted September 12, 2016 I just checked mine and my records saying It's all good. Might have to double check that Link to comment Share on other sites More sharing options...
TSP Posted September 12, 2016 Share Posted September 12, 2016 2 hours ago, prupdated said: I had this problem as well. It was something to do with search index queries taking over MySQL up to an hour. It was so bad mysql would lock causing php to queue up until the web server errored out. It took awhile to figure out because it appears to be run as a scheduled task. I tried tuning almost everything in MySQL and other places. Although, I had not tried those parameters mentioned in the previous post here. It had worked semi-fine until this latest update. Ultimately I just reduced my search index setting to the last 3 days. I had already kept it down to 30-60 days so as not to overload the server. I will likely try those new parameters, but with my search index down to 3 days it may not matter much now. The site runs pretty fast now with most of the native search kept to a minimum. My site has over a million topics and 4 million posts. So things that may work on a smaller site or test site may not scale for mine. On IPS 3 it was not a problem. Sphinx could index the entire site from scratch in about 10 minutes. Not so anymore. Apparently IPS staff believe these issues are only due to bad configurations, so you should really submit a support ticket to hopefully contribute to have them realize otherwise or help you with the configuration. For the sake of all clients. Even if it is solved with configuration, I kind of can't believe they don't offer fulltext search that doesn't make use of MySQL (because the extra overhead MySQL produces in this aspect is very real). I personally have turned down search index to 1 year, but even that has got it's complaints on our forum (when it comes to the searchability of the forum, especially from staff). However; with 16 million posts in total and trouble even with a 1 year limit, I don't really have a choice. Unfortunately I can't follow my own advice until we have updated our server rig. Link to comment Share on other sites More sharing options...
prupdated Posted September 12, 2016 Share Posted September 12, 2016 1 minute ago, TSP said: Apparently IPS staff believe these issues are only due to bad configurations, so you should really submit a support ticket to hopefully contribute to have them realize otherwise or help you with the configuration. For the sake of all clients. Even if it is solved with configuration, I kind of can't believe they don't offer fulltext search that doesn't make use of MySQL (because the extra overhead MySQL produces in this aspect is very real). I personally have turned down search index to 1 year, but even that has got it's complaints on our forum (when it comes to the searchability of the forum, especially from staff). However; with 16 million posts in total and trouble even with a 1 year limit, I don't really have a choice. Unfortunately I can't follow my own advice until we have updated our server rig. I'm actually a bit envious if you can do a year. Fortunately, there are relatively few actual text searches done on my site. So, it's easier to just forward them to the Google custom search page and let them search from about half the site that is actually indexed. If I had more search activity, I'd just work up a front end search box to my elasticsearch instance that I use anyway with all posts in it. Link to comment Share on other sites More sharing options...
georgebkk Posted September 13, 2016 Share Posted September 13, 2016 Hi, We had to limit search whilst indexing was going on to the last 60 days. reduced the load. Link to comment Share on other sites More sharing options...
Serega Zavodin Posted November 22, 2016 Share Posted November 22, 2016 bug in this file system/Content/Search/Mysql/Index.php comment this line \IPS\Db::i()->delete( 'core_search_index_item_map', array( 'index_item_id IN( ? )', \IPS\Db::i()->select( 'index_item_id', 'core_search_index', $where ) ) ); in public function removeClassFromSearchIndex( $class, $containerId=NULL ) Link to comment Share on other sites More sharing options...
Stuart Silvester Posted November 22, 2016 Share Posted November 22, 2016 5 hours ago, Serega Zavodin said: bug in this file system/Content/Search/Mysql/Index.php comment this line \IPS\Db::i()->delete( 'core_search_index_item_map', array( 'index_item_id IN( ? )', \IPS\Db::i()->select( 'index_item_id', 'core_search_index', $where ) ) ); in public function removeClassFromSearchIndex( $class, $containerId=NULL ) I really wouldn't do that. You can't just go around commenting code out and hoping it'll still work properly. This query has nothing to do with what the OP posted (we've also made improvements to searching since this topic was opened). Link to comment Share on other sites More sharing options...
Serega Zavodin Posted November 22, 2016 Share Posted November 22, 2016 it was added in 4.1.14 but it works and mysql don't stuck CPU Link to comment Share on other sites More sharing options...
Stuart Silvester Posted November 22, 2016 Share Posted November 22, 2016 1 minute ago, Serega Zavodin said: it was added in 4.1.14 but it works and mysql don't stuck CPU It opens the possibility of the search index containing orphaned data, which could possibly cause issues. If you're have an issue with it, you're welcome to open a support ticket so that we can look into it. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.