XF 1.3 Queries writing to tables instead of memory?

[OakleyForum]

I talked with my host today and was told I was expereincing higher than normal loads because:

root@host.oakleyforum.com [~]# mysql -e "show global status where Variable_name like '%tmp%disk%'"
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 129687 |
+-------------------------+--------+
=------

Your queries are creating temporary tables on the disk instead of in memory. This causes each of these queries that are created the temp tables on the disk to take at least 10x longer to complete. This causes IO wait which will increase the overall CPU load.

You will need to have your developer look over your MySQL queries especially the following to determine how to optimize the queries to avoid creating temporary tables on the disk.
=-----
root@host.oakleyforum.com [~]# mysqladmin proc|egrep oak
| 374093 | DELAYED | localhost | oakleyfo_forumdb | Delayed insert | 2 | Waiting for INSERT | |
| 374345 | DELAYED | localhost | oakleyfo_forumdb | Delayed insert | 160 | Waiting for INSERT | |

Is there anything I can do about this?

 

[Paul B]

You can disable delayed inserts in the ACP if it is causing issues.

 

[OakleyForum]

You can disable delayed inserts in the ACP if it is causing issues.

How would I go about this? And would this solve the issue of writing to hard disk instead of in memory?

 

[OakleyForum]

This is our current config.php:

## Caching
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions'] = array(
                                        'caching'                      => true,
                                        'automatic_serialization'      => true,
                                        'lifetime'                      => 10800,
                                        'cache_id_prefix' => 'xf'
);
$config['cache']['backend'] = 'Apc';

 

[Paul B]

ACP Search: delayed insert.

Disable it.

 

[OakleyForum]

Ok, attempting now.

 

[OakleyForum]

ACP Search: delayed insert.

Disable it.

I couldn't find it, and even asked my host and they couldn't find how to disable it either. Do you know specifically how you go about doing this? Like what needs to be altered or inserted in the file?

 

[euantor]

You don't need to change any files. It is a setting within the XenForo admin area of your forum.

 

[Paul B]

 

[OakleyForum]

View attachment 73703

Sorry thought it was something to be disabled in PHP. Thanks for the patience. Disabled, and hopefully this fixes it.

 

[Mike]

I should note that delayed inserts really don't have anything to do with the temp disk tables stat you mentioned. See: http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html#statvar_Created_tmp_disk_tables I should also note that one snapshot of a particular MySQL variable isn't that useful. It needs to be snapshotted over time to determine an actual per-second value.

High load is often something that can't be trivially investigated. It may need full query analysis to determine causes and then determine how to resolve them. (This is the sort of thing specialists charge $300/hour for.)