Professional Web Applications Themes

Performance Problem When Updating Member of a Unique Key - MySQL

I have a table similar to the example below with about 5 million records in it. When I attempt to update one of the member columns of a unique key the performance is awful. As the statement below indicate, the performance is great when we select via the unique key or when we update via the primary key. We can even perform the update 5 times faster when we look up the row via a column that is not indexed. Anyone know why MySQL would behave this way? Thanks in advance. Tim Dry Minden, NV CREATE TABLE `table_a` ( `col_a` ...

  1. #1

    Default Performance Problem When Updating Member of a Unique Key

    I have a table similar to the example below with about 5 million
    records in it. When I attempt to update one of the member columns of a
    unique key the performance is awful. As the statement below indicate,
    the performance is great when we select via the unique key or when we
    update via the primary key. We can even perform the update 5 times
    faster when we look up the row via a column that is not indexed.
    Anyone know why MySQL would behave this way? Thanks in advance.

    Tim Dry
    Minden, NV

    CREATE TABLE `table_a` (
    `col_a` bigint(20) NOT NULL auto_increment,
    `col_b` bigint(20) NOT NULL default '0',
    `col_c` int(11) default NULL,
    `col_d` varchar(10) default NULL,
    `col_e` varchar(255) default NULL,
    PRIMARY KEY (`col_a`),
    UNIQUE KEY `table_a_uk1` USING BTREE (`col_b`,`col_c`,`col_d`)
    ) ENGINE=InnoDB DEFAULT CHT=utf8;

    -- Selecting via the first two columns of the unique key returns
    instant results.
    select col_a, col_c, col_d from table_a where col_b = 3112 and col_c =
    2136251634;

    -- Updating a member of the unique key via the primary key returns
    instant results.
    update table_a set col_d = 123 where col_a = 423153;
    rollback;

    -- Updating the same member of the unique key via the first two parts
    of the unique key takes 2-4 minutes.
    update table_a set col_d = 123 where col_b = 3112 and col_c =
    2136251634;
    rollback;

    -- Even updating the same member of the unique key via col_e (which is
    not indexed) returns in about 30 seconds.
    update table_a set col_d = 123 where col_e = 'Unique String Value';

    tldry@charter.net Guest

  2. #2

    Default Re: Performance Problem When Updating Member of a Unique Key

    net wrote: 

    What version of MySQL, and what's the output from EXPLAIN on your update
    statement?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Performance Problem When Updating Member of a Unique Key

    Version 4.1.16. I do not know how to get to the execution plan for
    the problematic update. I don't believe "EXPLAIN" is implemented for
    updates in this version of MySQL. If you know a different way, I'd be
    happy to give it a shot. Thanks.

    -Tim

    Jerry Stuckle wrote: 
    >
    > What version of MySQL, and what's the output from EXPLAIN on your update
    > statement?
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    tldry@charter.net Guest

  4. #4

    Default Re: Performance Problem When Updating Member of a Unique Key

    net wrote: 
    >>
    >>What version of MySQL, and what's the output from EXPLAIN on your update
    >>statement?
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>net
    >>==================[/ref]
    >
    >
    > Version 4.1.16. I do not know how to get to the execution plan for
    > the problematic update. I don't believe "EXPLAIN" is implemented for
    > updates in this version of MySQL. If you know a different way, I'd be
    > happy to give it a shot. Thanks.
    >
    > -Tim
    >[/ref]

    (Top posting fixed)

    Ah, I misread your problem - the SELECT comes back immediately. OK,
    that throws out that idea.

    Are there other queries being performed on the table at the same time?
    I'm wondering if you're running into a delay due to locking - although I
    wouldn't expect it to be that long. It probably also wouldn't be
    predictable - sometimes it would go quickly, sometimes slowly, depending
    on what else is going on. But as a test what happens if your issue a
    LOCK TABLE before doing your update? Does the LOCK take a while to come
    back, then the UPDATE come back almost immediately? (Don't forget to
    unlock it :-) ).

    If this doesn't help with your problem, what does a SHOW ENGINE INNODB
    STATUS show while the UPDATE is running? SHOW FULL PROCESSLIST might
    give you some info, also.

    P.S. Please don't top post. Thanks.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Performance Problem When Updating Member of a Unique Key


    Jerry Stuckle wrote: 
    > >
    > >
    > > Version 4.1.16. I do not know how to get to the execution plan for
    > > the problematic update. I don't believe "EXPLAIN" is implemented for
    > > updates in this version of MySQL. If you know a different way, I'd be
    > > happy to give it a shot. Thanks.
    > >
    > > -Tim
    > >[/ref]
    >
    > (Top posting fixed)
    >
    > Ah, I misread your problem - the SELECT comes back immediately. OK,
    > that throws out that idea.
    >
    > Are there other queries being performed on the table at the same time?
    > I'm wondering if you're running into a delay due to locking - although I
    > wouldn't expect it to be that long. It probably also wouldn't be
    > predictable - sometimes it would go quickly, sometimes slowly, depending
    > on what else is going on. But as a test what happens if your issue a
    > LOCK TABLE before doing your update? Does the LOCK take a while to come
    > back, then the UPDATE come back almost immediately? (Don't forget to
    > unlock it :-) ).
    >
    > If this doesn't help with your problem, what does a SHOW ENGINE INNODB
    > STATUS show while the UPDATE is running? SHOW FULL PROCESSLIST might
    > give you some info, also.
    >
    > P.S. Please don't top post. Thanks.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Contention should not be an issue. This data has been ted to
    a development environment and the problem has been reproduced with 0
    other connections.

    "LOCK TABLE table_a write;" comes back immediately and yet the
    update statement still takes the same amount of time.

    Looking at the InnoDB status shows nothing of significance that I can
    see other than a query running and showing that it has a table lock.

    -Tim

    tldry@charter.net Guest

  6. #6

    Default Re: Performance Problem When Updating Member of a Unique Key

    net wrote: 
    >>
    >>(Top posting fixed)
    >>
    >>Ah, I misread your problem - the SELECT comes back immediately. OK,
    >>that throws out that idea.
    >>
    >>Are there other queries being performed on the table at the same time?
    >>I'm wondering if you're running into a delay due to locking - although I
    >>wouldn't expect it to be that long. It probably also wouldn't be
    >>predictable - sometimes it would go quickly, sometimes slowly, depending
    >>on what else is going on. But as a test what happens if your issue a
    >>LOCK TABLE before doing your update? Does the LOCK take a while to come
    >>back, then the UPDATE come back almost immediately? (Don't forget to
    >>unlock it :-) ).
    >>
    >>If this doesn't help with your problem, what does a SHOW ENGINE INNODB
    >>STATUS show while the UPDATE is running? SHOW FULL PROCESSLIST might
    >>give you some info, also.
    >>
    >>P.S. Please don't top post. Thanks.
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>net
    >>==================[/ref]
    >
    >
    > Contention should not be an issue. This data has been ted to
    > a development environment and the problem has been reproduced with 0
    > other connections.
    >
    > "LOCK TABLE table_a write;" comes back immediately and yet the
    > update statement still takes the same amount of time.
    >
    > Looking at the InnoDB status shows nothing of significance that I can
    > see other than a query running and showing that it has a table lock.
    >
    > -Tim
    >[/ref]

    That's strange.

    It really looks like it's doing a table scan instead of using your
    index. But I would think according to your indexes it should run faster.

    There is a similar problem in the MySQL bugs list, but that's back at
    4.1.3. The only resolution listed was they were going to upgrade to
    MySQL 5. Also, they were doing a join on two tables, and adding an
    index to the second table helped. You aren't doing that.

    Also, I haven't used BTREE indexes, but I wouldn't think that's a
    problem. I would think that would be quite efficient. I wonder how a
    non-BTREE index would work. I doubt it would be any faster, though.

    All I can think of is a problem in the query optimizer causing it to not
    optimize your conditions properly. You might put an item in the bug
    database on mysql.com. It sure shouldn't work that way.

    You could also upgrade MySQL - 4.1.21 is the latest in that sequence,
    and there shouldn't be any changes to cause you problems (famous last
    words!). Or you could upgrade to 5.0.26, but be aware there were some
    changes which may affect your system.

    Wish I could be of more help.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Problem updating unique mutiple records
    By sviolet in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: July 23rd, 11:19 PM
  2. PHP - Login and updating member records
    By d_dot_b in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: April 16th, 02:32 PM
  3. #26130 [NoF->Opn]: IBM DB2 Unique Key Problem
    By jay at nicwr dot mah dot nic dot in in forum PHP Development
    Replies: 0
    Last Post: November 18th, 05:21 AM
  4. #26130 [NEW]: IBM DB2 Unique Key Problem
    By jay at nicwr dot mah dot nic dot in in forum PHP Development
    Replies: 0
    Last Post: November 5th, 05:44 AM
  5. Updating cast member with "file" doesn't work with PHP
    By edenia webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: July 3rd, 10:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139