database-related performance issues

-- url of the page with the problem -- : shop.smartgart.com
-- HikaShop version -- : 2.6.3
-- Joomla version -- : 3.6.5
-- PHP version -- : 5.5
-- Browser(s) name and version -- : Chrome latest
-- Error-message(debug-mod must be tuned on) -- : page load timeout

Hi all,

I am running into database performance issues every few minutes:
Page load time becomes high (more than 1 minute); this is the time for the page, not for its referred resources, i. e. in Chrome's developer tools > Network, this is the very first request in the cascade.
Turning on Joomla debugging, I see these two queries take excessively (around 30 seconds) long:

SELECT DISTINCT b.*
FROM joomla_hikashop_product_category AS a
LEFT JOIN joomla_hikashop_product AS b
ON a.product_id=b.product_id
WHERE b.product_published=1
AND b.product_type = 'main'
AND a.category_id IN (20)
AND (b.product_access = 'all' OR b.product_access LIKE '%,9,%')
ORDER BY a.ordering ASC
LIMIT 0, 7

SELECT COUNT(*)

FROM joomla_hikashop_product AS p

INNER JOIN joomla_hikashop_product_category AS pc
ON pc.product_id = p.product_id

WHERE p.product_published = 1
AND pc.category_id = 281

The product table contains around 3 500 entries, the product_category_table around 300 entries.
The joins are done on primary keys.
In phpmyadmin, I have tried to check the integrity of the tables - they are reported OK.

In MySQL's process view, I intermittently see thse "Waiting for table level lock" related entries:

SELECT COUNT( * )
FROM joomla_hikashop_product AS p
INNER JOIN joomla_hikashop_product_category AS pc
[sometimes tons of those and also 2 x per page]

UPDATE `joomla_hikashop_product` SET `product_hit` = '677',
`product_last_seen_date` = '1491816111' WHERE

I have not changed the php / Joomla / HikaShop installation recently.
I did add a few products and categories, and I have changed site layout from category view 6 wide x 3 high to 6 wide x 12 high, as well as change the Protostar CSS to allow for a wider layout. So it could be that displaying 4 times more products per page is a cause, and I have already rewound the display to 6 x 6.

Directory /media/com_hikashop/upload contains about 3 500 image files.
Disk has 82 % used space and 32 % inodes used.

I am about to upgrade to HikaShop 3.x but would like to see stability first before changing more parameters.

best regards

Klaus

I am now trying transaction isolation level set from previous value ...

SELECT @@tx_isolation;
REPEATABLE-READ

... to new value in file /etc/mysql/my.cnf ...

transaction-isolation = SERIALIZABLE

... so that multiple read operations do not compete for locks.

When looking at the processes list in phpmyadmin, things look much less congested now.

Thanks Nicolas - you gave me a good direction for digging deeper. It's probably not the hourly / daily imports, but a DB-wise read-only, long-running, self-scripted export (initiated with http / PHP) that got critical mass on 08-April-2017 (screenshot: marked in red), leaving behind a ghost process of a big SQL select statement. I now split up that process in smaller (country-wise) chunks so that I do not run into Apache / http request timeouts.

I hope this solved the core problem and also gave me performance (page load time) benefits. This is one of the few times that I appreciate an error.