Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12

    Unanswered: performance problems with large tables

    I have massive perfomance problems on tables with about 400.000 to 500.000 rows.
    A single select on one table with one restriction takes 150-200 sec.
    When joining two such tables together, i have to kill the mysql server process because the comp is completely blocked for a very long time (forever?)

    I'm using MySQL 4.0.8 on Win2k with MyODBC 3.51.
    Indexes are on all columns which are involved in joins or restrictions.
    Table types are always InnoDB because of transaction handling. Tried to switch (just for test purposes) to MyISAM, no changes.

    The same statements work on an Oracle database (via OCI or ODBC) in less than one second (same table structure, same indexes, same amount of data, same SQL statement).

    Could somebody tell me, which server parameters are relevant for this problem and which values they should (at least) have?

    Thx

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    A single select on one table with one restriction takes 150-200 sec.
    I'm not surprised that you have a problem with your join, then. That's a very long time for a select on a table with a half million rows. You should be less than a second for a select like that.

    What do you get if you run

    mysql> SELECT BENCHMARK(1000000,1+1);

    ?
    Bradley

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: performance problems with large tables

    Originally posted by frankn
    I have massive perfomance problems on tables with about 400.000 to 500.000 rows.
    A single select on one table with one restriction takes 150-200 sec.
    When joining two such tables together, i have to kill the mysql server process because the comp is completely blocked for a very long time (forever?)

    I'm using MySQL 4.0.8 on Win2k with MyODBC 3.51.
    Indexes are on all columns which are involved in joins or restrictions.
    Table types are always InnoDB because of transaction handling. Tried to switch (just for test purposes) to MyISAM, no changes.

    The same statements work on an Oracle database (via OCI or ODBC) in less than one second (same table structure, same indexes, same amount of data, same SQL statement).

    Could somebody tell me, which server parameters are relevant for this problem and which values they should (at least) have?

    Thx
    Can you send a "describe" of the tables involved, the problematic query as well as an explain of the problematic query?

    I have a few tables of 30+ columns and a few million rows and get good performance with the same setup as yours... There has to be a big problem in your design or indexes!

  4. #4
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    back at work i could locate the problem a little bit more in detail:
    Code:
    # Table structure for table 'tw_grp'
    
    CREATE TABLE `tw_grp` (
      `obj_id` varchar(156) default NULL,
      `ver_num` bigint(20) default NULL,
      `ord_num` bigint(20) default NULL,
      `type` bigint(20) default NULL,
    ...
      KEY `tw_grp_ord_num` (`ord_num`),
      KEY `tw_grp_obj_id` (`obj_id`)
    ) TYPE=InnoDB;
    
    # Table structure for table 'tw_line'
    
    CREATE TABLE `tw_line` (
      `obj_id` varchar(156) default NULL,
      `ver_num` bigint(20) default NULL,
      `ord_num` bigint(20) default NULL,
      `vtx_num` bigint(20) default NULL,
    ...
      KEY `tw_line_obj_id` (`obj_id`),
      KEY `tw_line_ord_num` (`ord_num`),
      KEY `tw_line_vtx_num` (`vtx_num`)
    ) TYPE=InnoDB;
    tw_grp has about 430.000 records, tw_line has about 240.000 records.

    I run the following statement:

    Code:
    SELECT tw_grp.meaning, tw_grp.colour, tw_grp.thickness, tw_grp.ord_num, tw_grp.type, tw_grp.layer, tw_grp.figure,
           tw_grp.macro, tw_grp.ttechno, tw_grp.tsymtype, tw_grp.penno, tw_grp.areapen, tw_grp.contflag, tw_grp.ateflag,
           tw_grp.needflag, tw_grp.closeflg, tw_grp.tbareflag, tw_grp.tdiagflag, tw_grp.tplace, tw_grp.theight, tw_grp.tzoffset,
           tw_grp.tinsertid, tw_grp.tmaterial, tw_grp.tfillflag, tw_grp.tfillarea, tw_grp.twraptype, tw_grp.twrapnum,
           tw_line.l_style, tw_line.x1, tw_line.y1, tw_line.x2, tw_line.y2, tw_line.vtx_num, tw_line.f_pattern, tw_line.angle,
           tw_line.seg_type, tw_line.cut_out_nr, tw_line.ord_num
    FROM tw_grp INNER JOIN tw_line ON tw_grp.obj_id = tw_line.obj_id AND tw_grp.ord_num = tw_line.ord_num
    WHERE (tw_grp.obj_id = BINARY '002000100101:100101:ANSI:0001')
    ORDER BY tw_line.ord_num, tw_line.vtx_num;
    # Items selected: 14
    # Duration 0.016 seconds
    this works fine, but when i change the restriction to

    Code:
    ... WHERE (tw_grp.obj_id LIKE BINARY '002000cs5012a:cs5012a:GRAY\_BOX:0001') ...
    the server comes to its limits. no answer in 10 min.

    So it seems to be a problem of the LIKE clause, maybe combined with BINARY.
    I have to use BINARY comparison in all cases to ensure case sensitive comparisons.
    Unfortunately, I have to switch from = comparison to LIKE comparison when the restriction contains special characters, which have to be escaped, like underscore in this case.

    Any ideas how to solve this problem?

    Thanks, FrankN

  5. #5
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    p.s. i let the query mentioned above run to end, here the (shocking ) result:

    Code:
    ... WHERE (tw_grp.obj_id LIKE BINARY '002000cs5012a:cs5012a:GRAY\_BOX:0001') ...
    # Items selected: 46
    # Duration 2497.062 seconds
    the same statements in oracle (9i, server on local machine, mysql server on local machine too)

    Code:
    # equal operator
    ... WHERE (tw_grp.obj_id = '002000100101:100101:ANSI:0001') ... 
    # Items selected: 14
    # Duration 0.063 seconds
    
    # like operator
    ... WHERE (tw_grp.obj_id LIKE '002000cs5012a:cs5012a:GRAY\_BOX:0001' escape '\\') ...
    # Items selected: 46
    # Duration 1.610 seconds
    There is also a "LIKE penalty" on Oracle but not as significant as in MySQL...

  6. #6
    Join Date
    Feb 2003
    Location
    Germany, Grimma
    Posts
    2
    Yes, maybe, part from the MySQL-Docu:
    "Note that in some context MySQL will not be able to use the index efficiently when you cast an indexed column to BINARY."

    Additional: I don't like to set table-values per default to 'NULL', I define tables-columns as 'not null'

    Try to use left join instead of inner join.

    There is also a "LIKE penalty" on Oracle but not as significant as in MySQL... [/SIZE][/QUOTE]

  7. #7
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    the problem is indeed the BINARY compare.
    trying without BINARY i get a very good performance:
    Code:
    ... WHERE (tw_grp.obj_id = '002000100101:100101:ANSI:0001') ...
    # Items selected: 14
    # Duration 0.016 seconds
    
    ... WHERE (tw_grp.obj_id LIKE '002000cs5012a:cs5012a:GRAY\_BOX:0001') ...
    # Items selected: 46
    # Duration 0.078 seconds
    this would work perfectly, but now i lost my case sensitivity...
    has anybody an idea, how to realize case sensitiveness in another way, which is acceptable in terms of performance?

    thanks, frankn

  8. #8
    Join Date
    Feb 2003
    Location
    Germany, Grimma
    Posts
    2
    You're using a very sophisticated binary construct. Tell me something 'bout this. I hope, there is no character-Set-Translation.

    Then, try this;

    ... where xyz like binary 'Grey\_Box' [escape '\\'];
    rgds

  9. #9
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    finally i found the right solution, maybe it is interesting for others too.
    my thoughts were too complicated. simply declare the column as "VARCHAR(xx) BINARY" and not the restriction literal.
    then it works perfectly.

    the documentation was a little bit confusing in this case. it says that a comparison will be case sensitive when at least one of the operands are declared as BINARY. so i decided to cast the restriction to BINARY. that was the wrong idea.

    when the column is BINARY, the index will be used.
    when the restriction literal is cast to BINARY, the index will not be used.

    hope this helps also other people.

  10. #10
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by frankn
    finally i found the right solution, maybe it is interesting for others too.
    my thoughts were too complicated. simply declare the column as "VARCHAR(xx) BINARY" and not the restriction literal.
    then it works perfectly.

    the documentation was a little bit confusing in this case. it says that a comparison will be case sensitive when at least one of the operands are declared as BINARY. so i decided to cast the restriction to BINARY. that was the wrong idea.

    when the column is BINARY, the index will be used.
    when the restriction literal is cast to BINARY, the index will not be used.

    hope this helps also other people.
    Just curious, how much time does your query take now?

  11. #11
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    Here again an interesting comparison:

    1. bad idea: cast the restriction literal to BINARY
    Code:
    SELECT ... FROM ... WHERE (tw_grp.obj_id LIKE BINARY '002000cs5012a:cs5012a:GRAY\_BOX:0001') ...
    # Items selected: 46
    # Duration 2497.062 seconds
    2. good idea: declare all varchar columns as VARCHAR(x) BINARY
    Code:
    SELECT ... FROM ... WHERE (tw_grp.obj_id LIKE '002000cs5012a:cs5012a:GRAY\_BOX:0001') ...
    # Items selected: 46
    # Duration 0.156 seconds
    strange, isn't it? maybe a bug?
    but no problem if one knows how to handle it...

Posting Permissions

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