hello, my host has warned me about overusing server resources, due to locked/ sleep
below is a example of the issue
any advice to sorting this out would be great. thanks!
05:21:20 up 11 days, 16:37, 1 user, load average: 4.39, 6.23, 8.16
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
====================
| 138858 | _osc3 | localhost | _osc | Query | 83 | Sending data | select count(distinct p.products_id) as total from ((products p) left join manufacturers m using(ma |
| 138871 | _osc3 | localhost | _osc | Query | 44 | Locked | update products_description set products_viewed = products_viewed+1 where products_id = '1067' and l |
| 138873 | _osc3 | localhost | _osc | Query | 41 | Locked | update products_description set products_viewed = products_viewed+1 where products_id = '1067' and l |
| 138879 | _osc3 | localhost | _osc | Query | 31 | Locked | select products_name from products_description where products_id = '2666' and language_id = '1' |
| 138890 | ideasonc_tabbled | localhost | ideasonc_tabbled | Sleep | 0 | | |
| 138891 | root | localhost | | Query | 0 | | show processlist |
generallythe site works perfect, but will randomly start to lag until the sql is reset.
thank you
Latest News: (loading..)
0
sql oscommerce locked / sleep database queries
Started by mafiouso, May 08 2012 05:59 AM
sql oscommerce locked / sleep
5 replies to this topic
#2 ONLINE
Posted 08 May 2012 - 12:36 PM
@mafiouso
I suggest changing to a better hosting provider.as osCommerce processes database queries with almost every page load.
Chris
I suggest changing to a better hosting provider.as osCommerce processes database queries with almost every page load.
Chris
:|: Was this post helpful ? Click the LIKE THIS button :|:
See my Profile (click here) for more information and to contact me for professional osCommerce support that includes custom templates, add ons as well as cart leasing and support plans.
See my Profile (click here) for more information and to contact me for professional osCommerce support that includes custom templates, add ons as well as cart leasing and support plans.
#3
Posted 08 May 2012 - 02:56 PM
hi dunweb,
im not sure what the problem is, can you tell me why the Query are locked and sleeping?
is this normal or a issue.
as far as other hosting they all say they are good but how do i know whats good for this issue?
thanks
im not sure what the problem is, can you tell me why the Query are locked and sleeping?
is this normal or a issue.
as far as other hosting they all say they are good but how do i know whats good for this issue?
thanks
DunWeb, on 08 May 2012 - 12:36 PM, said:
@mafiouso
I suggest changing to a better hosting provider.as osCommerce processes database queries with almost every page load.
Chris
I suggest changing to a better hosting provider.as osCommerce processes database queries with almost every page load.
Chris
#5
FWR Media
-
- Community Sponsor
-
- 6,836 posts
- Real Name:Robert Fisher
- Gender:Male
- Location:Stowmarket - Suffolk - UK
Posted 14 August 2012 - 07:24 AM
@mafiouso
MySQL table locking issues
You need to track down the slow queries in your script, you could e.g. install KissER ( see my sig ).
This looks to me like query 83
select count(distinct p.products_id) as total from .....
Is a slow query taking a significant amount of time, I can't see all of the query but it is probably joining on products_description.
While this slow query is still sending data two updates are attempted on the products description table: -
update products_description set products_viewed = ......
As well as a SELECT: -
select products_name from products_description ....
UPDATE has a higher priority than SELECT so ..
The two UPDATE(s) are waiting for the first SELECT to finish, the new SELECT is waiting for both UPDATES to finish .. and so you have your problem.
The solution is to optimise these SELECT queries to run faster so that they lock tables for a shorter time.
MySQL table locking issues
You need to track down the slow queries in your script, you could e.g. install KissER ( see my sig ).
This looks to me like query 83
select count(distinct p.products_id) as total from .....
Is a slow query taking a significant amount of time, I can't see all of the query but it is probably joining on products_description.
While this slow query is still sending data two updates are attempted on the products description table: -
update products_description set products_viewed = ......
As well as a SELECT: -
select products_name from products_description ....
UPDATE has a higher priority than SELECT so ..
The two UPDATE(s) are waiting for the first SELECT to finish, the new SELECT is waiting for both UPDATES to finish .. and so you have your problem.
The solution is to optimise these SELECT queries to run faster so that they lock tables for a shorter time.
Edited by FWR Media, 14 August 2012 - 07:25 AM.
Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
KissIT Image Thumbnailer
Security Pro - Querystring protection against hackers ( a KISS contribution )
If you found my post useful please click the "Like This" button to the right.
Please only PM me for paid work.
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
KissIT Image Thumbnailer
Security Pro - Querystring protection against hackers ( a KISS contribution )
If you found my post useful please click the "Like This" button to the right.
Please only PM me for paid work.
#6
Posted 22 August 2012 - 01:13 PM
hello, im not a coder, but it seems that there is no exact query that is the issue of slow pages
it seems something is happening on the server at random that causes slow sql. then back to fast again.. i cant pin point it
the only thing my host says is its locked tables etc.
it seems something is happening on the server at random that causes slow sql. then back to fast again.. i cant pin point it
the only thing my host says is its locked tables etc.
FWR Media, on 14 August 2012 - 07:24 AM, said:
@mafiouso
MySQL table locking issues
You need to track down the slow queries in your script, you could e.g. install KissER ( see my sig ).
This looks to me like query 83
select count(distinct p.products_id) as total from .....
Is a slow query taking a significant amount of time, I can't see all of the query but it is probably joining on products_description.
While this slow query is still sending data two updates are attempted on the products description table: -
update products_description set products_viewed = ......
As well as a SELECT: -
select products_name from products_description ....
UPDATE has a higher priority than SELECT so ..
The two UPDATE(s) are waiting for the first SELECT to finish, the new SELECT is waiting for both UPDATES to finish .. and so you have your problem.
The solution is to optimise these SELECT queries to run faster so that they lock tables for a shorter time.
MySQL table locking issues
You need to track down the slow queries in your script, you could e.g. install KissER ( see my sig ).
This looks to me like query 83
select count(distinct p.products_id) as total from .....
Is a slow query taking a significant amount of time, I can't see all of the query but it is probably joining on products_description.
While this slow query is still sending data two updates are attempted on the products description table: -
update products_description set products_viewed = ......
As well as a SELECT: -
select products_name from products_description ....
UPDATE has a higher priority than SELECT so ..
The two UPDATE(s) are waiting for the first SELECT to finish, the new SELECT is waiting for both UPDATES to finish .. and so you have your problem.
The solution is to optimise these SELECT queries to run faster so that they lock tables for a shorter time.
