• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.3 Queries writing to tables instead of memory?

#1
I talked with my host today and was told I was expereincing higher than normal loads because:
Code:
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?
 
#4
This is our current config.php:

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

Mike

XenForo developer
Staff member
#11
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.)