Results 1 to 6 of 6

Thread: InnoDB issues

  1. #1
    Join Date
    Mar 2003
    Location
    Denver, CO
    Posts
    2

    Unanswered: InnoDB issues

    I apologize in advance if this has already been covered, but I searched through previous posts and couldn't find anything related to my issues.

    I run a site called BFStats - http://bfstats.com - which tracks people playing the multiplayer game Battlefield 1942. We have some fairly large tables; the one which holds the scores for each game is currently 5 million records. It's only going to get bigger as we make the system more accurate and add more game servers to our data pool.

    We're currently using MyISAM, which works OK, except that when we run our ranking script to show people how well they do against one another, it takes almost an hour. During this time the data insertion is interrupted, and anyone playing a game during that time is not seen. This is a huge issue for us.

    We've looked into InnoDB as an alternative, and have been working with it for about 2 weeks to try and switch over. Unfortunately, we're having a hell of a time getting data into and out of the tables! Our 5 million record table, for instance, took an entire day to copy to an InnoDB table (ouch!) and reading as few as 30 records from a 40,000 record table takes upwards of 15 seconds! This is on a PIII 1.8GHz machine with 512k of DDR RAM and a 7200RPM HDD, so I'm fairly sure it's not a hardware issue.

    Can anyone help me out here? I'm at my wit's end trying to get the supposedly super-fast InnoDB tables to return data within a day's time!

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

    Re: InnoDB issues

    Originally posted by Silicon
    I apologize in advance if this has already been covered, but I searched through previous posts and couldn't find anything related to my issues.

    I run a site called BFStats - http://bfstats.com - which tracks people playing the multiplayer game Battlefield 1942. We have some fairly large tables; the one which holds the scores for each game is currently 5 million records. It's only going to get bigger as we make the system more accurate and add more game servers to our data pool.

    We're currently using MyISAM, which works OK, except that when we run our ranking script to show people how well they do against one another, it takes almost an hour. During this time the data insertion is interrupted, and anyone playing a game during that time is not seen. This is a huge issue for us.

    We've looked into InnoDB as an alternative, and have been working with it for about 2 weeks to try and switch over. Unfortunately, we're having a hell of a time getting data into and out of the tables! Our 5 million record table, for instance, took an entire day to copy to an InnoDB table (ouch!) and reading as few as 30 records from a 40,000 record table takes upwards of 15 seconds! This is on a PIII 1.8GHz machine with 512k of DDR RAM and a 7200RPM HDD, so I'm fairly sure it's not a hardware issue.

    Can anyone help me out here? I'm at my wit's end trying to get the supposedly super-fast InnoDB tables to return data within a day's time!
    Well, can you provide the problematic queries and the MySQL EXPLAIN ? I have a few test databases with 50 million records here on a 256Mb DDR RAM 1.7Ghz machine and have really no performance issues. So 5 million records shouldn't be a problem at all unless you query against columns that are not indexed.

  3. #3
    Join Date
    Mar 2003
    Location
    Denver, CO
    Posts
    2
    The structure for our scores table is as follows (* = primary key):

    *pid bigint(32) - player ID
    *mid bigint(32) - map ID
    round int(8) - round #
    playtime int(8)
    score int(4)
    kills int(4)
    deaths int(4)
    team tinyint(1)

    The query I use to retrieve the data is simply this:

    SELECT * FROM scores LIMIT 0,30

    When I do an explain, I get this:

    table type possible_keys key key_len ref rows Extra
    scores ALL NULL NULL NULL NULL 0

    I also tried specifying the primary key, but it yielded the same result.

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by Silicon
    The structure for our scores table is as follows (* = primary key):

    *pid bigint(32) - player ID
    *mid bigint(32) - map ID
    round int(8) - round #
    playtime int(8)
    score int(4)
    kills int(4)
    deaths int(4)
    team tinyint(1)

    The query I use to retrieve the data is simply this:

    SELECT * FROM scores LIMIT 0,30

    When I do an explain, I get this:

    table type possible_keys key key_len ref rows Extra
    scores ALL NULL NULL NULL NULL 0

    I also tried specifying the primary key, but it yielded the same result.
    Bizarre... Tried a similar query with a 5 million table with rows 10 times the size of yours and got an answer in 1933ms.

    Even more bizarre is the fact that the explain shows 0 as the number of rows retrieved:
    scores ALL NULL NULL NULL NULL 0

    And what does the explain shows if you try something like:

    SELECT * FROM scores ORDER BY pid LIMIT 0, 30.

    BTW, tried on a 55 million rows table and gave me 46ms.

    How many connections do you have when you issue that query? It has to be something other than MySQL alone...
    What about using HIGH PRIORITY on the select? What about LOCK IN SHARE MODE ?

    I am pretty sure something lock up your query. 15 seconds is really not acceptable and since I can have a very fast response time on a machine with less RAM (256 Mb), equal processor and a table 10 times larger than yours show something's slowing down or blocking your query. When your query is waiting for 15 secs, what does SHOW INNODB STATUS tells you?

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by Silicon
    The structure for our scores table is as follows (* = primary key):

    *pid bigint(32) - player ID
    *mid bigint(32) - map ID
    round int(8) - round #
    playtime int(8)
    score int(4)
    kills int(4)
    deaths int(4)
    team tinyint(1)

    The query I use to retrieve the data is simply this:

    SELECT * FROM scores LIMIT 0,30

    When I do an explain, I get this:

    table type possible_keys key key_len ref rows Extra
    scores ALL NULL NULL NULL NULL 0

    I also tried specifying the primary key, but it yielded the same result.
    Fastest I could get was:

    SELECT HIGH_PRIORITY *
    FROM scores
    ORDER BY pid
    LIMIT 0, 30 LOCK IN SHARE MODE;

    But did notice something fun... Works fine for the first records that were inserted in the table... Goes up to 10 seconds when I get in the middle of my 50 million records table... And the explain shows the problem: MySQL has to do a full table scan since the possible_keys is NULL.

    Wouldn't it be better if you'd calculate the interval returned by LIMIT yourself? That way you could profit from the index.

    Something like:

    SELECT *
    FROM scores
    WHERE ID BETWEEN 0 AND 30
    ORDER BY ID;

    And just curious, why do you retrieve the first 30 records? Do you care about the order or which records are returned?

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

    Re: InnoDB issues

    Originally posted by Silicon
    I apologize in advance if this has already been covered, but I searched through previous posts and couldn't find anything related to my issues.

    I run a site called BFStats - http://bfstats.com - which tracks people playing the multiplayer game Battlefield 1942. We have some fairly large tables; the one which holds the scores for each game is currently 5 million records. It's only going to get bigger as we make the system more accurate and add more game servers to our data pool.

    We're currently using MyISAM, which works OK, except that when we run our ranking script to show people how well they do against one another, it takes almost an hour. During this time the data insertion is interrupted, and anyone playing a game during that time is not seen. This is a huge issue for us.

    We've looked into InnoDB as an alternative, and have been working with it for about 2 weeks to try and switch over. Unfortunately, we're having a hell of a time getting data into and out of the tables! Our 5 million record table, for instance, took an entire day to copy to an InnoDB table (ouch!) and reading as few as 30 records from a 40,000 record table takes upwards of 15 seconds! This is on a PIII 1.8GHz machine with 512k of DDR RAM and a 7200RPM HDD, so I'm fairly sure it's not a hardware issue.

    Can anyone help me out here? I'm at my wit's end trying to get the supposedly super-fast InnoDB tables to return data within a day's time!
    You can improve the performance of that type of query by using:

    ALTER TABLE myTable ORDER BY myPrimaryColumn.

    This works best with tables that aren't changed very often or are taken offline at night so you can keep them optimised in this way.

Posting Permissions

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