MySQL causes heavy load of dedicated server

Databases
1

Hello,

I have a dedicated server and PHP/MySQL website. Sometimes my server become extremely slow. I mean I can’t connect to it, I get different kind of errors (PHP maximum execution time exceed or even error 500).

Task Manager shows that MySQL loads server sometimes and the load is abnormal.

I’ve connected to MySQL with MySQL Administartor and I’ve seen that simple queries take too much time:

I wonder how can I fix this and what can cause those problems?

Thanks in advance. :slight_smile:

2

Anyone please? How can I fix this?

I guess the code causes this is right:

$sql = "SELECT * FROM geo.thing WHERE country_code = '$countryCode' ORDER BY ansiname LIMIT ".($allShow * 70).",70";
	
$trResult = mysql_query($sql, $conn) or die('Query failed: ' . mysql_error());

Did I make some error in it?

3

Also, I wonder is it possible to get some information about MySQL query but it’s PID (which is shown in MySQL Administrator)? I’d like to know what script creates that query.

4

Does the geo.thing table have any indexes set?

Try running this query:


EXPLAIN SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 41300, 70;

It will tell you what’s going on. If you post the results of that query here, we can help you a bit further :slight_smile:

5

Hey

It’s your LIMIT.
Just test these two queries:


SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 300, 70;
SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 141300, 70;

The second one should be a hell of allot slower than the first.
Basically, to be able to get those 70 records, mysql will have to select the first 370 in the first example, and the first 141370 in the second (allot slower).

To get over this, you can use 2 queries, and need a small table modification.

#1 Add a field to your table, numeric, unique, indexed, call it “orderKey”.
#2 populate this field, with numbers, 1 to N, based on the order of your ansiname field, so the first one will be 1, second 2, 3rd 3, etc.
#3 When you need to select, your query will look like:


SELECT * FROM geo.thing WHERE country_code = 'US' AND orderKey >= 41300 ORDER BY orderKey ASC LIMIT 70;

That should be allot faster than your current solution.