Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: [1]   Go Down

Author Topic: Misleading database query statistics  (Read 13731 times)

0 Members and 1 Guest are viewing this topic.

Veronica

  • Contributor
  • Coppermine frequent poster
  • ***
  • Country: se
  • Offline Offline
  • Posts: 106
  • Coppermine 1.5.22
Misleading database query statistics
« on: January 12, 2013, 04:14:09 pm »

In debug mode all the time measurements for db query times are misleading
as all the precision is removed before calculating the sum of the individual db query times.

A db query time of up to 0.499 ms is counted as 0 ms and if you have 100 such queries
the total time in Coppermine will be reported as 0 ms even if the real time used is 49.9 ms.

Db query times between 0.5 and 1.49 ms are all reported as 1 ms etc

At my shared web hosting most db query times are less than 0.5 ms.

Also the time to connect to the database is not included in the db query time
and my typical time for a db connect is 2.5 ms
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Misleading database query statistics
« Reply #1 on: January 17, 2013, 04:51:31 pm »

Suggested patch file attached. I haven't added the database connection time, as the header is "Queries" and my system returned a huge negative value.
Logged

Veronica

  • Contributor
  • Coppermine frequent poster
  • ***
  • Country: se
  • Offline Offline
  • Posts: 106
  • Coppermine 1.5.22
Re: Misleading database query statistics
« Reply #2 on: January 18, 2013, 02:08:24 am »

This looks good you add MySQL times in microseconds and publish results in milliseconds (ms) with 2 decimals.

For the question about database connection time its overhead you get for the queries
without a connection you can't execute any queries so time to connect and select a database must be included.

Test this replacement for cpg_db_connect:

Quote
function cpg_db_connect()
{
    global $CONFIG, $query_stats, $queries;

    $query_start = cpgGetMicroTime();
    $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);

    if (!$result) {
        return false;
    }
    $query_end = cpgGetMicroTime();
    if (!isset($CONFIG['debug_mode']) || $CONFIG['debug_mode'] == 1 || $CONFIG['debug_mode'] == 2) {
        $trace = debug_backtrace();
        $last = $trace[0];
        $localfile = str_replace(realpath(dirname(__FILE__) . DIRECTORY_SEPARATOR . '..') . DIRECTORY_SEPARATOR , '', $last['file']);

        $duration      = ($query_end - $query_start) * 1000;
        $query_stats[] = $duration;
        $queries[]     = "mysql_connect: {$CONFIG['dbserver']} [$localfile:{$last['line']}] (".round($duration, 2)." ms)";
    }

    $query_start = cpgGetMicroTime();
    if (!mysql_select_db($CONFIG['dbname'])) {
        return false;
    }
    $query_end = cpgGetMicroTime();
    if (!isset($CONFIG['debug_mode']) || $CONFIG['debug_mode'] == 1 || $CONFIG['debug_mode'] == 2) {
        $duration      = ($query_end - $query_start) * 1000;
        $query_stats[] = $duration;
        $queries[]     = "mysql_select_db: {$CONFIG['dbname']} [$localfile:{$last['line']}] (".round($duration, 2)." ms)";
    }

    if (!empty($CONFIG['dbcharset'])) {
        cpg_db_query("SET NAMES '{$CONFIG['dbcharset']}'", $result);
    }

    return $result;
}
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Misleading database query statistics
« Reply #3 on: January 24, 2013, 04:15:38 pm »

without a connection you can't execute any queries so time to connect and select a database must be included.

I don't see a reason why it must be included. It could be included, but I don't see a reason to include it. The heading is "Queries", as we want to list the database queries in case somebody wants to find slow queries or for whatever reason. Of course we could add a new section "database connection" or something like that above the queries which shows the information you request. But what's the benefit of this information? We just connect to/select the database once per page load and the average user doesn't need that value nor can fix it himself, usually. If there's something wrong with your MySQL server it's not Coppermine related and a small script will return the same worse value. The debug output isn't meant to provide basic system info.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Misleading database query statistics
« Reply #4 on: January 24, 2013, 04:48:53 pm »

Committed my first suggestion in SVN revision 8537.
Logged
Pages: [1]   Go Up
 

Page created in 0.021 seconds with 19 queries.