mysql query slow and down mysql server

  • Posts: 138
  • Thank you received: 4
7 years 7 months ago #267170

-- HikaShop version -- : 2.5.0
-- Joomla version -- : 3.5
-- PHP version -- : 5.6

hi,
my shop site(hikashop) many time average load server high and down server.
hosting provider say : this problem releated hikashop component.
please help me to resolve this problem
error site when server down : Error displaying the error page: Application Instantiation Error: Could not connect to MySQL.
directadmin system message : Subject: Warning: The system load average is 62.33
top - 14:12:09 up 3:50, 1 user, load average: 80.91, 62.39, 40.32
Tasks: 325 total, 3 running, 317 sleeping, 1 stopped, 4 zombie
%Cpu(s): 36.5 us, 1.5 sy, 0.0 ni, 60.6 id, 1.1 wa, 0.0 hi, 0.0 si, 0.3 st
KiB Mem : 4914072 total, 155828 free, 4646664 used, 111580 buff/cache
KiB Swap: 2097148 total, 3644 free, 2093504 used. 90932 avail Mem

thanks

Joomla Developer | am.ebrahimzadeh[at]gmail.com

Please Log in or Create an account to join the conversation.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
7 years 7 months ago #267173

Hello,

Can you host provider can give more details why HikaShop would be responsible ?

With the current information, all I can say is that your system is overloaded and MySQL server is not able to serve all requests.
But I'm afraid nothing is pointing HikaShop as source of your issue.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.
The following user(s) said Thank You: aminweb

Please Log in or Create an account to join the conversation.

  • Posts: 138
  • Thank you received: 4
7 years 7 months ago #267224

I access server root (ssh)
How can I find out, what the database table, it puts pressure on the server?
please help me
thanks

Joomla Developer | am.ebrahimzadeh[at]gmail.com

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
7 years 7 months ago #267232

Hi,

Turn on the debug mode of the Joomla configuration and browse your website, on the pages that are slow to display, look at the debug MySQL query data at the bottom of the page and that will provide important information on which query is slow.
Then, you can look at the tables used in these queries.

Please Log in or Create an account to join the conversation.

  • Posts: 138
  • Thank you received: 4
7 years 7 months ago #267274

SELECT *
FROM kbs5e_hikashop_file AS file
WHERE file.file_type = 'product'
AND file_ref_id = 4021
ORDER BY file_ordering ASC

this query many time repeat and per query time 1.3 ms
and dont any key for this query

SELECT *
FROM kbs5e_hikashop_field as a
WHERE a.`field_published` = 1
AND a.`field_display` LIKE '%;field\_item\_product\_cart=1;%'
AND a.field_table='item'
AND (a.field_access = 'all' OR a.field_access LIKE '%,8,%')
ORDER BY a.`field_ordering` ASC

query time : 1.47 ms
SELECT a.*
FROM kbs5e_hikashop_zone_link AS a
WHERE a.zone_child_namekey IN ('country_United_Kingdom_222','tax_europe_9728','ship__1818572586');

query time : 1.23 ms

Joomla Developer | am.ebrahimzadeh[at]gmail.com
Last edit: 7 years 7 months ago by aminweb.

Please Log in or Create an account to join the conversation.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
7 years 7 months ago #267292

Hello,

You can see to add an index for "file_ref_id" on the table "hikashop_file" : but a query around 1-2 ms is not problematic.
Thanks to the debug mode, you can also check what file is performing the query.
If you have a query processed several times, it can help to understand if something is unusual.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
7 years 7 months ago #267733

Hi,

We got your debug.
Here is what we can say out of it:
- There are 416 MySQL queries generated for the loading of data on that page.
- It only took 0.35 second to run all these MySQL queries.
- Out of these 416 queries, HikaShop only accounts for 240 of them.

More than 400 queries for one page is a lot. However, they are really fast on your website so the problem with your MySQL server doesn't come from these.
So either the problem is on other pages than the one for which you provided the debug, or the slow down isn't MySQL related.
Also, a normal page should have about 100 queries provided it's configured properly. Looking at them, it looks like your template (or some other module-related extension) is adding a lot of extra queries on the modules which aren't there normally.
And regarding HikaShop, I suppose that it comes from how you configured the listings. There are ways to reduce the number of queries. For example, if you turn off the display of the custom item fields in your listings, the system will remove a lot of the queries on the listings.
But even doing all that won't help for your main issue as it apparently comes from something else.
Please ask your provider what exactly slows down the server and provide them when the debug file you gave. Because saying it comes from HikaShop without saying what is the problem exactly is like saying there is a bug in Windows without saying where it comes from or how to reproduce it and Microsoft won't be able to do anything in that case.
You can even give them the link to this message so that they don't have to analyze the debug file and directly read my conclusions.

Please Log in or Create an account to join the conversation.

Time to create page: 0.069 seconds
Powered by Kunena Forum