GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql profiler help

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

  • mysql profiler help

    Hi ! I was on your website http://www.mysqlperformanceblog.com/ and read full of interesting article but i had a problem with mysl profiler. I was looking answer everywhere but didn't find so i think maybe You can help me.

    So:

    I'm trying to use mysql profiler.
    I'm using the command:

    set profiling=1;
    to turn it on.
    Later, i execute my query which is correlated subquery with EXISTS.
    It looks like:
    SELECT * FROM A1 WHERE EXISTS (SELECT B1 FROM B2 WHERE B3=A2);

    After that i use command:
    show profile;
    to see id of my query;
    mysql> show profiles;
    +----------+------------+--
    | Query_ID | Duration| Query
    +----------+------------+-
    | 1 |1224.1249092 select ...
    +----------+------------+-

    But when i'm doing query to information_schema.profiling for this query duration is much smaller. Why is such difference? Always displays 100 rows, and most of it is 'sending data'. I can't see 'starting' or 'inicialization' or 'opening tables'. When i increase limit to 200 also displays 100 rows.

    When i'm doing:
    select count(*) from information_schema.profiling where query_id=1;
    It shows:
    # count(*) '100'

    But when i'm doing:
    select * from information_schema.profiling where query_id=1;
    SEQ starts from 1099931 and shows last 100 SEQ.

    mysql> select sum(duration) from information_schema.profiling where query_id=1;

    # sum(duration)
    12.234170

    mysql> show profile for query 1;
    executing 0.000003
    Sending data 0.004343
    executing 0.000003
    Sending data 0.293625
    executing 0.000003
    Sending data 0.294627
    executing 0.000003
    Sending data 0.293227
    executing 0.000003
    Sending data 0.293953
    executing 0.000004
    Sending data 0.293879
    executing 0.000004
    Sending data 0.293932
    executing 0.000003
    Sending data 0.293652
    executing 0.000003
    Sending data 0.003999
    executing 0.000002
    Sending data 0.292655
    executing 0.000004
    Sending data 0.295063
    executing 0.000005
    Sending data 0.296534
    executing 0.000003
    Sending data 0.295801
    executing 0.000004
    Sending data 0.296010
    executing 0.000004
    Sending data 0.004082
    executing 0.000003
    Sending data 0.297623
    executing 0.000004
    Sending data 0.317437
    executing 0.000003
    Sending data 0.301188
    executing 0.000004
    Sending data 0.301276
    executing 0.000004
    Sending data 0.295963
    executing 0.000004
    Sending data 0.300331
    executing 0.000004
    Sending data 0.296260
    executing 0.000003
    Sending data 0.004008
    executing 0.000002
    Sending data 0.294382
    executing 0.000004
    Sending data 0.310844
    executing 0.000005
    Sending data 0.295127
    executing 0.000006
    Sending data 0.005463
    executing 0.000003
    Sending data 0.296227
    executing 0.000003
    Sending data 0.302356
    executing 0.000004
    Sending data 0.294737
    executing 0.000004
    Sending data 0.295317
    executing 0.000004
    Sending data 0.314639
    executing 0.000003
    Sending data 0.296097
    executing 0.000005
    Sending data 0.297066
    executing 0.000003
    Sending data 0.294738
    executing 0.000003
    Sending data 0.315274
    executing 0.000004
    Sending data 0.296043
    executing 0.000003
    Sending data 0.293831
    executing 0.000003
    Sending data 0.294903
    executing 0.000003
    Sending data 0.293740
    executing 0.000003
    Sending data 0.294724
    executing 0.000005
    Sending data 0.297695
    executing 0.000004
    Sending data 0.294476
    executing 0.000003
    Sending data 0.297282
    executing 0.000004
    Sending data 0.298081
    executing 0.000003
    Sending data 0.297057
    executing 0.000004
    Sending data 0.004090
    end 0.000063
    query end 0.000006
    freeing items 0.000267
    logging slow query 0.000002
    logging slow query 0.000003
    cleaning up 0.000004

    MySQL version 5.6.14 but I also try 5.5 and 5.1 and result is the same.
    Any help? Please ! I'm helpless!


    -- Izabela Stalończyk
Working...
X