Forum Moderators: open
In addition to this, I am constantly updating data by crawling the web to look for very, very specific data for my particular "niche". I had it set up to run at a decent amount of time, but it was using up 50 GB of bandwidth a day. The crawler just constantly updates the same data over and over and over...you get the point.
Anyways, I've been noticing that MySQL queries sometimes take FOREVER! I've seen queries take 30 seconds to finish, which causes the PHP page to not load for 30 seconds! What could be the main cause of this?
My virtual dedicated server supposedly gives me 256 MB of ram. The server is through GoDaddy.
Is there a general well-known method of knowing when to upgrade to a dedicated vs virtual dedicated?
I've seen queries take 30 seconds to finish, which causes the PHP page to not load for 30 seconds! What could be the main cause of this?
Query times this long would suggest that you are querying on a non-indexed field or group of fields.
Has your database grown significantly since you started, and is there a correlation between the increased query time and the increased size of the database?
My virtual dedicated server supposedly gives me 256 MB of ram. The server is through GoDaddy.
That doesn't tell me much. 256MB is on the low side, though, for a web server + MySQL that is doing any significant volume.
Does your VPS plan guarantee you a specific percentage of machine resources? If not, what you have is no better than a shared plan, except that it gives you greater flexibility to set things up your way.
I did a test with 2 PHP scripts...one of them used MySQL, the other didn't. The one that didn't use MySQL instantly loaded but the one that DID use a MySQL select took over 30 seconds during one of these "bouts".
The question of guaranteed server resources is critical. In particular, if you don't have a hard dedicated percentage of CPU resources, another VPS user on the same machine could be causing this. What % of CPU time are you guaranteed? This is an answer you should get BEFORE signing-up for any VPS plan.n And is it hard or soft? Many low-end VPS plans don't offer a hard limit.
It also could be a locking issue. If possible, you might schedule a "maintainence period" when anything that writes to the database is disabled. (It sounds like only your crawler updates the database? If so, this should be easy. See if you can reproduce the problem. Perhaps you keep a database locked during a time that the crawler might have network access problems.
I don't think the issue is related to my queries being wrong because when the issue is happening, Plesk control panel takes forever to load as well! I'm just guessing here, but I am going to make an assumption that Plesk is MySQL driven, as well, based off the sole fact that it is also affected.
I honestly haven't heard of anything about giving me a % of CPU processing
Of course you haven't. VPS vendors are cagey this way. You have to ask. They will usually "get back with you on that" and never return the call or email.
You need to know what processor(s) they are using, how many processors per box, what clock speed, and how many users per box. If they can't or won't answer this, walk away - you have no idea what you are buying, and can't compare plans between different vendors.
You also need to know whether you have a "hard" CPU limit. There are pluses and minuses to hard vs. soft limits. With a soft limit, you can momentarily take more of the CPU than you are allotted. But other users on the box might affect you. With a hard limit (my preference) other users can't affect you - at least in terms of CPU. Unused CPU cycles go wasted, so it's less efficient for the vendor, though.
You're buying a slice. Who buys a slice of something without knowing how big it is, or if somebody else will be allowed to eat theirs if they don't dig-in in time? Unfortunately, a lot of VPS users do...
I don't think the issue is related to my queries being wrong because when the issue is happening, Plesk control panel takes forever to load as well! I'm just guessing here, but I am going to make an assumption that Plesk is MySQL driven, as well, based off the sole fact that it is also affected.
I wouldn't assume that.
I'd run "top" and observe what happens when things gum-up vs normal.
Top shows that mysqld uses up 93% of the CPU. Is this normal?
When you are experiencing the problem, or all the time? Does it blip to that amount when you do a query, or is it constant?
Depends on what you are doing with it whether that is "normal" or not. As a rule, though, no, that is not normal.
At least this confirms your suspicion that MySql is somehow related to the problem. Now to narrow things down.
I'd shut off things that are using MySql, one at a time, to try to isolate the problem.
It's *possible* that you've simply out-grown your server, but that seems unlikely, given the evidence you've given.
I'd suspect your crawler.
I think it's less likely now that it has anything to do with locking, because in that case, MySQL would likely be using very little CPU, rather than a lot.
Be careful about assuming that "everything is indexed". When you query on some combination of columns (say, LastName + FirstName) you need to make sure there is an additional index that combines the columns. That is - it is *not* sufficient to simply have an index for LastName and another one for FirstName. You must also have a LastName+FirstName index.
That's probably a poor example, as there are likely only a few records with the same last name. Think about State+<something>, ProductCategory+<something>, etc. That is, doing a search on some combination of columns where the first column in the search has a lot of records matching a given value.
Let's say I do a query of something like WHERE #*$! = 123 AND yyy = 456, I would just need the individual indexes for each, correct, and not what you are saying?
SELECT COUNT(MissionID) AS count FROM (
SELECT Sum(MaxScore) AS SumScore, MissionID FROM (
SELECT Max(Score) AS MaxScore, Gamertag, MissionID FROM CampaignMissions
GROUP BY Gamertag, MissionID)
AS MaxScoreTable GROUP BY Gamertag HAVING SumScore > '{$user->totalCampaignScore}')
AS SumScoreTable
This is a slow query...what index should I use?
Since you are grouping by Gamertag, you should have an index on Gamertag.
Since you are also grouping on Gamertag+MissionID, you should have a composite index on Gamertag+MissionID.
You should index Score, since you are taking MAX of Score.
Oh. You may not need an index on Score. But you sure need one on Score+Gamertag+MissionID.
You do realize that this query is going to read the entire CampaignMissions table every time you run it, right? There isn't a WHERE clause in sight. Is this what you intended?
Methinks you might need to have a dba take a look at your database schema, indexing, and queries.
I do need to brush up on my MySQL skills, however! :) I just learned how to do the nested selects, so I'm still experimenting.
The mysqld process still is using up a ton of CPU, but I don't know why. Is there a way to check what is causing it to be so high?
I'm seeing no more instances where the server just stops responding. Additionally, yesterday was the sites best day ever! Analytics reported 7700 unique visitors and 75000 page views. I guess the site being more stable let more people visit.
This should give you some indication of how many users you were sharing your VPS hardware with - or at least what fraction of the equivalent of your current hardware you were getting.
(VPSs normally run on fairly fast dual or quad-core machines, so it's hard to compare without all the information.)
The new server has no problems at all, fortunately! :)
