Before And After

Discussion in 'NZ Computing' started by Lawrence D'Oliveiro, Sep 18, 2009.

  1. Customer was complaining that downloading a report was hanging the server.
    Check the machine, and sure enough, the mysqld process is using 150% CPU.
    Look through my code to see which likely query is taking so long.
    Substituted the values from the specific customer request, came up with
    this:

    mysql> explain extended select max(nrcalls) from (select count(*) as nrcalls from call_completions where projectid = "39" and number in
    (select number from numbers where listid = "52") group by number) as completions_temp;
    +----+--------------------+------------------+------+---------------+------+---------+------+--------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
    |
    +----+--------------------+------------------+------+---------------+------+---------+------+--------+----------------------------------------------+
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 466 |
    |
    | 2 | DERIVED | call_completions | ALL | NULL | NULL | NULL | NULL | 104007 | Using where; Using temporary;
    Using filesort |
    | 3 | DEPENDENT SUBQUERY | numbers | ALL | NULL | NULL | NULL | NULL | 115904 | Using where
    |
    +----+--------------------+------------------+------+---------------+------+---------+------+--------+----------------------------------------------+
    3 rows in set, 1 warning (2 min 2.07 sec)

    Never mind how long it takes to perform the query, it takes over _two
    minutes_ just to explain it to me! Notice it's not using any indexes in the
    query, so add a couple:

    mysql> alter table numbers add key (listid);
    Query OK, 115904 rows affected (2.63 sec)
    Records: 115904 Duplicates: 0 Warnings: 0

    mysql> alter table call_completions add key (projectid);
    Query OK, 104007 rows affected (1.12 sec)
    Records: 104007 Duplicates: 0 Warnings: 0

    Rerun the explain select:

    mysql> explain extended select max(nrcalls) from (select count(*) as nrcalls from call_completions where projectid = "39" and number in
    (select number from numbers where listid = "52") group by number) as completions_temp;
    +----+--------------------+------------------+------+---------------+-----------+---------+------+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
    |
    +----+--------------------+------------------+------+---------------+-----------+---------+------+------+----------------------------------------------+
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 466 |
    |
    | 2 | DERIVED | call_completions | ref | projectid | projectid | 8 | | 466 | Using where; Using temporary;
    Using filesort |
    | 3 | DEPENDENT SUBQUERY | numbers | ref | listid | listid | 8 | | 2973 | Using where
    |
    +----+--------------------+------------------+------+---------------+-----------+---------+------+------+----------------------------------------------+
    3 rows in set, 1 warning (2.95 sec)

    Ah, much better--see how the row counts have dropped? And when I request the
    original report that was triggering the problem, I now get it in a
    reasonable time, too.
     
    Lawrence D'Oliveiro, Sep 18, 2009
    #1
    1. Advertising

  2. Lawrence D'Oliveiro

    Enkidu Guest

    Lawrence D'Oliveiro wrote:
    >
    > Customer was complaining that downloading a report was hanging the server.
    > Check the machine, and sure enough, the mysqld process is using 150% CPU.
    > Look through my code to see which likely query is taking so long.
    > Substituted the values from the specific customer request, came up with
    > this:
    >

    mysql

    Sure enough, that's the problem. Get a proper database.

    Cheers,

    Cliff

    --

    The Internet is interesting in that although the nicknames may change,
    the same old personalities show through.
     
    Enkidu, Sep 18, 2009
    #2
    1. Advertising

  3. Lawrence D'Oliveiro

    EMB Guest

    Enkidu wrote:
    > Lawrence D'Oliveiro wrote:
    > >
    >> Customer was complaining that downloading a report was hanging the
    >> server.
    >> Check the machine, and sure enough, the mysqld process is using 150% CPU.
    >> Look through my code to see which likely query is taking so long.
    >> Substituted the values from the specific customer request, came up with
    >> this:
    >>

    > mysql
    >
    > Sure enough, that's the problem. Get a proper database.


    Get a proper DBA would be a better step.
     
    EMB, Sep 18, 2009
    #3
  4. Lawrence D'Oliveiro

    Carnations Guest

    On Fri, 18 Sep 2009 21:38:57 +1200, Lawrence D'Oliveiro wrote:

    > Customer was complaining that downloading a report was hanging the
    > server. Check the machine, and sure enough, the mysqld process is using
    > 150% CPU.


    How can a process use 150% of the available CPU time!!?


    --
    "Filtering the Internet is like trying to boil the ocean"
     
    Carnations, Sep 19, 2009
    #4
  5. Lawrence D'Oliveiro

    Enkidu Guest

    Carnations wrote:
    > On Fri, 18 Sep 2009 21:38:57 +1200, Lawrence D'Oliveiro wrote:
    >
    >> Customer was complaining that downloading a report was hanging the
    >> server. Check the machine, and sure enough, the mysqld process is using
    >> 150% CPU.

    >
    > How can a process use 150% of the available CPU time!!?
    >

    Multiple CPUs.

    Cheers,

    Cliff

    --

    The Internet is interesting in that although the nicknames may change,
    the same old personalities show through.
     
    Enkidu, Sep 19, 2009
    #5
  6. In message <>, Carnations wrote:

    > On Fri, 18 Sep 2009 21:38:57 +1200, Lawrence D'Oliveiro wrote:
    >
    >> Customer was complaining that downloading a report was hanging the
    >> server. Check the machine, and sure enough, the mysqld process is using
    >> 150% CPU.

    >
    > How can a process use 150% of the available CPU time!!?


    Actually, I might have read the wrong column in top :).

    Certainly it was churning away like mad without making much progress, until
    I fixed it.
     
    Lawrence D'Oliveiro, Sep 20, 2009
    #6
  7. Lawrence D'Oliveiro

    Gordon Guest

    On 2009-09-19, Carnations <> wrote:
    > On Fri, 18 Sep 2009 21:38:57 +1200, Lawrence D'Oliveiro wrote:
    >
    >> Customer was complaining that downloading a report was hanging the
    >> server. Check the machine, and sure enough, the mysqld process is using
    >> 150% CPU.

    >
    > How can a process use 150% of the available CPU time!!?
    >
    >

    Mutli core CPUs now rule okay?
     
    Gordon, Sep 20, 2009
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Felix Havemann

    IP accounting before/after ACL?

    Felix Havemann, Jun 1, 2004, in forum: Cisco
    Replies:
    5
    Views:
    715
    Felix Havemann
    Jun 2, 2004
  2. matt r

    Before Sunset & Before Sunrise

    matt r, Nov 24, 2004, in forum: DVD Video
    Replies:
    2
    Views:
    569
    matt r
    Nov 25, 2004
  3. =?Utf-8?B?Z3JhY2U=?=

    how do you insert a section break before and after an embedded obj

    =?Utf-8?B?Z3JhY2U=?=, Jul 14, 2006, in forum: Microsoft Certification
    Replies:
    0
    Views:
    566
    =?Utf-8?B?Z3JhY2U=?=
    Jul 14, 2006
  4. embee

    Before and After sensor cleaning

    embee, Dec 30, 2004, in forum: Digital Photography
    Replies:
    2
    Views:
    406
    Stacey
    Dec 31, 2004
  5. Luc Henrot
    Replies:
    3
    Views:
    487
    Carlos
    Jun 20, 2008
Loading...

Share This Page