MySql settings issue in Virtualmin - query timing out, works fine outside

1 post / 0 new
#1 Thu, 06/24/2010 - 01:48
ssomu007

MySql settings issue in Virtualmin - query timing out, works fine outside

i have this situation.

after some period of inactivity my website sqls times out (only some not all)

Testing results

  1. when execute a simple sql like (select count(*) from products) this works fine all the time.
  2. when execute below sql

SELECT products.pid FROM products INNER JOIN catalog ON products.cid=catalog.cid WHERE products.is_visible='Yes' AND ( products.inventory_control = 'No' OR products.stock > 0 OR products.inventory_rule = 'OutOfStock' OR (products.inventory_control = 'AttrRuleInc' AND products.stock >= 0) ) AND products.is_home='Yes' GROUP BY products.pid

it times out ( Note it will not timeout all the time, this happens after 1 hour or 2 hour of inactivity)

  1. The very first execution takes 30+ seconds and it times out and after that the above executing SQL 2 to 3 times the 4th time onwards it executes fast in 3 to 5 seconds

after 1 or 2 hour inactivity this pattern repeats.

I did not do any changes in settings.

i have 2 sites on this server.

1st server has 20,000 rows of in database ( this one works fine ) 2nd site has 150,000 rows in the databse ( this one is having this issue)

So this is something "query time out"

i do not think this is something on SQL settings, if it is then i should see this behavior on both sites

can anyone help me