Explanation for slow MySQL queries?

Question

Lately, my site has been generating "cannot connect to mysql database" errors. Not all the time, just now and then (at high traffic times).

I asked my hosting company about this and they replied that I had a lot of slow (>1 second) MySQL queries. These queries are logged and I've spent a lot of time going over the log files, but no pattern is emerging. There are all kinds of queries in the files: some that I have written, but also some from third-party software like Mediawiki and Omeka. It seems odd that something could be wrong with all of these very different queries.

I've tried using the MySQL "explain" function on some of the queries and that doesn't seem to be showing any problems (thought I'm no expert at interpreting the results of "explain").'

I'm on a shared hosting plan that allows 15 simultaneous MySQL connections. I've run the "repair" feature on all of my databases with no effect.

So, my question: Is there something other than the structure of the queries themselves that I should be looking at as an explanation for the slowness?

Thanks for any advice you can offer.

===============================================================

Additional info as requested:

Below are two queries and their "explain" results. Regarding the configuration on the shared host, it's Apache with mod_php.

Okay, here are some examples.

This is a query that brings up info on a staff member (email x-ed out). The staff table has <100 rows.

# Wed Oct  7 00:32:58 2015
# Thread_id: 500674  Schema: ithacali_live  Last_errno: 0  Killed: 0
# Query_time: 2.402803  Lock_time: 0.017267  Rows_sent: 1  Rows_examined: 59  Rows_affected: 0  Rows_read: 59
# Bytes_sent: 1450
use ithacali_live;
SET timestamp=1444199578;
SELECT s.staff_id, s.lname, s.fname, s.title, s.tel, s.email, d.name, bio, subject_id
FROM staff s
LEFT JOIN department d on s.department_id = d.department_id
LEFT JOIN staff_subject ss ON s.staff_id = ss.staff_id
WHERE s.email = 'xxxx@xxxxx.edu'
GROUP BY s.lname

+----+-------------+-------+--------+----------------------------+------------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys              | key              | key_len | ref                           | rows | Extra       |
+----+-------------+-------+--------+----------------------------+------------------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | s     | index  | NULL                       | INDEXSEARCHstaff | 516     | NULL                          |    3 | Using where |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY                    | PRIMARY          | 4       | ithacali_live.s.department_id |    1 |             |
|  1 | SIMPLE      | ss    | ref    | PRIMARY,fk_ss_staff_id_idx | PRIMARY          | 4       | ithacali_live.s.staff_id      |   25 | Using index |
+----+-------------+-------+--------+----------------------------+------------------+---------+-------------------------------+------+-------------+
3 rows in set (0.00 sec)

Here's one from our homegrown CMS. The "pluslet" table contains ~8,000 rows and "subject" about 700.

# Wed Oct  7 21:46:02 2015
# Thread_id: 756435  Schema: ithacali_live  Last_errno: 0  Killed: 0
# Query_time: 6.854625  Lock_time: 0.000179  Rows_sent: 10  Rows_examined: 30  Rows_affected: 0  Rows_read: 30
# Bytes_sent: 7956
use ithacali_live;
SET timestamp=1444275962;
SELECT p.pluslet_id, p.title, p.body, ps.pcolumn, p.type, p.extra
    FROM pluslet p, subject s, pluslet_subject ps
    WHERE p.pluslet_id = ps.pluslet_id
    AND s.subject_id = ps.subject_id
    AND s.subject_id = '246'
    ORDER BY prow ASC

+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+-----------------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys                             | key                  | key_len | ref                         | rows | Extra                       |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+-----------------------------+------+-----------------------------+
|  1 | SIMPLE      | s     | const  | PRIMARY                                   | PRIMARY              | 8       | const                       |    1 | Using index; Using filesort |
|  1 | SIMPLE      | ps    | ref    | fk_sp_pluslet_id_idx,fk_sp_subject_id_idx | fk_sp_subject_id_idx | 8       | const                       |   10 | Using where                 |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY                                   | PRIMARY              | 4       | ithacali_live.ps.pluslet_id |    1 |                             |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+-----------------------------+------+-----------------------------+
3 rows in set (0.00 sec)

Answer 1

Shared hosts are going to be a nightmare, they always oversell computing resources and judging by your MySQL logs - your queries aren't that heavy, they inspect less than 1000 rows, which is by all standards very, very low.

I asked about PHP execution model. Reason for this is that php-fpm is extremely resource friendly, especially when it comes to connecting to MySQL since it's able to open the connection once and keep it open for subsequent visits. Usually, an instance of php-fpm will open as many connections to MySQL as there are child processes (I don't have more than 8). When mod_php is used, this isn't happening, it's the complete opposite - it's not resource friendly.

What could be happening, since you are on a shared host - someone else is using the hard disks' resources (I/O, bandith) and you're stuck with limited resources. This is the usual reason why sites are moved to a dedicated host - so someone else doesn't eat your lunch. You can try to optimize your code as much as you want, but you are constrained by PHP execution model and by the fact you share resources.

The point of this post was to inform you, with at least some facts, that your shared host simply won't cut it for you at this stage. No amount of programming will fix the lack of resources.

Answer 2

You have the queries that are causing the problem, and that's exactly what you need to address the issue. The way we handle this is the following. We first address the queries that are taking the longest time in the slow query log, and then we address the queries that are appearing frequently (for example, updating a #__session table on a Joomla website).

Now addressing the queries can be one of the following:

• Rewriting the queries
• Removing duplicate queries

• Restricting the queries. For example, if your homepage has something like:

  SELECT * FROM table_name LIMIT 0, 20

And table_name has something like 100k articles and the homepage only retrieves the last 20, then you can do something like

 SELECT * FROM `table_name` WHERE `id` > 99,000 LIMIT 0, 20

(Of course, the above query assumes contiguous IDs, so the 99,000 should be replaced with something that accommodates your situation and must be updated every once in a while).

• Indexing some fields that should have been indexed but aren't
• Changing the type of some fields (some tables use text fields for fields that take no more than 64 characters, and should be of type VARCHAR instead of TEXT)
• Modifying the configuration of the database to accommodate the load on the server