Gabriel Torres Posted August 4, 2017 Share Posted August 4, 2017 Hi guys, We run a very busy community, with around 600,000 registered users. We use two dedicated servers. The webserver: PowerEdge R520, 2x Xeon E5-2430, 32 GiB RAM The database server: PowerEdge R520, 2x Xeon E5-2430, 64 GiB RAM, 2x 120 GiB SSD RAID 0, Percona with XtraDB (InnoDB) They are connected through a private Gigabit Ethenet connection to reduce latency and improved security. Since the upgrade to 4.2 this week we are seeing a higher than usual load in our database server. Before, load rarely reached 1.00, usually stayed in the 0.50~0.70 range. Now we are seeing load constantly above 1.00, sometimes reaching 3.00. SHOW PROCESSLIST doesn't show anything unusual, several connections sleeping. Slow log queries are being logged and the only queries that are taking a long time are search-related queries. What is strange is that the load is concentrated in the first two cores of the CPU, all the other cores are pretty much unused. From my experience, when the load on a MySQL server is high, it usually means the server needs more RAM, but that isn't the case, as we have plenty of unused RAM as you can see from the screenshots. I hope you guys can give me some input on what might be going on and/or how to debug or find the source of this problem. Thank you in advance! Link to comment Share on other sites More sharing options...
Mastric Posted August 4, 2017 Share Posted August 4, 2017 How's the slow query log look? Link to comment Share on other sites More sharing options...
Dll Posted August 4, 2017 Share Posted August 4, 2017 Are the background tasks still running - there's some re-indexing etc that runs after updating? Link to comment Share on other sites More sharing options...
Gabriel Torres Posted August 4, 2017 Author Share Posted August 4, 2017 @Dll At this point I am thinking it is a couple of background processes from some custom apps we have. I will need a few more days to investigate this possibility. Link to comment Share on other sites More sharing options...
Daniel F Posted August 4, 2017 Share Posted August 4, 2017 Im currios about this too. Let me know if you need help and I'll check it in my free time. Link to comment Share on other sites More sharing options...
Gabriel Torres Posted August 5, 2017 Author Share Posted August 5, 2017 Thanks, @Daniel F. I will have a more definitive answer within the next couple of days, I am performing some tests at the moment. Link to comment Share on other sites More sharing options...
Gabriel Torres Posted August 5, 2017 Author Share Posted August 5, 2017 There is one final thing I need to do before throwing the towel, which is upgrading PHP to 7.x. We are still running 5.6.x. I will keep you guys posted. Link to comment Share on other sites More sharing options...
ProSkill Posted August 6, 2017 Share Posted August 6, 2017 5 hours ago, Gabriel Torres said: There is one final thing I need to do before throwing the towel, which is upgrading PHP to 7.x. We are still running 5.6.x. I will keep you guys posted. That made all the difference for me. Link to comment Share on other sites More sharing options...
Gabriel Torres Posted August 11, 2017 Author Share Posted August 11, 2017 Hi guys! @ProSkill @Daniel F @Dll @Mastric Thanks for your interest in helping me. The load is now back to "normal", below 1.0. Here is what has been done that lowered the MySQL server load and, more dramatically, the load on the webserver: 1. Upgraded from PHP 5.6 to 7.1 2. Enabled Opcache instead of Xcache 3. Enabled Memcached (we weren't using any caching mechanism for the datastorage data) 4. Several bugfixes performed and upgrade to IPS 4.2.2 5. Our backup routine for the MySQL database, which runs nightly as a cronjob, was been performed incorrectly, locking the database and therefore increasing the server load during the process. (For those curious about this one, I highly suggest reading this: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html) Still monitoring and making some fine tunings, as I guess we can improve performance a little bit more. For that we will need the servers up and running without a restart for a few days. Cheers! Link to comment Share on other sites More sharing options...
Daniel F Posted August 11, 2017 Share Posted August 11, 2017 Thanks for the update. Link to comment Share on other sites More sharing options...
Serega Zavodin Posted August 13, 2017 Share Posted August 13, 2017 I can confirm there is a new CPU mysql problem When I choise use information in admin panel mysql stuck at 100% cpu for 10 sec I can't find what php file/mysql query create this problem Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.