COMMUNITY FORUM

Community Forum
  1. Forum
  2. K2 Community Forum
  3. English K2 Community
  4. [SOLVED] K2 query (cache?) problem

[SOLVED] K2 query (cache?) problem

English K2 Community 4 replies 3154 views
Kmedia 9 years 5 months ago #162211
Hi, some time ago we started suffering slow queries on our production site. We tested the K2 query issue on separate workstation from exactly same tables and there caching and query execution seems to work fine.

However, the query takes long time in production database without exceptions. It seems like the query-cache doesn't work correctly. ( variable query_cache_type | ON ) slow-log says that typically query takes over 2 seconds.

Our site has constantly approx. 200-500 active users opening K2 items, some of those registered and thus going past cache.
Site has some 65 000 to 100 000+ page views daily.
We have 200 000+ K2 items and more added daily
Site has 3 minutes cache set

System info

PHP Built On Linux www 4.4.0-53-generic #74-Ubuntu SMP Fri Dec 2 15:59:10 UTC 2016 x86_64
Database Version 5.5.56-MariaDB
Database Collation latin1_swedish_ci
Database Connection Collation utf8mb4_general_ci
PHP Version 7.0.13-0ubuntu0.16.04.1
Web Server nginx/1.10.0
WebServer to PHP Interface fpm-fcgi
Joomla! Version Joomla! 3.6.4 Stable [ Noether ] 21-October-2016 16:33 GMT
Joomla! Platform Version Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT



I can send URLs and more details privately.

Query:
SELECT i.*, c.name as categoryname,c.id as categoryid, c.alias as categoryalias, c.params as
categoryparams FROM xxx_k2_items as i RIGHT JOIN xxx_k2_categories AS c ON c.id = i.catid
WHERE i.published = 1 AND i.access IN(1,1) AND i.trash = 0 AND c.published = 1 AND c.access
IN(1,1) AND c.trash = 0 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2017-
05-17 07:01:49' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2017-05-
17 07:01:49' ) AND c.id IN (269,270,271,272,273,274,276,402) ORDER BY i.publish_up DESC
LIMIT 0, 15;

Test database:
Server: MariaDB
Server version: 10.1.22-MariaDB-1~xenial mariadb.org binary distribution
Protocol version: 10

At test database (with no external load) query times: 1.38s, 0s, 0s, 0s, 0s

Test database variables:

| query_alloc_block_size | 16384

| query_cache_limit | 131072

| query_cache_min_res_unit | 4096

| query_cache_size | 67108864

| query_cache_strip_comments | OFF

| query_cache_type | ON


Production database:
Server: MariaDB
Server version: 5.5.56-MariaDB MariaDB Server
Protocol version: 10

At production database query times: 3.23s, 2.89s, 2.99s, 2.93s, 2.78s


Production database variables:

| query_cache_limit | 33554432

| query_cache_min_res_unit | 4096

| query_cache_size | 134217728

| query_cache_strip_comments | OFF

| query_cache_type | ON

Slow log occurence (trigger time 2s)

SET timestamp=1495005105;
SET timestamp=1495005155;
SET timestamp=1495005174;
SET timestamp=1495005179;
SET timestamp=1495005220;
SET timestamp=1495005227;
SET timestamp=1495005256;
SET timestamp=1495005308;
SET timestamp=1495005333;
SET timestamp=1495005364;
SET timestamp=1495005366;
SET timestamp=1495005366;
SET timestamp=1495005398;
SET timestamp=1495005400;
SET timestamp=1495005434;
SET timestamp=1495005509;
SET timestamp=1495005536;
SET timestamp=1495005541;
SET timestamp=1495005550;
SET timestamp=1495005555;
SET timestamp=1495005557;
SET timestamp=1495005592;
SET timestamp=1495005612;
SET timestamp=1495005614;
SET timestamp=1495005622;
SET timestamp=1495005670;
SET timestamp=1495005681;
SET timestamp=1495005683;
SET timestamp=1495005687;
SET timestamp=1495005706;
SET timestamp=1495005715;
SET timestamp=1495005719;
SET timestamp=1495005727;
SET timestamp=1495005746;
SET timestamp=1495005748;
SET timestamp=1495005757;
SET timestamp=1495005763;
SET timestamp=1495005766;
SET timestamp=1495005769;

Profiled query information:
Running first time:
+
+
+
| Status | Duration |
+
+
+
| starting | 0.000024 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000075 |
| checking permissions | 0.000004 |
| checking permissions | 0.000004 |
| Opening tables | 0.000016 |
| After opening tables | 0.000005 |
| System lock | 0.000006 |
| Table lock | 0.000003 |
| After table lock | 0.000004 |
| Waiting for query cache lock | 0.000003 |
| After table lock | 0.000017 |
| init | 0.000051 |
| optimizing | 0.000030 |
| statistics | 0.000173 |
| preparing | 0.000042 |
| executing | 0.000005 |
| Creating tmp table | 0.000249 |
| Copying to tmp table | 2.228058 |
| Sorting result | 0.505713 |
| Sending data | 0.000511 |
| end | 0.000010 |
| removing tmp table | 0.124335 |
| end | 0.000022 |
| query end | 0.000005 |
| closing tables | 0.000024 |
| freeing items | 0.000022 |
| updating status | 0.000045 |
| logging slow query | 0.000060 |
| cleaning up | 0.000006 |
+
+
+

Running second time:
+
+
+
| Status | Duration |
+
+
+
| starting | 0.000042 |
| Waiting for query cache lock | 0.000006 |
| checking query cache for query | 0.000104 |
| checking permissions | 0.000006 |
| checking permissions | 0.000006 |
| Opening tables | 0.000026 |
| After opening tables | 0.000006 |
| System lock | 0.000006 |
| Table lock | 0.000004 |
| After table lock | 0.000005 |
| Waiting for query cache lock | 0.000003 |
| After table lock | 0.000023 |
| init | 0.000065 |
| optimizing | 0.000039 |
| statistics | 0.000132 |
| preparing | 0.000043 |
| executing | 0.000005 |
| Creating tmp table | 0.000272 |
| Copying to tmp table | 2.357103 |
| Sorting result | 0.768763 |
| Sending data | 0.000117 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 0.000099 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 0.000065 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 0.000113 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 0.000032 |
| Waiting for query cache lock | 0.000004 |
| Sending data | 0.000251 |
| end | 0.000054 |
| removing tmp table | 0.127060 |
| end | 0.000023 |
| query end | 0.000006 |
| closing tables | 0.000119 |
| freeing items | 0.000024 |
| updating status | 0.000007 |
| Waiting for query cache lock | 0.000003 |
| updating status | 0.000040 |
| Waiting for query cache lock | 0.000004 |
| updating status | 0.000002 |
| storing result in query cache | 0.000040 |
| logging slow query | 0.000074 |
| cleaning up | 0.000007 |
+
+
+

When I tried to run it third time, the result was something similar to step one, thus running the query again instead of fetching cached results.

So, it seems that query cache is invalidated too frequently, may be due to the heavy use of k2_items and k2_categories tables.

Is there something we could do to get the cache working correctly and get less slow queries?

Thank you!

Edited 9 years 5 months ago by Kmedia · Reason: Solved

Kmedia Topic Author 9 years 5 months ago #162264
Hi, no need to haste and flood topic with answers ;)

We tackled the slow query issue.

Main problem seems to be that when going past critical mass of items, about 200 000+ the form of the query isn't the best in handling that amount of items. It uses temporary tables and the writing process to temporary table takes a lot of time. As items are added daily to the table it slows down the whole process exponentially

We solved the issue by adding FORCE INDEX (item) to query:
explain SELECT i.*, c.name as categoryname,c.id as categoryid, c.alias as categoryalias, c.params as categoryparams FROM xxx_k2_items as i RIGHT JOIN xxx_k2_categories AS c ON c.id = i.catid WHERE i.published = 1 AND i.access IN(1,1) AND i.trash = 0 AND c.published = 1 AND c.access IN(1,1) AND c.trash = 0 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2017-05-31 07:37:57' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2017-05-31 07:37:03' ) AND c.id IN (269,270,271,272,273,274,276,402) ORDER BY i.publish_up DESC LIMIT 74610, 15;
+------+-------------+-------+-------+-----------------------------------------+---------+---------+---------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys                           | key     | key_len | ref           | rows | Extra                                                               |
+------+-------------+-------+-------+-----------------------------------------+---------+---------+---------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | c     | range | PRIMARY,category,published,access,trash | PRIMARY | 4       | NULL          |    8 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | i     | ref   | item,catid                              | catid   | 4       | xxx.c.id |  935 | Using index condition; Using where                                  |
+------+-------------+-------+-------+-----------------------------------------+---------+---------+---------------+------+---------------------------------------------------------------------+
2 rows in set (0.05 sec)



 explain SELECT i.*, c.name as categoryname,c.id as categoryid, c.alias as categoryalias, c.params as categoryparams FROM xxx_k2_items as i FORCE INDEX (item) RIGHT JOIN xxx_k2_categories AS c ON c.id = i.catid WHERE i.published = 1 AND i.access IN(1,1) AND i.trash = 0 AND c.published = 1 AND c.access IN(1,1) AND c.trash = 0 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2017-05-31 07:37:57' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2017-05-31 07:37:03' ) AND c.id IN (269,270,271,272,273,274,276,402) ORDER BY i.publish_up DESC LIMIT 74610, 15;
+------+-------------+-------+--------+-----------------------------------------+---------+---------+------------------+--------+------------------------------------+
| id   | select_type | table | type   | possible_keys                           | key     | key_len | ref              | rows   | Extra                              |
+------+-------------+-------+--------+-----------------------------------------+---------+---------+------------------+--------+------------------------------------+
|    1 | SIMPLE      | i     | range  | item                                    | item    | 10      | NULL             | 102081 | Using where                        |
|    1 | SIMPLE      | c     | eq_ref | PRIMARY,category,published,access,trash | PRIMARY | 4       | xxx.i.catid |      1 | Using index condition; Using where |
+------+-------------+-------+--------+-----------------------------------------+---------+---------+------------------+--------+------------------------------------+
2 rows in set (0.01 sec)

That dropped the slow queries immediately.

Edited 9 years 5 months ago by Kmedia

Baze 9 years 3 months ago #163248
How did you do this? can you please let me know the procedure to fix it, i'm having the same problem :(
Kmedia Topic Author 9 years 2 months ago #163417
Hi, sorry that I wasn't able to answer sooner.

You need to modify the file at ../components/com_k2/models/itemlist.php

There we modified the query string by adding FORCE INDEX (item):

$query .= " FROM #__k2_items as i RIGHT JOIN #__k2_categories AS c ON c.id = i.catid";

To

$query .= " FROM #__k2_items as i FORCE INDEX (item) RIGHT JOIN #__k2_categories AS c ON c.id = i.catid";

Hope this helps!
Krikor Boghossian 9 years 2 months ago #163445
Keep in mind that this is considered a hack and you will need to keep a backup of this file.

Log in or create an account to reply.

RSS feed