- MySQL database (version 5.1.73)
- a Magento-Shop
executes a statement (A) that uses distinct, see
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
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.
- 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
- a different context (Magento vs. WordPress)
- and other aspects (several instances of the query).
With best regards,