jonnyu Posted November 18, 2007 Posted November 18, 2007 I’m using OSCommers and I have my site in Siteground. They have sent me an email stating that OS commerce database was taking too many resources from the server (CPU and memory). They said that a few select queries where taking a lot of resources and that I should solve this problem in my database. My database has only 6 MB and I don’t understand how simple select queries can cause such a high resource consumption. Here is the report: “We have conducted a detailed investigation of the reason for the high resource consumption and it turned out that your osCommerce installation executes slow queries towards its database, which eventually hogs the server. The server tries to execute your slow queries while making other processes stay in the queue until some memory is freed. While they are waiting however, they stack up and further deteriorate the problem. Upon further investigation, it turned out that the following queries in your account are slow and heavily consume server resources: ---------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------------------+-----------+---------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 6736 | designpc | localhost | designpc_osc1 | Query | 43 | Opening tables | select value from sessions where sesskey = '85bc7fc71e247754a1cfdba999d8957d' and expiry > '11946387 | | 6737 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = 'ef466fadb66d522a9ec22373dccb5cf3' and expiry > '11946387 | | 6738 | designpc | localhost | designpc_osc1 | Query | 42 | Opening tables | delete from whos_online where time_last_click < '1194637869' | | 6739 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = 'c2ae46af5beb5455eb42071367bbfc31' and expiry > '11946387 | | 6742 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = '180c19712586d97123681fae796501be' and expiry > '11946387 | | 6744 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = '85bc7fc71e247754a1cfdba999d8957d' and expiry > '11946387 | In order to have the limitations removed, please optimize your script. If this problem with your website is not resolved in 7 days we may need to SUSPEND YOUR ACCOUNT.” They are saying that they will suspend my account. I checked these tables and they are temporary data tables and they only had a few fields and records. I believe that this may have something to do with the ability of creating shopping carts without being logged. Is there a way to fix this?
Guest Posted November 18, 2007 Posted November 18, 2007 I’m using OSCommers and I have my site in Siteground.They have sent me an email stating that OS commerce database was taking too many resources from the server (CPU and memory). They said that a few select queries where taking a lot of resources and that I should solve this problem in my database. My database has only 6 MB and I don’t understand how simple select queries can cause such a high resource consumption. Here is the report: “We have conducted a detailed investigation of the reason for the high resource consumption and it turned out that your osCommerce installation executes slow queries towards its database, which eventually hogs the server. The server tries to execute your slow queries while making other processes stay in the queue until some memory is freed. While they are waiting however, they stack up and further deteriorate the problem. Upon further investigation, it turned out that the following queries in your account are slow and heavily consume server resources: ---------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------------------+-----------+---------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 6736 | designpc | localhost | designpc_osc1 | Query | 43 | Opening tables | select value from sessions where sesskey = '85bc7fc71e247754a1cfdba999d8957d' and expiry > '11946387 | | 6737 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = 'ef466fadb66d522a9ec22373dccb5cf3' and expiry > '11946387 | | 6738 | designpc | localhost | designpc_osc1 | Query | 42 | Opening tables | delete from whos_online where time_last_click < '1194637869' | | 6739 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = 'c2ae46af5beb5455eb42071367bbfc31' and expiry > '11946387 | | 6742 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = '180c19712586d97123681fae796501be' and expiry > '11946387 | | 6744 | designpc | localhost | designpc_osc1 | Query | 47 | Opening tables | select value from sessions where sesskey = '85bc7fc71e247754a1cfdba999d8957d' and expiry > '11946387 | In order to have the limitations removed, please optimize your script. If this problem with your website is not resolved in 7 days we may need to SUSPEND YOUR ACCOUNT.” They are saying that they will suspend my account. I checked these tables and they are temporary data tables and they only had a few fields and records. I believe that this may have something to do with the ability of creating shopping carts without being logged. Is there a way to fix this? They do not want your business, change hosts.
Jan Zonjee Posted November 18, 2007 Posted November 18, 2007 I don’t understand how simple select queries can cause such a high resource consumption. Me neither, but the table sessions is where a lot inserts and updates are done so the first to get corrupted one a shared server with perhaps too many sites and therefore under heavy load. Perhaps repairing the table (in phpMyAdmin) does the trick: repair table sessions; It wouldn't hurt to cut down on the number of queries though (unrelated to your problem) if you want to be nice to the hosting company (and your customers ;) ). There is a whole thread devoted to it: A Store Speed Optimization in Progress, Step by step from a vanilla install!.
jonnyu Posted November 20, 2007 Author Posted November 20, 2007 Me neither, but the table sessions is where a lot inserts and updates are done so the first to get corrupted one a shared server with perhaps too many sites and therefore under heavy load. Perhaps repairing the table (in phpMyAdmin) does the trick: repair table sessions; It wouldn't hurt to cut down on the number of queries though (unrelated to your problem) if you want to be nice to the hosting company (and your customers ;) ). There is a whole thread devoted to it: A Store Speed Optimization in Progress, Step by step from a vanilla install!. Hi Jan, thanks for the reply. I’m checking the whole post you gave me and there are a lot of posts since 2004. Should I read only the last page? I’m a little lost here because this is the first time I’m working with OSCommerce. I didn’t create the project, I’m just trying to solve the problem. Thanks for your support.
Jan Zonjee Posted November 20, 2007 Posted November 20, 2007 I’m checking the whole post you gave me and there are a lot of posts since 2004. Should I read only the last page?I’m a little lost here because this is the first time I’m working with OSCommerce. I didn’t create the project, I’m just trying to solve the problem. Then solve the problem first. The rest is icing on the cake. Are you sure the database is only 6 MB for instance? I think with RC1 the table sessions is not backed-up. I have read a few times that people had a very large table with session data. For some reason this was not cleaned out (which is done automatically) from old data. It would mean your site would be very slow too. I guess that should be a point of immediate attention for you.
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.