Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-9813

fulltext_native.php on innodb loading deadly slow for big indexes

    Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.0.7-PL1
    • Fix Version/s: 3.0.11-RC1
    • Component/s: ACP
    • Labels:
      None
    • Environment:
      php-5.3.3 mysql 5.1.37

      Description

      when using INNODB and having a BIG search index (like 25 million entries) on heavily loaded database, opening the maintenance->search index page is deadly slow (like 5+ minutes to load).

      After tracing the issue, I notices phpBB is issuing a SELECT count on that tables, which causes a full table scan on innoDB.
      It would be better to use SHOW TABLE STATUS here to obtain the row count.

      I'll attack a patch that uses show table status instead count when using MySQL.

        Issue Links

          Activity

          Hide
          igorw Igor Wiedler [X] (Inactive) added a comment -

          According to this thread: http://forums.mysql.com/read.php?22,56366,56366 SHOW TABLE STATUS is inaccurate.

          Show
          igorw Igor Wiedler [X] (Inactive) added a comment - According to this thread: http://forums.mysql.com/read.php?22,56366,56366 SHOW TABLE STATUS is inaccurate.
          Hide
          igorw Igor Wiedler [X] (Inactive) added a comment -

          More information and a possibly better approach can be found here: http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/

          Show
          igorw Igor Wiedler [X] (Inactive) added a comment - More information and a possibly better approach can be found here: http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/
          Hide
          chibisuke chibisuke added a comment -

          Only on innoDB. For MyISAM (where the problem doesn't exist) it gives correct values, so there is no harm here.

          For InnoDB Yes it gives slightly inaccurate values, but I'd prefer inaccurate values, then a PHP-Timeout while killing my database.

          Show
          chibisuke chibisuke added a comment - Only on innoDB. For MyISAM (where the problem doesn't exist) it gives correct values, so there is no harm here. For InnoDB Yes it gives slightly inaccurate values, but I'd prefer inaccurate values, then a PHP-Timeout while killing my database.
          Hide
          chibisuke chibisuke added a comment -

          thx for the hint on the index approach, but that doesn't work on this tables.

          The index-hint aproach requires a unique index to be available, for which a cardinality is stored. (means NO split index, like it is on the phpbb_search_wordmatch table).

          The best result on this 23 million lines table I can get is 1 minute and 45 seconds using an index hint. Which is still better then 4 minutes on the original query, but still unacceptable, as the fastcgi processor kills the request after 60 seconds.

          The only way this index-hint aproach would be working would be adding a auto_increment column to the table and specify it as a primary key. But there is no point in doing so, for just the statistic feature.

          I don't think the numbers themself are that important that they need to be 100% accurate.
          On my system I got a deviation of less than 5%, which is fully acceptable for me.

          Show
          chibisuke chibisuke added a comment - thx for the hint on the index approach, but that doesn't work on this tables. The index-hint aproach requires a unique index to be available, for which a cardinality is stored. (means NO split index, like it is on the phpbb_search_wordmatch table). The best result on this 23 million lines table I can get is 1 minute and 45 seconds using an index hint. Which is still better then 4 minutes on the original query, but still unacceptable, as the fastcgi processor kills the request after 60 seconds. The only way this index-hint aproach would be working would be adding a auto_increment column to the table and specify it as a primary key. But there is no point in doing so, for just the statistic feature. I don't think the numbers themself are that important that they need to be 100% accurate. On my system I got a deviation of less than 5%, which is fully acceptable for me.
          Hide
          igorw Igor Wiedler [X] (Inactive) added a comment -

          Having an inaccurate statistic is better than none at all (maybe it should be stated that it might be inaccurate, in the language files). includes/functions_admin.php:get_database_size() includes a check for InnoDB. Perhaps this check could be included to only run the SHOW TABLE STATUS query on InnoDB.

          Show
          igorw Igor Wiedler [X] (Inactive) added a comment - Having an inaccurate statistic is better than none at all (maybe it should be stated that it might be inaccurate, in the language files). includes/functions_admin.php:get_database_size() includes a check for InnoDB. Perhaps this check could be included to only run the SHOW TABLE STATUS query on InnoDB.
          Hide
          Oleg Oleg [X] (Inactive) added a comment -

          Very nice. Add a warning that the table name should be escaped, or maybe escape it?

          Show
          Oleg Oleg [X] (Inactive) added a comment - Very nice. Add a warning that the table name should be escaped, or maybe escape it?
          Hide
          bantu Andreas Fischer added a comment -

          Yeah, can do that. But maybe code to calculate row count should go into dbal.

          Show
          bantu Andreas Fischer added a comment - Yeah, can do that. But maybe code to calculate row count should go into dbal.

            People

            • Assignee:
              bantu Andreas Fischer
              Reporter:
              chibisuke chibisuke
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development