Announcement

Announcement Module
Collapse
No announcement yet.

"freeing items"

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • "freeing items"

    Hi

    My query which contributes the highest to server load is one which runs something like the following:

    UPDATE VgsmEngines SET routingReady=true WHERE channel='SIP/jhb-ecncore-vgsm20/9*'


    A profile shows:

    mysql> show profile for query 6;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000085 || checking permissions | 0.000008 || Opening tables | 0.000014 || System lock | 0.000007 || Table lock | 0.000007 || init | 0.000065 || Updating | 0.000081 || end | 0.000017 || query end | 0.000005 || freeing items | 0.139479 || logging slow query | 0.000013 || logging slow query | 0.000017 || cleaning up | 0.000005 |+----------------------+----------+13 rows in set (0.00 sec)


    So, most of the time is in 'freeing items'

    I can't find much info about this state, apart from :
    Quote:


    freeing items
    The thread has executed a command. This state is usually followed by cleaning up.


    What can I do to improve the execution time of these queries. What extra info would be helpful in diagnosing this issue?

    Thanks
    Chris

  • #2
    I'm not positive, but I believe that has to do with invalidating queries in the query cache that reference the table VgsmEngines.

    You may wish to disable the query cache by default by setting query_cache_type=2, and changing your SELECTs to SELECT SQL_CACHE for the queries you wish to cache (ones that reference tables that rarely change).

    http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#sysvar_query_cache_type

    Comment


    • #3
      We are having the same problem. Query Cache is off, IO is 10% of capacity, is the master in a master-slave replication setup. Anyone know what could cause this?

      Comment


      • #4
        To answer this, we need to understand what the server does in this state. I took a brief look at the source and it looks like a lot of things happen in this state. So unless I look at a lot of code, I think this is best to debug with an external tool such as gdb or oprofile. If you can duplicate this problem in a test server, I would just run the query in an infinite loop, and oprofile the server -- and if that does not show anything useful, then use poormansprofiler to try to find out more.

        Comment

        Working...
        X