-1

On a

  • MySQL database (version 5.1.73)
  • a Magento-Shop

executes a statement (A) that uses distinct, see

http://pastebin.com/A2sy45nF

and therefore is "Using temporary" according to "explain".

Under certain load conditions (when an involved table is updated and the cached query becomes invalid) the statements are waiting in the State "Copying to tmp table", this happens for example for 24 clients, see

http://www.directupload.net/file/d/3809/8xqjqk4p_png.htm

It has been checked that the internal tmp tables that MySQL creates for statement (A) are indeed counted as Created_tmp_tables and not as Created_tmp_disk_tables. This is consistent with the fact that the threads are in the state "Copying to tmp table".

The observation leads me to the hypothesis that

insufficent memory for the innodb or the database as a whole prevents the creation of the tmp tables and this leads to callapse of the system.

Questions:

  • How can we prove or reject this hypothesis?
  • What are promising changes in the datbase configuraiton to prevent the collapse?

This question might be related to

Query getting periodically stuck in 'copying to tmp table' state, never completes,

but has

  • a different context (Magento vs. WordPress)
  • and other aspects (several instances of the query).

With best regards,

Andreas

0

The application now is stable again.

As a first step the innodb_thread_concurrency was reduced 2 * number of cores. That seems to reduce the problem slightly.

But in the end the statement that the Magento-Shop has issued has been replace by a simpler statement that is adequate for the special situation of the concrete shop. The shop uses only one attribute set and therefore the result is independent from the catalog_product_entity table. The modified statement is now so simple that the result can always taken from the query cache.

With best regards,

Andreas

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.