Osticket: Slow Query and performance

[geo782]

Description

Our agents are having problem with loading speed of osticket. Our database is 15GB and ost_file_chunk is 14GB. We use osticket since 1st Of Martch, this year.
From last Thursday, im getting a lot of complains from agents, that osticket is very slow.

After checking and editing mysql settings, I checked the slow queries and i have the following :
QUERY COUNT TOTAL TIME AVERAGE LATENCY
SELECT SQL_CALC_FOUND_ROWS A1.* FROM ost_file A1 LEFT JOIN ost_attachment A2 ON (A1.id = A2.file_id) WHERE (A2.object_id IS NULL AND A1.ft = ? AND A1.created < NOW() - INTERVAL ? DAY) LIMIT ? 1 01:51.000 01:51.000
SELECT A1.* FROM ost_file A1 LEFT JOIN ost_attachment A2 ON (A1.id = A2.file_id) WHERE (A2.object_id IS NULL AND A1.ft = ? AND A1.created < NOW() - INTERVAL ? DAY) LIMIT ?, ? 1 02:00.000 02:00.000
SELECT A1.* FROM ost_file A1 LEFT JOIN ost_attachment A2 ON (A1.id = A2.file_id) WHERE (A2.object_id IS NULL AND A1.ft = ? AND A1.created < NOW() - INTERVAL ? DAY) 20 36:44.000 01:50.000

The 3rd query is count 20 and total time 36:44.000 and average latency 01:50.000

Can we stop this query from running all time?

1.10.1 with 1.10.2 patch

George

[JediKev]

It's the query to delete orphaned files from the database. This query runs every other cron run and you can find this here. You can change the runtime of this query from every other cron run to once or twice every night. This will still delete the orphaned files at night but reduce the load on the db. We are currently in the process of reworking this query so please stay tuned.

You can also update your query to the most recent query for now until we release the rewrite: https://github.com/osTicket/osTicket/pull/4253

Cheers.

[geo782]

@JediKev
Thank you for the reply. Last Thursday I updated query #4253, so now I changed class.cron to delete orphaned files every 120 crons instead of 10.

I was thinking to Optimize table ost_file_chunk after stopping cronjobs and agents from working, but in class.cron you say : # XXX: Please do not add an OPTIMIZE for the file_chunk table! and Im a little bit scared now!!

George

[greezybacon]

@geo782 the reason behind the cautionary statement is because the OPTIMIZE command will cause a complete table rebuild. So it's safe to do it once, but it should not be done automatically as part of the cron system--since rebuilding a 14GB table every couple days would be horrendous.

That said, the big problem I have with your slow queries is the question marks. That indicates you're using the parameterized statements. osTicket moved away from them as of 4f8f236d6a114c710362fdc302c4763cdf2fd387 (September 2015), which is included in every release of v1.10. So I'm unsure why you're seeing them if you're running v1.10.2.

When I run an explain on the query, I get these results:

MariaDB [osticket]> explain SELECT A1.* FROM ost_file A1 LEFT JOIN ost_attachment A2 ON (A1.id = A2.file_id) WHERE (A2.object_id IS NULL AND A1.ft = 'T' AND A1.created < NOW() - INTERVAL 1 DAY);
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                                                                    |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------------------------------------------------------+
|    1 | SIMPLE      | A1    | ALL   | ft            | NULL      | NULL    | NULL | 1941 | Using where                                                              |
|    1 | SIMPLE      | A2    | index | NULL          | file-type | 11      | NULL | 1975 | Using where; Using index; Not exists; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------------------------------------------------------+

One big first takeaway from this is that the file_chunk table is not used to perform the statement. So optimizing it won't make a difference.

Judging by the output, it looks like its going to:

• Perform a full index scan of file table, using the ft index
• Scan for ft = 'T', then fetch the record and check for created < 1 day ago
• Scan for matching records in the attachment table using the file-type index, looking for object_id of NULL and corresponding file_id
• Combine the results using a join buffer (not a temporary file)

This will mean an index scan of attachment.file-type for every record of file created over one day ago. Or a performance impact of O(n**2) where n is the number of files over one day old. And my guess is that you have lots of files which match those criteria.

This seems to be a much better query for the task:

MariaDB [osticket]> explain SELECT A1.* FROM ost_file A1 WHERE (A1.ft = 'T' AND A1.created < NOW() - INTERVAL 1 DAY) AND NOT EXISTS (SELECT id FROM ost_attachment A2 WHERE A1.id = A2.file_id);
+------+--------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type  | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+--------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | PRIMARY      | A1    | ALL   | ft            | NULL      | NULL    | NULL | 1942 | Using where |
|    2 | MATERIALIZED | A2    | index | NULL          | file-type | 11      | NULL | 1976 | Using index |
+------+--------------+-------+-------+---------------+-----------+---------+------+------+-------------+

But currently, the ORM does not have the concept of the NOT EXISTS operation. Perhaps it would be better to use a query for the time being?:

diff --git a/include/class.file.php b/include/class.file.php
index 2a0073b4..e8ea7db9 100644
--- a/include/class.file.php
+++ b/include/class.file.php
@@ -627,20 +627,17 @@ class AttachmentFile extends VerySimpleModel {
      * canned-response, or faq point to any more.
      */
     static function deleteOrphans() {
-
-        // XXX: Allow plugins to define filetypes which do not represent
-        //      files attached to tickets or other things in the attachment
-        //      table and are not logos
-        $files = static::objects()
-            ->filter(array(
-                'attachments__object_id__isnull' => true,
-                'ft' => 'T',
-                'created__lt' => SqlFunction::NOW()->minus(SqlInterval::DAY(1)),
-            ));
-
-        foreach ($files as $f) {
-            if (!$f->delete())
-                break;
+        $sql = "SELECT `id` FROM ".FILE_TABLE.
+            " A1 WHERE (A1.ft = 'T' AND A1.created < NOW() - INTERVAL 1 DAY)".
+            " AND NOT EXISTS (SELECT id FROM ".ATTACHMENT_TABLE.
+            " A2 WHERE A1.`id` = A2.`file_id`)";
+
+        if (($res=db_query($sql)) && db_num_rows($res)) {
+            while (list($id) = db_fetch_row($res)) {
+                if ($f = static::lookup((int) $id))
+                    if (!$f->delete())
+                        break;
+            }
         }

         return true;

[geo782]

Thanks for the information, i will test it this week and report back

[tbjornli]

After upgrading from v1.10.1 to v1.10.4 we noticed that the performance decreased noticeably after a couple of hours. When checking the processlist we saw a lot of these queries

SELECT `id` FROM ost_file A1 WHERE (A1.ft = 'T' AND A1.created < NOW() - INTERVAL 1 DAY) AND NOT EXISTS (SELECT id FROM ost_attachment A2 WHERE A1.`id` = A2.`file_id`)

Some of them had been running for over 6000 seconds. We stopped the cronjob and added an index file_id on the ost_attachment table by running

ALTER TABLE `ost_attachment` ADD INDEX(`file_id`);

After enabling the cronjob again there was no longer a performance issue and no longer lots of queries stuck in the processlist.

Deleting orphaned files from the database on every second run seems a bit extreme. Running it once or twice every night should be sufficient.

[JediKev]

@tbjornli

We are addressing these issues in the 1.11.x series.

Cheers.

[geo782]

@JediKev
Update after changing class.file.php (sorry for the delay to reply!)

Since yesterday, when i delete 25 tickets with attachements, the time to delete them is ~25 minutes. For 1 file it was ~1 minute. I slow query log, query time is 70-110 secs.
Today we extended total ram of VM to 7GB from 4GB and changed innodb_buffer_size to 4GB from 1GB. I also optimize all tables and ost_file_chuck table returned to 15,5GB from 17GB.
Also I updated auto_refresh of lists in all agent profiles to 0 from 3 minutes.

After changing class.file.php with new code, ticket deletion is super fast !!!!! Thank you for the new code.

My only question regarding to what @tbjornli writes : Do I have to add index to ost_attachment?

with SHOW INDEX FROM ost_attachement i get :

| Table          | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ost_attachment |          0 | PRIMARY   |            1 | id          | A         |       22934 |     NULL | NULL   |      | BTREE      |         |               |
| ost_attachment |          0 | file-type |            1 | object_id   | A         |       22934 |     NULL | NULL   |      | BTREE      |         |               |
| ost_attachment |          0 | file-type |            2 | file_id     | A         |       22934 |     NULL | NULL   |      | BTREE      |         |               |
| ost_attachment |          0 | file-type |            3 | type        | A         |       22934 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Im also to 1.10.4 (from 1.10.1)
Regards
George