Commit | Line | Data |
---|---|---|
f9d757fc MM |
1 | #!/usr/bin/perl -w |
2 | ||
9705b3ad | 3 | # Where is the osm stats database? |
f9d757fc MM |
4 | $statsdb = 'osmstats'; |
5 | ||
6 | # How many tiles are a metatile? These get merged / counted as one tile. | |
7 | # If you really want to get stats for individual tiles and not per metatile, | |
8 | # just set this to 1. | |
9 | $metatilesize = 8; | |
10 | ||
11 | %stylemap = ( 'tiles' => 1, | |
12 | 'osmde' => 2, | |
13 | 'lowzoom' => 3, | |
9705b3ad MM |
14 | 'osmhd' => 4, |
15 | 'osmdehd' => 5 | |
f9d757fc MM |
16 | ); |
17 | ||
9705b3ad MM |
18 | %mapmap = ( 'osm' => 1, |
19 | 'osmde' => 2, | |
20 | 'osmhd' => 4, | |
21 | 'osmdehd' => 5 | |
f9d757fc MM |
22 | ); |
23 | ||
24 | # ----------------------------------------------------------------------------- | |
25 | ||
26 | use DBI; | |
27 | use POSIX qw(strftime mktime); | |
28 | ||
29 | # Parameter 0: table name | |
30 | # Returns: 1 if the table exists, or 0 if not. | |
31 | sub dbtableexists($) { | |
32 | my $cnt = $dbh->selectrow_array("SELECT COUNT(*) FROM pg_tables" . | |
33 | " WHERE tablename=" . $dbh->quote($_[0])); | |
34 | if ((defined($cnt)) && ($cnt > 0)) { | |
35 | return 1; | |
36 | } else { | |
37 | return 0; | |
38 | } | |
39 | } | |
40 | ||
41 | # Initializes database if it doesn't exist yet | |
42 | sub doinitdb() { | |
43 | unless (dbtableexists('tilerequests')) { | |
44 | my $res = $dbh->do( <<EOCR ); | |
45 | CREATE TABLE tilerequests ( | |
46 | styleid INTEGER NOT NULL DEFAULT NULL, | |
47 | date DATE NOT NULL DEFAULT NULL, | |
48 | x INTEGER NOT NULL DEFAULT NULL, | |
49 | y INTEGER NOT NULL DEFAULT NULL, | |
50 | z INTEGER NOT NULL DEFAULT NULL, | |
51 | requests BIGINT NOT NULL DEFAULT 0, | |
52 | PRIMARY KEY (styleid, date, x, y, z) | |
53 | ) | |
54 | EOCR | |
55 | unless ($res) { | |
56 | print("Could not create table tilerequests. $DBI::errstr\n"); exit(1); | |
57 | } | |
58 | } | |
59 | unless (dbtableexists('renderrequests')) { | |
60 | my $res = $dbh->do( <<EOCR ); | |
61 | CREATE TABLE renderrequests ( | |
62 | mapid INTEGER NOT NULL DEFAULT NULL, | |
63 | ts BIGINT NOT NULL DEFAULT NULL, | |
64 | x INTEGER NOT NULL DEFAULT NULL, | |
65 | y INTEGER NOT NULL DEFAULT NULL, | |
66 | z INTEGER NOT NULL DEFAULT NULL, | |
67 | rendertime REAL NOT NULL DEFAULT NULL, | |
68 | PRIMARY KEY (mapid, ts, x, y, z) | |
69 | ) | |
70 | EOCR | |
71 | unless ($res) { | |
72 | print("Could not create table renderrequests. $DBI::errstr\n"); exit(1); | |
73 | } | |
74 | } | |
75 | } | |
76 | ||
77 | sub showhelp() { | |
78 | print("Syntax: $0 --tirex|--apache [logfile]\n"); | |
79 | print("Parses the logfile given as tirex or apache log into the respective database table.\n"); | |
80 | print("If no logfile is given, uses STDIN.\n"); | |
81 | } | |
82 | ||
83 | unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) { | |
84 | print(STDERR "Failed to open database. Please try again later.\n"); exit(1); | |
85 | } | |
86 | # Create database tables if they do not exist yet | |
87 | doinitdb(); | |
88 | my $FILENAME = '-'; | |
89 | my $RUNMODE = 0; | |
90 | for ($i = 0; $i < @ARGV; $i++) { | |
91 | if ($ARGV[$i] eq '--help') { | |
92 | showhelp(); exit(0); | |
93 | } elsif ($ARGV[$i] eq '-h') { | |
94 | showhelp(); exit(0); | |
95 | } elsif ($ARGV[$i] eq '--tirex') { | |
96 | $RUNMODE = 2; | |
97 | } elsif ($ARGV[$i] eq '--apache') { | |
98 | $RUNMODE = 1; | |
99 | } else { | |
100 | unless ($FILENAME eq '-') { | |
101 | print("ERROR: At most one filename can be given on the command line.\n"); | |
102 | showhelp(); exit(1); | |
103 | } | |
104 | $FILENAME = $ARGV[$i]; | |
105 | } | |
106 | } | |
107 | unless (($RUNMODE >= 1) && ($RUNMODE <= 2)) { | |
108 | print("ERROR: You must select a logfile mode (--apache or --tirex)\n"); | |
109 | showhelp(); exit(1); | |
110 | } | |
111 | if ($FILENAME eq '-') { | |
112 | $ALF = STDIN; | |
113 | } else { | |
114 | unless (open($ALF, '<', $FILENAME)) { | |
115 | print(STDERR "ERROR: Could not open logfile $FILENAME\n"); | |
116 | exit(1); | |
117 | } | |
118 | } | |
119 | if ($RUNMODE == 1) { # Apache | |
120 | my %ctr = (); | |
121 | my $nlines = 0; | |
122 | while ($ll = <$ALF>) { | |
123 | $ll =~ s/[\r\n]//g; | |
124 | # 127.0.0.0 - - [23/Sep/2013:17:25:23 +0200] "GET /server-status?auto HTTP/1.1" 200 2431 "-" "libwww-perl/6.03" | |
125 | #print("$ll\n"); | |
126 | # If there is a " in the URL or referrer or user agent, it is escaped by | |
127 | # apache with '\"' - that makes it pretty hard to parse with a regexp. | |
128 | # Therefore, work around the problem by replacing the \" with something else. | |
129 | $ll =~ s/\\"/\%22/g; # HTML code for the " | |
130 | if ($ll =~ m/^([^ ]+) ([^ ]+) ([^ ]+) \[([^\]]+)\] "([^"]+)" ([^ ]+) ([^ ]+)/) { | |
131 | my $srcip = $1; | |
132 | my $statuscode = $6; | |
133 | my $bytessent = $7; | |
134 | my $filename = $5; | |
135 | if ($filename !~ m/^GET /) { next; } | |
136 | $filename =~ s/^GET //g; | |
137 | $filename =~ s!(http|https)://[^/]*!!g; | |
138 | $filename =~ s! HTTP/\d\.\d$!!g; | |
139 | $filename =~ s!\?[^ ]*$!!g; | |
140 | if (($statuscode !~ m/^2../) && ($statuscode !~ m/^304/)) { | |
141 | # only count successful requests. 304 is 'not modified', so a successful request. | |
142 | next; | |
143 | } | |
144 | if ($filename =~ m!([^/]+)/(\d+)/(\d+)/(\d+)\.png!) { | |
145 | my $sn = $1; my $z = $2; my $x = $3; my $y = $4; | |
146 | my $s = $stylemap{$sn}; | |
147 | unless (defined($s)) { next; } | |
148 | $x = $x & ~($metatilesize - 1); | |
149 | $y = $y & ~($metatilesize - 1); | |
150 | #print("tilestyle: $sn (# $s) $x $y $z\n"); | |
151 | if (defined($ctr{$s}{$z}{$y}{$x})) { | |
152 | $ctr{$s}{$z}{$y}{$x}++; | |
153 | } else { | |
154 | $ctr{$s}{$z}{$y}{$x} = 1; | |
155 | } | |
156 | $nlines++; | |
157 | } | |
158 | } | |
159 | } | |
160 | close($ALF); | |
161 | my $ninserts = 0; | |
162 | my $nupdates = 0; | |
163 | foreach $s (keys(%ctr)) { | |
164 | my %h1 = %{$ctr{$s}}; | |
165 | foreach $z (keys(%h1)) { | |
166 | my %h2 = %{$h1{$z}}; | |
167 | foreach $y (keys(%h2)) { | |
168 | my %h3 = %{$h2{$y}}; | |
169 | foreach $x (keys(%h3)) { | |
170 | #print("$s $z $y $x $h3{$x}\n"); | |
171 | my $date = strftime("%Y-%m-%d", localtime(time() - 86400)); | |
172 | local $dbh->{'PrintError'}; # We're fully aware that the execute can | |
173 | local $dbh->{'PrintWarn'}; # fail, no need to spam about it. | |
174 | unless ($dbh->do('INSERT INTO tilerequests(styleid, date, z, y, x, requests)' . | |
175 | ' VALUES(' . $s . ',' . $dbh->quote($date) . ',' . | |
176 | $z . ',' . $y . ',' . $x . ',' . $h3{$x} . ')')) { | |
177 | # Try again with update | |
178 | unless ($dbh->do('UPDATE tilerequests SET requests=requests+' . $h3{$x} . | |
179 | ' WHERE styleid=' . $s . ' AND date=' . $dbh->quote($date) . | |
180 | ' AND z=' . $z . ' AND y=' . $y . ' AND x=' . $x)) { | |
181 | print(STDERR "Both INSERT and UPDATE to DB failed: $DBI::errstr\n"); | |
182 | exit(1); | |
183 | } else { | |
184 | $nupdates++; | |
185 | } | |
186 | } else { | |
187 | $ninserts++; | |
188 | } | |
189 | } | |
190 | } | |
191 | } | |
192 | } | |
193 | print("Done. $nlines relevant lines of logfile were handled with $ninserts DB inserts and $nupdates DB updates.\n"); | |
194 | } | |
195 | if ($RUNMODE == 2) { # tirex logfile | |
196 | my $nreqs; | |
197 | while ($ll = <$ALF>) { | |
198 | my $reqtime; my $map; my $x; my $y; my $z; my $rendertime; | |
199 | $ll =~ s/[\r\n]//g; | |
200 | # 2015-09-07T13:37:58 id=1441625875_43436056 map=osmde x=34816 y=22928 z=16 prio=20 request_time=1441625875 expire= sources=MMMMMMMM render_time=3277 success=1 | |
201 | unless ($ll =~ m/\ssuccess=1/) { | |
202 | next; # We do not care about failed requests. | |
203 | } | |
204 | if ($ll =~ m/\srequest_time=(\d+)\s/) { | |
205 | $reqtime = $1; | |
206 | } else { | |
207 | next; | |
208 | } | |
209 | if ($ll =~ m/\smap=([^ ]+)\s/) { | |
210 | $map = $mapmap{$1}; | |
211 | unless (defined($map)) { next; } | |
212 | } else { | |
213 | next; | |
214 | } | |
215 | if ($ll =~ m/\srender_time=(\d+)\s/) { | |
216 | $rendertime = ($1 / 1000.0); | |
217 | } else { | |
218 | next; | |
219 | } | |
220 | if ($ll =~ m/\sx=(\d+)\s/) { $x = $1; } else { next; } | |
221 | if ($ll =~ m/\sy=(\d+)\s/) { $y = $1; } else { next; } | |
222 | if ($ll =~ m/\sz=(\d+)\s/) { $z = $1; } else { next; } | |
223 | #print("$map $z $y $x $rendertime\n"); | |
224 | unless ($dbh->do('INSERT INTO renderrequests(mapid, ts, z, y, x, rendertime)' . | |
225 | ' VALUES(' . $map . ',' . $reqtime . ',' . $z . ',' . | |
226 | $y . ',' . $x . ',' . $rendertime . ')')) { | |
227 | print(STDERR "Failed to insert renderrequest into DB: $DBI::errstr\n"); | |
228 | } | |
229 | $nreqs++; | |
230 | } | |
231 | close($ALF); | |
232 | print("Done. Inserted $nreqs entries into DB.\n"); | |
233 | } |