Problem with mysql and indexing

[fabriceb]

Hello everyone,

We have a problem with a multisites installation
We regularly overload on our server because there are a lot of mysql queries that are sleeping.
We believe that this is related to the indexing of the catalog and products. Regularly such indexation is not going to end.
This blocks the researches on the site.

Do some of you have had this problem? Do Someone have to change the server cofigurations to not block the database.

Thank you in advance for your feedback

[Pronto]

What indexing method you use? Update on Save or Manual?

[fabriceb]

Hi,

We use the "update on save" method

[Pronto]

Update on save does automatic index update whenever product is saved. This is not a big deal when your site is small: one view and few hundred products, but it can become major cause of overhead when you have several thousand products and couple of stores / views.

I usually turn off "Update on save" for my projects and use "Manual Update" instead. I prefer to update indices using cron during slow times at night.

[fabriceb]

Thank you for your answer,

It's right that we a have a couple of store/views and many products.

So we will try yo turn off the automatic update and use a cron to do it at night.

We will see if it resolves this problem.

Thanks