admin/stats page takes ages to load

Question

I'm finding that the admin/stats page is taking a long time to load nowadays, and in fact on localhost just hit the maximum 30 seconds execution time. From the debug mode I understand that it's running a lot of queries to obtain stats.

The slowest by a long way is "SHOW TABLE STATUS" (6+ seconds) which seems to only be for getting the size of the database. Also this is getting the ENTIRE database, not just the current Q2A installation. On my site I have 3 Q2A instances plus all the tables for my whole site.

Perhaps we could use "SHOW TABLE STATUS LIKE 'qa\_%'" (with the correct prefix for current Q2A) - that one still takes about a second for me but it's a big improvement.

Comments

It's a good idea. I'll check into it for Q2A 1.6.3. Might need two such queries for cases where multiple sites are sharing a user database.

Answer 1

Here's the drop-in replacement for function qa_db_table_size() in qa-db-admin.php:

  function qa_db_table_size()

/*

  Return the total size in bytes of all relevant tables in the Q2A database

*/

  {

    if (defined('QA_MYSQL_USERS_PREFIX')) { // check if one of the prefixes is a prefix itself of the other

      if (stripos(QA_MYSQL_USERS_PREFIX, QA_MYSQL_TABLE_PREFIX)===0)

        $prefixes=array(QA_MYSQL_TABLE_PREFIX);

      elseif (stripos(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX)===0)

        $prefixes=array(QA_MYSQL_USERS_PREFIX);

      else

        $prefixes=array(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX);

    

    } else

      $prefixes=array(QA_MYSQL_TABLE_PREFIX);

      

    $size=0;

    foreach ($prefixes as $prefix) {

      $statuses=qa_db_read_all_assoc(qa_db_query_raw(

        "SHOW TABLE STATUS LIKE '".$prefix."%'"

      ));

 

      foreach ($statuses as $status)

        $size+=$status['Data_length']+$status['Index_length'];

    }

    

    return $size;

  }

 

Note that if you share user tables across Q2A installations, the data for those users will be included on every admin/stats page, so totalling them up with cause double counting.

This fix will be rolled into Q2A 1.6.3.