-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

closed as off-topic by RolandoMySQLDBA, Mark Storey-Smith, ypercubeᵀᴹ, RLF, Paul White Dec 17 '14 at 11:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Mark Storey-Smith, ypercubeᵀᴹ, RLF, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

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

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