diff options
| -rw-r--r-- | bgp-logger.org | 79 |
1 files changed, 71 insertions, 8 deletions
diff --git a/bgp-logger.org b/bgp-logger.org index 5f2ae17..f9a648f 100644 --- a/bgp-logger.org +++ b/bgp-logger.org @@ -7,11 +7,11 @@ Should've been started by init: - Filter port 50001, where BGPMon runs sudo ~linus/fw.sh -- BGPMon -- runs in foreground +- BGPMon -- runs in foreground (so you might want to to this in screen(1)) cd ~linus/bgpmon && sudo ~frank/bgpmon_v6/bgpmon -- bgpstore -- runs in foreground - sudo linus ~linus/src/bgp-logger/src/start-bgpstore.sh victoria 50001 bgpstore bgpstore bgpstore localhost +- bgpstore -- runs in foreground (so you might want to to this in screen(1)) + sudo -u bgpstore ~bgpstore/bgp-logger/src/start-bgpstore.sh victoria 50001 bgpstore bgpstore bgpstore localhost - psql -- for peeking in the database (pw = bgpstore) psql -h localhost bgpstore bgpstore @@ -43,12 +43,55 @@ should've read so now it does just that. * Running postgresql -To start the server - - sudo sudo -u postgres /opt/local/lib/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb +** Setting up postgres accounts +$ sudo -u postgres createuser +Enter name of role to add: bgpstore +Shall the new role be a superuser? (y/n) n +Shall the new role be allowed to create databases? (y/n) y +Shall the new role be allowed to create more new roles? (y/n) n +** Starting the server + sudo -u postgres /opt/local/lib/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb or - sudo -i -u postgres - /opt/local/lib/postgresql83/bin/pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l logfile start + sudo -iu postgres + /opt/local/lib/postgresql83/bin/pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l logfile start +** Backing up the bgpstore table + sudo -iu postgres + time nice pg_dump bgpstore | dd bs=10M of=/var/backups/postgresql/bgpstore + +With ~3.3G in /var/lib/postgresql/ I got + + 3039893830 bytes (3.0 GB) copied, 778.109 s, 3.9 MB/s + real 12m58.420s +** Creating index +After ~9 million records had been added to bgp_message, selecting on +prefix was really slow. We decided to create an index on that column, +which we should've done from the start. + +PostgreSQL can do that without locking the table but it will take much +longer and may fail, see [[http://www.postgresql.org/docs/8.3/interactive/sql-createindex.html][docu]]. We decided that it's perfectly ok to +lose some data at this point, so bgpstore was stopped for a couple of +minutes while the index was created. + +bgpstore=> create index prefix_idx on bgp_message (prefix); +CREATE INDEX +bgpstore=> \d bgp_message + Table "public.bgp_message" + Column | Type | Modifiers +----------------+-----------+---------------------------------------------------------- + id | integer | not null default nextval('bgp_message_id_seq'::regclass) + timestamp | integer | not null + precision_time | smallint | + prefix | cidr | not null + label | text | not null + path | integer[] | + nexthop | inet | + bgp_octets | text | not null +Indexes: + "bgp_message_pkey" PRIMARY KEY, btree (id) + "prefix_idx" btree (prefix) + +Searching for prefixes now happens within second(s) rather than a +minute. Feels much better. :-) * db layout <2009-06-24 Wed> Discussions with Fredrik. @@ -104,4 +147,24 @@ Disk used (whole system) early morning 2009-06-26: 1781468 Kb. | <2009-07-06 Mon 00:01> | | 2392316 | 499656 | 536224 45388 | | <2009-07-12 Sun 12:54> | 2.9 | 2831324 | 499128 | 536224 42864 | | <2009-07-16 Thu 22:04> | 3.86 | 3330048 | 499752 | 536224 45452 | +| <2009-07-27 Mon 14:01> | 5.8 | 4064096 | 499776 | 536224 43308 | | | | | | | + +** <2009-08-07 Fri> bgpstore isn't running +The SBCL process probably died when server was rebooted last Tuesday +(2009-07-28) since it ran in the foreground in a screen on server. + +We have 6221901 rows in the table. + +Lowest and highest timestamp: +Fri Jun 26 03:58:59 UTC 2009 +Tue Jul 28 13:27:29 UTC 2009 + +( + bgpstore=> select timestamp from bgp_message order by timestamp limit(1); + bgpstore=> select timestamp from bgp_message order by timestamp desc limit(1); + $ date -ur 1245988739 +) + +Restarting bgpstore: +2009-08-07 09:30:26: bgpstore started .. |