1. 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?

Discussion in 'Troubleshooting and Problems' started by OakleyForum, May 14, 2014.

  1. OakleyForum

    OakleyForum Active Member

    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?
  2. Brogan

    Brogan XenForo Moderator Staff Member

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

    OakleyForum Active Member

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

    OakleyForum Active Member

    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';
  5. Brogan

    Brogan XenForo Moderator Staff Member

    ACP Search: delayed insert.

    Disable it.
  6. OakleyForum

    OakleyForum Active Member

    Ok, attempting now.
  7. OakleyForum

    OakleyForum Active Member

    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?
  8. euantor

    euantor Well-Known Member

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

    Brogan XenForo Moderator Staff Member

  10. OakleyForum

    OakleyForum Active Member

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

    Mike XenForo Developer Staff Member

    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.)

Share This Page