HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

High MySql CPU usage after upgrading to version 2.4.7


#1

I’m not sure, if this belongs here or in the regular request system, but I’m hoping that maybe this is of general enough nature to be useful in the forum.

Yesterday evening we upgraded to HelpSpot 2.4.7 (from 2.1.1) to overcome another issue (which Ian is familar with). However since then our HelpSpot installation seems to have become much more sluggish than before. When running the top command on our server, it appears that MySql is gobbling up a ton of CPU resources compared to any other processes on the machine (a virtual machine environment - Linux CentOS 5 - with only HelpSpot on it.)

I’ve changed the HelpSpot Live Refresh interval from the default 30 seconds to a slower 90 seconds in an attempt to take a little pressure off MySql. But that seems to have had relatively little effect on MySql CPU consumption.

I also restarted the entire CentOS 5 server during the day to ensure things got off to a clean start all around.

So my question is, if it’s a known issue that there is a lot more mysql consumption in version 2.4.7 or if I need to do some checking that the upgrade didn’t do anything strange. (I used the install.php through the browser, and there were no indications of trouble in that process. Also HelpSpot appears to work fine - just more sluggish than before the upgrade.


#2

Thanks John,

Yeah, I had tried to get mytop installed and working before my original post, but didn’t get it working until just now. (The readme on the mytop home page had pointed to an outdated version of TermReadKey, so mytop kept seg faulting on me – arggh!)

And I’m still hoping not to have to become a MySql performance tuning expert. :slight_smile:

In any case, I’ll be monitoring tomorrow when things get busy again.


#3

Hi Niels,

There’s not anything know to be more sluggish. The filters are improved speed wise over 2.1.1, though the next release includes some massive speed improvements for filters so that may help. I agree that it’s likely a filter issue, perhaps a poorly defined filter.

Check your filters, especially for ones that include a full text search condition. Those can be very slow and put a lot of burden on MySQL. Especially be careful about using the “count” option on filters that include a full text condition as it will cause that full text search to be run on every page load in order to get the count each time.

The slow query log will also be useful as John mentions, if you get some data in there post it up and we can take a look.


#4

thanks Ian and John, your advice and info is much appreciated. I’ll do as you advise and psot back


#5

ok - I had to wait until after office hours to restart mysql with the slow-queries.log enabled. While running mytop all day, it definitely wasn’t the case of a few huge reports (i.e. very long queries) slowing everything down. It’s more like we are getting nickled and dimed to death with 3 second queries for retrieving a count.

After enabling the log just a few minutes ago, I just clicked on a couple of simple filters with a few requests in them, went into my queue, which has 2 items in it, went into a couple of request details. All simple stuff - no running of reports – and I’m all alone on the system, no other users, HelpSpot is the only thing on that VPS (virtual private server). Everything else flies on the machine, Apache is taking no time at all to serve the pages - it’s just mysql, which gobbles up CPU in big chunks.

So in just a few minutes I was able to collect some of the queries crossing a threshold of 3 seconds:

Here they are - sorry for the lousy formatting:


/usr/libexec/mysqld, Version: 5.0.22-log. started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument

Time: 081126 19:26:23

User@Host: xxxxxx[xxxxxx] @ localhost []

Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 93095

use HelpSpot;
SELECT COUNT(HS_Request.xRequest) AS thecount
FROM HS_luStatus,HS_Request
LEFT OUTER JOIN HS_Category ON HS_Request.xCategory = HS_Category.xCategory

				  WHERE HS_Request.xStatus = HS_luStatus.xStatus AND  (( HS_Request.xRequest IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE HS_Request_ReportingTags.xReportingTag IN ('7'))) AND  HS_Request.xStatus <> 2 ) AND  HS_Request.fTrash <> 1  
				   LIMIT 1;

/usr/libexec/mysqld, Version: 5.0.22-log. started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.22-log. started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument

Time: 081126 19:56:26

User@Host: xxxxxx[xxxxxx] @ localhost []

Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 93098

use HelpSpot;
SELECT COUNT(HS_Request.xRequest) AS thecount
FROM HS_luStatus,HS_Request
LEFT OUTER JOIN HS_Category ON HS_Request.xCategory = HS_Category.xCategory

				  WHERE HS_Request.xStatus = HS_luStatus.xStatus AND  (( HS_Request.xRequest IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE HS_Request_ReportingTags.xReportingTag IN ('7'))) AND  HS_Request.xStatus <> 2 ) AND  HS_Request.fTrash <> 1  
				   LIMIT 1;

Time: 081126 20:03:17

User@Host: xxxxxx[xxxxxx] @ localhost []

Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 93098

SELECT COUNT(HS_Request.xRequest) AS thecount
FROM HS_luStatus,HS_Request
LEFT OUTER JOIN HS_Category ON HS_Request.xCategory = HS_Category.xCategory

				  WHERE HS_Request.xStatus = HS_luStatus.xStatus AND  (( HS_Request.xRequest IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE HS_Request_ReportingTags.xReportingTag IN ('7'))) AND  HS_Request.xStatus <> 2 ) AND  HS_Request.fTrash <> 1  
				   LIMIT 1;

Time: 081126 20:05:57

User@Host: xxxxxx[xxxxxx] @ localhost []

Query_time: 4 Lock_time: 0 Rows_sent: 2 Rows_examined: 93070

SELECT HS_Request.*, HS_Category.sCategory, HS_luStatus.sStatus, CONCAT_WS(’ ',HS_Request.sFirstName,HS_Request.sLastName) AS fullname , (SELECT MAX(dtGMTChange)
FROM HS_Request_History
WHERE HS_Request_History.xRequest = HS_Request.xRequest
GROUP BY HS_Request_History.xRequest) AS lastupdate
FROM HS_luStatus,HS_Request
LEFT OUTER JOIN HS_Category ON HS_Request.xCategory = HS_Category.xCategory

				  WHERE HS_Request.xStatus = HS_luStatus.xStatus AND  (( HS_Request.xRequest IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE HS_Request_ReportingTags.xReportingTag IN ('7'))) AND  HS_Request.xStatus <> 2 ) AND  HS_Request.fTrash <> 1  
				   ORDER BY fUrgent DESC, HS_Request.xRequest DESC LIMIT 0,500;

#6

Here’s the output from phpMyAdmin (sorry about the formatting again) about the indices for the Requests and Request_History tables

Keyname Type Cardinality Action Field
PRIMARY PRIMARY 93095 Edit Drop xRequest
HS_Request_IndexfOpenxPersonAssignedTo INDEX 33 Edit Drop fOpen
xPersonAssignedTo
HS_Request_IndexxCategory INDEX 6 Edit Drop xCategory
HS_Request_IndexxStatus INDEX 3 Edit Drop xStatus
HS_Request_IndexdtGMTOpened INDEX 93095 Edit Drop dtGMTOpened
HS_Request_IndexsUserId INDEX 5 Edit Drop sUserId 10
HS_Request_IndexsLastName INDEX 8463 Edit Drop sLastName 10
HS_Request_IndexsEmail INDEX 13299 Edit Drop sEmail 10
HS_Request_IndexfTrash INDEX 2 Edit Drop fTrash
Custom2 INDEX 5 Edit Drop Custom2 10
Custom3 INDEX 7 Edit Drop Custom3 10
Custom4 INDEX 8 Edit Drop Custom4 10
Custom5 INDEX 15 Edit Drop Custom5
Custom6 INDEX 80 Edit Drop Custom6

and

Keyname Type Cardinality Action Field
PRIMARY PRIMARY 379150 Edit Drop xRequestHistory
HS_Request_History_IndexxRequest INDEX 94787 Edit Drop xRequest
HS_Request_History_IndexdtGMTChange INDEX 379150 Edit Drop dtGMTChange
HS_Request_History_IndextNote FULLTEXT 1 Edit Drop tNote


#7

It looks like taking the counters off the filters as per Ian’s suggestion may have done the trick. Thanks Ian!

Also thanks John for your kind help and the valuable links. They proved very useful!

And a virtual machine environment is several OS running in the same physical machine. We are having half a gig of dedicated RAM,which generally seems quite good for running HelpSpot.


#8

Hi Niels,

Glad to hear turning off counts improved things. Normally a db with your number or rows wouldn’t be much of a problem, I wonder though if the VM environment was causing the issue? Perhaps you could have tried adding 1GB (if possible) of RAM to see if that helped.

In any event, the next release has optimizations of the sub selects which may be the issue and should generally be much faster. I notice all the slow queries you posted were for filters using reporting tags as conditions. These are faster in the next release.

What you could probably do is enable counts on all your filters that you want except for the ones that use reporting tags (or full text searches) as conditions and that should be fine for your DB.


#9

Hi Ian,

thanks for the follow-up tips and the preview of some of the next release performance improvements.

I’ll explore alternatives to the text searches - because it looked like to me that some of the custom fields are indexed, and maybe we can make better use of those for essentially creating different queues with their own counters.

The interesting thing was, that mysql clearly gobbled up CPU like crazy, and once I took the counters off, it stopped doing that, so my hunch is, that RAM may not have been the biggest issue - since to my understanding memory swapping will add elapsed time, but shouldn’t add exorbitant CPU cycles.

In any case thanks to your advice, we are in good shape for now and we also may move from a virtual machine environment to a regular server over the next couple of months or so.

Thanks again!