Use the same timestamp for the entire category, to avoid stats being
split across seconds. Also fix the timestamp on old stats in the same category
that were less than a minute apart.
Patch by pauamma.
diffs (83 lines):
diff -r d869e1462b6c -r a0086d1075d6 bin/upgrading/update-db-general.pl
--- a/bin/upgrading/update-db-general.pl Tue Sep 14 13:45:39 2010 +0800
+++ b/bin/upgrading/update-db-general.pl Tue Sep 14 14:51:37 2010 +0800
@@ -3895,6 +3895,36 @@ EOF
do_alter( 'poll2',
"ALTER TABLE poll2 ADD COLUMN isanon enum('yes','no') NOT
NULL default 'no'");
}
+
+ # Merge within-category split timestamps
+ if ( table_relevant( "site_stats" )
+ && !check_dbnote( "unsplit_stats_timestamps" ) ) {
+ # Because category+key+time is a UNIQUE key, there's no need to check
+ # for duplicates or inconsistencies. Instead, just rely on mysql
+ # complaining. Update is idempotent, interruptible, and restartable.
+ my $stats = $dbh->selectall_hashref(
+ qq{ SELECT category_id, insert_time, COUNT(*)
+ FROM site_stats
+ GROUP BY category_id, insert_time
+ ORDER BY category_id ASC,
+ insert_time ASC; },
+ [ qw( category_id insert_time ) ] );
+ die $dbh->errstr if $dbh->err || !defined $stats;
+ foreach my $cat ( keys %$stats ) {
+ my $lasttime;
+ foreach my $time ( sort { $a <=> $b } keys %{$stats->{$cat}} ) {
+ # Arbitrary limit is arbitrary
+ if ( defined $lasttime and $time - $lasttime < 60 ) {
+ do_sql( qq{ UPDATE site_stats SET insert_time = $lasttime
+ WHERE category_id = $cat
+ AND insert_time = $time } );
+ } else {
+ $lasttime = $time;
+ }
+ }
+ }
+ set_dbnote( "unsplit_stats_timestamps", 1 )
+ }
});
diff -r d869e1462b6c -r a0086d1075d6 cgi-bin/DW/StatStore.pm
--- a/cgi-bin/DW/StatStore.pm Tue Sep 14 13:45:39 2010 +0800
+++ b/cgi-bin/DW/StatStore.pm Tue Sep 14 14:51:37 2010 +0800
@@ -9,7 +9,7 @@
# Pau Amma <[email protected]>
# Afuna <[email protected]>
#
-# Copyright (c) 2009 by Dreamwidth Studios, LLC.
+# Copyright (c) 2009-2010 by Dreamwidth Studios, LLC.
#
# This program is free software; you may redistribute it and/or modify it under
# the same terms as Perl itself. For a copy of the license, please reference
@@ -27,7 +27,7 @@ DW::StatStore -- Statistics store update
# get pony stats from one day ago
DW::StatStore->get( 'ponies' );
-
+
# get pony stats over the last 30 days
DW::StatStore->get( 'ponies', 30 );
@@ -57,6 +57,10 @@ sub add {
my $dbh = LJ::get_db_writer()
or return undef;
+ # Using UNIX_TIMESTAMP can cause partial retrievals in get_latest if the
+ # database server clock ticks between keys for the category.
+ my $now = time;
+
while ( my ( $key, $val ) = splice( @stats, 0, 2 ) ) {
my $key_id = $class->to_id( $key )
or next;
@@ -65,8 +69,8 @@ sub add {
# statistics is not the end of the world
$dbh->do(
q{INSERT INTO site_stats (category_id, key_id, insert_time, value)
- VALUES (?, ?, UNIX_TIMESTAMP(), ?)},
- undef, $catkey_id, $key_id, $val+0
+ VALUES (?, ?, ?, ?)},
+ undef, $catkey_id, $key_id, $now, $val+0
);
}
_______________________________________________
dw-changelog mailing list
[email protected]
http://lists.dwscoalition.org/mailman/listinfo/dw-changelog
